1. 索引選擇與Change Buffer
問題引出：普通索引 vs 唯一索引 ——如何選擇？
在實際業務中，如果一個字段的值天然具有唯一性（如身份證號），并且業務代碼已確保無重復寫入，那就存在兩種選擇：
- 創建唯一索引
- 創建普通索引
雖然邏輯上兩者都可以正確工作，但從 性能角度看，應該如何選擇呢？
1.1. 查詢場景下的性能差異
查詢語句示例：
SELECT name FROM CUser WHERE id_card = 'xxxxxxxyyyyyyzzzzz';查詢過程分析：
- InnoDB 使用 B+ 樹索引，查找過程是按層遍歷到葉子節點。
- 普通索引：
找到首個滿足條件的記錄后，還會繼續查找，直到條件不再滿足。
- 唯一索引：
找到首個滿足條件的記錄后立即停止。
性能差距分析：
- InnoDB 是按數據頁（默認16KB）為單位讀取的。
- 也就是說，命中一條記錄時，整個數據頁已在內存中。
- 普通索引多做一次判斷和指針移動，性能開銷極小，可以忽略不計。
結論：查詢性能差異微乎其微
1.2. 更新場景下的性能差異 （關注 Change Buffer ）
Change Buffer 的概念：
- 又稱 變更緩沖區，用于緩存針對尚未加載入內存的數據頁的 DML 操作。
- 目的是延遲磁盤讀寫，提升寫性能。
- 持久化存儲，內存+磁盤雙存儲。
Merge 操作：
- 當數據頁被訪問或系統后臺線程定期觸發時，change buffer 會被合并（merge）到實際數據頁中。
兩種索引對比：
| 特性 | 唯一索引 | 普通索引 |
| 查詢性能差距 | 幾乎無 | 幾乎無 |
| 是否能使用 Change Buffer | ? 不能使用 | ? 可以使用 |
| 寫入磁盤前是否需加載數據頁 | ? 是 | ? 否 |
| 寫多讀少場景優化空間 | ?? 受限 | ? 提升明顯 |
| 建議使用場景 | 嚴格校驗唯一性 | 默認首選 |
- 唯一索引需驗證是否存在重復值，必須讀入數據頁判斷唯一性，無法延遲IO。
- 而普通索引可以直接緩存寫操作，延遲數據頁加載。
1.3. Change Buffer 的影響和適用場景
Change Buffer 的實際影響分析
1. 情況一：目標頁在內存中
- 唯一索引：讀內存判斷唯一性后插入，結束。
- 普通索引：直接插入，結束。
- ? 性能差異極小
2. 情況二：目標頁不在內存中
- 唯一索引：
需要將目標頁從磁盤加載入內存進行唯一性判斷 → 高成本的隨機 IO
- 普通索引：
操作直接寫入 Change Buffer，延遲磁盤讀寫 → 性能提升明顯
這是唯一索引與普通索引的性能關鍵差異點！
Change Buffer 的適用場景
適用場景 ：
- 寫多讀少 的系統
例如：日志系統、賬單系統等
頁面寫完之后很少會被立即查詢，Change Buffer 能發揮顯著優勢。
不適用場景 ：
- 寫后立刻讀 的業務模型
寫操作剛緩存就被查詢命中，觸發 merge，反而增加了維護成本。
實際應用建議
- 查詢性能差異不大，但更新性能差異明顯。
- 盡量優先選擇普通索引，除非業務邏輯依賴數據庫強一致性校驗。
- 寫多讀少場景下，配合開啟 Change Buffer（默認開啟），顯著優化性能。
- 使用機械硬盤時，Change Buffer 的效果更明顯，應適當調大
innodb_change_buffer_max_size參數（如 50%）。 - 若寫后即讀，可以考慮 關閉 Change Buffer。
2. MySQL選錯索引問題分析
2.1. 索引錯選問題
問題背景與現象：
- 有時 MySQL 執行 SQL 時并沒有選擇最佳索引，導致性能下降。
- 通過一個具體例子說明了優化器因估算錯誤而選錯索引的情況。
實驗設計：
1. 表結構與索引
CREATE TABLE t (a INT,b INT,c INT,INDEX(a),INDEX(b)
);2. 數據插入
- 插入數據：
(1,1,1)到(100000,100000,100000)共 10 萬行。
預期查詢語句
SELECT * FROM t WHERE a BETWEEN 10000 AND 20000;3. 實驗步驟（關鍵觸發邏輯）
- Session A：開啟事務，未提交；
- Session B：
刪除所有數據；
重新插入 10 萬行；
執行上面的查詢。
4. 異常現象
- 查詢變慢，發現 優化器選擇了全表掃描 而不是走
a的索引。
執行計劃對比與影響分析：
Q1：默認語句
SELECT * FROM t WHERE a BETWEEN 10000 AND 20000;- 使用了全表掃描，rows = 104620
- 掃描耗時約 40ms
Q2：強制使用索引
SELECT * FROM t FORCE INDEX(a) WHERE a BETWEEN 10000 AND 20000;- 使用索引
a，rows = 10001 - 掃描耗時約 21ms
- 結論：Q2 明顯更優
2.2. MySQL 優化器選錯索引原因
優化器目標
- 找出 執行代價最小 的執行計劃；
- 代價估算核心：行數（row estimate） + 回表成本。
行數估算依賴“統計信息”
- MySQL 使用索引的基數（cardinality） 估算結果行數；
- 采樣得出，不一定準確；
- 命令查看基數：
SHOW INDEX FROM t;統計信息采樣機制
- 參數
innodb_stats_persistent：
ON：采樣頁數 20，觸發更新閾值 10
OFF：采樣頁數 8，觸發更新閾值 16
- 采樣帶來的估算誤差：
優化器以為 a between 10000 and 20000 會返回約 37000 行；
實際只有 10001 行，高估了結果量。
回表代價高估
- 索引
a是二級索引，取出數據后需要回主鍵索引查全行（回表）； - 優化器認為：
37000 次回表 ≈ 37000 次隨機 IO；
而全表掃描只需約 100 頁順序讀；
所以選擇全表掃描。
2.3. 驗證與解決方案
觀察 EXPLAIN 輸出
EXPLAIN SELECT * FROM t WHERE a BETWEEN 10000 AND 20000;- rows ≈ 37116（高估）→ 優化器認為成本更高。
修復手段：更新統計信息
ANALYZE TABLE t;- 執行后重新
EXPLAIN，rows 變為 10001； - 優化器重新選擇正確索引。
總結與實踐建議
| 類別 | 內容 |
| 問題核心 | 優化器因統計信息誤差、高估回表代價，選錯了索引 |
| 典型表現 | EXPLAIN 中 顯著高估；執行計劃走了全表掃描 |
| 核心原因 | 索引基數估算不準確；二級索引導致回表開銷被放大 |
| 解決辦法 | 使用 更新統計信息 |
| 實踐建議 | 當發現慢查詢/rows 異常時，第一步先做統計更新；必要時使用 臨時規避 |
)
