MySQL索引八大法則之上篇
索引優(yōu)化的目的主要是讓索引不失效，走正確的索引，今天主要分享的是最近整理的索引八大法則上篇，看完的話面試考索引應(yīng)該沒問題了~
下面主要以實(shí)驗(yàn)來幫助大家理解~
一、最佳左前綴法則
1. 定義
在創(chuàng)建了多列索引的情況下，查詢從索引的最左前列開始且不能跳過索引中的列。
最佳左前綴法則就是說如果創(chuàng)建了多個(gè)索引，在使用索引時(shí)要按照創(chuàng)建索引的順序來使用，不能缺少或跳過，當(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. 只有火車頭
說明：
- 索引的創(chuàng)建順序?yàn)閚ame，age，gender;
- 直接使用name(火車頭)作為條件，可以看到type=ref，key_len=82，ref=const，效果還行。
5. 只有車廂
說明：沒使用火車頭(name)，直接用車廂，導(dǎo)致走全表掃描(type=ALL)
6. 火車頭加車廂、火車頭加車尾
說明：
火車頭加車廂、火車頭加車尾,雖然都是type=ref，但是觀察key_len和ref兩項(xiàng)，并對(duì)比只有火車頭中的結(jié)果，可得出在使用火車頭(name)和車尾(gender)時(shí)，只使用了部分索引也就是火車頭(name)的索引。
通俗理解：火車頭單獨(dú)跑沒問題，火車頭與直接相連的車廂一起跑也沒問題，但是火車頭與車尾，如果中間沒有車廂，只能火車頭自己跑。
7. 火車頭加車廂加車尾
說明：火車頭加車廂加車尾，三者串聯(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ì)算
說明：這里使用了函數(shù)計(jì)算，type=ALL，導(dǎo)致索引失效。
2. 隱式類型轉(zhuǎn)換
說明：這里'123'是字符串，而123是數(shù)字，發(fā)生了隱式類型轉(zhuǎn)換，導(dǎo)致全表掃描(type=ALL)
三、范圍右邊索引列全失效
存儲(chǔ)引擎不能使用索引中范圍右邊的列，也就是說范圍右邊的索引列會(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。說明索引全部用上，從key_len與ref可以看出。
- 當(dāng)使用范圍時(shí)(age>27)，type=range，key_len=86，ref=Null，可以看到只使用了部分索引，但gender索引沒用上。
結(jié)論：范圍右邊的索引列失效。
四、盡量使用覆蓋索引
1. 覆蓋索引定義
如果一個(gè)索引包含(或覆蓋)所有需要查詢的字段的值，稱為‘覆蓋索引’。即只需掃描索引而無須回表。
只掃描索引而無需回表的優(yōu)點(diǎn)：
- 索引條目通常遠(yuǎn)小于數(shù)據(jù)行大小，只需要讀取索引，則mysql會(huì)極大地減少數(shù)據(jù)訪問量。
- 因?yàn)樗饕前凑樟兄淀樞虼鎯?chǔ)的，所以對(duì)于IO密集的范圍查找會(huì)比隨機(jī)從磁盤讀取每一行數(shù)據(jù)的IO少很多。
- 一些存儲(chǔ)引擎如myisam在內(nèi)存中只緩存索引，數(shù)據(jù)則依賴于操作系統(tǒng)來緩存，因此要訪問數(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ì)比是否使用覆蓋索引好處
盡量使用覆蓋索引(查詢列和索引列盡量一致，通俗說就是對(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';
說明：對(duì)比兩個(gè)sql，第一個(gè)使用select *，第二個(gè)使用覆蓋索引(查詢列與條件列對(duì)應(yīng))，可看到Extra從Null變成了Using index，提高檢索效率。
