接手线上MySQL慢查询优化这类活儿看着是加几个索引的事实际上是一整套“读表逻辑”的博弈。索引优化策略不只是“在WHERE条件字段上建索引”这么简单它背后涉及索引结构、查询执行计划、数据分布、写入成本之间的复杂权衡。这篇就把我在实际项目中总结的索引优化完整思路盘一遍从准备分析到设计落地再到常见踩坑全是我实测验证过的东西。1. 动手优化之前先把这几件事查清楚1.1 慢查询日志和当前索引状态拿到一个慢查询优化的需求第一步不是看SQL而是先把“现状”捞出来。我通常先看三样东西慢查询日志、表结构和现有索引、执行计划。慢查询日志能告诉我们哪些SQL是真正有问题的、执行频率多高、扫描了多少行。开启方式很简单但不建议直接在线上改全局参数可以在会话级别临时开-- 查看当前慢查询日志状态 SHOW VARIABLES LIKE slow_query_log%; SHOW VARIABLES LIKE long_query_time; -- 如果没开启会话级别临时开启生产环境谨慎建议先确认 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL log_queries_not_using_indexes ON;第二步是看表结构和现有索引。SHOW CREATE TABLE users\G SHOW INDEX FROM users;不要小看这两条它能直接告诉你哪些索引已经存在、哪些其实已经冗余或失效。有的表里明明有联合索引但查询写出来连最左前缀都没满足等于索引完全没走。第三步是看执行计划也就是EXPLAIN。这一步是整个优化的入口。1.2 EXPLAIN这张“体检报告”怎么看EXPLAIN输出里的关键字段就这么几个type、key、rows、Extra。我一般先看type它直接暗示了访问级别ALL是灾难index是折中range是基本合格ref和eq_ref是比较理想const是极致。rows是预估扫描行数这个数字不是真实行数但能反映优化器的大致判断。Extra里的几个关键词要特别注意比如Using filesort文件排序、Using temporary临时表、Using index覆盖索引、Using where回表后过滤。打个比方MySQL执行一条查询就相当于在一家没有货架的仓库里找一件商品。没有索引时只能挨个箱子翻全表扫描有索引就好比按货架分区存放能直接按分区找而覆盖索引就是“贴着货架标签直接看到库存数量”连开箱都不用。1.3 优化前先确认数据分布和基数这一点经常被忽略但它恰恰会影响索引设计的成败。对一个字段建索引前我会先看它的区分度SELECT COUNT(*) AS total, COUNT(DISTINCT col) AS distinct_col FROM table;如果区分度太低比如性别字段只有两个值那么这个字段上的单列索引基本没有意义。MySQL优化器一算发现走索引还不如全表扫直接就放弃索引。另外要确认字段的数据类型、字符集、排序规则。我有一次优化接手的库两张关联表的字段字符集不一致一个utf8mb4一个latin1导致关联查询索引失效这种问题从SHOW CREATE TABLE就能看出来。2. 索引设计重点不是“加索引”而是“改查询结构”2.1 联合索引不是简单拼凑要按查询模式设计联合索引设计的核心是最左前缀原则。既然联合索引的B树先按第一列排序、再按第二列排序那么索引列的顺序就必须贴合实际查询使用模式。设计联合索引时我通常按一个顺序考虑列等值条件列放前面范围查询列放后面排序列优先考虑放进索引避免文件排序覆盖查询用到的列再放到后面举个例子一个订单表有user_id、status、create_time常见查询是SELECT * FROM orders WHERE user_id 123 AND status 1 ORDER BY create_time DESC;如果建三个单列索引(user_id)、(status)、(create_time)MySQL只能选其中一个用得最好的其他索引就是浪费。更好的方案是建联合索引(user_id, status, create_time)理由如下user_id和status都是等值条件先定位到一个很小的集合create_time按索引顺序天然有序直接省掉filesort尤其对ORDER BY create_time DESC LIMIT 10这类分页查询效果极其明显如果SELECT只取需要的列甚至可以顺势做成覆盖索引这个索引对查询的优化实测下来扫描行数可以从几十万降到几十行排序耗时直接清零。2.2 覆盖索引最容易被低估的优化手段覆盖索引简单说就是查询需要读取的所有字段都能从索引树本身获取绕过回表。回表可不像看起来那么轻巧每回一次表就是一次随机I/O数据量大时性能差距会成倍显现。举个常见例子统计某用户的订单数量SELECT COUNT(*) FROM orders WHERE user_id 123;如果只有(user_id)普通索引InnoDB的辅助索引叶子节点虽然存储了主键值但COUNT(*)其实不需要回表所以这里天然有优势。再看另一种SELECT id, user_id, status FROM orders WHERE status 1 ORDER BY create_time DESC LIMIT 50;如果只有联合索引(user_id, status, create_time)这里会回表拿其他字段比如没在索引里的列。改成(status, create_time, id, user_id)这种覆盖查询组合就能完全避免回表。这里列顺序还要根据最左前缀来定如果status单独做等值条件它必须在最左边。覆盖索引不适合所有场景特别是SELECT *几乎不可能把全部列都塞进索引。但在统计类、列表分页类、汇总类查询里收益非常可观。我遇到很多慢查询其实不是索引没建而是建了索引后SELECT的列太宽导致回表开销过大。2.3 前缀索引和函数索引各有用武之地长文本字段的索引是个麻烦事。比如一个表里存URL或长描述直接对整个字段加索引B树的每个节点能容纳的键值数量就变少树变高检索效率反而下降。解决办法之一就是前缀索引。ALTER TABLE articles ADD INDEX idx_url_prefix (url(64));选取合适的N可以让区分度接近完整列同时大幅缩小索引体积。选取方法很简单SELECT COUNT(DISTINCT LEFT(url, 4)) / COUNT(*) AS ratio4, COUNT(DISTINCT LEFT(url, 8)) / COUNT(*) AS ratio8, COUNT(DISTINCT LEFT(url, 12)) / COUNT(*) AS ratio12 FROM articles;前缀索引的代价是无法用于覆盖索引和ORDER BY因为索引里存的不是完整值。我一般是在区分度达到90%以上才选不然意义不大。MySQL 8.0还引入了函数索引语法上使用(表达式)作为索引列。这对条件里必须加函数的场景非常有用比如统计日期的SELECT COUNT(*) FROM orders WHERE DATE_FORMAT(create_time, %Y-%m-%d) 2025-06-01;这种写法被很多老手称为“索引杀手”因为只要列套上函数普通索引就失效了。解决思路其实有两个一个是改写成范围查询SELECT COUNT(*) FROM orders WHERE create_time 2025-06-01 00:00:00 AND create_time 2025-06-02 00:00:00;另一个就是在MySQL 8.0里建函数索引ALTER TABLE orders ADD INDEX idx_date_create ((DATE_FORMAT(create_time, %Y-%m-%d)));函数索引本质是把表达式的结果存储到索引里代价是每次写数据都要计算一次读多写少的场景用起来很划算。2.4 关于冗余索引和“索引越多越好”的误解很多开发者的潜意识里是“查询慢就加索引”结果表上挂了十几个索引反而更新变慢、磁盘占用变大。InnoDB里每个索引都是额外的B树写入时要同步维护插入一条记录可能要同时更新四五棵树。冗余索引的典型例子是已经有了(a, b)联合索引再建一个单独的(a)索引。因为联合索引的最左前缀已经能覆盖只查a的场景单独的(a)索引就是完全多余的。但反过来(b)索引可不能省因为最左前缀决定了单独查b用不上(a, b)索引。检查冗余索引我一般直接用pt-duplicate-key-checker扫描一遍或者在运维窗口里自己查一下information_schema里的索引统计。人工检查的逻辑也很清晰如果一个索引是另一个索引的最左前缀通常就是冗余。3. 性能调优实操一个真实订单查询的优化过程3.1 建表与原始慢查询这里我拿一个简化过的订单表来演示表结构如下CREATE TABLE orders ( id bigint NOT NULL AUTO_INCREMENT, user_id bigint NOT NULL, order_no varchar(64) NOT NULL, status tinyint NOT NULL DEFAULT 0, amount decimal(12,2) NOT NULL, create_time datetime NOT NULL, update_time datetime NOT NULL, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;表里有大约300万行数据。线上反馈说一个统计页面打开极慢对应SQL是这样的SELECT user_id, COUNT(*) AS cnt, SUM(amount) AS total_amount FROM orders WHERE create_time 2025-01-01 AND create_time 2025-04-01 AND status 1 GROUP BY user_id ORDER BY cnt DESC LIMIT 100;这是一条典型的“时间段内统计用户订单数和金额”的报表查询。原始状态下没有任何索引能同时服务create_time的范围条件、status的等值条件、GROUP BY user_id的分组。执行计划大概率是全表扫描或只在一个单列索引上做范围扫描然后临时表分组加文件排序。3.2 第一次优化联合索引定位数据范围我先在(create_time, status, user_id)上建立一个联合索引理由是按时间范围框定数据区间再按状态过滤最后用user_id分组。ALTER TABLE orders ADD INDEX idx_time_status_user (create_time, status, user_id);执行计划的type从ALL变成了rangerows从300万降到约60万初步过滤是有效果的。但问题依然存在status和user_id其实都是等值或分组条件把时间范围列放第一位让status和user_id没法利用到索引的有序性GROUP BY user_id还是走了临时表和文件排序。3.3 第二次优化调整列顺序照顾GROUP BY联合索引的调整思路是让等值条件优先。把status提到最前create_time范围列放在后面user_id继续放末尾以满足分组排序ALTER TABLE orders ADD INDEX idx_status_time_user (status, create_time, user_id);这次type变成了refrows降到约2万行Extra里的Using temporary; Using filesort依然存在但临时表的数据量小了很多查询时间从秒级降到了几百毫秒。这里补充一个关键点GROUP BY user_id使用索引排序的条件是索引列顺序完全匹配分组顺序而且分组列必须是索引的最左部分。现在索引顺序是(status, create_time, user_id)排序时先按status再按create_time再按user_id显然无法直接为GROUP BY user_id工作。要想彻底消除临时表和文件排序理想情况是让user_id成为索引中分组相关的第一个键。3.4 换个思路用覆盖索引消除回表既然这条SQL只需要user_id、amount、create_time、status这几个字段那么干脆做一个完全覆盖查询的索引ALTER TABLE orders ADD INDEX idx_status_user_time_amount (status, user_id, create_time, amount);这里把status放最前是等值过滤user_id放第二是三方的分组依据create_time放在后面满足时间范围过滤amount作为最后一个叶子列则是为了让整个查询不需要回表取amount直接就能从索引里拿到求和需要的所有数据。执行计划里出现了Using index扫描行数进一步减少没有Using temporary了Using filesort可能还在因为最终排序是针对分组后的聚合结果。但从查询时间看原本2秒多的一条统计查询调到稳定在150毫秒以内这就是覆盖索引加联合索引组合拳的效果。3.5 用实际参数说话优化前后对比我最关注的指标是这几个指标优化前第一次优化第二次优化最终方案扫描行数预估300万60万2万5000typeALLrangerefrefExtra关键项Using temporary; Using filesortUsing temporary; Using filesort临时表缩小但仍有Using index查询耗时实测2.5s800ms280ms130ms从这张表能直观感受到索引策略的每一步是如何啃掉一块性能瓶颈的。3.6 善用索引下推和排序优化MySQL 5.6引入的索引条件下推ICP是个容易被忽视的优化点。它允许存储引擎在读取索引的过程中就过滤掉部分不满足条件的行而不是全查出来再回表过滤。上面最终方案里如果条件里再夹杂一些非索引字段判断ICP也能帮上忙前提是数据库版本至少5.6并且optimizer_switch里的index_condition_pushdown是开启状态。排序优化是另一个常被忽略的维度。如果在ORDER BY上有高成本的排序需求我通常分两种情况处理排序列能放进索引且顺序与查询一致直接走索引排序没有filesort排序列无法进入索引先缩小数据集合再排序必要时对结果集做缓存或落临时表这里有个血泪教训不要在ORDER BY的字段上使用表达式比如ORDER BY DATE_FORMAT(create_time, %Y-%m-%d)一旦带上函数或表达式即使这个字段有索引也无法利用索引排序。所以排序条件尽量写成裸列名。4. 索引失效的常见场景和排查方法4.1 隐式类型转换这类问题出现得非常多。比如order_no字段类型是varchar(64)但查询条件却用了整数SELECT * FROM orders WHERE order_no 1234567890;MySQL对字符串和数字比较做过特殊处理实际上会在这个列上触发隐式类型转换把order_no的每一行都转成数字再比较索引自然就废了。处理方式很简单条件写成字符串形式比如order_no 1234567890。另一个隐藏很深的点在字符集。两张表关联时如果a.user_id是utf8mb4b.user_id是utf8关联也会因隐式转换导致无法走索引。建表时统一字符集是个基础要求我之前接手过历史项目两个库的字符集居然都不一样每次关联查询都在慢查询日志里躺了一长串。4.2 对索引列做计算或函数操作这一点在上面说过。只要索引列被函数或表达式包裹优化器就基本放弃了索引。常见的还有WHERE create_time INTERVAL 1 DAY NOW() WHERE amount * 1.1 100 WHERE YEAR(create_time) 2025这类我都建议改写为范围比较条件让索引列保持“裸列”状态。比如YEAR(create_time) 2025可改写成WHERE create_time 2025-01-01 00:00:00 AND create_time 2026-01-01 00:00:00这是在应用查询中最简单的优化手段改动量小、收益直接。4.3 LIKE以通配符开头LIKE %abc这类查询注定无法使用索引因为B树只能按前缀定位。需要搜索中间片段时方案无非是全文索引FULLTEXT外接搜索引擎比如Elasticsearch存储冗余字段专门做反向前缀匹配如果数据量不大接受全表扫描并在应用层做缓存LIKE abc%则可以走索引因为它是前缀匹配。text或varchar大字段的LIKE优化尤其恼人尽量别把它作为常规查询条件。4.4 OR连接条件和非等值场景OR条件是我排查慢查询时最头疼的写法之一。比如SELECT * FROM orders WHERE user_id 123 OR status 1;user_id有索引status也有索引但MySQL在特定条件下可能不使用索引合并而是把OR条件转化为类似全表扫描的处理。即使使用了index_merge性能往往也不如查两次再合并。实际工作中我更建议把这类SQL拆成两条SELECT * FROM orders WHERE user_id 123 UNION ALL SELECT * FROM orders WHERE status 1 AND user_id 123;当然这个改动要看业务逻辑是否允许这样拆分语义等价性需要自己确认。另外NOT IN、NOT EXISTS、!这类否定型条件也经常让优化器放弃索引因为数据库认为“排除少数行”不如“扫描大部分行”划算。具体取决于数据分布也可能受null值影响。遇到!导致的全表扫描如果取的数据确实少可以考虑改写成范围查询比如col ? OR col ?但有没有收益需要实际验证。4.5 数据分布变化导致优化器“不长眼”有时候索引明明建好了执行计划却走了全表扫描。这可能是统计信息没有更新导致的。MySQL用统计信息基准确认数据的分布情况而InnoDB的统计信息是采样估算的。遇到数据量剧烈变化、大批量导入后可以执行ANALYZE TABLE orders;这会重新收集表的统计信息。我遇到过一次线上系统批量导入了上百万数据第二天慢查询突然变多执行计划从ref变成了ALL执行完ANALYZE TABLE后一切恢复。原因就是优化器用旧统计信息估算了错误的行数。4.6 快速定位索引失效的排查清单我习惯用表格整理排查步骤方便复盘和交接排查内容检查方法典型原因查询列是否被函数包裹看SQL里索引列是否有函数或表达式改写为范围条件字段类型是否一致SHOW CREATE TABLE对比关联字段统一字符集和类型条件是否有OREXPLAIN看type是否为ALL拆分SQL排序/分组是否与索引顺序一致看Extra是否有Using filesort调整联合索引列顺序统计信息是否过期ANALYZE TABLE后对比执行计划重新收集统计信息是否范围条件后在索引中还有后续列联合索引范围列后加列需要具体分析调整索引设计或改查询条件5. 运维层面的索引管理与线上变更注意事项5.1 索引变更不要直接在线执行除非用在线DDL索引优化策略里有一个环节常被忽略怎么把新索引安全地加到线上。早期MySQL版本的ALTER TABLE ADD INDEX会锁表对线上业务是致命的。MySQL 5.6开始支持在线DDLALGORITHMINPLACE也就是在变更过程中允许DML同时进行。但要注意在线DDL仍然会增加主从延迟大表上的索引创建可能需要数小时期间磁盘I/O压力巨大建议使用gh-ost或pt-online-schema-change这类工具来平滑处理超大表的索引变更我处理过一张5亿行的流水表加索引直接用原生ALTER TABLE跑了一个多小时监控显示主从延迟飙到几千秒。后来改用pt-online-schema-change加上流量控制参数业务基本无感。5.2 定期清理无效和重复索引索引不是越多越安全。除了上面提到的冗余索引还有一些长期不被使用的“僵尸索引”。判断依据很简单查询performance_schema.table_io_waits_summary_by_index_usage可以看到索引的使用统计。如果某索引的COUNT_STAR增长极慢、几乎为零说明优化器从没选过它可以考虑在业务低峰期删除。但删除索引前一定要确认历史慢查询和定时任务里没有隐式的依赖路径。我曾经清理了一个看起来“半年没被用过的索引”结果月底报表凌晨任务全部超时因为报表SQL只有在特定日期才能命中那个索引平时测试环境根本不会走到那条路径。教训就是删索引比加索引更需要走审批流程和灰度验证。5.3 分区表与索引的选择分区表不是独立的索引优化手段但常被拿来和索引一起考虑。比如按月份的订单表做了RANGE分区每个分区有独立的索引树查询时如果查询条件包含分区键可以显著减少扫描数据量。但分区表也有陷阱如果查询条件不包含分区键优化器依然要做全分区分区扫描索引效果大打折扣。而且分区表在新增分区DDL、全局唯一索引等场景上都有额外限制。我对大多数项目的建议是先优化索引数据量到了一定规模再考虑分区不要一上来就为“可能变大”而分区。5.4 监控和回归索引优化的闭环优化完索引不是结束还要做回归。常规手段是对比优化前后的慢查询日志确认目标SQL消失或耗时大幅下降观察一两个完整业务周期比如一天或一周防止夜间批处理任务偶发慢查询关注线上实例的QPS、磁盘I/O、CPU使用量变化确认没有引入负面效应把优化的执行计划备份到文档里作为后续复查基线我自己习惯写一份简单的“SQL优化登记表”记录优化日期、SQL指纹、原执行计划关键指标、新执行计划关键指标、影响业务方、回滚方案。这个习惯帮我在后续数据库版本升级、统计信息大范围更新时能快速定位哪些SQL可能会“回归变慢”。6. 关于索引优化策略我的一些实际感悟做了这么多年的MySQL性能优化我最大的体会是索引优化不是一个孤立的数据库操作而是对业务查询模式的深度理解。你越了解业务到底在查什么、查多频繁、返回多少行索引就越能建到点子上。完全不看业务场景拿着数据库理论往里套失败概率非常高。还有一点心得是优化索引要舍得“做减法”。一个表上塞满索引看着是每个查询都有保障实际上每一个写入请求、每一次数据更新都要付出额外的维护成本。很多看似复杂的性能问题反而是因为索引太冗余导致优化器决策困难选择范围过大反而容易“选错路”。如果让我给出一条可复制的执行路径我会推荐这么做先收集慢查询和表状态理清查询模式和业务优先级再针对最高频、最耗时的SQL做联合索引和覆盖索引设计然后小范围上线、观察执行计划和慢查询日志最后定期清理无效索引和统计信息。这条路径看起来很朴素但跑下来的效果往往比各种“高深优化技巧”实在得多。最后分享一个小细节每次改完索引我都会把EXPLAIN结果存档文件名按日期和业务模块来命名。别小看这个习惯等到三个月后业务方追一句“这个接口怎么变慢了”你能立刻把执行计划调出来对比省下的排查时间不是一星半点。