费了很多力,想优化的一个sql还是没优化出来Orz,但还是记录一下想办法的过程中学到的一些优化技巧和自己碰的壁。
首先,我想优化的sql长这样:
select * from a,o where o.type="xx" and a.aid=o.oid order by o.id limit 1900000,20000
两个表连接查询,可以先利用子查询通过单表过滤条件将结果集减小后再连接
优化后长这样:
select * from a.*,oo.* from a,(select * from o where o.type="xx" order by id limit 1900000,20000) where a.aid=o.aid
有一些效果,但离目标还差很远
先看子查询能不能优化
第一招 在条件字段type上加索引 但是type字段是一个选择性非常小的字段,一共只有几种值,且最常用的取值正式占比90%以上,所以这个字段加索引意义不大。 gg
第二招 在排序字段加索引 但是排序字段已经是主键了 gg
第三招 limit 1900000,20000改为 where id>1900000 limit 0,20000,这样由原来的读取1900000+20000条数据变为读取20000条数据,性能提升非常大 然而,我有过滤条件并且id本身是用雪花算法生成的,主键无法保证连续 gg
第四招 考虑将子查询中的select * 改为 select id 再连接o表自身。其实这里我不太明白,我知道select * 需要回表。但是联表查询为什么会比回表快?但是看到有人这么说,绝望的我还是试了一下,在mysql中(此时建了id_type索引)优化效果非常好,但在tidb中完全无效果。很迷。猜测可能是tidb中多一次网络传输的代价?
最终优化方案在第三招上做了调整,即where id>(上一次查出的集合中的id最大值) limit 20000