MySQL索引与性能优化：从慢查询到EXPLAIN的实战指南

你是不是也有过这样的经历：明明表里就几百万行数据，一条带WHERE条件的查询却跑了十几秒，把接口拖到超时，DBA半夜打电话喊你起来看慢查询。我见过太多刚入门的朋友一听到"MySQL索引与性能优化基础"就觉得是DBA才需要学的东西，结果自己负责的第一个线上模块就被一个全表扫描教会了做人。这篇博文我就用实际排查过的案例，把索引的底层逻辑、失效场景、EXPLAIN解读和优化链路一次讲透，适合正在学习MySQL基础教程的开发者，也适合那些已经被慢查询折磨过但没系统梳理过索引知识的人。

索引这事，说难不难，说简单也绝对不简单。难的不是建索引，而是理解为什么这样建、什么场景下它不干活、以及怎么用工具去验证自己的判断。这篇文章不会给你背一大堆命令，而是把"索引为什么能加速、什么时候失效、怎么排查、怎么设计"这四件事串起来，看完你至少能独立处理工作中八成以上的慢查询问题。

1. 从一次慢到离谱的查询说起：索引到底在加速什么

三年前有个后台管理系统的订单查询接口，数据量大约五百万行，前端一按查询按钮，接口平均响应时间稳定在8秒左右。我当时接手的第一反应不是急着看SQL，而是先确认了一件事：这个表到底建没建索引，建的是什么索引。结果发现订单表上除了主键索引之外，一个二级索引都没有，所有条件查询全在走全表扫描。这就是最典型的"能用但很慢"的案例。

1.1 没有索引时，MySQL在做什么

先聊一个最基础的问题：没有索引的时候，一条带WHERE条件的查询是怎么执行的。你可以把存储引擎想象成一个大仓库，数据行按插入顺序堆在里面。你要找"status=1且create_time在最近三天的订单"，MySQL没有任何捷径，只能从第一行数据开始，一行一行往下扫，把每一行都读出来，逐字段比对条件，命中的塞进结果集。这个过程叫全表扫描，时间复杂度是O(n)。

表只有几百行的时候，全表扫描无所谓，秒回。但当数据量到几十万、几百万行，每一行都涉及磁盘IO读取，这个开销就完全失控了。IO是数据库最宝贵的资源，机械硬盘随机读一次大概要10毫秒，即便换成NVMe固态，大量小尺寸随机读累积起来也扛不住。所以我一直跟新人强调：索引优化的本质，是减少存储引擎需要扫描的数据量，把O(n)的查找变成O(log n)甚至更少。

1.2 索引就是一本带目录的字典

很多人问索引到底是个啥，我习惯用字典来打比方。一本十万字的书，你要找一个特定关键词，没目录就只能从头翻到尾，运气好翻到第一页，运气不好翻到最后一页。有了目录之后，你先查目录定位到章节页，再跳到对应页码，几秒钟就搞定。MySQL的索引干的就是这件事：它把某几列的值单独拿出来，按照一定的数据结构组织好，并记录每个值对应的数据行位置。查询的时候先走这个"目录"定位，再根据指针去拿完整的数据行。

但这个目录不是白给的。它要占额外的磁盘空间，而且每次插入、更新、删除数据时，索引结构也得跟着维护，写入性能会打折扣。这就是为什么索引不能乱建——建多了写库变慢，建错了查询还是慢。后面第6节我会详细讲索引设计的取舍。

索引有好几种存储结构，MyISAM和InnoDB的差异也不小，但现代MySQL默认都是InnoDB引擎，所以我们聊优化都以InnoDB为前提。

2. 索引在存储引擎里的真实形态：B+树、聚簇索引与回表

光知道索引是"目录"还不够，你得稍微往底层看一眼。很多索引失效问题，根源就在于不理解InnoDB到底怎么组织索引的。这一节我尽量不堆术语，用大白话把B+树、聚簇索引、回表、覆盖索引这四件事讲明白。

2.1 为什么偏偏是B+树

索引最核心的数据结构是B+树。为什么不是二叉搜索树，不是哈希表，也不是红黑树？答案藏在数据库的真实使用场景里。

• 二叉搜索树在极端情况下会退化成链表，高度不可控
• 红黑树虽然是平衡的，但树的高度依然随数据量上升，而每次比较都伴随着磁盘IO
• 哈希表等值查找极快，但完全无法处理范围查询和排序

B+树之所以胜出，核心在于它把树高控制得非常低，而且叶子节点之间通过链表串了起来。InnoDB默认一页是16KB，一个节点能放很多个键值，通常一棵三四层的B+树就能存储几千万行数据。这意味着查找一条记录，磁盘IO次数基本就是树的层数，稳定在三四次左右。而叶子节点用链表串联，又让范围查询变得特别顺手——找到下限之后沿着链表往后扫就行。

我见过不少初学者纠结B+树和B树的区别，其实在日常优化中你不用做太深的对比，只要记住B+树的杀手锏是"矮胖"加"叶子有序链表"这两点就够用了。

2.2 聚簇索引与二级索引：每一根索引背后都藏着回表

InnoDB的表本身就是一个聚簇索引结构。表里的数据行，物理存储顺序就是按照主键排列的，这叫聚簇索引。你在建表时定义了主键，那么这个主键就是聚簇索引的键；如果没定义主键，InnoDB会找第一个非空唯一键；再没有，就生成一个隐藏的行ID。聚簇索引的叶子节点直接存了整行数据，所以通过主键查数据，一次索引查找就能拿到全部字段。

但我们建的普通索引，包括联合索引，在InnoDB里都叫二级索引。二级索引的叶子节点存的是什么？是索引列的值，加上一个主键值。这个设计非常巧妙，它让二级索引不依赖数据行的物理地址，即使数据页分裂移动了，索引也不需要跟着改。但代价是：如果你通过二级索引查到了主键，还想拿其他字段，就得拿着主键再去聚簇索引里查一次，这个动作就叫回表。

回表不是免费的。每回表一次，就是一次主键B+树的随机IO。假设二级索引命中了1000行，那最多可能要回表1000次。这时候性能瓶颈就从"索引查找"变成了"随机IO次数"。理解了回表，你才能真正理解"覆盖索引"为什么是性能利器——如果查询的字段都已经被包含在二级索引的叶子节点里，那查询压根不需要回表，直接在二级索引上就拿到了全部数据，速度自然快得多。

2.3 一张表只能有一个聚簇索引，但可以有多个二级索引

有次我让一个新人解释"为什么通过主键查询最快"，他回答说因为主键有索引。这个回答不能算错，但没说到点子上。准确说法是：主键对应的聚簇索引上，叶子节点已经带着整行数据，所以查找过程没有回表这一环。而二级索引无论怎么优化，要是查的字段超出索引范围，几乎免不了回表。

这个认知上的差异，直接影响你怎么设计索引。我见过很多开发者在经常查询的字段上建了一堆单列索引，却完全没考虑通过联合索引去覆盖查询字段。后面第5节讲EXPLAIN时你就能看到，一个Extra列出现"Using index"的查询有多爽，那代表MySQL连数据文件都没碰，纯靠索引就给出了答案。

3. 联合索引的最左前缀原则：什么时候能用、什么时候必然失效

联合索引是性能优化里最常用也最容易被误解的工具。一句话版本：联合索引 (a, b, c) 可以理解为先按a排序，a相同再按b排序，b相同再按c排序。它本质是一个排序后的列表，所以你在查询时只有从最左边的列开始连续命中，索引才能生效。这就是最左前缀原则。

3.1 哪些组合能走索引

假设有一张模拟订单表，我们建立联合索引 (user_id, status, create_time)。下面这些查询都能用上这个索引：

• WHERE user_id = 100
• WHERE user_id = 100 AND status = 1
• WHERE user_id = 100 AND status = 1 AND create_time > '2024-01-01'
• WHERE user_id = 100 ORDER BY status

这些查询的共性是：条件里包含了联合索引最左边的user_id，并且后续条件列是连续往右走的。MySQL可以用B+树的排序特性，先定位user_id=100的范围，再在这个范围内定位status=1的数据，再进一步筛选create_time。

3.2 哪些组合必然失效

反过来，下面这些情况就很尴尬：

• WHERE status = 1：没有user_id打头，索引根本没法用
• WHERE user_id = 100 AND create_time > '2024-01-01'：user_id能定位，但跨过了中间的status直接查create_time，create_time条件用不上索引
• WHERE create_time > '2024-01-01' AND user_id = 100：写反了顺序也没用，优化器不是按你写的顺序执行的，但索引匹配依然要求从最左列开始连续匹配

这里有个很多人容易忽略的点：所谓"最左前缀"，跟你在SQL里写条件的先后顺序没有关系。MySQL的优化器会做条件重排，但能不能走索引，取决于条件列是不是索引列的前缀序列，而不是SQL里谁在前谁在后。

3.3 范围条件之后，索引就罢工了

比最左前缀更容易忽略的坑，是范围查询对联合索引的"截断"。比如 (a, b) 联合索引，你执行 WHERE a = 1 AND b > 10 AND b < 20，这个b上的范围条件依然能用索引定位。但如果是 WHERE a > 1 AND b = 5，那么a用上了索引做范围扫描，b条件就只能在a筛选后的结果里再过滤，索引对b这一列就"罢工"了。

为什么？因为联合索引是先按a排序再按b排序的，一旦a是范围（不精确等于），那么在这个范围内，b的排序信息是不具备全局有序性的。MySQL只能一页一页地把a>1的数据读出来，再逐行判断b=5。这个现象叫"范围之后的列无法使用索引"。

所以设计联合索引的时候，一个非常实用的经验是：把等值条件放在前面，范围条件放在后面。这个原则不光影响SQL能不能走索引，还直接影响索引结构是不是合理。我在实际设计索引时，总是先问自己：这个查询里哪些条件是等值、哪些是范围、哪些只是排序需求，然后按等值列、范围列、排序列的顺序去组织联合索引的列。

4. 实战里的索引失效高发区：函数、隐式转换、模糊查询与OR

即使你建的索引完全符合最左前缀原则，现实中的SQL还是可能悄无声息地绕过索引。这一节列出的四个场景，几乎每个项目里都能抓到几个反例。我挑的都是线上真实遇到过的写法，看完建议你把公司代码仓库里的SQL拉出来自查一遍。

4.1 在索引列上做函数操作：索引直接报废

有个模拟会员系统的查询让我印象很深：

SELECT * FROM member WHERE DATE(create_time) = '2024-06-01';

开发者觉得建了create_time索引就该快，结果执行计划一出来，type=ALL，全表扫描。原因很简单：当你对索引列做了函数处理后，索引里存储的原始值在函数变换后的结果面前完全失去了顺序性，B+树没法通过比较原始值来定位目标，索引自然失效。

正确写法应该是：

SELECT * FROM member WHERE create_time >= '2024-06-01 00:00:00' AND create_time < '2024-06-02 00:00:00';

改成范围条件之后，create_time索引就能正常工作了。这个调整对范围查询的影响很小，效果却是天壤之别。类似的坑还包括在索引列上做算术运算，比如 WHERE age + 1 = 20，同样会让索引失效。尽量把运算移到等号右边。

4.2 隐式类型转换：字段是字符串，传进去数字

MySQL里有一类特别隐蔽的失效，就是隐式类型转换。表里的phone字段是VARCHAR类型，结果SQL写成：

SELECT * FROM user WHERE phone = 13800138000;

用户传的数字会被MySQL转成字符串再去跟索引比较吗？恰恰相反，在这个场景里，MySQL会尝试把列的值转换成数字来比较，于是索引列上相当于发生了一次隐式CAST操作，索引直接失效。解决方式很简单，传参加引号，或者干脆在应用层保证类型一致：

SELECT * FROM user WHERE phone = '13800138000';

顺带说一句，这种问题在ORM框架里更容易发生。有些框架的查询构造器不会帮你判断字段类型，你传什么它就拼什么，排查起来更头疼。所以该类型就写对类型，比事后优化省心得多。

4.3 前置模糊查询：%号放在了最前面

后缀模糊（xxx%）是可以走索引的，比如 LIKE 'abc%'，因为B+树可以按前缀范围定位。但前置模糊（%abc%）完全没法利用索引的排序特性，因为你要找的内容不固定出现在字符串开头。

-- 能走索引 SELECT * FROM article WHERE title LIKE 'MySQL%'; -- 索引失效 SELECT * FROM article WHERE title LIKE '%索引%';

线上业务如果有大量前置模糊搜索的需求，老实说模糊查询本身就不适合关系型数据库硬抗，更好的选择是引入专业的全文检索引擎。如果数据量不大、还是想用MySQL，可以考虑全文索引，但要注意全文索引的语法、中文分词和性能表现都跟普通索引不太一样，不是一个简单的替代方案。

4.4 OR条件：只要有一个字段没有索引，整体就可能全表扫

这是做得最让人无语的优化陷阱。比如：

SELECT * FROM order_info WHERE user_id = 100 OR order_no = 'A12345';

如果只有user_id上有索引，order_no上没有索引，你以为MySQL会先分别查两个条件再合并吗？在绝大多数版本和配置下，优化器会选择直接全表扫描，因为OR意味着任意一个条件成立就得返回，而另一个条件没有索引可用，那就干脆把所有行都扫一遍再过滤。

解决思路通常是改成UNION：

SELECT * FROM order_info WHERE user_id = 100 UNION SELECT * FROM order_info WHERE order_no = 'A12345';

这样两边都能各自走索引。或者更干脆一点，把order_no也加上索引，让优化器有更多的执行路径可选。这里有个原则：OR两侧的条件，要么都建索引，要么做好全表扫的心理预期。

5. 用EXPLAIN给SQL做体检：关键字段与执行计划的读法

前面讲的都是面粉，EXPLAIN是检查面粉状态的那台机器。我的习惯是：任何一条线上跑了超过200毫秒的查询，第一件事就是把它复制出来，在前面加上EXPLAIN，看执行计划。这比你拍脑袋猜"是不是该加索引"要靠谱得多。

5.1 EXPLAIN基础用法与输出列概览

EXPLAIN SELECT * FROM order_info WHERE user_id = 100 AND create_time > '2024-06-01'\G

执行之后你会看到一张表，每一行代表一个查询步骤。最常见的几个列，我按重要程度排一下：

5.2 type字段从好到坏的顺序

type的值从优到劣大致是这个顺序：

system > const > eq_ref > ref > range > index > ALL

• const：用主键或唯一索引等值查询，最多返回一行，这是理论最快
• eq_ref：被驱动表通过主键或唯一索引等值匹配，多表JOIN时的理想状态
• ref：普通二级索引等值匹配，不要求唯一性
• range：索引范围扫描，比如BETWEEN、>、<、IN等
• index：遍历整个索引树，比全表稍好一点，因为读的是紧凑的索引页
• ALL：全表扫描，性能最差，是优化要消灭的对象

你不需要背这个列表，只需要记住一条：看到range及以上的访问方式，说明索引基本上用上了；看到index和ALL，就必须多想一下为什么。我自己写SQL的底线是单表查询必须达到range级别，如果只能做到ALL，回去改SQL或者补索引。

5.3 Extra里那些值得警惕的信号

Extra列是执行计划里信息量最大的地方，它经常直接告诉你问题出在哪。

• Using index：俗称覆盖索引，查询的所有字段都从索引里取到了，不用回表。这是我最喜欢看到的Extra
• Using where：先读数据再过滤，通常表示索引只帮了部分忙
• Using filesort：文件排序，意味着排序操作没有用到索引的排序特性，要额外排序。看到这个就要考虑把ORDER BY字段纳入联合索引
• Using temporary：用了临时表，常见于GROUP BY、DISTINCT这类操作，往往伴随性能问题

举个例子，模拟订单表上有联合索引(user_id, status, create_time)，你执行：

SELECT user_id, status, create_time FROM order_info WHERE user_id = 100 ORDER BY create_time DESC;

如果Extra只显示Using index，说明查询不仅走了覆盖索引，排序也直接利用了索引的有序性，连排序操作都省了。这就是一条教科书级的完美SQL。同样一条SQL，如果你SELECT多加一个不包含在索引里的字段，Extra就变成"Using index condition; Using filesort"，回表加上排序，性能立马掉档。

6. 一个模拟慢查询案例的完整优化链路

光讲理论容易飘，我完整复盘一个代表性的优化过程。案例背景：一张模拟日志表operation_log，大约两千万行，核心字段有id、user_id、op_type、create_time、remark。线上有个报表接口要查某个用户最近一周的操作记录，SQL长这样：

SELECT id, user_id, op_type, create_time, remark FROM operation_log WHERE user_id = 73821 AND create_time > '2024-05-01' ORDER BY create_time DESC;

这个查询在线上稳定跑2秒左右。我先EXPLAIN看了一眼，type是ALL，rows预估扫描了快两千万行，Extra里还有Using filesort。整条SQL的问题一目了然：全表扫描加手动排序，慢得毫无悬念。

6.1 第一步：确认业务需求，再看建什么索引

优化之前先跟业务对齐：报表查询的过滤条件是不是永远是user_id加时间范围？排序是不是永远按create_time倒序？如果是，那这个查询的最优索引就是联合索引 (user_id, create_time)。等值条件user_id放前面，范围条件create_time放后面，排序字段create_time又恰好是索引的一部分，排序也能顺带解决。

执行建索引：

ALTER TABLE operation_log ADD INDEX idx_user_create (user_id, create_time);

这个索引建完之后，我再跑EXPLAIN，type变成了ref，rows降到用户的操作记录数级别，Extra里不再出现Using filesort，查询直接降到几十毫秒。从2秒到几十毫秒，本质上就是把扫描行数从两千万减到几百行。

6.2 第二步：检查SELECT的字段，能不能做覆盖索引优化

上一版虽然已经够快，但还能再压一点。因为我查询里还带着remark字段，而remark是文本字段，不在索引里，所以每命中一行都要回表取remark。如果报表接口其实不需要remark内容，只是习惯性SELECT *，那就改成只查必要的字段：

SELECT id, user_id, op_type, create_time FROM operation_log WHERE user_id = 73821 AND create_time > '2024-05-01' ORDER BY create_time DESC;

这样查询字段完全落在联合索引 (user_id, create_time) 里，Extra变成Using index，连回表都省了。注意我为了说明问题把id也算进去了，因为二级索引叶子节点本身就带着主键值，id不需要额外回表。这就是覆盖索引的实战意义。

6.3 第三步：验证索引对写入的影响

每次加索引之前都得问：这个表写多读少还是读多写少？operation_log的写入频率很高，加索引意味着每次INSERT都要多维护一棵B+树。好在 (user_id, create_time) 这两个列的联合索引数据量不大，且写入是追加型的，维护成本相对可控。我在测试环境压过写入性能，同样批量插入场景，加索引后写入耗时上升在可接受范围内，这才敢上生产。

这个案例里还有个细节：旧业务可能还有按op_type单独筛选的报表，那就要评估是复用联合索引还是再单独建一个(op_type, create_time)索引。索引要么不做，做了就要围绕真实查询模式来设计，不能为了"有索引"而建索引。

7. 索引设计的取舍与线上教训：冗余索引、区分度与维护成本

最后这部分聊的是更高一层的问题：索引怎么设计才不浪费、不冗余。我接手过不少因为索引设计随意导致的问题，有冗余索引撑爆磁盘的，有索引区分度太低等于没建的，还有联合索引列顺序排错导致慢查询反复出现的。这些都值得单独拎出来说。

7.1 谁的区分度高，谁就该站在联合索引的前面

联合索引的列顺序，除了遵循等值优先、范围靠后的原则，还要考虑区分度。区分度可以粗略理解为一列里不同值的比例，比如性别列只有男、女两个值，区分度极低；user_id如果一亿用户基本不重复，区分度就很高。

为什么区分度高的列要放前面？回到B+树查找过程：低区分度的列放前面，会导致索引树的第一个层级就聚集了大量相同值，需要在这个大范围里继续往下查，扫描的中间节点更多。高区分度列放前面，能更快地把扫描范围缩小。

有人做过很直观的对比：一个 (sex, user_id) 索引，查某个性别下的指定用户，可能要扫描一半的索引页；换成 (user_id, sex)，哪怕后面带一个低区分度的sex，查询定位也能一步到位。所以写联合索引的时候，把区分度高的列往左放，收益明显。

7.2 冗余索引：看着很全，其实每个都在拖慢写库

冗余索引是线上数据库最常见的浪费。比如已经有了索引 (a, b)，又单独建一个 (a)，这个单列索引基本是废的，因为任何能用 (a) 的查询，都能用 (a, b) 的最左前缀替代。还有一个典型场景：给order_no建了唯一索引，又给order_no加一个普通索引，后者完全多余。

我查过一次某业务库的索引清单，发现一张表上挂了十几个索引，其中至少四五个是冗余的。每个索引都意味着额外的磁盘占用和写入维护成本，这相当于给每次写操作都背上了一堆行李。清理冗余索引的标准做法是：先用performance_schema或者慢查询日志统计真实查询模式，确认没有SQL依赖某个索引之后，再把它drop掉，并且观察一段时间。

7.3 索引列上避免过长的字段：前缀索引与它的代价

如果要对一个很长的VARCHAR列建索引，比如文章内容或长备注，整列索引会让索引页装不下几个键值，树变高，查询变慢。这时候可以选择前缀索引，只对字段的前N个字符建索引：

ALTER TABLE article ADD INDEX idx_title_prefix (title(20));

但要记住，前缀索引在排序和覆盖索引上会打折扣，因为它存的不是完整值。如果你发现某个查询因为加了前缀索引，Extra里本来能用的Using index变成了回表，那就要权衡前缀长度。我一般建议：能用整列就别用前缀，非用不可时选一个能在业务上区分绝大部分数据的前缀长度，比如20到30个字符。

7.4 线上教训：一次因为低区分度索引引起的慢查询

说一个我印象很深的案例：模拟支付流水表上建了一个 (status, create_time) 联合索引。status字段只有0、1、2、3四个值，区分度非常低。业务上的报表经常查status=1且create_time在某一天的数据。执行计划显示虽然走了索引，但扫描行数几乎等于全表四分之一，因为status=1的数据量太大了，还要在这个大集合里继续过滤create_time。

这种索引有效果吗？有。但效果非常有限。后来我把索引调整成 (create_time, status)，同一个查询的扫描行数直接减少了两个数量级。原因就是create_time区分度高，能先把范围缩到一天，再在这一天里筛status。所以你看，索引列的顺序不是"建了就行"，顺序选错，索引的效率可能连预期的十分之一都达不到。

7.5 维护索引不是一次性工作

索引设计不是上线时建完就完事了。数据量在涨，业务查询模式在变，索引也要跟着演进。我个人的习惯是每季度做一次索引体检：导出所有索引清单，配合慢查询日志和EXPLAIN结果，找出那些长时间没被用到的索引、因为数据分布变化而失效的索引，以及新业务需求下缺失的索引，综合评估后统一调整。这个过程不用太频繁，但一定要坚持做。

有一点特别提醒：在大表上做ALTER TABLE加索引或删索引，会锁表或者至少带来很大的IO压力，务必选择业务低峰期操作，并且先在测试环境验证。我见过有人直接在千万级大表上跑ALTER TABLE ADD INDEX，结果把主库的CPU打到满载，整个服务的写请求全部卡住。数据库优化的前提是先保证稳定，再谈性能。

回到开头说的那个两千万行的日志表，优化完之后我还特意对比了一组数据，同一查询在优化前后的执行时间差异接近四十倍。这不是什么玄学，就是扫描行数从两千万降到了几百。MySQL索引与性能优化基础这件事，本质上就是让你学会用最小的代价，把扫描的数据量降到最低，同时不为这个目标付出过高的写入成本。

如果你现在正准备系统地整理自己的索引知识，我的建议是从这三点入手：第一，开口之前先EXPLAIN，让数据说话；第二，建联合索引先想查询模式，等值在前范围在后，区分度高的靠左；第三，每次加索引都问一句值不值——查询快了多少，写入慢了多少，磁盘多了多少。把这几个问题想清楚，你基本就不会在索引上栽大跟头了。