MySQL索引架構全解析：六大類型、三種創建方式、覆蓋索引與最左前綴原理實戰

索引本質與權衡

索引是有序數據結構（B+樹），加速查詢（WHERE/JOIN/ORDER BY/GROUP BY），但寫入時需維護順序，降低INSERT/UPDATE/DELETE性能。

黃金法則：大批量導入前刪除索引，導入後重建，避免逐行維護開銷。

六大索引類型詳解

三種創建語法對比

1. 建表時創建（推薦，無鎖）

CREATE TABLE t (
    id INT,
    name VARCHAR(20),
    INDEX idx_name (name),                    -- 普通
    UNIQUE INDEX uk_id (id),                  -- 唯一
    FULLTEXT INDEX ft_info (info)             -- 全文，需 ENGINE=MyISAM
);

2. 現有表 CREATE INDEX（元數據鎖，短暫阻塞寫入）

CREATE INDEX idx_name ON t(name);
CREATE UNIQUE INDEX uk_course ON t(course_id);
CREATE FULLTEXT INDEX ft_info ON t(info);

3. ALTER TABLE ADD INDEX（兼容性最好，支持更多選項）

ALTER TABLE t ADD INDEX idx_name (name(10));      -- 前綴索引
ALTER TABLE t ADD UNIQUE INDEX uk_id (id);
ALTER TABLE t ADD FULLTEXT INDEX ft_info (info);

核心進階概念

前綴索引

INDEX idx_name (name(10))：僅索引前10字符，大幅縮小索引體積，適用於長字符串（URL、標題）。需評估選擇性：COUNT(DISTINCT LEFT(col,10))/COUNT(*)接近1時效果佳。

多列索引與最左前綴原理

INDEX idx_a_b_c (a, b, c)等價於創建(a)、(a,b)、(a,b,c)三個索引。
查詢必須包含最左列a才能用到索引；WHERE b=1無法使用，WHERE a=1 AND c=3僅用到a。

覆蓋索引

查詢列全部包含在索引中，無需回表查聚簇索引。EXPLAIN顯示Using index，性能極佳。

索引刪除

DROP INDEX idx_name ON t;           -- 普通/唯一/全文/空間通用
ALTER TABLE t DROP INDEX idx_name;  -- 等價語法
DROP PRIMARY KEY ON t;              -- 刪除主鍵

避坑指南

• 過度索引：單表索引>5個需審慎評估寫入影響
• 隱式轉換：WHERE varchar_col = 123導致索引失效
• 函數/運算：WHERE DATE(created_at) = '...'失效，改用範圍查詢
• LIKE通配符：%abc%失效，abc%可用

索引設計是查詢模式驅動的，而非表結構驅動。根據EXPLAIN、慢查詢日誌、業務SQL持續迭代優化。