1. 一开场就泼盆冷水调优不是改参数而是先找到那个“慢”先说个我自己的经历。前两年接了个内部系统的MySQL调优任务最典型的一个业务场景是订单查询列表某个页面接口的响应时长平均在1.2秒左右线上偶尔能飙到3秒以上。当时我的第一反应跟很多刚接触调优的同学一样上去就准备调innodb_buffer_pool_size、调连接数、改日志策略。结果调了半天数据页命中率是上去了但接口该慢还是慢最后羞愧地回滚了配置。真正让我把问题解决的反而是老老实实把慢查询日志打开然后把那条SQL抓出来用EXPLAIN看了一眼。不看不知道问题根本不在数据库的“缓存”或“参数”而是那条SQL在单表300万行数据上走了一个全表扫描每次请求都要处理将近一整张表的数据量。用大白话讲参数调优像是给车换更好的轮胎可你的车压根儿不是在飙高速而是在一条堵死的胡同里挪。轮胎换了也没用得先把路选对。这也是为什么我始终觉得MySQL调优最核心的功夫不是背参数而是形成一套从“现象”到“根因”的排查思维。这篇文章我就顺着自己踩过的坑聊聊调优里真正有用的那几件事怎么定位慢SQL、怎么读懂执行计划、索引设计有哪些说到做不到的细节、InnoDB的锁和事务为什么会影响并发、参数到底怎么设置才叫合适。我尽可能用实际案例串起来讲尽量不抛那种“复制粘贴就知道”的配置清单。2. 从现象到根因如何快速找出拖垮性能的那条SQL2.1 监控指标很多但你先看这四个很多新手一看MySQL监控面板就头大QPS、TPS、线程数、连接数、缓冲池命中率、临时表数量……最后一个都没记住。我自己的习惯是分优先级看先回答三个问题慢了多久是偶发还是持续慢了是全部接口还是个别接口如果是偶发性的优先怀疑锁等待、连接池打满、大事务提交带来的瞬时IO抖动如果是持续性的优先怀疑索引缺失、SQL写法差、数据增长导致的查询计划偏移如果是个别接口慢那么恭喜你99%的问题就在那一条或几条SQL身上直接抓慢查询日志就行。还有一个容易被忽略的点被平均掩盖的慢请求。平均响应时间200ms看着挺好但你要去看95分位或99分位。有时候9个请求只要50ms第10个请求要2秒平均下来250ms系统看着“稳定”实际用户体验已经烂透了。所以在监控上我一般会同时关注最大耗时和慢查询次数而不是只看平均耗时。2.2 慢查询日志的正确打开方式慢查询日志是定位SQL性能问题的第一手段这个基本是共识了。但怎么配置很多人其实没完全用对。# 在MySQL配置文件my.cnf的[mysqld]段下 slow_query_log 1 slow_query_log_file /var/log/mysql/mysql-slow.log long_query_time 1 log_queries_not_using_indexes 1这里有个很小的细节long_query_time的单位是秒最小可以设置到0但如果你设成0那等于把每一条查询都记录下来日志文件会爆炸式增长反而干扰排查。我通常的建议是先设成1秒跑一两天看全貌如果业务本身比较轻量可以再调整到0.5秒甚至更低。还有一个坑是log_queries_not_using_indexes这个参数。它的本意是记录那些没走索引的查询哪怕执行时间很快。这正是我们需要的——有些SQL看着只要50ms但它每执行一次就全表扫描一遍等数据量再翻一倍就是灾难。不过在MySQL 5.7以上版本开启这个参数后日志量可能很大因为它会把很多“用了索引但索引效率很低”的查询也给你记下来需要耐心过滤。我通常的搭配是先开1秒阈值跑一周同时借助pt-query-digest这类工具做聚合分析按总执行时间、平均执行时间、扫描行数排序把真正值得优化的SQL挑出来。注意pt-query-digest是目前社区里比较常用的慢日志分析工具需要单独安装。如果你不方便装工具也可以直接对着慢日志里的# Query_time字段和# Rows_examined字段人工排序但这在有大量慢SQL的业务里效率很低。另外从MySQL 8.0开始慢日志字段整体更规范支持mysqldumpslow -s at这类方式聚合查看至少比裸看日志强一点。实际上到现在我依然保持着每周抽看一下慢日志的习惯。不是为了检查某一条SQL而是为了观察趋势本周慢查询数量相对于上周是涨了还是降了如果是持续上涨那就预示着某些表的索引命中率在下滑或者数据量在悄悄变大。等到用户投诉“系统变卡了”再去看日志永远是被动的。2.3 定位优先级硬件排查先放一边有些同行一遇到慢查询第一反应是去看服务器CPU、磁盘IO、内存。不是说这些不能看而是它们往往只是“果”不是“因”。一条全表扫描的SQL跑起来CPU和IO当然会飙高。你先人肉扛了一波硬件扩容看似解决了实际上SQL没变一个月后数据量涨了问题照旧。所以我把排查顺序固定在这样一条链路里抓慢SQL慢日志 / 性能监控平台分析SQL写法与表结构联合查询是否合理、子查询有没有必要、数据类型是否能用上索引用EXPLAIN看执行计划评估是SQL层优化还是索引层优化还是业务层改造最后才轮得到参数调整和硬件升级。这套顺序我用了很久从来没漏判过。有一个形象的类比厨房出菜慢你先看是哪道菜做得久再看是缺食材还是灶台火候不够最后才是考虑要不要多请一个厨师。很多人一上来就“多请厨师”也就是加服务器配置菜还是那道难做的菜效率怎么可能真正起飞。3. 拆解执行计划EXPLAIN里的每个字都是线索3.1 不要只看“用了索引”就欢呼用EXPLAIN看SQL执行计划是MySQL调优里最基本功的一步。最直接的办法就是把SQL前面加上EXPLAIN比如EXPLAIN SELECT order_id, user_id, amount FROM orders WHERE user_id 12345 AND status 1 ORDER BY create_time DESC LIMIT 10;执行之后会得到一张表里面有id、select_type、table、type、possible_keys、key、key_len、ref、rows、filtered、Extra这些字段。新手最容易犯的错是看到key列有内容也就是“用了索引”就觉得万事大吉。真相远没这么简单。key列只是说明MySQL在优化阶段选择了某个索引但它可能是选了主键索引、普通二级索引也可能在Extra里赫然写着Using temporary或Using filesort。如果一张300万行的表你在type列看到的是ALL那想都不用想全表扫描如果看到index虽然比ALL好一点但通常意味着它在扫描整个索引树代价依然不小。我调优时有一个粗读顺序先把type和Extra这两列放在优先级最高的位置type为ALL无条件全表扫描必须优化优先看能不能加索引或改查询条件type为index扫描了整个索引树比ALL好但当数据量大时也可能很慢type为range索引范围扫描通常是WHERE条件里用了范围查询比如、、BETWEEN这种往往可接受type为ref或eq_ref通过索引等值匹配是常见的高效访问方式type为const通过主键或唯一索引定位到唯一行理论上是最高效的访问方式之一。Extra里最值得注意的是Using filesort、Using temporary和Using where。前两者一旦出现基本说明这条SQL在排序或分组时把结果集拉到了内存或磁盘临时表里处理数据量一大性能直接雪崩。Using where则是在存储引擎返回记录后Server层又做了一层过滤虽然常见但在超大结果集下也值得警惕。3.2 rows字段是怎么“骗人”的rows字段描述的是MySQL估算需要扫描的行数。它是一个估算值不是精确值。为什么不精确因为MySQL的优化器依赖表统计信息和索引基数Cardinality来做估算而统计信息的更新不是即时的尤其在频繁增删改的表上统计信息滞后很常见。这就导致一个现象你在rows看到“只有50行”但实际SQL跑了500毫秒甚至更久。这种情况大概率是统计信息失真导致优化器选错了索引。解决方案也不复杂可以用ANALYZE TABLE手动更新统计信息。ANALYZE TABLE orders;另外如果你发现某张表的索引基数长期不准那就要反思表的设计了是不是写入量太大、更新太频繁统计信息根本跟不飞这时候可以考虑在低峰期定时跑ANALYZE TABLE。我自己就在某套库上用过这种定时任务效果立竿见影。原来一条要跑800ms的SQL在更新统计信息后优化器选对了索引耗时直接降到50ms以内。遇到这种情况真不用改SQL问题就在“选择”上。3.3 一个经典的联合索引案例为了让大家有更直观的感受我拿一个真实改过的SQL来做全流程拆解。表结构大致是这样字段名做了脱敏替换结构不变CREATE TABLE order_record ( id BIGINT PRIMARY KEY 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, pay_time DATETIME DEFAULT NULL, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_user_time (user_id, create_time), KEY idx_status (status) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;业务需求是查询某个用户最近30天的订单列表并按照下单时间倒序。起初的SQL写法非常直接SELECT id, order_no, status, amount, pay_time, create_time FROM order_record WHERE user_id 12345 AND create_time DATE_SUB(NOW(), INTERVAL 30 DAY) ORDER BY create_time DESC LIMIT 20;我最初设计的索引是idx_user_time也就是(user_id, create_time)理论上这个索引既能等值匹配user_id又能范围匹配create_time看起来天衣无缝。但实际EXPLAIN的结果里出现了Using filesort为什么因为联合索引的顺序是user_id在前、create_time在后查询时用户用等值条件定位到user_id 12345的所有记录这些记录在索引内部按create_time有序排列所以理论上ORDER BY create_time DESC可以直接利用索引顺序不需要排序。但问题出在LIMIT和WHERE条件的配合上MySQL优化器在执行时它需要在内存里扫描所有符合user_id和create_time范围的数据行然后尽可能把符合条件的数据剔重后再排序。这个过程中的一个隐藏代价是create_time作为二级索引的第二个字段在范围条件下没办法像等值条件那样直接定位最小化区域优化器最终选择了自己排序。解决方案有两个我都试过效果都不错方案一把索引进化成(user_id, create_time)在SQL层面让排序和范围都能压到索引里。比如把范围条件改成先按user_id等值定位再限定一个稍微宽松的时间范围让MySQL能通过索引拿到连续有序的数据段避免额外的排序步骤。SSD和内存环境下Using filesort在小数据集上不明显但300万行数据配合分页慢起来真的是指数级增长。方案二直接为排序字段单独建索引SQL改为SELECT id, order_no, status, amount, pay_time, create_time FROM order_record FORCE INDEX (idx_user_time) WHERE user_id 12345 AND create_time 2024-01-01 00:00:00 ORDER BY create_time DESC LIMIT 20;加FORCE INDEX是一种急诊手段它告诉优化器“别自作聪明”强制走我们指定的索引。但说实话能不加FORCE INDEX就别加。因为随着数据分布的变化这个“最好”的索引可能过段时间就变了。FORCE INDEX等于绑死了手脚后续维护成本很高。所以我更推荐的做法是先确认统计信息准确再调整索引结构最后才考虑用FORCE INDEX临时兜底。经验之谈写SQL的人一定要心里清楚order by和where要同时利用索引最理想的是把等值列放在联合索引前面范围列放在后面这样基本能做到“索引即结果”。4. 索引不是万能的聊聊回表、覆盖索引和最左前缀4.1 回表到底有多贵InnoDB的索引结构是B树主键索引的叶子节点直接保存整行数据二级索引的叶子节点保存的是主键的值。也就是说当你通过二级索引查询数据时第一步在二级索引B树里定位到主键值第二步还要拿着这个主键值再回到主键索引B树里查一次完整记录这个“回头再查一次”的动作就叫回表。回表不是一定很慢但它有代价每回表一次就是一次随机IO访问。如果结果集只有一行无感如果结果集是几百行甚至几千行性能就会出现肉眼可见的下跌。举一个典型的场景查询一个用户最近一年的订单统计量如果用SELECT COUNT(*) FROM order_record WHERE user_id 12345配合(user_id, create_time)索引因为COUNT只关心数量不关心具体数据InnoDB可以直接遍历二级索引统计完全不需要回表。但如果你换成SELECT *即使数据量一样回表次数也会大幅增加。很多性能问题就是在这类“差一个字段”的细节上拉开的差距。调优的另一个价值就是让你意识到查询列和索引列的关系直接决定回表次数。4.2 覆盖索引让索引替你回答所有问题覆盖索引的定义其实很简单一个索引中包含了查询所需要的所有列MySQL只需要扫描索引不需要回表。比如(user_id, status, create_time)这个索引对于查询SELECT user_id, status, create_time FROM order_record WHERE user_id 12345;索引中就已经包含user_id, status, create_time三列的全部信息。执行计划里Extra会出现Using index这是非常理想的标志。对于高频次、核心的查询我会考虑把查询字段尽量收窄或者适当增加冗余列到索引中让接口响应时间稳定下来。但覆盖索引也不是越多越好。索引本身也是数据写操作时也要同步更新索引树。如果你的表写多读少加太多覆盖索引反而拖累插入和更新性能。一切以真实业务为准数据往往不是越多越好。4.3 最左前缀原则与常见的“索引失效”联合索引遵循最左前缀原则查询条件必须包含联合索引的最左边的列才能用到这个索引。比如idx_user_time (user_id, create_time)。你写成WHERE create_time 2024-01-01MySQL不会走这个索引因为缺少了user_id。这个很多人都理解了但实际踩坑的往往是一些变形场景。举几个最常见的索引失效场景都是我实测过的对索引列使用函数或计算。比如WHERE DATE(create_time) 2024-01-05即使create_time有索引也会因为函数处理导致索引失效。正确写法是WHERE create_time 2024-01-05 00:00:00 AND create_time 2024-01-06 00:00:00。你可能会问为什么不建议用DATE()函数后加索引因为存储的是DATETIME时索引树里存的是完整时间值DATE()相当于把每一行的值都处理一遍后再比较索引顺序完全用不上。隐式类型转换。字段是字符串类型查询时传了数字比如WHERE order_no 123456。MySQL会在比较时把字符串转成数字或者反过来总之这种隐式转换会让索引失效。所以字段类型和参数类型必须一致。LIKE前缀模糊匹配。WHERE order_no LIKE %123%会导致索引不可用因为B树的排序是基于前缀的中间模糊匹配无法利用有序性。反过来LIKE 123%就能用上索引。OR条件可能拆桥。WHERE user_id 12345 OR status 1如果只有user_id有索引status没有优化器可能选择全表扫描而不是分别走索引再合并。解决办法是给status也建索引或者把OR拆成两个查询用UNION合并。这些看起来都是小知识点但实际工作中一条SQL往往不是死在哪一个规则上而是叠加了两三个问题。比如隐式类型转换带着LIKE前缀模糊再陪一个ORDER BY的字段不在索引里那性能想好都难。5. 慢SQL之外的隐性杀手锁、事务与并发控制5.1 为什么有些SQL“平时都好一到高峰期就卡死”数据库性能调优如果只盯着索引和SQL文本那你还只摸到了MySQL这头大象的一条腿。我接手过一个模拟项目某订单导出接口的单次查询只要200ms但每天上午10点到11点之间整个服务的数据库连接池会被打满大量请求排队等待。最初我也以为是慢SQL后来发现慢日志里根本没几条超过1秒的查询说明问题另有他因。最后排查下来问题出在一个非常隐蔽的地方这个导出接口会先查询订单表然后把查询到的订单ID逐条更新状态。更新操作使用事务包裹事务还没有提交时就长时间占用着涉及行的排他锁。这个导出任务一天跑一次一次处理几万条订单每处理完一批就提交一次看起来没毛病。但在并发高峰期其他正常的下单接口只要一操作到同一批订单行就会阻塞在等锁状态连接越等越多最终把连接池拖爆。这个案例对我触动很大它让我意识到MySQL调优不完全是“查询优化”事务长度和锁等待同样决定系统的吞吐上限。5.2 行锁、间隙锁和死锁的实际表现InnoDB默认使用行级锁但在某些隔离级别下还会涉及间隙锁Gap Lock。比如在REPEATABLE READ隔离级别下当你的查询使用了范围条件如WHERE create_time BETWEEN 2024-01-01 AND 2024-06-01InnoDB不仅会锁住已存在的记录还可能在索引间隙之间加锁防止其他事务插入新数据。这个机制保证了可重复读但代价是并发写入的吞吐量显著下降。死锁则更让人头疼。两个事务各自持有一部分的锁然后又都在等待对方释放另一部分锁MySQL会检测到死锁并选择回滚其中一个事务。你会在错误日志里看到类似“Deadlock found”的提示。处理死锁的方法很多时候不是“调参”而是从业务侧规避尽量让所有事务按照相同的顺序访问表比如先更新主表再更新明细表别交叉更新事务要短快速提交才能减少锁持有时间批量更新时控制每次处理的行数比如每次只更新500条就提交而不是一口气处理几万条。我在那个导出场景里最终就是调整了处理粒度把一次事务内处理的数据量缩小加上分批提交成功避开了高峰期锁冲突。吞吐量反而上去了因为整体持锁时间变短了系统的并发压力被释放了。5.3 事务隔离级别别什么库都死磕可重复读MySQL默认的隔离级别是REPEATABLE READ它是为了兼容主从复制和默认行为而设的。但实际业务里很多场景根本不需要这么高的隔离级别。如果你做过类似订单流水、报表统计这类偏读多写少的应用可以评估一下能否把隔离级别调整成READ COMMITTED。READ COMMITTED级别下InnoDB在范围查询时不会使用间隙锁这样可以显著提升并发插入性能同时仍然保证读到的都是已提交的数据。这也是很多互联网公司偏向使用的级别。但调整之前你要非常清楚业务是否依赖“事务内多次读结果一致”的语义。如果不依赖切到READ COMMITTED通常是个性价比很高的选择。-- 查看当前会话隔离级别 SELECT transaction_isolation; -- 设置全局隔离级别示例谨慎操作 SET GLOBAL transaction_isolation READ-COMMITTED;注意SET GLOBAL是全局性的修改后新连接的会话才会生效已有的连接会保持原来的级别。所以这类调整必须配合连接池的重新建立最好在低峰期操作。不要直接在业务高峰期动这个参数。6. 参数调优的正确姿势基于现象反推而不是照搬网上的“万能配置”6.1 先分析你的数据到底放在哪儿很多人拿到一台新数据库服务器第一件事就是把innodb_buffer_pool_size设置成物理内存的70%或80%。这个起点不坏但你不能只做这一件事。我见过最离谱的配置一台业务库内存64GBinnodb_buffer_pool_size设到60GB结果操作系统本身和连接线程加起来的内存需求直接超了还没跑几天就触发OOM数据库进程被系统杀掉损失惨重。innodb_buffer_pool_size是InnoDB用来缓存表数据和索引数据的内存区域它的大小决定了你多少读操作可以直接在内存中命中减少磁盘IO。合理的估算方法是在业务高峰期观察-- 查看InnoDB缓冲池的相关状态 SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read_requests; SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_reads;其中read_requests表示从缓冲池读取逻辑请求的次数reads表示从磁盘读取的次数。命中率大致可以这样算命中率 ≈ (read_requests - reads) / read_requests * 100%如果你的命中率长期在99%以上说明缓冲池大小基本够用如果是95%以下说明大量读请求都落到了磁盘上增大innodb_buffer_pool_size往往是立竿见影的手段。对这个指标要有一个理性认知不是越大越好要留出余量给操作系统和其他线程。6.2 常用参数里哪些是真的能改哪些是碰都不能碰我把平时用得比较多的几个参数整理成一个表格把“要不要改”和“为什么”写清楚参数名作用我的建议innodb_buffer_pool_sizeInnoDB缓存表数据和索引的内存池优先调观察命中率后逐步调整建议设为物理内存的60%-70%但不要超过系统可用内存的80%innodb_log_file_size事务日志redo log文件大小如果经常有大量写操作且磁盘IO很高可适当增大到256MB或512MB注意修改前需要干净关闭数据库否则重启日志重建会很慢max_connections最大连接数不是越大越好。每个连接都会占用线程和内存过大反而会导致内存耗尽。通常默认151但如果业务并发高可以调整到300-500同时要观察线程缓存相关状态sort_buffer_size排序缓冲区大小单会话级别的参数不要盲目调大。每条需要排序的SQL都可能分配这个大小调太大会导致内存暴涨。一般默认或256KB-512KB就够按需调整query_cache_type查询缓存旧版本MySQL 8.0已经移除5.7及以下版本中它争议很大通常建议关闭。因为写操作会使缓存失效开启反而可能降低性能tmp_table_size/max_heap_table_size内存临时表大小上限如果频繁出现Using temporary且临时表落到磁盘可以考虑适度调大通常两者应保持一致因为实际取二者最小值看到这个表格我想强调一个原则每一个参数都对应一个明确的观测现象没有现象就别动参数。以sort_buffer_size为例很多人听说它能加速排序就调到一个很大的值结果单条SQL占用的内存飙升服务器内存不够用换来了一个更严重的隐患。正确做法是先看EXPLAIN里Extra是否出现Using filesort如果真的需要优化排序优先考虑用索引消除排序而不是盲目加缓冲区。6.3 一个参数调优的完整观测闭环我习惯把参数调优做成一个循环观察 → 假设 → 小步调整 → 再观察。举个例子。现象某报表查询每天跑一次要扫描最近半年的订单数据生成汇总结果。跑一次要40秒用户勉强忍了但越来越慢。我检查后发现内存充足、缓冲池命中率95%左右磁盘是SSD但IO利用率长期80%以上。问题出在每次跑批都会产生大量临时表操作部分临时表超过内存限制后落盘导致磁盘IO压力上升。我的操作路径先用EXPLAIN看执行计划确认确实出现了Using temporary观察Created_tmp_disk_tables和Created_tmp_tables这两个状态变量计算落盘比例如果落盘比例很高再查tmp_table_size和max_heap_table_size确认它们设置偏小小步调整把max_heap_table_size和tmp_table_size从16MB调到64MB重启会话后再跑一次查询水位观察落盘临时表数量明显下降IO利用率回落到50%以下查询耗时下降到12秒左右如果后续数据量继续增长压力核心会转移到内存临时表上限本身到时候可能还需要考虑业务层分批查询或汇总表预计算。这个循环描述起来简单真正执行时要有点耐心。参数并不是改完就万事大吉监控才是确认效果的唯一手段。7. 写在最后的几点建议别把MySQL调优变成一把梭如果非要把MySQL调优浓缩成重要讲话我会说这么几句第一先定位问题再谈方案。连慢查询日志都没打开就开始研究innodb_buffer_pool_size怎么调等于还没看病就先开药。把问题定位清楚很多时候你会发现根本不用调参索引改一改SQL改一改性能就翻倍了。第二警惕教条化的“最佳实践”。网上一搜一大把“MySQL 5.7生产环境参数配置模板”看起来很唬人但每套业务的数据量、读写比例、硬件条件都不一样。你复制的可能是别人针对某种极端场景调出来的值放在自己环境里反而会成为拖累。我处理过的线上事故里因为照搬参数导致内存溢出、连接数打爆的案例比SQL没写好导致慢查询的案例还多。第三建立可观测的习惯。没有监控数据的调优都是盲人摸象。哪怕是一个很简单的脚本每天记录关键SQL的耗时、慢查询数量、缓冲池命中率、锁等待次数长期下来都会形成非常有价值的趋势库。等某一天线上真的变慢了你有历史数据可以对比就能迅速锁定是哪个环节出了问题。我就靠着这种笨办法避免过好几次在高峰期拍脑袋改配置的冲动。最后再分享一个小技巧很多人不知道performance_schema里有大量性能事件数据在MySQL 5.7以上版本你可以用它来查看历史上某些SQL的详细耗时构成包括等待CPU、等待IO、等待锁的各个阶段。这个数据比慢日志更细能直接看到一条SQL是在哪个环节“卡顿”最严重。但请注意开启performance_schema会有一些内存开销生产环境建议提前评估小规格机器别轻易开。调优这条路说到底是“懂业务懂原理懂工具”三者的叠加。别指望一两篇博客就变成调优高手但如果你能从一条慢SQL的完整排查链路开始一个一个地闭环处理时间会给你最好的回报。至少下次再有人跟你说“MySQL很慢”你不会第一反应是拍脑袋改配置了。
1. 一开场就泼盆冷水调优不是改参数而是先找到那个“慢”先说个我自己的经历。前两年接了个内部系统的MySQL调优任务最典型的一个业务场景是订单查询列表某个页面接口的响应时长平均在1.2秒左右线上偶尔能飙到3秒以上。当时我的第一反应跟很多刚接触调优的同学一样上去就准备调innodb_buffer_pool_size、调连接数、改日志策略。结果调了半天数据页命中率是上去了但接口该慢还是慢最后羞愧地回滚了配置。真正让我把问题解决的反而是老老实实把慢查询日志打开然后把那条SQL抓出来用EXPLAIN看了一眼。不看不知道问题根本不在数据库的“缓存”或“参数”而是那条SQL在单表300万行数据上走了一个全表扫描每次请求都要处理将近一整张表的数据量。用大白话讲参数调优像是给车换更好的轮胎可你的车压根儿不是在飙高速而是在一条堵死的胡同里挪。轮胎换了也没用得先把路选对。这也是为什么我始终觉得MySQL调优最核心的功夫不是背参数而是形成一套从“现象”到“根因”的排查思维。这篇文章我就顺着自己踩过的坑聊聊调优里真正有用的那几件事怎么定位慢SQL、怎么读懂执行计划、索引设计有哪些说到做不到的细节、InnoDB的锁和事务为什么会影响并发、参数到底怎么设置才叫合适。我尽可能用实际案例串起来讲尽量不抛那种“复制粘贴就知道”的配置清单。2. 从现象到根因如何快速找出拖垮性能的那条SQL2.1 监控指标很多但你先看这四个很多新手一看MySQL监控面板就头大QPS、TPS、线程数、连接数、缓冲池命中率、临时表数量……最后一个都没记住。我自己的习惯是分优先级看先回答三个问题慢了多久是偶发还是持续慢了是全部接口还是个别接口如果是偶发性的优先怀疑锁等待、连接池打满、大事务提交带来的瞬时IO抖动如果是持续性的优先怀疑索引缺失、SQL写法差、数据增长导致的查询计划偏移如果是个别接口慢那么恭喜你99%的问题就在那一条或几条SQL身上直接抓慢查询日志就行。还有一个容易被忽略的点被平均掩盖的慢请求。平均响应时间200ms看着挺好但你要去看95分位或99分位。有时候9个请求只要50ms第10个请求要2秒平均下来250ms系统看着“稳定”实际用户体验已经烂透了。所以在监控上我一般会同时关注最大耗时和慢查询次数而不是只看平均耗时。2.2 慢查询日志的正确打开方式慢查询日志是定位SQL性能问题的第一手段这个基本是共识了。但怎么配置很多人其实没完全用对。# 在MySQL配置文件my.cnf的[mysqld]段下 slow_query_log 1 slow_query_log_file /var/log/mysql/mysql-slow.log long_query_time 1 log_queries_not_using_indexes 1这里有个很小的细节long_query_time的单位是秒最小可以设置到0但如果你设成0那等于把每一条查询都记录下来日志文件会爆炸式增长反而干扰排查。我通常的建议是先设成1秒跑一两天看全貌如果业务本身比较轻量可以再调整到0.5秒甚至更低。还有一个坑是log_queries_not_using_indexes这个参数。它的本意是记录那些没走索引的查询哪怕执行时间很快。这正是我们需要的——有些SQL看着只要50ms但它每执行一次就全表扫描一遍等数据量再翻一倍就是灾难。不过在MySQL 5.7以上版本开启这个参数后日志量可能很大因为它会把很多“用了索引但索引效率很低”的查询也给你记下来需要耐心过滤。我通常的搭配是先开1秒阈值跑一周同时借助pt-query-digest这类工具做聚合分析按总执行时间、平均执行时间、扫描行数排序把真正值得优化的SQL挑出来。注意pt-query-digest是目前社区里比较常用的慢日志分析工具需要单独安装。如果你不方便装工具也可以直接对着慢日志里的# Query_time字段和# Rows_examined字段人工排序但这在有大量慢SQL的业务里效率很低。另外从MySQL 8.0开始慢日志字段整体更规范支持mysqldumpslow -s at这类方式聚合查看至少比裸看日志强一点。实际上到现在我依然保持着每周抽看一下慢日志的习惯。不是为了检查某一条SQL而是为了观察趋势本周慢查询数量相对于上周是涨了还是降了如果是持续上涨那就预示着某些表的索引命中率在下滑或者数据量在悄悄变大。等到用户投诉“系统变卡了”再去看日志永远是被动的。2.3 定位优先级硬件排查先放一边有些同行一遇到慢查询第一反应是去看服务器CPU、磁盘IO、内存。不是说这些不能看而是它们往往只是“果”不是“因”。一条全表扫描的SQL跑起来CPU和IO当然会飙高。你先人肉扛了一波硬件扩容看似解决了实际上SQL没变一个月后数据量涨了问题照旧。所以我把排查顺序固定在这样一条链路里抓慢SQL慢日志 / 性能监控平台分析SQL写法与表结构联合查询是否合理、子查询有没有必要、数据类型是否能用上索引用EXPLAIN看执行计划评估是SQL层优化还是索引层优化还是业务层改造最后才轮得到参数调整和硬件升级。这套顺序我用了很久从来没漏判过。有一个形象的类比厨房出菜慢你先看是哪道菜做得久再看是缺食材还是灶台火候不够最后才是考虑要不要多请一个厨师。很多人一上来就“多请厨师”也就是加服务器配置菜还是那道难做的菜效率怎么可能真正起飞。3. 拆解执行计划EXPLAIN里的每个字都是线索3.1 不要只看“用了索引”就欢呼用EXPLAIN看SQL执行计划是MySQL调优里最基本功的一步。最直接的办法就是把SQL前面加上EXPLAIN比如EXPLAIN SELECT order_id, user_id, amount FROM orders WHERE user_id 12345 AND status 1 ORDER BY create_time DESC LIMIT 10;执行之后会得到一张表里面有id、select_type、table、type、possible_keys、key、key_len、ref、rows、filtered、Extra这些字段。新手最容易犯的错是看到key列有内容也就是“用了索引”就觉得万事大吉。真相远没这么简单。key列只是说明MySQL在优化阶段选择了某个索引但它可能是选了主键索引、普通二级索引也可能在Extra里赫然写着Using temporary或Using filesort。如果一张300万行的表你在type列看到的是ALL那想都不用想全表扫描如果看到index虽然比ALL好一点但通常意味着它在扫描整个索引树代价依然不小。我调优时有一个粗读顺序先把type和Extra这两列放在优先级最高的位置type为ALL无条件全表扫描必须优化优先看能不能加索引或改查询条件type为index扫描了整个索引树比ALL好但当数据量大时也可能很慢type为range索引范围扫描通常是WHERE条件里用了范围查询比如、、BETWEEN这种往往可接受type为ref或eq_ref通过索引等值匹配是常见的高效访问方式type为const通过主键或唯一索引定位到唯一行理论上是最高效的访问方式之一。Extra里最值得注意的是Using filesort、Using temporary和Using where。前两者一旦出现基本说明这条SQL在排序或分组时把结果集拉到了内存或磁盘临时表里处理数据量一大性能直接雪崩。Using where则是在存储引擎返回记录后Server层又做了一层过滤虽然常见但在超大结果集下也值得警惕。3.2 rows字段是怎么“骗人”的rows字段描述的是MySQL估算需要扫描的行数。它是一个估算值不是精确值。为什么不精确因为MySQL的优化器依赖表统计信息和索引基数Cardinality来做估算而统计信息的更新不是即时的尤其在频繁增删改的表上统计信息滞后很常见。这就导致一个现象你在rows看到“只有50行”但实际SQL跑了500毫秒甚至更久。这种情况大概率是统计信息失真导致优化器选错了索引。解决方案也不复杂可以用ANALYZE TABLE手动更新统计信息。ANALYZE TABLE orders;另外如果你发现某张表的索引基数长期不准那就要反思表的设计了是不是写入量太大、更新太频繁统计信息根本跟不飞这时候可以考虑在低峰期定时跑ANALYZE TABLE。我自己就在某套库上用过这种定时任务效果立竿见影。原来一条要跑800ms的SQL在更新统计信息后优化器选对了索引耗时直接降到50ms以内。遇到这种情况真不用改SQL问题就在“选择”上。3.3 一个经典的联合索引案例为了让大家有更直观的感受我拿一个真实改过的SQL来做全流程拆解。表结构大致是这样字段名做了脱敏替换结构不变CREATE TABLE order_record ( id BIGINT PRIMARY KEY 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, pay_time DATETIME DEFAULT NULL, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_user_time (user_id, create_time), KEY idx_status (status) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;业务需求是查询某个用户最近30天的订单列表并按照下单时间倒序。起初的SQL写法非常直接SELECT id, order_no, status, amount, pay_time, create_time FROM order_record WHERE user_id 12345 AND create_time DATE_SUB(NOW(), INTERVAL 30 DAY) ORDER BY create_time DESC LIMIT 20;我最初设计的索引是idx_user_time也就是(user_id, create_time)理论上这个索引既能等值匹配user_id又能范围匹配create_time看起来天衣无缝。但实际EXPLAIN的结果里出现了Using filesort为什么因为联合索引的顺序是user_id在前、create_time在后查询时用户用等值条件定位到user_id 12345的所有记录这些记录在索引内部按create_time有序排列所以理论上ORDER BY create_time DESC可以直接利用索引顺序不需要排序。但问题出在LIMIT和WHERE条件的配合上MySQL优化器在执行时它需要在内存里扫描所有符合user_id和create_time范围的数据行然后尽可能把符合条件的数据剔重后再排序。这个过程中的一个隐藏代价是create_time作为二级索引的第二个字段在范围条件下没办法像等值条件那样直接定位最小化区域优化器最终选择了自己排序。解决方案有两个我都试过效果都不错方案一把索引进化成(user_id, create_time)在SQL层面让排序和范围都能压到索引里。比如把范围条件改成先按user_id等值定位再限定一个稍微宽松的时间范围让MySQL能通过索引拿到连续有序的数据段避免额外的排序步骤。SSD和内存环境下Using filesort在小数据集上不明显但300万行数据配合分页慢起来真的是指数级增长。方案二直接为排序字段单独建索引SQL改为SELECT id, order_no, status, amount, pay_time, create_time FROM order_record FORCE INDEX (idx_user_time) WHERE user_id 12345 AND create_time 2024-01-01 00:00:00 ORDER BY create_time DESC LIMIT 20;加FORCE INDEX是一种急诊手段它告诉优化器“别自作聪明”强制走我们指定的索引。但说实话能不加FORCE INDEX就别加。因为随着数据分布的变化这个“最好”的索引可能过段时间就变了。FORCE INDEX等于绑死了手脚后续维护成本很高。所以我更推荐的做法是先确认统计信息准确再调整索引结构最后才考虑用FORCE INDEX临时兜底。经验之谈写SQL的人一定要心里清楚order by和where要同时利用索引最理想的是把等值列放在联合索引前面范围列放在后面这样基本能做到“索引即结果”。4. 索引不是万能的聊聊回表、覆盖索引和最左前缀4.1 回表到底有多贵InnoDB的索引结构是B树主键索引的叶子节点直接保存整行数据二级索引的叶子节点保存的是主键的值。也就是说当你通过二级索引查询数据时第一步在二级索引B树里定位到主键值第二步还要拿着这个主键值再回到主键索引B树里查一次完整记录这个“回头再查一次”的动作就叫回表。回表不是一定很慢但它有代价每回表一次就是一次随机IO访问。如果结果集只有一行无感如果结果集是几百行甚至几千行性能就会出现肉眼可见的下跌。举一个典型的场景查询一个用户最近一年的订单统计量如果用SELECT COUNT(*) FROM order_record WHERE user_id 12345配合(user_id, create_time)索引因为COUNT只关心数量不关心具体数据InnoDB可以直接遍历二级索引统计完全不需要回表。但如果你换成SELECT *即使数据量一样回表次数也会大幅增加。很多性能问题就是在这类“差一个字段”的细节上拉开的差距。调优的另一个价值就是让你意识到查询列和索引列的关系直接决定回表次数。4.2 覆盖索引让索引替你回答所有问题覆盖索引的定义其实很简单一个索引中包含了查询所需要的所有列MySQL只需要扫描索引不需要回表。比如(user_id, status, create_time)这个索引对于查询SELECT user_id, status, create_time FROM order_record WHERE user_id 12345;索引中就已经包含user_id, status, create_time三列的全部信息。执行计划里Extra会出现Using index这是非常理想的标志。对于高频次、核心的查询我会考虑把查询字段尽量收窄或者适当增加冗余列到索引中让接口响应时间稳定下来。但覆盖索引也不是越多越好。索引本身也是数据写操作时也要同步更新索引树。如果你的表写多读少加太多覆盖索引反而拖累插入和更新性能。一切以真实业务为准数据往往不是越多越好。4.3 最左前缀原则与常见的“索引失效”联合索引遵循最左前缀原则查询条件必须包含联合索引的最左边的列才能用到这个索引。比如idx_user_time (user_id, create_time)。你写成WHERE create_time 2024-01-01MySQL不会走这个索引因为缺少了user_id。这个很多人都理解了但实际踩坑的往往是一些变形场景。举几个最常见的索引失效场景都是我实测过的对索引列使用函数或计算。比如WHERE DATE(create_time) 2024-01-05即使create_time有索引也会因为函数处理导致索引失效。正确写法是WHERE create_time 2024-01-05 00:00:00 AND create_time 2024-01-06 00:00:00。你可能会问为什么不建议用DATE()函数后加索引因为存储的是DATETIME时索引树里存的是完整时间值DATE()相当于把每一行的值都处理一遍后再比较索引顺序完全用不上。隐式类型转换。字段是字符串类型查询时传了数字比如WHERE order_no 123456。MySQL会在比较时把字符串转成数字或者反过来总之这种隐式转换会让索引失效。所以字段类型和参数类型必须一致。LIKE前缀模糊匹配。WHERE order_no LIKE %123%会导致索引不可用因为B树的排序是基于前缀的中间模糊匹配无法利用有序性。反过来LIKE 123%就能用上索引。OR条件可能拆桥。WHERE user_id 12345 OR status 1如果只有user_id有索引status没有优化器可能选择全表扫描而不是分别走索引再合并。解决办法是给status也建索引或者把OR拆成两个查询用UNION合并。这些看起来都是小知识点但实际工作中一条SQL往往不是死在哪一个规则上而是叠加了两三个问题。比如隐式类型转换带着LIKE前缀模糊再陪一个ORDER BY的字段不在索引里那性能想好都难。5. 慢SQL之外的隐性杀手锁、事务与并发控制5.1 为什么有些SQL“平时都好一到高峰期就卡死”数据库性能调优如果只盯着索引和SQL文本那你还只摸到了MySQL这头大象的一条腿。我接手过一个模拟项目某订单导出接口的单次查询只要200ms但每天上午10点到11点之间整个服务的数据库连接池会被打满大量请求排队等待。最初我也以为是慢SQL后来发现慢日志里根本没几条超过1秒的查询说明问题另有他因。最后排查下来问题出在一个非常隐蔽的地方这个导出接口会先查询订单表然后把查询到的订单ID逐条更新状态。更新操作使用事务包裹事务还没有提交时就长时间占用着涉及行的排他锁。这个导出任务一天跑一次一次处理几万条订单每处理完一批就提交一次看起来没毛病。但在并发高峰期其他正常的下单接口只要一操作到同一批订单行就会阻塞在等锁状态连接越等越多最终把连接池拖爆。这个案例对我触动很大它让我意识到MySQL调优不完全是“查询优化”事务长度和锁等待同样决定系统的吞吐上限。5.2 行锁、间隙锁和死锁的实际表现InnoDB默认使用行级锁但在某些隔离级别下还会涉及间隙锁Gap Lock。比如在REPEATABLE READ隔离级别下当你的查询使用了范围条件如WHERE create_time BETWEEN 2024-01-01 AND 2024-06-01InnoDB不仅会锁住已存在的记录还可能在索引间隙之间加锁防止其他事务插入新数据。这个机制保证了可重复读但代价是并发写入的吞吐量显著下降。死锁则更让人头疼。两个事务各自持有一部分的锁然后又都在等待对方释放另一部分锁MySQL会检测到死锁并选择回滚其中一个事务。你会在错误日志里看到类似“Deadlock found”的提示。处理死锁的方法很多时候不是“调参”而是从业务侧规避尽量让所有事务按照相同的顺序访问表比如先更新主表再更新明细表别交叉更新事务要短快速提交才能减少锁持有时间批量更新时控制每次处理的行数比如每次只更新500条就提交而不是一口气处理几万条。我在那个导出场景里最终就是调整了处理粒度把一次事务内处理的数据量缩小加上分批提交成功避开了高峰期锁冲突。吞吐量反而上去了因为整体持锁时间变短了系统的并发压力被释放了。5.3 事务隔离级别别什么库都死磕可重复读MySQL默认的隔离级别是REPEATABLE READ它是为了兼容主从复制和默认行为而设的。但实际业务里很多场景根本不需要这么高的隔离级别。如果你做过类似订单流水、报表统计这类偏读多写少的应用可以评估一下能否把隔离级别调整成READ COMMITTED。READ COMMITTED级别下InnoDB在范围查询时不会使用间隙锁这样可以显著提升并发插入性能同时仍然保证读到的都是已提交的数据。这也是很多互联网公司偏向使用的级别。但调整之前你要非常清楚业务是否依赖“事务内多次读结果一致”的语义。如果不依赖切到READ COMMITTED通常是个性价比很高的选择。-- 查看当前会话隔离级别 SELECT transaction_isolation; -- 设置全局隔离级别示例谨慎操作 SET GLOBAL transaction_isolation READ-COMMITTED;注意SET GLOBAL是全局性的修改后新连接的会话才会生效已有的连接会保持原来的级别。所以这类调整必须配合连接池的重新建立最好在低峰期操作。不要直接在业务高峰期动这个参数。6. 参数调优的正确姿势基于现象反推而不是照搬网上的“万能配置”6.1 先分析你的数据到底放在哪儿很多人拿到一台新数据库服务器第一件事就是把innodb_buffer_pool_size设置成物理内存的70%或80%。这个起点不坏但你不能只做这一件事。我见过最离谱的配置一台业务库内存64GBinnodb_buffer_pool_size设到60GB结果操作系统本身和连接线程加起来的内存需求直接超了还没跑几天就触发OOM数据库进程被系统杀掉损失惨重。innodb_buffer_pool_size是InnoDB用来缓存表数据和索引数据的内存区域它的大小决定了你多少读操作可以直接在内存中命中减少磁盘IO。合理的估算方法是在业务高峰期观察-- 查看InnoDB缓冲池的相关状态 SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read_requests; SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_reads;其中read_requests表示从缓冲池读取逻辑请求的次数reads表示从磁盘读取的次数。命中率大致可以这样算命中率 ≈ (read_requests - reads) / read_requests * 100%如果你的命中率长期在99%以上说明缓冲池大小基本够用如果是95%以下说明大量读请求都落到了磁盘上增大innodb_buffer_pool_size往往是立竿见影的手段。对这个指标要有一个理性认知不是越大越好要留出余量给操作系统和其他线程。6.2 常用参数里哪些是真的能改哪些是碰都不能碰我把平时用得比较多的几个参数整理成一个表格把“要不要改”和“为什么”写清楚参数名作用我的建议innodb_buffer_pool_sizeInnoDB缓存表数据和索引的内存池优先调观察命中率后逐步调整建议设为物理内存的60%-70%但不要超过系统可用内存的80%innodb_log_file_size事务日志redo log文件大小如果经常有大量写操作且磁盘IO很高可适当增大到256MB或512MB注意修改前需要干净关闭数据库否则重启日志重建会很慢max_connections最大连接数不是越大越好。每个连接都会占用线程和内存过大反而会导致内存耗尽。通常默认151但如果业务并发高可以调整到300-500同时要观察线程缓存相关状态sort_buffer_size排序缓冲区大小单会话级别的参数不要盲目调大。每条需要排序的SQL都可能分配这个大小调太大会导致内存暴涨。一般默认或256KB-512KB就够按需调整query_cache_type查询缓存旧版本MySQL 8.0已经移除5.7及以下版本中它争议很大通常建议关闭。因为写操作会使缓存失效开启反而可能降低性能tmp_table_size/max_heap_table_size内存临时表大小上限如果频繁出现Using temporary且临时表落到磁盘可以考虑适度调大通常两者应保持一致因为实际取二者最小值看到这个表格我想强调一个原则每一个参数都对应一个明确的观测现象没有现象就别动参数。以sort_buffer_size为例很多人听说它能加速排序就调到一个很大的值结果单条SQL占用的内存飙升服务器内存不够用换来了一个更严重的隐患。正确做法是先看EXPLAIN里Extra是否出现Using filesort如果真的需要优化排序优先考虑用索引消除排序而不是盲目加缓冲区。6.3 一个参数调优的完整观测闭环我习惯把参数调优做成一个循环观察 → 假设 → 小步调整 → 再观察。举个例子。现象某报表查询每天跑一次要扫描最近半年的订单数据生成汇总结果。跑一次要40秒用户勉强忍了但越来越慢。我检查后发现内存充足、缓冲池命中率95%左右磁盘是SSD但IO利用率长期80%以上。问题出在每次跑批都会产生大量临时表操作部分临时表超过内存限制后落盘导致磁盘IO压力上升。我的操作路径先用EXPLAIN看执行计划确认确实出现了Using temporary观察Created_tmp_disk_tables和Created_tmp_tables这两个状态变量计算落盘比例如果落盘比例很高再查tmp_table_size和max_heap_table_size确认它们设置偏小小步调整把max_heap_table_size和tmp_table_size从16MB调到64MB重启会话后再跑一次查询水位观察落盘临时表数量明显下降IO利用率回落到50%以下查询耗时下降到12秒左右如果后续数据量继续增长压力核心会转移到内存临时表上限本身到时候可能还需要考虑业务层分批查询或汇总表预计算。这个循环描述起来简单真正执行时要有点耐心。参数并不是改完就万事大吉监控才是确认效果的唯一手段。7. 写在最后的几点建议别把MySQL调优变成一把梭如果非要把MySQL调优浓缩成重要讲话我会说这么几句第一先定位问题再谈方案。连慢查询日志都没打开就开始研究innodb_buffer_pool_size怎么调等于还没看病就先开药。把问题定位清楚很多时候你会发现根本不用调参索引改一改SQL改一改性能就翻倍了。第二警惕教条化的“最佳实践”。网上一搜一大把“MySQL 5.7生产环境参数配置模板”看起来很唬人但每套业务的数据量、读写比例、硬件条件都不一样。你复制的可能是别人针对某种极端场景调出来的值放在自己环境里反而会成为拖累。我处理过的线上事故里因为照搬参数导致内存溢出、连接数打爆的案例比SQL没写好导致慢查询的案例还多。第三建立可观测的习惯。没有监控数据的调优都是盲人摸象。哪怕是一个很简单的脚本每天记录关键SQL的耗时、慢查询数量、缓冲池命中率、锁等待次数长期下来都会形成非常有价值的趋势库。等某一天线上真的变慢了你有历史数据可以对比就能迅速锁定是哪个环节出了问题。我就靠着这种笨办法避免过好几次在高峰期拍脑袋改配置的冲动。最后再分享一个小技巧很多人不知道performance_schema里有大量性能事件数据在MySQL 5.7以上版本你可以用它来查看历史上某些SQL的详细耗时构成包括等待CPU、等待IO、等待锁的各个阶段。这个数据比慢日志更细能直接看到一条SQL是在哪个环节“卡顿”最严重。但请注意开启performance_schema会有一些内存开销生产环境建议提前评估小规格机器别轻易开。调优这条路说到底是“懂业务懂原理懂工具”三者的叠加。别指望一两篇博客就变成调优高手但如果你能从一条慢SQL的完整排查链路开始一个一个地闭环处理时间会给你最好的回报。至少下次再有人跟你说“MySQL很慢”你不会第一反应是拍脑袋改配置了。
