这条优化原则要求查询中按执行顺序尽量用小数据集驱动大数据集
在使用in条件时,先执行in条件子句此时由于B表先查,A表后查B表的数据集应小于A表的数据集。
B表数据集小于A表数据集时用in优于exists。
在使用exists条件时先执行外层的查询,此時由于A表先查B表后查,A表的数据集应小于B表的数据集
A表数据集小于B表数据集时,用exists优于in
注意:A表和B表的id字段都应建索引
exists:将主查询嘚数据放到子查询中做条件验证,根据验证结果来决定主查询的数据是否保留
order by子句应尽量使用index方式排序避免使用filesort方式排序。因为index方式排序效率高而filesort存在文件内排序,降低了执行效率
order by子句使鼡index方式排序需要满足以下条件之一:
因此尽可能在索引列上完成排序操作,并遵循索引的左前列原则
从磁盘读取查询需要的所有列,按照order by列在buffer中对进行排序然后扫描排序后的列表进行输出。避免了二次读取数据并把隨机IO变成了顺序IO。
这个流程引出了一个问题:
filesort需要一次性将所有排序字段都取出如果超过了sort_buffer的容量,导致每次只能取出该容量的数据通过创建tmp文件、多路合并的方式来排序,将大大增加IO次数导致性能急剧下降。
group by实质上是先排序再分组因此也遵循索引的左前缀原则,其索引失效情况和order by保持一致
需要注意的是group by还有一个having子句作为分组后的条件,由于where子句先于having执行因此能在where中限定的条件不要放在having中。
众所周知InnoDB存储引擎是支持行锁的适合高并发场景,但由于MySQL的行锁是通过索引实现的即行锁是通过给索引項加锁来实现的,只有通过索引条件查找数据InnoDB才使用行级锁,否则将使用表锁因此当索引失效时,行锁会变成表锁导致性能下降