這里寫目錄標題

事故現(xiàn)場

解決方案

提到的“回表查詢”

InnoDB的索引

什么是回表查詢

怎么優(yōu)化回表查詢

事故現(xiàn)場

數(shù)據(jù)庫使用的MySQL，有一個日志表，需要進行分頁查詢，于是很容易就想到了limit [offset偏移量] [count數(shù)量]這個查詢方式,當我們偏移量比較小時,似乎是沒什么問題

SELECT* FROMt_log WHEREtype = 1 LIMIT 5, 50

查詢時間:0.45s

但是隨著offset的增加,就出現(xiàn)了查詢時間越來越長，但是每次查出的數(shù)據(jù)都只有50條，這就讓我特別不理解

SELECT* FROMt_log WHEREtype = 1 LIMIT 500000, 50

查詢時間:57.252s

SELECT* FROMt_log WHEREtype = 1 LIMIT 1000000, 50

查詢時間:89.15s

解決方案

查閱資料發(fā)現(xiàn)“l(fā)imit”的工作方式是：

第一步.先查詢offset+count條數(shù)據(jù)；

第二步.再拋棄前offset條數(shù)據(jù)

但是全字段查詢肯定會有回表查詢操作，這就導致了進行百萬次的回表查詢，速度肯定會很慢，于是我的解決思路是，在“第一步”時不進行回表查詢，這樣會不會效率提高很多，于是把sql改成下面的等效查詢。

SELECT*

FROMt_log t

RIGHT JOIN (

SELECT uid

FROM t_log

WHERE type = 1

LIMIT 1000000,50

) tmp ON tmp.uid = t.uid

查詢時間：0.64

這下時間縮短了一百倍多，查出來的結(jié)果也是正確的，達到了我們要的效果，到此sql已經(jīng)優(yōu)化好了。

提到的“回表查詢”

上面說到“第一步”中進行了回表查詢，什么是回表查詢呢？

InnoDB的索引

這里我們不得不先解釋一下InnoDB的索引，要從InnoDB的索引實現(xiàn)說起。InnoDB有兩大類索引，一類是聚集索引(Clustered Index)，一類是普通索引(Secondary Index)

InnoDB聚集索引和普通索引有什么差異？有什么區(qū)別呢？

InnoDB的聚集索引

每行數(shù)據(jù)是存在InnoDB聚集索引的葉子節(jié)點上的，因此InnoDB必須要有且只有一個聚集索引，下面聚集索引的生成規(guī)則：

如果表定義了PK(Primary Key，主鍵)，那么PK就是聚集索引。

如果表沒有定義PK，則第一個NOT NULL UNIQUE的列就是聚集索引。

否則InnoDB會另外創(chuàng)建一個隱藏的ROWID作為聚集索引。

這種機制使得基于PK的查詢速度非常快，因為直接定位的行記錄。

InnoDB普通索引

InnoDB普通索引的葉子節(jié)點存儲主鍵值。想拿到行數(shù)據(jù)，還得去聚集索引中掃描索引樹。

注意，不是存儲行記錄頭指針，MyISAM的索引葉子節(jié)點存儲記錄指針。

什么是回表查詢

下面舉個例子解釋：

假設有個表user(id PK，name，code)，id是聚集索引，code是普通索引。表中有幾條數(shù)據(jù)。

id

name

code

-----

1

小明

AQ

-----

4

小陳

DR

-----

7

小紅

CY

-----

9

小劉

FP

-----

那么兩種索引的B+樹索引就是如下圖這樣：

從普通索引無法直接定位行記錄，那普通索引的查詢過程是怎么樣的呢？

例:select * from user where code = 'CY';

其查詢過程在通常情況下是需要掃描兩遍索引樹的,這里的執(zhí)行過程是這樣的：

如帶色的路徑：

第一遍先通過普通索引定位到主鍵值

然后第二遍再通過聚集索引定位到具體行記錄。

這就是所謂的**回表查詢**，即先定位主鍵值，再根據(jù)主鍵值定位行記錄，性能相對于只掃描一遍聚集索引樹的性能要低一些。

怎么優(yōu)化回表查詢

那怎么樣解決這個性能低的問題呢？這就涉及到一個概念---------覆蓋索引

覆蓋索引

覆蓋索引就是是一種避免回表查詢的優(yōu)化策略。就是把所有需要查詢的字段都放到普通索引中，這樣普通索引查到的葉子結(jié)點(即上圖中的黑色方框)中已經(jīng)能夠得到所需的所有字段，就不會再去聚集索引中再查詢。

實現(xiàn)覆蓋索引的方式

可分為兩種：

第一種減少查詢字段只查詢縮影有的字段，例如我們上面提到的使用limit查詢，我們只查了id字段，這樣幾百萬條數(shù)據(jù)就不會回表查詢，外層查詢時只有50條數(shù)據(jù)去聚集索引里進行了查詢。又如上面的user表 優(yōu)化sql為不查詢name字段。

例:select id，code from user where code = 'CY';

第二種方式就是修改表創(chuàng)建的索引，增加需要查詢的字段，如上面user表，把name也加到索引中，設置(name，code)兩個字段的聯(lián)合索引 。

覆蓋索引的定義與注意事項

如果一個索引覆蓋(包含)了所有需要查詢的字段的值，這個索引就是覆蓋索引。因為索引中已經(jīng)包含了要查詢的字段的值，因此查詢的時候直接返回索引中的字段值就可以了，不需要再到表中查詢，避免了對主鍵索引的二次查詢，也就提高了查詢的效率。

要注意的是，不是所有類型的索引都可以成為覆蓋索引的。因為覆蓋索引必須要存儲索引的列值，而哈希索引、空間索引和全文索引等都不存儲索引列值，索引MySQL只能使用B-Tree索引做覆蓋索引。

另外，當發(fā)起一個被索引覆蓋的查詢(索引覆蓋查詢)時，在explain(執(zhí)行計劃)的Extra列可以看到【Using Index】的信息。

覆蓋索引的優(yōu)點

1.索引條目通常遠小于數(shù)據(jù)行的大小，因為覆蓋索引只需要讀取索引，極大地減少了數(shù)據(jù)的訪問量。

2.索引是按照列值順序存儲的，對于IO密集的范圍查找會比隨機從磁盤讀取每一行數(shù)據(jù)的IO小很多。

3.一些存儲引擎比如MyISAM在內(nèi)存中只緩存索引，數(shù)據(jù)則依賴操作系統(tǒng)來緩存，因此要訪問數(shù)據(jù)的話需要一次系統(tǒng)調(diào)用，使用覆蓋索引則避免了這一點。

4.由于InnoDB的聚簇索引，覆蓋索引對InnoDB引擎下的數(shù)據(jù)庫表特別有用。因為InnoDB的二級索引在葉子節(jié)點中保存了行的主鍵值，如果二級索引能夠覆蓋查詢，就避免了對主鍵索引的二次查詢。

關(guān)注公眾號，每天進步一點點！

創(chuàng)作挑戰(zhàn)賽新人創(chuàng)作獎勵來咯，堅持創(chuàng)作打卡瓜分現(xiàn)金大獎

總結(jié)

以上是生活随笔為你收集整理的mysql 回表查询优化_MySQL中的回表查询与索引覆盖：一次百万级别分页查询使用Limit 从90秒到0.6毫秒的优化...的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。