☆* o(≧▽≦)o *☆嗨~我是小奧??
??????個(gè)人博客：小奧的博客
??????CSDN：個(gè)人CSDN
??????Github：傳送門
??????面經(jīng)分享（?？椭黜?yè)）：傳送門
??文章作者技術(shù)和水平有限，如果文中出現(xiàn)錯(cuò)誤，希望大家多多指正！
?? 如果覺得內(nèi)容還不錯(cuò)，歡迎點(diǎn)贊收藏關(guān)注喲！ ??

MySQL深度分頁(yè)優(yōu)化問題

一、Limit使用

limit子句可以用于強(qiáng)制select語(yǔ)句返回指定的記錄數(shù)。

select 字段 from 表名 limit 參數(shù)1,參數(shù)2;
select 字段 from 表名 limit 參數(shù)2 offset 參數(shù)1;（為了與 PostgreSQL 兼容）

• 參數(shù)1：指定第一個(gè)返回記錄行的偏移量，從0開始
• 參數(shù)2：指定返回記錄行的最大數(shù)目

如果只給定一個(gè)參數(shù)，那么表示返回的最大記錄行數(shù)目。

如果第二個(gè)參數(shù)為-1，表示第一個(gè)參數(shù)的偏移量之后的所有的數(shù)據(jù)。

二、深度分頁(yè)優(yōu)化

查詢偏移量過大的場(chǎng)景我們稱為深度分頁(yè)，這會(huì)導(dǎo)致查詢性能較低。比如下面的查詢：

# 普通分頁(yè)查詢
test> select * from t_demo order by id limit 1000000, 10
[2024-01-18 20:26:41] 在 396 ms (execution: 346 ms, fetching: 50 ms) 內(nèi)檢索到從 1 開始的 10 行

這條SQL在執(zhí)行的過程中，通過非聚簇索引去查詢主鍵，然后拿到主鍵再通過聚簇索引進(jìn)行回表查詢，查詢到滿足條件的1000010條數(shù)據(jù)，丟棄前面的1000000條，返回最后10條。

優(yōu)化的思路也非常明確：

• 減少回表的次數(shù)
• 盡量通過索引來查詢

2.1 范圍查詢

當(dāng)可以保證 ID 的連續(xù)性時(shí)，根據(jù) ID 范圍進(jìn)行分頁(yè)是比較好的解決方案：

# 連續(xù)id時(shí)，根據(jù)id范圍進(jìn)行分頁(yè)
test> select * from t_demo where id > 1000000 and id <= 1000010 order by id
[2024-01-18 20:26:41] 在 69 ms (execution: 8 ms, fetching: 61 ms) 內(nèi)檢索到從 1 開始的 10 行

# 通過記錄上次查詢結(jié)果的最后一條記錄的ID進(jìn)行下一頁(yè)的查詢
test> select * from t_demo where id > 1000000 limit 10
[2024-01-18 20:26:41] 在 76 ms (execution: 6 ms, fetching: 70 ms) 內(nèi)檢索到從 1 開始的 10 行

但是一般來說，實(shí)際生產(chǎn)中很少會(huì)去使用數(shù)據(jù)庫(kù)的自增ID，所以這種優(yōu)化方式的局限性比較大，而且也不是很有必要。

2.2 子查詢

阿里《Java開發(fā)手冊(cè)》中也有明確的優(yōu)化方式：

我們先來看子查詢，子查詢的思路是：先查詢出 limit 第一個(gè)參數(shù)對(duì)應(yīng)的主鍵值，再根據(jù)這個(gè)主鍵值再去過濾并 limit，這樣效率會(huì)高一點(diǎn)。

# 子查詢
test> select * from t_demo where id >= (select id from t_demo limit 1000000, 1) limit 10
[2024-01-18 20:26:41] 在 210 ms (execution: 177 ms, fetching: 33 ms) 內(nèi)檢索到從 1 開始的 10 行

不過，子查詢的結(jié)果會(huì)先產(chǎn)生一張新的表，會(huì)影響數(shù)據(jù)庫(kù)的性能，所以應(yīng)該避免使用子查詢，并且id>=(...) 也限制了ID必須是遞增的，這同樣不適用比較復(fù)雜的場(chǎng)景。

2.3 inner join 延遲關(guān)聯(lián)

延遲關(guān)聯(lián)的優(yōu)化思路，跟子查詢的優(yōu)化思路其實(shí)是一樣的：都是把條件轉(zhuǎn)移到主鍵索引，然后減少回表。不同點(diǎn)是，延遲關(guān)聯(lián)使用了 INNER JOIN 代替子查詢。

# inner join 延遲關(guān)聯(lián)
test> select t1.* from t_demo t1
      inner join (select id from t_demo limit 1000000, 1) t2
      on t1.id >= t2.id
      limit 10
[2024-01-18 20:26:42] 在 211 ms (execution: 181 ms, fetching: 30 ms) 內(nèi)檢索到從 1 開始的 10 行

2.4 覆蓋索引

索引中已經(jīng)包含了所有需要獲取的字段的查詢方式稱為覆蓋索引。

# 覆蓋索引
test> select id, a, b from t_demo
      order by a
      limit 1000000, 10
[2024-01-18 20:26:42] 在 279 ms (execution: 248 ms, fetching: 31 ms) 內(nèi)檢索到從 1 開始的 10 行

覆蓋索引的好處：文章來源地址http://www.zghlxwxcb.cn/news/detail-803829.html

• 避免 InnoDB 表進(jìn)行索引的二次查詢，也就是回表操作： InnoDB 是以聚集索引的順序來存儲(chǔ)的，對(duì)于 InnoDB 來說，二級(jí)索引在葉子節(jié)點(diǎn)中所保存的是行的主鍵信息，如果是用二級(jí)索引查詢數(shù)據(jù)的話，在查找到相應(yīng)的鍵值后，還要通過主鍵進(jìn)行二次查詢才能獲取我們真實(shí)所需要的數(shù)據(jù)。而在覆蓋索引中，二級(jí)索引的鍵值中可以獲取所有的數(shù)據(jù)，避免了對(duì)主鍵的二次查詢 ，減少了 IO 操作，提升了查詢效率。
• 可以把隨機(jī) IO 變成順序 IO 加快查詢效率： 由于覆蓋索引是按鍵值的順序存儲(chǔ)的，對(duì)于 IO 密集型的范圍查找來說，對(duì)比隨機(jī)從磁盤讀取每一行的數(shù)據(jù) IO 要少的多，因此利用覆蓋索引在訪問時(shí)也可以把磁盤的隨機(jī)讀取的 IO 轉(zhuǎn)變成索引查找的順序 IO。

到了這里，關(guān)于MySQL深度分頁(yè)優(yōu)化問題的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！