是時(shí)候檢查一下使用索引的姿勢(shì)是否正確了！
索引，可以有效提高我們的數(shù)據(jù)庫(kù)搜索效率，各種數(shù)據(jù)庫(kù)優(yōu)化八股文里都有相關(guān)的知識(shí)點(diǎn)可背，不過(guò)單純的被條目其實(shí)很容易忘記。
所以松哥想通過(guò)幾篇文章，和大家仔細(xì)聊一聊索引的正確使用姿勢(shì)，結(jié)合一些具體的例子來(lái)幫助大家理解索引優(yōu)化，這是一個(gè)小小的系列，可能會(huì)有幾篇文章，今天先來(lái)第一篇。
1. 索引列獨(dú)立
當(dāng)我們將帶有索引的列作為搜索的條件的時(shí)候，需要確保索引不在表達(dá)式中，索引中也不包含各種運(yùn)算。
我舉個(gè)簡(jiǎn)單例子，假設(shè)我有如下一張表：
一個(gè) user 表，里邊就四個(gè)字段，每個(gè)字段上都建了索引，現(xiàn)在有三條測(cè)試數(shù)據(jù)：
我們來(lái)比較如下兩個(gè)查詢(xún)：
可以看到：
- 第一個(gè) type 為 ALL 表示全表掃描(沒(méi)用上索引);第二個(gè) type 為 ref 表示通過(guò)索引查找數(shù)據(jù)，一般出現(xiàn)等值匹配的時(shí)候，type 會(huì)為 ref。
- 第二個(gè)的 key 指明了 MySQL 使用哪個(gè)索引來(lái)優(yōu)化查詢(xún);rows 則顯示了 MySQL 為了找到所需的值而要讀取的行數(shù).
- 第一個(gè)的 Extra 為 Using where 表示這個(gè)搜索需要在 server 層進(jìn)行判斷(過(guò)濾)，即存儲(chǔ)引擎層無(wú)法返回滿(mǎn)足條件的數(shù)據(jù)(當(dāng)然這里也不需要回表，因?yàn)閴焊紱](méi)有用啥索引)。
從上面的分析中可以看到，雖然 age-1=98 與 age=99 雖然在邏輯上并無(wú)二致，但是 MySQL 卻無(wú)法自動(dòng)解析第一個(gè)表達(dá)式，進(jìn)而導(dǎo)致第一個(gè)無(wú)法使用索引。所以，我們不要在 where 條件中寫(xiě)表達(dá)式，不僅僅是上面這種表達(dá)式，一些使用了自帶函數(shù)的表達(dá)式也不能使用，我們要盡量簡(jiǎn)化 where 條件。
不過(guò)上面這個(gè)例子太牽強(qiáng)了，一般大家不會(huì)犯這種錯(cuò)誤，但是下面這個(gè)例子就不一定了，可能會(huì)有小伙伴在上面栽跟頭：查詢(xún)最近一年出生的用戶(hù)(birthday 列也是索引)：
在這張圖里，我給出了兩種不同的查詢(xún)思路：
對(duì) birthday 做計(jì)算，如果 birthday 加上一年，得到的時(shí)間大于當(dāng)前時(shí)間，那么說(shuō)明該用戶(hù)出生日期在最近一年一年之內(nèi)。
對(duì)當(dāng)前日期進(jìn)行計(jì)算，如果當(dāng)前日期減去一年得到的時(shí)間小于 birthday，說(shuō)明 birthday 在一年之內(nèi)。
根據(jù)上圖 explain 的結(jié)果，很明顯第一種方案沒(méi)有用上索引，進(jìn)行了全表掃描;而第二種方案則用上了索引，只讀取了兩行數(shù)據(jù)就可以了。究其原因，就是因?yàn)榈谝环N方案在索引列上進(jìn)行了函數(shù)運(yùn)算，導(dǎo)致 MySQL 沒(méi)法使用索引了。
2. 巧用覆蓋索引
一般來(lái)說(shuō)我們不建議在查詢(xún)中直接使用 select *，使用 select * 有很多問(wèn)題，其中一個(gè)問(wèn)題就是無(wú)法利用索引覆蓋掃描(覆蓋索引)。
那這里需要大家首先明白什么是覆蓋索引。
在什么是 MySQL 的“回表”?一文中，松哥和大家聊了，索引按照物理存儲(chǔ)方式可以分為聚簇索引和非聚簇索引。
我們?nèi)粘Ｋf(shuō)的主鍵索引，其實(shí)就是聚簇索引(Clustered Index);主鍵索引之外，其他的都稱(chēng)之為非主鍵索引，非主鍵索引也被稱(chēng)為二級(jí)索引(Secondary Index)，或者叫作輔助索引。
對(duì)于主鍵索引和非主鍵索引，使用的數(shù)據(jù)結(jié)構(gòu)都是 B+Tree，唯一的區(qū)別在于葉子結(jié)點(diǎn)中存儲(chǔ)的內(nèi)容不同：
主鍵索引的葉子結(jié)點(diǎn)存儲(chǔ)的是一行完整的數(shù)據(jù)。
非主鍵索引的葉子結(jié)點(diǎn)存儲(chǔ)的則是主鍵值以及索引列的值。
這是兩者最大的區(qū)別。
所以，搜索時(shí)如果使用了非主鍵索引，那么一共會(huì)搜索兩棵 B+Tree，第一次搜索 B+Tree 拿到主鍵值后再去搜索主鍵索引的 B+Tree，這個(gè)過(guò)程就是所謂的回表。但是，如果搜索的字段剛好就在二級(jí)索引的葉子結(jié)點(diǎn)上，那么是不是就不需要回表了?我們來(lái)驗(yàn)證下。
假設(shè)我有如下一張表：
- CREATE TABLE `user2` (
- `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
- `username` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
- `address` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
- `gender` varchar(4) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
- PRIMARY KEY (`id`),
- KEY `username` (`username`,`address`)
- ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
id 是主鍵，username 和 address 是復(fù)合索引。
這表有三條記錄：
我們來(lái)做個(gè)簡(jiǎn)單測(cè)試，先來(lái)看如下 SQL：
- explain select username,address from user2 where username='javaboy';
這個(gè)查詢(xún) SQL，我們查詢(xún)的字段是 username 和 address，由于這兩個(gè)字段是復(fù)合索引，因此都保存在二級(jí)索引的 B+Tree 的葉子結(jié)點(diǎn)中，搜索到 username 后也就能拿到 address 的值了，因此不需要回表查詢(xún)。大家注意最后 Extra 中的 Using index 就是這意思。
Using index 表示使用索引覆蓋掃描來(lái)返回記錄，直接從索引中過(guò)濾不需要的記錄并返回命中結(jié)果，這是在 MySQL 服務(wù)器層完成的，但是無(wú)須再回表查詢(xún)記錄。
相同的道理，id 的值也存在于二級(jí)索引中，按理說(shuō)也不需要回表，所以我稍微修改一下查詢(xún) SQL，加入 id，大家來(lái)看下：
- explain select username,address,id from user2 where username='javaboy';
可以看到跟我們想的一樣。
那么我再加上 gender 呢?如果要查詢(xún)的字段中包含 gender，由于 gender 并沒(méi)有保存在二級(jí)索引的的葉子結(jié)點(diǎn)中，那么此時(shí)就需要回表查詢(xún)了：
- explain select gender from user2 where username='javaboy';
可以看到，此時(shí) Extra 為空，同時(shí)用到了二級(jí)索引 username，那么此時(shí)就需要回表了。
這個(gè)就是覆蓋索引，巧用覆蓋索引，能避免回表，提高查詢(xún)效率。那么此時(shí)就要盡量避免使用 select * 了(因?yàn)橐话銇?lái)說(shuō)不太可能給所有字段都建立一個(gè)復(fù)合索引)。
好啦，不知道小伙伴看明白沒(méi)有，下篇文章我們繼續(xù)~
本文轉(zhuǎn)載自微信公眾號(hào)「江南一點(diǎn)雨」，可以通過(guò)以下二維碼關(guān)注。轉(zhuǎn)載本文請(qǐng)聯(lián)系江南一點(diǎn)雨公眾號(hào)。
