MySQL 基础教程 - 第八章：索引与性能优化基础
做了这么多年 MySQL 相关的开发和调优，我越来越觉得索引这东西，知道怎么建和真正理解为什么这么建，完全是两码事。很多初学者对索引的印象就停留在“查询慢了加个索引就好”，可真到线上出了问题，往往不知道从哪下手。这一章我们专门聊聊索引与性能优化的基础，我会把索引的底层原理、常见类型、建索引的核心逻辑、以及怎么用 EXPLAIN 和慢查询日志定位问题都串一遍，让不熟悉这块的朋友也能建立一个完整、可用的知识框架。
这一章适合谁看呢？一类是刚系统学完 SQL 语法、想进一步弄明白“为什么这条 SQL 慢”的初学者；另一类是写业务代码多年、平时能跑就行、但一说起执行计划就含糊其辞的开发同学。目标很直接：看完之后你能判断一条慢 SQL 到底该不该建索引、该怎么建，并且能用工具验证自己的判断，而不是靠猜。
1. 索引到底解决了什么问题
1.1 从全表扫描说起
先想一个最基础的问题：一条 SELECT 语句在 MySQL 里是怎么找数据的。假设有一张用户表，里面有 100 万行记录，你现在要执行SELECT * FROM user WHERE age = 28。
在没有索引的情况下，MySQL 只能把主键索引（也就是聚簇索引）的整棵 B+ 树叶子节点从头到尾扫一遍，逐行检查 age 字段是否等于 28。这个过程叫全表扫描，时间复杂度是 O(N)。100 万行数据可能还感觉不明显，等数据量到几千万甚至上亿，一次全表扫描消耗的磁盘 I/O 和 CPU 资源会让你怀疑人生。
索引做的事情说白了就是给字段建立一种“目录结构”，让数据库不需要全表遍历就能快速定位到目标行。你可以类比成一本几百页的书：没有目录，你要找某个知识点就得一页页翻；有了目录，直接翻到对应章节页码就行了。索引就是数据库里的“目录”。
1.2 索引为什么能快：B+ 树结构与二分查找
MySQL 默认的存储引擎 InnoDB 使用 B+ 树作为索引的数据结构。B+ 树是一种多路平衡查找树，它的特点是：
- 所有数据都存储在叶子节点，并且叶子节点之间通过双向指针连接；
- 非叶子节点只存储索引键值和指向子节点的指针；
- 树的层数很低，通常三层左右就能存储千万级数据。
三层是什么概念？也就是你查询一条数据最多只需要经历三次磁盘 I/O 左右，就能从根节点走到叶子节点。相比之下，全表扫描可能要读取几万个数据页。这就是索引能大幅提升查询性能的核心原因。
有一类特殊索引叫哈希索引，在 InnoDB 中主要用于自适应哈希索引，它通过哈希函数直接定位数据，查询速度更快，但只支持等值查询，不支持范围查询和排序。日常开发中我们主要使用 B+ 树索引，选型时要根据业务查询类型来定。
2. 索引类型与使用场景拆解
2.1 主键索引、普通索引、唯一索引有什么区别
从功能定位上，索引可以分成几类：
主键索引（PRIMARY KEY）：一种特殊的唯一索引，不允许有空值。InnoDB 中每张表都必须有一个主键索引，它就是聚簇索引，数据行实际存储在它的叶子节点上。如果没有显式定义主键，InnoDB 会选择一个非空唯一索引，或生成隐藏主键。
唯一索引（UNIQUE KEY）：不允许重复值，但允许 NULL。它和主键索引最大的区别在于一个表只能有一个主键，但可以有多个唯一索引。业务上像身份证号、手机号这类业务标识字段，就适合建唯一索引来保证数据唯一性和查询效率。
普通索引（INDEX）：也叫二级索引，不约束值的唯一性。它针对查询频繁但不是唯一的字段建立，比如订单表里的 user_id。
2.2 单列索引与联合索引
单列索引，顾名思义，只对一个字段建立索引。联合索引则是对多个字段按照一定顺序建立的一个索引，比如INDEX idx_user_status (user_id, status)。
联合索引必须遵守最左前缀原则。也就是说，查询条件中必须用到联合索引的最左边字段，这个索引才会生效。例如上面的联合索引，WHERE user_id = 100和WHERE user_id = 100 AND status = 1都能用到索引，但如果直接WHERE status = 1，索引就无法使用。
这个原则本质上是由 B+ 树索引的存储结构决定的。联合索引在排序时先按第一个字段排序，第一个字段相同的按第二个字段排序。所以查询条件如果不包含最左字段，B+ 树根本没法利用索引本身的顺序性。
2.3 前缀索引与覆盖索引
字符串类型的字段，比如 URL、用户昵称，往往很长。如果对整个字段建索引，索引占用的空间大，而且比较速度慢。这时可以只对字段的前 N 个字符建立索引，叫前缀索引。比如INDEX idx_nickname (nickname(10))，只取昵称前 10 个字符。需要注意的是，前缀索引可能会导致查询结果不精确，MySQL 拿到前缀匹配的行之后还要回表再判断完整值，所以选择前缀长度时要在区分度和长度之间找平衡。
覆盖索引是指查询的字段全部包含在索引字段中，不需要回表查询数据行。比如你在 user_id 和 status 上建了联合索引，查询SELECT user_id, status FROM order WHERE user_id = 5，那么索引叶子节点已经包含了这两个字段的值，MySQL 就不用再回聚簇索引查完整行了。覆盖索引是减少 I/O 损耗的重要手段，在设计查询时尽量让索引“覆盖”掉查询字段，是一种非常实用的优化思路。
3. 实操：建索引的正确姿势与参数细节
3.1 建索引前的字段选择分析
我见过不少开发同学，一遇到慢查询就随手给 WHERE 条件的字段加索引，完全不管字段区分度、业务查询模式和数据分布。这种“无脑加索引”的做法，短期看似解决了问题，实际上可能给后续埋下一堆坑。
一个字段值不同种类越多、分布越均匀，区分度就越高，建索引效果越好。区分度具体有多好，可以通过一条 SQL 计算出来：
SELECT COUNT(DISTINCT column_name) / COUNT(*) AS selectivity FROM table_name;这个比值约接近 1，说明字段区分度越高，索引效果越好。比如“性别”字段只有两个取值，区分度就是 2/总行数，非常低，建了索引也很难有效过滤数据；而“订单号”这种几乎唯一的字段，区分度接近 1，非常适合建索引。
实际操作中，我通常按照这个思路来选字段：
- 优先给 WHERE 条件中经常出现的字段建索引；
- 对 ORDER BY、GROUP BY 的字段建索引，可以有效避免文件排序；
- JOIN 的关联字段必须建索引，否则大表 join 会非常痛苦；
- 数据量小（比如几百上千行）的表，索引意义不大，反而增加写入负担。
3.2 联合索引怎么设计才能不踩坑
联合索引的字段顺序，是最容易出错的地方。很多人下意识把查询频率最高的字段放在最前面，其实不完全正确。举个例子，你要建一个 (a, b) 联合索引，如果单查 b 也很常见，而 a 的区分度很低，可能就需要考虑拆成两个索引来设计。
字段顺序的确定原则是：把区分度高、查询条件中最常固定使用的字段放在前面。比如订单表经常按 user_id + status 组合查询，而且 user_id 的区分度明显高于 status，那联合索引应该设计成 (user_id, status)，这样既能直接支撑组合查询，也能单独支撑 user_id 查询，一举两得。
还要注意一个细节：联合索引需要考虑排序需求。如果业务上既要WHERE user_id = ? ORDER BY create_time，那么设计成(user_id, create_time)联合索引就非常合适，因为 B+ 树在 user_id 相同的情况下已经按 create_time 排序好了，查询时不需要额外排序。
3.3 SQL 写法对索引的影响
建好了索引，不代表每条 SQL 都能用上。SQL 的写法对索引是否生效有决定性影响。最常见的“索引失效”场景包括：
- 对索引字段使用了函数，比如
WHERE DATE(create_time) = '2024-01-01'，这样索引就不再生效，因为 MySQL 无法对函数处理后的结果做索引查找； - 隐式类型转换，比如字段是字符串类型，但查询用数字去比较，MySQL 会先把字段转成数字，索引也会失效；
- LIKE 查询中前面使用了通配符，比如
LIKE '%abc'，从索引树中间位置无法定位，只能全表扫； - 用 OR 连接非索引字段和索引字段，可能导致索引失效。
所以，代码评审时我特别关注 SQL 写法：字段是否被函数包裹、参数类型是否和字段类型一致、联合索引是否从最左列开始。这些看起来是小问题，线上却能搞出大事。
4. 索引优化进阶：EXPLAIN 执行计划解读
4.1 EXPLAIN 怎么用，重点看哪些列
写了 SQL，建了索引，到底有没有用？最直接的办法就是用 EXPLAIN 看执行计划。MySQL 会告诉我们查询优化器打算怎么执行这条 SQL。
基本用法很简单，在 SELECT 语句前加上 EXPLAIN 关键字：
EXPLAIN SELECT user_id, status FROM order WHERE user_id = 100 AND status = 1;输出结果中重点关注的列有这些：
- type：访问类型，从好到差依次是 system > const > eq_ref > ref > range > index > ALL。如果出现 ALL，那就是全表扫描，基本意味着索引没建好或者没用上。
- key：实际使用的索引名称。如果为 NULL，说明这条 SQL 没走任何索引。
- rows：MySQL 预估需要扫描的行数。这个数字越小越好，和实际扫描行数差距太大时就要留意统计信息是否过期。
- Extra：额外信息。里面如果出现 Using filesort 或 Using temporary，说明查询需要额外排序或临时表，性能通常好不到哪去；出现 Using index 则是好消息，说明用了覆盖索引。
我第一次真正理解 type 列的价值，是排查一条线上慢查询。当时有个统计报表 SQL，EXPLAIN 出来 type 是 ALL，rows 显示要扫 800 万行。后来通过调整查询条件和索引设计，type 变成了 ref，rows 降到几千行，查询时间从 3 秒降到 0.05 秒以下。这就是执行计划带来的直观价值。
4.2 常见的 Extra 信息具体是什么含义
EXPLAIN 的 Extra 列最容易被忽略，但它往往藏着性能瓶颈的答案。
Using filesort 表示 MySQL 需要对结果额外做一次排序操作。这个排序不一定是磁盘文件，也可能发生在内存中，但无论如何，它都意味着无法利用索引自带的顺序性。遇到这个情况，优先考虑设计联合索引让 ORDER BY 字段直接命中索引顺序。
Using temporary 表示查询过程中使用了临时表，常见于 GROUP BY、DISTINCT 以及多表连接中某些复杂场景。临时表的开销很大，如果在高频查询中频繁出现，最好是改写 SQL 或调整索引设计来规避。
Using index 则是最理想的状态，查询到的数据全部来自索引，不需要回表。比如覆盖索引查询就会出现这个标识。很多优化场景的目标，就是让尽量多的查询达到这个状态。
4.3 统计信息与索引基数
执行计划是优化器基于统计信息做的决策。InnoDB 会维护索引的基数（Cardinality），也就是索引中不同值的数量估算。如果这个统计信息不准确，优化器可能会选错索引，明明有索引却不用。
遇到这种情况，可以运行ANALYZE TABLE table_name更新统计信息。表数据量发生较大变化、或者大批量导入数据之后，最好主动执行一次，让优化器有准确的依据。
5. 慢查询日志：定位索引问题的第一工具
5.1 怎么打开慢查询日志
光靠 EXPLAIN 分析单条 SQL 还不够，线上慢 SQL 怎么发现？答案是慢查询日志。MySQL 可以把执行时间超过设定阈值的 SQL 记录下来，是定位性能问题的第一入口。
开启和配置慢查询日志，可以动态设置：
SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 1; SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';long_query_time 的单位是秒，1 表示超过 1 秒的 SQL 会被记录。上线初期建议先设为 1，等优化稳定后再慢慢调低到 0.5 甚至 0.1。设置太低会记录大量 SQL，日志文件膨胀非常快，影响磁盘空间和排查效率。
5.2 慢查询日志里怎么分析
分析慢查询日志，我一般分三步走：
第一步，找出出现频率最高的慢 SQL。用工具统计慢查询日志中相同 SQL 出现的次数，优先处理频率最高的那些。
第二步，拿到慢 SQL 后先跑 EXPLAIN，确认有没有走索引、type 是什么级别、rows 预估多少。
第三步，根据执行计划反推索引设计的问题。如果 type 是 ALL 或者 rows 预估很大，那就回到第 3 节说的字段选择原则重新评估。
过去我处理过一条典型的慢 SQL，是报表统计类的，频繁查询一张上亿行的流水表。EXPLAIN 显示 type 为 ALL。分析后发现，查询条件里有三个过滤字段，但索引只建了其中一个，联合索引严重设计不足。改造索引后，rows 从亿级别降到百万级别，查询时间从 30 多秒降到 0.8 秒左右。这就是慢查询日志加执行计划双重分析带来的效果。
5.3 慢查询日志分析的常用工具
手工一条条看慢查询日志很费劲。如果环境允许，可以装一些开源工具辅助分析。比如 mysqldumpslow 是 MySQL 自带的慢查询日志汇总工具，可以把相同模式的 SQL 归并排序；Percona Toolkit 里的 pt-query-digest 功能更强，能按总耗时、平均耗时、响应时间占比排序，直接输出分析报告。
需要提醒的是，分析工具只帮你定位问题，真正的性能优化还是要回到索引结构和 SQL 写法上。
6. 回表、索引下推与常见索引失效案例
6.1 什么是回表查询
很多初学者第一次看到“回表”这个词会有点懵。它的意思是，通过二级索引找到主键值后，还要再根据主键值去聚簇索引里查找完整的数据行。因为二级索引的叶子节点只存了索引字段和主键值，不存完整行数据。
回表本身并不是坏事，它是 InnoDB 存储引擎的正常工作方式。但回表意味着额外的磁盘 I/O，如果二级索引过滤后命中了大量数据行，回表次数就会非常多，性能随之下降。避免回表的思路就是前面说的覆盖索引，让查询字段全部被索引覆盖，一步到位。
6.2 索引下推是怎么减少回表的
索引下推（Index Condition Pushdown，简称 ICP）是 MySQL 5.6 引入的优化手段。它允许存储引擎在索引遍历过程中，直接过滤掉不满足索引中其他字段条件的数据，减少回表次数。
举个例子，假设有联合索引 (name, age)，查询WHERE name LIKE '张%' AND age = 28。在没有 ICP 的时候，存储引擎会把所有 name 前缀匹配的记录都回表，把完整的行取出来，再判断 age 是否等于 28。有了 ICP，存储引擎在索引叶子节点上就能判断 age 条件，提前过滤掉不满足的记录，只有真正符合条件的才回表。
这个优化对联合索引的查询场景帮助很大，也是为什么我们在设计联合索引时，把多个过滤条件都放进索引里是明智选择的原因之一——即使做不到完全覆盖，索引下推也能帮你省掉大量无效回表。
6.3 实战案例：一个典型索引失效问题的完整排查
最后分享一个很有代表性的排查案例。某晚上线上订单查询接口突然变慢，平均耗时从 200ms 涨到 3 秒以上。我先打开慢查询日志，找到了一条按订单创建时间范围查询的 SQL：SELECT id, order_no, amount FROM order WHERE create_time BETWEEN '2023-12-01 00:00:00' AND '2023-12-31 23:59:59'。
当时表上是创建了 create_time 单列索引的，理论上应该能走索引。可是 EXPLAIN 一看，type 是 ALL，rows 接近全表。经过检查，问题出在索引因为数据分布原因被优化器放弃了：create_time 字段上有一大段数据值几乎连续，优化器判断走索引还需要大量回表，代价比全表扫描还大。
处理方法：把查询改成主键范围分段查询，同时在索引设计上增加覆盖字段，把 id、order_no、amount 全部放进索引，改成(create_time, order_no, amount)联合索引。这样查询直接走索引，还能避免回表。改造之后，接口耗时恢复到 180ms 左右。
这个案例给我的启发是：索引优化不是一锤子买卖，不要在建立索引后就高枕无忧。实际数据分布、查询模式的变化，都可能让原本合理的索引失效。定期用 EXPLAIN 抽查线上慢查询，学会读执行计划，才能把索引优化做扎实。
我个人在实际工作中还有一个体会：索引不是越多越好，过多索引会拖慢写入性能，占用额外磁盘空间，还会增加优化器的选择成本。每个索引的建立，都应该能对应到真实存在的查询需求上。优化完一个查询，记得用覆盖索引和联合索引的思维多问自己一句——这个查询还能不能少回一次表、少一次排序？带着这个问题去调整，你的 MySQL 性能优化能力才能在实践中真正成长起来。
