千萬級用戶系統的 SQL 調優實戰

1 案例引入

某系統需要對特定的大量用戶推送一些消息：

促銷活動
讓你辦卡
有個特價商品

而首先要通過一些條件篩選出這些用戶，而該過程很耗時！

日活百萬，註冊用戶千萬，而且若還未分庫分表，則該 DB 裏的用戶表可能就一張，單表就上千萬的用戶數據。對該運營系統篩選用戶的 SQL：

SELECT id, name 
FROM users 
WHERE id IN (
  SELECT user_id 
  FROM users_extent_info
  # 查詢最近登錄過的用戶  WHERE latest_login_time < xx)

一般存儲用戶數據的表會分爲兩張表：

存儲用戶的核心數據，如 id、name、暱稱、手機號，即 users 表
存儲用戶的一些拓展信息，如家庭住址、興趣愛好、最近一次登錄時間，即 users_extent_info 表

然後在外層查詢，用 IN 子句查詢 id 在子查詢結果範圍裏的 users 表數據，此時該 SQL 突然會查出很多數據，可能幾千、幾萬、幾十萬，所以執行此類 SQL 前，都會先執行 count！

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

再在內存裏再做小批量的批次讀數據操作，比如判斷：

若結果在 1k 條內，就一下子讀出來
若超過 1k 條，可通過 Limit 語句，每次就從該結果集裏查 1k 條，查 1000 條就做一次批量的消息 Push，再查下一批次的 1k 條數據

但在千萬級數據量的大表下，上面 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 |

先子查詢，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 多條數據。

2 到底爲什麼慢？

先執行了子查詢查出 4561 條數據，物化成臨時表，接着對 users 主表全表掃描，掃描過程把每條數據都放到物化臨時表裏做全表掃描，本質就是在 join。

對子查詢的結果做了一次物化臨時表，落地磁盤，接着還全表掃描 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 發現恢復爲正常狀態：

有個 SUBQUERY 子查詢，基於 range 方式掃描索引，搜索出 4561 條數據
接着有個 PRIMARY 類型主查詢，直接基於 id 這個 PRIMARY 主鍵索引搜索
然後再把這個 SQL 語句真實跑一下看看，性能竟然提升幾十倍，僅 100 多 ms。

所以，其實反而是 MySQL 自動執行的 semi join 半連接優化，導致極差性能，關閉之即可。

生產環境當然不能隨意更改這些設置，於是想了多種辦法嘗試去修改 SQL 語句的寫法，在不影響其語義情況下，儘可能改變 SQL 語句的結構和格式，最終嘗試出如下寫法：

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

上述寫法下，WHERE 語句的 OR 後面的第二個條件，業務上根本不可能成立，所以不會影響 SQL 的業務語義，但改變 SQL 後，執行計劃也會變，就不會再 semi join 優化了，而是常規地用了子查詢，主查詢也是基於索引。

所以最核心的，還是看懂 SQL 執行計劃，分析慢的原因，儘量避免全表掃描，用上索引！

程序員追風 專注於分享 Java 各類學習筆記、面試題以及 IT 類資訊。

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/CmHahpC0H8gULiSXW4frcg

1 案例引入

2 到底爲什麼慢？

3 show warnings

4 做個實驗

猜你喜歡