MySQL索引优化能改善慢查询吗？从执行计划到索引设计全解析

做MySQL优化的这些年，我见过太多人一遇到慢查询就条件反射式地加索引，结果有时候快如闪电，有时候却毫无变化，甚至更慢。标题这个提问“mysql索引优化能改善慢查询吗”，答案其实不是简单的“能”或“不能”，而是“在正确的前提下能，在错误的理解下不能”。这篇文章我会结合自己实际的排查经历，从慢查询日志的分析、索引失效的典型场景、执行计划的解读，到一次真实索引优化的完整过程，把这条链路彻底讲透。无论你是刚入门的新手，还是已经写过大量SQL的开发，这篇文章都能给你一套可以直接落地的排查方法论。

1. 慢查询定位：先搞清楚慢在哪里，再谈怎么优化

1.1 慢查询日志的开启与分析

动手优化之前，第一件事不是看索引，而是先确认慢查询日志到底记录了哪些语句。很多同学在本地开发环境根本不开慢查询日志，到了生产环境出了问题就拍脑袋猜，这是大忌。

开启慢查询日志其实很简单，在MySQL配置文件（通常是my.cnf或my.ini）的[mysqld]段下加入两行：

slow_query_log = 1 slow_query_log_file = /data/mysql/log/slow-query.log long_query_time = 1 log_queries_not_using_indexes = 1

这里的long_query_time = 1表示超过1秒的SQL会被记录下来，建议一开始设置1秒，后面可以根据实际情况调整到0.5秒甚至更低。log_queries_not_using_indexes这个参数非常有价值，它会把那些没走索引的SQL也记录到慢日志中，哪怕执行时间很短——因为在大数据量下，这种SQL迟早会拖垮数据库。

还有一种临时开启方式，不需要重启MySQL，直接执行SQL：

SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 1; SET GLOBAL log_queries_not_using_indexes = 'ON';

不过这种方式在MySQL重启后会丢失，生产环境还是建议写进配置文件。

拿到慢查询日志之后，我习惯先用mysqldumpslow这个内置工具做个初步统计：

mysqldumpslow -s at -t 10 /data/mysql/log/slow-query.log

这个命令会按平均执行时间倒序排列前10条最耗时的SQL。注意看返回值里的Rows examined和Rows sent这两个关键指标。如果Rows examined非常大而Rows sent很小，意味着这条SQL扫描了大量数据却只返回了少量结果，这种场景就是索引优化的重点对象。

注意：慢查询日志文件会持续增大，生产环境一定要配置log_rotate或者定时清理策略，否则磁盘被撑爆的后果比慢查询还严重。

1.2 定位慢SQL的三板斧：执行计划、状态值、Profile

拿到慢SQL之后，不能直接上去建索引，先做三个基础诊断。

第一板斧是EXPLAIN。直接在慢SQL前面加上EXPLAIN关键字，MySQL会返回一张执行计划表格，核心关注这几个字段：

• type：连接类型，从好到差依次是system > const > eq_ref > ref > range > index > ALL。看到ALL基本就是全表扫描，指数优化重点关注的类型。
• key：实际用到的索引名。如果为NULL，说明没走索引。
• rows：预估扫描的行数。这个数字越大，性能越差。
• Extra：常见值Using where、Using index、Using filesort等。出现Using filesort意味着排序没有用上索引，这种场景通常可以通过建立合适的联合索引来消除。

第二板斧是SHOW PROFILE（MySQL 8.0之后用SHOW PROFILE或performance_schema监控）。这个方法用来分析一条SQL内部各阶段的耗时占比：

SET profiling = 1; -- 执行你的慢SQL SHOW PROFILES; SHOW PROFILE FOR QUERY 1;

返回结果会列出executing、Sending data、Sorting result等阶段的耗时。如果Sending data占大头，说明数据读取和传输是瓶颈；如果Sorting result很大，那就要重点查ORDER BY相关的字段是否走索引。

第三板斧是查看SHOW GLOBAL STATUS里的Handler_read_first、Handler_read_key、Handler_read_rnd_next这几个计数器的变化。比如Handler_read_rnd_next值特别高，说明大量的随机读操作，典型特征就是没有走索引而进行的全表扫描。

这三个诊断做完，基本就能确定一条SQL慢的根源是“扫描行数太多”“排序开销太大”还是“回表次数过多”。不同的病因对应不同的索引策略，这也是为什么我不建议跳过诊断直接加索引的原因。

2. 索引生效与失效：为什么你建的索引不生效

2.1 联合索引的最左前缀原则

联合索引是最容易被误解的知识点之一。很多开发同学在(a, b, c)三个字段上建了个联合索引，然后理直气壮地说我已经建索引了为什么查询还是慢。

问题往往出在查询条件没有遵守最左前缀原则。所谓最左前缀，指的是查询条件必须从联合索引的最左列开始，并且不能跳过中间的列。

举个例子，假设在(user_id, status, create_time)三个字段上建了联合索引idx_user_status_time，那么下面这些查询可以利用索引：

WHERE user_id = 1001 WHERE user_id = 1001 AND status = 1 WHERE user_id = 1001 AND status = 1 AND create_time > '2024-01-01'

但下面这些查询无法充分利用这个联合索引：

WHERE status = 1 WHERE create_time > '2024-01-01' WHERE status = 1 AND create_time > '2024-01-01'

一条通俗的类比：联合索引就像一本按“姓氏-名字-手机号”排序的电话簿。你直接按“手机号”去查人，等于把整本电话簿翻一遍，完全用不上排序规则；按“姓氏-名字”去查，就能快速定位到那一小段。

理解了这个原则之后，设计联合索引时就要有意识地考虑字段顺序。区分度高的字段、等值查询的字段放在前面，范围查询的字段放在后面，这能最大程度利用索引树的有序性。

2.2 让索引失效的七个典型场景

我总结了七种最容易踩的索引失效场景，这里全部列出来，建议大家收藏自查：

第一，在索引列上使用函数。比如WHERE DATE(create_time) = '2024-01-01’，哪怕create_time上建了索引也用不上，因为MySQL需要先对每一行的create_time执行函数计算后才能比较。正确写法是改成范围查询：WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02'。

第二，隐式类型转换。当索引列是varchar类型，查询条件却传入了数字时，MySQL会隐式地把字符串转成数字，导致索引失效。比如WHERE phone = 13812345678，而phone字段是varchar，这条SQL就全表扫了。正确写法是WHERE phone = '13812345678'。

第三，前导模糊匹配。WHERE name LIKE '%张' 这种以通配符开头的模糊查询，无法使用基于B+树的索引。但WHERE name LIKE '张%'可以。如果业务确实需要后模糊匹配，考虑用全文索引或搜索引擎方案。

第四，OR连接的条件中有一个字段没有索引。比如WHERE a = 1 OR b = 2，即使a上有索引，如果b上没有索引，整个查询可能退化为全表扫描。这种情况建议改为UNION或者两边都建立索引。

第五，索引列参与运算。WHERE num + 1 = 10，这种对索引列做算术运算的写法会让索引失效，改写为WHERE num = 9。

第六，负向查询。!=、<>、NOT IN、NOT LIKE这类操作符在大多数情况下无法走索引。这跟B+树的索引组织方式有关——负向查询意味着需要扫描几乎所有不匹配的节点，优化器评估后觉得全表扫更划算。

第七，数据分布的影响。当优化器评估后认为索引选择性太差，或者需要扫描超过表中大约20%到30%的数据时，它宁可选择全表扫描也不走索引。比如一个性别字段只有“男”“女”两种值，在上面建了索引也可能被优化器放弃。这种场景下索引基本没有意义，不做也罢。

2.3 回表与覆盖索引的概念

理解了回表才能理解覆盖索引的价值。InnoDB的聚簇索引（主键索引）的叶子节点上存储了整行数据，而二级索引（普通索引）的叶子节点只存储了索引列和主键值。当查询的列不在二级索引中时，MySQL需要通过主键值回到聚簇索引中查找完整行记录，这个额外操作就叫做回表。

回表本身是正常的，但如果查询涉及大量数据，回表次数多就会产生大量的随机I/O，性能自然下降。覆盖索引就是针对这个问题的优化手段——把查询需要的列都包含在索引中，此时二级索引的叶子节点已经包含了全部所需数据，无需回表。

举个例子，如果业务上经常执行这条SQL：

SELECT user_id, status FROM orders WHERE status = 1 AND create_time > '2024-01-01';

那么可以设计联合索引idx_status_time_user(status, create_time, user_id)，让查询的所有字段都包含在这个索引中。这样查询直接在索引树上完成数据读取，Extra列会显示Using index，意味着这是一个覆盖索引扫描，性能会明显提升。

提示：不要无脑把所有查询列都塞进索引。索引也是需要存储空间的，写入时也需要更新，索引字段越多，写入成本越高。设计覆盖索引时要结合真实的业务查询频率来判断，高频且简单就能覆盖的查询才值得这么做。

3. 一次真实的索引优化实践：从2.8秒到12毫秒

3.1 场景需求与表结构

为了把前面讲的原理串起来，我这里分享一个我近期处理的真实案例。某电商业务有一个订单查询页面，运营同学反馈打开非常慢，接口平均响应时间3秒左右。我抓取了对应的SQL，表现如下：

SELECT order_id, user_id, status, total_amount, pay_time, express_company, express_no FROM t_order WHERE status = 2 AND pay_time >= '2024-06-01' AND pay_time <= '2024-06-30' ORDER BY pay_time DESC LIMIT 20;

执行计划显示type为ALL，全表扫描，预估rows约48万行，Extra中还有Using where和Using filesort。

表结构的关键部分这样定义：

CREATE TABLE `t_order` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `order_id` varchar(32) NOT NULL COMMENT '订单号', `user_id` bigint(20) NOT NULL COMMENT '用户ID', `status` tinyint(4) NOT NULL COMMENT '订单状态：1待支付 2已支付 3已发货 4已完成', `total_amount` decimal(10,2) NOT NULL DEFAULT '0.00', `pay_time` datetime DEFAULT NULL, `express_company` varchar(50) DEFAULT NULL, `express_no` varchar(50) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_user_id` (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

可以看到原来的表只有user_id上的索引，完全无法支撑这个按status和pay_time过滤的查询。

3.2 索引设计过程

按照前面讲的诊断方法，我分析出这条SQL的核心痛点是：全表扫描了48万条数据，然后还要在内存中做filesort排序，最后只取20条返回。在这里，索引需要同时解决过滤和排序两个问题。

我设计了联合索引(status, pay_time)，并给出设计依据：

• status是等值查询条件，放在联合索引最左边，匹配最左前缀原则。
• pay_time在查询中是范围条件，同时ORDER BY pay_time DESC复用了索引的排序特性，放在status之后，过滤出一个相对较小的数据集时已经是有序的，Using filesort就会被消除。
• 为什么不需要把order_id、user_id这些查询列也塞进索引？因为查询涉及total_amount、express_company、express_no等大字段，全部塞进索引会导致索引体积膨胀，牺牲写入性能。而且这个查询最终返回数据量只有20条，回表成本完全可以接受。

我直接通过DDL创建索引，生产环境在线执行，注意用ALGORITHM和LOCK选项避免长时间锁表：

ALTER TABLE t_order ADD INDEX idx_status_paytime (status, pay_time), ALGORITHM=INPLACE, LOCK=NONE;

执行完再看执行计划，type为ref，key用的是idx_status_paytime，rows从48万降到约6.3万，Extra只剩Using where，不再有Using filesort。

3.3 优化前后效果对比

为了让大家更直观地理解优化效果，我做了多轮对比测试。同样在6月的已支付订单里按支付时间倒序取20条，执行结果如下：

这里有个细节值得关注：优化前第二次执行仍有接近2秒的耗时，因为全表扫描需要把48万行数据从磁盘加载到内存，单纯依赖Buffer Pool缓存效果有限；优化后第二次执行耗时降到12毫秒，是因为索引树的中间节点被缓存后，每次查询只需要沿着索引定位到那一小段数据再回表取20条即可。

当然，这里我还要主动说明一点：这个索引对这个查询是有效的，但并不意味着在所有订单状态上都能起同样效果。比如status=4（已完成）的订单如果占据了表中绝大多数数据，优化器可能会判断全表扫描更优，走索引反而慢。索引优化永远要结合数据分布来评估，这也是为什么同样一条SQL在不同业务库中表现会差很多的原因。

3.4 配套的SQL改写建议

索引建好之后，我还顺手改写了这条SQL的一个小问题：把pay_time的BETWEEN写法改成半开区间写法，规避边界值问题。原来的写法：

AND pay_time >= '2024-06-01' AND pay_time <= '2024-06-30'

改成：

AND pay_time >= '2024-06-01' AND pay_time < '2024-07-01'

这样做的好处是，当pay_time字段包含时分秒时，BETWEEN或<=写法会漏掉6月30日当天23:59:59之后的数据，而< '2024-07-01'天然包含整个6月30日最后一秒之前的所有时间点，逻辑上更严谨。这个属于SQL书写习惯层面的优化，能让索引效果发挥得更稳定。

4. 索引之外的性能提升组合拳

4.1 SQL改写技巧

索引不是万能的。有时候就算有合适的索引，SQL写法不讲究，性能一样上不去。这里分享几个我在实际工作中高频用到的SQL改写技巧。

第一，避免使用SELECT *。尽量只查询业务需要的字段，一方面减少网络传输数据量，另一方面有机会命中覆盖索引。如果某个表有50个字段，而你的列表页只需要其中5个，SELECT *会让回表概率大增。改写成明确的字段列表后，联合索引可能直接覆盖所有查询列，Extra中出现Using index，性能完全不是一个量级。

第二，分页深了要改思路。LIMIT 100000, 20这种深分页SQL会让MySQL扫描前100020条然后丢弃前10万条，代价非常高。常见的优化方向有两个：一是记录上一页最后一条数据的排序字段值，用WHERE条件定位下一页，比如WHERE id < 100000 ORDER BY id DESC LIMIT 20；二是利用子查询或JOIN先在索引上定位起始位置再取数据，比如先查这20条数据的主键ID，再用主键去关联原表取完整数据。

第三，慎用DISTINCT。DISTINCT本质是去重，如果是在大表上查询去重后的结果集，开销往往比想象中大得多。先确认业务是否真的需要去重，有些场景改用EXISTS或者GROUP BY配合索引效果更好，但也要分情况测试，没有银弹。

4.2 查询条件与索引设计的顺序

我遇到过很多“SQL看着没问题，但就是慢”的情况，最后排查下来，原因是查询条件的先后顺序和联合索引的字段顺序不一致，导致优化器没能用到最优索引。这里分享一条设计原则。

当一条SQL有多个等值条件和范围条件混合时，等值条件对应字段放在联合索引靠前的位置，范围条件对应字段放在后面。为什么？因为等值条件可以精确定位到某一段连续的索引区间，范围条件只能缩小扫描范围但无法让每一层索引都精确定位。如果把范围条件放在前面，联合索引在范围之后的字段排序意义就大打折扣了。

举个例子，索引设计为(user_id, status, pay_time)，那么查询WHERE status = 2 AND pay_time >= '2024-06-01' AND user_id = 1001这种写法，虽然逻辑上结果一样，但优化器在选择索引时可能不会把这个查询匹配到最优的索引路径上。最好把查询条件也写成user_id = 1001 AND status = 2 AND pay_time >= '2024-06-01'，保证等值条件在前、范围条件在后，让优化器能够直接命中联合索引的最优前缀。

4.3 硬件与配置层面的辅助手段

索引优化是性能提升的核心手段，但绝不是唯一手段。当一条SQL已经充分使用索引、行数扫描极少时，如果仍然慢，就要从更底层的层面找原因了。

最常见的是Buffer Pool配置。InnoDB的Buffer Pool用于缓存数据页和索引页。如果设置过小，索引页被频繁淘汰，每次查询都要产生磁盘I/O，性能自然上不去。一个经验值是Buffer Pool设为机器物理内存的60%到80%，但具体要看机器是专用数据库还是混部部署。

还有一个很容易被忽略的参数是innodb_buffer_pool_instances。在MySQL 5.7及之后版本，如果Buffer Pool总大小超过1GB，建议调整为多个实例（通常设为8），减少并发场景下的锁竞争。8.0版本还有一个innodb_buffer_pool_chunk_size参数可以调整内存分配粒度，但这些偏底层的参数调整需要长时间观察和压测，不建议一上来就动。

另外，查询缓存（Query Cache）这个特性在MySQL 8.0已经被移除了，如果你的版本还开着它，建议关闭，因为它在多写场景下维护缓存本身的锁竞争开销会拖慢整体性能。

5. 常见问题与排查技巧实录

5.1 慢查询排查问题速查表

我整理了一份平时排查慢查询时的高频问题对照表，直接对着排查，效率会高很多。

这张表是我踩了无数坑之后总结出来的。碰到一张新的慢查询，我基本按这个框架去推导，大多数问题在十分钟内就能定位到根因。

5.2 一个容易忽略的坑：隐式字符集不一致

最后分享一个比较隐蔽的坑。两个表关联查询，其中一个表的字段是utf8mb4字符集，另一个表是utf8字符集，关联字段如果用户名字符串，MySQL在做表连接时需要在内存中做字符集转换，这个转换过程可能导致关联字段上的索引失效。

有一次我在排一个多表JOIN慢查询时，单表EXPLAIN都正常，但整个JOIN的执行计划里驱动表直接全表扫了。排查半天，最后发现是关联字段字符集不一致。解决办法也很简单，把两张表的关联字段统一成utf8mb4，然后重新建索引，问题就消失了。

这类字符集问题在生产环境中并不少见，尤其是老系统升级或不同模块交接的场景。如果排查索引没问题但查询仍然慢，记得检查一下表结构里的CHARSET和COLLATE配置是否一致。

再补充一个容易被忽略的细节：使用前缀索引时要注意区分度。比如对varchar(200)的URL字段建前缀索引，如果只取前10个字符，区分度可能非常低，优化器很可能放弃索引。我习惯用一条件SQL来测试不同前缀长度的区分度：

SELECT COUNT(DISTINCT LEFT(url, 10)) AS prefix10, COUNT(DISTINCT LEFT(url, 20)) AS prefix20, COUNT(DISTINCT url) AS full_count FROM t_url;

通过对比prefix10和full_count的比值，选择区分度能够达到80%以上同时又尽量短的长度作为前缀索引长度，这样既能减小索引体积又不至于丧失选择性。

5.3 索引过多也是灾难

还有一个必须提醒的误区：索引不是越多越好。每多一个索引，INSERT、UPDATE、DELETE时的索引维护成本都会增加。如果一张表有10个索引，写入一条记录就要维护10棵索引树，在高并发写入场景下性能下降非常明显。

我给一个建议：表上索引总数尽量控制在5个以内，超过这个数就要审视是否有冗余索引。举个常见的冗余场景，你已经有了(a, b)联合索引，又单独建了a字段的单列索引，这时a单列索引就是完全冗余的，因为(a, b)联合索引天然支持只按a查询的场景。可以用下面的SQL查询冗余索引，通过比较索引的字段前缀来识别：

SELECT s1.TABLE_NAME, s1.INDEX_NAME, GROUP_CONCAT(s1.COLUMN_NAME ORDER BY s1.SEQ_IN_INDEX) AS index_columns FROM information_schema.STATISTICS s1 GROUP BY s1.TABLE_NAME, s1.INDEX_NAME ORDER BY s1.TABLE_NAME, index_columns;

拿到这张索引清单后，人工对比前缀一样的索引组合，把重复的删掉即可。上线前先在测试环境观察删除索引后的执行计划变化，确保没有查询因为删了冗余索引而走全表扫。

我在实际项目中见过一张表最多堆了16个索引的情况，删掉6个冗余索引后，写入性能提升了将近30%，而查询性能完全不受影响。性能优化有时候是减法，不是加法。

做MySQL性能优化这几年，我最深的一个体会是：索引优化的核心不是“会不会写CREATE INDEX”，而是“能不能读懂一条SQL的执行计划”。你建的每个索引都应该有明确的依据，要么减少了扫描行数，要么消除了文件排序，要么实现了覆盖索引。如果连执行计划都懒得看，加了索引也只是碰运气。希望这篇文章能把慢查询排查的完整路径讲清楚——从慢日志定位，到执行计划分析，再到索引设计落地，最后用实际数据验证效果。下次再碰到慢查询，别急着加索引，先按这套流程走一遍，你会发现很多问题的答案自己就浮出水面了。