千萬級用戶系統SQL調優實戰分享
用戶日活百萬級，註冊用戶千萬級，而且若還沒有進行分庫分表，則該DB裡的用戶表可能就一張，單表上千萬的用戶數據。
某系統專門通過各種條件篩選大量用戶，接著對那些用戶去推送一些消息：
- 一些促銷活動消息
- 讓你辦會員卡的消息
- 告訴你有一個特價商品的消息
通過一些條件篩選出大量用戶，針對這些用戶做推送，該過程較耗時-篩選用戶過程。
用戶日活百萬級，註冊用戶千萬級，而且若還沒有進行分庫分表，則該DB裡的用戶表可能就一張，單表上千萬的用戶數據。
對運營系統篩選用戶的SQL：
SELECT id, name FROM users WHERE id IN ( SELECT user_id FROM users_extent_info WHERE latest_login_time < xxxxx )
一般存儲用戶數據的表會分為兩張表：
- 存儲用戶的核心數據，如id、
name、昵稱、手機號之類的信息，也就是上面SQL語句裡的users表 - 存儲用戶的一些拓展信息，比如說傢庭住址、興趣愛好、最近一次登錄時間之類的，即
users_extent_info表
有個子查詢，裡面針對用戶的拓展信息表，即users_extent_info查下最近一次登錄時間<某個時間點的用戶，可以查詢最近才登錄過的用戶，也可查詢很長時間未登錄的用戶，然後給他們發push，無論哪種場景， 該SQL都適用。
然後在外層查詢，用id 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 )
然後內存裡做個小批量，多批次讀取數據的操作，比如判斷如果在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 < xxxxx)
系統運行時，先COUNT查該結果集有多少數據，再分批查詢。然而COUNT在千萬級大表場景下，都要花幾十s。實際上每個不同的MySQL版本都可能會調整生成執行計劃的方式。
通過：
EXPLAIN SELECT COUNT(id) FROM users WHERE id IN ( SELECT user_id FROM users_extent_info WHERE latest_login_time < xxxxx )
如下執行計劃是為瞭調優，在測試環境的單表2萬條數據場景，即使是5萬條數據，當時這個SQL都跑瞭十多s，註意執行計劃裡的數據量
執行計劃裡的第三行
先子查詢，針對users_extent_info，使用idx_login_time索引，做瞭range類型的索引范圍掃描，查出4561條數據，沒有做額外篩選，所以filtered=100%。
MATERIALIZED：這裡把子查詢的4561條數據代表的結果集進行瞭物化，物化成瞭一個臨時表，這個臨時表物化，一定是會把4561條數據臨時落到磁盤文件裡去的，這過程很慢。
第二條執行計劃
針對users表做瞭一個全表掃描，在全表掃描的時候掃出來49651條數據，Extra=Using join buffer，此處居然在執行join。
執行計劃裡的第一條
針對子查詢產出的一個物化臨時表，即做瞭個全表查詢，把裡面的數據都掃描瞭一遍。
為何對這臨時表進行全表掃描?讓users表的每條數據都和物化臨時表裡的數據進行join，所以針對users表裡的每條數據，隻能是去全表掃描一遍物化臨時表，從物化臨時表裡確認哪條數據和他匹配，才能篩選出一條結果。
第二條執行計劃的全表掃描結果表明一共掃到49651條，但全表掃描過程中，因為和物化臨時表執行join，而物化臨時表裡就4561條數據，所以最終第二條執行計劃的filtered=10%，即最終從users表裡也篩選出4000多條數據。
到底為什麼慢
| id | select_type | table | type | key | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+--- | 1 | SIMPLE | | ALL | NULL | NULL | 100.00 | NULL | | 1 | SIMPLE | users | ALL | NULL | 49651 | 10.00 | Using where; Using join buffer(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=off'
關閉半連接優化，再執行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 < xxxxx) OR id IN ( SELECT user_id FROM users_extent_info WHERE latest_login_time < -1) )
上述寫法下，WHERE語句的OR後面的第二個條件，根本不可能成立，因為沒有數據的latest_login_time<-1，所以那不會影響SQL業務語義，但改變SQL後，執行計劃也會變，就沒有再semi join優化瞭，而是常規地用瞭子查詢，主查詢也是基於索引，同樣達到幾百ms 性能優化。
所以最核心的，還是看懂SQL執行計劃，分析慢的原因，盡量避免全表掃描，務必用上索引。
到此這篇關於千萬級用戶系統SQL調優實戰分享的文章就介紹到這瞭,更多相關SQL調優實戰內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！