Mysql调优:表连接优化

Mysql存储引擎

  • MyISAM,不支持事务、也不支持外键,但其访问速度快,对事务完整性没有要求。
  • InnoDB,提供了具有提交、回滚和崩溃恢复能力的事务安全。但是比起MyISAM存储引擎,InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。
  • MEMORY, 使用存在内存中的内容来创建表。 每个MEMORY表只实际对应一个磁盘文件。MEMORY类型的表访问非常得快,因为它的数据是放在内存中的,并且默认使用HASH索引。
    image.png

表连接的类型

  • Left Join
  • Right Join
  • Inner Join
  • Full Join(Do not support)
image.png

临时表的使用

一旦涉及排序和分组,就很大可能会使用临时表,而临时表分两类:

  • 内存临时表 - MEMORY Engine
  • 磁盘临时表 - MyISAM Engine

根据临时表的大小(tmp_table_size),mysql会决定使用哪种临时表;众所周知,内存的IO速度远远超过了磁盘的IO速度,所以在做优化的时候,尽量避免使用磁盘临时表的情况。

常见优化方式

  • 先定位慢查询 show status like ‘slow_queries’;
  • 使用explain查看SQL的执行计划,如果出现Using temporary的字段,就意味着Query使用了临时表,如下图:
image.png
  • Order by或者Group by的字段建立索引
  • 拆分SQL语句,将查询与排序进行分离,为了减少临时表的大小,可以先找到符合条件的查询结果集,然后通过IN或者NOT IN的方式连接在一起。
  • 业务变通,了解业务的真正需求,比如查询是否一定要实时,数据是否需要强一致性,是否可以以空间换时间的做法解决问题。
    • 物化视图,预先将需要关联或者汇总的数据,通过实体表存放起来,避免Join查询
    • 增加冗余字段,减少连表查询
    • 增加冗余表,比如统计汇总表
    • 缓存, 数据库缓存或者程序的缓存
    • 增加索引
  • 应用层的优化
    • 业务代码进行结果集的组装(排序或者分组),减少数据库的Join。
    • 将不变的数据,从数据库读取出来后,就保存到内存中,直接调用相应的静态方法或者Map,减少和数据库的交互次数。
  • 架构层面优化
    • 读写分离,通过slave节点,分担读的压力,提高性能(与硬件升级类似)
    • 分库分表,会使Join更加复杂和麻烦(特别是如何保证高效的分布式事务一致性),不到万不得已,千万别使用。
    • Join太多,是否意味着这种多表联合查询,放在大数据分析里面会更合适?

参考

最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 151,688评论 1 330
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 64,559评论 1 273
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 101,749评论 0 226
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 42,581评论 0 191
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 50,741评论 3 271
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 39,684评论 1 192
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 31,122评论 2 292
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 29,847评论 0 182
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 33,441评论 0 228
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 29,939评论 2 232
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 31,333评论 1 242
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 27,783评论 2 236
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 32,275评论 3 220
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 25,830评论 0 8
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 26,444评论 0 180
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 34,553评论 2 249
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 34,618评论 2 249

推荐阅读更多精彩内容