MySQL查询优化

        MySQL版本：5.7.31。

---

1 索引优化

        《MySQL索引优化》

---

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+树上叶子节点会存放所有这行的数据。二级索引相比于主键索引数据量会更少，理论上来说检索的性能会更高。