先说个真实场景。上个月线上订单表到了千万级一个按用户查近期订单的接口响应时间从 100ms 一路飙到 1.5s数据库 CPU 偶尔直接打满。同事第一反应是服务器配置不够加内存换 SSD结果第二天又崩了。后来把慢查询日志打开一条 SQL 扫了全表问题根本不在硬件。这就是 MySQL 里 SQL 调优最典型的起点——大部分性能问题的根因就藏在 SQL 写法、索引设计和执行计划里而不是服务器配置上。这篇内容我会按照实际排查的顺序来写怎么发现问题、怎么读懂执行计划、怎么写索引、怎么改 SQL配合系统层的参数配置和调优工具覆盖调优路上最常踩的坑。不管你是刚接触 MySQL 的新人还是被慢 SQL 折磨过几次的开发按这套思路走下来基本能解决绝大多数线上问题。1. 先定位问题再谈调优很多人在 SQL 调优时容易犯一个错误一上来就翻参数配置调 buffer pool、改刷盘策略结果 SQL 还是慢。调优的第一步永远不是改东西而是把问题找出来。MySQL 本身提供了完整的诊断链路从慢查询日志到执行计划再到 profiling一层一层往下挖就行。1.1 慢查询日志是第一现场慢查询日志是 MySQL 记录执行时间超过阈值的 SQL 的日志文件也是排查慢 SQL 的首要入口。默认情况下这个功能是关闭的需要手动开启我用得最多的方式是直接在 MySQL 命令行执行# 查看当前慢查询日志状态 SHOW VARIABLES LIKE slow_query_log; # 开启慢查询日志并设置阈值我这里设为2秒测试环境可以设得更低 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 2; SET GLOBAL slow_query_log_file /var/log/mysql/slow.log;注意long_query_time的单位是秒而且这个参数对已经开启的会话不生效测试时需要重新连接或者另开一个会话。实际生产环境我平时会设为 1 秒再配合log_queries_not_using_indexes ON记录没有走索引的 SQL这样连扫描全表的问题也能暴露出来。开启之后线上跑一段时间用mysqldumpslow工具把慢日志汇总一下就能快速找出哪些 SQL 出现频率高、总耗时最长。命令大概是这样的mysqldumpslow -s at -t 10 /var/log/mysql/slow.log-s at表示按平均耗时排序-t 10表示只看前 10 条。这样能快速锁定最需要处理的目标而不是在几千条日志里大海捞针。1.2 EXPLAIN读懂 MySQL 的“路线图”拿到慢 SQL 之后下一步就是看执行计划。执行计划就是 MySQL 优化器生成的查询执行方案相当于导航软件给出的路线图。用EXPLAIN加在 SQL 前面就能看到这张图比如EXPLAIN SELECT order_id, amount FROM orders WHERE user_id 12345 ORDER BY created_at DESC LIMIT 20;输出结果里最关键的是这几个字段type访问类型从好到差依次是system、const、eq_ref、ref、range、index、ALL。看到ALL意味着全表扫描这是最需要警惕的。key实际用到的索引如果为NULL说明没有使用索引。rows预估扫描的行数这个值越小越好。如果预估 10 万行实际返回 20 条说明过滤性很差需要排查索引或 SQL 写法。Extra经常能看到Using filesort文件排序、Using temporary使用临时表、Using index覆盖索引等信息这些信息直接决定后续优化方向。我习惯每次看执行计划的时候把注意力放在type和rows上这两个字段最直观。如果type是ALL先别急着改 SQL很可能就是缺少合适的索引如果type是ref但rows特别大那要考虑索引列的选择性是不是够好。1.3 用 profiling 量化耗时分布EXPLAIN能告诉你计划长什么样但要知道时间到底花在哪个阶段就得靠 profiling。MySQL 提供了SHOW PROFILES命令可以查看一条 SQL 在服务器端各个阶段的耗时。SET profiling ON; -- 执行你的慢 SQL SELECT * FROM orders WHERE user_id 12345; SHOW PROFILES; SHOW PROFILE FOR QUERY 1;输出里会展示Sending data、Sorting result、Creating sort index等阶段分别耗时多少。这里我分享一个实际排查经验有一次一条查询EXPLAIN显示走的是索引但响应还是要 300ms用 profiling 一看发现大部分时间花在Sending data上说明通过网络传输的数据量太大根本问题其实是查询返回了太多用不到的字段这也是为什么我一直强调尽量避免SELECT *。2. 索引设计决定 SQL 性能上限调优做到后面你会发现80% 的 SQL 性能问题都出在索引上。索引不是建了就完事建错索引比不建索引更可怕因为 MySQL 优化器可能被误导走了错误的执行计划。这一章我会把索引设计里最核心的几个问题讲清楚。2.1 最左前缀原则联合索引的黄金法则联合索引是 MySQL 里最常用也最容易用错的索引。很多人建了(a, b, c)联合索引就以为任何查询都能走索引结果 SQL 还是全表扫描其实就是没搞懂最左前缀原则。最左前缀原则的定义是联合索引的生效前提是查询条件从索引的最左列开始并且不能跳过中间的列。比如索引(user_id, created_at, status)下面这些查询能用上索引WHERE user_id 1 WHERE user_id 1 AND created_at 2024-01-01 WHERE user_id 1 AND created_at 2024-01-01 AND status 1但下面这两种情况索引基本就废了WHERE created_at 2024-01-01 -- 没有 user_id最左列缺失 WHERE user_id 1 AND status 1 -- 跳过了 created_atstatus 条件无法走索引所以建联合索引的时候列的顺序要根据实际查询模式来定。我的经验是等值条件放在最左边范围条件放在中间排序字段放在最后。如果某个字段在查询里只是做范围过滤那就不能把它放在联合索引的第一位否则后面的列都用不上。2.2 覆盖索引与回表少一次 IO覆盖索引是一个特别实用的优化手段。所谓覆盖索引指的是查询需要的所有字段都包含在同一个索引里MySQL 可以直接从索引中返回结果不需要回表查数据行。我举个例子。假设订单表上有索引(user_id, created_at)执行这个查询SELECT user_id, created_at FROM orders WHERE user_id 12345 ORDER BY created_at DESC;因为user_id和created_at都在索引里MySQL 扫描索引就能拿到全部数据不需要再根据主键去聚簇索引里找那一整行。这样能显著减少磁盘 IO。如果改成SELECT user_id, created_at, amount FROM orders WHERE user_id 12345 ORDER BY created_at DESC;那么查到索引记录后还需要用主键回表读取amount字段性能就会打折扣。很多人有个误区觉得索引越多越好结果一张表建了十几个索引。实际上每个索引都要占磁盘空间每次插入更新都要维护索引。我处理过的案例里有张表索引占了几个 GB写入性能被拖垮。正确的做法是优先保证高频查询能覆盖低频查询宁可让它走临时索引也不要盲目建索引。2.3 索引失效的常见场景与应对索引建得没问题但 SQL 写法不对照样用不上索引。这里列几个我平时排查时第一眼就会看的场景。第一类是对索引列使用函数或表达式。比如WHERE DATE(created_at) 2024-01-01这种情况下 MySQL 必须对每一行的created_at都先计算一次DATE()索引就失效了。正确写法是改成范围条件WHERE created_at 2024-01-01 AND created_at 2024-01-02第二类是隐式类型转换。如果索引列是字符串类型但查询条件传的是数字MySQL 会在内部做类型转换导致索引失效。比如user_id是 varchar查询写成了WHERE user_id 12345这就会出问题。正确做法是写成WHERE user_id 12345。第三类是前导模糊查询。LIKE %关键字这种写法索引在这个条件上是用不上的因为无法确定匹配的起点。如果业务确实需要这种模糊匹配要么考虑全文索引要么就接受它必须扫描更多的数据。第四类是OR连接的条件。如果 OR 两边的字段只有一个有索引MySQL 很可能放弃索引转成全表扫描。可以考虑把 OR 拆成两个查询用UNION ALL合并每个分支都能走索引效率反而更高。3. 慢 SQL 改写几个高频场景的实战索引设计做好之后接下来就是 SQL 本身的写法。很多时候完全相同的查询只是写法不同性能差距能到一个数量级。这一章我会讲几个线上反复出现的高频场景。3.1 深分页OFFSET 越大越慢分页查询的深分页问题应该是所有业务系统都会遇到的。常见写法是SELECT order_id, amount FROM orders ORDER BY created_at DESC LIMIT 100000, 20;这个 SQL 的问题在于MySQL 需要先扫描前 100000 行再把它们丢掉最后只返回 20 行。扫描的行数随着分页深度线性增长到后面每页都会越来越慢。优化方案通常有两种。第一种是延迟关联先通过覆盖索引拿到主键再回表取数据SELECT o.order_id, o.amount FROM orders o INNER JOIN ( SELECT order_id FROM orders ORDER BY created_at DESC LIMIT 100000, 20 ) tmp ON o.order_id tmp.order_id;第二种是记录上一页最后一条数据的位置用位置来翻页SELECT order_id, amount FROM orders WHERE created_at 2024-01-01 00:00:00 ORDER BY created_at DESC LIMIT 20;这种方案适合业务上能接受“上一页翻下一页”的场景响应时间基本恒定不会随着页数增加而恶化。我在实际项目里遇到上百页的大列表基本都会改成这种写法效果立竿见影。3.2 IN、EXISTS 与 JOIN 的选择见过很多人在子查询和 JOIN 之间反复横跳其实在 MySQL 里并没有绝对的“哪个一定更快”关键要看优化器能不能把子查询改写成 JOIN以及数据量分布是什么样的。我个人的经验是如果关联字段上有索引而且数据量不大优先考虑 EXISTS 或 JOIN 配合索引的方式。IN适用于子查询结果集很小的情况比如查几千个 ID 列表如果子查询本身要全表扫描那就大概率是性能瓶颈。举一个例子查所有下过有效订单的用户SELECT u.user_id, u.name FROM users u WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.user_id u.user_id AND o.status paid );如果orders表的user_id上有索引这个 EXISTS 查询一般都能走嵌套循环性能比较稳定。一旦你发现某个子查询反复出现在慢日志里优先去看执行计划里有没有被改写成 JOIN如果没有就要考虑手动拆成两步先查子查询结果再查主表。3.3 避免无谓排序与临时表ORDER BY和GROUP BY是 SQL 调优里的两个大头。MySQL 在无法利用索引完成排序或分组时会额外创建临时表和文件排序这两个操作代价都不低Extra里的Using filesort和Using temporary就是在提醒你这一点。排序的优化思路很简单让排序字段能走索引。如果你经常按created_at排序并且过滤条件里有user_id那么建(user_id, created_at)联合索引MySQL 就能直接从索引里按顺序读数据完全不需要额外的排序操作。GROUP BY的情况要更谨慎一些尤其是按多个字段分组再加HAVING过滤的时候。我踩过的一个坑是对一个大表按用户分组统计订单金额SQL 写了 5 分钟都跑不出来EXPLAIN显示Using temporary; Using filesort。后来发现是因为HAVING里用了聚合函数优化器没法直接利用索引。解决办法是先用子查询把分组结果缩小再用HAVING过滤SELECT user_id, total_amount FROM ( SELECT user_id, SUM(amount) AS total_amount FROM orders WHERE created_at 2024-01-01 GROUP BY user_id ) tmp WHERE total_amount 1000;需要说明的是这个改写并不一定在所有场景下都更优关键还是要看执行计划和数据量我只是提供一个大致方向。3.4 COUNT 的优化策略COUNT(*)这种操作在 MyISAM 里会被特殊优化直接返回表的总行数但 MySQL 默认的 InnoDB 引擎没有这个特性必须扫描统计。如果只是想知道一张表有多少行其实没有特别好的办法只能看information_schema.tables的估算值。如果要统计满足条件的行数而且这个统计很频繁我建议直接维护一个统计表或者计数器在业务逻辑里更新。比如订单表每天的新增订单数完全可以在插入订单时同步更新一张统计表查询时直接读统计表几毫秒就出来了。实际上很多报表系统的设计思路都是这样先离线聚合查询时只读结果。还有一个容易忽略的点COUNT(1)、COUNT(*)和COUNT(具体字段)在实际执行计划里基本不会有性能差异真正需要关心的是统计范围和索引覆盖率。如果一张 500 万行的表统计某条件下的行数需要扫描 200 万行那再怎么优化 SQL 也就是从 2 秒变成 1.5 秒这种场景不是 SQL 写法能救的必须从业务架构上想办法。4. 系统层参数配置一个“不拖后腿”的数据库当 SQL 和索引都优化完性能还是不够时才轮到系统层参数。我见过不少团队把这个顺序反了上来就调参数结果 MySQL 在错误配置下跑了几个月问题越调越多。这一章讲的参数都是我在实际压测和线上故障处理中验证过有效果的按优先级说明。4.1 内存与 Bufferinnodb_buffer_pool_size 是重中之重InnoDB 的数据和索引都缓存在 buffer pool 里这个参数基本决定了数据库能“记住”多少数据。如果设置得过小MySQL 会频繁做磁盘读写再好的 SQL 也会被 IO 拖慢。通用的建议是设置为服务器物理内存的 60%-70%。比如一台 32G 内存的数据库服务器可以设置成 20G 左右。注意要预留操作系统的内存给文件缓存、连接线程、排序缓冲等使用不能全部分配给 MySQL。修改方式是在配置文件my.cnf的[mysqld]段下设置innodb_buffer_pool_size 20G innodb_buffer_pool_instances 8innodb_buffer_pool_instances表示把 buffer pool 拆成多少个实例多实例可以减少并发访问时的锁竞争。这个参数需要重启 MySQL 才能生效所以如果在线上环境最好提前规划好。我的判断方法是用SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read%;查看Innodb_buffer_pool_read_requests和Innodb_buffer_pool_reads前者是逻辑读请求数后者是真正访问物理磁盘的次数。如果物理读占逻辑读的比例超过 1%说明 buffer pool 可能太小或者缓存命中率不够理想。我踩过的一个实际教训是一台内存 64G 的机器buffer pool 默认只有 128M一个白天每秒有几百次物理读数据库响应状况非常差。调大到 40G 之后物理读比例直线下降。4.2 连接与 SQL Mode别让客户端和配置拖累查询连接数上限也是一个经常被忽略的瓶颈。max_connections默认值通常只有 151如果应用连接池开得比较大很容易打满。查看当前连接状态用SHOW STATUS LIKE Threads_connected;我见过一个非常典型的情况应用连接池设置了 200 个连接MySQL 的max_connections只有 151结果高峰期大量连接失败整个应用频繁重连数据库雪上加霜。合理的方式是让应用连接池的大小和数据库配置匹配同时留出余量给后台任务、监控脚本等使用。另外一个不常被提到但很重要的点是sql_mode。比如ONLY_FULL_GROUP_BY如果没开GROUP BY时可以选择任意字段语法更宽松但很容易产生语义错误而且还可能导致 MySQL 选择了不优化的执行计划。我通常建议保持STRICT_TRANS_TABLES, NO_ENGINE_SUBSTITUTION这类相对严格但不过分的配置避免因为 SQL 不规范而引发隐藏的性能问题。4.3 监控三件套从系统指标反推 SQL 问题在调优过程中光看数据库内部的指标还不够还要结合系统层的指标来看。我平时在 Linux 服务器上最常用的三个监控工具是top、iostat和vmstat有人把它们叫作系统排查三件套。top看 CPU 和内存负载iostat -x 1看磁盘读写和 I/O 等待vmstat 1看上下文切换和 CPU 状态。有一次 MySQL 线上性能告警EXPLAIN显示 SQL 都走了索引数据库内部指标也都正常最后用iostat一看磁盘%util接近 100%才定位到问题是磁盘 IO 饱和根本瓶颈在硬件层。系统监控的意义在于它让你知道优化方向是朝哪个层面发力。如果 CPU 高重点查 SQL 逻辑和索引如果磁盘 IO 高优先考虑减少扫描行数、扩大 buffer pool如果是内存不足再考虑参数调整。顺序错了容易做无用功。5. 常见问题与排查技巧实录调优经验是靠一个个问题堆出来的。这里我把平时最容易遇到的几个“看起来很正常但性能就是上不去”的场景以及对应的排查思路整理出来。5.1 走了索引还是慢有一种情况特别让人头疼EXPLAIN里清楚写着key用了某个索引type是ref但 SQL 还是慢。这种问题通常出在回表次数太多上。比如一张表有索引(user_id)查询WHERE user_id 12345 ORDER BY created_at DESC LIMIT 20。MySQL 会通过索引找到该用户的所有记录然后按主键回表读取整行再在内存里排序取 20 条。如果这个用户有 10 万条订单记录即使走了索引也有 10 万次回表性能自然快不了。解决办法是在索引里把排序字段和查询字段都覆盖进去。建联合索引(user_id, created_at, order_id, amount, status)之类的覆盖索引让排序和查询都在索引里完成。这里我建议多观察执行计划里的Extra字段如果出现Using index condition或Using filesort就要考虑调整索引结构。5.2 统计信息不准导致执行计划偏差MySQL 优化器在选择执行计划时依赖表上的统计信息来估算扫描行数。如果统计信息过时就会做出错误的判断比如明明该走索引却选择了全表扫描。这时可以执行ANALYZE TABLE orders;重新收集统计信息。在我的经验里大表在高频增删改之后统计信息偏差造成慢 SQL 的情况并不少见。之前排查过一个案例某张表实际只有 20 万行活跃数据但因为大量逻辑删除统计信息显示 500 万行优化器全程优先全表扫描数据量不大却慢得离谱。重新收集统计信息后问题立刻解决。一个额外的技巧是对于大表尽量使用online DDL进行索引变更避免在业务高峰期直接ALTER TABLE这是运维层面的经验。5.3 索引过多拖累写入性能索引不是装饰品每多一个索引写入时就要多维护一份 B 树。我遇到过一张业务表有 14 个索引写入吞吐量上不去后来排查到有人为了几个低频查询疯狂加索引最终导致正常业务写入变慢。解决办法是对索引做瘦身。我常用的检查方式是查询information_schema.statistics找出哪些索引从没有被查询用到。也可以用performance_schema.table_io_waits_summary_by_index_usage看索引的访问次数。那些长时间没用过且与唯一约束无关的索引可以和业务方确认后直接删除。这里我有一个小习惯每次新建索引之前先问自己三个问题——这个查询多久跑一次能不能用现有索引覆盖索引列的区分度高不高如果三个问题里有两个不理想就不要急着建索引。5.4 SQL 安全问题防注入也是调优的一部分SQL 注入问题之所以能跟调优扯上关系是因为不安全写法很容易导致查询计划被打乱甚至出现一批恶意构造的 SQL 把数据库拖垮。比如有些代码写成字符串拼接String sql SELECT * FROM users WHERE name name ;只要用户在输入框里传一个带引号的值就能改变 SQL 语义。我在排查一个线上故障时就是因为有人用这种方式拼接了一个查询条件导致 MySQL 生成了一个巨大的笛卡尔积执行计划整个实例几乎卡死。解决思路是使用预编译语句也就是项目里常用的PreparedStatementString sql SELECT * FROM users WHERE name ?; PreparedStatement ps conn.prepareStatement(sql); ps.setString(1, name);预编译语句不仅能让 SQL 写法更安全还能让 MySQL 端复用执行计划减少 SQL 解析开销这本身就是一种调优手段。除了代码层面数据库账号也应该遵循最小权限原则查询账号只给SELECT权限不要给DROP、ALTER之类的权限避免误操作或者更严重的问题。6. 调优顺序与习惯养成最后分享一套我实际工作中沉淀下来的调优顺序这套顺序帮我避免了很多弯路。先看业务确认这条 SQL 是否还有存在的必要能不能少查一次能不能用缓存。很多时候业务层面去掉一个无用的查询比在数据库层面死磕半天的效果要好得多。然后是数据结合执行计划里的rows和实际数据量做对比判断是不是统计信息失真。接着看索引是否缺索引索引列的顺序是否合理能不能覆盖查询。然后再看 SQL 写法能不能避免回表、能不能避免临时表、分页有没有写深。最后才轮到系统参数buffer pool、连接数、刷盘策略。在实际项目里我见过最坑的情况不是索引失效而是大家一开始就急着去调整参数把服务器配置改得乱七八糟数据库性能反而更不稳定。所以我还是想再强调一遍SQL 调优SQL 和索引永远是最前面的参数调优只是收尾动作。这个内容后续还可以扩展的方向有两个。一个是把调优场景自动化把慢查询日志采集、执行计划分析、索引检查集成到一套脚本里每天定时报告形成 SQL 性能看板。另一个是引入压测工具做对比验证比如用 TPC-H 这类测试数据集来检验索引和 SQL 改写的实际效果。不管选哪条路核心思路都是一样的先定位再优化最后验证形成闭环。每次改完 SQL 或索引记得留一份 before/after 的响应时间记录这是判断改动是否有用的唯一证据。
MySQL SQL调优实战：从慢查询日志到索引设计，解决线上性能瓶颈
先说个真实场景。上个月线上订单表到了千万级一个按用户查近期订单的接口响应时间从 100ms 一路飙到 1.5s数据库 CPU 偶尔直接打满。同事第一反应是服务器配置不够加内存换 SSD结果第二天又崩了。后来把慢查询日志打开一条 SQL 扫了全表问题根本不在硬件。这就是 MySQL 里 SQL 调优最典型的起点——大部分性能问题的根因就藏在 SQL 写法、索引设计和执行计划里而不是服务器配置上。这篇内容我会按照实际排查的顺序来写怎么发现问题、怎么读懂执行计划、怎么写索引、怎么改 SQL配合系统层的参数配置和调优工具覆盖调优路上最常踩的坑。不管你是刚接触 MySQL 的新人还是被慢 SQL 折磨过几次的开发按这套思路走下来基本能解决绝大多数线上问题。1. 先定位问题再谈调优很多人在 SQL 调优时容易犯一个错误一上来就翻参数配置调 buffer pool、改刷盘策略结果 SQL 还是慢。调优的第一步永远不是改东西而是把问题找出来。MySQL 本身提供了完整的诊断链路从慢查询日志到执行计划再到 profiling一层一层往下挖就行。1.1 慢查询日志是第一现场慢查询日志是 MySQL 记录执行时间超过阈值的 SQL 的日志文件也是排查慢 SQL 的首要入口。默认情况下这个功能是关闭的需要手动开启我用得最多的方式是直接在 MySQL 命令行执行# 查看当前慢查询日志状态 SHOW VARIABLES LIKE slow_query_log; # 开启慢查询日志并设置阈值我这里设为2秒测试环境可以设得更低 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 2; SET GLOBAL slow_query_log_file /var/log/mysql/slow.log;注意long_query_time的单位是秒而且这个参数对已经开启的会话不生效测试时需要重新连接或者另开一个会话。实际生产环境我平时会设为 1 秒再配合log_queries_not_using_indexes ON记录没有走索引的 SQL这样连扫描全表的问题也能暴露出来。开启之后线上跑一段时间用mysqldumpslow工具把慢日志汇总一下就能快速找出哪些 SQL 出现频率高、总耗时最长。命令大概是这样的mysqldumpslow -s at -t 10 /var/log/mysql/slow.log-s at表示按平均耗时排序-t 10表示只看前 10 条。这样能快速锁定最需要处理的目标而不是在几千条日志里大海捞针。1.2 EXPLAIN读懂 MySQL 的“路线图”拿到慢 SQL 之后下一步就是看执行计划。执行计划就是 MySQL 优化器生成的查询执行方案相当于导航软件给出的路线图。用EXPLAIN加在 SQL 前面就能看到这张图比如EXPLAIN SELECT order_id, amount FROM orders WHERE user_id 12345 ORDER BY created_at DESC LIMIT 20;输出结果里最关键的是这几个字段type访问类型从好到差依次是system、const、eq_ref、ref、range、index、ALL。看到ALL意味着全表扫描这是最需要警惕的。key实际用到的索引如果为NULL说明没有使用索引。rows预估扫描的行数这个值越小越好。如果预估 10 万行实际返回 20 条说明过滤性很差需要排查索引或 SQL 写法。Extra经常能看到Using filesort文件排序、Using temporary使用临时表、Using index覆盖索引等信息这些信息直接决定后续优化方向。我习惯每次看执行计划的时候把注意力放在type和rows上这两个字段最直观。如果type是ALL先别急着改 SQL很可能就是缺少合适的索引如果type是ref但rows特别大那要考虑索引列的选择性是不是够好。1.3 用 profiling 量化耗时分布EXPLAIN能告诉你计划长什么样但要知道时间到底花在哪个阶段就得靠 profiling。MySQL 提供了SHOW PROFILES命令可以查看一条 SQL 在服务器端各个阶段的耗时。SET profiling ON; -- 执行你的慢 SQL SELECT * FROM orders WHERE user_id 12345; SHOW PROFILES; SHOW PROFILE FOR QUERY 1;输出里会展示Sending data、Sorting result、Creating sort index等阶段分别耗时多少。这里我分享一个实际排查经验有一次一条查询EXPLAIN显示走的是索引但响应还是要 300ms用 profiling 一看发现大部分时间花在Sending data上说明通过网络传输的数据量太大根本问题其实是查询返回了太多用不到的字段这也是为什么我一直强调尽量避免SELECT *。2. 索引设计决定 SQL 性能上限调优做到后面你会发现80% 的 SQL 性能问题都出在索引上。索引不是建了就完事建错索引比不建索引更可怕因为 MySQL 优化器可能被误导走了错误的执行计划。这一章我会把索引设计里最核心的几个问题讲清楚。2.1 最左前缀原则联合索引的黄金法则联合索引是 MySQL 里最常用也最容易用错的索引。很多人建了(a, b, c)联合索引就以为任何查询都能走索引结果 SQL 还是全表扫描其实就是没搞懂最左前缀原则。最左前缀原则的定义是联合索引的生效前提是查询条件从索引的最左列开始并且不能跳过中间的列。比如索引(user_id, created_at, status)下面这些查询能用上索引WHERE user_id 1 WHERE user_id 1 AND created_at 2024-01-01 WHERE user_id 1 AND created_at 2024-01-01 AND status 1但下面这两种情况索引基本就废了WHERE created_at 2024-01-01 -- 没有 user_id最左列缺失 WHERE user_id 1 AND status 1 -- 跳过了 created_atstatus 条件无法走索引所以建联合索引的时候列的顺序要根据实际查询模式来定。我的经验是等值条件放在最左边范围条件放在中间排序字段放在最后。如果某个字段在查询里只是做范围过滤那就不能把它放在联合索引的第一位否则后面的列都用不上。2.2 覆盖索引与回表少一次 IO覆盖索引是一个特别实用的优化手段。所谓覆盖索引指的是查询需要的所有字段都包含在同一个索引里MySQL 可以直接从索引中返回结果不需要回表查数据行。我举个例子。假设订单表上有索引(user_id, created_at)执行这个查询SELECT user_id, created_at FROM orders WHERE user_id 12345 ORDER BY created_at DESC;因为user_id和created_at都在索引里MySQL 扫描索引就能拿到全部数据不需要再根据主键去聚簇索引里找那一整行。这样能显著减少磁盘 IO。如果改成SELECT user_id, created_at, amount FROM orders WHERE user_id 12345 ORDER BY created_at DESC;那么查到索引记录后还需要用主键回表读取amount字段性能就会打折扣。很多人有个误区觉得索引越多越好结果一张表建了十几个索引。实际上每个索引都要占磁盘空间每次插入更新都要维护索引。我处理过的案例里有张表索引占了几个 GB写入性能被拖垮。正确的做法是优先保证高频查询能覆盖低频查询宁可让它走临时索引也不要盲目建索引。2.3 索引失效的常见场景与应对索引建得没问题但 SQL 写法不对照样用不上索引。这里列几个我平时排查时第一眼就会看的场景。第一类是对索引列使用函数或表达式。比如WHERE DATE(created_at) 2024-01-01这种情况下 MySQL 必须对每一行的created_at都先计算一次DATE()索引就失效了。正确写法是改成范围条件WHERE created_at 2024-01-01 AND created_at 2024-01-02第二类是隐式类型转换。如果索引列是字符串类型但查询条件传的是数字MySQL 会在内部做类型转换导致索引失效。比如user_id是 varchar查询写成了WHERE user_id 12345这就会出问题。正确做法是写成WHERE user_id 12345。第三类是前导模糊查询。LIKE %关键字这种写法索引在这个条件上是用不上的因为无法确定匹配的起点。如果业务确实需要这种模糊匹配要么考虑全文索引要么就接受它必须扫描更多的数据。第四类是OR连接的条件。如果 OR 两边的字段只有一个有索引MySQL 很可能放弃索引转成全表扫描。可以考虑把 OR 拆成两个查询用UNION ALL合并每个分支都能走索引效率反而更高。3. 慢 SQL 改写几个高频场景的实战索引设计做好之后接下来就是 SQL 本身的写法。很多时候完全相同的查询只是写法不同性能差距能到一个数量级。这一章我会讲几个线上反复出现的高频场景。3.1 深分页OFFSET 越大越慢分页查询的深分页问题应该是所有业务系统都会遇到的。常见写法是SELECT order_id, amount FROM orders ORDER BY created_at DESC LIMIT 100000, 20;这个 SQL 的问题在于MySQL 需要先扫描前 100000 行再把它们丢掉最后只返回 20 行。扫描的行数随着分页深度线性增长到后面每页都会越来越慢。优化方案通常有两种。第一种是延迟关联先通过覆盖索引拿到主键再回表取数据SELECT o.order_id, o.amount FROM orders o INNER JOIN ( SELECT order_id FROM orders ORDER BY created_at DESC LIMIT 100000, 20 ) tmp ON o.order_id tmp.order_id;第二种是记录上一页最后一条数据的位置用位置来翻页SELECT order_id, amount FROM orders WHERE created_at 2024-01-01 00:00:00 ORDER BY created_at DESC LIMIT 20;这种方案适合业务上能接受“上一页翻下一页”的场景响应时间基本恒定不会随着页数增加而恶化。我在实际项目里遇到上百页的大列表基本都会改成这种写法效果立竿见影。3.2 IN、EXISTS 与 JOIN 的选择见过很多人在子查询和 JOIN 之间反复横跳其实在 MySQL 里并没有绝对的“哪个一定更快”关键要看优化器能不能把子查询改写成 JOIN以及数据量分布是什么样的。我个人的经验是如果关联字段上有索引而且数据量不大优先考虑 EXISTS 或 JOIN 配合索引的方式。IN适用于子查询结果集很小的情况比如查几千个 ID 列表如果子查询本身要全表扫描那就大概率是性能瓶颈。举一个例子查所有下过有效订单的用户SELECT u.user_id, u.name FROM users u WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.user_id u.user_id AND o.status paid );如果orders表的user_id上有索引这个 EXISTS 查询一般都能走嵌套循环性能比较稳定。一旦你发现某个子查询反复出现在慢日志里优先去看执行计划里有没有被改写成 JOIN如果没有就要考虑手动拆成两步先查子查询结果再查主表。3.3 避免无谓排序与临时表ORDER BY和GROUP BY是 SQL 调优里的两个大头。MySQL 在无法利用索引完成排序或分组时会额外创建临时表和文件排序这两个操作代价都不低Extra里的Using filesort和Using temporary就是在提醒你这一点。排序的优化思路很简单让排序字段能走索引。如果你经常按created_at排序并且过滤条件里有user_id那么建(user_id, created_at)联合索引MySQL 就能直接从索引里按顺序读数据完全不需要额外的排序操作。GROUP BY的情况要更谨慎一些尤其是按多个字段分组再加HAVING过滤的时候。我踩过的一个坑是对一个大表按用户分组统计订单金额SQL 写了 5 分钟都跑不出来EXPLAIN显示Using temporary; Using filesort。后来发现是因为HAVING里用了聚合函数优化器没法直接利用索引。解决办法是先用子查询把分组结果缩小再用HAVING过滤SELECT user_id, total_amount FROM ( SELECT user_id, SUM(amount) AS total_amount FROM orders WHERE created_at 2024-01-01 GROUP BY user_id ) tmp WHERE total_amount 1000;需要说明的是这个改写并不一定在所有场景下都更优关键还是要看执行计划和数据量我只是提供一个大致方向。3.4 COUNT 的优化策略COUNT(*)这种操作在 MyISAM 里会被特殊优化直接返回表的总行数但 MySQL 默认的 InnoDB 引擎没有这个特性必须扫描统计。如果只是想知道一张表有多少行其实没有特别好的办法只能看information_schema.tables的估算值。如果要统计满足条件的行数而且这个统计很频繁我建议直接维护一个统计表或者计数器在业务逻辑里更新。比如订单表每天的新增订单数完全可以在插入订单时同步更新一张统计表查询时直接读统计表几毫秒就出来了。实际上很多报表系统的设计思路都是这样先离线聚合查询时只读结果。还有一个容易忽略的点COUNT(1)、COUNT(*)和COUNT(具体字段)在实际执行计划里基本不会有性能差异真正需要关心的是统计范围和索引覆盖率。如果一张 500 万行的表统计某条件下的行数需要扫描 200 万行那再怎么优化 SQL 也就是从 2 秒变成 1.5 秒这种场景不是 SQL 写法能救的必须从业务架构上想办法。4. 系统层参数配置一个“不拖后腿”的数据库当 SQL 和索引都优化完性能还是不够时才轮到系统层参数。我见过不少团队把这个顺序反了上来就调参数结果 MySQL 在错误配置下跑了几个月问题越调越多。这一章讲的参数都是我在实际压测和线上故障处理中验证过有效果的按优先级说明。4.1 内存与 Bufferinnodb_buffer_pool_size 是重中之重InnoDB 的数据和索引都缓存在 buffer pool 里这个参数基本决定了数据库能“记住”多少数据。如果设置得过小MySQL 会频繁做磁盘读写再好的 SQL 也会被 IO 拖慢。通用的建议是设置为服务器物理内存的 60%-70%。比如一台 32G 内存的数据库服务器可以设置成 20G 左右。注意要预留操作系统的内存给文件缓存、连接线程、排序缓冲等使用不能全部分配给 MySQL。修改方式是在配置文件my.cnf的[mysqld]段下设置innodb_buffer_pool_size 20G innodb_buffer_pool_instances 8innodb_buffer_pool_instances表示把 buffer pool 拆成多少个实例多实例可以减少并发访问时的锁竞争。这个参数需要重启 MySQL 才能生效所以如果在线上环境最好提前规划好。我的判断方法是用SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read%;查看Innodb_buffer_pool_read_requests和Innodb_buffer_pool_reads前者是逻辑读请求数后者是真正访问物理磁盘的次数。如果物理读占逻辑读的比例超过 1%说明 buffer pool 可能太小或者缓存命中率不够理想。我踩过的一个实际教训是一台内存 64G 的机器buffer pool 默认只有 128M一个白天每秒有几百次物理读数据库响应状况非常差。调大到 40G 之后物理读比例直线下降。4.2 连接与 SQL Mode别让客户端和配置拖累查询连接数上限也是一个经常被忽略的瓶颈。max_connections默认值通常只有 151如果应用连接池开得比较大很容易打满。查看当前连接状态用SHOW STATUS LIKE Threads_connected;我见过一个非常典型的情况应用连接池设置了 200 个连接MySQL 的max_connections只有 151结果高峰期大量连接失败整个应用频繁重连数据库雪上加霜。合理的方式是让应用连接池的大小和数据库配置匹配同时留出余量给后台任务、监控脚本等使用。另外一个不常被提到但很重要的点是sql_mode。比如ONLY_FULL_GROUP_BY如果没开GROUP BY时可以选择任意字段语法更宽松但很容易产生语义错误而且还可能导致 MySQL 选择了不优化的执行计划。我通常建议保持STRICT_TRANS_TABLES, NO_ENGINE_SUBSTITUTION这类相对严格但不过分的配置避免因为 SQL 不规范而引发隐藏的性能问题。4.3 监控三件套从系统指标反推 SQL 问题在调优过程中光看数据库内部的指标还不够还要结合系统层的指标来看。我平时在 Linux 服务器上最常用的三个监控工具是top、iostat和vmstat有人把它们叫作系统排查三件套。top看 CPU 和内存负载iostat -x 1看磁盘读写和 I/O 等待vmstat 1看上下文切换和 CPU 状态。有一次 MySQL 线上性能告警EXPLAIN显示 SQL 都走了索引数据库内部指标也都正常最后用iostat一看磁盘%util接近 100%才定位到问题是磁盘 IO 饱和根本瓶颈在硬件层。系统监控的意义在于它让你知道优化方向是朝哪个层面发力。如果 CPU 高重点查 SQL 逻辑和索引如果磁盘 IO 高优先考虑减少扫描行数、扩大 buffer pool如果是内存不足再考虑参数调整。顺序错了容易做无用功。5. 常见问题与排查技巧实录调优经验是靠一个个问题堆出来的。这里我把平时最容易遇到的几个“看起来很正常但性能就是上不去”的场景以及对应的排查思路整理出来。5.1 走了索引还是慢有一种情况特别让人头疼EXPLAIN里清楚写着key用了某个索引type是ref但 SQL 还是慢。这种问题通常出在回表次数太多上。比如一张表有索引(user_id)查询WHERE user_id 12345 ORDER BY created_at DESC LIMIT 20。MySQL 会通过索引找到该用户的所有记录然后按主键回表读取整行再在内存里排序取 20 条。如果这个用户有 10 万条订单记录即使走了索引也有 10 万次回表性能自然快不了。解决办法是在索引里把排序字段和查询字段都覆盖进去。建联合索引(user_id, created_at, order_id, amount, status)之类的覆盖索引让排序和查询都在索引里完成。这里我建议多观察执行计划里的Extra字段如果出现Using index condition或Using filesort就要考虑调整索引结构。5.2 统计信息不准导致执行计划偏差MySQL 优化器在选择执行计划时依赖表上的统计信息来估算扫描行数。如果统计信息过时就会做出错误的判断比如明明该走索引却选择了全表扫描。这时可以执行ANALYZE TABLE orders;重新收集统计信息。在我的经验里大表在高频增删改之后统计信息偏差造成慢 SQL 的情况并不少见。之前排查过一个案例某张表实际只有 20 万行活跃数据但因为大量逻辑删除统计信息显示 500 万行优化器全程优先全表扫描数据量不大却慢得离谱。重新收集统计信息后问题立刻解决。一个额外的技巧是对于大表尽量使用online DDL进行索引变更避免在业务高峰期直接ALTER TABLE这是运维层面的经验。5.3 索引过多拖累写入性能索引不是装饰品每多一个索引写入时就要多维护一份 B 树。我遇到过一张业务表有 14 个索引写入吞吐量上不去后来排查到有人为了几个低频查询疯狂加索引最终导致正常业务写入变慢。解决办法是对索引做瘦身。我常用的检查方式是查询information_schema.statistics找出哪些索引从没有被查询用到。也可以用performance_schema.table_io_waits_summary_by_index_usage看索引的访问次数。那些长时间没用过且与唯一约束无关的索引可以和业务方确认后直接删除。这里我有一个小习惯每次新建索引之前先问自己三个问题——这个查询多久跑一次能不能用现有索引覆盖索引列的区分度高不高如果三个问题里有两个不理想就不要急着建索引。5.4 SQL 安全问题防注入也是调优的一部分SQL 注入问题之所以能跟调优扯上关系是因为不安全写法很容易导致查询计划被打乱甚至出现一批恶意构造的 SQL 把数据库拖垮。比如有些代码写成字符串拼接String sql SELECT * FROM users WHERE name name ;只要用户在输入框里传一个带引号的值就能改变 SQL 语义。我在排查一个线上故障时就是因为有人用这种方式拼接了一个查询条件导致 MySQL 生成了一个巨大的笛卡尔积执行计划整个实例几乎卡死。解决思路是使用预编译语句也就是项目里常用的PreparedStatementString sql SELECT * FROM users WHERE name ?; PreparedStatement ps conn.prepareStatement(sql); ps.setString(1, name);预编译语句不仅能让 SQL 写法更安全还能让 MySQL 端复用执行计划减少 SQL 解析开销这本身就是一种调优手段。除了代码层面数据库账号也应该遵循最小权限原则查询账号只给SELECT权限不要给DROP、ALTER之类的权限避免误操作或者更严重的问题。6. 调优顺序与习惯养成最后分享一套我实际工作中沉淀下来的调优顺序这套顺序帮我避免了很多弯路。先看业务确认这条 SQL 是否还有存在的必要能不能少查一次能不能用缓存。很多时候业务层面去掉一个无用的查询比在数据库层面死磕半天的效果要好得多。然后是数据结合执行计划里的rows和实际数据量做对比判断是不是统计信息失真。接着看索引是否缺索引索引列的顺序是否合理能不能覆盖查询。然后再看 SQL 写法能不能避免回表、能不能避免临时表、分页有没有写深。最后才轮到系统参数buffer pool、连接数、刷盘策略。在实际项目里我见过最坑的情况不是索引失效而是大家一开始就急着去调整参数把服务器配置改得乱七八糟数据库性能反而更不稳定。所以我还是想再强调一遍SQL 调优SQL 和索引永远是最前面的参数调优只是收尾动作。这个内容后续还可以扩展的方向有两个。一个是把调优场景自动化把慢查询日志采集、执行计划分析、索引检查集成到一套脚本里每天定时报告形成 SQL 性能看板。另一个是引入压测工具做对比验证比如用 TPC-H 这类测试数据集来检验索引和 SQL 改写的实际效果。不管选哪条路核心思路都是一样的先定位再优化最后验证形成闭环。每次改完 SQL 或索引记得留一份 before/after 的响应时间记录这是判断改动是否有用的唯一证据。
