接手线上业务系统这几年最让我头疼的问题之一就是MySQL慢查询。明明功能开发完了、测试也过了一到流量高峰期几个核心接口突然就卡了几秒数据库CPU飙高紧接着就是连接数打满、应用报错。排查下来十有八九是SQL走了全表扫描或者索引没建到位。说穿了MySQL性能优化核心就两件事把慢SQL捞出来把索引设计好。这条链路我踩了不少坑今天把从慢查询定位到索引设计的完整实操流程整理出来希望能帮同行少走弯路。这篇文章适合谁看一种是刚接手项目的后端开发者线上出现慢查询但不知道从哪下手另一种是已经知道EXPLAIN和索引概念但遇到真实场景还是不知道怎么组合使用的人。我会把每个环节的原理、操作、踩坑点都说透能直接套用到自己的项目里。1. 慢查询日志把“案发现场”先抓出来1.1 开启慢查询日志的正确姿势很多人的第一反应是慢SQL不是直接在应用日志里能看到吗还真不一定。应用层能看到的是接口超时、报错StackTrace、数据库连接异常但具体是哪条SQL把数据库拖垮的通常要等DBA拉数据库侧日志才能看到。而慢查询日志就是我们最直接的“案发现场”。在MySQL里开启慢查询日志并不复杂但有个前提你要知道当前实例的参数是什么状态。我用得最多的一条语句是SHOW VARIABLES LIKE %slow%;重点看三个参数slow_query_log、slow_query_log_file、long_query_time。默认情况下MySQL的慢查询日志开关是关闭的你光改long_query_time是不生效的必须把slow_query_log置为ON。线上环境我建议这样配置在my.cnf的[mysqld]段下修改或启动参数追加slow_query_log ON slow_query_log_file /var/log/mysql/mysql-slow.log long_query_time 1 log_queries_not_using_indexes ON log_throttle_queries_not_using_indexes 10long_query_time设成1秒不是0.5秒也不是5秒这是我实践下来的经验值。设成0.5秒会把大量正常查询都记录下来日志量膨胀得很快真正问题反而被淹没设成5秒又太迟钝很多隐患其实是查询从1秒涨到3秒、5秒等你看到5秒的慢查询时业务其实早就受影响了一轮。1秒是个折中值既能抓到大部分异常查询又不会制造太多日志噪音。这里有个细节容易被忽略long_query_time是精确到微秒的配置文件里写整数1表示1秒整你也可以写成0.5表示500毫秒。但我在生产环境很少用小数一是日志噪音大二是团队其他成员对“什么样的SQL算慢”的认知会变得模糊。log_queries_not_using_indexes这个参数意思是“即使查询没超过long_query_time但只要没走索引也记录到慢日志里”。这个参数非常有用因为很多性能隐患不是query耗时已经很长而是已经开始出现全表扫描的苗头了。配合log_throttle_queries_not_using_indexes做限流能防止这类“无索引查询”日志刷屏。1.2 慢查询日志长什么样我截取一条线上真实的慢日志片段脱敏处理过说明一下怎么看# Time: 2025-01-15T10:23:45.123456Z # UserHost: app_user[app_user] [192.168.1.108] # Query_time: 5.234567 Lock_time: 0.000123 Rows_sent: 25 Rows_examined: 1048576 SET timestamp1736922225; SELECT order_id, user_id, total_amount, status FROM orders WHERE create_time BETWEEN 2025-01-14 00:00:00 AND 2025-01-14 23:59:59 AND status 1 ORDER BY create_time DESC LIMIT 25;先看Query_time: 5.23说明这条查询总共花了5.23秒。再看Rows_examined: 1048576扫描了104万行但只返回25行。什么叫低效这就是典型例子——为了拿25条数据翻了100万行。再看Lock_time只有0.000123秒说明没有长时间锁等待纯粹是查询本身慢排除了锁竞争的因素。这几组数字组合起来能帮我们快速判断问题的大方向高Query_time 高Rows_examined典型扫描行数过大优先考虑索引优化。高Query_time 低Rows_examined 高Lock_time可能有锁等待需要排查并发事务。高Query_time 高Rows_sent可能是数据批量导出或报表类查询未必是索引问题。1.3 日志分析工具从海量日志里捞重点线上跑了几天后慢日志文件可能已经几百MB了。你不可能一条一条用眼睛看得用工具归类。我常用的两个工具mysqldumpslow是MySQL自带的简单直接。我最常跑的这几条命令# 按平均查询时间排序查看Top 10 mysqldumpslow -t 10 /var/log/mysql/mysql-slow.log # 按总查询时间排序适合查看哪类SQL消耗的数据库时间最多 mysqldumpslow -s at /var/log/mysql/mysql-slow.log # 按扫描行数排序看哪些SQL在扫描大量数据 mysqldumpslow -s ar /var/log/mysql/mysql-slow.log但要注意mysqldumpslow默认会把SQL中的具体值替换掉比如订单号、用户ID都会变成N。这有好处也有坏处好处是能把同一类SQL聚合起来看坏处是你无法直接拿到某条真实SQL的完整参数值。线上排查时通常需要上下文我会用mysqldumpslow快速定位到某个模板SQL类型后再去对应时间段的应用日志里找具体参数。如果你的环境允许安装Percona Toolkitpt-query-digest是更专业的工具。它的报告会按查询消耗总时间排序还会给出每一类SQL的响应时间占比、历史波动情况甚至能告诉你哪些查询是“单次快但次数极多”这类问题在慢日志里按单次耗时看不出来但按总消耗排就能暴露出真相。我经常遇到的情况是某条SQL单次只要80毫秒不算慢但每秒要被调用几百次累积下来的数据库压力比一条3秒的慢查询还可怕。pt-query-digest对这种“高频微慢”型查询的识别能力是很强的。2. 用EXPLAIN读懂SQL的执行路径2.1 EXPLAIN到底在说什么抓出了慢SQL下一步就是对它执行计划。EXPLAIN的输出列每一个都值钱。我按自己的理解把核心字段解释一遍。id查询编号。通常一个查询只有一个id但如果SQL里有多表连接、子查询就可能出现多行记录。id越大越先执行这是理解多表查询顺序的关键。select_type查询类型。最常用于判断是否出现了子查询或UNION。table这行输出对应哪张表。type这是所有字段里最重要的直接决定了访问方式。从上到下效率递减system const eq_ref ref range index ALL我简单说明一下system和const是走了主键或唯一索引的等值查询这是效率最高的MySQL直接把结果当成常量处理eq_ref是连接查询中被驱动表走主键或唯一索引每个驱动表行最多匹配一行ref是非唯一索引的等值匹配可以匹配多行range是索引范围扫描典型的就是BETWEEN、、、INindex听起来像走索引但实际上是把整个索引树扫了一遍比全表扫描好一点但也是扫描全部ALL就是全表扫描基本是性能问题的头号嫌疑。看到ALL大概率就要警觉。但别看到ALL就急着加索引还要结合rows字段看。rows预估扫描行数。这个数字和实际执行的行数可能不完全一致但可以用来比较不同执行计划的优劣。filtered预估被条件过滤的比例。rows乘以filtered大致就是实际返回行数。Extra这一列信息量也很大。我挑几个常见的说Using index condition 使用索引下推通常和二级索引配合 Using where 存储引擎返回后在Server层过滤 Using index 使用覆盖索引不回表 Using temporary 用临时表常见于GROUP BY或去重 Using filesort 文件排序通常需要优化Using temporary和Using filesort是两个需要注意的信号尤其是Using temporary往往意味着MySQL需要把中间结果暂存到临时表一旦数据量大磁盘I/O就是把性能拖垮的元凶。2.2 用EXPLAIN实测发现索引失效的瞬间来看一条线上出过问题的SQLEXPLAIN SELECT * FROM orders WHERE order_no 202501150001231;这个表order_no字段其实建了唯一索引但执行计划显示type为ALLrows到了几十万。为什么有索引还全表扫这里要警惕order_no字段可能是VARCHAR类型而我传的参数是数值类型MySQL会做隐式类型转换先把字段值转成数值再比较索引就失效了。遇见这种情况我会再多跑一条SQL确认EXPLAIN SELECT * FROM orders WHERE order_no 202501150001231;这次type变成了const走了唯一索引。一对比问题原因就非常清楚了。隐式类型转换是索引失效的高频原因而且往往专门坑老手——因为看起来SQL写得没问题参数类型也没人深究。排查慢查询时把常用查询条件字段的类型和应用传入类型逐一对齐是我给自己定的硬规矩。2.3 对执行计划快速判断的流程拿到一个执行计划我习惯按下面的顺序过一遍先看type是不是ALL或index。如果是基本可以确认索引设计有问题。再看Extra里有没有Using temporary或Using filesort。然后看rows结合表总行数估算扫描比例。如果扫描比例超过5%-10%即使走了索引也可能需要考虑优化SQL逻辑因为从索引中读取5%的数据优化器往往会判断为不如全表扫。最后看key列确认实际用的索引是哪条。有时候你建了索引但优化器选了另一条也常见。这套四步流程走完慢SQL的病根基本能定位个七八成。3. 索引设计从B树原理到落地策略3.1 为什么索引能提速以及它的代价理解了慢查询和EXPLAIN还只是解决了“发现问题”这一步。真正难的是“如何设计索引”。要设计好索引得先理解InnoDB索引底层的B树结构。一句话概括B树是一种多路平衡查找树数据只存在叶子节点非叶子节点只存索引键值。叶子节点之间用双向链表连接。这样的结构带来的好处是按索引键查找时从根节点到叶子节点的路径非常短三层B树就能支撑上千万行数据也就是查询一次最多读三个节点再加上叶子节点链表对范围查询的天然支持效率远高于二分查找或全表扫描。但索引不是免费的。每一次插入、更新、删除都要额外维护索引结构写放大是不可避免的。一张表如果建了太多索引写入性能会明显下降磁盘空间占用上涨。这就是为什么“索引建得越多越好”是错误观念。核心原则是优先保证查询频次高、区分度好的字段走索引同时控制索引总数。3.2 单列索引还是组合索引大多数优化课题都会走到组合索引这一步。组合索引的核心逻辑是“最左前缀原则”。一个组合索引(a, b, c)存储时是先按a排序a相同再按b排序b相同再按c排序。查询时MySQL可以利用到a、a,b、a,b,c但直接用b或c作为条件就命不中这个索引的左前缀规则。就拿我遇到过的一个实际例子来说某张业务表经常有这样的查询SELECT * FROM user_order WHERE user_id 1001 AND status 1 AND create_time 2025-01-01 ORDER BY create_time DESC;如果分别建单列索引(user_id)、(status)、(create_time)MySQL一次查询只能选择其中一个索引作为主驱动。这会导致其他条件在返回后过滤扫描行数多排序也要额外处理。而正确的做法是建组合索引(user_id, status, create_time)让三个条件都尽可能走索引同时ORDER BY create_time也能直接用索引的有序性完成排序减少Using filesort的概率。组合索引的列顺序总体原则是把区分度高的列放前面。区分度可以理解为某个列值的不重复比例比如user_id通常就是高区分度字段status如果只有两个枚举值区分度就很低。把高区分度列放在组合索引前面能更快地缩小扫描范围。需要强调最左前缀原则并不是说组合索引必须覆盖所有查询条件才能用只要查询条件符合最左前缀的顺序哪怕只有第一个字段等值匹配索引也能生效。但如果是第一个字段的范围查询后面字段的排序利用就会受限这是写SQL时要注意的细节。3.3 覆盖索引少查一次回表的收益InnoDB的二级索引叶子节点存的是索引列主键值通过二级索引查到主键后再去聚簇索引取完整行数据这个动作叫回表。回表次数越多I/O开销越大尤其是遇到大量随机主键时性能损失非常明显。覆盖索引的意思是查询需要的所有列都在同一个二级索引里不需要回表。举个例子表结构有字段order_id、user_id、status、create_time原本查询是SELECT order_id, user_id, status FROM orders WHERE user_id 1001;如果建立了组合索引(user_id, order_id, status)那么索引里就包含user_id、order_id、status三列查询完全可以只扫描索引就返回结果Extra里会出现Using index标记斜体提示就是这意思。覆盖索引的优化效果很直观能明显降低磁盘I/O但代价是索引本身占用更多空间所以只推荐在核心高频查询上这么做。3.4 索引设计要不要管写入性能这是个容易走极端的问题。我见过一些开发同学为了让查询“飞起来”一口气建了七八个索引结果写入变慢、表体积膨胀甚至用户体验更差了。索引一定是有代价的这个代价在写入密集的表上感受得特别明显。实际项目中尤其是订单、日志、流水这类有持续写入压力的表每加一个索引写入时就要多维护一棵B树锁竞争和redo日志都会变大。我的做法先统计表中的高频查询条件再统计低频查询条件优先级排序后设计索引。凡不属于核心路径的查询宁愿让它慢一点也不让索引数量失控。表行数超过百万、核心查询又有强排序、强过滤需求的有策略性的冗余字段设计往往是比死磕索引更划算的方向。4. 完整优化实战从诊断到落地的全流程4.1 案例一深分页查询慢得离谱某日线上有个接口前端要支持翻页到第100页往后某同事反馈数据一多就超时。慢日志找到的SQL是这样SELECT id, order_no, user_id, status, create_time FROM orders WHERE user_id 10086 ORDER BY create_time DESC LIMIT 5000, 20;这个SQL的问题在于LIMIT 5000, 20MySQL并不是“跳过5000行再取20行”而是先按条件扫描出前5020行把前5000行扔掉只返回最后20行。用户ID过滤后基线本来就有几十万行每次翻到靠后的页码前面所有扫描都成了无用功。优化方法之一是用“延迟关联”SELECT o.id, o.order_no, o.user_id, o.status, o.create_time FROM ( SELECT id FROM orders WHERE user_id 10086 ORDER BY create_time DESC LIMIT 5000, 20 ) tmp JOIN orders o ON tmp.id o.id;核心逻辑是让子查询先用索引走最窄的列只查主键ID把分页的行数扫描降到最小然后再回原表取完整行数据。实际测试中同样的翻页深度这个写法能把查询从原来的3秒多压到0.2秒以内效果立竿见影。4.2 案例二IN 子查询的深坑有个报表需求要从两张表里查数据SQL长这样SELECT * FROM user_order WHERE user_id IN ( SELECT user_id FROM user_vip WHERE vip_level 3 );慢日志显示执行计划走了全表扫描user_order表上其实有user_id的索引但子查询导致优化器可能选择了不合适的执行策略。我的排查思路是第一步先单独执行子查询确认返回行数第二步手动改写为JOIN测试执行计划差异。改写为SELECT o.* FROM user_order o JOIN user_vip v ON o.user_id v.user_id WHERE v.vip_level 3;改写后优化器能更好地判断驱动表和被驱动表配合索引效果明显。需要提醒的是不是所有IN子查询都必须改写为JOIN在子查询结果集很小、外层表很大的场景下IN的写法也可能走很好的索引。关键是要用EXPLAIN去验证让我特别无语的是有的人拿着优化手段当教条不看执行计划就一律改写成JOIN结果把正常的执行计划改坏了性能反而更差。4.3 案例三ORDER BY的Filesort怎么消除我们系统里有个查询榜单的需求按点击量降序取Top N原SQLSELECT article_id, title, click_count FROM article WHERE status 1 ORDER BY click_count DESC LIMIT 100;执行计划里出现Using filesort。如果能在(status, click_count)上建立组合索引因为status是等值条件、click_count又是排序列且组合索引天然有序MySQL可以避免额外排序。但这里有个经典的坑如果status不是等值过滤而是范围条件比如status 0组合索引的排序能力就会被范围条件破坏因为范围条件下相同status内无法保证按click_count有序。真正的解法是缩小过滤范围或者明确让order by字段成为组合索引最后一列。4.4 案例四大表统计类的查询优化有一次报表需求要从一个月的数据里做聚合统计SQL写法SELECT DATE_FORMAT(create_time, %Y-%m-%d) AS day, COUNT(*) AS cnt, SUM(total_amount) AS amount FROM orders WHERE create_time 2025-01-01 AND create_time 2025-02-01 GROUP BY day;这种按时间范围做GROUP BY的查询在create_time上建索引是基础但真正让这个大查询变快的还能用上“时间分区表”或者“预聚合表”。当时因为表本身已经有create_time索引方案上是先把范围查询的扫描行数降到最低利用索引下推把create_time过滤在引擎层完成。但如果只是临时跑报表更简单的方式是让分区表按月分group by就直接落在各自分区上互不干扰。小项目不建议一上来就分区但如果确实每月有几十万行增长尽早设计分区是有远见的。5. 高频问题快查与排查方法论5.1 慢查询问题速查表现象可能原因优先排查方向typeALL、Rows_examined很大无索引或索引失效看WHERE条件字段是否有索引SQL有索引但typerange范围条件或隐式转换检查参数类型、范围过滤占比ExtraUsing filesortORDER BY未走索引检查排序列是否在索引后段ExtraUsing temporaryGROUP BY或DISTINCT内存不足看临时表是否下到了磁盘高频微慢查询N1查询或CPU单点压力应用侧合并查询、缓存热点数据锁等待导致慢查询事务并发冲突查看INNODB_TRX和锁等待状态5.2 排查方法论先看趋势再看单条慢查询排查我习惯分为三步第一步用mysqldumpslow或pt-query-digest做整体趋势分析选定最耗资源的一类SQL第二步定位到具体SQL后用EXPLAIN看执行计划逐步拆解问题第三步在测试环境复现并对比优化前后效果注意不要在线上直接改索引尤其是删除旧索引一定要先验证再执行。很多人在第一步就卡住了因为慢日志里的SQL太多不知道该先处理哪条。我给个经验法则先处理“总耗时高”的SQL再处理“单条耗时高”的SQL。业务高峰期哪怕单条查询只要200毫秒只要调用量足够大对数据库的压力也远超一条偶尔出现的2秒慢查询。5.3 监控和预防机制优化完成之后不要觉得就万事大吉了。数据库的查询模式会随着业务版本的迭代持续变化可能下个月新上线一个功能就带来了新的慢查询。建议把以下几个环节固化到日常流程里持续开启慢查询日志最少保留30天。日志轮转要配置好避免磁盘被打爆。每天或每周自动分析一次慢日志Top 10的SQL变化趋势做成报表能及时发现性能波动。关键表的索引变更走review流程不要随便加也不要随便删。大版本发布前先对比慢日志基线确认新查询语句的执行计划符合预期。另外说一个运维上的技巧对线上慢日志文件的命名建议按天切割比如mysql-slow-2025-01-15.log。方便后续按月归档、删除也方便回溯某个活动日当天数据库的表现。5.4 几个容易踩的坑遇到过有人为了让一条SQL走索引在WHERE条件里对索引字段做函数运算比如DATE(create_time) 2025-01-15这会让create_time上的索引失效因为MySQL要先把每一行的字段都应用一遍DATE函数才能进行比较索引排序规则被破坏了。正确写法是create_time 2025-01-15 00:00:00 AND create_time 2025-01-16 00:00:00。这个坑藏得很深因为结果可能没错但性能差了一两个数量级。还有一件事是关于NULL值的。字段上有索引但NULL在B树中如何存储不同版本、不同引擎有细微差异。经验上如果查询经常用IS NULL或IS NOT NULL就不要在可空字段上指望索引能很好地工作尽量设计成NOT NULL DEFAULT用0或者空字符串替代这在索引层面更友好。再来说说索引维护。线上大表的索引经过长期高并发写入后B树页分裂会造成碎片查询性能会缓慢下降。有定期维护需求的话可以执行ALTER TABLE table_name ENGINEInnoDB来进行碎片整理但注意这个操作会锁表大表要谨慎建议在维护窗口执行。写在最后的一个小技巧优化MySQL性能我觉得最关键的能力不是会背多少命令而是能快速判断“是什么在拖慢数据库”。慢查询日志定位到SQLEXPLAIN拆解执行计划索引设计解决访问路径这套流程跑熟之后大部分性能问题都能在半小时内定位到根因。最后分享一个我自己养成的小习惯每次新交付的查询SQL我都会顺手跑一次EXPLAIN确认type不是ALL、Extra里没有Using filesort再合入代码。这个习惯帮我拦住了非常多线上问题成本几乎为零收益却极大。