千萬級用戶系統(tǒng)的SQL調(diào)優(yōu)實戰(zhàn)

千萬級用戶系統(tǒng)的SQL調(diào)優(yōu)實戰(zhàn)

某系統(tǒng)專門通過各種條件篩選大量用戶，接著對那些用戶去推送一些消息：

一些促銷活動消息

讓你辦會員卡的消息

告訴你有一個特價商品的消息

通過一些條件篩選出大量用戶，針對這些用戶做推送，該過程較耗時-篩選用戶過程。

用戶日活百萬級，注冊用戶千萬級，而且若還沒有進行分庫分表，則該DB里的用戶表可能就一張，單表上千萬的用戶數(shù)據(jù)。

對運營系統(tǒng)篩選用戶的SQL：

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

一般存儲用戶數(shù)據(jù)的表會分為兩張表：

存儲用戶的核心數(shù)據(jù)，如id、name、昵稱、手機號之類的信息，也就是上面SQL語句里的users表

存儲用戶的一些拓展信息，比如說家庭住址、興趣愛好、最近一次登錄時間之類的，即users_extent_info表

有個子查詢，里面針對用戶的拓展信息表，即users_extent_info查下

最近一次登錄時間＜某個時間點

的用戶，可以查詢最近才登錄過的用戶，也可查詢很長時間未登錄的用戶，然后給他們發(fā)push，無論哪種場景， 該SQL都適用。

然后在外層查詢，用id IN子句查詢 id 在子查詢結(jié)果范圍里的users表的所有數(shù)據(jù)，此時該SQL突然會查出很多數(shù)據(jù)，可能幾千、幾萬、幾十萬，所以執(zhí)行此類SQL前，都會先執(zhí)行count：

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

然后內(nèi)存里做個小批量，多批次讀取數(shù)據(jù)的操作，比如判斷如果在1000條以內(nèi)，那么就一下子讀取出來，若超過1000條，可通過LIMIT語句，每次就從該結(jié)果集里查1000條數(shù)據(jù)，查1000條就做次批量PUSH，再查下一波1000條。

就是在千萬級數(shù)據(jù)量大表場景下，上面SQL直接輕松跑出來耗時幾十s，不優(yōu)化不行！

今天咱們繼續(xù)來看這個千萬級用戶場景下的運營系統(tǒng)SQL調(diào)優(yōu)案例，上次已經(jīng)給大家說了一下業(yè)務背景 以及SQL，這個SQL就是如下的一個：

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

系統(tǒng)運行時，先COUNT查該結(jié)果集有多少數(shù)據(jù)，再分批查詢。然而COUNT在千萬級大表場景下，都要花幾十s。實際上每個不同的MySQL版本都可能會調(diào)整生成執(zhí)行計劃的方式。

通過：

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

如下執(zhí)行計劃是為了調(diào)優(yōu)，在測試環(huán)境的單表2萬條數(shù)據(jù)場景，即使是5萬條數(shù)據(jù)，當時這個SQL都跑了十多s，注意執(zhí)行計劃里的數(shù)據(jù)量

執(zhí)行計劃里的第三行

先子查詢，針對users_extent_info，使用idx_login_time索引，做了range類型的索引范圍掃描，查出4561條數(shù)據(jù)，沒有做額外篩選，所以?ltered=100%。

MATERIALIZED：這里把子查詢的4561條數(shù)據(jù)代表的結(jié)果集進行了物化，物化成了一個臨時表，這個臨時表物化，一定是會把4561條數(shù)據(jù)臨時落到磁盤文件里去的，這過程很慢。

第二條執(zhí)行計劃

針對users表做了一個全表掃描，在全表掃描的時候掃出來49651條數(shù)據(jù)，Extra=Using join bu?er，此處居然在執(zhí)行join。

執(zhí)行計劃里的第一條

針對子查詢產(chǎn)出的一個物化臨時表，即做了個全表查詢，把里面的數(shù)據(jù)都掃描了一遍。

為何對這臨時表進行全表掃描？讓users表的每條數(shù)據(jù)都和物化臨時表里的數(shù)據(jù)進行join，所以針對users表里的每條數(shù)據(jù)，只能是去全表掃描一遍物化臨時表，從物化臨時表里確認哪條數(shù)據(jù)和他匹配，才能篩選出一條結(jié)果。

第二條執(zhí)行計劃的全表掃描結(jié)果表明一共掃到49651條，但全表掃描過程中，因為和物化臨時表執(zhí)行join，而物化臨時表里就4561條數(shù)據(jù)，所以最終第二條執(zhí)行計劃的?ltered=10%，即最終從users表里也篩選出4000多條數(shù)據(jù)。

到底為什么慢

| 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 |

先執(zhí)行了子查詢查出4561條數(shù)據(jù)，物化成臨時表，接著對users主表全表掃描，掃描過程把每條數(shù)據(jù)都放到物化臨時表里做全表掃描，本質(zhì)在做join。

對子查詢的結(jié)果做了一次物化臨時表，落地磁盤，接著還全表掃描users表，每條數(shù)據(jù)居然跑到一個沒有索引的物化臨時表里，又做了一次全表掃描找匹配的數(shù)據(jù)。

對users表的全表掃描耗時嗎？

對users表的每一條數(shù)據(jù)跑到物化臨時表里做全表掃描耗時嗎？

所以必然非常慢，幾乎用不到索引。為什么MySQL會這樣呢？

執(zhí)行完上述SQL的EXPLAIN命令，看到執(zhí)行計劃之后，再執(zhí)行：

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在這里，生成執(zhí)行計劃的時候，自動就把一個普通IN子句，“優(yōu)化”成基于semi join來進行IN+子查詢的操作。那對users表不是全表掃描了嗎？對users表里每條數(shù)據(jù)，去對物化臨時表全表掃描做semi join，無需將users表里的數(shù)據(jù)真的跟物化臨時表里的數(shù)據(jù)join。只要users表里的一條數(shù)據(jù)，在物化臨時表能找到匹配數(shù)據(jù)，則users表里的數(shù)據(jù)就會返回，這就是semi join，用來做篩選。

所以就是semi join和物化臨時表導致的慢題，那怎么優(yōu)化？

做個實驗

執(zhí)行

SET optimizer_switch='semijoin=o?'

關(guān)閉半連接優(yōu)化，再執(zhí)行EXPLAIN發(fā)現(xiàn)恢復為正常狀態(tài)：

有個SUBQUERY子查詢，基于range方式去掃描索引，搜索出4561條數(shù)據(jù)

接著有個PRIMARY類型主查詢，直接基于id這個PRIMARY主鍵聚簇索引去執(zhí)行的搜索

然后再把這個SQL語句真實跑一下看看，性能竟然提升了幾十倍，僅100多ms。

所以，其實反而是MySQL自動執(zhí)行的semi join半連接優(yōu)化，導致了極差性能，關(guān)閉即可。

生產(chǎn)環(huán)境當然不能隨意更改這些設(shè)置，于是想了多種辦法嘗試去修改SQL語句的寫法，在不影響其語義情況下，盡可能改變SQL語句的結(jié)構(gòu)和格式，最終嘗試出如下寫法：

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后面的第二個條件，根本不可能成立，因為沒有數(shù)據(jù)的latest_login_time＜-1，所以那不會影響SQL業(yè)務語義，但改變SQL后，執(zhí)行計劃也會變，就沒有再semi join優(yōu)化了，而是常規(guī)地用了子查詢，主查詢也是基于索引，同樣達到幾百ms 性能優(yōu)化。

所以最核心的，還是看懂SQL執(zhí)行計劃，分析慢的原因，盡量避免全表掃描，務必用上索引。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔相應法律責任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實后本網(wǎng)站將在24小時內(nèi)刪除侵權(quán)內(nèi)容。