千万级用户系统的SQL调优实战

某系统专门通过各种条件筛选大量用户，接着对那些用户去推送一些消息：

通过一些条件筛选出大量用户，针对这些用户做推送，该过程较耗时-筛选用户过程。

用户日活百万级，注册用户千万级，而且若还没有进行分库分表，则该DB里的用户表可能就一张，单表上千万的用户数据。

对运营系统筛选用户的SQL：

一般存储用户数据的表会分为两张表：

有个子查询，里面针对用户的拓展信息表，即 users_extent_info 查下

最近一次登录时间 某个时间点

的用户，可以查询最近才登录过的用户，也可查询很长时间未登录的用户，然后给他们发push，无论哪种场景， 该SQL都适用。

然后在外层查询，用id IN子句查询 id 在子查询结果范围里的users表的所有数据，此时该SQL突然会查出很多数据，可能几千、几万、几十万，所以执行此类SQL前，都会先执行count：

然后内存里做个小批量，多批次读取数据的 *** 作，比如判断如果在1000条以内，那么就一下子读取出来，若超过1000条，可通过LIMIT语句，每次就从该结果集里查1000条数据，查1000条就做次批量PUSH，再查下一波1000条。

就是在千万级数据量大表场景下，上面SQL直接轻松跑出来耗时几十s，不优化不行！

今天咱们继续来看这个千万级用户场景下的运营系统SQL调优案例，上次已经给大家说了一下业务背景 以及SQL，这个SQL就是如下的一个：

系统运行时，先COUNT查该结果集有多少数据，再分批查询。然而COUNT在千万级大表场景下，都要花几十s。实际上每个不同的MySQL版本都可能会调整生成执行计划的方式。

通过：

如下执行计划是为了调优，在测试环境的单表2万条数据场景，即使是5万条数据，当时这个SQL都跑了十多s，注意执行计划里的数据量

第二条执行计划的全表扫描结果表明一共扫到49651条，但全表扫描过程中，因为和物化临时表执行join，而物化临时表里就4561条数据，所以最终第二条执行计划的filtered=10%，即最终从users表里也筛选出4000多条数据。

先执行了子查询查出4561条数据，物化成临时表，接着对users主表全表扫描，扫描过程把每条数据都放到物化临时表里做全表扫描，本质在做join。

对子查询的结果做了一次物化临时表，落地磁盘，接着还全表扫描users表，每条数据居然跑到一个没有索引的物化临时表里，又做了一次全表扫描找匹配的数据。

对users表的全表扫描耗时吗？

对users表的每一条数据跑到物化临时表里做全表扫描耗时吗？

所以必然非常慢，几乎用不到索引。为什么MySQL会这样呢？

执行完上述SQL的EXPLAIN命令，看到执行计划之后，再执行：

显示出：

注意 semi join ，MySQL在这里，生成执行计划的时候，自动就把一个普通IN子句，“优化”成基于semi join来进行IN+子查询的 *** 作。那对users表不是全表扫描了吗？对users表里每条数据，去对物化临时表全表扫描做semi join，无需将users表里的数据真的跟物化临时表里的数据join。只要users表里的一条数据，在物化临时表能找到匹配数据，则users表里的数据就会返回，这就是semi join，用来做筛选。

所以就是semi join和物化临时表导致的慢题，那怎么优化？

执行

关闭半连接优化，再执行EXPLAIN发现恢复为正常状态：

所以，其实反而是MySQL自动执行的semi join半连接优化，导致了极差性能，关闭即可。

生产环境当然不能随意更改这些设置，于是想了多种办法尝试去修改SQL语句的写法，在不影响其语义情况下，尽可能改变SQL语句的结构和格式，最终尝试出如下写法：

上述写法下，WHERE语句的OR后面的第二个条件，根本不可能成立，因为没有数据的latest_login_time -1，所以那不会影响SQL业务语义，但改变SQL后，执行计划也会变，就没有再semi join优化了，而是常规地用了子查询，主查询也是基于索引，同样达到几百ms 性能优化。

所以最核心的，还是看懂SQL执行计划，分析慢的原因，尽量避免全表扫描，务必用上索引。

使用视图的两种算法merge和temptable分别统计

表tb_phone中market_price大于4000的手机,然后查询视图查找出小于6000的手机

简单总结最终获取的结果:查询出market_price大于4000且小于6000的手机

表数据:

merge合并算法

合并的执行方式,每当执行的时候,先将视图的sql语句与外部查询视图的sql语句,合并在一起,最终执行.

以下是使用常规select语句模拟合并算法:

执行结果模拟与算法的结果相同:

temptable临时表算法

先执行视图再执行其他 *** 作的查询

临时表模式,每当查询的时候,将视图所使用的select语句生成一个结果的临时表,再在当前的临时表内进行查询

以下是使用常规select语句模拟临时表算法:

执行结果模拟与算法的结果相同:

比较两种算法结果也相同,似乎是这样的,接下来的实例将不同

实例2

使用视图的两种算法merge和temptable分别统计

表tb_phone , 同类(cid)手机的最高价格(market_price)的产品

表数据:

首先可以考虑基本select语句的实现方式(这里就不再赘述):

先排序再分组

接下来使用两种算法:

merge合并算法

最后一句select模拟合并算法有语法错误的,order by 不能在 group by之前,这时执行会报错,但在视图中不会报错,而是直接跳过了错误部分order by 直接执行了

select * from tb_phone group by cid

最终结果(错误):

temptable临时表算法

发现执行结果与普通语句相同,模拟的语句同样正确

最终结果(正确):

解决办法：

当临时表不存在时，将查询结果保存在临时表中：

CREATE TEMPORARY TABLE tmp_table SELECT * FROM table_name；

我的具体代码是

CREATE temporary table tmp

(SELECT t1.pid a, t1.pname b,t2.pid c, t2.pdescribe d FROM

(SELECT pid,pname FROM admin_advert_place WHERE pid IN (3,4,5,6,7)) t1 LEFT JOIN

(SELECT pid,pdescribe FROM admin_advert_place

WHERE pgid IN (6,7,8)) t2 ON t1.pid = t2.pid)

---