MySQL索引有哪些法則

這篇文章給大家介紹MySQL索引有哪些法則，內(nèi)容非常詳細(xì)，感興趣的小伙伴們可以參考借鑒，希望對(duì)大家能有所幫助。

一、最佳左前綴法則

1. 定義

在創(chuàng)建了多列索引的情況下，查詢從索引的最左前列開(kāi)始且不能跳過(guò)索引中的列。

最佳左前綴法則就是說(shuō)如果創(chuàng)建了多個(gè)索引，在使用索引時(shí)要按照創(chuàng)建索引的順序來(lái)使用，不能缺少或跳過(guò)，當(dāng)然如果只使用最左邊的索引列，也就是第一個(gè)索引是可以的。

2. 環(huán)境準(zhǔn)備

DROP TABLE IF EXISTS `tb_emp`; CREATE TABLE `tb_emp` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `name` varchar(20) NOT NULL,   `age` int(11) NOT NULL,   gender varchar(10) NOT NULL,   email varchar(20),   PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT INTO `tb_emp` (name,age,gender,email) VALUES ('Tom', '22','male','1@qq.com'); INSERT INTO `tb_emp` (name,age,gender,email) VALUES ('Mary', '21','female','2@qq.com'); INSERT INTO `tb_emp` (name,age,gender,email) VALUES ('Jack', '27','male','3@qq.com'); INSERT INTO `tb_emp` (name,age,gender,email) VALUES ('Rose', '23','female','4@qq.com');

3. 創(chuàng)建組合索引

create index idx_all on tb_emp(name,age,gender); show index from tb_emp;

這里用火車頭代表name,車廂代表age,車尾代表gender。

4. 只有火車頭

說(shuō)明：

• 索引的創(chuàng)建順序?yàn)閚ame，age，gender;

• 直接使用name(火車頭)作為條件，可以看到type=ref，key_len=82，ref=const，效果還行。

5. 只有車廂

說(shuō)明：沒(méi)使用火車頭(name)，直接用車廂，導(dǎo)致走全表掃描(type=ALL)

6. 火車頭加車廂、火車頭加車尾

說(shuō)明：

火車頭加車廂、火車頭加車尾,雖然都是type=ref，但是觀察key_len和ref兩項(xiàng)，并對(duì)比只有火車頭中的結(jié)果，可得出在使用火車頭(name)和車尾(gender)時(shí)，只使用了部分索引也就是火車頭(name)的索引。

通俗理解：火車頭單獨(dú)跑沒(méi)問(wèn)題，火車頭與直接相連的車廂一起跑也沒(méi)問(wèn)題，但是火車頭與車尾，如果中間沒(méi)有車廂，只能火車頭自己跑。

7. 火車頭加車廂加車尾

說(shuō)明：火車頭加車廂加車尾，三者串聯(lián)，就變成了奔跑的小火車。type=ref，key_len=128，ref=const，const，const。

二、索引列不做計(jì)算

在索引列上做任何操作(計(jì)算、函數(shù)、(自動(dòng)or手動(dòng))類型轉(zhuǎn)換)，會(huì)導(dǎo)致索引失效從而轉(zhuǎn)向全表掃描。

1. 函數(shù)計(jì)算

說(shuō)明：這里使用了函數(shù)計(jì)算，type=ALL，導(dǎo)致索引失效。

2. 隱式類型轉(zhuǎn)換

說(shuō)明：這里'123'是字符串，而123是數(shù)字，發(fā)生了隱式類型轉(zhuǎn)換，導(dǎo)致全表掃描(type=ALL)

三、范圍右邊索引列全失效

存儲(chǔ)引擎不能使用索引中范圍右邊的列，也就是說(shuō)范圍右邊的索引列會(huì)失效。

對(duì)以上4個(gè)SQL進(jìn)行分析：

• 條件單獨(dú)使用name時(shí)，type=ref，key_len=82，ref=const。

• 條件加上age時(shí)(使用常量等值)，type=ref，key_len=86，ref=const，const。

• 當(dāng)全值匹配時(shí)，type=ref，key_len=128，ref=const，const，const。說(shuō)明索引全部用上，從key_len與ref可以看出。

• 當(dāng)使用范圍時(shí)(age>27)，type=range，key_len=86，ref=Null，可以看到只使用了部分索引，但gender索引沒(méi)用上。

結(jié)論：范圍右邊的索引列失效。

四、盡量使用覆蓋索引

1. 覆蓋索引定義

如果一個(gè)索引包含(或覆蓋)所有需要查詢的字段的值，稱為‘覆蓋索引’。即只需掃描索引而無(wú)須回表。

只掃描索引而無(wú)需回表的優(yōu)點(diǎn)：

• 索引條目通常遠(yuǎn)小于數(shù)據(jù)行大小，只需要讀取索引，則mysql會(huì)極大地減少數(shù)據(jù)訪問(wèn)量。

• 因?yàn)樗饕前凑樟兄淀樞虼鎯?chǔ)的，所以對(duì)于IO密集的范圍查找會(huì)比隨機(jī)從磁盤(pán)讀取每一行數(shù)據(jù)的IO少很多。

• 一些存儲(chǔ)引擎如myisam在內(nèi)存中只緩存索引，數(shù)據(jù)則依賴于操作系統(tǒng)來(lái)緩存，因此要訪問(wèn)數(shù)據(jù)需要一次系統(tǒng)調(diào)用

• innodb的聚簇索引，覆蓋索引對(duì)innodb表特別有用。(innodb的二級(jí)索引在葉子節(jié)點(diǎn)中保存了行的主鍵值，所以如果二級(jí)主鍵能夠覆蓋查詢，則可以避免對(duì)主鍵索引的二次查詢)

覆蓋索引必須要存儲(chǔ)索引列的值，而哈希索引、空間索引和全文索引不存儲(chǔ)索引列的值，所以mysql只能用B-tree索引做覆蓋索引。

當(dāng)發(fā)起一個(gè)索引覆蓋查詢時(shí)，在explain的extra列可以看到using index的信息

2. 對(duì)比是否使用覆蓋索引好處

盡量使用覆蓋索引(查詢列和索引列盡量一致，通俗說(shuō)就是對(duì)A、B列創(chuàng)建了索引，然后查詢中也使用A、B列)，減少select *的使用。

mysql> explain select * from tb_emp where name='Jack' and age=27 and gender='male'; mysql> explain select name,age,gender from tb_emp where name='Jack' and age=27 and gender='male';

說(shuō)明：對(duì)比兩個(gè)sql，第一個(gè)使用select *，第二個(gè)使用覆蓋索引(查詢列與條件列對(duì)應(yīng))，可看到Extra從Null變成了Using  index，提高檢索效率。

關(guān)于MySQL索引有哪些法則就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，可以學(xué)到更多知識(shí)。如果覺(jué)得文章不錯(cuò)，可以把它分享出去讓更多的人看到。