今天我們?cè)倮^續(xù)聊聊跟 MySQL 索引有關(guān)的概念。
在開始這篇文章之前，我們先來看一下這個(gè)問題：在下面這個(gè)表 T 中搬男，如果我執(zhí)行 select * from T where k between 3 and 5钞它，需要執(zhí)行幾次樹的搜索操作，會(huì)掃描多少行扶关？
下面是這個(gè)表的初始化語句阴汇。
mysql> create table T (
ID int primary key,
k int NOT NULL DEFAULT 0,
s varchar(16) NOT NULL DEFAULT '',
index k(k))
engine=InnoDB;
insert into T values(100,1,'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');
現(xiàn)在，我們一起來看看這條 SQL 查詢語句的執(zhí)行流程：
- 在 k 索引樹上找到 k=3 的記錄节槐，取得 ID = 300搀庶；
- 再到 ID 索引樹查到 ID=300 對(duì)應(yīng)的 R3；
- 在 k 索引樹取下一個(gè)值 k=5铜异，取得 ID=500哥倔；
- 再回到 ID 索引樹查到 ID=500 對(duì)應(yīng)的 R4；
- 在 k 索引樹取下一個(gè)值 k=6揍庄，不滿足條件咆蒿，循環(huán)結(jié)束。
在這個(gè)過程中蚂子，回到主鍵索引樹搜索的過程沃测，我們稱為回表。可以看到食茎，這個(gè)查詢過程讀了 k 索引樹的 3 條記錄（步驟 1蒂破、3 和 5），回表了兩次（步驟 2 和 4）别渔。
在這個(gè)例子中寞蚌，由于查詢結(jié)果所需要的數(shù)據(jù)只在主鍵索引上有，所以不得不回表钠糊。那么挟秤，有沒有可能經(jīng)過索引優(yōu)化，避免回表過程呢抄伍？
覆蓋索引
如果執(zhí)行的語句是 select ID from T where k between 3 and 5艘刚，這時(shí)只需要查 ID 的值，而 ID 的值已經(jīng)在 k 索引樹上了截珍，因此可以直接提供查詢結(jié)果攀甚，不需要回表。也就是說岗喉，在這個(gè)查詢里面秋度，索引 k 已經(jīng)“覆蓋了”我們的查詢需求，我們稱為覆蓋索引钱床。
由于覆蓋索引可以減少樹的搜索次數(shù)荚斯，顯著提升查詢性能，所以使用覆蓋索引是一個(gè)常用的性能優(yōu)化手段。
基于上面覆蓋索引的說明事期，我們來討論一個(gè)問題：在一個(gè)市民信息表上滥壕，是否有必要將身份證號(hào)和名字建立聯(lián)合索引？
假設(shè)這個(gè)市民表的定義是這樣的：
CREATE TABLE `tuser` (
`id` int(11) NOT NULL,
`id_card` varchar(32) DEFAULT NULL,
`name` varchar(32) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`ismale` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `id_card` (`id_card`),
KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB
我們知道兽泣，身份證號(hào)是市民的唯一標(biāo)識(shí)绎橘。也就是說，如果有根據(jù)身份證號(hào)查詢市民信息的需求唠倦，我們只要在身份證號(hào)字段上建立索引就夠了称鳞。而再建立一個(gè)（身份證號(hào)、姓名）的聯(lián)合索引稠鼻，是不是浪費(fèi)空間胡岔？
如果現(xiàn)在有一個(gè)高頻請(qǐng)求，要根據(jù)市民的身份證號(hào)查詢他的姓名枷餐，這個(gè)聯(lián)合索引就有意義了靶瘸。它可以在這個(gè)高頻請(qǐng)求上用到覆蓋索引，不再需要回表查整行記錄毛肋，減少語句的執(zhí)行時(shí)間怨咪。
最左前綴原則
看到這里你一定有一個(gè)疑問，如果為每一種查詢都設(shè)計(jì)一個(gè)索引润匙，索引是不是太多了诗眨。如果我現(xiàn)在要按照市民的身份證號(hào)去查他的家庭地址呢？雖然這個(gè)查詢需求在業(yè)務(wù)中出現(xiàn)的概率不高孕讳，但總不能讓它走全表掃描吧匠楚？反過來說，單獨(dú)為一個(gè)不頻繁的請(qǐng)求創(chuàng)建一個(gè)（身份證號(hào)厂财，地址）的索引又感覺有點(diǎn)浪費(fèi)芋簿。應(yīng)該怎么做呢？
這里璃饱，我先和你說結(jié)論吧与斤。B+ 樹這種索引結(jié)構(gòu)，可以利用索引的“最左前綴”荚恶，來定位記錄撩穿。
為了直觀地說明這個(gè)概念，我們用（name谒撼，age）這個(gè)聯(lián)合索引來分析食寡。
可以看到廓潜，索引項(xiàng)是按照索引定義里面出現(xiàn)的字段順序排序的抵皱。
當(dāng)你的邏輯需求是查到所有名字是“張三”的人時(shí)善榛，可以快速定位到 ID4，然后向后遍歷得到所有需要的結(jié)果叨叙。
如果你要查的是所有名字第一個(gè)字是“張”的人，你的 SQL 語句的條件是"where name like ‘張 %’"堪澎。這時(shí)擂错，你也能夠用上這個(gè)索引，查找到第一個(gè)符合條件的記錄是 ID3樱蛤，然后向后遍歷钮呀，直到不滿足條件為止。
可以看到昨凡，不只是索引的全部定義爽醋，只要滿足最左前綴，就可以利用索引來加速檢索便脊。這個(gè)最左前綴可以是聯(lián)合索引的最左 N 個(gè)字段蚂四，也可以是字符串索引的最左 M 個(gè)字符。
基于上面對(duì)最左前綴索引的說明哪痰，我們來討論一個(gè)問題：在建立聯(lián)合索引的時(shí)候遂赠，如何安排索引內(nèi)的字段順序。
這里我們的評(píng)估標(biāo)準(zhǔn)是晌杰，索引的復(fù)用能力跷睦。因?yàn)榭梢灾С肿钭笄熬Y，所以當(dāng)已經(jīng)有了 (a,b) 這個(gè)聯(lián)合索引后肋演，一般就不需要單獨(dú)在 a 上建立索引了抑诸。因此，第一原則是爹殊，如果通過調(diào)整順序蜕乡，可以少維護(hù)一個(gè)索引，那么這個(gè)順序往往就是需要優(yōu)先考慮采用的梗夸。
所以現(xiàn)在你知道了异希，這段開頭的問題里，我們要為高頻請(qǐng)求創(chuàng)建 (身份證號(hào)绒瘦，姓名）這個(gè)聯(lián)合索引称簿，并用這個(gè)索引支持“根據(jù)身份證號(hào)查詢地址”的需求。
那么惰帽，如果既有聯(lián)合查詢憨降，又有基于 a、b 各自的查詢呢该酗？查詢條件里面只有 b 的語句授药，是無法使用 (a,b) 這個(gè)聯(lián)合索引的士嚎，這時(shí)候你不得不維護(hù)另外一個(gè)索引，也就是說你需要同時(shí)維護(hù) (a,b)悔叽、(b) 這兩個(gè)索引莱衩。
索引下推
上一段我們說到滿足最左前綴原則的時(shí)候，最左前綴可以用于在索引中定位記錄娇澎。這時(shí)笨蚁，你可能要問，那些不符合最左前綴的部分趟庄，會(huì)怎么樣呢括细？
我們還是以市民表的聯(lián)合索引（name, age）為例。如果現(xiàn)在有一個(gè)需求：檢索出表中“名字第一個(gè)字是張戚啥，而且年齡是 10 歲的所有男孩”奋单。那么，SQL 語句是這么寫的：
mysql> select * from tuser where name like '張%' and age=10 and ismale=1;
你已經(jīng)知道了前綴索引規(guī)則猫十，所以這個(gè)語句在搜索索引樹的時(shí)候览濒，只能用 “張”，找到第一個(gè)滿足條件的記錄 ID3拖云。當(dāng)然匾七，這還不錯(cuò)，總比全表掃描要好江兢。
然后呢昨忆？
當(dāng)然是判斷其他條件是否滿足。
在 MySQL 5.6 之前杉允，只能從 ID3 開始一個(gè)個(gè)回表邑贴。到主鍵索引上找出數(shù)據(jù)行，再對(duì)比字段值叔磷。
而 MySQL 5.6 引入的索引下推優(yōu)化（index condition pushdown)拢驾， 可以在索引遍歷過程中，對(duì)索引中包含的字段先做判斷改基，直接過濾掉不滿足條件的記錄繁疤，減少回表次數(shù)。
在圖 3 和 4 這兩個(gè)圖里面秕狰，每一個(gè)虛線箭頭表示回表一次稠腊。
圖 3 中，在 (name,age) 索引里面我特意去掉了 age 的值鸣哀，這個(gè)過程 InnoDB 并不會(huì)去看 age 的值架忌，只是按順序把“name 第一個(gè)字是’張’”的記錄一條條取出來回表。因此我衬，需要回表 4 次叹放。
圖 4 跟圖 3 的區(qū)別是饰恕，InnoDB 在 (name,age) 索引內(nèi)部就判斷了 age 是否等于 10，對(duì)于不等于 10 的記錄井仰，直接判斷并跳過埋嵌。在我們的這個(gè)例子中，只需要對(duì) ID4俱恶、ID5 這兩條記錄回表取數(shù)據(jù)判斷雹嗦，就只需要回表 2 次。
小結(jié)
今天這篇文章速那，我和你繼續(xù)討論了數(shù)據(jù)庫索引的概念俐银，包括了覆蓋索引尿背、前綴索引端仰、索引下推。你可以看到田藐，在滿足語句需求的情況下荔烧， 盡量少地訪問資源是數(shù)據(jù)庫設(shè)計(jì)的重要原則之一。我們?cè)谑褂脭?shù)據(jù)庫的時(shí)候汽久，尤其是在設(shè)計(jì)表結(jié)構(gòu)時(shí)鹤竭，也要以減少資源消耗作為目標(biāo)。
