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

发布时间：2022-09-12 16:18:07 所属栏目：MsSql教程来源：

导读：　　1 案例引入

　　某系统需要对特定的大量用户推送一些消息：

　　而首先要通过一些条件筛选出这些用户，而该过程很耗时！

　　日活百万，注册用户千万，而且若还未分库分表，则该DB里的用户表可能就一

　　1 案例引入

　　某系统需要对特定的大量用户推送一些消息：

　　而首先要通过一些条件筛选出这些用户，而该过程很耗时！

　　日活百万，注册用户千万，而且若还未分库分表，则该DB里的用户表可能就一张，单表就上千万的用户数据。对该运营系统筛选用户的SQL：

　　SELECT id, name FROM users WHERE id IN ( SELECT user_id FROM users_extent_info # 查询最近登录过的用户 WHERE latest_login_time < xx)

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

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

　　SELECT COUNT(id)FROM usersWHERE id IN ( SELECT user_id FROM users_extent_info WHERE latest_login_time < xxxxx )

　　再在内存里再做小批量的批次读数据操作，比如判断：

　　但在千万级数据量的大表下，上面SQL竟然耗时几十s！

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

　　通过：

　　EXPLAIN SELECT COUNT(id) FROM users WHERE id IN ( SELECT user_id FROM users_extent_info WHERE latest_login_time < xx)

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

　　| id | select_type | table | type | key | rows | ?ltered | Extra |+----+-------------+-------+------------+-------+---------------+----------+---------+---| 1 | SIMPLE | | ALL | NULL | NULL | 100.00 | NULL || 1 | SIMPLE | users | ALL | NULL | 49651 | 10.00 | Using where; Using join bu?er(Block Nested Loop) || 2 | MATERIALIZED | users_extent_info | range | idx_login_time | 4561 | 100.00 | NULL |

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

　　2 到底为什么慢？

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

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

　　对users表的全表扫描、对users表的每一条数据跑到物化临时表里做全表扫描都很耗时！所以最后结果必然很慢，几乎用不到索引，难道MySQL疯了？

　　看完执行计划之后，我们可以再执行：

　　3 show warnings

　　显示出：

　　/* select#1 */ select count( d2. users . user_id `) AS COUNT(users.user_id)`from d2 . users users semi join xxxxxx

　　注意 semi join ，MySQL在这里生成执行计划时，自动就把一个普通IN子句“优化”成基于semi join来进行 IN+子查询的操作，那这对users表不就是全表扫描了吗？

　　对users表里的每条数据，去对物化临时表全表扫描做semi join，无需将users表里的数据真的跟物化临时表里的数据join。只要users表里的一条数据，在物化临时表能找到匹配数据，则users表里的数据就会返回，这就是semi join，用来做筛选。

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

　　4 做个实验SET optimizer_switch='semijoin=o?'

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

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

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

　　SELECT COUNT(id)FROM usersWHERE ( id IN (SELECTuser_idFROMusers_extent_infoWHERElatest_login_time

　　上述写法下，WHERE语句的OR后面的第二个条件，业务上根本不可能成立，所以不会影响SQL的业务语义，但改变SQL后，执行计划也会变，就不会再semi join优化了，而是常规地用了子查询，主查询也是基于索引。

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

（编辑：开发网_商丘站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

SQL数据库备份怎样做？	sqlserver中行列互换怎
小议sqlserver数据库主	SQLserver 数据库危险