前言
在之前的文章《
聊聊Mysql優化之索引優化》中，筆者簡單介紹了Mysql索引優化的原理和一些使用場景，然而Mysql索引優化的内容還遠遠不止這些。在實際工作中，我們有時候會碰到明明已經建了索引，但是查詢速度還是上不去的問題，這時候就要當心了，有可能你的查詢語句根本就沒使用到索引，因為Mysql索引在某些情況下會失效，今天我将為大家介紹下Mysql索引優化中不得不提防的坑。
為了友善下文講解，我們先建2張表：user表和address表（由于不同MySQL版本與執行引擎的優化方法不一樣，是以本文所舉的例子都是針對MySQL 5.7.18版本，InnoDB存儲引擎而言的），建表語句如下：
CREATE TABLE `user` (
`id` varchar(255) NOT NULL ,
`phone` varchar(255) NULL ,
`age` int NULL ,
`name` varchar(255) NULL ,
PRIMARY KEY (`id`) ,
INDEX `normal_index_phone` (`phone`) USING BTREE ,
INDEX `normal_index_age` (`age`) USING BTREE
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8;
從上述SQL語句可知，user表一共有4個字段，id為varchar類型，最大長度為255，且為主鍵；phone為varchar類型，最大長度為255；name為varchar類型，最大長度為255；age為int類型。此外，我們在phone字段上建了一個普通的B-tree索引normal_index_phone；在age字段上建了一個普通的B-tree索引normal_index_age。關于B-tree索引的介紹，可以閱讀文章《
》。
接下來我們往這張表插入一批資料，資料概要如下：
表的記錄數為百萬級别：
由于是實驗目的，是以手機号跟姓名是随意填寫的，此外id的特點是字首“id”+序号，當然在實際工作中我們很少會這樣設計id，之是以這樣設計純粹是為了試驗目的，切勿生搬硬套，接下來開始我們的實驗。
實驗一：查詢手機号為12622717935的使用者資訊
大家可能心裡會想，這也太簡單了吧，一個where語句就搞定了。沒錯，隻要是有一點sql基礎的人都能很容易寫出這句sql。于是，有些同學很快就寫出了以下sql：
SELECT * FROM `user` WHERE phone=12622717935
查詢結果如下圖：
資料順利查出來了，但是這樣就完事了嗎？我可以告訴你這樣做雖然可以查詢出資料，但是卻不是最優的寫法。大家可以回過頭看看上文的建表語句，phone字段是varchar類型的，而上述我們寫的sql語句的where條件是一個數字，并不是字元串，因為沒有帶上單引号。在這種情況下MySQL是不會使用索引去查詢資料的。不信的話我們用EXPLAIN語句查詢下該語句的執行計劃。關于EXPLAIN語句的用法在下文會進行簡要介紹，有興趣的同學可以自己去深入了解下。接下來我們執行以下EXPLAIN語句：
EXPLAIN SELECT * FROM `user` WHERE phone=12622717935
結果輸出如下：
在EXPLAIN輸出中我們重點關注的是key字段、rows字段和type字段。key字段表示執行引擎最終選擇使用的索引，該字段為空，說明該查詢沒有選擇索引查詢。再看rows字段，rows字段表示執行引擎預估要掃描的記錄數，注意是預估的，并非絕對精确，這裡可以看到掃描的行數非常多，接近全表行數了。再看type字段，其值為ALL，表示MySQL将進行全表掃描。
以上種種表明該查詢并沒有使用我們在phone字段上建的B-tree索引，那有沒有辦法既能查詢出資料又能使用到索引呢？當然有，而且很簡單，隻要把上述查詢語句稍微修改下就可以了：
SELECT * FROM `user` WHERE phone='12622717935'
修改後的sql語句與之前的sql語句相比，僅僅是在手機号前後多了個單引号而已。有同學可能會有疑問，這樣就能使用到索引了嗎？我們再EXPLAIN下就知道了：
EXPLAIN SELECT * FROM `user` WHERE phone='12622717935'
其執行結果如下：
我們看下key字段，這個時候值為normal_index_phone，也就是phone字段上的B-tree索引，說明MySQL選中了normal_index_phone索引進行查詢。再看rows字段，這個時候預估的掃描記錄數變為1了，不再是之前的全表掃描了。
是以，對于字元串字段的查詢，在查詢條件中一定要使用單引号括起來，這是一個好習慣。
實驗二：查詢id序号為1000的使用者資訊
由于id字段具有一定的規則：字首“id”+序号。是以，這裡至少有2個方法可以查詢出資料，一個方法是查詢id為“id1000”的使用者，另一個方法是利用字元串截取函數SUBSTR截取“id”字元串後面的整數，再查詢該整數等于1000的使用者。在實際工作中，相信絕大多數人都會使用第一種方式，這裡因為實驗需要才引入第二種查詢方式，實際上很少人會用第二種方式去實作的。那麼這兩種實作方式有什麼不同呢？我們EXPLAIN一下就知曉了。首先看下第一種方式，我們執行下列EXPLAIN語句：
EXPLAIN SELECT * FROM `user` WHERE id ='id1000';
首先看key字段，為PRIMARY，說明使用到了主鍵索引。再看rows字段，值為1，說明預估的掃描行數為1。執行計劃看起來非常理想。接下來我們看下第二種實作方式，我們執行下列EXPLAIN語句：
EXPLAIN SELECT * FROM `user` WHERE SUBSTR(id,3) =1000;
首先看key字段為空，說明查詢引擎沒有使用到索引。再看rows字段，值非常大，已經接近總行數了。接着看type字段，值為ALL，說明使用了全表掃描。
觀察兩句sql的差別，無非就是第二句sql在索引列上使用了函數，導緻查詢引擎無法使用索引查詢。是以，在索引列上進行條件查詢時，一定要保證索引列是獨立的，獨立的意思是索引列不能使用函數，也不能是表達式的一部分。在索引列上使用函數就是上述第二種查詢方式犯的錯。至于說索引列不能是表達式的一部分，簡單了解就是表達式不能參與列加減乘除等運算。比如查詢年齡等于70的使用者，有下列2種sql寫法（第二種寫法基本不會有人這樣寫，同樣也是出于實驗目的才列出來的）：
select * from user where age =70 limit 1;
select * from user where age+1 =71 limit 1;
第二句sql的索引列是表達式的一部分，是以第二句sql沒法使用到索引，而第一句則可以。不信的話大家可以看下執行計劃，在此就不再做分析了：
總結
本文通過2個小案例講解了Mysql索引優化中經常碰到的坑，并簡要介紹了如何通過EXPLAIN語句去分析sql語句的執行計劃，進而對症下藥，進行合适的索引優化。當然關于Mysql索引優化可以講的内容還有不少，後面會再進行深入探讨。
如果覺得這篇文章對你有幫助，可以掃描下方二維碼，關注本人公衆号，獲得更多優質文章推送。