千萬(wàn)級(jí)用戶(hù)系統(tǒng)SQL調(diào)優(yōu)的示例分析

這篇文章主要介紹了千萬(wàn)級(jí)用戶(hù)系統(tǒng)SQL調(diào)優(yōu)的示例分析，具有一定借鑒價(jià)值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著大家一起了解一下。

用戶(hù)日活百萬(wàn)級(jí)，注冊(cè)用戶(hù)千萬(wàn)級(jí)，而且若還沒(méi)有進(jìn)行分庫(kù)分表，則該DB里的用戶(hù)表可能就一張，單表上千萬(wàn)的用戶(hù)數(shù)據(jù)。

某系統(tǒng)專(zhuān)門(mén)通過(guò)各種條件篩選大量用戶(hù)，接著對(duì)那些用戶(hù)去推送一些消息：

• 一些促銷(xiāo)活動(dòng)消息

• 讓你辦會(huì)員卡的消息

• 告訴你有一個(gè)特價(jià)商品的消息

通過(guò)一些條件篩選出大量用戶(hù)，針對(duì)這些用戶(hù)做推送，該過(guò)程較耗時(shí)-篩選用戶(hù)過(guò)程。

用戶(hù)日活百萬(wàn)級(jí)，注冊(cè)用戶(hù)千萬(wàn)級(jí)，而且若還沒(méi)有進(jìn)行分庫(kù)分表，則該DB里的用戶(hù)表可能就一張，單表上千萬(wàn)的用戶(hù)數(shù)據(jù)。

對(duì)運(yùn)營(yíng)系統(tǒng)篩選用戶(hù)的SQL：

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

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

• 存儲(chǔ)用戶(hù)的核心數(shù)據(jù)，如id、name、昵稱(chēng)、手機(jī)號(hào)之類(lèi)的信息，也就是上面SQL語(yǔ)句里的users表

• 存儲(chǔ)用戶(hù)的一些拓展信息，比如說(shuō)家庭住址、興趣愛(ài)好、最近一次登錄時(shí)間之類(lèi)的，即users_extent_info表

有個(gè)子查詢(xún)，里面針對(duì)用戶(hù)的拓展信息表，即users_extent_info查下最近一次登錄時(shí)間<某個(gè)時(shí)間點(diǎn)的用戶(hù)，可以查詢(xún)最近才登錄過(guò)的用戶(hù)，也可查詢(xún)很長(zhǎng)時(shí)間未登錄的用戶(hù)，然后給他們發(fā)push，無(wú)論哪種場(chǎng)景， 該SQL都適用。

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

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

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

就是在千萬(wàn)級(jí)數(shù)據(jù)量大表場(chǎng)景下，上面SQL直接輕松跑出來(lái)耗時(shí)幾十s，不優(yōu)化不行!

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

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

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

通過(guò)：

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

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

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

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

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

第二條執(zhí)行計(jì)劃

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

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

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

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

第二條執(zhí)行計(jì)劃的全表掃描結(jié)果表明一共掃到49651條，但全表掃描過(guò)程中，因?yàn)楹臀锘R時(shí)表執(zhí)行join，而物化臨時(shí)表里就4561條數(shù)據(jù)，所以最終第二條執(zhí)行計(jì)劃的?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í)行了子查詢(xún)查出4561條數(shù)據(jù)，物化成臨時(shí)表，接著對(duì)users主表全表掃描，掃描過(guò)程把每條數(shù)據(jù)都放到物化臨時(shí)表里做全表掃描，本質(zhì)在做join。

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

對(duì)users表的全表掃描耗時(shí)嗎?

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

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

執(zhí)行完上述SQL的EXPLAIN命令，看到執(zhí)行計(jì)劃之后，再執(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í)行計(jì)劃的時(shí)候，自動(dòng)就把一個(gè)普通IN子句，“優(yōu)化”成基于semi join來(lái)進(jìn)行IN+子查詢(xún)的操作。那對(duì)users表不是全表掃描了嗎?對(duì)users表里每條數(shù)據(jù)，去對(duì)物化臨時(shí)表全表掃描做semi join，無(wú)需將users表里的數(shù)據(jù)真的跟物化臨時(shí)表里的數(shù)據(jù)join。只要users表里的一條數(shù)據(jù)，在物化臨時(shí)表能找到匹配數(shù)據(jù)，則users表里的數(shù)據(jù)就會(huì)返回，這就是semi join，用來(lái)做篩選。

所以就是semi join和物化臨時(shí)表導(dǎo)致的慢題，那怎么優(yōu)化?

做個(gè)實(shí)驗(yàn)

執(zhí)行：

SET optimizer_switch='semijoin=o?'

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

有個(gè)SUBQUERY子查詢(xún)，基于range方式去掃描索引，搜索出4561條數(shù)據(jù)
接著有個(gè)PRIMARY類(lèi)型主查詢(xún)，直接基于id這個(gè)PRIMARY主鍵聚簇索引去執(zhí)行的搜索
然后再把這個(gè)SQL語(yǔ)句真實(shí)跑一下看看，性能竟然提升了幾十倍，僅100多ms。
所以，其實(shí)反而是MySQL自動(dòng)執(zhí)行的semi join半連接優(yōu)化，導(dǎo)致了極差性能，關(guān)閉即可。

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

最終嘗試出如下寫(xiě)法：

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

上述寫(xiě)法下，WHERE語(yǔ)句的OR后面的第二個(gè)條件，根本不可能成立，因?yàn)闆](méi)有數(shù)據(jù)的latest_login_time<-1，所以那不會(huì)影響SQL業(yè)務(wù)語(yǔ)義，但改變SQL后，執(zhí)行計(jì)劃也會(huì)變，就沒(méi)有再semi join優(yōu)化了，而是常規(guī)地用了子查詢(xún)，主查詢(xún)也是基于索引，同樣達(dá)到幾百ms 性能優(yōu)化。

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

感謝你能夠認(rèn)真閱讀完這篇文章，希望小編分享的“千萬(wàn)級(jí)用戶(hù)系統(tǒng)SQL調(diào)優(yōu)的示例分析”這篇文章對(duì)大家有幫助，同時(shí)也希望大家多多支持億速云，關(guān)注億速云行業(yè)資訊頻道，更多相關(guān)知識(shí)等著你來(lái)學(xué)習(xí)!