MySQL版本:5.7.31。
1 索引优化
2 分页查询优化
最容易想到的分页sql如下所示:
select * from employees limit 10000,10;
上面表示从employees表中取出从10001行开始的10行记录,但是实际上MySQL是先查出10010行记录,然后再把前面的10000行记录删掉。可想而知,如果数据量特别大的情况下,这种方式的效率会很低。
常见的优化策略有如下两种:
2.1 根据自增且连续的主键排序
EXPLAIN select * from employees limit 90000,5;
EXPLAIN select * from employees where id > 90000 limit 5;
这种方式有两个限制:主键必须是自增且连续的,而且结果是按照主键进行排序的。
从执行计划上可以看到使用了范围索引,但是正如上面红字所说,该种情况的要求还是比较严格的,在很多场景并不实用,因为实际情况下可能中间的数据被删除了导致主键不再连续,这样查询出的结果和实际有差别。
2.2 根据非主键字段排序
EXPLAIN select * from employees ORDER BY name limit 90000,5;
可以看到并没有使用索引,而且使用到了文件排序。MySQL优化器会内部进行判断,如果发现全表扫描的成本要低于走索引,那么就不会使用索引。改用下面的方式:
EXPLAIN select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;
从执行计划上可以看到会先执行子查询,而子查询因为查的是主键id,所以会走覆盖索引。然后再根据id查外面的查询。因为分页的话一页一般都是10条数据,最多也就50条,数据量很小,所以外查询的效率也并不低。
3 Join关联查询优化
- 关联字段要加上索引。
- 小标驱动大表,如果left join和inner join都能达到需求的话,尽量使用inner join,让MySQL优化器去判断谁是小表,因为大部分情况下mysql优化器是比人要聪明的。
4 in和exsits优化
in:当B表的数据量小于A表的数据量时,in优于exists
select * from A where id in (select id from B)
exists:当A表的数据量小于B表的数据量时,exists优于in
select * from A where exists (select 1 from B where B.id = A.id)
5 count(*)查询优化
EXPLAIN select count(1) from employees;
EXPLAIN select count(id) from employees;
EXPLAIN select count(name) from employees;
EXPLAIN select count(*) from employees;
可以看到四种sql的执行计划都是一样的,说明这四种sql的效率应该是差不多的。区别是如果使用第三种根据某个非主键字段进行count的话,需要留意该字段是否有null值,该种方式是不会统计null值的数量的,而其它三种会统计。
这里可能会对第二种方式有些疑惑,为什么根据主键进行count查找不会走主键索引而走了二级索引?原因是因为二级索引B+树的叶子节点上只会保存指向主键索引B+树上的主键的指针,而主键索引B+树上叶子节点会存放所有这行的数据。二级索引相比于主键索引数据量会更少,理论上来说检索的性能会更高。