MySQL 索引創建與刪除完整指南：語法、類型、前綴索引、實戰案例與優化建議

MySQL 索引創建與刪除操作手冊

索引是提升查詢性能的核心手段。本文系統講解 MySQL 5.0+ 支持的索引類型、創建/刪除語法、前綴索引策略、EXPLAIN 驗證與優化建議。

一、MySQL 索引類型總覽

二、創建索引語法

1. ALTER TABLE (推薦，單表多索引原子操作)

ALTER TABLE table_name
    ADD [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [USING {BTREE | HASH}]
    (col_name[(length)] [ASC | DESC], ...);

2. CREATE INDEX (單索引)

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
ON table_name (col_name[(length)] [ASC | DESC], ...)
[USING {BTREE | HASH}];

3. 建表時指定

CREATE TABLE t (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100),
    content TEXT,
    INDEX idx_name (name),
    UNIQUE uk_email (email),
    FULLTEXT ft_content (content)
);

三、前綴索引深度解析

適用場景

• BLOB、TEXT、長 VARCHAR (超過 255 字符)
• InnoDB 單列索引長度限制 767 字節 (innodb_large_prefix=ON 時 3072)
• MyISAM 單列 1000 字節

長度選擇科學方法

-- 計算不同前綴長度的選擇性
SELECT 
    COUNT(DISTINCT LEFT(col, 10)) / COUNT(*) AS sel_10,
    COUNT(DISTINCT LEFT(col, 20)) / COUNT(*) AS sel_20,
    COUNT(DISTINCT LEFT(col, 50)) / COUNT(*) AS sel_50
FROM table_name;

-- 選擇性接近 1.0 (如 0.99+) 時的長度即為最佳

實戰範例

-- city 表 city 列創建前綴索引長度 2
ALTER TABLE city ADD INDEX idx_city_prefix (city(2));

-- 驗證使用
EXPLAIN SELECT * FROM city WHERE city = 'Beijing'\G
-- 關鍵輸出: key=idx_city_prefix, rows=1

前綴索引限制

• 不能用於 ORDER BY、GROUP BY 優化
• 不能實現覆蓋索引掃描
• LIKE '%suffix' 仍無法使用

四、刪除索引

-- ALTER TABLE 方式
ALTER TABLE table_name DROP INDEX index_name;

-- DROP INDEX 方式 (MySQL 特有別名)
DROP INDEX index_name ON table_name;

-- 刪除主鍵
ALTER TABLE table_name DROP PRIMARY KEY;

五、實戰案例：城市表索引優化

表結構

CREATE TABLE city (
    id INT PRIMARY KEY AUTO_INCREMENT,
    city VARCHAR(100),
    country_code CHAR(3),
    population INT
);

優化步驟

-- 1. 高頻精確查詢: 城市名
ALTER TABLE city ADD INDEX idx_city (city);

-- 2. 篩選+排序: 國家+人口
ALTER TABLE city ADD INDEX idx_country_pop (country_code, population);

-- 3. 長文本搜索: 城市描述 (假設有 description TEXT)
ALTER TABLE city ADD FULLTEXT ft_desc (description);

-- 4. 驗證各查詢計劃
EXPLAIN SELECT * FROM city WHERE city = 'Shanghai'\G
EXPLAIN SELECT * FROM city WHERE country_code = 'CN' ORDER BY population DESC\G
EXPLAIN SELECT * FROM city WHERE MATCH(description) AGAINST('+river' IN BOOLEAN MODE)\G

六、索引修改 (實質重建)

MySQL 不支持原地修改索引定義，需 DROP + ADD。

ALTER TABLE city 
    DROP INDEX idx_city,
    ADD INDEX idx_city_new (city(10));

七、優化建議總結

1. 所有列類型均可索引，但需評估寫入開銷
2. 每表最少 16 個索引 (早期版本)，新版無硬性限制但建議 < 50
3. 總索引長度限制：InnoDB 3072 字節，MyISAM 1000 字節
4. 默認 B-Tree，MEMORY 表可選 HASH
5. 定期 ANALYZE TABLE 更新統計信息
6. 監控未使用索引：sys.schema_unused_indexes (8.0+)

掌握索引創建語法、前綴索引選擇科學方法、EXPLAIN 驗證技巧，能精準解決查詢性能瓶頸。