一、索引概述

索引本質是幫助MySQL高效獲取數據的排序數據結構（類似書籍目錄），通過減少磁盤I/O次數提升查詢效率。其核心價值體現在大數據量場景下的快速定位能力，但同時帶來存儲和維護成本。

核心特點：

• 優點：
  • 減少數據檢索量（時間復雜度從O(n)降至O(log n)）
  • 加速排序和分組操作（ORDER BY/GROUP BY）
  • 保證數據唯一性（唯一索引）
• 缺點：
  • 占用額外磁盤空間（索引文件獨立存儲）
  • 降低寫操作性能（INSERT/UPDATE/DELETE需維護索引樹）
  • 不恰當的索引設計可能引發性能劣化

二、索引結構

1.?B+Tree（主流結構）

• 層級結構：非葉子節點僅存索引鍵（Key），葉子節點存儲數據指針且形成雙向鏈表 。
• 優勢：
  • 樹高可控（一般3-4層支撐千萬級數據）
  • 范圍查詢高效（葉子節點鏈表直接遍歷）
  • 適合磁盤存儲（節點大小=磁盤頁大小，減少I/O）

2.?Hash索引

• 基于哈希表實現，O(1)時間復雜度的等值查詢 。
• 局限性：
  • 不支持范圍查詢和排序
  • 哈希沖突影響性能（鏈表或紅黑樹處理）
  • 僅Memory引擎原生支持，InnoDB提供自適應哈希（AHI）

3.?全文索引（Full-Text）

• 基于倒排索引實現，針對TEXT類型字段進行關鍵詞搜索 。
• 支持自然語言查詢（MATCH...AGAINST語法）
• 僅InnoDB/MyISAM引擎支持

三、索引分類

按數據結構劃分

類型	特點	適用場景
B+Tree	支持范圍查詢、排序，磁盤友好	90%以上的索引場景
Hash	等值查詢極快，不支持范圍操作	內存表、精確匹配場景
Fulltext	文本關鍵詞搜索	文章內容檢索

按物理存儲劃分

類型	特點	示例
聚集索引	數據行存儲在葉子節點（InnoDB主鍵索引）	PRIMARY KEY
非聚集索引	葉子節點存儲主鍵值或數據地址，需二次查找	普通單列/組合索引

按字段特性劃分

類型	特點	語法示例
主鍵索引	唯一且非空，InnoDB的表數據按主鍵順序存儲	PRIMARY KEY (id)
唯一索引	列值唯一，允許NULL	UNIQUE INDEX (email)
普通索引	無唯一性約束，加速查詢	INDEX (name)
全文索引	文本內容分詞檢索	FULLTEXT (content)

按字段數量劃分

類型	特點	優化規則
單列索引	單字段索引	INDEX (age)
聯合索引	多字段組合索引，遵循最左前綴原則	INDEX (name,age)

特殊類型：

• 覆蓋索引：索引包含查詢所需全部字段，避免回表?
  ?（如SELECT id,name FROM users WHERE name='A'，若索引是(name,id)）
• 前綴索引：對長字符串前N字符創建索引，節省空間?
  （如INDEX (title(10))）

四、索引語法

1. 通用操作

-- 創建索引 
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON table_name (col1 [ASC|DESC], ...); 
-- 修改表添加索引 
ALTER TABLE table_name ADD [UNIQUE|FULLTEXT] INDEX index_name (col1, ...); 
-- 刪除索引 
DROP INDEX index_name ON table_name;

2. 分類示例

主鍵索引：

-- 建表時指定 
CREATE TABLE users (
id INT AUTO_INCREMENT, 
name VARCHAR(50), 
PRIMARY KEY (id) -- 聚集索引 ); 
-- 修改添加 
ALTER TABLE orders ADD PRIMARY KEY (
order_id);

聯合索引：

-- 優化多條件查詢 
CREATE INDEX idx_name_age ON employees (
last_name, hire_date);

全文索引：

-- 支持文本搜索 
CREATE FULLTEXT INDEX ft_content ON articles (content); 
SELECT * FROM articles WHERE MATCH(content) AGAINST('數據庫');

前綴索引：

-- 長字段優化 
CREATE INDEX idx_city_prefix ON customers (city(10));

五、設計原則

1. 高頻查詢字段優先（WHERE/JOIN/ORDER BY）
2. 區分度高字段前置（如性別字段不宜單獨建索引）
3. 避免過度索引（超過5個索引需謹慎評估）
4. 聯合索引左前綴匹配（INDEX(a,b,c)適用a=1 AND b>2，不適用b>2）
5. 長文本使用前綴/全文索引