SQL系统调优来应对千万级用户

79次阅读

共计 3063 个字符，预计需要花费 8 分钟才能阅读完成。

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

SQL 系统调优来应对千万级用户
某系统专门通过各种条件筛选大量用户，接着对那些用户去推送一些消息：

一些促销活动消息
让你办会员卡的消息
告诉你有一个特价商品的消息
通过一些条件筛选出大量用户，针对这些用户做推送，该过程较耗时 - 筛选用户过程。

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

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

SELECT id, name 
FROM users 
WHERE id IN (
  SELECT user_id 
  FROM users_extent_info 
  WHERE latest_login_time 
 一般存储用户数据的表会分为两张表：
 存储用户的核心数据，如 id、name、昵称、手机号之类的信息，也就是上面 SQL 语句里的 users 表 

存储用户的一些拓展信息，比如说家庭住址、兴趣爱好、最近一次登录时间之类的，即 users_extent_info 表 

有个子查询，里面针对用户的拓展信息表，即 users_extent_info 查下最近一次登录时间
SELECT COUNT(id)
FROM users
WHERE id IN (
    SELECT user_id
    FROM users_extent_info
    WHERE latest_login_time 
 然后内存里做个小批量，多批次读取数据的操作，比如判断如果在 1000 条以内，那么就一下子读取出来，若超过 1000 条，可通过 LIMIT 语句，每次就从该结果集里查 1000 条数据，查 1000 条就做次批量 PUSH，再查下一波 1000 条。
 就是在千万级数据量大表场景下，上面 SQL 直接轻松跑出来耗时几十 s，不优化不行!
 今天咱们继续来看这个千万级用户场景下的运营系统 SQL 调优案例，上次已经给大家说了一下业务背景 以及 SQL，这个 SQL 就是如下的一个：
SELECT COUNT(id) FROM users WHERE id IN (SELECT user_id FROM 
users_extent_info WHERE latest_login_time 
 系统运行时，先 COUNT 查该结果集有多少数据，再分批查询。然而 COUNT 在千万级大表场景下，都要花几十 s。实际上每个不同的 MySQL 版本都可能会调整生成执行计划的方式。
 通过：
EXPLAIN 
SELECT COUNT(id) 
FROM users 
WHERE id IN (
  SELECT user_id 
  FROM users_extent_info 
  WHERE latest_login_time 
 如下执行计划是为了调优，在测试环境的单表 2 万条数据场景，即使是 5 万条数据，当时这个 SQL 都跑了十多 s，注意执行计划里的数据量 
 执行计划里的第三行 
 先子查询，针对 users_extent_info，使用 idx_login_time 索引，做了 range 类型的索引范围扫描，查出 4561 条数据，没有做额外筛选，所以ﬁltered=100%。
MATERIALIZED：这里把子查询的 4561 条数据代表的结果集进行了物化，物化成了一个临时表，这个临时表物化，一定是会把 4561 条数据临时落到磁盘文件里去的，这过程很慢。
 第二条执行计划 
 针对 users 表做了一个全表扫描，在全表扫描的时候扫出来 49651 条数据，Extra=Using join buﬀer，此处居然在执行 join。
 执行计划里的第一条 
 针对子查询产出的一个物化临时表，即做了个全表查询，把里面的数据都扫描了一遍。
 为何对这临时表进行全表扫描? 让 users 表的每条数据都和物化临时表里的数据进行 join，所以针对 users 表里的每条数据，只能是去全表扫描一遍物化临时表，从物化临时表里确认哪条数据和他匹配，才能筛选出一条结果。
 第二条执行计划的全表扫描结果表明一共扫到 49651 条，但全表扫描过程中，因为和物化临时表执行 join，而物化临时表里就 4561 条数据，所以最终第二条执行计划的ﬁltered=10%，即最终从 users 表里也筛选出 4000 多条数据。
 到底为什么慢 
| 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 |
 先执行了子查询查出 4561 条数据，物化成临时表，接着对 users 主表全表扫描，扫描过程把每条数据都放到物化临时表里做全表扫描，本质在做 join。
 对子查询的结果做了一次物化临时表，落地磁盘，接着还全表扫描 users 表，每条数据居然跑到一个没有索引的物化临时表里，又做了一次全表扫描找匹配的数据。
 对 users 表的全表扫描耗时吗?
 对 users 表的每一条数据跑到物化临时表里做全表扫描耗时吗?
 所以必然非常慢，几乎用不到索引。为什么 MySQL 会这样呢?
 执行完上述 SQL 的 EXPLAIN 命令，看到执行计划之后，再执行：
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 和物化临时表导致的慢题，那怎么优化?
 做个实验 
 执行 
SET optimizer_switch='semijoin=oﬀ'
 关闭半连接优化，再执行 EXPLAIN 发现恢复为正常状态：
 有个 SUBQUERY 子查询，基于 range 方式去扫描索引，搜索出 4561 条数据 

接着有个 PRIMARY 类型主查询，直接基于 id 这个 PRIMARY 主键聚簇索引去执行的搜索 

然后再把这个 SQL 语句真实跑一下看看，性能竟然提升了几十倍，仅 100 多 ms。

所以，其实反而是 MySQL 自动执行的 semi join 半连接优化，导致了极差性能，关闭即可。
 生产环境当然不能随意更改这些设置，于是想了多种办法尝试去修改 SQL 语句的写法，在不影响其语义情况下，尽可能改变 SQL 语句的结构和格式，最终尝试出如下写法：
SELECT COUNT(id)
FROM users
WHERE (
    id IN (
        SELECT user_id
        FROM users_extent_info
        WHERE latest_login_time 
 上述写法下，WHERE 语句的 OR 后面的第二个条件，根本不可能成立，因为没有数据的 latest_login_time

正文完

星哥玩云-微信公众号