MySQL实战：SQL调优案例实战

SQL调优的基本思路

千万用户场景下的运营系统SQL调优

本文总结自《从零开始带你成为MySQL实战优化高手》

在互联网公司中，需要用运营系统筛选出大量的用户，接着对这些用户推送消息，执行的sql如下

users存储用户的核心数据，比如id，name，昵称等

users_extent_info存储用户的拓展信息，比如家庭住址，兴趣爱好，最后一次登陆时间等

当然我们得先统计一下相关的用户量有多少，于是执行如下sql

在千万级数据量的大表场景下，上面的sql跑出耗时几十秒的速度 对应的执行计划如下

先看执行计划第三行，select_type为MATERIALIZED表明对子查询进行了物化

接着看执行计划的第一二行id值相同，表明物化表与users表进行连接查询

既然是连接查询，说明mysql在生成执行计划的时候，自动把一个普通的in语句，优化成了基于semi_join的连接操作

而这个连接操作驱动表和被驱动表都是全表扫描，这就是性能差的原因所在

我们来验证一下想法，首先执行set optimizer_switch=‘semijoin=off’，关闭半连接优化，此时执行explain命令看一下此时的执行计划，发现此时恢复为一个正常的状态

就是有一个SUBQUERY的子查询，基于range的方式去扫描，接着有一个PRIMARY类型的主查询，直接基于主键聚簇索引去执行搜索。再把这个sql跑一下看看，发现性能提升了几十倍，变成了100多豪秒

当然生产环境是不能随意更改这些配置的，所以我们只需要更改一下sql的写法，让其不会产生半查询优化

在原有的语句上加了一个or条件，但是or条件是不可能成立的，因为没有数据的latest_login_time是小于-1的，由于or条件的加入不满足半查询优化的条件，就不会在进行半查询优化，而是正常的用了子查询

亿级数据量商品系统的SQL调优实战

数据库的监控系统中发现了慢查询

这是一个很简单的语句，根据商品的类别及其子类进行筛选，然后按id倒序排，最后分页。上面这个语句居然一执行就是几十秒

数据库的连接基本被慢查询打满，一个连接要执行几十秒的sql，才能执行下一个sql，数据库基本就报废了

按理说用到category索引的时候速度是很快的，explain看一波

possible_keys里是有我们的index_category，结果实际用的不是这个索引，而是PRIMARY

使用 force index 语法

强制让sql使用你指定的sql，此时再次执行这个语句，会发现他仅仅耗费100ms而已，性能瞬间就上来了

数十亿数量级评论系统的SQL调优实战

对评论表进行分页查询的sql

where product_id = ‘xx’ and is_good_comment = ‘1’ 这2个条件，不是一个联合索引，必然会出现大量的回表操作，这个耗时是极高的

改写一下上述sql

这个语句就会彻底改变他的执行计划，先执行括号里面的子查询，子查询会使用PRIMARY聚集索引，按照聚集索引的id值倒序方向进行扫描，把符合 where product_id = ‘xx’ and is_good_comment = ‘1’ 条件的数据筛选出来

你会从执行计划看到针对子查询的结果集，一个临时表，进行全表扫描，拿到20条数据，接着对20条数据进行遍历，每一条数据按照id去聚集索引里找一下完整的数据，就可以了

总结

in语句被优为semi_join的形式，2表join导致执行效率很低，改写sql将其变为SUBQUERY子查询即可

sql语句没有正确使用索引，用 force index 强制指定使用的索引即可

深分页导致大量回表操作，将其改为派生表查询

参考博客

[1]https://mp.weixin.qq.com/s/2ATCvniADrxyb0MhV5k3EQ