一、問(wèn)題復(fù)現(xiàn)

在實(shí)際的軟件系統(tǒng)開發(fā)過(guò)程中，隨著使用的用戶群體越來(lái)越多，表數(shù)據(jù)也會(huì)隨著時(shí)間的推移，單表的數(shù)據(jù)量會(huì)越來(lái)越大。

以訂單表為例，假如每天的訂單量在 4 萬(wàn)左右，那么一個(gè)月的訂單量就是 120 多萬(wàn)，一年就是 1400 多萬(wàn)，隨著年數(shù)的增加和單日下單量的增加，訂單表的數(shù)據(jù)量會(huì)越來(lái)越龐大，訂單數(shù)據(jù)的查詢不會(huì)像最初那樣簡(jiǎn)單快速，如果查詢關(guān)鍵字段沒(méi)有走索引，會(huì)直接影響到用戶體驗(yàn)，甚至?xí)绊懙椒?wù)是否能正常運(yùn)行！

下面我以某個(gè)電商系統(tǒng)的客戶表為例，數(shù)據(jù)庫(kù)是 Mysql，數(shù)據(jù)體量在 100 萬(wàn)以上，詳細(xì)介紹分頁(yè)查詢下，不同階段的查詢效率情況（訂單表的情況也是類似的，只不過(guò)它的數(shù)據(jù)體量比客戶表更大）。

圖片

圖片

下面我們一起來(lái)測(cè)試一下，每次查詢客戶表時(shí)最多返回 100 條數(shù)據(jù)，不同的起始下，數(shù)據(jù)庫(kù)查詢性能的差異。

• 當(dāng)起點(diǎn)位置在 0 的時(shí)候，僅耗時(shí)：18 ms

圖片

• 當(dāng)起點(diǎn)位置在 1000 的時(shí)候，僅耗時(shí)：23 ms

圖片

• 當(dāng)起點(diǎn)位置在 10000 的時(shí)候，僅耗時(shí)：54 ms

圖片

• 當(dāng)起點(diǎn)位置在 100000 的時(shí)候，僅耗時(shí)：268 ms

圖片

• 當(dāng)起點(diǎn)位置在 500000 的時(shí)候，僅耗時(shí)：1.16 s

圖片

• 當(dāng)起點(diǎn)位置在 1000000 的時(shí)候，僅耗時(shí)：2.35 s

圖片

可以非常清晰的看出，隨著起點(diǎn)位置越大，分頁(yè)查詢效率成倍的下降，當(dāng)起點(diǎn)位置在 1000000 以上的時(shí)候，對(duì)于百萬(wàn)級(jí)數(shù)據(jù)體量的單表，查詢耗時(shí)基本上以秒為單位。

而事實(shí)上，一般查詢耗時(shí)超過(guò) 1 秒的 SQL 都被稱為慢 SQL，有的公司運(yùn)維組要求的可能更加嚴(yán)格，比如小編我所在的公司，如果 SQL 的執(zhí)行耗時(shí)超過(guò) 0.2s，也被稱為慢 SQL，必須在限定的時(shí)間內(nèi)盡快優(yōu)化，不然可能會(huì)影響服務(wù)的正常運(yùn)行和用戶體驗(yàn)。

對(duì)于千萬(wàn)級(jí)的單表數(shù)據(jù)查詢，小編我剛剛也使用了一下分頁(yè)查詢，起點(diǎn)位置在 10000000，也截圖給大家看看，查詢耗時(shí)結(jié)果：39 秒！

沒(méi)有接觸過(guò)這么大數(shù)據(jù)體量的同學(xué)，可能多少對(duì)這種查詢結(jié)果會(huì)感到吃驚，事實(shí)上，這還只是數(shù)據(jù)庫(kù)層面的耗時(shí)，還沒(méi)有算后端服務(wù)的處理鏈路時(shí)間，以及返回給前端的數(shù)據(jù)渲染時(shí)間，以百萬(wàn)級(jí)的單表查詢?yōu)槔绻麛?shù)據(jù)庫(kù)查詢耗時(shí) 1 秒，再經(jīng)過(guò)后端的數(shù)據(jù)封裝處理，前端的數(shù)據(jù)渲染處理，以及網(wǎng)絡(luò)傳輸時(shí)間，沒(méi)有異常的情況下，差不多在 3～4 秒之間，可能有些同學(xué)對(duì)這個(gè)請(qǐng)求時(shí)長(zhǎng)數(shù)值還不太敏感。

據(jù)互聯(lián)網(wǎng)軟件用戶體驗(yàn)報(bào)告，當(dāng)平均請(qǐng)求耗時(shí)在1秒之內(nèi)，用戶體驗(yàn)是最佳的，此時(shí)的軟件也是用戶留存度最高的；2 秒之內(nèi)，還勉強(qiáng)過(guò)的去，用戶能接受；當(dāng)超過(guò) 3 秒，體驗(yàn)會(huì)稍差；超過(guò) 5 秒，基本上會(huì)卸載當(dāng)前軟件。

有的公司為了提升用戶體驗(yàn)，會(huì)嚴(yán)格控制請(qǐng)求時(shí)長(zhǎng)，當(dāng)請(qǐng)求時(shí)長(zhǎng)超過(guò) 3 秒，自動(dòng)放棄請(qǐng)求，從而倒逼技術(shù)優(yōu)化調(diào)整 SQL 語(yǔ)句查詢邏輯，甚至調(diào)整后端整體架構(gòu)，比如引入緩存中間件 redis，搜索引擎 elasticSearch 等等。

繼續(xù)回到我們本文所需要探討的問(wèn)題，當(dāng)單表數(shù)據(jù)量到達(dá)百萬(wàn)級(jí)的時(shí)候，查詢效率急劇下降，如何優(yōu)化提升呢？

二、解決方案

下面我們一起來(lái)看看具體的解決辦法。

2.1、方案一：查詢的時(shí)候，只返回主鍵 ID

我們繼續(xù)回到上文給大家介紹的客戶表查詢，將select *改成select id，簡(jiǎn)化返回的字段，我們?cè)賮?lái)觀察一下查詢耗時(shí)。

• 當(dāng)起點(diǎn)位置在 100000 的時(shí)候，僅耗時(shí)：73 ms

圖片

• 當(dāng)起點(diǎn)位置在 500000 的時(shí)候，僅耗時(shí)：274 ms

圖片

• 當(dāng)起點(diǎn)位置在 1000000 的時(shí)候，僅耗時(shí)：471 ms

圖片

可以很清晰的看到，通過(guò)簡(jiǎn)化返回的字段，可以很顯著的成倍提升查詢效率。

實(shí)際的操作思路就是先通過(guò)分頁(yè)查詢滿足條件的主鍵 ID，然后通過(guò)主鍵 ID 查詢部分?jǐn)?shù)據(jù)，可以顯著提升查詢效果。

-- 先分頁(yè)查詢滿足條件的主鍵ID
select id from bizuser order by id limit 100000,10;

-- 再通過(guò)分頁(yè)查詢返回的ID，批量查詢數(shù)據(jù)
select * from bizuser where id in (1,2,3,4,.....);

2.2、方案二：查詢的時(shí)候，通過(guò)主鍵 ID 過(guò)濾

這種方案有一個(gè)要求就是主鍵ID，必須是數(shù)字類型，實(shí)踐的思路就是取上一次查詢結(jié)果的 ID 最大值，作為過(guò)濾條件，而且排序字段必須是主鍵 ID，不然分頁(yè)排序順序會(huì)錯(cuò)亂。

• 查詢 100000～1000100 區(qū)間段的數(shù)據(jù)，僅耗時(shí)：18 ms

圖片

• 查詢 500000～5000100 區(qū)間段的數(shù)據(jù)，僅耗時(shí)：18 ms

圖片

• 查詢 1000000～1000100 區(qū)間段的數(shù)據(jù)，僅耗時(shí)：18 ms

圖片

可以很清晰的看到，帶上主鍵 ID 作為過(guò)濾條件，查詢性能非常的穩(wěn)定，基本上在20 ms內(nèi)可以返回。

這種方案還是非常可行的，如果當(dāng)前業(yè)務(wù)對(duì)排序要求不多，可以采用這種方案，性能也非常杠！

但是如果當(dāng)前業(yè)務(wù)對(duì)排序有要求，比如通過(guò)客戶最后修改時(shí)間、客戶最后下單時(shí)間、客戶最后下單金額等字段來(lái)排序，那么上面介紹的【方案一】，比【方案二】查詢效率更高！

2.3、方案三：采用 elasticSearch 作為搜索引擎

當(dāng)數(shù)據(jù)量越來(lái)越大的時(shí)候，尤其是出現(xiàn)分庫(kù)分表的數(shù)據(jù)庫(kù)，以上通過(guò)主鍵 ID 進(jìn)行過(guò)濾查詢，效果可能會(huì)不盡人意，例如訂單數(shù)據(jù)的查詢，這個(gè)時(shí)候比較好的解決辦法就是將訂單數(shù)據(jù)存儲(chǔ)到 elasticSearch 中，通過(guò) elasticSearch 實(shí)現(xiàn)快速分頁(yè)和搜索，效果提升也是非常明顯。

關(guān)于 elasticSearch 的玩法，之前有給大家介紹過(guò)具體的實(shí)踐，這里不在過(guò)多撰書。

三、小結(jié)

不知道大家有沒(méi)有發(fā)現(xiàn)，上文中介紹的表主鍵 ID 都是數(shù)值類型的，之所以采用數(shù)字類型作為主鍵，是因?yàn)閿?shù)字類型的字段能很好的進(jìn)行排序。

但如果當(dāng)前表的主鍵 ID 是字符串類型，比如 uuid 這種，就沒(méi)辦法實(shí)現(xiàn)這種排序特性，而且搜索性能也非常差，因此不建議大家采用 uuid 作為主鍵ID，具體的數(shù)值類型主鍵 ID 的生成方案有很多種，比如自增、雪花算法等等，都能很好的滿足我們的需求。