您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 查詢優化 7 大核心技巧：從索引失效到 JOIN 改寫

編輯：MySQL 資料庫相關知識

SQL 調優核心在於「讓索引生效、減少掃描行、避免不必要排序/臨時表」。本文歸納 7 類常見導致索引失效的寫法、3 種構造優化技巧，配合 EXPLAIN 驗證，助你快速定位並解決慢查詢。

一、導致索引失效的 4 大「殺手」

1. WHERE 條件使用 != / <>

SELECT * FROM t_user WHERE id != 2;  -- 全表掃描
-- 優化: 若業務允許, 改為 id < 2 OR id > 2 (仍可能掃描), 或應用層過濾

2. 列上使用函數/表達式

SELECT * FROM t_user WHERE DAY(reg_time) = 6;  -- 索引失效
-- 優化: 範圍查詢
SELECT * FROM t_user WHERE reg_time >= '2024-01-06 00:00:00' AND reg_time < '2024-01-07 00:00:00';

3. JOIN 關聯字段類型/字符集/長度不一致

-- t_user.id BIGINT, t_user_city.user_id INT
SELECT * FROM t_user_city c LEFT JOIN t_user u ON u.id = c.user_id;  -- 類型不匹配, 索引失效
-- 優化: 統一類型 (推薦 BIGINT), 或 CAST(c.user_id AS UNSIGNED) (仍可能失效)

4. LIKE 以通配符開頭

SELECT * FROM t_user WHERE name LIKE '%abc';  -- 全表掃描
SELECT * FROM t_user WHERE name LIKE 'abc%';  -- 可用索引 (最左前綴)

二、優化器自動放棄索引的場景

5. 索引選擇度過低 (掃描行 > 30% 表行數)

SELECT * FROM t_user WHERE is_novice = 1;  -- is_novice 只有 0/1, 各佔 50%
-- 優化: 不建索引, 或改用 ENUM/TINYINT, 應用層分表 (novice_users / veteran_users)

三、構造層面優化技巧

1. 用 IN 代替 OR

-- 低效
SELECT * FROM t_user WHERE id = 2 OR id = 4 OR id = 6;
-- 高效 (優化器可將 IN 轉為 range 掃描)
SELECT * FROM t_user WHERE id IN (2,4,6);

2. 避免 WHERE 子句對列做運算

-- 低效
SELECT * FROM t_user WHERE DATE(reg_time) = '2024-01-01';
-- 高效: 範圍查詢, 利用索引
SELECT * FROM t_user WHERE reg_time >= '2024-01-01' AND reg_time < '2024-01-02';

3. 將計算下推到應用層

-- 低效: DB 計算距離
SELECT user_id, SQRT(POW(unit_x,2) + POW(unit_y,2)) AS distance FROM t_map_unit WHERE user_id = 2;
-- 高效: 應用層讀取 unit_x, unit_y 計算, 或新增 distance 列配合觸發器維護

4. 拆分大 JOIN 為多條簡單查詢

-- 低效: 大表 JOIN 大表
SELECT c.*, u.name FROM t_user_city c LEFT JOIN t_user u ON u.id = c.user_id;
-- 高效: 兩步查詢 + 應用層組裝
SELECT * FROM t_user_city WHERE ...;  -- 取 user_id 列表
SELECT name FROM t_user WHERE id IN (...);  -- 覆蓋索引, 極快
-- 適用於: 關聯表大、不需資料庫端過濾/排序、應用層記憶體足夠

四、驗證優化成效

EXPLAIN SELECT ...;
-- 關鍵列:
-- type: 期望 ref/range/eq_ref, 避免 ALL/index
-- key: 實際使用的索引
-- rows: 預估掃描行數 (越小越好)
-- Extra: Using index (覆蓋索引), Using where, Using temporary, Using filesort (需優化)