「MySQL」高效能索引優化策略

天澄發表於2019-03-02
MySql索引優化

MySQL知識梳理圖，一圖看完整篇文章:

「MySQL」高效能索引優化策略

今天接上一篇『MySQL』揭開索引神祕面紗 討論了索引的實現原理，瞭解了大概的原理，接下來了解一下高效能索引的優化策略，這也是面試中經常會問到的問題。

1. 工具 Explain

在詳細總結MySQL的索引優化策略之前，先給大家介紹一個工具，方便在查慢查詢的過程，排查大部分的問題：Explain。有關Explain的詳細介紹，可以檢視官網地址： dev.mysql.com/doc/refman/… 。這裡再給大家推薦一個學習方法，就是一定要去官網學習第一手資料，如果覺得英語閱讀有挑戰的朋友，建議還是平時都積累看看英文文章，英語對於程式設計師來說很重要，先進的技術和理論很多資料都是英文版，而官網也是非常全的，要想成為技術大牛，這是必須需要修煉的。 扯淡就到這裡，下面我簡單描述一下Explain怎麼使用。 舉例：

  mysql> explain select * from user where name="xiao" and age=9099 and birthday="1980-08-02";
     +----+-------------+-------+------------+------+---------------+------------+---------+-------------------+------+----------+-------+
     | id | select_type | table | partitions | type | possible_keys | key        | key_len | ref               | rows | filtered | Extra |
     +----+-------------+-------+------------+------+---------------+------------+---------+-------------------+------+----------+-------+
     |  1 | SIMPLE      | user  | NULL       | ref  | unique_key    | unique_key | 249     | const,const,const |    1 |   100.00 | NULL  |
     +----+-------------+-------+------------+------+---------------+------------+---------+-------------------+------+----------+-------+
複製程式碼

Explain 結果有好幾列，簡單說一下常用的列：select_type, type, key, key_len, ref, rows。其餘列可以參考官網介紹。

  • select_type，是說查詢的型別，是簡單的查詢還是複雜的查詢，如果不是涉及子查詢和UNION，select_type就是SIMPLE。其餘的複雜查詢還有SUBQUERY和UNION等。
  • type， 非常重要，經常查詢分析時用到，type有幾個值ALL、index、range、ref、const(system)、NULL。ALL代表全表掃描，從頭掃到尾；index跟全表掃描一樣，只不過MySQL掃描表的時候是按照索引次序進行而不是行；range，範圍掃描，即有限制的索引掃描，開始於索引的某一點，返回匹配這個值域的行。ref，索引訪問，返回所有匹配某個單個值得行。const，常量，查詢的某部分優化轉換成一個常量。NULL，一般就是說執行的時候用不著再訪問表或者索引。查詢速度型別排序：const > ref > range > index=ALL。
  • key，這個好理解，用到了哪個索引。
  • key_len。索引裡使用的位元組數。
  • ref，表示key列記錄的索引中查詢所用的列或者常量。

2. 準備Table

CREATE TABLE `user` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `gender` varchar(16) DEFAULT NULL,
 `name` varchar(64) DEFAULT NULL,
 `birthday` varchar(16) NOT NULL,
 `age` int(11) unsigned NOT NULL,
 PRIMARY KEY (`id`),
 KEY `unique_key` (`name`,`age`,`birthday`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
複製程式碼

往表裡插入了一些資料，方便下面問題的分析

3. B-Tree索引場景和相關限制:

B-Tree索引，按上一篇原理分析知道是按順序儲存資料的，所以並不是只要查詢語句中用了索引就能起作用的，下面來看看具體的場景和限制

  1. 全值匹配。 全值匹配指的是和索引中的所有列進行匹配，例如：
mysql> explain select * from user where name="xiao" and age=9099 and birthday="1980-08-02";
+----+-------------+-------+------------+------+---------------+------------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key        | key_len | ref               | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ref  | unique_key    | unique_key | 249     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------------+---------+-------------------+------+----------+-------+
複製程式碼

全值匹配，即按照索引的所有列均精確匹配，從ref和key_len看出，從語句用到了三個索引。理論上索引對順序是比較敏感的，但實際上執行下面語句可以看看結果：

explain select * from user where age=9099 and birthday="1980-08-02" and name="xiao";
複製程式碼

結果答案是一樣的，因為MySQL查詢優化器會自動調整where子句的條件順序，從而匹配最適合的索引。

  1. 匹配最左字首 。如果想查詢name=xiao的所有人，即只使用索引的第一列。
mysql> explain select * from user where name="xiao";
+----+-------------+-------+------------+------+---------------+------------+---------+-------+-------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key        | key_len | ref   | rows  | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+-------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ref  | unique_key    | unique_key | 195     | const | 15170 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+-------+----------+-------+
複製程式碼

可以看到用到了name這個索引。如果沒有匹配最左字首，結果是怎麼樣了：

mysql> explain select * from user where birthday="1980-08-02";
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 30340 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
複製程式碼

可以看到如果沒有用name查詢索引，則變成了全表查詢。

  1. 匹配列字首。也就是說可以只匹配某一列的值的開頭部分，例如想匹配name=xiao-1開頭的資料
   mysql> explain select * from user where name like "xiao-1%";
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | user  | NULL       | range | unique_key    | unique_key | 195     | NULL | 1111 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
複製程式碼

可以看到型別是range, 使用key=unique_key的聯合索引。 如果是name like "%xiao-1%" 則就不能使用索引了，其中原因可以根據B-Tree的特性想一下。

  1. 匹配範圍值 。例如，想查詢查詢name在[xiao-1, xiao-200]之間的資料。
mysql> explain select * from user where name >  "xiao-1" and name <= "xiao-200";
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | user  | NULL       | range | unique_key    | unique_key | 195     | NULL | 1113 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
複製程式碼

可以看出type=range，用到了unique_key索引。

  1. 精確匹配某一列並範圍匹配另外一列。比如想查 name="xiao"， age在[1,100]之間的資料。
mysql> explain select * from user where name="xiao" and age > 1 and age < 100;
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | user  | NULL       | range | unique_key    | unique_key | 199     | NULL |   98 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
複製程式碼

可以從key_len的長度參考上一條範圍匹配，發現key_len的長度變長了，實際就是用到了name和age2個索引，name是精準匹配，age是範圍匹配。 思考： 如果sql語句變成:

select * from user where name="xiao" and age > 1 and age < 100 and birthday="2000-08-02";
複製程式碼

birthday的索引會用到嗎？

剛才上面也提到了B-Tree索引有一些限制，現在總結一下：

  • 最左字首原理，如果不是按照索引的最左列開始查詢，則無法使用索引。
  • 不能跳過索引中的列，比如索引是(name, age, birthday)，那麼如果只提供name, birthday兩列，則birthday的索引是無法使用的。
  • 如果查詢中的某個列範圍查詢，則其右邊所有列都無法使用索引。從第5點最後的思考題就是說的這一點限制。
  • 如果查詢語句有函式或者表示式，都是沒法使用索引的，比如age-1=18，或者left(name, 3) = xia 。
  • 匹配列字首，也就是上面提到的第三點，如果like表示式是“%xiao-1%”，則也是沒法使用索引的。

4. 索引策略

先總結一下索引的優點：

  • 索引大大減少了伺服器需要掃描的資料量
  • 索引可以幫助伺服器避免排序和臨時表
  • 索引可以將隨機I/O變成順序I/O

說了三大優點，是不是覺得只要是個表，是個列就全部加上索引就好了？

這樣顯示是不對的，雖然索引雖然加快了查詢速度，但索引也是有代價的：索引檔案本身要消耗儲存空間，同時索引會加重插入、刪除和修改記錄時的負擔，另外，MySQL在執行時也要消耗資源維護索引，因此索引並不是越多越好。 只要當索引幫助儲存引擎快速查詢到記錄帶來的好處大於其帶來的額外工作時，索引才是比較有效的。

那是否有什麼辦法知道什麼時候該用索引，什麼時候不該用了？

  • 表記錄比較少，簡單的全表掃描更高效。少的界定的話一般也是靠經驗，沒有明確多少行算少，個人覺得2000行以內就ok的，實際業務中很多配置表，明顯覺得不會有2000行的都可以。

  • 索引選擇性。高效能MySQL（第三版）對索引選擇性的定義是：不重複的索引值（也稱為基數，cardinality）和資料表的記錄總數（#T）的比值，範圍從1/#T到1之間。索引選擇性越高則查詢效率越高，因為選擇性高的索引可以讓MySQL在查詢時過濾掉更多的行。唯一索引的選擇性是1，下面舉例看一下如何計算選擇性：

    mysql> select count( distinct name) / count(1) from user;
+----------------------------------+
| count( distinct name) / count(1) |
+----------------------------------+
|                           0.6632 |
+----------------------------------+

mysql> select count( distinct birthday) / count(1) from user;
+--------------------------------------+
| count( distinct birthday) / count(1) |
+--------------------------------------+
|                               0.0002 |
+--------------------------------------+

mysql> select count( distinct id) / count(1) from user;
+--------------------------------+
| count( distinct id) / count(1) |
+--------------------------------+
|                         1.0000 |
+--------------------------------+
複製程式碼

    上面可以看出，user表裡，name索引的選擇性還蠻高，id自增主鍵選擇性就是1，birthday的選擇性很低，其實沒有必要做索引了。說白了就是不能有效區分資料的列不適合做索引列(如性別，男女未知，最多也就三種，區分度非常低)。

    接下來總結一下常見的索引策略：

    • 獨立的列： 獨立的列是指索引的列不能使表示式的一部分，也不能是函式的引數。這個上面也有提到到，就不再重複。
    • 字首索引： 有時候需要索引很長的字元列，比如name，名字這種字元一般比較長，如果作為索引，會將整個索引檔案變得很大，也會導致查詢速度慢下來。一種方法只索引開始的部分字元，這樣可以大大節約索引空間，從而提高索引效率。當然，這種優化也會降低索引的選擇性，舉例如下：
    mysql> select count( distinct left(name, 8)) / count(1) from user;
+-------------------------------------------+
| count( distinct left(name, 8)) / count(1) |
+-------------------------------------------+
|                                    0.3648 |
+-------------------------------------------+

mysql> select count( distinct left(name, 9)) / count(1) from user;
+-------------------------------------------+
| count( distinct left(name, 9)) / count(1) |
+-------------------------------------------+
|                                    0.6630 |
+-------------------------------------------+
複製程式碼

    可以看到當採用name，字首8個字元時，選擇性還比較低，當變成9個字元時，選擇性就高了很多，修改索引為left(name, 9)，看一下索引的長度降低了多少了。 將索引改為(name(9), age, birthday)

    mysql> explain select * from user where name="xiao" and age=9099 and birthday="1980-08-02";
+----+-------------+-------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref               | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ref  | unique_key2   | unique_key2 | 84      | const,const,const |    1 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------------+
複製程式碼

    可以對比全值匹配中的explain語句，key_len從249縮小到了84，縮小了三倍，大大減少了索引檔案的大小，提高了效率。但是也有缺點，字首索引對ORDER BY or GROUP BY操作無效。

  • 選擇合適的索引列順序，從場景分析中看，對於B-Tree索引是按順序儲存資料，所以選擇一個最合適的順序索引列對查詢非常有幫助，但這個也沒有比較直觀的方法，一般考慮選擇性和業務需求的特性。比如上面的例子，name的選擇性>age>birthday，且通常業務中按某個使用者的name查詢的場景會居多，所以索引的順序就是(name, age, birthday)。說白了就是較頻繁作為查詢條件的欄位才去建立索引。

  • 聚簇索引的特性， 從上一篇索引的原理分析，InnoDB引擎使用的B-Tree索引就是聚簇索引，這類索引有什麼特性了，上一篇也提到過，InnoDB資料是按主鍵聚集，如果表沒有顯示定義主鍵，則InnoDB會優先選擇一個唯一的非空索引代替，如果找不到這樣的索引，會隱式定義一個主鍵來聚簇索引。 所以在選擇主鍵的時候，建議參考以下：

    • 佔的字元儘量的小
    • 使用自增ID作為主鍵
    • 更新頻繁的列最好不要作為索引

    有些人覺得使用業務中的唯一欄位作為主鍵即可，沒必要選一個跟業務無關的自增id作為主鍵，但我個人建議最好使用跟業務無關的自增ID作為主鍵。原因如下：

    • InnoDB資料按主鍵順序聚集儲存，資料記錄本身被存於主索引的葉子節點上。這就要求同一個葉子節點內（大小為一個記憶體頁或磁碟頁）的各條資料記錄按主鍵順序存放，因此每當有一條新的記錄插入時，MySQL會根據其主鍵將其插入適當的節點和位置，如果頁面達到裝載因子（InnoDB預設為15/16），則開闢一個新的頁（節點）。 如果表使用自增主鍵，那麼每次插入新的記錄，記錄就會順序新增到當前索引節點的後續位置，當一頁寫滿，就會自動開闢一個新的頁。 如果用業務的唯一主鍵，可能非自增主鍵（如身份證號或學號等），由於每次插入主鍵的值近似於隨機，因此每次新紀錄都要被插到現有索引頁得中間某個位置，此時MySQL不得不為了將新記錄插到合適位置而移動資料，甚至目標頁面可能已經被回寫到磁碟上而從快取中清掉，此時又要從磁碟上讀回來，這增加了很多開銷，同時頻繁的移動、分頁操作造成了大量的碎片，得到了不夠緊湊的索引結構，後續不得不通過OPTIMIZE TABLE來重建表並優化填充頁面。可見插入的消耗是巨大的。

    • 為什麼主鍵的字元要小了，因為二級索引是根據主鍵來檢索資料，則葉子節點儲存了主鍵列，也就是說二級索引的訪問需要訪問二次主鍵索引，如果主鍵索引很大，二級索引的可能比想象的要大很多，從而影響效能。

    • 更新頻繁的列最好不要作為索引，如果更新頻繁的列作為索引，每次更新，為了保持有順，需要調整整個索引B-Tree樹，這樣的消耗也是挺大的。

  • 冗餘和重複索引 MySQL允許相同列上建立多個索引，有時候看到建了一個UNIQUE KEY (name, age)，然後還建了個 KEY (name)，這樣name這個索引就重複了，發現則需要刪除單獨的索引，可以減少很多開銷。索引越多，會導致插入資料變慢。

  • 未使用的索引 在設計表的時候，剛開始需求可能需要用到某個欄位的去查詢，就將此欄位增加了索引，可能最後需求變更的時候，這個欄位基本很少有場景去查，這時候經常會忘記去刪除此索引，導致沒必要的開銷。所以沒必要的索引，最好是刪除。

5. 大表如何刪除無用資料：

如果一張表百萬級以上，索引是需要額外的維護成本，因為索引檔案是單獨存在的檔案,所以當我們對資料的增刪改，都會產生額外的對索引檔案的操作,這些操作需要消耗額外的IO,會降低增刪改的執行效率。且刪除資料的速度跟建立的索引的數量是成正比的。有一個小技巧，可以參考：

  • 先刪除索引，如果直接刪除資料，會帶來索引樹的資料大規模的調整，消耗無法預估。
  • 然後刪除無用資料，這時候沒有索引，刪除無用資料的速度將會快很多。
  • 刪除資料後再重建索引，這時候資料也少了一些，速度也會相對快一點。

上面三個步驟比直接刪除肯定是要快一點，如果直接刪除資料的過程中刪除失敗，導致事務回滾，那消耗就成倍增加了。

索引策略就說這麼多，下一篇總結MySQL增刪改查和多表查詢優化。

更多精彩文章，請關注公眾號: 「天澄技術雜談」

「MySQL」高效能索引優化策略

相關文章