MySQL 索引使用策略及優化

xugege發表於2019-08-07

MySQL的優化主要分為結構優化(Scheme optimization)和查詢優化(Query optimization)。
本章討論的高效能索引策略主要屬於結構優化範疇。本章的內容完全基於上文的理論基礎,實際上一旦理解了索引背後的機制,那麼選擇高效能的策略就變成了純粹的推理,並且可以理解這些策略背後的邏輯。

一、示例資料庫

為了討論索引策略,需要一個資料量不算小的資料庫作為示例。本文選用MySQL官方文件中提供的示例資料庫之一:employees。這個資料庫關係複雜度適中,且資料量較大。下圖是這個資料庫的E-R關係圖(引用自MySQL官方手冊):

mysql索引使用策略及優化
可以從https://github.com/datacharmer/test_db倒入employees.sql檔案

二、最左字首原理與相關優化

高效使用索引的首要條件是知道什麼樣的查詢會使用到索引,這個問題和B+Tree中的“最左字首原理”有關,下面通過例子說明最左字首原理。
這裡先說一下聯合索引的概念。在上文中,我們都是假設索引只引用了單個的列,實際上,MySQL中的索引可以以一定順序引用多個列,這種索引叫做聯合索引。
一般的,一個聯合索引是一個有序元組<a1, a2, …, an>,其中各個元素均為資料表的一列,實際上要嚴格定義索引需要用到關係代數,但是這裡我不想討論太多關係代數的話題,因為那樣會顯得很枯燥,所以這裡就不再做嚴格定義。另外,單列索引可以看成聯合索引元素數為1的特例。
以employees.titles表為例,下面先檢視其上都有哪些索引:
MySQL 索引使用策略及優化

三、EXPLAIN

在日常工作中,我們會有時會開慢查詢去記錄一些執行時間比較久的SQL語句,找出這些SQL語句並不意味著完事了,些時我們常常用到explain這個命令來檢視一個這些SQL語句的執行計劃,檢視該SQL語句有沒有使用上了索引,有沒有做全表掃描,這都可以通過explain命令來檢視。

所以我們深入瞭解MySQL的基於開銷的優化器,還可以獲得很多可能被優化器考慮到的訪問策略的細節,以及當執行SQL語句時哪種策略預計會被優化器採用。
EXPLAIN出來的資訊有10列,分別是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra
概要描述:

  • id:選擇識別符號
  • select_type: 表示查詢的型別。
  • table: 輸出結果集的表
  • type: 表示表的連線型別

    all(全表掃描) 、index(按照索引順序的全表掃描)、range(有範圍的索引掃描)
    req (查詢條件列使用了索引而且不為主鍵和unique,使用該索引列的值並不唯一)、ref_eq(使用了主鍵或者唯一性索引進行查詢的情況)、
    const(主鍵放置到where後面作為條件查詢,mysql優化器就能把這次查詢優化轉化為一個常量)

  • possible_keys: 表示查詢時,可能使用的索引
  • key: 表示實際使用的索引
  • key_len: 索引欄位的長度
  • ref: 列與索引的比較
  • rows: 掃描出的行數(估算的行數)
  • Extra:執行情況的描述和說明

四、具體內容

情況一:全列匹配

MySQL 索引使用策略及優化

explain SELECT * FROM employees.titles WHERE emp_no='10001' AND title = 'Senior Engineer' AND from_date='1986-06-26';
很明顯,當按照索引中所有列進行精確匹配(這裡精確匹配指“=”或“IN”匹配)時,索引可以被用到。這裡有一點需要注意,理論上索引對順序是敏感的,但是由於MySQL的查詢優化器會自動調整where子句的條件順序以使用適合的索引,例如我們將where中的條件順序顛倒效果是一樣的。

情況二:最左字首匹配

MySQL 索引使用策略及優化
EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001';
當查詢條件精確匹配索引的左邊連續一個或幾個列時,如或<emp_no, title>,所以可以被用到,但是隻能用到一部分,即條件所組成的最左字首。上面的查詢從分析結果看用到了PRIMARY索引,但是key_len為4,說明只用到了索引的第一列字首。

情況三:查詢條件用到了索引中列的精確匹配,但是中間某個條件未提供

MySQL 索引使用策略及優化
EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND from_date='1986-0626'; ![MySQL 索引使用策略及優化](https://cdn.learnku.com/uploads/images/201908/07/16909/m2YLbEyydU.jpg!/fw/1240)
此時索引使用情況和情況二相同,因為title未提供,所以查詢只用到了索引的第一列,而後面的from_date雖然也在索引中,但是由於title不存在而無法和左字首連線,因此需要對結果進行掃描過濾from_date(這裡由於emp_no唯一,所以不存在掃描)。
如果想讓from_date也使用索引而不是where過濾,可以增加一個輔助索引<emp_no, from_date>,此時上面的查詢會使用這個索引。除此之外,還可以使用一種稱之為“隔離列”的優化方法,將emp_no與from_date之間的“坑”填上。
首先我們看下title一共有幾種不同的值:

MySQL 索引使用策略及優化
只有7種。在這種成為“坑”的列值比較少的情況下,可以考慮用“IN”來填補這個“坑”從而形成最左字首:

MySQL 索引使用策略及優化
這次key_len為56,說明索引被用全了,但是從type和rows看出IN實際上執行了一個range查詢,這裡檢查了7個key。
“填坑”後效能提升了一點。如果經過emp_no篩選後餘下很多資料,則後者效能優勢會更加明顯。當然,如果title的值很多,用填坑就不合適了,必須建立輔助索引。

情況四:查詢條件沒有指定索引第一列

MySQL 索引使用策略及優化

由於不是最左字首,索引這樣的查詢顯然用不到索引。

情況五:匹配某列的字首字串

MySQL 索引使用策略及優化

此時可以用到索引,但是如果萬用字元不是隻出現在末尾,則無法使用索引。(原文表述有誤,如果萬用字元%不出現在開頭,則可以用到索引,但根據具體情況不同可能只會用其中一個字首)

情況六:範圍查詢

MySQL 索引使用策略及優化

範圍列可以用到索引(必須是最左字首),但是範圍列後面的列無法用到索引。同時,索引最多用於一個範圍列,因此如果查詢條件中有兩個範圍列則無法全用到索引。
MySQL 索引使用策略及優化
可以看到索引對第二個範圍索引無能為力。這裡特別要說明MySQL一個有意思的地方,那就是僅用explain可能無法區分範圍索引和多值匹配,因為在type中這兩者都顯示為range。同時,用了“between”並不意味著就是範圍查詢,例如下面的查詢:

MySQL 索引使用策略及優化
看起來是用了兩個範圍查詢,但作用於emp_no上的“BETWEEN”實際上相當於“IN”,也就是說emp_no實際是多值精確匹配。可以看到這個查詢用到了索引全部三個列。因此在MySQL中要謹慎地區分多值匹配和範圍匹配,否則會對MySQL的行為產生困惑。

情況七、索引選擇性與字首索引

既然索引可以加快查詢速度,那麼是不是隻要是查詢語句需要,就建上索引?答案是否定的。因為索引雖然加快了查詢速度,但索引也是有代價的:索引檔案本身要消耗儲存空間,同時索引會加重插入、刪除和修改記錄時的負擔,另外,MySQL在執行時也要消耗資源維護索引,因此索引並不是越多越好。一般兩種情況下不建議建索引。

第一種情況是表記錄比較少,例如一兩千條甚至只有幾百條記錄的表,沒必要建索引,讓查詢做全表掃描就好了。至於多少條記錄才算多,這個個人有個人的看法,我個人的經驗是以2000作為分界線,記錄數不超過 2000可以考慮不建索引,超過2000條可以酌情考慮索引。

另一種不建議建索引的情況是索引的選擇性較低。所謂索引的選擇性(Selectivity),是指不重複的索引值(也叫基數,Cardinality)與表記錄數(#T)的比值:

Index Selectivity = Cardinality / #T

顯然選擇性的取值範圍為(0, 1],選擇性越高的索引價值越大,這是由B+Tree的性質決定的。例如,上文用到的employees.titles表,如果title欄位經常被單獨查詢,是否需要建索引,我們看一下它的選擇性:
MySQL 索引使用策略及優化
title的選擇性不足0.0001(精確值為0.00001579),所以實在沒有什麼必要為其單獨建索引。

有一種與索引選擇性有關的索引優化策略叫做字首索引,就是用列的字首代替整個列作為索引key,當字首長度合適時,可以做到既使得字首索引的選擇性接近全列索引,同時因為索引key變短而減少了索引檔案的大小和維護開銷。下面以employees.employees表為例介紹字首索引的選擇和使用。
從示例資料庫圖可以看到employees表只有一個索引,那麼如果我們想按名字搜尋一個人,就只能全表掃描了,如果頻繁按名字搜尋員工,這樣顯然效率很低,因此我們可以考慮建索引。有兩種選擇,建或<first_name, last_name>,看下兩個索引的選擇性:
MySQL 索引使用策略及優化

顯然選擇性太低,選擇性很好,但是first_name和last_name加起來長度為30,有沒有兼顧長度和選擇性的辦法? >> 可以考慮用first_name和last_name的前幾個字元建立索引,例如, 看看其選擇性: ![MySQL 索引使用策略及優化](https://cdn.learnku.com/uploads/images/201908/07/16909/sO6dCKnvZS.jpg!/fw/1240)

這時選擇性已經很理想了,而這個索引的長度只有18,比短了接近一半,我們把這個字首索引 建上: ``` ALTER TABLE employees.employees ADD INDEX `first_name_last_name4` (first_name, last_name(4)); ``` 此時再執行一遍按名字查詢,比較分析一下與建索引前的結果: 效能的提升是顯著的,查詢速度提高了120多倍。

>> 字首索引兼顧索引大小和查詢速度,但是其缺點是不能用於ORDER BY和GROUP BY操作,也不能用於Covering index(即當索引本身包含查詢所需全部資料時,不再訪問資料檔案本身)。

相關文章