作者：吳守陽
索引是提高 MySQL 查詢性能的重要工具，但在使用索引時需要遵循一定的規則和最佳實踐，以避免常見的索引失效問題。通過合理設計和維護索引，可以顯著提升數據庫的性能和響應速度。希望本文對你在 MySQL 索引的使用和優化方面有所幫助。
在數據庫設計和優化中，索引是提高查詢性能的重要工具。本文將詳細介紹 MySQL 索引的基本概念、創建索引的規則、最佳實踐以及注意事項，幫助你更好地利用索引優化數據庫性能。
什么是索引？
索引是一種數據結構，用于快速查找數據庫表中的特定行。索引的作用類似于書籍的目錄，通過索引可以加速查詢速度。常見的索引類型包括 B-Tree 索引、哈希索引、全文索引和空間索引。
索引的類型
- B-Tree 索引：這是 MySQL 中最常用的索引類型，適用于大多數查詢操作，包括精確匹配和范圍查詢。
- 哈希索引：這種索引類型僅適用于精確匹配查詢，速度非常快，但不支持范圍查詢。
- 全文索引：主要用于對文本字段進行全文搜索。
- 空間索引：用于地理空間數據類型的查詢。
MySQL 中幾種常見的索引類型，包括主鍵索引、唯一索引、普通索引、全文索引和組合索引。
主鍵索引 (Primary Key Index)
介紹：
- 主鍵索引是一種特殊的唯一索引，不允許 NULL 值。
- 一個表只能有一個主鍵索引。
特點：
- 強制唯一性約束，確保每行數據的唯一性。
- 自動創建索引，通常用于表的標識符字段。
注意事項：
- 主鍵索引應盡量選擇單一列且較短的字段，以提高檢索效率。
- 主鍵值不宜頻繁更新，因為這會影響索引的重建。
唯一索引 (Unique Index)
簡介：
- 唯一索引保證列的值是唯一的，但允許 NULL 值。
- 一個表可以有多個唯一索引。
特點：
- 強制列值的唯一性，防止重復數據。
- 可以在多個列上創建唯一索引。
注意事項：
- 普通索引適用于查詢頻繁但沒有唯一性要求的列。
- 普通索引的列值可以重復且允許 NULL 值。
普通索引 (Index)
簡介：
- 普通索引是最基本的索引類型，沒有唯一性約束。
- 可以在表的一個或多個列上創建。
特點：
- 提高查詢速度，沒有唯一性要求。
- 可以在多列上創建組合索引。
注意事項：
- 普通索引適用于查詢頻繁但沒有唯一性要求的列。
- 普通索引的列值可以重復且允許 NULL 值。
全文索引 (Fulltext Index)
簡介：
- 全文索引用于全文搜索，可以在 CHAR、VARCHAR 和 TEXT 列上創建。
- 適用于 InnoDB 和 MyISAM 存儲引擎。
特點：
- 支持自然語言全文檢索和布爾全文檢索。
- 適用于包含大量文本數據的列，如文章內容、評論等。
注意事項：
- 全文索引在插入和更新數據時的性能開銷較大。
- 適用于需要全文檢索的場景，如搜索引擎、博客系統等。
組合索引 (Composite Index)
簡介：
- 組合索引是指在多個列上創建的索引。
- 可以包含多個列，通常用于聯合查詢。
特點：
- 提高多列組合查詢的性能。
- 左前綴原則：索引可以被部分列使用，但必須從最左邊的列開始。
注意事項：
- 組合索引遵循最左前綴原則，查詢條件必須包含最左邊的列。
- 組合索引的列順序應根據查詢頻率和過濾條件進行設計。
空間索引 (Spatial Index)
簡介：
- 空間索引用于存儲和查詢地理空間數據，適用于 MyISAM 存儲引擎。
- 主要用于 GIS（地理信息系統）應用。
特點：
- 支持對空間數據類型（如 POINT、LINESTRING、POLYGON）進行快速查詢。
- 適用于存儲地理位置信息的數據表。
注意事項：
- 空間索引只適用于 MyISAM 存儲引擎，不支持 InnoDB。
- 適用于地理信息系統和空間數據分析。
覆蓋索引
簡介：
覆蓋索引（Covering Index）指的是索引中包含查詢所需的所有列，這樣在執行查詢時可以直接從索引中獲取數據，無需訪問數據表。
特點：
- 提高查詢性能，因為避免了回表查詢。
- 覆蓋索引通常是一個組合索引。
注意事項：
- 覆蓋索引需要包含查詢中所有涉及的列，包括 SELECT 子句和 WHERE 子句中的列。
- 如果索引不覆蓋所有查詢列，MySQL 將需要回表查詢以獲取缺失的數據，失去了覆蓋索引的優勢。
索引的規則
創建索引的基本規則
- 唯一性：確保索引列的唯一性，以避免重復數據。例如，使用 UNIQUE 約束創建唯一索引。
- 選擇性：選擇性高的列適合創建索引。選擇性是指不同值的數量與總記錄數的比例，越接近 1 越好。
- 頻繁使用：對經常出現在 WHERE 子句中的列創建索引。
- 組合索引：對于多個列一起使用的查詢，創建組合索引。例如，WHERE 子句中同時使用 col1 和 col2，則可以創建 (col1, col2) 的組合索引，遵循最左原則，鎖定最少數據索引字段放在最左邊。
- 排序使用：ORDER BY，GROUP BY，DISTINCT的字段需要添加在索引的后面，避免生成臨時表。
- Join使用：在join字段上建立索引。
索引的最佳實踐
- 前綴索引：對于較長的字符串列，可以使用前綴索引，只索引前 N 個字符，以節省空間。
- 覆蓋索引：使用包含查詢所有列的索引，可以避免回表查詢，提高性能。
- 適當數量：并非越多索引越好，索引過多會影響寫操作性能，合理創建索引，保持適當數量。
索引的注意事項
索引的維護
- 定期重建索引：對于頻繁更新的表，定期重建索引可以提高查詢性能。
- 監控索引使用情況：使用 MySQL 提供的性能_schema 或 EXPLAIN 命令監控索引的使用情況，及時調整索引。
索引的局限性
- 存儲空間：索引會占用額外的存儲空間，表的索引越多，所需的存儲空間越大。
- 寫操作開銷：每次插入、更新和刪除操作都會導致索引的更新，因此索引過多會影響寫操作的性能。
- 索引失效：不合理的查詢可能導致索引失效，例如在索引列上進行函數操作、使用不等號查詢或類型不匹配等。
索引失效的情況
- 不等于操作：<> 操作符可能導致索引失效。
- 函數操作：在索引列上使用函數會導致索引失效。
- 類型不匹配：查詢條件中的數據類型與索引列類型不匹配，會導致索引失效。
- 使用 OR：在多個條件之間使用 OR 可能會導致索引失效，尤其是當 OR 條件中的列沒有索引時。
- Like %使用：禁止使用%前綴模糊查詢，例如LIKE “%ganji”，索引失效。
- 非等值操作：WHERE條件中的非等值條件（IN、BETWEEN、<、<=、>、>=）會導致使用不了聯合索引的后面字段，注意避免。
- 隱式轉化操作：避免where條件進行隱式類型轉化，導致索引失效。
結論
索引是提高 MySQL 查詢性能的重要工具，但在使用索引時需要遵循一定的規則和最佳實踐，以避免常見的索引失效問題。通過合理設計和維護索引，可以顯著提升數據庫的性能和響應速度。希望本文對你在 MySQL 索引的使用和優化方面有所幫助。
作者介紹
吳守陽，51CTO社區編輯，擁有8年DBA工作經驗，熟練管理MySQL、Redis、MongoDB等開源數據庫。精通性能優化、備份恢復和高可用性架構設計。善于故障排除和自動化運維，保障系統穩定可靠。具備良好的團隊合作和溝通能力，致力于為企業提供高效可靠的數據庫解決方案。
責任編輯：姜華
來源：
51CTO
