mysql索引覆蓋實(shí)例分析

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

索引覆蓋

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

假設(shè)有一張t15表，在表中建立了一個(gè)聯(lián)合索引：cp（cat_id,price）

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

select price from t15 where cat_id = 1;

在來看一題，創(chuàng)建一張t11表，在email列有一個(gè)索引

假設(shè)我們用這樣的查詢語句：

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

查詢分析：

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

這里的數(shù)據(jù)是經(jīng)過排序的。原本的數(shù)據(jù)是這樣的。

索引覆蓋的問題

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

表中有幾個(gè)很長的字段varbinary(3000) ，在id、ver上有聯(lián)合索引 ，共10000條數(shù)據(jù)

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

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

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

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

（2）假設(shè)此表使用的是InnoDB的索引，那么select id from A order by id這句sql使用到了主鍵索引，因?yàn)镮nnoDB的每個(gè)主鍵都掛載這每行的數(shù)據(jù)，并且本題中還有幾個(gè)特別大的字段，所以會(huì)在查找id的時(shí)候需要走的相對慢；而select id from A order by id,ver這句sql使用到了id,ver聯(lián)合索引，在InnoDB存儲(chǔ)引擎中，次索引保存的是對主鍵索引的應(yīng)用，所以次索引不掛載該行的數(shù)據(jù)，那么在(id,ver)索引中查找id會(huì)快，當(dāng)找到對應(yīng)的節(jié)點(diǎn)樹時(shí)，只需要再次查找到主鍵索引的位置，即可拿到該行的數(shù)據(jù)，這樣比較快。

推斷:

（1）表如果是myisam引擎,2個(gè)語句,速度不會(huì)有明顯差異.
（2）innodb表因?yàn)榫鄞厮饕?id索引要在磁盤上跨N多塊,導(dǎo)致速度慢.
（3）即使innodb引擎,如果沒有那幾個(gè)varbinay長列, 2個(gè)語句的速度也不會(huì)有明顯差異.

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

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

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

t12,t13,t14每張表中都有1W條數(shù)據(jù)，然后進(jìn)行測試，測試結(jié)果如下，我們的推論是正確的。

更多關(guān)于MySQL相關(guān)內(nèi)容感興趣的讀者可查看本站專題：《MySQL索引操作技巧匯總》、《MySQL常用函數(shù)大匯總》、《MySQL日志操作技巧大全》、《MySQL事務(wù)操作技巧匯總》、《MySQL存儲(chǔ)過程技巧大全》及《MySQL數(shù)據(jù)庫鎖相關(guān)技巧匯總》

希望本文所述對大家MySQL數(shù)據(jù)庫計(jì)有所幫助。