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太多，是否意味着这种多表联合查询，放在大数据分析里面会更合适？

参考

• https://dev.mysql.com/doc/refman/8.0/en/internal-temporary-tables.html