慢SQL分析——强制索引、limit性能优化、join的底层原理

1、强制索引

今天操作了一张数据挺多的MySQL表，用到了如下SQL

select t1.id, t1.b, t1.c, t1.d, t2.e, t3.f
from t1
left join t2 on t1.a = t2.a
left join t3 on t1.b = t3.b
where t1.state = 1
order by t1.id
limit 0, 20

其中 t1.id 为其主键，t2.a 和 t3.b 均有索引。

所以按道理来说 t1会用到主键索引，t2会用到 index_a，t3会用到index_b。在测试环境中，用explain命令查看也确实是这样的。但到了线上，莫名其妙的不会用到t1的主键索引，所以全表查询，速度很慢。便考虑添加强制索引，如下：

select t1.id, t1.b, t1.c, t1.d, t2.e, t3.f
from t1 force index(PRIMARY)
left join t2 on t1.a = t2.a
left join t3 on t1.b = t3.b
where t1.state = 1
order by t1.id
limit 0, 20

加了强制索引之后，首页数据加载速度确实快了几倍不止。

 

2、limit性能优化

但是末尾页依然很慢，大佬提醒应该是offset过大时，limit有性能问题，便搜索了limit的优化方法。

优化原理如这篇文章所说：https://www.jb51.net/article/158044.htm

优化后的SQL语句如下，速度又快了n倍，cool！

select t1.id, t1.b, t1.c, t1.d, t2.e, t3.f
from t1 force index(PRIMARY)
inner join 
(
    select id from t1
    where state = 1
    order by t1.id
    limit 0, 20
) test on t1.id = t2.id
left join t2 on t1.a = t2.a
left join t3 on t1.b = t3.b

为什么用 inner join ，用 in 可以吗？不可以，原因如下

1. 首先结果集顺序得不到保证；

2. 然后 where 不能在 join 之前；

3. in 操作会遍历 t1 表，判断每一行的 id 是否在集合中，集合无索引；

改改SQL语句就能从卡的一批到流畅运行，所以说会优化SQL语句是很重要的。

 

3、order by还能如期望的那样正常工作吗？（join的底层原理）

在以上优化后的SQL中，将  order by t1.id 由语句末尾移动到了子语句中，那这么做会不会改变业务逻辑？

可以想到：若 inner join 的过程是遍历右表（即SQL中的临时表），每遍历一行右表，便在左表中找到对应的一行，此时本SQL业务逻辑是正确的。若 inner join 的过程是遍历左表（即 t1），每遍历一行左表，便在右表中找到对应的一行，此时本SQL业务逻辑是错误的。

 

所以 inner join 到底是怎么运行的呢？我通过查询资料得知，寻找结果集的方式不是固定的！

MySQL有 Index Nested-Loop Join 和 Block Nested-Loop Join 两种结果集寻找方式。具体差别可百度，简单来说就是前者的被驱动表有索引，后者无。而前者是遍历驱动表，后者是遍历被驱动表。

另外 inner join 驱动表的选择MySQL也是有优化的，优化后，永远是以小表驱动大表。

 

回到我的SQL语句中，临时表是小表，所以它是驱动表，t1 是被驱动表。t1 作为被驱动表有索引，故寻找结果集的方式是 Index Nested-Loop Join，即遍历驱动表的方式，即遍历临时表，所以业务逻辑没错！哈哈。。绕死我了。。

其他的 left join 操作都会已左表为驱动表。

 

为了确定对驱动表的分析是否正确，可以通过 explain 命令，结果表的第一行即为驱动表，对于这条 SQL 就是临时表，因此分析正确。

 

才疏学浅，分析SQL语句、MySQL原理还是有点难的，错误的地方希望有大佬指正。