索引是查詢性能的關鍵。本文系統整理 MySQL 索引的創建語法、類型選擇、維護操作、優化原則與數據類型建議。
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(100) NOT NULL,
name VARCHAR(50),
status TINYINT DEFAULT 1,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
UNIQUE INDEX uk_email (email),
INDEX idx_status_created (status, created_at),
FULLTEXT INDEX ft_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 主鍵
ALTER TABLE users ADD PRIMARY KEY (id);
-- 唯一索引
ALTER TABLE users ADD UNIQUE INDEX uk_email (email);
-- 普通索引
ALTER TABLE users ADD INDEX idx_name (name);
-- 組合索引
ALTER TABLE users ADD INDEX idx_status_created (status, created_at);
-- 全文索引
ALTER TABLE users ADD FULLTEXT INDEX ft_name (name);
CREATE INDEX idx_name ON users (name);
CREATE UNIQUE INDEX uk_email ON users (email);
CREATE FULLTEXT INDEX ft_name ON users (name);
-- 刪除普通/唯一/全文索引
ALTER TABLE users DROP INDEX idx_name;
-- 或
DROP INDEX idx_name ON users;
-- 刪除主鍵
ALTER TABLE users DROP PRIMARY KEY;
-- 查看索引
SHOW INDEX FROM users\G
組合索引 (a, b, c) 可支持:
WHERE a = 1WHERE a = 1 AND b = 2WHERE a = 1 AND b = 2 AND c = 3ORDER BY a, b, c❌ 不支持:WHERE b = 2、WHERE c = 3、WHERE a = 1 AND c = 3 (跳過 b)
-- 查詢僅需讀取索引,無需回表
EXPLAIN SELECT id, email FROM users WHERE email = 'test@example.com';
-- Extra: Using index
TINYINT < SMALLINT < INT < BIGINT;CHAR 固定長,VARCHAR 可變長INT UNSIGNED (INET_ATON/INET_NTOA)NOT NULL DEFAULT '' 或 DEFAULT 0;NULL 增加統計複雜度、索引存儲額外位圖| 場景 | 示例 | 解決 |
|---|---|---|
| 隱式類型轉換 | WHERE varchar_col = 123 | WHERE varchar_col = '123' |
| 函數/表達式作用於索引列 | WHERE DATE(created_at) = '2024-01-01' | WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02' |
| LIKE 以 % 開頭 | WHERE name LIKE '%abc' | 全文索引或應用層反轉存儲 |
| OR 條件未全覆蓋索引 | WHERE a = 1 OR b = 2 | 改用 UNION ALL 或組合索引 |
| 不等於 (!=, <>) | WHERE status != 1 | 全表掃描,考慮反向查詢 |
| IS NULL/IS NOT NULL | WHERE col IS NULL | MySQL 5.7+ 可用索引,但選擇性需評估 |
-- 重建索引(InnoDB)
ALTER TABLE tbl_name ENGINE=InnoDB;
-- 或
OPTIMIZE TABLE tbl_name;
Handler_read_key / Handler_read_rnd_next 比例:越高說明索引命中率越好Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests:命中率SHOW ENGINE INNODB STATUS 中 BUFFER POOL AND MEMORY 區塊EXPLAIN 分析執行計劃:type=ref/range/eq_ref、key、rows、ExtraANALYZE TABLE 更新統計信息