建立索引語法

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [USING index_type]
    ON tbl_name (col_name[(length)], ...) [ASC | DESC];

索引類型選擇指南

類型	關鍵字	適用場景	支援引擎
一般索引	INDEX / KEY	WHERE、JOIN、ORDER BY 欄位	所有
唯一索引	UNIQUE INDEX	業務鍵 (Email、身分證) 防重複	所有
主鍵	PRIMARY KEY	表唯一識別，叢集索引	所有
全文索引	FULLTEXT INDEX	長文本搜尋 (CHAR/VARCHAR/TEXT)	MyISAM, InnoDB 5.6+
空間索引	SPATIAL INDEX	幾何資料 (POINT, POLYGON)	MyISAM, InnoDB 5.7+
複合索引	INDEX (a,b,c)	多欄位聯合查詢、排序、覆蓋索引	所有

實戰範例

-- 一般索引
CREATE INDEX idx_name ON customer (name(10));
-- 前綴索引：僅索引前 10 字元，節省空間

-- 唯一索引 (建表時)
CREATE TABLE persons (
    id_p INT NOT NULL,
    lastname VARCHAR(255) NOT NULL,
    CONSTRAINT uc_personid UNIQUE (id_p, lastname)
);

-- 唯一索引 (事後添加)
ALTER TABLE persons ADD UNIQUE INDEX uc_personid (id_p, lastname);

-- 全文索引
ALTER TABLE articles ADD FULLTEXT INDEX ft_content (content);

-- 刪除索引
DROP INDEX idx_name ON customer;
-- 或
ALTER TABLE customer DROP INDEX idx_name;

效能實測：複合索引優化分頁查詢

場景：article 表 20 萬筆，查詢 WHERE article_category=11 ORDER BY article_id DESC LIMIT 5

• 單欄索引 article_category：~5 秒
• 複合索引 CREATE INDEX idx_cat_id ON article (article_category, article_id)：0.0027 秒

原理：複合索引滿足 WHERE 過濾 + ORDER BY 排序，避免 filesort 並實現覆蓋索引。

索引設計黃金法則

1. 優先為 WHERE、JOIN、ORDER BY 欄位建索引
2. 高選擇性 (基數大) 欄位放在複合索引最左
3. 避免過度索引：寫入效能下降、佔用空間
4. 定期 ANALYZE TABLE 更新統計資訊