第83篇MySQL索引2026版系列导航《Java 100 天进阶之路》完整目录 |⬅️ 上一篇第82篇Spring面试压轴题待发布 |➡️ 下一篇第84篇MySQL事务与锁待发布一、核心知识点索引本质排好序的快速查找数据结构BTree 索引MySQL InnoDB 默认索引结构叶子节点存储数据非叶子节点存索引聚簇索引 vs 二级索引聚簇索引叶子存整行数据二级索引叶子存主键值回表二级索引查到主键后再到聚簇索引查完整行覆盖索引索引列包含查询所需的所有字段无需回表最左前缀原则联合索引从左到右匹配跳过中间列则后面失效索引下推ICPMySQL 5.6在索引遍历时直接过滤减少回表次数索引失效场景函数操作、隐式类型转换、%开头的模糊查询等MySQL 8.0 新特性隐藏索引、降序索引、索引统计信息持久化二、通俗讲解1分钟开心学1. 索引是什么索引是数据库的“目录”。没有索引就像一本没有目录的书要找某句话只能一页页翻全表扫描有了索引直接翻到对应页码即可。生活类比图书馆的图书分类标签索书号就是索引。你要找《Java编程思想》先查电脑索引得到索书号 TP312JA/123然后直接去书架定位不用逛遍整个图书馆。2. BTree 为什么适合数据库BTree 特点非叶子节点只存索引不存数据能存放更多索引项降低树高度一般 2~4 层。叶子节点形成双向链表支持范围查询和顺序遍历。数据均匀分布在叶子节点查询稳定任何查找都 O(logN)。对比其他数据结构Hash 索引单点查询快但不支持范围查询仅 Memory 引擎默认。二叉树可能退化成链表树高不可控。B-Tree非叶子也存数据导致每层能存的索引变少树更高。3. 聚簇索引 vs 二级索引聚簇索引叶子节点存储整行数据。InnoDB 中主键就是聚簇索引没有主键则第一个 NOT NULL UNIQUE 列都没有则隐式生成 rowid。二级索引辅助索引叶子节点存储主键值查询时先找到主键再到聚簇索引回表取完整数据。生活类比聚簇索引就像按学号排好的学生档案柜学号对应整套档案。二级索引就像按姓名建的索引卡上面写着学号你需要根据学号再去档案柜取档案回表。4. 最左前缀原则联合索引(a, b, c)相当于按 a 排序a 相同时按 b 排序b 相同时按 c 排序。查询时从索引最左边开始匹配跳过中间列则后续无法使用。✅WHERE a 1 AND b 2 AND c 3全用✅WHERE a 1 AND b 2用 a、b✅WHERE a 1用 a❌WHERE b 2无法使用跳过了 a❌WHERE a 1 AND c 3只用 ac 失效三、实操代码案例 场景说明测试表用户订单表数据量 100 万。CREATETABLEorders(idbigint(20)NOTNULLAUTO_INCREMENT,user_idint(11)NOTNULL,order_novarchar(32)NOTNULL,amountdecimal(10,2)DEFAULTNULL,statustinyint(4)DEFAULT0,create_timedatetimeDEFAULTCURRENT_TIMESTAMP,PRIMARYKEY(id),KEYidx_user_status(user_id,status),KEYidx_order_no(order_no))ENGINEInnoDBDEFAULTCHARSETutf8mb4;3.1 使用 EXPLAIN 分析执行计划-- 1. 有效索引EXPLAINSELECT*FROMordersWHEREuser_id123ANDstatus1;-- type: ref, key: idx_user_status, rows: 很小-- 2. 索引失效跳过左侧EXPLAINSELECT*FROMordersWHEREstatus1;-- type: ALL全表扫描-- 3. 索引失效隐式类型转换EXPLAINSELECT*FROMordersWHEREorder_no123;-- order_no 是 varchar传入 int-- key: NULL不会用 idx_order_no-- 4. 索引失效函数操作EXPLAINSELECT*FROMordersWHEREDATE(create_time)2026-01-01;-- key: NULL-- 5. 覆盖索引无需回表EXPLAINSELECTuser_id,statusFROMordersWHEREuser_id123;-- Extra: Using index3.2 索引下推优化ICPMySQL 5.6 默认开启。对于idx_user_status (user_id, status)SELECT*FROMordersWHEREuser_id123ANDstatus1;没有 ICP先在二级索引找到 user_id123 的所有主键可能几百个再回表读取完整行再判断 status1。有 ICP在二级索引遍历时直接判断 status1不匹配的就不回表大幅减少回表次数。3.3 排序与索引-- 索引能支持排序避免 filesortEXPLAINSELECT*FROMordersWHEREuser_id123ORDERBYstatus;-- Extra: Using index condition无 filesort-- 索引不能支持排序跳过了 user_idEXPLAINSELECT*FROMordersORDERBYstatus;-- Extra: Using filesort3.4 MySQL 8.0 新特性隐藏索引与降序索引-- 隐藏索引不被优化器使用用于测试删除影响ALTERTABLEordersALTERINDEXidx_user_status INVISIBLE;-- 再次查询观察是否走索引-- 降序索引联合索引中指定降序CREATEINDEXidx_user_create_descONorders(user_idASC,create_timeDESC);-- 适用于 ORDER BY user_id ASC, create_time DESC 场景四、避坑要点高频失效场景错误写法原因正确做法WHERE status 1联合索引跳过最左列建立 status 单独索引或带上 user_idWHERE order_no 123隐式类型转换varchar → int应用层类型匹配order_no 123WHERE LEFT(name,3) abc函数操作破坏索引改用name LIKE abc%或冗余存储WHERE create_time 1 ...列参与运算改为create_time ... - 1WHERE name LIKE %abc前模糊匹配尽量放后面LIKE abc%或使用倒排索引/ESOR连接不同列可能不走索引拆分为 UNION或用IN替代!或范围查询大概率全表考虑业务重构或用IS NULL/IS NOT NULL某些情况不走索引根据实际数据分布必要时建索引统计信息过期优化器选择错误执行计划ANALYZE TABLE更新统计信息在线加索引未注意 MDL 锁短暂阻塞写操作使用ALGORITHMINPLACE, LOCKNONE五、面试高频考点Q1什么是回表如何避免二级索引查到主键后再到聚簇索引查询完整行的过程。避免方法使用覆盖索引索引包含查询所需所有字段或直接查主键。Q2最左前缀原则是什么举例说明。联合索引从左到右匹配跳过中间列则后续无效。例如索引(a, b, c)条件a1 AND c1只用到 ac 失效。原因是索引排序规则。Q3为什么用 BTree 而不用 B-Tree 或 HashBTree非叶子只存索引叶子存数据且形成链表树矮、范围查询快。B-Tree非叶子也存数据层数更高I/O 更多。Hash单点查询 O(1)但不支持范围、排序、模糊查询。Q4索引下推ICP是什么MySQL 5.6在索引遍历时直接过滤条件减少回表次数。仅适用于二级索引且条件列在索引中。Q5如何设计联合索引原则区分度高的列在前等值查询列在前范围查询列在后考虑排序需求。示例where a1 and b2 order by c建议索引(a, c)避免 filesort。Q6主键为什么建议自增自增主键保证插入时顺序写入减少页分裂UUID 主键随机插入页分裂频繁导致索引碎片性能差。Q7在线加索引会导致锁表吗MySQL 5.6 支持 Online DDLALGORITHMINPLACE, LOCKNONE主库加索引不阻塞写但仍有短暂元数据锁MDL。生产环境建议在低峰期操作。Q8MySQL 优化器如何选择索引基于索引统计信息Cardinality索引唯一值数量。如果统计信息过期优化器可能误判导致用错索引。执行ANALYZE TABLE可更新统计信息。MySQL 8.0 将统计信息持久化减少问题发生。Q9什么是隐藏索引Invisible IndexMySQL 8.0 引入可设置索引对优化器不可见但 DML 仍维护索引。用于测试删除索引的影响避免直接删除后重建的麻烦。Q10索引与锁的关系在不走索引的UPDATE/DELETE中InnoDB 会锁全表实际是锁所有聚簇索引扫描到的行。走索引则只锁索引覆盖的行减少锁冲突。六、练习题分析有一张订单表查询SELECT * FROM orders WHERE user_id123 ORDER BY create_time DESC LIMIT 10如何建索引最优 思路等值查询user_id放在前面排序create_time放在后面(user_id, create_time)可同时支持过滤和排序避免 filesort。案例某慢查询日志显示SELECT id,name,age FROM user WHERE name LIKE %张%耗时 5 秒如何优化 思路前模糊无法走普通索引可用倒排索引存储反转字符串或 Elasticsearch或改用搜索引擎。代码使用EXPLAIN分析你项目中的一条慢查询找出优化点。 你的学习进度当前第83篇 / 共108篇 ·进阶篇数据库与持久层框架第83~90篇✅ 已完成基础篇44篇 第91~96篇Redis/MQ 第83篇 正在学第83篇⏳ 待学习第84~90篇MySQL 事务/锁/SQL优化/MyBatis 第97~108篇微服务/物联网/AI/设计模式/面试压轴 完整目录 学习指南 | 订阅本专栏不错过每一篇 本专栏每篇都包含避坑表 面试高频考点 练习题。每天30分钟100天拿offer 下一篇文章预告《第84篇MySQL事务与锁2026版》内容简介事务四大特性ACID、隔离级别读未提交→读已提交→可重复读→串行化、MVCC 原理、间隙锁与幻读、死锁排查与解决。 学完这篇你将彻底搞懂 MySQL 并发控制面试不再怕“RR 如何解决幻读”。福利提醒评论区留言“MySQL索引”可领取《MySQL 索引优化实战清单》PDF。《Java 100 天进阶之路 | 从入门到上岗就业》每天一篇建议收藏 关注一起100天拿offer 点击关注我更新后第一时间收到推送
《Java 100 天进阶之路》第83篇：MySQL索引（2026版）
发布时间：2026/6/11 17:12:37
第83篇MySQL索引2026版系列导航《Java 100 天进阶之路》完整目录 |⬅️ 上一篇第82篇Spring面试压轴题待发布 |➡️ 下一篇第84篇MySQL事务与锁待发布一、核心知识点索引本质排好序的快速查找数据结构BTree 索引MySQL InnoDB 默认索引结构叶子节点存储数据非叶子节点存索引聚簇索引 vs 二级索引聚簇索引叶子存整行数据二级索引叶子存主键值回表二级索引查到主键后再到聚簇索引查完整行覆盖索引索引列包含查询所需的所有字段无需回表最左前缀原则联合索引从左到右匹配跳过中间列则后面失效索引下推ICPMySQL 5.6在索引遍历时直接过滤减少回表次数索引失效场景函数操作、隐式类型转换、%开头的模糊查询等MySQL 8.0 新特性隐藏索引、降序索引、索引统计信息持久化二、通俗讲解1分钟开心学1. 索引是什么索引是数据库的“目录”。没有索引就像一本没有目录的书要找某句话只能一页页翻全表扫描有了索引直接翻到对应页码即可。生活类比图书馆的图书分类标签索书号就是索引。你要找《Java编程思想》先查电脑索引得到索书号 TP312JA/123然后直接去书架定位不用逛遍整个图书馆。2. BTree 为什么适合数据库BTree 特点非叶子节点只存索引不存数据能存放更多索引项降低树高度一般 2~4 层。叶子节点形成双向链表支持范围查询和顺序遍历。数据均匀分布在叶子节点查询稳定任何查找都 O(logN)。对比其他数据结构Hash 索引单点查询快但不支持范围查询仅 Memory 引擎默认。二叉树可能退化成链表树高不可控。B-Tree非叶子也存数据导致每层能存的索引变少树更高。3. 聚簇索引 vs 二级索引聚簇索引叶子节点存储整行数据。InnoDB 中主键就是聚簇索引没有主键则第一个 NOT NULL UNIQUE 列都没有则隐式生成 rowid。二级索引辅助索引叶子节点存储主键值查询时先找到主键再到聚簇索引回表取完整数据。生活类比聚簇索引就像按学号排好的学生档案柜学号对应整套档案。二级索引就像按姓名建的索引卡上面写着学号你需要根据学号再去档案柜取档案回表。4. 最左前缀原则联合索引(a, b, c)相当于按 a 排序a 相同时按 b 排序b 相同时按 c 排序。查询时从索引最左边开始匹配跳过中间列则后续无法使用。✅WHERE a 1 AND b 2 AND c 3全用✅WHERE a 1 AND b 2用 a、b✅WHERE a 1用 a❌WHERE b 2无法使用跳过了 a❌WHERE a 1 AND c 3只用 ac 失效三、实操代码案例 场景说明测试表用户订单表数据量 100 万。CREATETABLEorders(idbigint(20)NOTNULLAUTO_INCREMENT,user_idint(11)NOTNULL,order_novarchar(32)NOTNULL,amountdecimal(10,2)DEFAULTNULL,statustinyint(4)DEFAULT0,create_timedatetimeDEFAULTCURRENT_TIMESTAMP,PRIMARYKEY(id),KEYidx_user_status(user_id,status),KEYidx_order_no(order_no))ENGINEInnoDBDEFAULTCHARSETutf8mb4;3.1 使用 EXPLAIN 分析执行计划-- 1. 有效索引EXPLAINSELECT*FROMordersWHEREuser_id123ANDstatus1;-- type: ref, key: idx_user_status, rows: 很小-- 2. 索引失效跳过左侧EXPLAINSELECT*FROMordersWHEREstatus1;-- type: ALL全表扫描-- 3. 索引失效隐式类型转换EXPLAINSELECT*FROMordersWHEREorder_no123;-- order_no 是 varchar传入 int-- key: NULL不会用 idx_order_no-- 4. 索引失效函数操作EXPLAINSELECT*FROMordersWHEREDATE(create_time)2026-01-01;-- key: NULL-- 5. 覆盖索引无需回表EXPLAINSELECTuser_id,statusFROMordersWHEREuser_id123;-- Extra: Using index3.2 索引下推优化ICPMySQL 5.6 默认开启。对于idx_user_status (user_id, status)SELECT*FROMordersWHEREuser_id123ANDstatus1;没有 ICP先在二级索引找到 user_id123 的所有主键可能几百个再回表读取完整行再判断 status1。有 ICP在二级索引遍历时直接判断 status1不匹配的就不回表大幅减少回表次数。3.3 排序与索引-- 索引能支持排序避免 filesortEXPLAINSELECT*FROMordersWHEREuser_id123ORDERBYstatus;-- Extra: Using index condition无 filesort-- 索引不能支持排序跳过了 user_idEXPLAINSELECT*FROMordersORDERBYstatus;-- Extra: Using filesort3.4 MySQL 8.0 新特性隐藏索引与降序索引-- 隐藏索引不被优化器使用用于测试删除影响ALTERTABLEordersALTERINDEXidx_user_status INVISIBLE;-- 再次查询观察是否走索引-- 降序索引联合索引中指定降序CREATEINDEXidx_user_create_descONorders(user_idASC,create_timeDESC);-- 适用于 ORDER BY user_id ASC, create_time DESC 场景四、避坑要点高频失效场景错误写法原因正确做法WHERE status 1联合索引跳过最左列建立 status 单独索引或带上 user_idWHERE order_no 123隐式类型转换varchar → int应用层类型匹配order_no 123WHERE LEFT(name,3) abc函数操作破坏索引改用name LIKE abc%或冗余存储WHERE create_time 1 ...列参与运算改为create_time ... - 1WHERE name LIKE %abc前模糊匹配尽量放后面LIKE abc%或使用倒排索引/ESOR连接不同列可能不走索引拆分为 UNION或用IN替代!或范围查询大概率全表考虑业务重构或用IS NULL/IS NOT NULL某些情况不走索引根据实际数据分布必要时建索引统计信息过期优化器选择错误执行计划ANALYZE TABLE更新统计信息在线加索引未注意 MDL 锁短暂阻塞写操作使用ALGORITHMINPLACE, LOCKNONE五、面试高频考点Q1什么是回表如何避免二级索引查到主键后再到聚簇索引查询完整行的过程。避免方法使用覆盖索引索引包含查询所需所有字段或直接查主键。Q2最左前缀原则是什么举例说明。联合索引从左到右匹配跳过中间列则后续无效。例如索引(a, b, c)条件a1 AND c1只用到 ac 失效。原因是索引排序规则。Q3为什么用 BTree 而不用 B-Tree 或 HashBTree非叶子只存索引叶子存数据且形成链表树矮、范围查询快。B-Tree非叶子也存数据层数更高I/O 更多。Hash单点查询 O(1)但不支持范围、排序、模糊查询。Q4索引下推ICP是什么MySQL 5.6在索引遍历时直接过滤条件减少回表次数。仅适用于二级索引且条件列在索引中。Q5如何设计联合索引原则区分度高的列在前等值查询列在前范围查询列在后考虑排序需求。示例where a1 and b2 order by c建议索引(a, c)避免 filesort。Q6主键为什么建议自增自增主键保证插入时顺序写入减少页分裂UUID 主键随机插入页分裂频繁导致索引碎片性能差。Q7在线加索引会导致锁表吗MySQL 5.6 支持 Online DDLALGORITHMINPLACE, LOCKNONE主库加索引不阻塞写但仍有短暂元数据锁MDL。生产环境建议在低峰期操作。Q8MySQL 优化器如何选择索引基于索引统计信息Cardinality索引唯一值数量。如果统计信息过期优化器可能误判导致用错索引。执行ANALYZE TABLE可更新统计信息。MySQL 8.0 将统计信息持久化减少问题发生。Q9什么是隐藏索引Invisible IndexMySQL 8.0 引入可设置索引对优化器不可见但 DML 仍维护索引。用于测试删除索引的影响避免直接删除后重建的麻烦。Q10索引与锁的关系在不走索引的UPDATE/DELETE中InnoDB 会锁全表实际是锁所有聚簇索引扫描到的行。走索引则只锁索引覆盖的行减少锁冲突。六、练习题分析有一张订单表查询SELECT * FROM orders WHERE user_id123 ORDER BY create_time DESC LIMIT 10如何建索引最优 思路等值查询user_id放在前面排序create_time放在后面(user_id, create_time)可同时支持过滤和排序避免 filesort。案例某慢查询日志显示SELECT id,name,age FROM user WHERE name LIKE %张%耗时 5 秒如何优化 思路前模糊无法走普通索引可用倒排索引存储反转字符串或 Elasticsearch或改用搜索引擎。代码使用EXPLAIN分析你项目中的一条慢查询找出优化点。 你的学习进度当前第83篇 / 共108篇 ·进阶篇数据库与持久层框架第83~90篇✅ 已完成基础篇44篇 第91~96篇Redis/MQ 第83篇 正在学第83篇⏳ 待学习第84~90篇MySQL 事务/锁/SQL优化/MyBatis 第97~108篇微服务/物联网/AI/设计模式/面试压轴 完整目录 学习指南 | 订阅本专栏不错过每一篇 本专栏每篇都包含避坑表 面试高频考点 练习题。每天30分钟100天拿offer 下一篇文章预告《第84篇MySQL事务与锁2026版》内容简介事务四大特性ACID、隔离级别读未提交→读已提交→可重复读→串行化、MVCC 原理、间隙锁与幻读、死锁排查与解决。 学完这篇你将彻底搞懂 MySQL 并发控制面试不再怕“RR 如何解决幻读”。福利提醒评论区留言“MySQL索引”可领取《MySQL 索引优化实战清单》PDF。《Java 100 天进阶之路 | 从入门到上岗就业》每天一篇建议收藏 关注一起100天拿offer 点击关注我更新后第一时间收到推送
