【MySQL】前缀索引、索引下推、访问方法，自适应哈希索引

最左前缀原则

• 对于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