MySQL 索引架構全解析：類型、創建、優化與實戰案例

MySQL 索引架構全解析：類型、創建、優化與實戰案例

一、索引基礎理論

索引在存儲引擎層實現，不同引擎支持的索引類型、最大數量、長度限制各異。所有引擎至少支持：

• 每表 ≥ 16 個索引
• 總索引長度 ≥ 256 字節

MySQL 索引存儲類型：

索引優缺點

二、索引分類體系

1. 普通 vs 唯一：主鍵是特殊唯一索引 (不允許 NULL)
2. 單列 vs 復合：復合索引遵循「最左前綴原則」
3. 全文索引 (FULLTEXT)：僅 MyISAM 支持，適用 CHAR/VARCHAR/TEXT
4. 空間索引 (SPATIAL)：僅 MyISAM 支持，針對 GEOMETRY/POINT/LINESTRING/POLYGON，列必須 NOT NULL

三、索引創建語法

CREATE TABLE table_name (
    col_name data_type,
    [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (col_name[(length)]) [ASC|DESC]
);

• INDEX 和 KEY 同義
• length 僅字符串類型可指定 (前綴索引)
• ASC/DESC 指定索引排序方向 (InnoDB 8.0+ 真正支持降序)

四、實戰演示：各類索引創建與驗證

1. 普通索引

CREATE TABLE book (
    bookid INT NOT NULL,
    bookname VARCHAR(255) NOT NULL,
    authors VARCHAR(255) NOT NULL,
    info VARCHAR(255) NULL,
    comment VARCHAR(255) NULL,
    year_publication YEAR NOT NULL,
    INDEX (year_publication)
) ENGINE=MYISAM;

驗證：

SHOW CREATE TABLE book;
EXPLAIN SELECT * FROM book WHERE year_publication=1990;

EXPLAIN 關鍵列解讀

2. 唯一索引

CREATE TABLE t1 (id INT NOT NULL, name CHAR(30) NOT NULL, UNIQUE INDEX UniqIdx(id));

3. 復合索引 (最左前綴驗證)

CREATE TABLE t3 (id INT, name CHAR(30), age INT, info VARCHAR(255), INDEX MultiIdx(id, name, age));

-- 命中索引
EXPLAIN SELECT * FROM t3 WHERE id=1 AND name='小芳';

-- 未命中 (未使用最左列)
EXPLAIN SELECT * FROM t3 WHERE name='小芳';

4. 全文索引

CREATE TABLE t4 (id INT, info VARCHAR(255), FULLTEXT INDEX FulltxtIdx(info)) ENGINE=MYISAM;

5. 空間索引

CREATE TABLE t5 (g GEOMETRY NOT NULL, SPATIAL INDEX spatIdx(g)) ENGINE=MYISAM;

五、既有表添加/刪除索引

ALTER TABLE 語法

-- 添加
ALTER TABLE t1 ADD INDEX NameIdx(name);
ALTER TABLE t3 ADD INDEX t3AgeAndInfo(age, info);
ALTER TABLE t6 ADD FULLTEXT INDEX infoFTIdx(info);
ALTER TABLE t7 ADD SPATIAL INDEX spatIdx(g);

-- 刪除
ALTER TABLE book DROP INDEX UniqidIdx;

CREATE/DROP INDEX 語法

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX idx_name ON tbl_name(col_name[(len)]);
DROP INDEX idx_name ON tbl_name;

SHOW INDEX 解讀

SHOW INDEX FROM t3;

六、關鍵限制與最佳實踐

• 主鍵/自增列的唯一索引不可刪除
• 刪除列時：若列是索引前綴，自動從索引移除；若索引所有列均被刪除，索引整體消失
• 前綴索引：長文本列僅索引前 N 字符，平衡空間與選擇性
• 覆蓋索引：查詢列全包含在索引中，避免回表，Extra 顯示 Using index