MySQL 性能优化系列之一 单表预处理
背景介绍
我们经常在写多表关联的SQL时，会想到 left jion（左关联），right jion（右关联），inner jion（内关联）等。
但是，当表中数据量过大时，如果没有写好查询条件或者查询条件书写的先后顺序不同，可能会有明显的性能差别。
近期，有个同事遇到一个SQL查询比较慢的问题：tableA，tableB，tableC三张表联合查询的SQL，查询用时将近50s。
原因分析
1、分别确认3张表的数据量
tableA：3千万 条记录；
tableB：5千 条记录；
tableC：7千 条记录；
2、确认SQL查询逻辑
SQL如下（查询tableA中name包含miracle，tableB中age大于20岁，tableC性别为male的联合信息）：
select * from tableA a, tableB b, tableC c where a.id = c.id and b.uuid = c.uuid and a.name like ‘%miracle%‘ and b.age > 20 and c.sex = ‘male‘
上述SQL的效果和内关联等价，根据SQL的关联逻辑我们可以知道，表之间关联查询，其实就是集合之间先做“笛卡尔积”，根据查询条件对这个笛卡尔积结果集再次做过滤。
可以看到，此时笛卡尔积的集合容量为：(3千万 )*(5千 )*(7千 )，是一个百万亿级的庞大数据集合。
因此从这个庞大集合中，再按照过滤条件查询想要的数据，当然会慢很多。
调优方案
1、单表预处理
tableA 预处理 (处理后，tableA 中“有效”数据量级降到1千 )：
select * from tableA where name like ‘%miracle%‘
tableB 预处理 (处理后，tableB 中“有效”数据量级降到2千 )：
select * from tableB where age > 20
tableC 预处理 (处理后，tableC 中“有效”数据量级降到3千 )：
select * from tableC where sex = ‘male‘
此时三张表“笛卡尔”的数据量级为：(1千 )*(2千 )*(3千 )，约为 十亿级 的数据集合。相比之前，量级已经降低了十万倍。
2、调整查询SQL结构
select * from (select * from tableA where name like ‘%miracle%‘) a, (select * from tableB where age > 20) b, (select * from tableC where sex = ‘male‘) c where a.id = c.id and b.uuid = c.uuid
此时，SQL的查询时间为 0.14s，相比之前的50s，查询速度已经提高了几百倍。
3、表关联方式转换（二次优化）
做了上面的操作，查询速度得到了明显提高。
如果我们想让查询效率更上一层楼，可以对关联方式做下调整。
由于是三张表关联，左关联和内关联在性能上还是有很大差距的。
此时三表的左关联比内关联查询性能上是提高的，SQL调整如下：
select * from (select * from tableA where name like ‘%miracle%‘) a left jion (select * from tableC where sex = ‘male‘) c on a.id = c.id left jion (select * from tableB where age > 20) b on b.uuid = c.uuid
此时 tableA 和 tableC 左关联的笛卡尔积集合容量为(1千 )*(2千 )，是一个 百万级 的数据集合，经过 a.id = c.id 过滤后得到是一个 1千 的数据集合
将tableA 和 tableC 左关联后的结果集和 tableB 进行左关联，其笛卡尔积集合容量为 为(1千 )*(3千 )，也是一个 百万级 的数据集合。
相比于步骤2的 十亿量级， 又降低了 1000 倍。最终，上述 SQL执行用时不到 0.1s
优化总结
对于数据表的数据量比较大的多表联合查询的场景。
SQL优化原则如下：
1、预处理单表数据，获取每张表的“有效”数据，达到首次“降级”的目的；
2、调整关联关系，实现二次“降级”。
（说明：本文说的“降级”，指的是降低SQL执行的数量级）
PS：
希望能帮到大家，谢谢！