共計(jì) 2554 個(gè)字符，預(yù)計(jì)需要花費(fèi) 7 分鐘才能閱讀完成。
這篇文章給大家介紹 MySQL 索引有哪些法則，內(nèi)容非常詳細(xì)，感興趣的小伙伴們可以參考借鑒，希望對(duì)大家能有所幫助。
一、最佳左前綴法則
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.com3. 創(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)?name，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è)索引包含 (或覆蓋) 所有需要查詢的字段的值，稱為 lsquo; 覆蓋索引 rsquo;。即只需掃描索引而無須回表。
只掃描索引而無需回表的優(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，提高檢索效率。
關(guān)于 MySQL 索引有哪些法則就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，可以學(xué)到更多知識(shí)。如果覺得文章不錯(cuò)，可以把它分享出去讓更多的人看到。
