MySql索引知识点汇总 三（个人总结）

一、 索引下推（Index Condition Pushdown, ICP）

这是 MySQL 5.6 之后引入的黑科技

1. 逻辑演进

假设你有联合索引 (name, age)，查询语句是：SELECT * FROM users WHERE name = '张三' AND age > 20;

• 没有 ICP 时： 存储引擎通过索引找到所有 name = '张三' 的主键 ID，然后回表去聚簇索引找整行记录，最后把数据给 Server 层，由 Server 层过滤 age > 20 的数据。
• 有了 ICP 时： 存储引擎在遍历索引树时，发现索引里已经包含了 age 字段，于是直接在索引内部就把不符合 age > 20 的记录过滤掉，最后只对符合条件的数据进行回表。

2. 物理意义

极大减少了“回表”的次数。 磁盘 IO 是数据库最贵的资源，少一次回表，性能就提升一大截。

二、 索引设计的“金字塔”法则

并不是所有的字段都适合加索引。构建索引时，你需要遵循以下三个维度：

1. 离散度（Selectivity）

定义： 字段中不重复的值与总行数的比例。

• 高离散度： 身份证号、手机号（极佳）。
• 低离散度： 性别、状态（如 0/1）。

避坑： 不要给“性别”建索引。如果数据分布均匀，MySQL 发现走索引还要回表，成本比全表扫描还高，它会直接放弃索引。

2. 前缀索引（Prefix Index）

如果你要给一个很长的字符串（比如 VARCHAR(255) 的 URL 或 Email）建索引，整个存进去太占空间。

• 方法：ALTER TABLE users ADD INDEX idx_email (email(10)); 只取前 10 个字符。
• 代价： 节省了空间，但无法使用覆盖索引，且无法用于 ORDER BY。

3. 联合索引的顺序

逻辑： 将过滤性最强的字段放在最左侧。

• 例如：where 城市 = '北京' and 姓 = '张'。全国姓张的多，但北京的“张”相对确定，如果“城市”过滤性更强，索引应该是 (city, last_name)。

三、 性能分析利器：EXPLAIN

我们要通过 EXPLAIN 命令查看 SQL 的执行计划，观察索引是否生效。

重点关注这三个指标：

1. type： 访问类型。

• system > const > eq_ref > ref > range > index > ALL
• 目标： 至少达到 range 级别，力争 ref。如果看到 ALL，说明在全表扫描，赶紧加索引。

1. key： 实际使用的索引。如果是 NULL，说明没用上。
2. Extra： 额外信息。

• Using index：好消息！使用了覆盖索引，没回表。
• Using filesort：坏消息！说明需要额外排序，没用到索引的顺序。
• Using temporary：最坏的消息！用了临时表，通常出现在 GROUP BY 没加索引时。

四、 索引的代价（负作用）

作为一个合格的工程师，必须知道索引不是越多越好。

1. 维护成本： 每次 INSERT、UPDATE、DELETE，MySQL 都需要同步更新 B+ 树。
2. 空间成本： 索引文件会占用大量磁盘。
3. 优化器干扰： 索引太多，MySQL 优化器在选索引时会纠结（耗时），甚至选错。

总结：

1. 宏观： 索引是目录，物理上是磁盘里的 B+ 树。
2. 微观： B+ 树通过“页”管理，叶子节点有序且双向链接。
3. 规则： 最左匹配是根基，ICP 是优化，回表是性能杀手。
4. 实战： 用 EXPLAIN 检查 type，用离散度挑选字段。