Mysql 查询优化
MySQL查询优化
查询优化就是MySQl会将程序员编写的⼀些⽐较耗费性能的语句进⾏查询重写
- 条件化简
- 移除不必要的括号
- 常量传递：
（1）某个表达式是某个列和某个常量的等值匹配，会直接⽤常量替换列名 - 移除没⽤的条件：
（1）⽐如永远为true或false： - 表达式计算：
（1）表达式只包含常量的话，值会被计算出来
（2）如果某个列在函数中或者以运算形式出现，优化器不会进⾏化简 - having和where⼦句的合并：
（1）查询语句中没有sum、max这样的聚集函数以及group⼦句的话，优化器会将having和where⼦句合并
（2）having⼦句⽤于分组后过滤，where⽤于分组前合并 - 常量表检测：
（1）使⽤主键等值匹配、使⽤唯⼀⼆级索引列等值匹配进⾏查询的表称为常量表
（2）优化器会优先执⾏常量表查询，因为速度⾮常快 - 外连接消除
- 优化器会将右连接转换为左连接查询
- 空值拒绝
（1）在外连接查询中，指定的where⼦句中包含被驱动表的列不为null值的条件（就是不允许查出来的记录中含有null值）
（2）被驱动表的where⼦句符合空值拒绝的条件后，外连接和内连接可以相互转换
⼦查询优化
- ⼦查询简介
在⼀个查询语句中的某个位置可以出现另⼀个查询语句，这另⼀个查询就叫⼦查询
（1）按出现位置分类
- 在select⼦句中
1
select (select m1 from t1 limit 1);
- 在from⼦句中 将这种⼦查询的结果当做⼀个表，在from⼦句中的⼦查询称为派⽣表
1
select m, n from (select ...);
- 在where或on⼦句中
1
select * from t1 where m1 in (select ...);
（2）按返回的结果集分类
- 标量⼦查询
只返回⼀个单⼀值1
select (select m1 from t1 limit 1);
- ⾏⼦查询
返回⼀条记录，需要包含多个列使⽤limit 1保证⼦查询的结果只有⼀条记录1
select * from t1 where (m1, n1) = (select m2, n2 from t2 limit 1);
- 列⼦查询
返回⼀个列的数据，可能包含多条纪录1
select * from t1 where m1 in (select m2 from t2);
- 表⼦查询
⼦查询的结果既有多条纪录，又有多个列1
select * from t1 where (m1, n1) = (select m2, n2 from t2);
- 按外层查询关系分类
- 不相关⼦查询
⼦查询可以单独运⾏出结果，不依赖于外层查询的值
上⾯都是例⼦
- 相关⼦查询
⼦查询的执⾏需要依赖于外层查询的值1
select * from t1 where m1 in (select m2 from t2 where n1 = n2);
⼦查询的注意事项：
- 必须⽤⼩括号括起来
- select⼦句中的⼦查询必须是标量⼦查询
- 要想得到标量⼦查询/⾏⼦查询，应该使⽤limit 1
- 对于[not] in/any/som/all⼦查询来说，⼦查询不允许出现limit
- ⼦查询中不必使⽤order by：⼦查询相当于⼀个集合，集合没必要排序
- ⼦查询中不必使⽤distinct，因为集合也不需要去重
- ⼦查询中没有聚集函数以及having⼦句时，不必使⽤group by⼦句
⼦查询的执⾏⽅式
（1）不优化
- 对于不相关⼦查询，先执⾏⼦查询，再将⼦查询的结果作为外层查询的参数
- 对于相关⼦查询，先从外层查询中取⼀条记录，取相关列进⾏⼦查询，如此循环
（2）in⼦查询的优化
（3）物化
- 将⼦查询的结果写⼊临时表中，该临时表就是物化表
- 基于内存的物化表建⽴哈希索引，基于磁盘的物化表建⽴B+树索引
（4）半连接 - s1表和s2表半连接的意思是：对于s1表的某条肌理，只关⼼在s2表中是否存在与之匹配的记录，⽽不关⼼与多少条记录与之匹配
- 这只是MySQL内部执⾏⼦查询的⼀种⽅式，不⾯向⽤户
（5）实现⽅式
（6）table pullout（表上拉） - 查询列表只有主键或唯⼀索引列是，将⼦查询中的表上拉到外层查询的from⼦句中
（7）重复值消除
- ⽤临时表消除半连接结果集的重复值
- 松散扫描
- 半连接物化