前言
在實際開發(fā)中分頁查詢是最常用的場景之一，但也通常也是最容易出問題的地方。DBA的同事給出的解決方法就是加“索引/組合索引”，例如在name,create_time,status等字段上加組合索引，這樣根據(jù)這些條件查詢的時候能夠有效的利用索引，性能迅速的就提高了。
以上做法對 select * from table limit 0,10 這個沒有問題，但是當 limit 1000000,10 的時候數(shù)據(jù)讀取就很慢了。我只想要10條數(shù)據(jù)但是每次查詢都掃描了100多萬行，在高頻訪問下堵的死死的。那么有什么辦法解決這個問題呢？
方案1—最大ID
前提：數(shù)據(jù)庫主鍵ID為自增
正常分頁SQL語句為：
select id,name,content,create_time from users order by id asc limit 1000000,10
優(yōu)化后SQL語句為：
select id,name,content,create_time from users where id > 1000000 order by id asc limit 10
看到這里大家應該明白了吧？進行查詢的時候可以將上一頁的最大值ID當成參數(shù)作為查詢條件的，那么當次查詢只需要掃描最大ID開始的20行數(shù)據(jù)即可。
方案2—入庫時間
那么有的同學會問如果設計的表主鍵不是自增又該怎么辦？這里還有一種方法根據(jù)數(shù)據(jù)的入庫時間，具體方法和上面雷同。
select id,name,content,create_time from users where id > '2020-01-01 12:29:23' order by id asc limit 10
這樣查詢的時間基本固定，并且也不會隨著數(shù)據(jù)量的增長而發(fā)生變化。
測評
如下圖所示，小編準備了300w+的數(shù)據(jù)，這張表共有28個字段。
正常分頁所需時長：
從200w開始獲取10條數(shù)據(jù)共花費12s多。
根據(jù)入庫時間進行分頁：
這里的查詢時間是上面SQL查詢出來結(jié)果的create_time，可以看到花費了2s多。性能提升了約6倍！
備注：create_time 字段沒有增加索引。
總結(jié)
當一個數(shù)據(jù)庫表過于龐大，LIMIT offset, length中的offset值過大，則SQL查詢語句會非常緩慢，你需增加order by，并且order by字段需要建立索引。
如果使用子查詢?nèi)?yōu)化LIMIT的話，則子查詢必須是連續(xù)的，某種意義來講，子查詢不應該有where條件，where會過濾數(shù)據(jù)，使數(shù)據(jù)失去連續(xù)性。
如果你查詢的記錄比較大，并且數(shù)據(jù)傳輸量比較大，比如包含了text類型的field，則可以通過建立子查詢。
SELECT id,title,content FROM items WHERE id IN (SELECT id FROM items ORDER BY id limit 900000, 10);
如果limit語句的offset較大，你可以通過傳遞pk鍵值來減小offset = 0，這個主鍵最好是int類型并且auto_increment
SELECT * FROM users WHERE uid > 456891 ORDER BY uid LIMIT 0, 10;
這條語句，大意如下:
SELECT * FROM users WHERE uid >= (SELECT uid FROM users ORDER BY uid limit 895682, 1) limit 0, 10; 如果limit的offset值過大，用戶也會翻頁疲勞，你可以設置一個offset最大的，超過了可以另行處理，一般連續(xù)翻頁過大，用戶體驗很差，則應該提供更優(yōu)的用戶體驗給用戶。
