回表查詢

InnoDB索引分為兩大類，一類是聚集索引(Clustered Index)，一類是非聚集索引(Secondary Index)

聚集索引（聚簇索引）：葉子節(jié)點(diǎn)中存的是整行數(shù)據(jù)，找到索引也就找到了數(shù)據(jù)，索引即數(shù)據(jù)，表中行的物理順序與鍵值的邏輯（索引）順序相同，一個表只能包含一個聚集索引。因?yàn)樗饕夸洠┲荒馨凑找环N方法進(jìn)行排序。

非聚集索引（普通索引、非聚簇索引、二級索引）：非聚集索引的btree葉子節(jié)點(diǎn)中存儲的是當(dāng)行數(shù)據(jù)的PK（主鍵）。例如MYISAM通過key_buffer把索引先緩存到內(nèi)存中，當(dāng)需要訪問數(shù)據(jù)時(通過索引訪問數(shù)據(jù))，在內(nèi)存中直接搜索索引，然后通過索引找到磁盤相應(yīng)數(shù)據(jù)，這也就是為什么索引不在key buffer命中時，速度慢的原因。

為什么非主鍵索引結(jié)構(gòu)葉子結(jié)點(diǎn)存儲的是主鍵值？

減少了出現(xiàn)行移動或者數(shù)據(jù)頁分裂時二級索引的維護(hù)工作（當(dāng)數(shù)據(jù)需要更新的時候，二級索引不需要修改，只需要修改聚簇索引，一個表只能有一個聚簇索引，其他的都是二級索引，這樣只需要修改聚簇索引就可以了，不需要重新構(gòu)建二級索引）

在使用非聚集索引時，為了取到具體數(shù)據(jù)，則需要通過PK回到聚集索引里去查詢數(shù)據(jù)。著就叫回表查詢。掃描了2次索引樹。所以效率相對較低。

索引覆蓋

索引覆蓋就是解決回表查詢的一種方案。見名知意，就是查詢的所有列均被所使用的索引列覆蓋(可以是單列索引也可以是聯(lián)合索引，通常是聯(lián)合索引，單列索引很難覆蓋查詢的所有列)。

因?yàn)樗饕幸呀?jīng)包含了要查詢的字段的值，因此查詢的時候直接返回索引中的字段值就可以了，不需要再到表中查詢，避免了對主鍵索引的二次查詢，也就提高了查詢的效率。

id為聚集索引，name為非聚集索引：

select name, age from t where name = 'lcc';

就需要回表查詢

索引覆蓋：

在SQL中只查詢name字段。這樣name的索引就覆蓋到了所有的查詢列。

select name  from t where name = 'lcc';

將name的索引修改為聯(lián)合索引(name, age )，之后還是執(zhí)行select name, age from t where name = 'lcc'。這樣也覆蓋到了所有的查詢列。
因?yàn)楦采w索引必須要存儲索引的列值，而哈希索引、空間索引和全文索引等都不存儲索引列值，從而只有使用B-Tree索引的數(shù)據(jù)可以做覆蓋索引。

進(jìn)行索引覆蓋查詢時，在explain（執(zhí)行計劃）的Extra列可以看到【Using Index】的信息。

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

• 索引條目通常遠(yuǎn)小于數(shù)據(jù)行的大小，因?yàn)楦采w索引只需要讀取索引，極大地減少了數(shù)據(jù)的訪問量。
• 索引是按照列值順序存儲的，對于IO密集的范圍查找會比隨機(jī)從磁盤讀取每一行數(shù)據(jù)的IO小很多。
• 一些存儲引擎比如MyISAM在內(nèi)存中只緩存索引，數(shù)據(jù)則依賴操作系統(tǒng)來緩存，因此要訪問數(shù)據(jù)的話需要一次系統(tǒng)調(diào)用，使用覆蓋索引則避免了這一點(diǎn)。
• 由于InnoDB的聚簇索引，覆蓋索引對InnoDB引擎下的數(shù)據(jù)庫表特別有用。因?yàn)镮nnoDB的二級索引在葉子節(jié)點(diǎn)中保存了行的主鍵值，如果二級索引能夠覆蓋查詢，就避免了對主鍵索引的二次查詢。

哪些場景適合使用索引覆蓋來優(yōu)化SQL

• 當(dāng)不需要查詢整行記錄時；
• 全表count查詢優(yōu)化；
• Limit分頁查詢；

哪些情況下不要建索引

• 表記錄太少
• 經(jīng)常增刪改的表或者字段（如用戶余額）
• Where條件里用不到的字段不創(chuàng)建索引
• 過濾性不好的不適合建索引（如性別）

索引下推

索引下推優(yōu)化是 MySQL 5.6 引入的， 可以在索引遍歷過程中，對索引中包含的字段先做判斷，直接過濾掉不滿足條件的記錄，減少回表次數(shù)

建立聯(lián)合索引：

KEY `username` (`name`,`age`) )

執(zhí)行：

select * from user2 where name like 'j%' and age=99;

上面的查詢sql符合索引的最左前綴原則，所以將會用到 username 索引

5.5中上面這個 SQL 的執(zhí)行流程是這樣的：

• 首先 MySQL 的 server 層調(diào)用存儲引擎獲取第一個以 j 開頭的 username。
• 存儲引擎找到 username=‘j’ 的第一條記錄后，在 B+Tree 的葉子結(jié)點(diǎn)中保存著主鍵 id，此時通過回表操作，去主鍵索引中找到該條記錄的完整數(shù)據(jù)，并返回給 server 層。
• server 層拿到數(shù)據(jù)之后，判斷該條記錄的 age 是否為 99，如果 age=99，就把該條記錄返回給客戶端，如果 age!=99，那就就丟棄該記錄。

 5.6中上面這個 SQL 的執(zhí)行流程是這樣的：

• MySQL 的 server 層首先調(diào)用存儲引擎定位到第一個以 j 開頭的 username。
• 找到記錄后，存儲引擎并不急著回表，而是繼續(xù)判斷這條記錄的 age 是否等于 99，如果 age=99，再去回表，如果 age 不等于 99，就不去回表了，直接繼續(xù)讀取下一條記錄。

 到此這篇關(guān)于MySQL回表查詢與索引覆蓋的區(qū)別的文章就介紹到這了,更多相關(guān)MySQL回表查詢與索引覆蓋內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論