最左前綴原則

• 對于INDEX(name, age)來說
• 最左前綴可以是聯合索引的最左N個字段, 也可以是字符串索引的最左M個字符。

 SELECT * FROM t WHERE name LIKE '張%'

• 其效果和單獨創建一個INDEX(name)的效果是一樣的
• 若通過調整索引字段的順序, 可以少維護一個索引樹, 那么這個順序就是需要優先考慮采用的
• 對字符串創建索引，要考慮如果字符串很長的情況下，那么維護和響應成本也會很高，這時，可以使用字符串最左邊開始的部分字符建立索引
• 索引的選擇性指的是 : 不重復的索引值和數據表的記錄總數(#T)的比值, 范圍為 1/#T 到 1 之間, 索引選擇性越高則查詢效率越高

索引選擇性 = 不重復值的數量 / 總記錄數，它反映了“這個索引字段能不能把數據查得很精”。

如果一個字段有 10 萬條記錄，但只有 3 個不同的值（比如性別:男、女，武裝直升機），選擇性 = 3 / 100000 = 0.00003 → 很低，不推薦建立單獨索引。

如果字段有 10 萬條記錄，幾乎每條都不同（比如身份證號），選擇性接近 1 → 非常高，適合做索引。

• 為什么選擇性越高，查詢越快？
  • 因為選擇性越高，能過濾掉的數據就越多，減少回表，查詢效率就越高。
• 對于BLOB, TEXT, VARCHAR等類型的列, 必須使用前綴索引, MySQL不允許索引這些列的完整長度
• MySQL 不允許直接為這類大字段建完整索引（因為太大了）。所以要建前綴索引，也就是只索引字段前 N 個字符

如何選 N（前綴長度）？

1. 先看整列的區分度（理想情況）：

   SELECT COUNT(DISTINCT name)/COUNT(*) FROM t;
   

2. 再試試不同前綴長度的選擇性，比如前 3、5、10 個字符：

   SELECT COUNT(DISTINCT LEFT(name, 3))/COUNT(*) FROM t;
   SELECT COUNT(DISTINCT LEFT(name, 5))/COUNT(*) FROM t;
   ...
   

3. 看哪個N更靠近1, 進行索引的創建，用它來建索引：

   CREATE INDEX idx_name_prefix ON t(name(N));
   

-- 查看詳細索引信息
show index from products;

---

什么是“索引下推”？

索引下推是 MySQL 從 5.6 開始引入的一種優化技術，它讓更多的WHERE 條件在索引掃描階段就被處理掉，減少了回表次數，從而提升查詢效率。

---

舉個例子說明：

SQL語句如下：

SELECT * FROM t WHERE name LIKE '陳%' AND age = 10;
-- 假設我們創建了聯合索引：INDEX(name, age)

---

在 MySQL 5.6 之前（沒有索引下推）：

1. 使用索引找出 name LIKE '陳%' 匹配的主鍵 id。
2. 對這些 id 一條條回表（到原始數據）。
3. 然后在回表的數據中判斷 age = 10。

問題：大量回表，浪費IO。

---

MySQL 5.6 之后（有索引下推 ICP）：

1. 先用索引查出 name LIKE '陳%'。
2. 再在索引中繼續判斷 age = 10（因為索引中也有 age 字段）。
3. 只有兩個條件都滿足，才回表拿全部字段。

優勢：回表次數少，速度快。

---

對比圖解（簡化描述）：

[無 ICP]       name → (回表) → 判斷 age → 符合 → 返回結果
[有 ICP]       name → 判斷 age (在索引內完成) → (回表) → 返回結果

---

使用條件：

• 使用了聯合索引（如 INDEX(name, age)）。
• 查詢中涉及多個字段條件。
• 被篩選的多個字段都在索引中存在。

---

想驗證是否用了索引下推？

你可以使用 EXPLAIN 或 EXPLAIN FORMAT=JSON：

EXPLAIN SELECT name, age FROM t WHERE name LIKE '陳%' AND age = 10;

查看 Extra 中是否出現：

Using index condition

就說明用了索引下推。

索引下推使用條件逐條理解

條件	含義解釋	是否關鍵	原因與說明
? 只能用于 range、ref、eq_ref、ref_or_null 訪問方法	ICP 只在索引訪問的情況下生效，這幾種是索引訪問方式中常見的	是	全表掃描 (ALL) 不會使用索引，自然也就無索引下推
? 只能用于 InnoDB 和 MyISAM 引擎（含分區表）	目前 ICP 僅支持這兩個存儲引擎	是	其他引擎如 MEMORY、CSV 不支持該特性
? 對 InnoDB 來說，ICP 只作用于 二級索引（輔助索引）	InnoDB 的主鍵是聚簇索引，數據和索引一體	是	聚簇索引查詢不需要回表，因此沒有回表優化的必要
? ICP 的目的就是減少回表次數（減少 IO）	回表代價高，所以想辦法讓更多的過濾發生在索引層	是	回表越少，磁盤 IO 越少，性能越高
? 子查詢中的條件不能下推	ICP 只作用于主查詢中的 WHERE 條件	是	子查詢優化路徑不同，不能在索引層提前判斷
? 使用存儲函數（如 IFNULL(col, '')）的條件不能下推	存儲引擎無法理解和執行存儲函數	是	ICP 是存儲引擎層做的過濾，函數是 SQL 層的，隔離了

---

什么是這些“訪問方法”？（range、ref、eq_ref、ref_or_null）

這些是 MySQL 優化器 在執行 SELECT 時使用的 索引訪問方式，用來決定“怎么查你這張表”。

你可以用 EXPLAIN 看見，比如：

EXPLAIN SELECT * FROM users WHERE id = 1;

type 那一列就可能會出現：ref、range、ALL 等。

---

訪問方法	中文意思	舉例	是否能用 ICP	說明
range	范圍查找	id > 5 AND id < 10	? 支持	利用索引范圍掃描（B+ 樹區間）
ref	普通等值查找	name = '張三' 且 name 有索引	? 支持	單值等值匹配，常見聯合索引匹配場景
eq_ref	唯一等值查找	t1.id = t2.id 且 t2.id 是主鍵或唯一索引	? 支持	用于連接，精確匹配唯一值
ref_or_null	等值 + null 查找	name = '張三' OR name IS NULL	? 支持	對空值的處理也是索引可識別的
ALL	全表掃描	沒有用索引	? 不支持	沒有用到索引，自然談不上索引下推

• 嘗試時的一個問題

create index idx_product_name_and_category_union on products(product_name, category);
show index from products;
-- 這里沒觸發索引下推，LIKE '筆%' 是可以使用索引的（前綴匹配），
-- 但匹配度低，MySQL 可能選擇不下推 category 的判斷，保留到回表階段處理。
explain select product_id from products where product_name like '筆%' and category = 'Electronics';-- 加上 FORCE INDEX 強制使用聯合索引，后就使用索引下推了
EXPLAIN SELECT * FROM products FORCE INDEX(idx_product_name_and_category_union)
WHERE product_name LIKE '筆%' AND category = 'Electronics';

---

自適應哈希索引

InnoDB 支持一種 自適應哈希索引（AHI） 的優化機制，它不是我們手動建的索引，而是 InnoDB 在運行過程中自動創建的哈希索引，

• 目的是加快查詢速度，特別是對頻繁訪問的相同范圍或相同條件的 B+樹索引查詢，會自動轉化為哈希結構，提升效率。

具體來說：

• InnoDB 會監控 B+ 樹索引的使用頻率
• 如果發現某段范圍經常被查詢，而且是“等值查找”（不是模糊、范圍），就會自動為這段建立哈希索引
• 這樣后續的查找可以從 O(log n) 變為 O(1)，提升性能
• 它是完全自動、由 InnoDB 維護的，不需要我們手動干預

---

點	內容
使用場景	頻繁的等值查詢，例如 WHERE id = 123，會被自動轉換為哈希索引優化
控制開關	參數：innodb_adaptive_hash_index=ON（默認開啟）
內存占用	哈希索引是存在 Buffer Pool 的內存中，不是磁盤上的
限制	只能用于等值匹配，不支持范圍查詢或模糊匹配（LIKE、BETWEEN 等）
風險	對熱點表、高并發寫可能帶來鎖沖突，可考慮關閉該功能

---

https://github.com/0voice