实用性极强的MySQL查询优化策略( 二 ) _MySQL

通过explain分析可以发现，左边的表走了全表扫描，可以考虑给左边的表的tenant_name和user表的account 各自创建索引。
create index idx_name on tenant(tenant_name);
create index idx_account on `user`(account);
再次使用explain分析结果如下：

文章插图
可以看到第二行type变为ref，rows的数量优化比较明显。这是由左连接特性决定的， LEFT JOIN条件用于确定如何从右表搜索行，左边一定都有，所以右边是我们的关键点，一定需要建立索引。
3.内连接关联的字段建立索引
我们知道，左连接和右连接查询的数据分别是完全包含左表数据，完全包含右表数据，而内连接（inner join 或join）则是取交集（共有的部分），在这种情况下，驱动表的选择是由mysql优化器自动选择的。
在上面的基础上，首先移除两张表的索引：
ALTER TABLE `user` DROP INDEX idx_account;
ALTER TABLE `tenant` DROP INDEX idx_name;
使用explain语句进行分析：

文章插图
然后给user表的account字段添加索引，再次执行explain我们发现，user表竟然被当作是被驱动表了。

文章插图
此时，如果我们给tenant表的tenant_name加索引，并移除user表的account索引，得出的结果竟然都没有走索引，再次说明，使用内连接的情况下，查询优化器将会根据自己的判断进行选择。

文章插图
四、子查询优化
子查询在日常编写业务的SQL时也是使用非常频繁的做法，不是说子查询不能用，而是当数据量超出一定的范围之后，子查询的性能下降是很明显的，关于这一点，本人在日常工作中深有体会。
比如下面这条sql，由于student表数据量较大，执行起来耗时非常长，可以看到耗费了将近3秒。
select st.* from student st where st.classId in (
select id from class where id > 100
);
通过执行explain进行分析得知，内层查询 id > 100的子查询尽管用上了主键索引，但是由于结果集太大，带入到外层查询，即作为in的条件时，查询优化器还是走了全表扫描。

文章插图
针对上面的情况，可以考虑下面的优化方式：
select st.id from student st join class cl on st.classId = cl.id where cl.id > 100;
子查询性能低效的原因

子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表，然后外层查询语句从临时表中查询记录，查询完毕后，再撤销这些临时表。这样会消耗过多的CPU和IO资源，产生大量的慢查询；
子查询结果集存储的临时表，不论是内存临时表还是磁盘临时表都不能走索引，所以查询性能会受到一定的影响；
对于返回结果集比较大的子查询，其对查询性能的影响也就越大。

使用mysql查询时，可以使用连接（JOIN）查询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用索引的话，性能就会更好，尽量不要使用NOT IN 或者 NOT EXISTS，用LEFT JOIN xxx ON xx WHERE xx IS NULL替代。
一个真实的案例
在下面的这段sql中，优化前使用的是子查询，在一次生产问题的性能分析中，发现某个tenant_id下的数据达到了35万多，这样直接导致某个列表页面的接口查询耗时达到了5秒左右。

文章插图
找到了问题的根源后，尝试使用上面的优化思路进行解决即可，优化后的sql大概如下：

文章插图
五、排序（order by）优化
在MySQL，排序主要有两种方式：

Using filesort : 通过表索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序；
Using index : 通过有序的索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。

对于以上两种排序方式，Using index的性能高，而Using filesort的性能低，我们在优化排序操作时，尽量要优化为 Using index 。