Mysql最佳化之索引

前言

　　這幾天抽了個時間將《高效能Mysql》看了一下忽覺索引非常之重要，習之然後總結鞏固知識。本文索引使用的是InnoDB儲存引擎。因為本文並不是說用索引的好處，所以並不會書寫QPS之類的測試結果請大家見諒。我的mysql版本是8.0.11。

目錄

　　（一）索引使用最佳化

　　　　　 ①獨立的列

                  ②覆蓋索引

                  ③索引匹配

　　（二）索引建立最佳化

　　　　　 ①首碼索引和索引選擇性

                   ②選擇合適的索引順序

                   ③不建立冗餘和重複索引

索引使用最佳化

　　我們有時候雖然建立了合適的索引但是使用不當依然會使索引失效，所以我將書上的索引使用大致總結了一下。在這之前我先介紹一下EXPLAIN產生結果中欄位type和Extra的意義,先說一下type常出現的結果。

　　（1）const 表中最多隻有一行用於主鍵和唯一索引的匹配

　　（2）all全表掃描

　　（3）ref使用索引並符合最左匹配

　　（4）index ：❶

　　　　　　a.當查詢是索引覆蓋的，即所有資料均可從索引樹擷取的時候（Extra中有Using Index）；

　　　　　　b.以索引順序從索引中尋找資料行的全表掃描（無 Using Index）；

　　　　　　c.如果Extra中Using Index與Using Where同時出現的話，則是利用索引尋找索引值的意思；

　　　　　　d.如單獨出現，則是用讀索引來代替讀行，但不用於尋找

　　接下來我們解釋一下Extra出現的結果：

　　 （1）using index 使用覆蓋索引。

　　 （2）using where 條件陳述式中部分條件使用的是索引，其他條件需要去表中篩選。

　　 （3）using inex condition 條件陳述式中所有條件都在索引中，但是所需要的資料不在索引中。

　　 （4）using where;using index 條件和所需資料都在索引中。

　獨立的列

　　獨立的列一眼上看去以為是針對於一個單獨的列建立索引但是實際上並不是這樣的。“獨立的列”是指索引列不能是運算式的一部分，也不能是函數的參數❷。這句話的前面一句話在書上是：如果使用獨立的列則mysql不會使用索引。這句話有點模稜兩可，“不會使用索引”到底是包括索引全掃描還是不包括索引全掃描，如果包括的話則與實驗結果不相符，如果不包括的話那就沒問題了。廢話不多說還是用結果來證明吧。首先我的資料庫表結構是這樣子的，如所示：

　　

　　我建立了兩個單獨列的索引用來測試運算式和函數如所示:

　　

       

　　測試sql: explain select age  from user where age =2;

　　

　　從測試結果中我們可以看到type為ref(使用BTree索引),Extra為Using index(使用了覆蓋索引)

　　如果我們把sql語句改為： explain select age from user where age+1=2;解釋結果如下所示：

　　

　　可以看到這條查詢語句是使用了索引的，不過是掃描索引的全部資料。接下來測試一下如果條件陳述式中使用了函數是否會使用索引我的sql語是：EXPLAIN SELECT id from user where TO_DAYS(birthday) >= 50000000;測試結果如所示：

　　

　　OK，結果也是index。至於書上那句話是對是錯我就不得而知了，不過大家可以自己去測試一下。

 

 

　覆蓋索引

 　如果把使用索引比作你開了一輛五菱宏光的話，那麼你使用覆蓋索引就是開了一輛蘭博基尼（蘭博基尼的效能是由你自己來決定的）。覆蓋索引簡單的來講就是你所要查詢的欄位和條件陳述式都在一條索引中。接下來又是證明的過程，我建立一個新的索引如所示:

　　

　　然後我使用這條sql語句 EXPLAIN SELECT first_name,age from user where  first_name='張' and age >0，在這條sql語句中我查詢兩個不同索引中的列查詢結果如下所示;

　　

　　在這條sql語句中我使用了兩個索引idx_fk_name和idx_fk_age，查詢的列和查詢條件都是在這兩個索引中，測試的結果為using where(需要回表查詢所需要的資料)。接下來我們使用這個sql語句 EXPLAIN SELECT last_name FROM user where first_name = '張',使用結果如所示：

      

 

　索引匹配

　　如果我們書寫的sql語句符合索引匹配原則，那麼我們就可以不進行索引的全部資料掃描，結果就是我們的查詢效率又變高了。那麼索引匹配原則是啥？我就簡略的總結一下吧。

　   全值匹配

　　全值匹配就是查詢條件和索引中的所有列進行匹配。如我上面建立的idx_fx_name索引。select * from user where first_name='張' 和 last_name = '三' 這條sql語句就是全值匹配。注意如果寫成last_name='三' and first_name='張'也是全值匹配

　　最左匹配

　　 我把書中匹配最左首碼和匹配列首碼都劃分為最左匹配，因為我覺得它都是從最左邊開始匹配的，好像網上也是這麼說的。

　　最左首碼就是你寫的條件查詢語句針對於某個索引來說它符合從左邊一個一個進行匹配的方式（經過實測條件陳述式的順序不影響最左匹配的原則）,再拿我的idx_fx_name索引來舉個例子。如select * from user where last_name = '三'和 select * from user where first_name = '張' 這兩個sql語句查詢索引的方式都不一樣，前者是掃描索引所有資料，第二個就只掃描了索引的部分資料。測試結果如下所示：

　　

　　

　　匹配範圍值

　　 在符合最左匹配的基礎上可以使用範圍進行查詢。

　　 精確匹配加範圍匹配

　　  在符合最左匹配的基礎上最後一個查詢條件可以記性範圍查詢。

　　

 索引建立最佳化　　首碼索引和索引選擇性

　　　我們先說說索引的選擇性吧。索引的選擇性是指不重複的索引值（也稱為基數,cardinality）和資料表的記錄總數（#T）的比值,範圍從1/#T到1之間❸。這句話通俗的理解就是你選擇作為索引（當然是只能選擇某個欄位，欄位的全部或者部分）的資料在表中這個欄位列中重複率越低越好，因為這樣可以過濾更多的資料行。首碼索引就是可以拿某個欄位的首碼作為索引之所以把首碼所以和索引選擇性放到一起說是為瞭解決當我們選擇一個特別長的欄位作為索引時首先會很浪費空間其次是查詢的時候速度肯定會比較慢。

　　  那麼我們怎麼計算索引選擇性的高低呢？這個有方法的，方法就是通過關鍵字DISTINCT 和 Count來計算索引的選擇性。如我計算first_name的選擇性高低可以這樣計算：

　　 select count(DISTINCT first_name) / count(1) as a1 from user;

　　 如果我要計算以first_name前三個字元作為索引的話計算選擇性可以這樣寫：

　　 select count(DISTINCT LEFT(first_name,3)) / count(1) as a1 from user;

　　 通過不斷的修改所包含的首碼的大小我們就能找到選擇性高的索引。

　  選擇合適的索引序列

　　  其實選擇合適的索引序列我覺得根據實際情況來做分析。不過一般來說我們都把選擇性高的放在前面，其他的就是要根據where子句中的排序、分組和範圍條件等其他因素來選擇索引的序列

　  不建立冗餘和重複的索引

　　  這裡有兩個問題擺在我們的面前什麼是冗餘的索引？什麼是重複索引？

　　 重複索引：具有相同列的索引就是重複索引。如(A,B)和(B,A)就是重複索引。

　　 冗餘索引：一個索引的子集就是冗餘索引。如(A,B,C) 和(A,B) (B,C)就是冗餘索引。

　　 從一般情況來說就是盡量不建立重複索引和冗餘索引，但是在特殊的情況下我們可以建立冗餘索引。

 

總結

　　以上就是我學習《高效能mysql》書籍的總結。如果有什麼問題請大家及時反饋給我畢竟互相交流才能促進學習。

借鑒書籍或部落格

❶http://blog.51cto.com/lijianjun/1881208

❷《高效能mysql》第五章第三節

❸《高效能msql》第五章第三節第二小節