MySQL索引完全指南：让你的查询速度飞起来-平芜编程栈

以下是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. 主键（自动创建聚簇索引）CREATETABLEusers(idBIGINTUNSIGNEDAUTO_INCREMENTPRIMARYKEY,...);-- 2. 普通单列索引CREATEINDEXidx_emailONusers(email);-- 3. 唯一索引CREATEUNIQUEINDEXuk_phoneONusers(phone);-- 4. 复合索引（最左前缀原则顺序非常重要）CREATEINDEXidx_name_age_cityONusers(name,age,city);-- 5. 覆盖索引（查询字段全在索引中，无需回表）CREATEINDEXidx_coverONorders(user_id,order_time,amount);-- 6. 函数索引（MySQL 8.0+）CREATEINDEXidx_upper_nameONusers((UPPER(name)));-- 7. 降序索引（8.0+）CREATEINDEXidx_time_descONorders(create_timeDESC);-- 8. 全文索引（ngram 适合中文）CREATEFULLTEXTINDEXft_title_contentONarticles(title,content)WITHPARSER ngram;-- 9. 空间索引CREATETABLElocations(idINTAUTO_INCREMENTPRIMARYKEY,coordPOINTNOTNULLSRID4326,SPATIALINDEXidx_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），无需回表

经典案例：

-- 慢（回表）SELECTid,name,create_timeFROMusersWHEREphone='13812345678';-- 快（覆盖）CREATEINDEXidx_phone_name_timeONusers(phone,name,create_time);SELECTid,name,create_timeFROMusersWHEREphone='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 倍