上一篇我们搭好了 MySQL 的骨架——了解了 SQL 的执行流程和 InnoDB 存储引擎。你可能会想知道这些有什么用呢答案是——如果不知道索引的原理你写的 SQL 就像在图书馆里一本本翻书找资料。而有了索引就相当于有了图书管理员的检索系统几十万本书里秒级定位已经烂大街了的例子。这一篇我们来掌握这个 Java 面试中出现频率超过 80%的核心话题。先热个身索引到底是什么抛开所有课本定义我们从一个生活中的例子开始。你想在新华字典里找到张字。正常人的做法是翻到拼音目录z 开头然后逐层缩小范围——这就是索引的作用。没有人会从第一页阿字开始逐个找。MySQL 的索引就是同一回事一种有序的数据结构让数据库能以极少的磁盘 I/O 定位到你要的数据。为什么磁盘 I/O 这么关键因为内存读取纳秒级和磁盘读取毫秒级之间差了10 万倍。一次磁盘寻找的时间CPU 可以执行数十万条指令。所以衡量数据库性能的核心指标不是扫描了多少行而是用了多少次磁盘 I/O。一、BTreeMySQL 索引的发动机1.1 为什么 BTree 能成为首选数据库索引可以用哈希表、二叉树、B 树……MySQLInnoDB最终选择了BTree。这不是拍脑门的决定而是对大量数据、高并发、支持范围查询三大需求反复权衡后的结果。1.2 BTree 长什么样[8 | 15] -- 非叶子节点只存索引键不存数据 / | \ [3|6] [10|12] [18|22] -- 非叶子节点进一步缩小范围 / | \ ... ... \ [1|2]↔[4|5]↔[6|7] ... -- 叶子节点存全部数据双向链表连接看懂三句话上面的是路牌非叶子节点告诉你往哪走本身不存数据。最下面的是目的地叶子节点存储了所有记录。所有目的地之间用双向箭头连起来双向链表方便前后遍历。1.3 BTree 的四个核心优势面试必考特性说明带来的好处数据全在叶子节点非叶子节点只存键值不放数据一个节点能装更多键树更矮磁盘 I/O 更少叶子节点是双向链表所有叶子节点首尾相连范围查询BETWEEN、、、ORDER BY极快树高极低千万级数据表树高通常只有 3-4 层一次查询只需 3-4 次磁盘 I/O查询稳定每次都要走到叶子节点O(log n) 复杂度性能可预测1.4 一次索引查询做了多少次 I/O以 2000 万行数据的表为例BTree 每个节点约 16KBInnoDB 页大小 如果主键是 BIGINT8字节 指针6字节每个节点可存约 1170 个键 层1根节点 1 个节点1170 个指针 层2 1170 个节点1170 × 1170 ≈ 137 万条键 层3叶子 137 万 × 1170 ≈ 16 亿行 → 远超 2000 万 所以 2000 万数据只需要 2-3 层一次主键查询2-3 次磁盘 I/O 即可。这就是索引为什么能让大数据量表依然秒级响应的秘密。BTree 的结构我们搞清楚了。但它具体怎么存储我们表中的数据呢这就引出了两个核心概念聚簇索引和二级索引。二、聚簇索引与二级索引数据到底存在哪2.1 聚簇索引主键索引InnoDB 中表数据按照主键顺序物理存储在 BTree 的叶子节点中。这意味着主键索引的叶子节点 一整行数据。聚簇索引主键为 id [id1, 张三, 25, 北京] ↔ [id2, 李四, 30, 上海] ↔ [id3, 王五, 28, 广州] ↑ 叶子节点直接存储了整行数据的所有列这就解释了为什么自增主键很重要如果主键是递增的新数据直接追加到 BTree 的最后面操作干净利落。如果是随机的 UUID就要在树的中间插入——可能触发页分裂一个数据页装不下了被迫拆分成两页性能大幅下降。2.2 二级索引辅助索引除了主键以外的索引统统叫二级索引。它的叶子节点存的不是整行数据而是主键值。二级索引name 列为索引 [李四, id2] ↔ [王五, id3] ↔ [张三, id1] ↑ 叶子节点只存 (索引键, 主键值)不存其他列2.3 回表是什么为什么大家都想避免它假设你执行了SELECT * FROM user WHERE name 张三;执行过程如下第 1 步在 name 索引树中查找张三 → 得到主键 id1 第 2 步拿着 id1 去主键索引树中查找 → 得到整行数据第 2 步就是回表——多走了一次主键索引树的查找意味着一倍的磁盘 I/O。2.4 覆盖索引一劳永逸-- ❌ 需要回表name 索引只有 name idage 和 city 要回表取 SELECT id, name, age, city FROM user WHERE name 张三; -- ✅ 覆盖索引建一个包含所有需要列的联合索引 CREATE INDEX idx_name_age_city ON user(name, age, city); -- 现在这个查询需要的所有列都在索引里了不需要回表 SELECT name, age, city FROM user WHERE name 张三;如何判断是否用了覆盖索引执行EXPLAIN看 Extra 列——出现Using index就表示覆盖索引生效。这是你写 SQL 时应该追求的绿色通行证。掌握了聚簇索引和回表的概念接下来我们面对的是日常开发中最常用的索引类型——联合索引。它看似简单却暗藏一个让无数新人踩坑的规则。三、联合索引与最左前缀原则3.1 什么是最左前缀原则联合索引按照创建时的列顺序进行排序。就像字典先按拼音首字母排、再按第二个字母排一样CREATE INDEX idx_a_b_c ON test(a, b, c); -- 排序逻辑先按 a 排a 相同再按 b 排b 相同再按 c 排最左前缀原则查询条件必须从索引的最左边的列a开始并且中间不能跳过才能利用索引。-- ✅ 走索引 SELECT * FROM test WHERE a 1; -- 用到 a SELECT * FROM test WHERE a 1 AND b 2; -- 用到 a, b SELECT * FROM test WHERE a 1 AND b 2 AND c 3; -- 用到 a, b, c SELECT * FROM test WHERE a 1 AND c 3; -- 用到 ac 没用到因为跳过了 b -- ❌ 不走索引 SELECT * FROM test WHERE b 2; -- 没有 a无从查起 SELECT * FROM test WHERE b 2 AND c 3; -- 同上 SELECT * FROM test WHERE c 3; -- 同上一句话记住没有最左边那一列后面的列就像断了线的珠子——散了。3.2 索引下推MySQL 5.6这是 MySQL 5.6 引入的一个智能优化理解它有助于面试中展示深度-- 联合索引 idx_name_age(name, age) SELECT * FROM user WHERE name LIKE 张% AND age 25;没有索引下推时先找到所有name LIKE 张%的主键 → 全部回表 → 再在 Server 层筛掉age ! 25的行。有索引下推时找到name LIKE 张%→直接在索引中判断 age25→ 只对满足条件的行回表。相当于把 WHERE 条件的过滤工作下推到了存储引擎层减少了不必要的回表。理论讲完了现在进入实战——怎么判断你写的 SQL 走不走索引遇到慢查询怎么定位问题四、EXPLAINSQL 优化的显微镜EXPLAIN SELECT * FROM orders WHERE user_id 100 ORDER BY create_time DESC;EXPLAIN 是 MySQL 提供的最强大的 SQL 分析工具。我们重点看三个字段4.1 type访问类型最重要从优到差排列system → 表只有一行系统表 const → 主键或唯一索引等值查询最多一行 ✅ 优秀 eq_ref → JOIN 时用主键或唯一索引关联 ✅ 优秀 ref → 普通索引等值查询 ✅ 良好 range → 索引范围扫描、、BETWEEN、IN ⚠️ 还行 index → 全索引扫描扫描整个索引树 ❌ 较差 ALL → 全表扫描 ❌ 最差实际开发标准至少要达到range级别。如果你在 EXPLAIN 里看到typeALL就等于数据库在对你喊我是在硬扫每一行啊大哥4.2 Extra额外信息值含义级别Using index覆盖索引不回表 最优Using whereServer 层做过滤 正常Using index condition索引下推生效 好的Using filesort需要额外排序 需要优化Using temporary用了临时表 严重问题4.3 key 和 rowskey实际使用的索引名。如果是 NULL说明索引没生效。rowsMySQL 预估要扫描的行数。这个数字越小越好是判断优化效果的直观指标。五、SQL 优化实战5.1 深分页面试经典题-- ❌ 深分页问题扫描前 100010 行再扔掉前 100000 行 SELECT * FROM orders ORDER BY id LIMIT 100000, 10;原理很直观MySQL 不知道第 100000 行在哪只能从第 1 行开始数数到第 100000 行再开始返回数据。推荐方案——延迟关联-- ✅ 先在索引中定位主键再用主键关联取全量数据 SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders ORDER BY id LIMIT 100000, 10 ) t ON o.id t.id;子查询只需要扫描id索引覆盖索引不用回表定位到 10 个主键后再关联获取完整数据。5.2 JOIN 优化小表驱动大表-- ❌ 关联字段没索引 → 被驱动表每次都要全表扫描 SELECT * FROM user u JOIN orders o ON u.id o.user_id WHERE u.name LIKE %张%; -- ✅ 优化方式 -- 1. orders.user_id 上建索引被驱动表的关联列必须有索引 -- 2. user 表先执行 WHERE 过滤用小结果集驱动大表 -- 3. 去掉前缀模糊 %张 → 张%让 user.name 索引生效核心原则JOIN 时 MySQL 会自动选择小表作为驱动表但被驱动表的 JOIN 列上必须有索引——否则每次关联都是一次全表扫描。5.3 ORDER BY 优化利用索引排序-- ❌ Extra 出现 Using filesort需要额外排序消耗大 SELECT * FROM orders WHERE status 1 ORDER BY create_time DESC; -- ✅ 建联合索引让 WHERE 和 ORDER BY 共用一个索引 CREATE INDEX idx_status_ctime ON orders(status, create_time); SELECT * FROM orders WHERE status 1 ORDER BY create_time DESC; -- 索引本身就是按 (status, create_time) 排序的不需要额外排序5.4 建立索引的六要六不要六要WHERE、JOIN、ORDER BY 的列要建索引区分度高的列要建索引手机号、邮箱等优先建覆盖索引减少回表字符串字段考虑前缀索引idx(title(20))联合索引把区分度高的列放左边频繁查询的列优先考虑六不要不在大字段上建索引TEXT、BLOB不在低区分度列建索引性别只有男女不在索引列上做函数/表达式运算不使用%开头的模糊匹配不建过多索引单表不超过 5 个不忽略最左前缀原则5.5 COUNT 优化-- InnoDB 下COUNT(*) 和 COUNT(1) 效率相同优化器会改写 -- COUNT(col) 只统计 col 非 NULL 的行可能比 COUNT(*) 慢 -- ✅ 需要粗略值时 SELECT TABLE_ROWS FROM information_schema.TABLES WHERE TABLE_SCHEMA mydb AND TABLE_NAME orders; -- ✅ 需要精确值时用汇总表 定时任务维护本篇回顾学完这一篇你应该能回答BTree 长什么样为什么 MySQL 选它做索引聚簇索引和二级索引的本质区别是什么什么是回表覆盖索引如何避免回表最左前缀原则是什么索引下推解决了什么问题EXPLAIN 的 type 和 Extra 字段怎么看深分页、JOIN、ORDER BY 如何优化索引是 MySQL 性能优化的绝对核心。面试官问你怎么优化慢 SQL80% 的答案都与索引有关。建议你在自己项目里找几条慢 SQL用 EXPLAIN 分析一下看到typeALL或Using filesort就去建个合适的索引——这种真刀真枪的练习比刷十道题更有用。下一篇我们将进入 MySQL 中最烧脑但也最精彩的部分——事务、锁与 MVCC。你会理解为什么你转账 100 元不会凭空消失、以及数据库是怎么让几百个人同时操作而数据不乱的。【上一篇Java开发必知必会的MySQL核心知识点(一)-基础入门从零开始认识数据库核心】【下一篇Java开发必知必会的MySQL核心知识点(三)-深入理解事务、锁与 MVCC】