以下是 2025–2026 年视角下 MySQL 索引的完整、实用指南（基于 MySQL 8.0 ~ 8.4 主流版本 + InnoDB 引擎）。
内容结构化、带代码示例、常见误区、优化 checklist 和真实案例，目标是让你读完就能真正把查询速度提起来，而不是只停留在“知道 B+ 树”层面。
一、为什么索引能让查询“飞起来”？核心原理（B+ 树 vs B 树）
| 项目 | B 树（经典） | B+ 树（InnoDB 实际使用） | 为什么 B+ 树更快？ |
|---|---|---|---|
| 叶子节点存数据 | 是（所有节点都存数据） | 只叶子节点存数据，非叶子只存键 | 同一层级能放更多键，树更矮 |
| 叶子节点是否链表 | 否 | 是（双向链表） | 范围查询顺序扫描极快 |
| 范围查询效率 | 中等 | 极高 | 顺序 I/O 而非随机 I/O |
| 扇出（fan-out） | 较低 | 极高（一页 16KB 可存数百~上千键） | 层级少 → 查找次数少 |
| InnoDB 页大小 | — | 默认 16KB | — |
一句话总结：
InnoDB 用 B+ 树 + 数据页 + 双向链表 + 自适应哈希索引（adaptive hash index）组合，让 等值 + 范围 + 排序 + 覆盖 查询都非常高效。
二、MySQL 主流索引类型对比（2025–2026 现状）
| 索引类型 | 底层结构 | 支持操作 | 典型场景 | InnoDB 支持 | MyISAM 支持 | 备注 / 限制（MySQL 8.4） |
|---|---|---|---|---|---|---|
| 主键索引 | B+ 树 | =, >, <, BETWEEN, IN, ORDER BY | 每张表必须有（聚簇索引） | 是 | 是 | 必须 NOT NULL + UNIQUE |
| 唯一索引 | B+ 树 | 同上 | 邮箱、手机号、订单号等 | 是 | 是 | 允许 NULL（但 NULL 不重复） |
| 普通索引 | B+ 树 | 同上 | where、order by、group by 高频列 | 是 | 是 | — |
| 复合索引 | B+ 树 | 最左前缀匹配 | 多条件组合查询 | 是 | 是 | 遵循最左前缀原则 |
| 全文索引 | 倒排索引 | MATCH AGAINST | 文章搜索、商品标题模糊搜索 | 是（ngram / MeCab） | 是 | InnoDB 5.6+ 支持 |
| 空间索引 | R 树 | ST_Contains, ST_Within 等 | GIS、地图附近查询 | 是（5.7+） | 是 | 必须 GEOMETRY 类型 |
| 哈希索引 | 哈希表 | =, <=> | MEMORY 引擎等值查询 | 不直接支持（有自适应哈希） | 否 | InnoDB 自适应哈希内部使用 |
| 函数索引（8.0+） | B+ 树 | 对函数/表达式建索引 | UPPER(name)、JSON_EXTRACT 等 | 是 | 否 | 非常实用 |
| 降序索引（8.0+） | B+ 树（逆序） | ORDER BY DESC 优化 | 按时间倒序分页 | 是 | 是 | 减少 filesort |
| 不可见索引（8.0+） | B+ 树（隐藏） | — | 测试新索引效果，不影响现有查询计划 | 是 | 是 | 安全上线神器 |
三、创建索引的正确姿势（推荐写法）
-- 1. 主键（自动创建聚簇索引）
CREATE TABLE users (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
...
);
-- 2. 普通单列索引
CREATE INDEX idx_email ON users(email);
-- 3. 唯一索引
CREATE UNIQUE INDEX uk_phone ON users(phone);
-- 4. 复合索引（最左前缀原则顺序非常重要）
CREATE INDEX idx_name_age_city ON users(name, age, city);
-- 5. 覆盖索引（查询字段全在索引中，无需回表）
CREATE INDEX idx_cover ON orders(user_id, order_time, amount);
-- 6. 函数索引（MySQL 8.0+）
CREATE INDEX idx_upper_name ON users((UPPER(name)));
-- 7. 降序索引（8.0+）
CREATE INDEX idx_time_desc ON orders(create_time DESC);
-- 8. 全文索引（ngram 适合中文）
CREATE FULLTEXT INDEX ft_title_content ON articles(title, content) WITH PARSER ngram;
-- 9. 空间索引
CREATE TABLE locations (
id INT AUTO_INCREMENT PRIMARY KEY,
coord POINT NOT NULL SRID 4326,
SPATIAL INDEX idx_coord(coord)
);四、复合索引最左前缀原则（最容易踩的坑）
规则：复合索引 (a,b,c) 能用到的查询条件必须从 最左边开始连续匹配。
| 查询条件 | 是否走索引 (idx_a_b_c) | 能用到的索引部分 | 说明 |
|---|---|---|---|
| WHERE a=1 | 是 | a | 最左匹配 |
| WHERE a=1 AND b=2 | 是 | a,b | 连续最左 |
| WHERE a=1 AND c=3 | 是 | a | b 断开，c 无法用 |
| WHERE b=2 | 否 | — | 不符合最左 |
| WHERE a>1 AND b=2 | 是（a 范围，b 等值） | a,b | 范围后等值仍可 |
| WHERE a=1 ORDER BY b,c | 是 | a,b,c | 排序也能用 |
| WHERE a=1 ORDER BY c | 部分（a 用索引，c filesort） | a | — |
黄金经验：把频率最高、最有区分度的列放最左。
五、覆盖索引 & 回表（性能差距可达 5~20 倍）
- 回表：查了索引，但要再去聚簇索引取整行数据 → 额外一次 IO
- 覆盖索引：查询的所有字段都在索引中（包括 SELECT、WHERE、ORDER BY），无需回表
经典案例：
-- 慢（回表）
SELECT id, name, create_time FROM users WHERE phone = '13812345678';
-- 快（覆盖）
CREATE INDEX idx_phone_name_time ON users(phone, name, create_time);
SELECT id, name, create_time FROM users WHERE phone = '13812345678';六、2025–2026 高频索引失效场景（带解决方案）
| 失效场景 | 原因 | 解决方案（MySQL 8.0+） |
|---|---|---|
| LIKE ‘%xx%’ | 最左通配符 | 用全文索引 / ngram / 业务前缀搜索 |
| 函数/运算 (WHERE YEAR(create_time)=2025) | 破坏索引列完整性 | 创建函数索引 / 添加计算列 + 索引 |
| 类型隐式转换 (WHERE phone = 138…) | varchar 与 int 比较转字符串 | 统一类型，WHERE phone = ‘138…’ |
| OR 条件两边索引不一致 | 优化器放弃索引 | 拆成 UNION ALL 或都建索引 |
| != / NOT IN / IS NOT NULL | 选择性低或无法范围扫描 | 业务改为正向查询 / IS NULL 用得少 |
| 索引列上用了 != / < > 等 | 部分场景仍可，但选择性低时弃用 | 评估区分度 |
| 联合索引跳跃使用 | 不符合最左前缀 | 调整列顺序或拆索引 |
七、索引优化 checklist（生产必查）
- 单表索引个数 ≤ 5～7 个（超过容易选错索引 + 写性能下降）
- 每张表必须有主键（InnoDB 聚簇索引）
- 区分度低的列（如 gender、status）不要单独建索引
- 频繁更新的列慎建索引（写性能下降明显）
- 复合索引字段顺序：高频 where → 高区分度 → 排序字段 → 覆盖字段
- 优先建覆盖索引
- 用
EXPLAIN+type、key、rows、Extra判断
- type: ref / eq_ref / range / index 好；ALL / index 坏
- Extra: Using index（覆盖）好；Using filesort / Using temporary 坏
- 大表加索引用 pt-online-schema-change / gh-ost（在线 DDL）
- 开启慢查询日志 +
long_query_time=1+log_queries_not_using_indexes=1 - 定期用
ANALYZE TABLE/OPTIMIZE TABLE更新统计信息
八、真实案例对比（速度提升倍数）
| 场景 | 原 SQL / 索引 | 优化后索引 / SQL | 速度提升 |
|---|---|---|---|
| 用户手机号查信息 | phone varchar(20), 无索引 | INDEX(phone) | 几百～几千倍 |
| 订单按用户+时间范围 | WHERE user_id=1 AND create_time BETWEEN | INDEX(user_id, create_time) | 10～50 倍 |
| 商品标题模糊搜索 | LIKE ‘%iPhone%’ | FULLTEXT(title) + MATCH AGAINST | 几十～几百倍 |
| 按创建时间倒序分页 | ORDER BY create_time DESC LIMIT 10,10 | INDEX(create_time DESC) | 避免 filesort |
| JSON 字段查询 | JSON_EXTRACT(data,’$.status’)=1 | 生成列 + 索引 或 函数索引 | 10～100 倍 |
如果你当前有慢查询 SQL、EXPLAIN 输出、表结构，可以贴出来，我帮你现场分析 + 给出最优索引方案。
想深入哪个部分？
- EXPLAIN 字段逐个详解
- 聚簇 vs 非聚簇索引内存布局
- 自适应哈希索引原理
- 全文索引 ngram vs MeCab（中文场景）
- 大表加索引在线方案（gh-ost / pt-osc）
随时说～
