【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）

这些是 MySQL 优化器 在执行 SELECT 时使用的 索引访问方式，用来决定“怎么查你这张表”。

你可以用 EXPLAIN 看见，比如：

EXPLAIN SELECT * FROM users WHERE id = 1;

type 那一列就可能会出现：ref、range、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 维护的，不需要我们手动干预

https://github.com/0voice