MySQL系列文章

本文是 MySQL索引系列的第四篇。在前三篇文章中，我們系統介紹了索引的數據結構、覆蓋索引、最左前綴原則、索引下推等核心優化技術，以及字符串索引的優化方法。本文將深入分析索引失效的多種場景及其背後的原理，幫助你全面理解索引為何有時會“失效”，以及如何有效避免和優化這類問題。

一、核心原理：B+樹索引的有序性特性

要理解索引失效的原因，我們首先需要回顧B+樹索引的核心特性——有序性。InnoDB存儲引擎使用的B+樹索引結構保持同一層兄弟節點的有序性，這是索引能夠快速定位數據的根本原因。

實際上，B+樹提供的快速定位能力，正是來源於同一層兄弟節點的有序性。當我們執行等值查詢或範圍查詢時，優化器可以藉助這種有序性快速跳過不符合條件的數據塊，極大減少需要掃描的數據量。

然而，當我們對索引字段進行函數操作時（下文都默認字段上有索引），問題就出現了：

-- 示例：按月份查詢訂單數據
SELECT * FROM orders WHERE MONTH(create_time) = 7;

這條SQL語句的問題在於：B+樹索引是按照create_time的原始值排序的，而不是按照MONTH(create_time)的計算結果排序的。如果計算month()函數，你會看到傳入7的時候，在樹的第一層就不知道該怎麼辦了，因為所有月份的日期值都被轉換為1-12的數字，完全破壞了原有的有序性。

也就是説，對索引字段做函數操作，可能會破壞索引值的有序性，因此優化器就決定放棄走樹搜索功能，轉而使用全索引掃描或全表掃描。

二、函數操作導致索引失效的詳細分析

2.1 顯式函數操作

最常見的索引失效場景就是在索引列上直接使用函數：

WHERE SUBSTRING(name, 1, 4) = 'Johnoh'，在 MySQL 中表示：
篩選出 name 列中「從第 1 個字符開始，連續截取 4 個字符，結果等於 'John'」的所有用户記錄。

2.2 隱式類型轉換

MySQL的隱式類型轉換也會在底層轉換為函數操作，導致索引失效：

-- order_no是VARCHAR類型，但用數字查詢
SELECT * FROM orders WHERE order_no = 1001;

-- MySQL實際執行的是：
SELECT * FROM orders WHERE CAST(order_no AS SIGNED) = 1001;

MySQL字符轉換默認規則：在MySQL中，字符串和數字做比較的話，是將字符串轉換成數字。這個規則可以通過簡單查詢驗證：

SELECT '10' > 9;  -- 返回1（true），説明字符串'10'被轉換為數字10

如果MySQL將數字轉換為字符串，按字符串比較'10'和'9'，應該返回0（false），因為'10'的第一個字符'1'比'9'小。但實際返回1，證實了MySQL的字符串到數字的轉換規則。

2.3 關鍵區別：索引列 vs 查詢值

重要區別：只有在索引列上做函數操作才會導致索引失效，在查詢值上做函數操作不會影響索引使用：

-- 不會導致索引失效（在查詢值上做操作）
SELECT * FROM users WHERE id = 1000 + 1;
SELECT * FROM users WHERE age = '30';  -- 字符串轉數字
SELECT * FROM orders WHERE create_time = DATE_ADD('2023-01-01', INTERVAL 7 DAY);

-- 會導致索引失效（在索引列上做操作）
SELECT * FROM users WHERE id + 1 = 1001;
SELECT * FROM users WHERE CAST(age AS CHAR) = '30';
SELECT * FROM orders WHERE DATE_FORMAT(create_time, '%Y-%m') = '2023-06';

三、隱式字符編碼轉換的多表關聯問題

在多表關聯查詢中，如果關聯字段的字符集不同，也會導致隱式轉換和索引失效：

-- 訂單表使用utf8mb4字符集
CREATE TABLE orders (
    id INT PRIMARY KEY,
    order_no VARCHAR(20) CHARACTER SET utf8mb4,
    KEY idx_order_no (order_no)
);

-- 訂單詳情表使用utf8字符集
CREATE TABLE order_details (
    id INT PRIMARY KEY,
    order_no VARCHAR(20) CHARACTER SET utf8,
    product_name VARCHAR(100),
    KEY idx_order_no (order_no)
);

-- 關聯查詢
SELECT o.*, od.* 
FROM orders o 
JOIN order_details od ON o.order_no = od.order_no;

MySQL實際執行的是：

SELECT o.*, od.* 
FROM orders o 
JOIN order_details od ON CONVERT(od.order_no USING utf8mb4) = o.order_no;

由於在order_details表的索引字段order_no上進行了CONVERT函數操作，導致該表的索引無法使用。

到這裏，你終於明確了，字符集不同只是條件之一，連接過程中要求在被驅動表的索引字段上加函數操作，是直接導致對被驅動表做全表掃描的原因。

四、MySQL優化器的"保守"行為

MySQL的優化器確實有"偷懶"的嫌疑，即使簡單地把where id+1=1000改寫成where id=1000-1就能夠用上索引快速查找，也不會主動做這個語句重寫。

這意味着開發者需要主動優化查詢語句，而不是依賴優化器自動優化：

-- 優化器不會重寫這個查詢（導致全表掃描）
SELECT * FROM users WHERE id + 1 = 1001;

-- 需要手動重寫為（可以使用索引）
SELECT * FROM users WHERE id = 1001 - 1;

這種"保守"行為提醒我們，作為開發者需要具備主動優化意識，不能完全依賴數據庫優化器。

前面4種情況其實説的都是同一個事情：對索引字段做函數操作，可能會破壞索引值的有序性，因此優化器就決定放棄走樹搜索功能，導致索引失效。

五、其他常見索引失效場景

除了函數操作，還有多種情況會導致索引無法有效使用：

5.1 違反最左前綴原則

對於複合索引 (col1, col2, col3)，以下查詢無法充分利用索引：

最左前綴原則要求查詢必須從複合索引的最左邊列開始，並且不能跳過中間的列。這是因為B+樹索引是按照索引定義的列順序構建的，如果跳過前面的列，就無法利用索引的有序性。

5.2 LIKE查詢以通配符開頭

-- 無法使用索引
SELECT * FROM products WHERE name LIKE '%apple%';
SELECT * FROM products WHERE name LIKE '%apple';

-- 可以使用索引
SELECT * FROM products WHERE name LIKE 'apple%';

當LIKE模式以通配符開頭時，優化器無法利用索引的有序性進行快速定位，因為無法確定匹配值的前綴。這種情況下，優化器只能進行全表掃描，逐行比較是否匹配模式。

對於%%全模糊匹配，可以考慮使用搜索引擎如Elasticsearch。如果必須使用前導通配符%apple，可以考慮使用反轉字符串並建立反轉索引的技巧。

5.3 OR條件使用不當

當OR條件中包含未索引列時，整個查詢可能無法使用索引：

-- 假設age字段沒有索引
SELECT * FROM users WHERE name = 'john' OR age > 30;

-- 優化方案：使用UNION或確保所有OR條件都有索引（但是需要注意union可能會使用臨時表）
SELECT * FROM users WHERE name = 'john'
UNION
SELECT * FROM users WHERE age > 30;

MySQL處理OR條件時，如果OR的各個條件都使用獨立的索引，可以使用index_merge優化。但如果其中一個條件沒有索引，優化器就無法使用任何索引，只能選擇全表掃描。

5.4 IN和NOT IN濫用

當IN列表中的值過多時，優化器可能選擇全表掃描：

-- 當value_list包含大量值時，可能導致全表掃描
SELECT * FROM products WHERE category_id IN (1, 2, 3, ..., 1000);

-- 最簡單的方案就是，分批次查詢（拆成5批）
SELECT * FROM products WHERE category_id IN (1, 2, ..., 200);

當IN列表包含大量值時，優化器需要評估回表查詢的代價。如果IN列表過大，優化器可能判斷全表掃描更高效。

一般來説，當IN列表包含的值超過表中總行數的30%時，優化器傾向於選擇全表掃描。

5.5 SELECT * 的性能影響

雖然SELECT *不會直接導致索引失效，但會帶來其他性能問題：

1. 無法使用覆蓋索引：除非索引字段全覆蓋（正常都不會）
2. 網絡傳輸浪費：返回不必要的數據增加了網絡傳輸開銷
3. 內存佔用增加：需要緩存更大的結果集，可能擠佔其他查詢的內存資源，影響內存命中率
4. 增加了排序和臨時表的使用：當需要排序或分組時，更大的行尺寸會增加臨時表的使用

-- 不推薦
SELECT * FROM users WHERE age > 30;

-- 推薦：只選擇需要的字段
SELECT id, name, email FROM users WHERE age > 30;

-- 使用覆蓋索引優化
CREATE INDEX idx_users_age_covering ON users(age) INCLUDE (id, name, email);
SELECT id, name, email FROM users WHERE age > 30;

六、診斷與優化：使用EXPLAIN深入分析查詢

要深入診斷索引是否被正確使用，EXPLAIN命令是最重要的工具。EXPLAIN執行計劃包含6個關鍵字段，每個字段都承載着優化器決策的關鍵信息：

-- 分析查詢執行計劃
EXPLAIN SELECT * FROM orders WHERE MONTH(create_time) = 6;

對於這條查詢，EXPLAIN結果可能顯示：

• type: ALL：表示全表掃描
• key: NULL：表示沒有使用索引
• rows: 1000000：表示需要掃描100萬行
• Extra: Using where：表示需要逐行判斷條件

這表明索引沒有被使用，需要進行優化。

如果想深入學習EXPLAIN的詳細用法和所有字段含義，推薦閲讀我的另一篇文章：《MySQL EXPLAIN執行計劃：SQL性能翻倍的秘密武器》

七、總結與最佳實踐

通過本文的分析，我們可以看到，大多數索引失效場景都源於同一個根本原因：對索引字段進行了某種形式的操作，破壞了索引值的有序性，導致優化器無法使用索引的快速定位能力。以下是詳細的總結和優化建議：

7.1 索引失效場景及解決方案總結表

數據分佈傾斜、統計信息過期出現概率較小，因此全文未具體介紹。

核心原因在於：MySQL使用採樣統計的方法導致索引統計信息不準確及優化器存在誤判的情況。

7.2 核心優化原則

1. 保持索引原始性：避免在索引列上進行任何函數計算、類型轉換或表達式運算
2. 注意隱式轉換：MySQL的隱式類型轉換和字符集轉換可能導致意外的函數操作
3. 統一設計規範：保持表結構設計的一致性，避免字符集和排序規則的不匹配
4. 主動優化意識：MySQL優化器不會自動重寫所有低效查詢，需要開發者主動優化
5. 使用EXPLAIN驗證：對關鍵查詢使用EXPLAIN分析執行計劃，確保索引被正確使用

7.3 結語

索引優化是數據庫性能調優的核心技能，也是一個需要持續學習和實踐的過程。通過本文的系統分析，希望你已經理解了各種索引失效場景背後的原理，並掌握了相應的優化方法。

在實際工作中，建議養成以下良好習慣：

• 在編寫SQL時就要考慮索引使用情況
• 定期使用EXPLAIN分析關鍵查詢的執行計劃
• 監控慢查詢日誌，及時發現性能問題
• 建立數據庫設計規範，避免常見的設計陷阱

數據庫優化之路永無止境，但每一步的探索都會帶來實實在在的性能提升和更好的用户體驗。希望本文能成為你索引優化路上的有力助手，幫助你在工作中解決更多的性能挑戰。