[Mysql]索引

Duancf發表於2024-07-05
MySql索引

MySQL索引詳解

索引介紹

索引是一種用於快速查詢和檢索資料的資料結構，其本質可以看成是一種排序好的資料結構。
索引的作用就相當於書的目錄。打個比方: 我們在查字典的時候，如果沒有目錄，那我們就只能一頁一頁的去找我們需要查的那個字，速度很慢。如果有目錄了，我們只需要先去目錄裡查詢字的位置，然後直接翻到那一頁就行了。
索引底層資料結構存在很多種型別，常見的索引結構有: B 樹， B+樹 和 Hash、紅黑樹。
在 MySQL 中，無論是 Innodb 還是 MyIsam，都使用了 B+樹作為索引結構。

索引的優缺點
優點：
使用索引可以大大加快 資料的檢索速度（大大減少檢索的資料量）, 這也是建立索引的最主要的原因。
透過建立唯一性索引，可以保證資料庫表中每一行資料的唯一性。
缺點：
建立索引和維護索引需要耗費許多時間。當對錶中的資料進行增刪改的時候，如果資料有索引，那麼索引也需要動態的修改，會降低 SQL 執行效率。
索引需要使用物理檔案儲存，也會耗費一定空間。
但是，使用索引一定能提高查詢效能嗎?
大多數情況下，索引查詢都是比全表掃描要快的。但是如果資料庫的資料量不大，那麼使用索引也不一定能夠帶來很大提升。

索引底層資料結構選型

Hash 表

雜湊表是鍵值對的集合，透過鍵(key)即可快速取出對應的值(value)，因此雜湊表可以快速檢索資料（接近 O（1））。
為何能夠透過 key 快速取出 value 呢？ 原因在於 雜湊演算法（也叫雜湊演算法）。透過雜湊演算法，我們可以快速找到 key 對應的 index，找到了 index 也就找到了對應的 value。

hash = hashfunc(key)
index = hash % array_size

但是雜湊演算法有個 Hash 衝突 問題，也就是說多個不同的 key 最後得到的 index 相同。通常情況下，我們常用的解決辦法是 鏈地址法。鏈地址法就是將雜湊衝突資料存放在連結串列中。就比如 JDK1.8 之前 HashMap 就是透過鏈地址法來解決雜湊衝突的。不過，JDK1.8 以後HashMap為了減少連結串列過長的時候搜尋時間過長引入了紅黑樹。

為了減少 Hash 衝突的發生，一個好的雜湊函式應該“均勻地”將資料分佈在整個可能的雜湊值集合中。

MySQL 的 InnoDB 儲存引擎不直接支援常規的雜湊索引，但是，InnoDB 儲存引擎中存在一種特殊的“自適應雜湊索引”（Adaptive Hash Index），自適應雜湊索引並不是傳統意義上的純雜湊索引，而是結合了 B+Tree 和雜湊索引的特點，以便更好地適應實際應用中的資料訪問模式和效能需求。自適應雜湊索引的每個雜湊桶實際上是一個小型的 B+Tree 結構。這個 B+Tree 結構可以儲存多個鍵值對，而不僅僅是一個鍵。這有助於減少雜湊衝突鏈的長度，提高了索引的效率。關於 Adaptive Hash Index 的詳細介紹，可以檢視 MySQL 各種“Buffer”之 Adaptive Hash Index 這篇文章。

既然雜湊表這麼快，為什麼 MySQL 沒有使用其作為索引的資料結構呢？
主要是因為 Hash 索引不支援順序和範圍查詢。假如我們要對錶中的資料進行排序或者進行範圍查詢，那 Hash 索引可就不行了。
並且，每次 IO 只能取一個。

試想一種情況:

SELECT * FROM tb1 WHERE id < 500;

在這種範圍查詢中，B+優勢非常大，直接遍歷比 500 小的葉子節點就夠了。而 Hash 索引是根據 hash 演算法來定位的，難不成還要把 1 - 499 的資料，每個都進行一次 hash 計算來定位嗎?這就是 Hash 最大的缺點了。

二叉查詢樹(BST)

二叉查詢樹（Binary Search Tree）是一種基於二叉樹的資料結構，它具有以下特點：
左子樹所有節點的值均小於根節點的值。
右子樹所有節點的值均大於根節點的值。
左右子樹也分別為二叉查詢樹。
當二叉查詢樹是平衡的時候，也就是樹的每個節點的左右子樹深度相差不超過 1 的時候，查詢的時間複雜度為 O(log2(N))，具有比較高的效率。
然而，當二叉查詢樹不平衡時，例如在最壞情況下（有序插入節點），樹會退化成線性連結串列（也被稱為斜樹），導致查詢效率急劇下降，時間複雜退化為 O（N）。

也就是說，二叉查詢樹的效能非常依賴於它的平衡程度，這就導致其不適合作為 MySQL 底層索引的資料結構。

為了解決這個問題，並提高查詢效率，人們發明了多種在二叉查詢樹基礎上的改進型資料結構，如平衡二叉樹、B-Tree、B+Tree 等。

AVL 樹

AVL 樹是電腦科學中最早被發明的自平衡二叉查詢樹，它的名稱來自於發明者 G.M. Adelson-Velsky 和 E.M. Landis 的名字縮寫。AVL 樹的特點是保證任何節點的左右子樹高度之差不超過 1，因此也被稱為高度平衡二叉樹，它的查詢、插入和刪除在平均和最壞情況下的時間複雜度都是 O(logn)。

AVL 樹採用了旋轉操作來保持平衡。主要有四種旋轉操作：
LL 旋轉、
RR 旋轉、
LR 旋轉和
RL 旋轉。其中 LL 旋轉和 RR 旋轉分別用於處理左左和右右失衡，而 LR 旋轉和 RL 旋轉則用於處理左右和右左失衡。

由於 AVL 樹需要頻繁地進行旋轉操作來保持平衡，因此會有較大的計算開銷進而降低了查詢效能。並且， 在使用 AVL 樹時，每個樹節點僅儲存一個資料，而每次進行磁碟 IO 時只能讀取一個節點的資料，如果需要查詢的資料分佈在多個節點上，那麼就需要進行多次磁碟 IO。 磁碟 IO 是一項耗時的操作，在設計資料庫索引時，我們需要優先考慮如何最大限度地減少磁碟 IO 操作的次數。

實際應用中，AVL 樹使用的並不多。

紅黑樹

紅黑樹是一種自平衡二叉查詢樹，透過在插入和刪除節點時進行顏色變換和旋轉操作，使得樹始終保持平衡狀態，它具有以下特點：

每個節點非紅即黑；
根節點總是黑色的；
每個葉子節點都是黑色的空節點（NIL 節點）；
如果節點是紅色的，則它的子節點必須是黑色的（反之不一定）；
從根節點到葉節點或空子節點的每條路徑，必須包含相同數目的黑色節點（即相同的黑色高度）。

和 AVL 樹不同的是，紅黑樹並不追求嚴格的平衡，而是大致的平衡。正因如此，紅黑樹的查詢效率稍有下降，因為紅黑樹的平衡性相對較弱，可能會導致樹的高度較高，這可能會導致一些資料需要進行多次磁碟 IO 操作才能查詢到，這也是 MySQL 沒有選擇紅黑樹的主要原因。也正因如此，紅黑樹的插入和刪除操作效率大大提高了，因為紅黑樹在插入和刪除節點時只需進行 O(1) 次數的旋轉和變色操作，即可保持基本平衡狀態，而不需要像 AVL 樹一樣進行 O(logn) 次數的旋轉操作。

紅黑樹的應用還是比較廣泛的，TreeMap、TreeSet 以及 JDK1.8 的 HashMap 底層都用到了紅黑樹。對於資料在記憶體中的這種情況來說，紅黑樹的表現是非常優異的。

B 樹& B+樹

B 樹也稱 B-樹,全稱為 多路平衡查詢樹 ，B+ 樹是 B 樹的一種變體。B 樹和 B+樹中的 B 是 Balanced （平衡）的意思。

目前大部分資料庫系統及檔案系統都採用 B-Tree 或其變種 B+Tree 作為索引結構。

B 樹& B+樹兩者有何異同呢？

B 樹的所有節點既存放鍵(key) 也存放資料(data)，而 B+樹只有葉子節點存放 key 和 data，其他內節點只存放 key。

B 樹的葉子節點都是獨立的;B+樹的葉子節點有一條引用鏈指向與它相鄰的葉子節點。
B 樹的檢索的過程相當於對範圍內的每個節點的關鍵字做二分查詢，可能還沒有到達葉子節點，檢索就結束了。而 B+樹的檢索效率就很穩定了，任何查詢都是從根節點到葉子節點的過程，葉子節點的順序檢索很明顯。
在 B 樹中進行範圍查詢時，首先找到要查詢的下限，然後對 B 樹進行中序遍歷，直到找到查詢的上限；而 B+樹的範圍查詢，只需要對連結串列進行遍歷即可。
綜上，B+樹與 B 樹相比，具備更少的 IO 次數、更穩定的查詢效率和更適於範圍查詢這些優勢。

在 MySQL 中，MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作為索引結構，但是，兩者的實現方式不太一樣。（下面的內容整理自《Java 工程師修煉之道》）

MyISAM 引擎中，B+Tree 葉節點的 data 域存放的是資料記錄的地址。在索引檢索的時候，首先按照 B+Tree 搜尋演算法搜尋索引，如果指定的 Key 存在，則取出其 data 域的值，然後以 data 域的值為地址讀取相應的資料記錄。這被稱為“非聚簇索引（非聚集索引）”。

InnoDB 引擎中，其資料檔案本身就是索引檔案。相比 MyISAM，索引檔案和資料檔案是分離的，其表資料檔案本身就是按 B+Tree 組織的一個索引結構，樹的葉節點 data 域儲存了完整的資料記錄。這個索引的 key 是資料表的主鍵，因此 InnoDB 表資料檔案本身就是主索引。這被稱為“聚簇索引（聚集索引）”，而其餘的索引都作為 輔助索引 ，輔助索引的 data 域儲存相應記錄主鍵的值而不是地址，這也是和 MyISAM 不同的地方。在根據主索引搜尋時，直接找到 key 所在的節點即可取出資料；在根據輔助索引查詢時，則需要先取出主鍵的值，再走一遍主索引。 因此，在設計表的時候，不建議使用過長的欄位作為主鍵，也不建議使用非單調的欄位作為主鍵，這樣會造成主索引頻繁分裂。

B樹和B+Tree每一個節點實際上可以理解為是一個檔案頁 mysql預設給一個檔案頁分配16k儲存
B樹的每個節點都會儲存data資料，而B+Tree的話非葉子節點是儲存的索引（冗餘），不儲存data資料，這樣每一頁檔案頁能儲存的節點就很多，樹的高度就可以得到很好的控制，樹的高度越高，從磁碟load節點到記憶體對比的次數就會越多，磁碟I/O是費時。
所以B+Tree在樹高度相同的情況下能夠儲存更多的索引資料，間接的減少了磁碟的I/O操作，B+Tree的I/O次數會更加穩定一些。
還有就是從範圍查詢的角度上來說B+Tree也具備絕對的優勢，因為B+Tree在每個相鄰的葉子節點之間都有互相指向
B+Tree在全表掃描的情況下也是比較佔優勢的，因為B+Tree的資料都是儲存在非葉子節點的，所以只需要掃描葉子節點就可以拿到全部資料了，B Tree的話就需要從頭遍歷整顆樹
————————————————

                        版權宣告：本文為博主原創文章，遵循 CC 4.0 BY 版權協議，轉載請附上原文出處連結和本宣告。

原文連結：https://blog.csdn.net/qq_42285585/article/details/128979081

索引型別總結

按照資料結構維度劃分：

B+Tree 索引：MySQL 裡預設和最常用的索引型別。只有葉子節點儲存 value，非葉子節點只有指標和 key。儲存引擎 MyISAM 和 InnoDB 實現 BTree 索引都是使用 B+Tree，但二者實現方式不一樣（前面已經介紹了）。

雜湊索引：類似鍵值對的形式，一次即可定位。

RTree 索引：一般不會使用，僅支援 geometry 資料型別，優勢在於範圍查詢，效率較低，通常使用搜尋引擎如 ElasticSearch 代替。

全文索引：對文字的內容進行分詞，進行搜尋。目前只有 CHAR、VARCHAR ，TEXT 列上可以建立全文索引。一般不會使用，效率較低，通常使用搜尋引擎如 ElasticSearch 代替。

按照底層儲存方式角度劃分：

聚簇索引（聚集索引）：索引結構和資料一起存放的索引，InnoDB 中的主鍵索引就屬於聚簇索引。
非聚簇索引（非聚集索引）：索引結構和資料分開存放的索引，二級索引(輔助索引)就屬於非聚簇索引。MySQL 的 MyISAM 引擎，不管主鍵還是非主鍵，使用的都是非聚簇索引。

按照應用維度劃分：

主鍵索引：加速查詢 + 列值唯一（不可以有 NULL）+ 表中只有一個。
普通索引：僅加速查詢。
唯一索引：加速查詢 + 列值唯一（可以有 NULL）。
覆蓋索引：一個索引包含（或者說覆蓋）所有需要查詢的欄位的值。
聯合索引：多列值組成一個索引，專門用於組合搜尋，其效率大於索引合併。
全文索引：對文字的內容進行分詞，進行搜尋。目前只有 CHAR、VARCHAR ，TEXT 列上可以建立全文索引。一般不會使用，效率較低，通常使用搜尋引擎如 ElasticSearch 代替。
MySQL 8.x 中實現的索引新特性：

隱藏索引：也稱為不可見索引，不會被最佳化器使用，但是仍然需要維護，通常會軟刪除和灰度釋出的場景中使用。主鍵不能設定為隱藏（包括顯式設定或隱式設定）。
降序索引：之前的版本就支援透過 desc 來指定索引為降序，但實際上建立的仍然是常規的升序索引。直到 MySQL 8.x 版本才開始真正支援降序索引。另外，在 MySQL 8.x 版本中，不再對 GROUP BY 語句進行隱式排序。
函式索引：從 MySQL 8.0.13 版本開始支援在索引中使用函式或者表示式的值，也就是在索引中可以包含函式或者表示式。

主鍵索引(Primary Key)

資料表的主鍵列使用的就是主鍵索引。

一張資料表有隻能有一個主鍵，並且主鍵不能為 null，不能重複。

在 MySQL 的 InnoDB 的表中，當沒有顯示的指定表的主鍵時，InnoDB 會自動先檢查表中是否有唯一索引且不允許存在 null 值的欄位，如果有，則選擇該欄位為預設的主鍵，否則 InnoDB 將會自動建立一個 6Byte 的自增主鍵。

回表查詢

回表查詢是指，當我們在索引裡沒查到想要的資料的時候，要根據索引中的主鍵值去再進行查詢

二級索引

二級索引（Secondary Index）又稱為輔助索引，是因為二級索引的葉子節點儲存的資料是主鍵。也就是說，透過二級索引，可以定位主鍵的位置。

唯一索引，普通索引，字首索引等索引屬於二級索引。

唯一索引(Unique Key)

唯一索引也是一種約束。唯一索引的屬性列不能出現重複的資料，但是允許資料為 NULL，一張表允許建立多個唯一索引。 建立唯一索引的目的大部分時候都是為了該屬性列的資料的唯一性，而不是為了查詢效率。

普通索引(Index)

普通索引的唯一作用就是為了快速查詢資料，一張表允許建立多個普通索引，並允許資料重複和 NULL。

字首索引(Prefix)

字首索引只適用於字串型別的資料。字首索引是對文字的前幾個字元建立索引，相比普通索引建立的資料更小，
因為只取前幾個字元。

全文索引(Full Text)

全文索引主要是為了檢索大文字資料中的關鍵字的資訊，是目前搜尋引擎資料庫使用的一種技術。Mysql5.6 之前只有 MYISAM 引擎支援全文索引，5.6 之後 InnoDB 也支援了全文索引。

聚簇索引

聚簇索引（Clustered Index）即索引結構和資料一起存放的索引，並不是一種單獨的索引型別。InnoDB 中的主鍵索引就屬於聚簇索引。

在 MySQL 中，InnoDB 引擎的表的 .ibd檔案就包含了該表的索引和資料，對於 InnoDB 引擎表來說，該表的索引(B+樹)的每個非葉子節點儲存索引，葉子節點儲存索引和索引對應的資料。

聚簇索引的優缺點
優點：

查詢速度非常快：聚簇索引的查詢速度非常的快，因為整個 B+樹本身就是一顆多叉平衡樹，葉子節點也都是有序的，定位到索引的節點，就相當於定位到了資料。相比於非聚簇索引， 聚簇索引少了一次讀取資料的 IO 操作。

對排序查詢和範圍查詢最佳化：聚簇索引對於主鍵的排序查詢和範圍查詢速度非常快。
缺點：

依賴於有序的資料：因為 B+樹是多路平衡樹，如果索引的資料不是有序的，那麼就需要在插入時排序，如果資料是整型還好，否則類似於字串或 UUID 這種又長又難比較的資料，插入或查詢的速度肯定比較慢。
更新代價大：如果對索引列的資料被修改時，那麼對應的索引也將會被修改，而且聚簇索引的葉子節點還存放著資料，修改代價肯定是較大的，所以對於主鍵索引來說，主鍵一般都是不可被修改的。

非聚簇索引

非聚簇索引(Non-Clustered Index)即索引結構和資料分開存放的索引，並不是一種單獨的索引型別。二級索引(輔助索引)就屬於非聚簇索引。MySQL 的 MyISAM 引擎，不管主鍵還是非主鍵，使用的都是非聚簇索引。

非聚簇索引的葉子節點並不一定存放資料的指標，
因為二級索引的葉子節點就存放的是主鍵，根據主鍵再回表查資料。

非聚簇索引的優缺點
優點：

更新代價比聚簇索引要小 。非聚簇索引的更新代價就沒有聚簇索引那麼大了，非聚簇索引的葉子節點是不存放資料的

缺點：

依賴於有序的資料:跟聚簇索引一樣，非聚簇索引也依賴於有序的資料
可能會二次查詢(回表):這應該是非聚簇索引最大的缺點了。 當查到索引對應的指標或主鍵後，可能還需要根據指標或主鍵再到資料檔案或表中查詢。

非聚簇索引一定回表查詢嗎(覆蓋索引)?

非聚簇索引不一定回表查詢。

試想一種情況，使用者準備使用 SQL 查詢使用者名稱，而使用者名稱欄位正好建立了索引。

SELECT name FROM table WHERE name='guang19';
那麼這個索引的 key 本身就是 name，查到對應的 name 直接返回就行了，無需回表查詢。

即使是 MYISAM 也是這樣，雖然 MYISAM 的主鍵索引確實需要回表，因為它的主鍵索引的葉子節點存放的是指標。但是！如果 SQL 查的就是主鍵呢?

SELECT id FROM table WHERE id=1;
主鍵索引本身的 key 就是主鍵，查到返回就行了。這種情況就稱之為覆蓋索引了。

覆蓋索引和聯合索引

如果一個索引包含（或者說覆蓋）所有需要查詢的欄位的值，我們就稱之為 覆蓋索引（Covering Index） 。我們知道在 InnoDB 儲存引擎中，如果不是主鍵索引，葉子節點儲存的是主鍵+列值。最終還是要“回表”，也就是要透過主鍵再查詢一次，這樣就會比較慢。而覆蓋索引就是把要查詢出的列和索引是對應的，不做回表操作！

覆蓋索引即需要查詢的欄位正好是索引的欄位，那麼直接根據該索引，就可以查到資料了，而無需回表查詢。

如主鍵索引，如果一條 SQL 需要查詢主鍵，那麼正好根據主鍵索引就可以查到主鍵。再如普通索引，如果一條 SQL 需要查詢 name，name 欄位正好有索引，
那麼直接根據這個索引就可以查到資料，也無需回表。

我們這裡簡單演示一下覆蓋索引的效果。

  1. 建立一個名為 cus_order 的表，來實際測試一下這種排序方式。為了測試方便， cus_order 這張表只有 id、score、name這 3 個欄位。

    CREATE TABLE `cus_order` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `score` int(11) NOT NULL,
  `name` varchar(11) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100000 DEFAULT CHARSET=utf8mb4;

  2. 定義一個簡單的儲存過程（PROCEDURE）來插入 100w 測試資料。

DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `BatchinsertDataToCusOder`(IN start_num INT,IN max_num INT)
BEGIN
      DECLARE i INT default start_num;
      WHILE i < max_num DO
          insert into `cus_order`(`id`, `score`, `name`)
          values (i,RAND() * 1000000,CONCAT('user', i));
          SET i = i + 1;
      END WHILE;
  END;;
DELIMITER ;

儲存過程定義完成之後，我們執行儲存過程即可！

CALL BatchinsertDataToCusOder(1, 1000000); # 插入100w+的隨機資料
等待一會，100w 的測試資料就插入完成了！

3、建立覆蓋索引並使用 EXPLAIN 命令分析。

為了能夠對這 100w 資料按照 score 進行排序，我們需要執行下面的 SQL 語句。

SELECT score,name FROM cus_order ORDER BY score DESC;#降序排序

使用 EXPLAIN 命令分析這條 SQL 語句，透過 Extra 這一列的 Using filesort ，我們發現是沒有用到覆蓋索引的。

不過這也是理所應當，畢竟我們現在還沒有建立索引呢！

我們這裡以 score 和 name 兩個欄位建立聯合索引：
(先按score排序，score相同的時候再按name排序)

ALTER TABLE cus_order ADD INDEX id_score_name(score, name);

建立完成之後，再用 EXPLAIN 命令分析再次分析這條 SQL 語句。

透過 Extra 這一列的 Using index ，說明這條 SQL 語句成功使用了覆蓋索引。

聯合索引

使用表中的多個欄位建立索引，就是 聯合索引，也叫 組合索引 或 複合索引。

以 score 和 name 兩個欄位建立聯合索引：

ALTER TABLE cus_order ADD INDEX id_score_name(score, name);

最左字首匹配原則

最左字首匹配原則指的是，在使用聯合索引時，MySQL 會根據聯合索引中的欄位順序，從左到右依次到查詢條件中去匹配，如果查詢條件中存在與聯合索引中最左側欄位相匹配的欄位，則就會使用該欄位過濾一批資料，直至聯合索引中全部欄位匹配完成，或者在執行過程中遇到範圍查詢（如 >、< ）才會停止匹配。對於 >=、<=、BETWEEN、like 字首匹配的範圍查詢，並不會停止匹配。所以，我們在使用聯合索引時，可以將區分度高的欄位放在最左邊，這也可以過濾更多資料。

聯合索引的最左匹配原則，在遇到範圍查詢（如 >、<）的時候，就會停止匹配，也就是範圍查詢的欄位可以用到聯合索引，但是在範圍查詢欄位後面的欄位無法用到聯合索引。注意，對於 >=、<=、BETWEEN、like 字首匹配的範圍查詢，並不會停止匹配。

相關閱讀：聯合索引的最左匹配原則全網都在說的一個錯誤結論。

索引下推

索引下推（Index Condition Pushdown） 是 MySQL 5.6 版本中提供的一項索引最佳化功能，可以在非聚簇索引遍歷過程中，對索引中包含的欄位先做判斷，過濾掉不符合條件的記錄，減少回表次數。

回表的時機是什麼時候

如果有一條語句中包含幾個條件，
那麼查到滿足第一個條件的資料的時候就要回表嗎？

正確使用索引的一些建議

  • 選擇合適的欄位建立索引

    • 不為 NULL 的欄位：索引欄位的資料應該儘量不為 NULL，因為對於資料為 NULL 的欄位，資料庫較難最佳化。如果欄位頻繁被查詢，但又避免不了為 NULL，建議使用 0,1,true,false 這樣語義較為清晰的短值或短字元作為替代。
    • 被頻繁查詢的欄位：我們建立索引的欄位應該是查詢操作非常頻繁的欄位。
    • 被作為條件查詢的欄位：被作為 WHERE 條件查詢的欄位，應該被考慮建立索引。
    • 頻繁需要排序的欄位：索引已經排序，這樣查詢可以利用索引的排序，加快排序查詢時間。
    • 被經常頻繁用於連線的欄位：經常用於連線的欄位可能是一些外來鍵列，對於外來鍵列並不一定要建立外來鍵，只是說該列涉及到表與表的關係。對於頻繁被連線查詢的欄位，可以考慮建立索引，提高多表連線查詢的效率。

  • 被頻繁更新的欄位應該慎重建立索引
    雖然索引能帶來查詢上的效率，但是維護索引的成本也是不小的。 如果一個欄位不被經常查詢，反而被經常修改，那麼就更不應該在這種欄位上建立索引了。

  • 限制每張表上的索引數量
    索引並不是越多越好，建議單張表索引不超過 5 個！索引可以提高效率同樣可以降低效率。

  • 索引可以增加查詢效率，但同樣也會降低插入和更新的效率，甚至有些情況下會降低查詢效率。
    因為 MySQL 最佳化器在選擇如何最佳化查詢時，會根據統一資訊，對每一個可以用到的索引來進行評估，以生成出一個最好的執行計劃，如果同時有很多個索引都可以用於查詢，就會增加 MySQL 最佳化器生成執行計劃的時間，同樣會降低查詢效能。

  • 儘可能的考慮建立聯合索引而不是單列索引
    因為索引是需要佔用磁碟空間的，可以簡單理解為每個索引都對應著一顆 B+樹。如果一個表的欄位過多，索引過多，那麼當這個表的資料達到一個體量後，索引佔用的空間也是很多的，且修改索引時，耗費的時間也是較多的。如果是聯合索引，多個欄位在一個索引上，那麼將會節約很大磁碟空間，且修改資料的操作效率也會提升。

  • 注意避免冗餘索引
    冗餘索引指的是索引的功能相同，能夠命中索引(a, b)就肯定能命中索引(a) ，那麼索引(a)就是冗餘索引。如（name,city ）和（name ）這兩個索引就是冗餘索引，能夠命中前者的查詢肯定是能夠命中後者的 在大多數情況下，都應該儘量擴充套件已有的索引而不是建立新索引。

  • 字串型別的欄位使用字首索引代替普通索引
    字首索引僅限於字串型別，較普通索引會佔用更小的空間，所以可以考慮使用字首索引帶替普通索引。

相關文章