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