為什麼mysql最好不要只用limit做分頁查詢?

語言: CN / TW / HK

在專案中遇到的真實問題,以及我的解決方案,部分資料做了脫敏處理。

問題

最近在做專案時需要寫sql做單表查詢,每次查出來的資料有幾百萬甚至上千萬條,公司用的資料庫是MySQL5.7,做了分庫分表,部分資料庫設定了查詢超時時間,比如查詢超過15s直接報超時錯誤,如下圖:

image.png

可以通過show variables like 'max_statement_time';命令檢視資料庫超時時間(單位:毫秒):

image.png

方案1

嘗試使用索引加速sql,從下圖可以看到該sql已經走了主鍵索引,但還是需要掃描150萬行,無法從這方面進行優化。

image.png

方案2

嘗試使用limit語句進行分頁查詢,語句為:

SELECT * FROM table WHERE user_id = 123456789 limit 0, 300000;

像這樣每次查30萬條肯定就不會超時了,但這會引出另一個問題--查詢耗時與起始位置成正比,如下圖:

image.png

第二條語句實際上查了60w條記錄,不過把前30w條丟棄了,只返回後30w條,所以耗時會遞增,最終仍會超時。

方案3

使用指定主鍵範圍的分頁查詢,主要思想是將條件語句改為如下形式(其中id為自增主鍵):

WHERE user_id = 123456789 AND id > 0 LIMIT 300000; WHERE user_id = 123456789 AND id > (上次查詢結果中最後一條記錄的id值) LIMIT 300000;

也可以將上述語句簡化成如下形式(注意:帶了子查詢會變慢):

WHERE user_id = 123456789 AND id >= (SELECT id FROM table LIMIT 300000, 1) limit 300000;

每次查詢只需要修改子查詢limit語句的起始位置即可,但我發現表中並沒有自增主鍵id這個欄位,表內主鍵是fs_id,而且是無序的。

這個方案還是不行,組內高工都感覺無解了。

方案4

既然fs_id是無序的,那麼就給它排序吧,加了個ORDER BY fs_id,最終解決方案如下: WHERE user_id = 123456789 AND fs_id > 0 ORDER BY fs_id LIMIT 300000; WHERE user_id = 123456789 AND fs_id > (上次查詢結果中最後一條記錄的id值) ORDER BY fs_id LIMIT 300000;

效果如下圖:

image.png

查詢時間非常穩定,每條查詢的fs_id都大於上次查詢結果中最後一條記錄的fs_id值。正常查30w條需要4.06s,排序後查30w條需要6.48s,確實慢了許多,但總算能把問題解決了。目前程式碼還在線上跑著哈哈,如果有更好的解決方案可以在評論區討論喲。