查詢為主的應用,缺索引會導致全表掃描,災難性拖慢效能。但低基數欄位(如性別、狀態碼)建索引不僅無效,還會拖慢 INSERT/UPDATE/DELETE(過度索引)。
-- 查詢:WHERE area='beijing' AND age=22
-- 單欄索引各自命中率低,複合索引效果最佳
CREATE INDEX idx_area_age ON users(area, age);
複合索引 (area, age, salary) 等同擁有 (area)、(area, age)、(area, age, salary) 三個索引。將最高選擇性、最常用於 WHERE 的欄位放最左。
單欄索引忽略 NULL;複合索引中任一欄為 NULL,該行不進索引。設計時將預設值設為 NOT NULL DEFAULT '' 或具體數值。
-- CHAR(255) 欄位,前 20 字元區分度已足夠
CREATE INDEX idx_name_prefix ON users(name(20));
優點:縮小索引體積、減少 I/O、提升快取命中率。
MySQL 單查詢只能用一個索引。若 WHERE 已用索引,ORDER BY 另一欄位會觸發 Filesort。解決:建複合索引 (where_col, order_col) 或接受自然順序。
LIKE 'aaa%' -- ✅ 使用索引(範圍查詢)
LIKE '%aaa%' -- ❌ 全表掃描
LIKE '%aaa' -- ❌ 全表掃描
業務若需前導模糊,考慮全文索引或反轉欄位存儲。
-- ❌ 索引失效
WHERE YEAR(adddate) = 2024
WHERE id + 1 = 100
-- ✅ 改寫為範圍查詢
WHERE adddate >= '2024-01-01' AND adddate < '2025-01-01'
WHERE id = 99
-- ❌ 全表掃描
WHERE id NOT IN (3, 5, 7)
-- ✅ 使用 NOT EXISTS 或範圍查詢
WHERE NOT EXISTS (SELECT 1 FROM ... WHERE id = t.id)
WHERE id < 3 OR id > 3
Functions(Indexed_Col)、Col % 2 = 0 等運算EXPLAIN 無 Using filesort/Using temporaryANALYZE TABLE 更新統計資訊