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原理还是有点难的,错误的地方希望有大佬指正。