萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 索引優化七大黃金法則:從建索引到避開效能陷阱

1. 該建索引時就建,但別過度

查詢為主的應用,缺索引會導致全表掃描,災難性拖慢效能。但低基數欄位(如性別、狀態碼)建索引不僅無效,還會拖慢 INSERT/UPDATE/DELETE(過度索引)。

2. 善用複合索引,發揮最左前綴特性

-- 查詢: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 的欄位放最左。

3. 索引不包含 NULL 值

單欄索引忽略 NULL;複合索引中任一欄為 NULL,該行不進索引。設計時將預設值設為 NOT NULL DEFAULT '' 或具體數值。

4. 使用短索引(前綴索引)

-- CHAR(255) 欄位,前 20 字元區分度已足夠
CREATE INDEX idx_name_prefix ON users(name(20));

優點:縮小索引體積、減少 I/O、提升快取命中率。

5. ORDER BY 與索引的關係

MySQL 單查詢只能用一個索引。若 WHERE 已用索引,ORDER BY 另一欄位會觸發 Filesort。解決:建複合索引 (where_col, order_col) 或接受自然順序。

6. LIKE 寫法決定索引生死

LIKE 'aaa%'  -- ✅ 使用索引(範圍查詢)
LIKE '%aaa%' -- ❌ 全表掃描
LIKE '%aaa'  -- ❌ 全表掃描

業務若需前導模糊,考慮全文索引或反轉欄位存儲。

7. 避免在索引欄上做運算/函數

-- ❌ 索引失效
WHERE YEAR(adddate) = 2024
WHERE id + 1 = 100

-- ✅ 改寫為範圍查詢
WHERE adddate >= '2024-01-01' AND adddate < '2025-01-01'
WHERE id = 99

8. 拒絕 NOT IN / <> / !=,改用 NOT EXISTS

-- ❌ 全表掃描
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 temporary
  • ☑ 定期 ANALYZE TABLE 更新統計資訊
copyright © 萬盛學電腦網 all rights reserved