MySQL 索引深入解析及優化政策

MySQL索引無疑是資料庫性能優化的重要手段。正确使用索引，可以大幅度提高查詢性能。本文将深入探讨MySQL的各種索引類型：聚簇索引、二級索引（普通索引和唯一索引）、聯合索引、覆寫索引，并分享相關的優化政策。

聚簇索引（主鍵索引）

在MySQL的InnoDB存儲引擎中，表中的資料實際上是按照主鍵順序存儲的，這就是所謂的聚簇索引。是以，基于主鍵的查詢性能非常高。 聚簇索引是InnoDB引擎的預設索引類型，這種索引的特點是表中的記錄是按照主鍵的順序來存儲的。這意味着主鍵查找會非常快，因為一旦找到了主鍵，對應的記錄就找到了。然而，這也意味着任何對主鍵的插入、删除和修改都可能涉及到資料的實體移動，因為InnoDB需要保持記錄的實體順序與主鍵的順序一緻。

CREATE TABLE users (
    id INT AUTO_INCREMENT,
    username VARCHAR(50),
    email VARCHAR(50),
    PRIMARY KEY (id)
);           

優化政策：選擇一個能唯一辨別記錄并且增長平穩的列作為主鍵，如自增ID，UUID等。

二級索引（普通索引和唯一索引）

普通索引

普通索引是最基本的索引，它沒有任何限制。使用普通索引可以提高查詢性能。 普通索引，也就是非主鍵的索引，其實是一種包含了索引字段和主鍵的映射表。當你進行索引字段的查詢時，InnoDB引擎會首先找到索引，然後通過索引找到對應的主鍵，再通過主鍵查詢到資料。

CREATE INDEX idx_username ON users (username);           

唯一索引

唯一索引是在普通索引的基礎上，增加了列值唯一的限制。使用唯一索引可以既提高查詢性能，又可以保證資料的唯一性。 唯一索引與普通索引在結構上沒有差別，都是包含了索引字段和主鍵的映射表。唯一的差別在于唯一索引要求索引字段的值必須是唯一的，不能出現重複。

CREATE UNIQUE INDEX idx_email ON users (email);           

優化政策：考慮查詢需求和資料更新的頻率，為常用查詢字段建立索引，但是要注意不要過度索引，因為每個額外的索引都會在插入和更新資料時增加額外的開銷。

聯合索引

聯合索引是包含了多個字段的索引。聯合索引遵循最左字首原則，即如果查詢條件沒有使用聯合索引的最左側的列，則該索引不會被使用。InnoDB引擎會按照索引定義的順序，先比較第一個字段，如果第一個字段相等，再比較第二個字段，依次類推。是以，可以利用聯合索引的字首進行查詢，例如，如果有一個包含(a, b, c)三個字段的聯合索引，那麼可以使用這個索引進行a，(a, b)，(a, b, c)的查詢，這就是最左字首原則。

CREATE INDEX idx_username_email ON users (username, email);           

優化政策：為常用的多列查詢建立聯合索引，列的順序應該根據查詢條件的常用模式來确定。但要注意，聯合索引的列數越多，索引的開銷就越大。

覆寫索引

覆寫索引是指一個查詢的所有列都被一個索引覆寫，是以查詢可以隻通過索引就擷取到所需的資料，無需通路資料行。這是因為索引不僅包含了索引字段的值，也包含了主鍵的值。是以，如果一個查詢的所有列都在索引中，那麼InnoDB引擎可以直接傳回索引中的資料，無需再通過主鍵查詢資料行。

SELECT username FROM users WHERE username = 'john';           

在這個查詢中，如果username列有索引，那麼這個查詢就是一個覆寫索引查詢，因為它隻需要通路索引就可以擷取所有需要的資料。 優化政策：考慮查詢的需求，如果某些查詢隻需要擷取某幾個列的資料，可以考慮建立覆寫這些列的索引，以避免通路資料行，提高查詢性能。但要注意，如果覆寫索引的列數過多，可能會增大索引的大小，影響寫操作的性能。

索引的使用注意事項和優化建議

索引并非越多越好

雖然索引可以提高查詢性能，但是每個索引都需要存儲空間，且會增加寫操作（插入、更新、删除）的開銷。是以，需要在查詢性能和寫操作性能之間找到一個平衡，避免無謂的索引。

選擇合适的索引列

應該為頻繁出現在WHERE子句中的列、用于JOIN操作的列、需要排序的列（ORDER BY子句）、需要分組的列（GROUP BY子句）建立索引。

了解并利用最左字首原則

對于聯合索引，MySQL會遵循最左字首原則。是以，當設計聯合索引時，應該将最常用作為查詢條件的列放在聯合索引的最左邊。

避免在索引列上使用函數或表達式

這會導緻索引失效，因為MySQL無法對函數或表達式的結果進行索引。

定期審查和優化索引

随着業務的發展，原有的索引可能不再适用，新的查詢模式可能需要新的索引。是以，應該定期審查和優化索引，以适應業務的變化。

使用索引的情況

WHERE子句中的條件列

當查詢語句中包含WHERE子句，并且條件列上有索引時，MySQL會使用索引來加速查找過程。例如，如果我們在username列上建立了索引，那麼以下查詢會使用索引：

SELECT * FROM users WHERE username = 'John';           

JOIN操作的列

在執行JOIN操作時，如果兩個表之間的JOIN列上有索引，MySQL會使用索引來加速JOIN過程。例如，以下查詢會使用users表的id索引和orders表的user_id索引：

SELECT * FROM users JOIN orders ON users.id = orders.user_id;           

ORDER BY和GROUP BY子句的列

當查詢語句包含ORDER BY或GROUP BY子句，如果對應的列上有索引，MySQL會使用索引來加速排序和分組操作。例如，以下查詢會使用username索引：

SELECT * FROM users ORDER BY username;
SELECT username, COUNT(*) FROM users GROUP BY username;           

覆寫索引

如果查詢的所有列都包含在索引中，MySQL會直接使用索引，而不需要通路表中的其他資料。這被稱為“覆寫索引”。例如，以下查詢會使用username索引：

SELECT username FROM users WHERE username = 'John';           

不使用索引的情況

雖然索引在許多情況下可以提高查詢性能，但在某些情況下，MySQL可能不會使用索引，包括以下情況：

索引列上使用函數或表達式

如果在索引列上使用了函數或表達式，那麼MySQL無法使用索引。例如，以下查詢無法使用birthday索引：

SELECT * FROM users WHERE MONTH(birthday) = 1;           

非最左字首查詢

對于聯合索引，如果查詢條件不包含索引的最左部分，那麼MySQL無法使用索引。例如，如果我們有一個(username, email)的聯合索引，以下查詢無法使用索引：

SELECT * FROM users WHERE email = '[email protected]';           

全表掃描更快時

如果表中的資料量很小，或者查詢的結果集占了表中的大部分資料，那麼全表掃描可能會比使用索引更快。因為在這種情況下，讀取整個表到記憶體中，然後進行掃描可能會比進行索引查找更快。 LIKE操作符以通配符開始：如果LIKE操作符的參數以通配符開始，那麼MySQL無法使用索引。例如，以下查詢無法使用username索引：

SELECT * FROM users WHERE username LIKE '%John';           

NULL值和索引

如果列包含NULL值，并且你在查詢中使用IS NULL或IS NOT NULL，那麼MySQL可能無法高效地使用索引。一般來說，如果可能，盡量避免在索引列上存儲NULL值。 資料類型不比對：如果查詢條件中的資料類型和列的資料類型不比對，MySQL可能無法使用索引。例如，如果id列是整型，并且我們在id上建立了索引，那麼以下查詢無法使用索引：

SELECT * FROM users WHERE id = '1';           

雖然'1'最終會被轉換為數字1，但是資料類型的不比對會使索引失效。 以上就是在什麼情況下會使用索引以及什麼情況下不會使用索引的一些常見例子。了解這些規則能幫助我們更好地設計和優化查詢，進而提高資料庫的性能。

總的來說，設計和使用索引是一個既需要理論知識，又需要具體分析業務需求的過程。希望這篇文章可以幫助你更好地了解和使用MySQL的索引，提高你的資料庫性能。

作者：仰望星空下的自己

連結：https://juejin.cn/post/7233589699214573629

來源：稀土掘金