MySQL 索引基礎操作全攻略：建立、檢視、刪除與六大類型實戰

為何使用索引

索引如書目錄，加速資料檢索。建立於表的一或多欄，儲存於 B+ 樹或雜湊表。InnoDB/MyISAM 支援 BTREE，MEMORY 支援 HASH。

適合建立索引場景

1. 常用於 WHERE 條件欄位
2. GROUP BY 分組欄位
3. JOIN 關聯鍵（PK/FK）
4. 唯一約束欄位

索引操作三種方式

1. 建表時建立
2. 既有表 CREATE INDEX
3. 既有表 ALTER TABLE ADD INDEX

六大索引類型建立語法

1. 普通索引

-- 建表時
CREATE TABLE t (
    col TYPE,
    INDEX idx_name (col [(len)] [ASC|DESC])
);

-- 既有表
CREATE INDEX idx_name ON t (col [(len)] [ASC|DESC]);
ALTER TABLE t ADD INDEX idx_name (col [(len)] [ASC|DESC]);

2. 唯一索引

-- 建表時
CREATE TABLE t (
    col TYPE,
    UNIQUE INDEX uk_name (col [(len)] [ASC|DESC])
);

-- 既有表
CREATE UNIQUE INDEX uk_name ON t (col [(len)] [ASC|DESC]);
ALTER TABLE t ADD UNIQUE INDEX uk_name (col [(len)] [ASC|DESC]);

3. 全文索引

適用 CHAR/VARCHAR/TEXT，MyISAM/InnoDB 5.6+ 支援。

-- 建表時
CREATE TABLE t (
    col TYPE,
    FULLTEXT INDEX ft_name (col)
);

-- 既有表
CREATE FULLTEXT INDEX ft_name ON t (col);
ALTER TABLE t ADD FULLTEXT INDEX ft_name (col);

-- 查詢語法
SELECT * FROM t WHERE MATCH(col1, col2) AGAINST('word1', 'word2');

4. 複合索引（多列索引）

遵循最左前綴原則：INDEX(a,b,c) 可作為 a、(a,b) 索引，不可單獨作為 b、c、(b,c)。

-- 建表時
CREATE TABLE t (
    a TYPE, b TYPE, c TYPE,
    INDEX idx_abc (a, b, c)
);

-- 既有表
CREATE INDEX idx_abc ON t (a, b, c);
ALTER TABLE t ADD INDEX idx_abc (a, b, c);

5. 空間索引

適用空間資料類型（GEOMETRY 等），MyISAM/InnoDB 支援，較少使用，此處略。

刪除索引

DROP INDEX idx_name ON table_name;

檢視索引

SHOW INDEX FROM table_name;

關鍵提醒

• 索引長度：CHAR/VARCHAR 可指定前綴；BLOB/TEXT 必須指定；InnoDB 單列上限 767/3072 位元組。
• 選擇性：COUNT(DISTINCT col)/COUNT(*) 越高越適合建索引。
• 複合索引順序：高選擇性、常用查詢、小型別優先。
• 全文索引：InnoDB 5.6+ 支援，早期僅 MyISAM。