索引，可以有效提高我們的數(shù)據(jù)庫搜索效率，各種數(shù)據(jù)庫優(yōu)化八股文里都有相關(guān)的知識點(diǎn)可背，不過單純的被條目其實(shí)很容易忘記。

所以松哥想通過幾篇文章，和大家仔細(xì)聊一聊索引的正確使用姿勢，結(jié)合一些具體的例子來幫助大家理解索引優(yōu)化，這是一個(gè)小小的系列，可能會(huì)有幾篇文章，今天先來第一篇。

1. 索引列獨(dú)立

當(dāng)我們將帶有索引的列作為搜索的條件的時(shí)候，需要確保索引不在表達(dá)式中，索引中也不包含各種運(yùn)算。

我舉個(gè)簡單例子，假設(shè)我有如下一張表：

一個(gè) user 表，里邊就四個(gè)字段，每個(gè)字段上都建了索引，現(xiàn)在有三條測試數(shù)據(jù)：

我們來比較如下兩個(gè)查詢：

可以看到：

• 第一個(gè) type 為 ALL 表示全表掃描(沒用上索引);第二個(gè) type 為 ref 表示通過索引查找數(shù)據(jù)，一般出現(xiàn)等值匹配的時(shí)候，type 會(huì)為 ref。
• 第二個(gè)的 key 指明了 MySQL 使用哪個(gè)索引來優(yōu)化查詢;rows 則顯示了 MySQL 為了找到所需的值而要讀取的行數(shù).
• 第一個(gè)的 Extra 為 Using where 表示這個(gè)搜索需要在 server 層進(jìn)行判斷(過濾)，即存儲(chǔ)引擎層無法返回滿足條件的數(shù)據(jù)(當(dāng)然這里也不需要回表，因?yàn)閴焊紱]有用啥索引)。

從上面的分析中可以看到，雖然 age-1=98 與 age=99 雖然在邏輯上并無二致，但是 MySQL 卻無法自動(dòng)解析第一個(gè)表達(dá)式，進(jìn)而導(dǎo)致第一個(gè)無法使用索引。所以，我們不要在 where 條件中寫表達(dá)式，不僅僅是上面這種表達(dá)式，一些使用了自帶函數(shù)的表達(dá)式也不能使用，我們要盡量簡化 where 條件。

不過上面這個(gè)例子太牽強(qiáng)了，一般大家不會(huì)犯這種錯(cuò)誤，但是下面這個(gè)例子就不一定了，可能會(huì)有小伙伴在上面栽跟頭：查詢最近一年出生的用戶(birthday 列也是索引)：

在這張圖里，我給出了兩種不同的查詢思路：

對 birthday 做計(jì)算，如果 birthday 加上一年，得到的時(shí)間大于當(dāng)前時(shí)間，那么說明該用戶出生日期在最近一年一年之內(nèi)。

對當(dāng)前日期進(jìn)行計(jì)算，如果當(dāng)前日期減去一年得到的時(shí)間小于 birthday，說明 birthday 在一年之內(nèi)。

根據(jù)上圖 explain 的結(jié)果，很明顯第一種方案沒有用上索引，進(jìn)行了全表掃描;而第二種方案則用上了索引，只讀取了兩行數(shù)據(jù)就可以了。究其原因，就是因?yàn)榈谝环N方案在索引列上進(jìn)行了函數(shù)運(yùn)算，導(dǎo)致 MySQL 沒法使用索引了。

2. 巧用覆蓋索引

一般來說我們不建議在查詢中直接使用 select *，使用 select * 有很多問題，其中一個(gè)問題就是無法利用索引覆蓋掃描(覆蓋索引)。

那這里需要大家首先明白什么是覆蓋索引。

在什么是 MySQL 的“回表”?一文中，松哥和大家聊了，索引按照物理存儲(chǔ)方式可以分為聚簇索引和非聚簇索引。

我們?nèi)粘Ｋf的主鍵索引，其實(shí)就是聚簇索引(Clustered Index);主鍵索引之外，其他的都稱之為非主鍵索引，非主鍵索引也被稱為二級索引(Secondary Index)，或者叫作輔助索引。

對于主鍵索引和非主鍵索引，使用的數(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è)過程就是所謂的回表。但是，如果搜索的字段剛好就在二級索引的葉子結(jié)點(diǎn)上，那么是不是就不需要回表了?我們來驗(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ù)合索引。

這表有三條記錄：

我們來做個(gè)簡單測試，先來看如下 SQL：

explain select username,address from user2 where username='javaboy'; 

這個(gè)查詢 SQL，我們查詢的字段是 username 和 address，由于這兩個(gè)字段是復(fù)合索引，因此都保存在二級索引的 B+Tree 的葉子結(jié)點(diǎn)中，搜索到 username 后也就能拿到 address 的值了，因此不需要回表查詢。大家注意最后 Extra 中的 Using index 就是這意思。

Using index 表示使用索引覆蓋掃描來返回記錄，直接從索引中過濾不需要的記錄并返回命中結(jié)果，這是在 MySQL 服務(wù)器層完成的，但是無須再回表查詢記錄。

相同的道理，id 的值也存在于二級索引中，按理說也不需要回表，所以我稍微修改一下查詢 SQL，加入 id，大家來看下：

explain select username,address,id from user2 where username='javaboy'; 

可以看到跟我們想的一樣。

那么我再加上 gender 呢?如果要查詢的字段中包含 gender，由于 gender 并沒有保存在二級索引的的葉子結(jié)點(diǎn)中，那么此時(shí)就需要回表查詢了：

explain select gender from user2 where username='javaboy'; 

可以看到，此時(shí) Extra 為空，同時(shí)用到了二級索引 username，那么此時(shí)就需要回表了。

這個(gè)就是覆蓋索引，巧用覆蓋索引，能避免回表，提高查詢效率。那么此時(shí)就要盡量避免使用 select * 了(因?yàn)橐话銇碚f不太可能給所有字段都建立一個(gè)復(fù)合索引)。

好啦，不知道小伙伴看明白沒有，下篇文章我們繼續(xù)~

本文轉(zhuǎn)載自微信公眾號「江南一點(diǎn)雨」，可以通過以下二維碼關(guān)注。轉(zhuǎn)載本文請聯(lián)系江南一點(diǎn)雨公眾號。