MySQL索引使用一定有效嗎?如何排查索引效果?
1. 索引一定有效嗎?
不一定! 即使你創建了索引,MySQL 也可能因為以下原因 不使用索引 或 索引效果不佳:
- 索引選擇錯誤:MySQL 優化器可能選擇了錯誤的索引。
- 索引失效場景:某些 SQL 寫法會導致索引失效。
- 數據分布問題:數據量太少或數據分布不均,導致全表掃描更快。
- 索引設計不合理:索引列順序、類型不匹配等。
2. 索引失效的常見場景
(1) 使用 !=
、NOT IN
、NOT EXISTS
SELECT * FROM users WHERE age != 20; -- 可能不走索引
SELECT * FROM users WHERE id NOT IN (1, 2, 3); -- 可能全表掃描
(2) 使用 LIKE
以通配符開頭
SELECT * FROM users WHERE name LIKE '%Alice%'; -- 不走索引
SELECT * FROM users WHERE name LIKE 'Alice%'; -- 可能走索引
(3) 對索引列使用函數或計算
SELECT * FROM users WHERE YEAR(create_time) = 2023; -- 不走索引
-- 優化:使用范圍查詢
SELECT * FROM users WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';
(4) 數據類型不匹配(隱式轉換)
-- name 是 VARCHAR,但用數字查詢
SELECT * FROM users WHERE name = 123; -- 不走索引(隱式轉換成字符串)
(5) 復合索引未遵循最左前綴原則
-- 假設有聯合索引 (name, age)
SELECT * FROM users WHERE age = 20; -- 不走索引(缺少 name 條件)
(6) 數據量太少
- 如果表只有幾十行數據,MySQL 可能直接全表掃描,因為索引查找+回表的開銷更大。
3. 如何排查索引效果?
方法 1:使用 EXPLAIN
分析執行計劃
EXPLAIN SELECT * FROM users WHERE name = 'Alice';
重點關注:
type
:查詢類型(const
>ref
>range
>index
>ALL
)key
:實際使用的索引rows
:預估掃描行數Extra
:額外信息(Using index
表示覆蓋索引)
示例輸出:
id | select_type | table | type | key | rows | Extra |
---|---|---|---|---|---|---|
1 | SIMPLE | users | ref | idx_name | 1 | NULL |
type=ALL
:全表掃描(索引可能未生效)key=NULL
:未使用索引
方法 2:檢查索引使用情況
-- 查看表的索引
SHOW INDEX FROM users;-- 查看索引使用統計(需開啟性能模式)
SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'your_db' AND OBJECT_NAME = 'users';
index_name
:索引名稱count_read
:索引被讀取次數(越高說明索引越有效)
方法 3:使用 OPTIMIZER_TRACE
查看優化器決策
-- 開啟優化器跟蹤
SET optimizer_trace = 'enabled=on';
SET optimizer_trace_max_mem_size = 1000000;-- 執行查詢
SELECT * FROM users WHERE name = 'Alice';-- 查看優化器決策
SELECT * FROM information_schema.optimizer_trace;
- 可以看到 MySQL 為什么選擇(或不選擇)某個索引。
方法 4:強制使用索引(測試對比)
-- 強制使用索引
SELECT * FROM users FORCE INDEX (idx_name) WHERE name = 'Alice';-- 對比性能
EXPLAIN SELECT * FROM users FORCE INDEX (idx_name) WHERE name = 'Alice';
EXPLAIN SELECT * FROM users IGNORE INDEX (idx_name) WHERE name = 'Alice';
- 如果強制索引后查詢變快,說明優化器可能選錯了索引。
4. 如何優化索引?
(1) 選擇合適的索引列
- 高選擇性列(如
user_id
比gender
更適合索引)。 - 頻繁查詢的列(如
WHERE
、ORDER BY
、JOIN
條件)。
(2) 使用覆蓋索引
-- 優化前:需要回表
SELECT id, name, age FROM users WHERE name = 'Alice';-- 優化后:使用 (name, age) 聯合索引,避免回表
ALTER TABLE users ADD INDEX idx_name_age (name, age);
(3) 避免索引冗余
-- 已有 (name, age) 索引,再建 (name) 就是冗余的
ALTER TABLE users DROP INDEX idx_name;
(4) 定期分析表
-- 更新索引統計信息
ANALYZE TABLE users;-- 重建索引(修復碎片化)
OPTIMIZE TABLE users;
5. 總結
問題 | 解決方案 |
---|---|
索引未生效 | 檢查 EXPLAIN ,避免索引失效場景 |
優化器選錯索引 | 使用 FORCE INDEX 或 OPTIMIZER_TRACE 分析 |
索引效果差 | 優化索引設計,使用覆蓋索引 |
數據量太少 | 可能不需要索引 |
📌 建議:
- 使用
EXPLAIN
分析關鍵查詢。 - 避免索引失效寫法(如
LIKE '%xxx%
、函數計算)。 - 定期檢查索引使用情況,刪除冗余索引。