【Mysql】查詢優(yōu)化——減少回表操作

??聚集索引：數(shù)據(jù)行的物理順序與列值（一般是主鍵的那一列）的邏輯順序相同，一個表中只能擁有一個聚集索引。 葉子結(jié)點存儲索引和行記錄，聚簇索引查詢會很快，因為可以直接定位到行記錄。

創(chuàng)新互聯(lián)建站主要從事做網(wǎng)站、成都做網(wǎng)站、網(wǎng)頁設(shè)計、企業(yè)做網(wǎng)站、公司建網(wǎng)站等業(yè)務(wù)。立足成都服務(wù)新田,十余年網(wǎng)站建設(shè)經(jīng)驗,價格優(yōu)惠、服務(wù)專業(yè),歡迎來電咨詢建站服務(wù):18980820575

??非聚集索引：該索引中索引的邏輯順序與磁盤上行的物理存儲順序不同，一個表中可以擁有多個非聚集索引。 葉子節(jié)點存儲聚簇索引值（主鍵id），需要掃碼兩遍索引樹，先通過普通索引定位到主鍵值id，再通過聚集索引定位到行記錄。

??回表查詢可以理解為普通索引的查詢，先定位主鍵值，再定位行記錄，它的性能較掃一遍索引樹更低。

??索引覆蓋，即將查詢sql中的字段添加到聯(lián)合索引里面，只要保證查詢語句里面的字段都在索引文件中，就無需進(jìn)行回表查詢；

??實際開發(fā)中，不可能把所有字段建立到聯(lián)合索引，可根據(jù)實際業(yè)務(wù)場景，把經(jīng)常需要查詢的字段建立到聯(lián)合索引中。

?? 在Mysql5.6的版本上推出，用于優(yōu)化查詢。 在索引遍歷過程中，對索引中包含的字段先做判斷，直接過濾掉不滿足條件的記錄，減少回表次數(shù)。

?? 優(yōu)化超多分頁場景。 查詢條件放到子查詢中，子查詢只查主鍵id，然后使用子查詢中確定的主鍵關(guān)聯(lián)查詢其他的屬性字段。

回表與覆蓋索引，索引下推

通俗的講就是，如果索引的列在 select 所需獲得的列中（因為在 mysql 中索引是根據(jù)索引列的值進(jìn)行排序的，所以索引節(jié)點中存在該列中的部分值）或者根據(jù)一次索引查詢就能獲得記錄就不需要回表，如果 select 所需獲得列中有大量的非索引列，索引就需要到表中找到相應(yīng)的列的信息，這就叫回表。

InnoDB聚集索引的葉子節(jié)點存儲行記錄，因此， InnoDB必須要有，且只有一個聚集索引：

（1）如果表定義了主鍵，則PK就是聚集索引；

（2）如果表沒有定義主鍵，則第一個非空唯一索引（not NULL unique）列是聚集索引；

（3）否則，InnoDB會創(chuàng)建一個隱藏的row-id作為聚集索引；

先創(chuàng)建一張表，sql 語句如下：

然后，我們再執(zhí)行下面的 SQL 語句，插入幾條測試數(shù)據(jù)。

假設(shè)，現(xiàn)在我們要查詢出 id 為 2 的數(shù)據(jù)。那么執(zhí)行 select * from xttblog where ID = 2; 這條 SQL 語句就不需要回表。原因是根據(jù)主鍵的查詢方式，則只需要搜索 ID 這棵 B+ 樹。主鍵是唯一的，根據(jù)這個唯一的索引，MySQL 就能確定搜索的記錄。

但當(dāng)我們使用 k 這個索引來查詢 k = 2 的記錄時就要用到回表。select * from xttblog where k = 2; 原因是通過 k 這個普通索引查詢方式，則需要先搜索 k 索引樹，然后得到主鍵 ID 的值為 1，再到 ID 索引樹搜索一次。這個過程雖然用了索引，但實際上底層進(jìn)行了兩次索引查詢，這個過程就稱為回表。

也就是說，基于非主鍵索引的查詢需要多掃描一棵索引樹。因此，我們在應(yīng)用中應(yīng)該盡量使用主鍵查詢。

我這里表里的數(shù)據(jù)量比較少，如果數(shù)據(jù)量大的話，你能很明顯的看出兩次查詢所用的時間，很明顯使用主鍵查詢效率更高。

更多如下圖：

（1）先通過普通索引定位到主鍵值id=5；

（2）在通過聚集索引定位到行記錄；

這就是所謂的回表查詢，先定位主鍵值，再定位行記錄，它的性能較掃一遍索引樹更低。

使用聚集索引（主鍵或第一個唯一索引）就不會回表，普通索引就會回表。

只需要在一棵索引樹上就能獲取SQL所需的所有列數(shù)據(jù)，無需回表，速度更快。

explain的輸出結(jié)果Extra字段為Using index時，能夠觸發(fā)索引覆蓋。

例子

第一個sql：

select id,name from user where name='shenjian';

Extra：Using index。

第二個sql：

select id,name,sex from user where name='shenjian';

能夠命中name索引， 索引葉子節(jié)點存儲了主鍵id，沒有儲存sex，sex字段必須回表查詢才能獲取到 ，不符合索引覆蓋，需要再次通過id值掃描聚集索引獲取sex字段，效率會降低。

Extra：Using index condition。

如果把(name)單列索引升級為聯(lián)合索引(name, sex)就不同了。

可以看到：

select id,name ... where name='shenjian';

select id,name,sex ... where name='shenjian';

單列索升級為聯(lián)合索引(name, sex)后，索引葉子節(jié)點存儲了主鍵id，name，sex ，都能夠命中索引覆蓋，無需回表。

畫外音，Extra：Using index。

場景1：全表count查詢優(yōu)化

原表為：

user(PK id, name, sex)；

直接：

select count(name) from user;

不能利用索引覆蓋。

添加索引：

alter table user add key(name);

就能夠利用索引覆蓋提效。

場景2：列查詢回表優(yōu)化

這個例子不再贅述，將單列索引(name)升級為聯(lián)合索引(name, sex)，即可避免回表。

場景3：分頁查詢

將單列索引(name)升級為聯(lián)合索引(name, sex)，也可以避免回表。

假設(shè)有這么個需求，查詢表中“名字第一個字是張，性別男，年齡為10歲的所有記錄”。那么，查詢語句是這么寫的：

根據(jù)前面說的“最左前綴原則”，該語句在搜索索引樹的時候，只能匹配到名字第一個字是‘張’的記錄（即記錄ID3），接下來是怎么處理的呢？當(dāng)然就是從ID3開始，逐個回表，到主鍵索引上找出相應(yīng)的記錄，再比對age和ismale這兩個字段的值是否符合。

但是！MySQL 5.6引入了索引下推優(yōu)化，可以在索引遍歷過程中， 對索引中包含的字段先做判斷，過濾掉不符合條件的記錄，減少回表字?jǐn)?shù) 。

下面圖1、圖2分別展示這兩種情況。

圖 1 中，在 (name,age) 索引里面我特意去掉了 age 的值， 這個過程 InnoDB 并不會去看 age 的值 ，只是按順序把“name 第一個字是’張’”的記錄一條條取出來回表。因此，需要回表 4 次。

圖 2 跟圖 1 的區(qū)別是，InnoDB 在 (name,age) 索引內(nèi)部就判斷了 age 是否等于 10，對于不等于 10 的記錄，直接判斷并跳過。在我們的這個例子中，只需要對 ID4、ID5 這兩條記錄回表取數(shù)據(jù)判斷，就只需要回表 2 次。

如果沒有索引下推優(yōu)化（或稱ICP優(yōu)化），當(dāng)進(jìn)行索引查詢時， 首先根據(jù)索引來查找記錄，然后再根據(jù)where條件來過濾記錄 ；在支持ICP優(yōu)化后，MySQL會在取出索引的同時， 判斷是否可以進(jìn)行where條件過濾再進(jìn)行索引查詢 ，也就是說提前執(zhí)行where的部分過濾操作，在某些場景下，可以大大減少回表次數(shù)，從而提升整體性能。

MySQL索引機(jī)制(詳細(xì)+原理+解析)

MySQL 前綴索引能有效減小索引文件的大小，提高索引的速度。但是前綴索引也有它的壞處：MySQL 不能在 ORDER BY 或 GROUP BY 中使用前綴索引，也不能把它們用作覆蓋索引(Covering Index)。

集一個索引包含多個列（最左前綴匹配原則）

索引列的值必須唯一，但允許有空值

全文索引為FUllText，在定義索引的列上支持值的全文查找，允許在這些索引列中插入重復(fù)值和空值，全文索引可以在CHAR,VARCHAR,TEXT類型列上創(chuàng)建

設(shè)定主鍵后數(shù)據(jù)會自動建立索引，InnoDB為聚簇索引

即一個索引只包含單個列，一個表可以有多個單列索引

覆蓋索引是指一個查詢語句的執(zhí)行只用從所有就能夠得到，不必從數(shù)據(jù)表中讀取，覆蓋索引不是索引樹，是一個結(jié)果，當(dāng)一條查詢語句符合覆蓋索引條件時候，MySQL只需要通過索引就可以返回查詢所需要的數(shù)據(jù)，這樣避免了查到索引后的回表操作，減少了I/O效率

查看索引

列名解析：

刪除索引

查看：

刪除前:

刪除后：

普通的索引，沒有什么介紹

查看:（注意和前綴索引Sub_part的區(qū)別）

當(dāng)索引的列是unique的時候，會生成唯一索引，唯一索引關(guān)于null有下列兩種情況

SQLSERVER 下的唯一索引的列，允許null值，但最多允許有一個空值

MYSQL下的唯一索引的列，允許null值，并且允許多個空值

查看:

會建立兩個索引，一個非聚簇索引，一個是唯一索引

結(jié)果:

可以插入兩個空值（明人不說暗話，我喜歡MySQL）

一方面，它不會索引所有字段所有字符，會減小索引樹的大小.

另外一方面，索引只是為了區(qū)別出值，對于某些列，可能前幾位區(qū)別很大，我們就可以使用前綴索引。

一般情況下某個前綴的選擇性也是足夠高的，足以滿足查詢性能。對于BLOB，TEXT，或者很長的VARCHAR類型的列，必須使用前綴索引，因為MySQL不允許索引這些列的完整長度。

查看：

查看：

復(fù)合索引的最左前綴匹配原則 ：

對于復(fù)合索引，查詢在一定條件才會使用該索引

減少開銷。 建一個聯(lián)合索引(col1,col2,col3)，實際相當(dāng)于建了(col1),(col1,col2),(col1,col2,col3)三個索引。每多一個索引，都會增加寫操作的開銷和磁盤空間的開銷。對于大量數(shù)據(jù)的表，使用聯(lián)合索引會大大的減少開銷！

覆蓋索引。 對聯(lián)合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通過遍歷索引取得數(shù)據(jù)，而無需回表，這減少了很多的隨機(jī)io操作。減少io操作，特別的隨機(jī)io其實是dba主要的優(yōu)化策略。所以，在真正的實際應(yīng)用中，覆蓋索引是主要的提升性能的優(yōu)化手段之一。

效率高。 索引列越多，通過索引篩選出的數(shù)據(jù)越少。有1000W條數(shù)據(jù)的表，有如下sql:select from table where col1=1 and col2=2 and col3=3,假設(shè)假設(shè)每個條件可以篩選出10%的數(shù)據(jù)，如果只有單值索引，那么通過該索引能篩選出1000W10%=100w條數(shù)據(jù)，然后再回表從100w條數(shù)據(jù)中找到符合col2=2 and col3= 3的數(shù)據(jù)，然后再排序，再分頁；如果是聯(lián)合索引，通過索引篩選出1000w10% 10% *10%=1w。

在模糊搜索中很有效，搜索全文中的某一個字段，可以參考這篇博文

:

我們先進(jìn)行下面一個實驗看看InnoDB下的主鍵索引的一個現(xiàn)象。

查看:

我們插入進(jìn)去的時候，數(shù)據(jù)的id都是亂序的，為什么這里最后select查詢出來的結(jié)果都是進(jìn)行了排序？

這是因為InnoDB索引底層實現(xiàn)的是B+tree，B+tree具有下列的特點：

所以上面的排序是為了使用B+tree的結(jié)構(gòu) ，B+tree為了范圍搜索，將主鍵按照從小到大排序后，拆分成節(jié)點。后續(xù)還有新的節(jié)點進(jìn)入的時候，和B-tree相同的操作，會進(jìn)行分裂。

一般來說，聚簇索引的B+tree都是三層

InnoDB中主鍵索引一定是聚簇索引，聚簇索引一定是主鍵索引。

為什么這里輔助索引葉子結(jié)點不直接存儲數(shù)據(jù)呢？

MYISAM只有非聚簇索引，索引最終指向的都是物理地址。

Q:既然有回表的存在，那么聚簇索引的優(yōu)勢在哪里？

Q:主鍵索引作為聚簇索引需要注意什么

在查詢語句中使用LIke關(guān)鍵字進(jìn)行查詢時，如果匹配字符串的第一個字符為"%"，索引不會使用。如果“%”不是在第一位，索引就會使用

多列索引是在表的多個字段上創(chuàng)建的索引，滿足最左前綴匹配原則，索引才會被使用

查詢語句只有Or關(guān)鍵字時候，如果OR前后的兩個條件都是索引，這這次查詢將會使用索引，否則Or前后有一個條件的列不是索引，那么查詢中將不使用索引

新聞名稱：mysql怎么避免回表,mysql減少回表操作
URL網(wǎng)址：http://chinadenli.net/article11/dsicjdd.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站內(nèi)鏈、網(wǎng)站建設(shè)、靜態(tài)網(wǎng)站、營銷型網(wǎng)站建設(shè)、、標(biāo)簽優(yōu)化

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)