3.建立高效能的索引

• 索引是儲存引擎用於快速找到記錄的一種資料結構。
• 對良好的效能非常關鍵。
• 對查詢效能優化最有效的手段。輕易將查詢效能提高几個數量級。

3.1 索引基礎

• 儲存引擎使用索引，先在索引中找到對應的值，然後根據匹配的索引記錄找到對應的資料行。
• 可以包含一個或多個列的值。如果包含多個列，那麼列的順序也很重要，因為MySQL只能高效地使用索引的最左字首列。

3.1.1 索引的型別

索引是在儲存引擎層而不是伺服器層實現的，所以沒有統一的索引標準：不同儲存引擎的索引的工作方式不一樣，也不是蓑鮋的儲存引擎都支援所有型別的索引。即使多個儲存引擎支援同一種型別的索引，其底層的實現也可能不同。

• B-Tree索引：

  • 實際上很多儲存引擎使用的是B+Tree，即每一個葉子節點都包含指向下一個葉子節點的指標，從而方便葉子節點的範圍遍歷。

  • 對索引列是順序組織儲存的，很適合查詢範圍資料。

  • 索引對多個值進行排序的依據是CREATE TABLE語句中定義索引時列的順序。

  • MyISAM使用字首壓縮儘速使得索引更小，並通過資料的物理位置引用被索引的列；InnoDB則按照原資料格式進行儲存，並根據主鍵引用被索引的行。

  • 優化效能的時候，可能需要使用相同的列但順序不同的索引來滿足不同型別的查詢需求。

  • B+Tree結構示例：

    

  • 意味著所有的值都是按順序儲存的，並且每個葉子頁到跟的距離相同。其中葉子節點的指標指向的是被索引的資料，而不是其它的節點頁。如下為一個節點和其對應的葉子節點示例圖，其實在根結點和葉子結點之間可能有很多層節點頁，樹的深度和表的大小直接相關。

    • 從索引的根結點(並未畫出)開始搜尋，因而儲存引擎不再需要進行全表掃描，加快訪問資料的速度。
    • 根結點的槽中存放了指向子節點的指標，儲存引擎根據這些指標向下查詢
    • 通過比較節點頁的值和要查詢的值可以找到合適的指標進入下層子節點，這些指標實際上定義了子節點頁中值的上限和下限。
    • 最終找到對應的值，要麼記錄不存在。

    

  • B-Tree索引的查詢型別(適用於全鍵值、鍵值範圍或鍵字首查詢，其中鍵字首查詢只適用於根據最左字首的查詢)：

    • 全值匹配：對索引中所有的列進行匹配
    • 匹配最左字首：匹配最左索引的列
    • 匹配列字首：匹配某一列的值的開頭部分，需包含最左列
    • 匹配範圍值：匹配某一列值的某一特定範圍，需包含最左列
    • 精確匹配某一列並範圍匹配另外一列，需包含最左列
    • 只訪問索引的查詢：查詢只需要訪問索引，而無須訪問資料行。

  • 除了按值查詢外，還可以用於查詢中的ORDER BY進行排序操作(按順序查詢)。如果ORDER BY滿足之前的查詢型別，也可以滿足對應的排序需求。

  • 限制：

    • 如果不是按照索引的最左列開始查詢，則無法使用索引。
    • 不能跳過索引的列
    • 如果查詢中有某個列的範圍查詢，如LIKE，則其右邊所有列都無法使用索引優化查詢。

• 雜湊索引：

  • 基於雜湊表實現，只有精確匹配索引所有列的查詢才有效。對於每一行資料，儲存引擎都會對所有的索引列計算一個雜湊碼(hash code)，雜湊碼是一個較小的值，並且不同鍵值的行計算出來的雜湊碼也不一樣。雜湊索引將所有的雜湊碼儲存在索引中，同時在雜湊表中儲存指向每個資料行的指標。

  • MySQL中，只有Memory引擎顯示支援雜湊索引，也是該引擎表的預設索引型別，也支援B-Tree索引，另外還支援非唯一雜湊索引(如果多個列的雜湊值相同，索引會以連結串列的方式存放多個記錄指標到同一個雜湊條目中)。

  • 索引自身只需儲存對應的雜湊值，所以索引的結構十分緊湊，也讓雜湊索引查詢的速度非常快。

  • 限制：

    • 只包含雜湊值和行指標，而不儲存欄位值，所以不能使用索引中的值來避免讀取行。但由於在記憶體中，對效能的影響並不明顯。
    • 並不是按照索引值順序儲存的，所以無法用於排序。
    • 不支援部分索引列匹配查詢，因為雜湊索引始終是使用索引列的全部內容來計算雜湊值。
    • 只支援等值比較查詢，包括=、IN()、<=>。也不支援任何範圍查詢。
    • 訪問雜湊索引的資料非常快，除非有很多雜湊衝突(不同索引列值卻有相同的hash值)。當出現雜湊衝突時，儲存引擎必須遍歷所有的行指標，逐行比較，直到找到對應的行。
    • 如果雜湊衝突很多的話，一些索引維護操作的代價也非常高。例如刪除一行時，需遍歷對應雜湊值的每一行。

  • 應用場景：

    • 資料倉儲應用中有一種經典的“星型”schema，需要管理很多查詢表

  • InnoDB的自適應雜湊索引(adaptive hash index):

    • 當InnoDB注意到某些值被使用的非常頻繁時，會在記憶體中基於B-Tree索引之上在建立一個hash索引，從而擁有hash索引的優點，如快速的hash查詢。這是一個完全自動的，內部的行為，使用者無法控制或者配置，但可以關閉。

  • 建立自定義雜湊索引：

    • 如果儲存引擎不支援hash索引，可以模仿像InnoDB一樣建立hash索引。

    • 思路：在B-Tree基礎上建立一個偽雜湊索引，即將要索引的列刪除索引，對其建立一個被索引雜湊列，裡面存放原索引列每一行資料的雜湊值。

    • 缺陷：需要維護雜湊值，可以手動維護，也可以使用觸發器實現。

    • 使用時不要使用SHA1()和MD5()作為雜湊函式，因為這兩個函式計算出來的雜湊值非常長，浪費大量空間而且比較時也會更慢。可以使用CRS32()。

    • 如果資料表非常大，CRS32()會出現大量的資料衝突，可以自行實現一個簡單的64位雜湊函式，這個函式要返回整數，而不是字串。也可以使用MD5()函式返回值的一部分來作為雜湊函式。

      SELECT CONV(RIGHT(MD5("str"), 16), 16, 10) AS HASH64

    • 使用雜湊索引進行查詢時，必須在WHERE子句包含對應列值，因為可能會有雜湊衝突從而選出多個不同的資料。

• 空間資料索引(R-Tree):

  • 與B-Tree不同，這類索引無需字首查詢，會從所有維度來索引資料。
  • 查詢時可以使用任意維度來組合查詢。
  • 必須使用MySQL的GIS相關函式如MBRCONTAINS()等來維護資料。(MySQL的GIS支援不完善，開源關聯式資料庫中較好的解決方案是PostgreSQL的PostGIS)
  • MyISAM表支援空間索引，可以用作地理資料儲存

• 全文索引：

  • 查詢的是文中關鍵詞，而不是直接比較索引中的值。
  • 與其他索引完全不一樣，需注意如停用詞、詞幹和複數、布林搜尋等細節。
  • 更類似於搜尋引擎做的事，而不是簡單的WHERE條件匹配。
  • 在相同列上同時建立全文索引和基於值的B-Tree索引不會有衝突，全文索引適用於MATCH AGAINST操作。

3.2 索引的優點

• 優點：
  • 大大減少伺服器需要掃描的資料量
  • 幫助伺服器避免排序和臨時表(B-Tree會將相關的列值儲存在一起，便於ORDER BY 和GROUP BY進行排序)
  • 可以將隨機IO變為順序IO
• 索引適合某個查詢的“三星系統”：
  • 將相關記錄放到一起則獲得一星
  • 索引中的資料順序和查詢中的排列順序一致獲得二星。
  • 索引中的列包含了查詢中需要的全部列獲得三星。
• 索引並不是最好的解決方案：
  • 非常小的表，大部分情況下全表掃描更高效。
  • 中到大型的表，索引非常有效。
  • 特大型的表，建立和使用索引的代價隨之增長，需要區分出查詢需要的一組資料，如分割槽技術。
  • 表的數量特別多，可以建立一個後設資料資訊表，用來查詢需要用到的某些特性。例如執行那些需要聚合多個應用分佈在多個表的資料的查詢，則需要記錄“哪個使用者的資訊儲存在哪個表中”的後設資料，這樣在查詢時就可以直接忽略掉那些不包含指定使用者資訊的表。對大型系統是一個常用的技巧
  • 對於TB級別的資料，定位單條記錄的意義不大，所以經常使用塊級後設資料技術來代替索引

3.3 高效能的索引策略

3.3.1 獨立的列

• 索引列不能是表示式的一部分，也不能是函式的引數。因為MySQL無法自動解析

  WHERE column + 1 = 5 AND TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col) <= 10

3.3.2 字首索引和索引選擇性

索引很長的字元列，會讓索引變得大且慢。一個策略是模擬的雜湊索引，另一個策略是字首索引。

• 索引選擇性：
  • 指不重複的索引值(也成基數，cardinality)和資料表的記錄總數(#T)的比值
  • 選擇性越高則查詢效率越高，唯一索引的選擇性是1，效能是最好的。

• 字首索引：

  • 索引開始的部分字串，即可節約索引空間，從而提高索引效率，但會降低索引的選擇性。

  • 一般情況下某個列字首的選擇性也是足夠高的，足以滿足查詢效能。

  • 針對BLOB，TEXT或很長的VARCHAR型別的列，必須使用字首索引，因為MySQL不允許索引這些列的完整長度。

  • 選擇字首長度的訣竅：保持較高的選擇性(接近於索引完整列)，同時又不能太長。換句話說，字首的基數應接近於完整列的基數。

  • 方法：

    先計算出完整列的選擇性：SELECT COUNT(DISTINCT col)/COUNT(*) FROM mytable , 再與平均選擇性和最差選擇性比較。

    • 平均選擇性：在一個查詢中針對不同字首長度進行計算

      SELECT COUNT(DISTINCT LEFT(col, 3))/COUNT(*) AS sel3,
          COUNT(DISTINCT LEFT(col, 4))/COUNT(*) AS sel4,
          ...
      FROM mytable;複製程式碼

    • 最差選擇性：針對平均選擇性選出的多個字首長度，考慮其資料分佈很不均勻下的選擇性。

  • 缺點：

    • MySQL無法使用字首索引做ORDER BY和GROUP BY
    • 無法做覆蓋掃描

  • 應用場景：

    • 針對很長的十六進位制唯一ID，如儲存網站的會話(SESSION)，可採用長度為8的字首索引，而且對上層應用完全透明。

  • 有時使用字尾索引也有用途，如找到某個域名的所有電子郵件地址。但MySQL原生不支援反向索引，可以通過觸發器把字串反轉後儲存，並基於此建立字首索引。

3.3.3 多列索引

• 在多個列上建立獨立的單列索引大部分情況下並不能提高MySQL的查效能。

  • MySQL5.0及以後版本引入“索引合併(index merge)"的策略，一定程度上可以使用表上的多個單列索引來定位指定的行。對示例查詢同時使用兩個單列索引進行掃描，並將結果進行合併，可以通過EXPLAIN的Extra看到過程。這演算法有三個變種：
    • OR條件的聯合(union)
    • AND條件的相交(intersection)
    • 組合前兩種情況的聯合及相交
  • 更早版本的MySQL只能使用其中的某一個單列索引，而這種情況下沒有哪一個獨立的單列索引是非常有效的。對示例查詢使用全表掃描，除非改成UNION的方式。

  -- 兩個單列索引的查詢
  mysql> SELECT film_id, actor_id FROM sakila.film_actor
      -> WHERE actor_id = 1 OR film_id = 1;複製程式碼

• 索引合併策略有時候是一種優化的結果，但實際上更多時候說明了表上的索引建得很糟糕：

  • 當出現伺服器對多個索引做相交操作時(通常有多個AND條件)，通常意味著需要一個包含所有相關列的多列索引，而不是多個獨立的單列索引
  • 當伺服器對多個索引做聯合操作時(通常有多個OR條件)，通常需要耗費大量的CPU和記憶體在演算法的快取、排序和合並操作上。特別是當其中有些索引的選擇性不高，需要合併掃描返回的大量資料的時候。
  • 優化器不會把這些計算到“查詢成本(cost)”中，而優化器只關心隨機頁面讀取。這會使得查詢的成本被低估，導致執行該計劃還不如直接走全表掃描。這樣做不但會消耗更多的CPU和記憶體資源，還可能會影響查詢的併發性，但如果是單獨執行這樣的查詢往往會忽略對併發性的影響。通常來說，將查詢改為UNION的方式往往更好。

• 如果在EXPLAIN中看到有索引合併，應檢查下查詢和表的結構以達到最優。也可以通過引數optimizer_switch來關閉索引合併功能，或使用INGORE INDEX提示讓優化器忽略掉某些索引。

3.3.4 選擇合適的索引列順序(B-Tree場景)

正確的順序依賴於使用該索引的查詢，並且同時需要考慮如何更好地滿足排序和分組的需要。

• 選擇索引列順序的經驗法則：
  • 當不需要考慮排序和分組時，將選擇性最高的列放到索引最前列
  • 效能不只是依賴於所有索引列的選擇性(整體基數)，也和查詢條件的具體值有關，也就是和值的分佈有關。
    • 如果某些索引值的選擇性非常小，即匹配的範圍非常大，說明該索引基本沒什麼用。該特殊情況可能會摧毀整個應用的效能。

3.3.5 聚簇索引(主要關注InnoDB)

• 不是一種單獨的索引型別，而是一種資料儲存的方式。具體的細節依賴於其實現方式，但InnoDB的聚簇索引實際上在同一個結構中儲存了B-Tree索引和資料行。

• 當表有聚簇索引時，它的資料行實際上存放在索引中的葉子頁(leaf page)中，但節點也只包含了索引列。術語的“聚簇”表示資料行和相鄰的鍵值緊湊地存放在一起(Oracle中為索引組織表)。如圖，被索引的列是主鍵列

  

• 一個表只能有一個聚簇索引，因為無法同時將資料行存放在兩個不同的地方

• MySQL內建的儲存引擎不支援選擇索引作為聚簇索引，InnoDB將通過主鍵聚集資料，其預設使用聚簇索引：

  • 如果沒有定義主鍵，InnoDB會選擇一個唯一的非空索引。
  • 如果沒有唯一的非空索引，InnoDB會隱式定義一個逐漸。

• InnoDB只聚集在同一個頁面的記錄，包含相鄰鍵值的頁面可能會相距甚遠。

• 優缺點：

  • 優點(設計表和查詢時充分利用可極大地提示效能):
    • 可以把相關資料儲存在一起。如實現電子郵箱時，根據使用者ID來聚集資料，這樣只需從磁碟讀取少數的資料頁就能獲取某個使用者的全部郵件。如果沒有使用聚簇索引，則每封電子郵件都可能導致一次磁碟IO。
    • 資料訪問更快。因為索引和資料都儲存在同一個B-Tree中。
    • 使用覆蓋索引掃描的查詢可以直接使用頁節點中的主鍵值。
  • 缺點：
    • 最大限度地提高了I/O密集型應用的效能，但如果資料全部存放在記憶體中，則訪問的順序就沒那麼重要了，聚簇索引也就沒什麼優勢。
    • 插入速度嚴重依賴於插入順序。按照主鍵的順序插入是載入資料到InnoDB表中速度最快的方式。如果不是按照主鍵順序載入資料，載入完成後最好使用OPTIMIZE TABLE命令重新組織一下表。
    • 更新聚簇索引列的代價很高，因為會強制InnoDB將每個被更新的行移動到新的位置。
    • 基於聚簇索引的表在插入新行，或者主鍵被更新導致需要移動行的時候，可能面臨“頁分裂(page split)”的問題。當行的主鍵值要求必須將這一行插入到某個已滿的頁中時，儲存引擎會將該頁分裂成兩個頁面來容納該行，這會導致表佔用更多的磁碟空間。
    • 可能導致全表掃描變慢，尤其是行比較稀疏，或者由於頁分裂導致資料儲存不連續的時候。
    • 二級索引(非聚簇索引)可能比想象的要更大，因為在二級索引的葉子節點包含了引用行的主鍵列。
    • 二級索引訪問需要兩次索引查詢，而不是一次。因為二級索引葉子節點儲存的不是指向行的物理位置的指標，而是行的主鍵值。(InnoDB的自適應雜湊索引能夠減少這樣的重複工作)

• InnoDB和MyISAM的資料分佈對比

  

  • InnoDB：
    • 由於採用了聚簇索引，其儲存了整個表
    • 聚簇索引每個葉子節點都包含了主鍵值、事務ID、用於事務和MVVC的回滾指標以及所有的剩餘列。
    • 二級索引的葉子節點儲存的不是"行指標"，而是主鍵值，並以此作為指向行的“指標”。即葉子節點包含被索引的列和主鍵列。這樣的策略會讓二級索引佔用更多的空間，但減少了當出現行移動或者資料頁分裂時二級索引的維護工作，因為無須更新二級索引中的指標。
  • MyISAM
    • 採用了獨立的行儲存，按照資料插入的順序儲存在磁碟上
    • 主鍵索引和其他索引在結構上一樣，主鍵索引是一個名為PRIMARY的唯一非空索引。

• 在InnoDB表中按主鍵順序插入行

  • 如果沒有資料需要聚集，建議定義一個代理鍵作為主鍵，並且主鍵的資料應該和應用無關。最簡單是使用AUTO_INCREMENT自增列，這樣可以保證資料行是按順序寫入的，對於根據主鍵做關聯操作的效能更好。

  • 最好避免隨機的(不連續且值的分佈範圍非常大)聚簇索引，特別是對於I/O密集型的應用，比如使用UUID作為聚簇索引可能會帶來糟糕的效能，它使得聚簇索引的插入完全隨機，使得插入行的時間更長，而且索引佔用的空間更大。因為主鍵的欄位更長，還由於頁分裂和碎片導致。

  • 根據順序id插入資料：

    每條記錄都儲存在上一條記錄的後面，當達到頁的最大填充因子時(InnoDB預設為頁大小的15/16，留出部分空間用於以後修改)，下一條記錄會插入新的頁中。一旦資料按照這種順序的方式載入，主鍵頁就會被近似於被順序的記錄填滿(二級索引頁可能是不一樣的)

    造成更壞結果的場景：

    • 對於高併發工作負載，可能會造成明顯的爭用。因為所有的插入都發生在這裡，可能導致間隙鎖競爭。
    • AUTO_INCREMENT鎖機制也可能會被爭用，需考慮重新設計表或者應用，或者更改innodb_autoinc_lock_mode配置。

  • 使用隨機id插入資料：

    新行的主鍵值不一定比之前插入的大，因此需要為新行找到合適的位置——通常是已有資料的中間位置——並分配空間。這會增加很多額外的工作，並導致資料分佈不夠優化。

    缺點：

    • 寫入的目標頁可能已經重新整理到磁碟並從快取中移除，或是還沒有被載入到快取中，InnoDB在插入之前需先從磁碟讀取目標頁到記憶體中，這將導致大量的隨機IO。
    • 因為寫入是亂序的，需要頻繁地做頁分裂操作，以便為新行分配空間。因為頁分裂會導致移動大量資料，一次插入最少需要修改三個頁而不是一個頁。
    • 由於頻繁的頁分裂，頁會變得稀疏並被不規則地填充，所以最終資料會有碎片。

3.3.6 覆蓋索引

設計優秀的索引應該考慮到整個查詢，而不單單是WHERE條件部分

• 覆蓋索引：一個索引包含(覆蓋)所有需要查詢的欄位的值

• 查詢只需要掃描索引而無須回表讀取資料行的好處：

  • 索引條目通常小於資料行大小，如果只需要讀取索引會極大地減少資料訪問量。這對快取的負載非常重要，因為這種情況下響應時間大部分花在資料拷貝上。覆蓋索引對IO密集型的應用也有幫助，因為索引被資料更小，更容易全部放入記憶體中(尤其是MyISAM能壓縮索引)
  • 索引是按照列值順序儲存的(至少在單個頁內是如此 )，所以對於IO密集型的範圍查詢會比隨機從磁碟讀取每一行資料的IO要少得多。
  • 一些儲存引擎如MyISAM在記憶體中只快取索引，資料則依賴於作業系統來快取，因此訪問資料需要一次系統呼叫。這可能會導致嚴重的效能問題，尤其是那些系統呼叫佔了資料訪問中的最大開銷。
  • 覆蓋索引對使用了聚簇索引的InnoDB的表非常有用。InnoDB的二級索引在葉子節點儲存了行的主鍵值，所以如果二級節點能夠覆蓋查詢，則可以避免對主鍵索引的二次查詢。

• 覆蓋索引必須要儲存索引列的值，而雜湊索引、空間索引和全文索引都不儲存，MySQL只能使用B-Tree索引做覆蓋索引。

• 無法使用覆蓋索引的原因：

  • 沒有任何索引能夠覆蓋這個查詢。
  • 不能再索引執行LIKE操作。

• 可以使用延遲關聯使用覆蓋索引，因為延遲了對列的訪問。先在查詢第一階段使用覆蓋索引，再在外層查詢所要獲取的列值。

• InnoDB的二級索引的葉子節點包含了主鍵的值，這意味著二級索引可以有效地利用這些主鍵列來覆蓋查詢。

  -- last_name欄位有二級索引，雖然該索引的列不包括逐漸actor_id，但也能用於對actor_id做覆蓋查詢
  mysql>EXPLAIN SELECT actor_id, last_name
       -> FROM sakila.actor WHERE last_name = "HOPPER"\G複製程式碼

• 使用InnoDB的表通過主鍵查詢所有列，並不是覆蓋查詢，雖然聚簇索引的葉子節點包含了所有列的資料，但它只是一種資料儲存方式，並不算索引。

3.3.7 使用索引掃描來做排序

• MySQL生成有序結果的方式：
  • 通過排序操作
  • 按索引順序掃描。EXPLAIN出來的type列的值為“index”
• 索引如果不能覆蓋查詢所需要的全部列，那每一條記錄都需要回表查詢。這基本上是隨機IO，比順序地全表掃描更慢，尤其是在IO密集型的工作負載時。
• 設計索引儘可能滿足排序和查詢行。
• 索引掃描排序的要求(如不滿足都要執行排序操作)：
  • 只有索引的列順序和ORDER BY子句順序完全一致，並且所有列的排序方向(倒序或正序)都一樣，MySQL才能夠使用索引來對結果進行排序。
  • 如果查詢需要關聯多個表，只有當ORDER BY子句引用的欄位全部為第一個表時
  • ORDER BY子句和查詢型查詢的限制是一樣的：需滿足索引的最左字首要求
• ORDER BY子句可以在前導列為常量\常數的時候忽略該限制，如果WHERE或者JOIN子句對這些列定義了常量。... WHERE col1="xxx" ORDER BY col2 DESC;，其中col1和col2為聯合索引。
• 不能使用索引做排序的查詢：
  • ORDER BY使用了兩種不同的排序方向
  • ORDER BY引用了不在索引中的列
  • WHERE 和ORDER BY 中的列無法組合索引的最左字首
  • WHERE在第一列是範圍查詢，MySQL無法索引其餘列
  • 在某列上有多個等於條件，對排序來說也是範圍查詢。

3.3.8 壓縮(字首壓縮)索引

• MyISAM使用字首壓縮來減少索引大小，從而讓更多索引可以放入記憶體中，在某些情況下能極大地提高效能。
• 預設只壓縮字串，通過設定也能壓縮整數。
• 壓縮每個索引塊的方法：先完全儲存索引塊的第一個值，然後將其他值和第一個值比較得到相同的字首位元組數和剩餘的不同字尾部分，再把這部分儲存起來。MyISAM對指標也採用類似的壓縮方式。
• 壓縮塊使用更少的空間，代價是某些操作可能更慢。因為每個值都依賴前面的值，無法使用二分查詢只能從頭開始掃描，而對倒序的掃描效能更差。
• 對CPU密集型應用，因為掃描經常要隨機查詢，不推薦使用該索引。
• 在CREATE TABLE語句中制定PACK_KEYS引數來控制索引壓縮的方式。

3.3.9 冗餘和重複索引

• 冗餘索引：在相同列上建立多個索引。MySQL需要單獨維護重複的索引，並且優化器在查詢時也需要逐個考慮，可能會影響效能。

  • (A)是(A,B)的冗餘索引，(B,A)和(B)則不是，只針對B-Tree索引來說
  • (A,ID)也是冗餘索引，因為對InnoDB主鍵列已經包含在二級索引中
  • 其他型別的如雜湊索引也不會是B-Tree的冗餘索引
  • 增加新索引會導致INSERT，UPDATE等操作的速度變得更慢，特別是新增索引達到了記憶體瓶頸的時候。

• 重複索引：在相同列上按照相同順序建立的相同型別的索引。應該避免這種操作，常見錯誤做法是對一個主鍵新增唯一限制和查詢索引，這屬於三個重複的索引。(如果索引的型別不同，並不算重複索引)

• 大多數情況下都不需要冗餘索引，應該儘量擴充套件已有的索引而不是建立新的索引。除非擴充套件已有的索引會導致其變得太大，從而影響其他使用該索引查詢的效能。

  • 假如在整數列上有一個查詢，現在需要額外增加很長的VARCHAR列來擴充套件該索引，可能會導致效能急劇下降。特別是有查詢把這個索引當作覆蓋查詢，或者是MyISAM表並且有很多範圍查詢。

    -- Q1查詢：
    SELECT count(*) FROM userinfo WHERE state_id=5;
    -- Q2查詢：
    SELECT state_id, city, address FROM userinfo WHERE state_id=5;
    -- Q2的查詢速度會比Q1慢，最簡單的辦法是擴充套件索引變成覆蓋查詢：
    ALTER TABLE userinfo DROP key state_id, ADD KEY state_id_2 (state_id, city, address);
    -- 索引擴充套件後，Q2執行更快，但Q1變慢了。如果想要兩個查詢都變得更快，就需要兩個索引，儘管這是冗餘的。複製程式碼

• 解決冗餘和重複索引的辦法只需要刪除它們。找出這些索引的辦法：

  • 寫一些複雜的訪問INFORMATION_SCHEMA表的查詢(伺服器如果有大量的資料或表，可能會導致效能問題)
  • 第三方工具。

• 由於二級索引包含了主鍵值，因此(A)相當於(A,ID)，對WHERE A=5 ORDER BY ID這樣的查詢很有用。但如果(A)擴充套件為(A,B)相當於(A,B,ID)，前面的查詢就無法使用該索引排序，而只能用檔案排序。

3.3.10 未使用的索引

找出它們，刪掉！不過有些索引的功能相當於唯一約束，雖然一直沒被查詢使用，但是是用於避免產生重複資料的。

3.3.11 索引和鎖

• 索引可以鎖定更少的行。如果查詢從不訪問那些不需要的行，那麼就會鎖定更少的行：
  • 雖然InnoDB的行鎖效率很高，記憶體使用也很少，但是鎖定行的時候仍然會帶來額外的開銷。
  • 鎖定超過需要的行會增加鎖爭用並減少併發性。
• InnoDB只有在訪問行的時候才會對其加鎖，而索引能夠減少InnoDB訪問的次數，從而減少鎖的數量。但只有當InnoDB在儲存引擎層能夠過濾掉所有不需要的行時才有效。
  • 如果索引無法過濾掉無效的行，那麼在InnoDB檢索到資料並返回給伺服器層後，MySQL伺服器才能應用WHERE子句。而這時候InnoDB已經鎖住了這些行(包含有沒被索引的行資料，這些是要在伺服器層被過濾掉的，因為索引只在儲存引擎層工作)，到適當的時候才釋放。
  • MySQL5.0及新版本，InnoDB可以在伺服器端過濾掉行就釋放鎖；但在早期版本，只有在事務提交後才能釋放鎖。
• 如果不使用索引查詢和鎖定行的話，MySQL可能會做全表掃描並鎖住所有的行，而不管是否需要。
• InnoDB在二級索引上使用共享(讀)鎖，但訪問主鍵索引需要排他(寫)鎖，這消除了使用覆蓋索引的可能性(不理解?????)，並且使得SELECT FOR UPDATE比LOCK IN SHARE MODE或非鎖定查詢要慢得多。

3.4 索引案例學習

設計一個線上約會網站，使用者資訊表包括國家、地區、城市、性別、眼睛顏色等等。網站必須支援上面這些特徵的各種組合來搜尋使用者，還必須允許根據使用者的最後線上時間、其他會員對使用者的評分等對使用者進行排序並對結果進行限制。

• 使用索引排序，還是先檢索資料再排序？使用索引排序會嚴格限制索引和查詢的設計。

3.4.1 支援多種過濾條件

• 先看哪些列擁有不同的取值，哪些列在WHERE子句中出現得最頻繁

  • country和sex選擇性通常比較低，考慮到使用頻率，建議將(sex, country)作為查詢字首

    • 即使查詢沒有使用sex列，也可在查詢條件中新增AND SEX IN('m', 'f')來繞過。但如果列有太多的值而導致IN()列表太長，或則IN()的數量太多導致有太多的組合，則不建議使用該技巧。
    • 基本原則之一：考慮表上的所有選項。當設計索引時，不要只為現有的查詢考慮需要哪些索引，還需要考慮對查詢進行優化。如果發現某些查詢需要建立新索引，但是這個查詢又會降低另一些查詢的效率，那麼應該考慮優化原有的查詢，在優化查詢和索引找到最佳的平衡，而不是一味追求最完美索引。
• 考慮其他常見的WHERE組合列表，並需要了解哪些組合在沒有合適索引的情況下會很慢。
  • (sex, country, age)、(sex, country, region, age)(sex, country, region, city, age)都很常見
    • 這會需要大量的索引。如果想盡量重用索引，可以使用前面提到的IN()技巧
    • 如果沒有指定這個欄位搜尋，就需要定義一個全部國家列表，或者國家的全部地區列表，來確保索引字首有同樣的約束(組合所有國家、地區、性別將會是一個非常大的條件)
• 為一些生僻的搜尋條件(比如has_pictures，eye_color，eduaction)來設計索引
  • 這些列選擇性高，使用也不頻繁，可以選擇忽略，讓MySQL多掃描一些額外的行
  • 或者在age列的前面加上這些列，在查詢是使用IN()技巧來處理搜尋時沒有這些列的場景。
• 為什麼要將age列放在最後？age列有什麼特殊的地方？
  • 儘可能讓MySQL使用更多的索引列，因為查詢只能使用索引的最左字首，直到遇到第一個範圍條件。前面的列都是等於條件，age列則大多是範圍條件。
  • 雖然可以用IN()來代替範圍查詢，例如age IN(18, 19, 20)，但不是所有的範圍查詢都可以轉換。
  • 基本原則之二：儘可能將需要做範圍查詢的列放到索引後面，以便優化器能使用盡可能多的索引列。

3.4.2 避免多個範圍條件

假設有一個last_online列並希望通過下面的查詢顯示在過去幾週上線過的使用者：

WHERE eye_color IN('brown', 'blue', 'hazel')
    AND hair_color IN('black', 'red', 'blonde', 'brown')
    AND sex IN("M", "F")
    AND last_online > DATE_SUB(NOW(), INTERVAL 7 DAY)
    AND age BETWEEN 18 AND 25;

-- MySQL會將age>18和age IN(18,19)都認為是範圍查詢(通過EXPLAIN檢視)，但兩種訪問效率是不同的，因為第二個查詢是多個等值條件查詢。對MySQL來說，無法在使用範圍查詢後面的其他索引列，但對多個等值範圍查詢沒有這個限制。複製程式碼

• 這個查詢有兩個範圍條件，MySQL無法同時使用它們。
• 如果無法將age欄位轉換為一個IN()的列表，並且要求對這兩個維度的範圍查詢的速度很快，很遺憾沒有一個直接的辦法解決該問題，但可以將其中的一個範圍查詢轉換成一個簡單的等值比較：
  • 事先計算好一個active列，這個欄位由定時任務來維護。當使用者每次登陸時，將對應值設定為1，並且將過去連續7天未登陸的使用者的值設定為0
  • 這個方法可以使用(active, sex, country, age)索引。active並不是完全精確的，因為對這類查詢的精度要求並不高。如果需要精確次數，可以把last_online列放到WHERE子句，但不加入到索引中。所以這個查詢條件沒法使用任何索引，但因為這個條件的過濾性不高，即使在索引中加入該列也沒有太大的幫助，或者說缺乏合適的索引對該查詢的影響也不明顯。
• 如果使用者系統同時看到活躍和不活躍使用者，可以在查詢中使用IN()列表。另一個可選方案是為不同的組合建立單獨的索引，至少要包含(active, sex, country, age)，(active, country, age)，(sex, country, age)和(country, age)，這些索引對某個具體的查詢來說可能是更優化的，但是考慮到索引的維護額額外的空間佔有代價，並不是一個好策略。

3.4.3 優化排序

• 對選擇性非常低的列，可以增加一些特殊的索引來做排序。例如，可以建立(sex, rating)，這個查詢同時使用了ORDER BY和LIMIT，如果沒有索引會很慢

  SELECT <cols> FROM profiles WHERE sex="M" ORDER BY rating LIMIT 10

• 即使有索引，如果使用者介面上要翻頁，並且翻頁翻到比較靠後時查詢也可能非常慢:

  SELECT <cols> FROM profiles WHERE sex="M" ORDER BY rating LIMIT 10000, 10;

  無論如何建立索引，這種查詢都是嚴重的問題。因為隨著偏移量的增加，MySQL需要花費大量的時間來掃描需要丟棄的資料。反正規化化、預先計算和快取可能是解決這類查詢的僅有策略。一個更好的辦法是限制使用者能夠翻頁的數量，而實際上這對使用者體驗的影響並不大，因為使用者很少真正在乎搜尋結果的第10000頁。

• 優化這類索引另一個較好的辦法是使用延遲關聯，通過使用覆蓋索引查詢返回需要的主鍵，再根據這些主鍵關聯原表獲得需要的行。這可以減少MySQL掃描那些需要丟棄的行數。

  SELECT <cols> FROM profiles INNER JOIN (
    SELECT <primary key cols> FROM profiles 
    WHERE sex="M" ORDER BY rating LIMIT 10000, 10
  ) AS x USING(<primary key cols>);複製程式碼

3.5 維護和索引表

維護表的三個目的：找到並修復損壞的表，維護準確的索引統計資訊，減少碎片

3.5.1 找到並修復順壞的表

損壞的索引會導致查詢返回錯誤的結果或莫須有的主鍵衝突問題，嚴重時還會導致資料庫崩潰。

• 嘗試執行CHECK TABLE來檢查是否發生了表損壞(注意有些引擎不支援該命令)，通常能夠找出大多數的表和索引的錯誤。
• 修復表錯誤的辦法：
  • 可以使用REPAIR TABLE來修復損壞的表(注意有些引擎不支援該命令)。
  • 如果儲存引擎不支援REPAIR TABLE，也可通過一個不做任何操作的ALTER來重建表，如修改表的儲存引擎為當前引擎：ALTER TABLE innodb_dbl ENGINE=INNODB;
  • 將資料匯出一份，然後再重新匯入。
  • 使用第三方工具
  • 如果損壞的是系統區域，或者是表的"行資料"區域，而不是索引，那麼之前的辦法就沒有用了。只能從備份中恢復表，或者嘗試從損壞的資料檔案中儘可能恢復資料。
• 如果InnoDB引擎的表出現了損壞，那麼一定是發現了嚴重的錯誤，需要立刻調查下原因。因為InnoDB的設計一般不會出現損壞。如果發生損壞，可能是資料庫的硬體問題，或者在MySQL外部操作了資料檔案，亦或是InnoDB的缺陷(不太可能)。不存在任何查詢讓InnoDB損壞。
  • 如果出現了資料損壞，最重要的是找出原因，而不是簡單的修復，否則很有可能會不斷的損壞。可以通過設定innodb_force_recovery引數進入InnoDB的強制恢復資料模式來修復資料。

3.5.2 更新索引統計資訊

• MySQL的查詢優化器通過兩個API來了解儲存引擎的索引值分佈資訊：
  • records_in_range()，通過傳入兩個邊界值獲取在這個範圍大概有多少條記錄。對某些儲存引擎如MyISAM返回精確值，對InnoDB返回一個估算值。
  • info()，返回各種型別的資料，包括索引的基數(每個鍵值有多少條記錄)
• 如果儲存引擎向優化器提供的掃描行數資訊是不準確的資料，或者執行計劃本身太複雜而無法精確地獲取各個階段匹配的行數，那麼優化器會使用索引統計資訊來估算掃描行數。
• MySQL優化器使用的是基於成本的模型，而衡量成本的主要指標就是一個查詢需要掃描多少行。如果表沒有統計資訊，或者統計資訊不準確，優化器很可能做出錯誤的決定。通過執行ANALYZE TABLE來重新生成統計資訊解決這個問題。而每種儲存引擎實現的統計資訊的方式不同，需要進行ANALYZE TABLE的頻率和每次執行的成本也不同：
  • Memory引擎根本不儲存索引統計資訊
  • MyISAM將索引統計資訊儲存在磁碟中，ANALYZE TABLE需要進行一次全索引掃描來計算索引基數，在整個過程中需要鎖表。
  • 直到MySQL5.5版本，InnoDB也不在磁碟儲存索引統計資訊，而是通過隨機的索引訪問進行評估並將其儲存在記憶體中。
• 使用SHOW INDEX FROM table;命令來檢視索引的基數(cadinality)。基數顯示了儲存引擎估算索引列有多少個不同的取值。在MySQL5.0及以後的版本，可以通過INFORMATION_SCHEMA.STATISTICS表很方便地查詢到這些資訊，不過如果伺服器的庫表非常多，從這裡獲取後設資料的速度會非常慢，而且會給MySQL帶來額外的壓力。
• InnoDB的統計資訊：
  • 該引擎通過抽樣的方式來計算統計資訊，首先隨機地讀取少量的索引頁面，然後以此為樣本計算索引的統計資訊。老版本中樣本頁數是8，新版本可以設定innodb_stats_sample_pages來設定樣本頁的數量。理論上越大的值可以幫助生成更準確的索引資訊，特別是對某些超大資料表來說。
  • 會在表首次開啟，或者執行ANALYZE TABLE，抑或表的大小發生非常大的變化(該變化超過十六分之一或者新插入20億行)的時候計算索引的統計資訊。
  • 會在開啟某些INFORMATION_SCHEMA表，或者使用SHOW TABLE STATUS和SHOW INDEX，抑或MySQL客戶端開啟自動補全功能的時候都會觸發索引統計資訊的更新。
    • 如果伺服器上有大量的資料，可能會導致嚴重的問題，尤其是IO比較慢的時候，客戶端或者監控程式觸發索引資訊取樣更新時可能會導致大量的鎖，並給伺服器帶來額外的壓力。可以關閉innodb_stats_on_metadata引數來避免上面提到的問題。

3.5.3 減少索引和資料的碎片

• 索引碎片化：
  • B-Tree索引可能會碎片化，這會降低查詢的效率。碎片化的索引可能會以很差或者無序的方式儲存在磁碟上。
  • 根據設計，B-Tree需要隨機磁碟訪問才能定位到葉子頁，所以隨機訪問是不可避免的。然而，如果葉子頁在物理分佈上是順序且緊密的，那麼查詢的效能就會更好。否則對於範圍查詢、索引覆蓋掃描等操作來說，速度可能會降低很多倍；對於索引覆蓋掃描這一點更明顯
• 表的資料儲存碎片化(比索引碎片化更加複雜)：
  • 型別：
    • 行碎片(Row fragmentation)：資料行被儲存為多個地方的多個片段中。及時查詢只從索引中訪問一行記錄，也會導致效能下降。
    • 行間碎片(Intra-row fragmentation)：指邏輯上順序的頁，或者行在磁碟上不是順序儲存的。行間碎片對諸如全表掃描和聚簇索引掃描之類的操作有很大的影響，因為這些操作原本能夠從磁碟上的順序儲存的資料收益。
    • 剩餘空間碎片(Free space fragmentation)：資料頁中有大量的空餘空間。這會導致伺服器讀取大量不需要的資料，造成浪費。
  • 對MyISAM表，這三類碎片化都可能發生；但InnoDB不會出現短小的行碎片，它會移動短小的行並重寫到一個片段中。
• 重新整理資料方式：
  • OPTIMIZE TABLE
  • 匯出再匯入
  • 排序演算法重建索引(針對MyISAM)
  • “線上”新增和刪除索引的功能，可以通過先刪除，然後在重新建立索引來消除索引碎片(針對最新版本InnoDB)
  • 通過一個不做任何操作的ALTER TABLE <table> ENGINE = <engine>;來重建表(針對不支援OPTIMIZE TABLE的引擎)
• 應該通過一些實際測量而不是隨意假設來確定是否需要消除索引和表的碎片化，還要考慮資料是否已達到穩定狀態(如果進行碎片整理將資料壓縮到一起，可能會導致後續的更新操作觸發一系列的頁分裂和重組，對效能造成不良的影響直到資料達到新的穩定狀態)

3.6總結

• MySQL和儲存引擎訪問資料的方式，加上索引的特性，使得索引成為一個影響資料訪問的有利而靈活的工作(無論資料實在磁碟還是在記憶體中)
• 大多數情況下都會使用B-Tree索引，其他型別的索引大多隻適用於特殊目的。
• 選擇索引和編寫利用這些索引的查詢時，有如下三個原則始終需要記住：
  • 單行訪問是很慢的。如果伺服器從儲存中讀取一個資料塊只是為了獲取其中的一行，那麼就浪費了很多工作。最好的讀取的塊中能包含儘可能多需要的行。使用索引可以建立位置引用提高效率。
  • 按順序訪問範圍資料是很快的。有兩個原因：順序IO不需要多次磁碟尋道而比隨機IO快很多；如果伺服器能夠按需要的順序讀取資料，就不再需要額外的排序操作，並且GROUP BY查詢也無須再做排序和將行按組進行聚合計算。
  • 索引覆蓋查詢是很快的。如果一個索引包含了查詢需要的所有列，那麼儲存引擎就不需要再回表查詢行，避免了大量的單行訪問。
• 編寫查詢語句應該儘可能選擇合適的索引以避免單行查詢，儘可能地使用資料原生順序而避免額外的資料排序操作，並儘可能使用索引覆蓋查詢。
• 對某些查詢不可能建立一個“三星”索引，必須要有所取捨，或者尋求替代策略(例如反正規化話、或者提前計算彙總表)
• 理解索引的工作原理來建立最適合的索引
• 判斷為一個系統建立的索引的合理性：按響應時間對查詢進行分析。
  • 找出那些消耗時間最長的或給伺服器帶來最大壓力的查詢
  • 檢查這些查詢的schema，SQL和索引結構
  • 判斷是否有查詢掃描了太多的行，是否做了很多額外的排序或者使用了臨時表，是否使用隨機IO訪問資料，或者是有太多回表查詢那些不在索引中的列的操作。
• 如果一個查詢無法從所有可能的索引中獲益，則應該看看是否可以建立一個更合適的索引來提升效能。如果不行，也要嘗試是否可以重寫該查詢，將其轉化成一個能夠高效利用現有索引或者新建立索引的查詢。