MySql大表分頁(附獨門秘技)

你不是說你會Aop嗎?

 

問題背景

MySql(InnoDB)中的訂單表需要按時間順序分頁查詢,且主鍵不是時間維度遞增,訂單表在百萬以上規模,此時如何高效地實現該需求?

註:本文並非主要講解如何建立索引,以下的分析均建立在有合適的索引的前提下

初步方案1

眾所周知,MySql中,有一個limit offset, pageSize的用法,可以實現分頁查詢

select * from order where user_id = xxx and 【其它業務條件】 order by created_time, id limit offset, pageSize

因為created_time可能重複,所以order by時應加上id,保證順序的確定性

點評

該方案在表規模較小的時候,不會暴露出問題,當order表增長到十萬級,並且查詢後面幾頁的時候,執行速度明顯變慢,可能降到100ms的量級,如果數據量增長到百萬級,則耗時達到秒級,如果增長到千萬級,那耗時就變得完全不可接受了(曾排查過這樣的線上慢SQL)

 

 深入分析

方案1為啥在大表中表現這麼差呢?我們可以來揣測一下MySql是怎麼執行這個查詢的

假設我們在user_id,created_time,以及【其它業務條件】建立了聯合索引,當我要查找第100000條到100049條的記錄時,因為MySql的索引是b+ tree結構,不像數組可以隨機定位到第N條記錄,它需要花不小的成本去找到N的位置,N越大,成本越大

拋開b+ tree的細節不講,我們還可以藉助統計表記錄總數的SQL來理解

select count(1) from order

如果能非常高效地定位第N條記錄,那麼上述統計也能非常高效的執行,但實際上,在大表中統計記錄總條數,也是非常慢的(本文是在InnoDB的場景下)

方案1低效的根本原因在於:定位到offset的成本過高,未能充分利用索引的有序性

 

方案2

索引(b+ tree)的特點在於,數據是有序的,雖然找到第N條記錄的效率比較低,但找到某一條數據在索引中的位置,其效率是很高的(索引本來就是解決這個問題的)

我們換一種思路,每次取50條記錄,第一次取的時候,指定從上次結束的位置繼續往後取50條,這樣,我們便可以利用上索引的有序性了

我們先看一個以id為序,進行分頁查詢的例子

select * from order where id > 'pre max id' order by id limit 50

第一次查詢不用帶條件,後續查詢則傳入前一次查詢的最大id,簡單分析可知,MySql在執行時,先定位到pre max id的位置(id是有序的,定位非常快),然後從這往後取50條記錄即可,整個過程非常高效

 

我們回到最開始的問題,「按時間順序分頁查詢,且主鍵不是時間維度遞增」,此時我們不能用id作為分頁的條件,因為按它去分頁,便不是按時間順序了,但也不能直接把id換成時間,因為時間可能會重複,我們來分析一下

id username created_time
xxx zhangsan 2019-01-01
ddd zhangsan 2019-02-03
yyy zhangsan 2019-02-03
abc zhangsan 2019-02-05
aaa zhangsan 2020-08-01

 

 

 

 

 

 

 

 

假如前一次分頁的最後一條記錄為id=ddd的這條(created_time為2019-02-03),下一次查詢使用created_time>2019-02-03作為條件時,則會把id=yyy的這條記錄漏掉,如果換成created_time>=2019-02-03也不行,id=ddd的這條記錄就又被查出來了

對於這個數據遺漏或重複的問題,我看到一種解決方案是這樣的:

分三種情況進行查詢

  1. 首次查詢,created_time>=’xxxx-xx-xx’,如果不要求以某時間開始,則無條件
    select * from order where user_id = xxx and 【其它業務條件】 and created_time >= 'xxxx-xx-xx' order by created_time, id limit pageSize
  2. 如果上次查詢的記錄條數等於pageSize,則用created_time和id的組合條件來查詢,為了防止created_time在邊界位置發生重複時漏掉數據
    select * from order where user_id = xxx and 【其它業務條件】 and created_time = 'created_time of latest recored' and id > 'id of latest recored' order by created_time, id limit pageSize
  3. 如果上次查詢的記錄數小於pageSize,並且上次查詢是第二種查詢,則僅用created_time來查詢,
    select * from order where user_id = xxx and 【其它業務條件】 and created_time > 'created_time of latest recored' order by created_time, id limit pageSize 
注意:

created_time不能為null,否=和>會返回null,導致對應結果查不出來,如果存在為null的情況,則需要對部分查詢把=和>分別改為is null和is not null來查詢

點評

上述方法確實可以解決漏掉數據或重複的問題,並且也有着不錯的性能,但缺點也比較明顯,查詢過於複雜,得分情況執行不同的SQL,並且分頁不穩定,中間查詢出來的記錄數可能小於pageSize(如果沒有重複項,那會多出一倍的結果為空的查詢),實際上後面還有數據

 

進一步深入分析

我嘗試在網上找過資料,只找到了以id為分頁順序,然後用id>’pre max id’這種方式來查,而我們要以可重複的created_time為分頁順序,如何寫出簡潔高效的SQL呢?

分佈式任務調度平台 → XXL-JOB 實戰

 

如果要成為一個優秀的程序員,我覺得分析&解決新問題的能力,是必不可少的,即使在網上能找到解決方案,優秀的分析能力也有助於借鑒並結合自己的場景,優化出更好的個性化方案。

 

我們在(user_id,created_time)建立了索引,並且我們知道InnoDB的輔助索引是包含了主鍵的,且主鍵一定不會重複,這意味着在索引上,每條記錄的順序是完全確定的,不存在重複的情況

我們要分頁的順序跟此索引的順序是吻合的,只需要沿着索引,一批一批地取數據就可以了,這是一個對索引很直接的利用,為什麼現在我沒辦法做到?

如果我是MySql的設計人員,針對這種很常見很直接的需求,我怎麼去提供支持?還是說不支持?

我舉一個例子,像java中的基於排序的TreeSet,我猜它一定有floor和ceiling這樣的方法(返回Set中,大於或小於指定元素的第一個元素),這是基於排序的數據結構該有的東西,如果它沒有,那早被人噴了然後加上去了

回到索引的話題,這種直接的需求,它應該支持,否則說不過去,現在的問題變成了:用什麼語法來,來實現在組合索引上,基於組合(user_id,created_time,id的組合)順序的遍歷?

此時腦海里便回想起以前用過的(a,b) in ((1,2),(3,4),(7,4))這樣的組合寫法,然後猜測它也支持大於小於這類比較,跑去MySql中驗證一下:

select (3,7)>(3,7),    (3,6)>(3,7),    (3,8)>(3,7),    (4,7)>(3,7),    (4,2)>(3,7);
返回:
0    0    1    1    1

如此一來,這問題就變得和id>’pre max id’這種一樣簡單了。

這種寫法在官方文檔中也找到了對應的資料,官方稱這類運算為「行比較」(row comparisons)

https://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#operator_greater-than

看到這裡,也許你跟我當時一樣,即開心又興奮,一個完美的方案就在眼前,然而MySql優化器沒有我們想像的聰明,在「行比較」面前,就變成了二傻子,不能很好地使用索引了

此時我又回過頭去試驗了一下「行比較」對應的等價寫法

(a,b)>(x,y)
等價於
a>x or (a=x and b>y)

發現這種看似很複雜且還有or的寫法,竟然能很好地使用索引,效率非常高,即使像(a,b,c)>(x,y,z),改成很複雜的等價寫法:

a>x or (a=x and (b>y or (b=y and c>z)))

也能很好地使用索引,此時真不知道該誇它還是罵它,唉

關於「行比較」的索引選擇,在官網找到這樣一份資料,文中說索引覆蓋不到時,建議拆開成普通寫法,這樣看來,也許人家是有什麼苦衷吧

https://dev.mysql.com/doc/refman/5.7/en/row-constructor-optimization.html

 方案3

由於有了a>x or (a=x and b>y)這種等價於組合比較的語法,且能正確地使用索引,所以可以寫出高效且還算簡潔的SQL

select * from order 

where user_id = xxx and 【其它業務條件】 and (created_time > 'created_time of latest recode' or (created_time = 'created_time of latest recode' and id > 'id of latest recode'))

order by created_time, id limit pageSize

此方式跟以id為序的分頁查詢是一樣的,首次查詢去掉組合條件即可,代碼略顯複雜,好在可以利用上組合索引,十分高效,耗時穩定,不會因為遍歷到末尾而性能降低

遺憾地是,最優雅的方式卻撞見個二傻子優化器,按理說用他們支持的特定語法(變化範圍更小,模式更固定)去精確地表達查詢需求,應該更容易被優化器識別出來並用最優方案去執行才說得通,結果卻不如人意

希望以後能MySql更好地支持「行比較」吧(8.0.19仍存在問題)

 注意:

這裡也不允許created_time為null,因為null值參與>和=運算,結果一律為null,即條件不成立,相應結果查不出來。

如果存在為null的情況,則要作一些調整,如果前一批數據的最後一條記錄的created_time為null(null在索引中被視作極小值),則可以這樣改:

(created_time is not null or (created_time is null and id > 'id of latest recode'))

仍舊可以走索引,實現高效分頁查詢

總結

方案1在小表的情況下,簡單方便,只用傳頁碼和頁大小即可,還可以隨機跳到指定頁,具有一定優勢

方案2和方案3在大表的情況下,有着優異的性能,以及穩定性,缺點是不能隨機地跳轉頁面,需要傳入上一頁的排序字段。這個弊端在一定程度上可以規避,比如現在很多分頁都是一頁一頁地往下翻,比如微博、朋友圈動態等,或者是分批處理全表數據,不需要隨機跳轉

細心的同學可能發現,where條件里還有【其它業務條件】,這樣還能正常走索引嗎?是否會發生全表掃描?這個問題其實是可以規避的,有空再寫一篇執行計劃並不完全可靠的案例。

註:執行計劃有時不能正確地反映實際執行效果,所以我沒有貼執行計劃;我使用的MySql版本為5.7.23和8.0.19

題外話

方案3的寫法是我自己琢磨出來的,在網上也沒找到類似的資料,算獨門秘技吧,除此之外,我覺得同樣很有價值的是【進一步深入分析】中的思考過程,如果養成這種思考習慣,有利於創新,去解決別人沒遇到過的問題,在未知的領域,知道該從哪個方向去尋找答案;或者找到新的方法更好地去解決舊問題。

如果本文有幫助到你,或者覺得有價值,麻煩點個贊,這樣我會更有動力去更多地分享自己的經驗

 

轉載請註明出處及作者(https://www.cnblogs.com/trytocatch/p/mysql-page-query.html by trytocatch)

 

MySql大表分頁(附獨門秘技)
免責聲明:非本網註明原創的信息,皆為程序自動獲取互聯網,目的在於傳遞更多信息,並不代表本網贊同其觀點和對其真實性負責;如此頁面有侵犯到您的權益,請給站長發送郵件,並提供相關證明(版權證明、身份證正反面、侵權鏈接),站長將在收到郵件12小時內刪除。

JVM系列之:JIT中的Virtual Call接口