高效能MySQL-索引

建立索引-高效索引

1.1 索引初體驗

1.1.1 介紹

索引是一種特殊的檔案(InnoDB資料表上的索引是表空間的一個組成部分)，它們包含著對資料表裡所有記錄的引用指標。

索引的作用是做資料的快速檢索，而快速檢索的實現的本質是資料結構。通過不同資料結構的選擇，實現各種資料快速檢索。在資料庫中，高效的查詢演算法是非常重要的，因為資料庫中儲存了大量資料，一個高效的索引能節省巨大的時間。

1.1.2 索引型別

https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
推薦使用上面的網站來視覺化檢視各種資料結構

B-Tree索引

儘管名稱為B-Tree索引，但事實上，其不同引擎對其內部實現結構還是會不一樣，Inno DB使用的是B+Tree這種結構來儲存索引

注意： InnoDB 的資料檔案本身就是索引檔案 即
表資料檔案本身就是按 B+Tree 組織的一個索引結構,這棵樹的葉點data域儲存了完整的資料記錄。這個索引的key是資料表的主鍵,因此 InnoDB 表資料檔案本身就是主索引。

對於MyISAM引擎，其採用了非聚集索引的方式來實現

即資料和索引落在不同的兩個檔案上。MyISAM 在建表時以主鍵作為 KEY 來建立主索引 B+樹，非葉子結點只儲存下一節點的指標，樹的葉子節點存的是對應資料的實體地址。我們拿到這個實體地址後，就可以到 MyISAM 資料檔案中直接定位到具體的資料記錄了。

對於InnoDB引擎，其使用聚集索引的方式來建表

即資料和索引都儲存在同一個檔案 ，對於InnoDB使用的B+Tree結構，其非葉子節點是不儲存資料的，只儲存索引，所有的資料都儲存在葉子節點頁，並且葉子節點儲存的是主鍵ID對應的資料，這也是為什麼Mysql在建表時要求必須指定主鍵的原因。由於資料真正的排序方式只能有一種，所以在每張表中只能存在著一個以主鍵為索引的聚集索引。 因此上面我們才說了InnoDB的資料檔案就是索引檔案

注意：InnoDB只在主鍵索引樹的葉子節點儲存了具體資料，但是其他索引樹卻不存具體資料，而要先找到主鍵，再在主鍵索引樹找到對應的資料。 別問，問就是節省空間，拿時間換空間。這也證明了選擇合適的主鍵的重要性。

B+Tree索引查詢型別：

• 全值匹配

  ​ 和索引中的所有列進行匹配,例如key(A,B,C),匹配(A,B,C)

• 匹配最左字首

  ​ 例如key(A,B,C),可以匹配(A) (A,B) (A,B,C)

• 匹配列字首

  ​ 例如資料changtong，加上索引後可以匹配(c*),這也就是為什麼我們對某一新增了索引的列使用模糊查詢時like a% 就會使用索引，如果是%a就只能夠完全檢索了

• 匹配範圍值

  ​ B-tree索引是順序儲存資料的，因此能夠使用索引進行範圍匹配

• 精確匹配某一列並範圍匹配另外一列

  ​ 例如key(A,B)可以匹配(A,B*)

• 只訪問索引的查詢

  ​ 這裡主要是覆蓋索引，只訪問索引，不訪問資料行

Hash索引

顧名思義，使用雜湊表實現，這也就意味著其只有對精確匹配才有效，這個實現有點像HashMap，根據資料的Hash值確定位置，這裡索引只存雜湊值和行指標，不存資料，由於Hash演算法的特點，也無法排序

InnoDB一般不使用Hash索引，但是其有一項功能叫“自適應雜湊索引”，當它發現某些資料訪問非常頻繁，可能會基於B-Tree的基礎上再建立一個Hash索引，該過程自發且不可控，可以選擇關閉。

綜上所述，對於使用InnoDB引擎的我們來說，Hash索引瞭解一下就好了，不過這也給我們提供了一個思路，例如我們儲存網路連結這樣的無序長字串，是不是可以使用上Hash演算法？我們可以新建一列url_hash，存放該連結對應的Hash值，然後我們對該Hash值建立索引，就能得到更好的查詢體驗了，在查詢時使用以下查詢語句即可

select id from url where url="http://changtong1819.top" 
and url_hash = CR32("http://changtong1819.top")

當然，缺點是我們可能需要使用觸發器等工具維護我們的Hash值

空間索引

MyISAM支援該索引

全文索引

全文索引是一種特殊型別的索引，它查詢的是文字中的關鍵詞，而不是簡單的where

1.2 索引的優點

索引可以讓伺服器快速地定位到表的指定位置。但是這並不是索引的唯一作用，到目前為止可以看到，根據建立索引的資料結構不同，索引也有一些其他的附加作用。

最常見的B-Tree索引，由於其按照順序儲存資料，所以 MySQL可以用來做ORDER BY和GROUPBY操作。因為資料是有序的，所以B-Tree也就會將相關的列值都儲存在一起。

最後，因為索引中儲存了實際的列值，所以某些查詢只使用索引就能夠完成全部查詢。據此特性，總結下來索引有如下三個優點:

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

注意：索引並非總是好的解決方案

只有當索引幫助儲存引擎快速查詢到記錄帶來的好處大於其帶來的額外工作時，索引才是有效的。對於非常小的表，大部分情況下簡單的全表掃描更高效。對於中到大型的表，索引就非常有效。但對於特大型的表，建立和使用索引的代價將隨之增長。這種情況下，則需要一種技術可以直接區分出查詢需要的一組資料，而不是一條記錄一條記錄地匹配。例如可以使用分割槽技術。

1.3 高效能索引

1.3.1 獨立的列

如果查詢的列不是獨立的，則不會使用索引。獨立的列意味著索引列不能是表示式、函式的一部分

例如：

select id from user where id + 1 = 10

儘管上面的寫法看著很智障，但貌似我以前好像寫過這樣的查詢語句，靠！總而言之，能在業務層簡化就儘量簡化，直接寫id = 9就會使用索引了。

1.3.2 字首索引和索引選擇性

當我們需要索引長字串怎麼辦？這會讓索引變得大且慢，前面提到了仿Hash索引是一個思路，也有其它類似方法，總而言之就是簡化索引欄位長度

我們如果儲存了一列全是類似"changtong1819hahahahahahahahhhehehehehhehehexixixiixixii "這樣的長資料列的話，我們可以增加一列，這一列只儲存上面資料列中的前10個字元，對該列新增索引，在查詢時匹配這兩列能夠極大的提高查詢速度。

那麼問題來了，具體擷取多少個字元呢？什麼是索引選擇性?
對於我們上面的例子來說，擷取太長，匹配效果好，但是既然這個索引還是長，那我要它幹嘛？擷取太短的話，就會導致我們該索引的相同值太多了，我們知道B-Tree好就好在其實順序儲存的，當相同值越多，就導致索引效果會越差，即選擇性差。因此，我們要權衡兩種效果來選擇合適的長度，這肯定是和實際資料庫的存放資料相關了。

1.3.3 多列索引

我們知道，每建立一個索引欄位就會產生一個B-Tree來儲存對應的索引，那麼我們分別對兩個欄位建立了索引會有什麼效果？我們該如何查詢呢？

MySQL引入了索引合併策略，在一定程度上可以幫助我們通過多個單列索引來定位到指定的資料，當然，儘管有該策略，我們應該也能想到其效率也不會有多高。這是MySQL對我們的查詢操作進行的優化，但是我們要儘量建立合適的索引。

1.3.4 索引列順序

我們遇到的最容易引起困惑的問題就是索引列的順序。正確的順序有利於使用該索引的查詢，並且同時需要考慮如何更好地滿足排序和分組的需要。當然，這一切都是在於我們使用了B-Tree這種順序儲存結構的情況下

通常情況下我們將選擇性最高的列放在索引的最前列，這有利於我們的where語句。但考慮到其他情況的話，這樣做就可能不是最好的選擇了。我們使用where語句時，要充分考慮到聯合索引的執行與否。

1.3.5 聚簇索引

聚簇索引並不是一種索引型別，而是一種資料儲存方式，前面提到過了，InnoDB的聚簇索引即是在一個結構中儲存了索引與資料行。

如果沒有定義主鍵，InnoDB會選擇一個唯一的非空索引代替。如果沒有這樣的索引，InnoDB會隱式定義一個主鍵來作為聚簇索引。InnoDB 只聚集在同一個頁面中的記錄。包含相鄰鍵值的頁面可能會相距甚遠。

注意：InnoDB只會對主鍵索引才回使用聚簇索引這一儲存方式，同時InnoDB表資料的儲存形式其實就是對主鍵的聚簇索引。即對於InnoDB，聚簇索引就是表。

我們平時手動建立的索引稱為二級索引，而通過二級索引查詢行，儲存引擎需要找到二級索引的葉子節點獲得對應的主鍵值（二級索引沒有行資料），然後根據這個值去聚簇索引中查詢到對應的行。對於InnoDB，自適應雜湊索引能夠減少這樣的重複工作。

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

如果正在使用InnoDB表並且沒有什麼資料需要聚集，那麼可以定義一個代理鍵(surrogate key)作為主鍵，這種主鍵的資料應該和應用無關，最簡單的方法是使用AUTO_INCREMENT自增列。這樣可以保證資料行是按順序寫入，對於根據主鍵做關聯操作的效能也會更好。

最好避免隨機的（不連續且值的分佈範圍非常大）聚簇索引，特別是對於IО密集型的應用。例如，從效能的角度考慮，使用UUID來作為聚簇索引則會很糟糕：它使得聚簇索引的插入變得完全隨機，這是最壞的情況，使得資料沒有任何聚集特性。

使用InnoDB時應該儘可能地按主鍵順序插入資料，並且儘可能地使用單調增加的聚簇鍵的值來插入新行。

1.3.6 覆蓋索引

如果索引的葉子節點中已經包含要查詢的資料，那麼就不需要再回表查詢

如果一個索引包含（或者說覆蓋）所有需要查詢的欄位的值，我們就稱之為“覆蓋索引”。

MySQL不能在索引中執行LIKE操作。這是底層儲存引擎API的限制，MySQL 5.5和更早的版本中只允許在索引中做簡單比較操作（例如等於、不等於以及大於)。

MySQL 能在索引中做最左字首匹配的LIKE比較，因為該操作可以轉換為簡單的比較操作，但是如果是萬用字元開頭的LIKE查詢，儲存引擎就無法做比較匹配。這種情況下，MySQL伺服器只能提取資料行的值而不是索引值來做比較。

1.3.7 使用索引掃描

MySQL有兩種方式可以生成有序的結果：通過排序操作；或者按索引順序掃描。

掃描索引本身是很快的，因為只需要從一條索引記錄移動到緊接著的下一條記錄。但如果索引不能覆蓋查詢所需的全部列，那就不得不每掃描一條索引記錄就都回表查詢一次對應的行。這基本上都是隨機I/o，因此按索引順序讀取資料的速度通常要比順序地全表掃描慢，尤其是在IO密集型的工作負載時。

MySQL可以使用同一個索引既滿足排序，又用於查詢行。因此，如果可能，設計索引時應該儘可能地同時滿足這兩種任務，這樣是最好的。

注意：只有當索引的列順序和ORDER BY子句的順序完全一致，並且所有列的排序方向（倒序或正序）都一樣時，MySQL才能夠使用索引來對結果做排序。如果查詢需要關聯多張表，則只有當ORDER BY子句引用的欄位全部為第一個表時，才能使用索引做排序。ORDER BY子句和查詢型查詢的限制是一樣的：需要滿足索引的最左字首的要求。

1.3.8 壓縮索引

MyISAM使用字首壓縮來減少索引的大小，從而讓更多的索引可以放入記憶體中，這在某些情況下能極大地提高效能。預設只壓縮字串，但通過引數設定也可以對整數做壓縮。

1.3.9 冗餘和重複索引

MySQL允許在相同列上建立多個索引，無論是有意的還是無意的。MySQL需要單獨維護重複的索引，並且優化器在優化查詢的時候也需要逐個地進行考慮，這會影響效能。

重複索引是指在相同的列上按照相同的順序建立的相同型別的索引。應該避免這樣建立重複索引，發現以後也應該立即移除。

通常情況下，我們不會寫出兩個KEY(A)這樣的索引。但是像主鍵、外來鍵MySQL會自動建立索引，我們再去新增索引就會導致重複了。至於冗餘索引，我們建立了KEY(A,B)之後在建立KEY(A)就很明顯冗餘了。

1.3.10 未使用的索引

有些索引可能從未被使用，可以用工具檢視，然後刪除

1.3.11 索引和鎖

索引可以讓查詢鎖定更少的行。如果你的查詢從不訪問那些不需要的行，那麼就會鎖定更少的行，從兩個方面來看這對效能都有好處。首先，雖然InnoDB的行鎖效率很高，記憶體使用也很少，但是鎖定行的時候仍然會帶來額外開銷﹔其次，鎖定超過需要的行會增加鎖爭用並減少併發性。

MySQL 5.1之後，InnoDB可以在服務端過濾掉行之後就釋放鎖，此前版本需要事務提交後才釋放鎖

如果不能使用索引查詢和鎖定行的話，MySQL會做全表掃描並鎖住所有的行，而不管是不是需要。

InnoDB在二級索引上使用共享(讀）鎖，但訪問主鍵索引需要排他（寫）鎖。

1.4 合理使用索引

1.4.1 支援多種過濾條件

建立索引時我們需要考慮哪些列擁有很多不同的取值，哪些列在WHERE子句中出現得最頻繁。在有更多不同值的列上建立索引的選擇性會更好。一般來說這樣做都是對的，因為可以讓MySQL更有效地過濾掉不需要的行。

像性別這樣的選擇性就兩個的欄位，單獨新增個索引毫無必要，索引的最主要目的就是快速查詢、定位資料。而性別這樣的資料在表中的定位性比較差，即便加上索引，優化器也是會認為此索引使用的成本過高，而不會使用索引。

但並不是性別就一定不能成為索引欄位，如果該表經常出現性別和其他欄位往往同時出現在where後面，那麼你可以將性別和其他欄位作為聯合索引。即能夠幫助我們多過濾一些資料行還是好的。

1.4.2 避免多範圍條件

前面我們提到了，InnoDB使用B+Tree作為索引儲存結構，而這是一種順序儲存結構，因此InnoDB是支援索引的範圍匹配的，但是這並不意味著我們可以隨意在where後面新增多個範圍匹配。

不過我嘗試了幾種多範圍條件的查詢，發現都使用了索引

1.4.3 優化排序

對於那些選擇性非常低的列，可以增加一些特殊的索引來做排序。例如，可以建立(sex,x)索引用於對x欄位和性別欄位的查詢。