mysql索引覆蓋實例分析

本文實例講述了mysql索引覆蓋。分享給大家供大家參考，具體如下：

索引覆蓋

如果查詢的列恰好是索引的一部分,那么查詢只需要在索引文件上進行,不需要回行到磁盤再找數據。這種查詢速度非?？?稱為“索引覆蓋”。

假設有一張t15表，在表中建立了一個聯合索引：cp（cat_id,price）

當我們使用下面的sql語句，會出現索引覆蓋的情況。不信我們可以來查看一下，這里的Extra中顯示了Using index，表示這條sql語句剛好用到了索引覆蓋。

select price from t15 where cat_id = 1;

在來看一題，創建一張t11表，在email列有一個索引

假設我們用這樣的查詢語句：

select eamil from  t11 where right(email,4)='.com'

查詢分析：

首先看一下Extra，這里有Using index，說明這里使用到了索引覆蓋，而possible_keys為NULL的原因是因為，使用到了mysql中的函數，所以在查詢的時候并沒有使用到email索引，但是key卻為email，表示了使用到了索引進行排序，不信我把數據打印看看。

這里的數據是經過排序的。原本的數據是這樣的。

索引覆蓋的問題

create table A (
id varchar(64) primary key,
ver int,
…
)

表中有幾個很長的字段varbinary(3000) ，在id、ver上有聯合索引 ，共10000條數據

為什么select id from A order by id特別慢？
而select id from A order by id,ver非?？?/p>

疑問: id, (id,ver)都有索引, select id 應該都產生”索引覆蓋”的效果,為什么前者慢,而后快？

思路：innodb聚簇與myisam索引的不同 , 索引覆蓋 這2個角度來考慮

（1）假設此表使用的是Myisam的索引，那么這兩條sql語句都不需要回行查找數據，那么他們的速度應該差不多。

（2）假設此表使用的是InnoDB的索引，那么select id from A order by id這句sql使用到了主鍵索引，因為InnoDB的每個主鍵都掛載這每行的數據，并且本題中還有幾個特別大的字段，所以會在查找id的時候需要走的相對慢；而select id from A order by id,ver這句sql使用到了id,ver聯合索引，在InnoDB存儲引擎中，次索引保存的是對主鍵索引的應用，所以次索引不掛載該行的數據，那么在(id,ver)索引中查找id會快，當找到對應的節點樹時，只需要再次查找到主鍵索引的位置，即可拿到該行的數據，這樣比較快。

推斷:

（1）表如果是myisam引擎,2個語句,速度不會有明顯差異.
（2）innodb表因為聚簇索引,id索引要在磁盤上跨N多塊,導致速度慢.
（3）即使innodb引擎,如果沒有那幾個varbinay長列, 2個語句的速度也不會有明顯差異.

t12表，存儲引擎為MyISAM，有主鍵索引和（id,ver）符合索引，還有幾個大的變長字段，測試推論1

t13表，存儲引擎為InnoDB，有主鍵索引和（id,ver）符合索引，還有幾個大的變長字段推論2

t14表，存儲引擎為InnoDB，有主鍵索引和（id,ver）符合索引，沒有幾個大的變長字段推論3

t12,t13,t14每張表中都有1W條數據，然后進行測試，測試結果如下，我們的推論是正確的。

更多關于MySQL相關內容感興趣的讀者可查看本站專題：《MySQL索引操作技巧匯總》、《MySQL常用函數大匯總》、《MySQL日志操作技巧大全》、《MySQL事務操作技巧匯總》、《MySQL存儲過程技巧大全》及《MySQL數據庫鎖相關技巧匯總》

希望本文所述對大家MySQL數據庫計有所幫助。