由于工作需要,我对tidb sql 优化有几个月的经验,现经验在分享如下
1. EXPLAIN ANALYZE
可以查看sql执行计划,并且sql会实际执行一遍,给出具体每个扫描函数,比EXPLAIN 更加有参考价值
2. with as
将一条sql中重复出现的部分用with as 抽取出来
3. where条件下推
部分嵌套的子查询,外层有where条件筛选,这些where条件有些可以直接在内层的子查询中执行,这样内层的查询结果集就会变小,减轻了外层查询的负担,sql速度就能提升
4. where条件复制
部分表在sql中重复出现,where条件各不相同,若业务允许,可将每个表的where条件增多(复制其他部分的where条件过来),有利于减少结果集增加sql运行速度
5. 分页参数limit内推
嵌套的sql中,limit往往在最外层,若业务允许,可将limit放入内层sql,用于减少内层sql结果集,增加sql运行速度
6. 加索引
7. left join改成子查询
大部分left join的左表和右表都是一对一关系。某些情况下,明明sql最终的结果集条数很少,但是执行计划中left join的表却是全表扫描。可以将left join转化为字段上的子查询,在sql最终结果集较少数据的情况下,每条数据只会通过子查询走索引查一次,无需全表扫描,sql速度则会提升
8. count 的情况下省略不必要的left join
部分count sql中,有很多left join,这些left join左表和右表是一对一关系,因此即使去掉这些left join也不会对最终的count结果产生影响,却能减少一个left join的工作
9.In、join、exist互转
众所周知,In、join、exist在底层其实都是join算子实现的,因此in和join有时候是可以互转的,互转之后可能会产生一些意想不到的优化效果。比如有个表in是全表扫描的,改成join之后反而能走索引了。
11. 避免回表查询
避免回表查询 完全在索引中进行推导和扫面非常快,但是如果需要回表查询,则会非常慢。比如在索引中查了几万的数据,每条数据都要返回主表关联其他字段,速度就会很慢。如果索引中存在需要关联的其他字段,自然就不需要回表了
12. 冗余字段
13. 小表join大表
14. limit改成游标分页
导出数据的时候,可能会出现这种limit 500000,1000 这种情况可以改成根据某个不重复的字段进行排序,记录本页该字段的最大值,下一页大于这个上一页的最大值进行查询即可得到下一页的数据
15. 子查询转left join后进行group by
sql的结果集很小,Tidb中有些字段的子查询明明能走索引却要全表扫描,可以改成等价的left join, 如果子查询中有汇总的操作,left join之后用group by进行汇总