MySQL性能优化这件事我做了快十年踩过的坑比很多人见过的表都多。坦白讲大部分性能问题根本轮不到“调参”和“换硬件”十有八九是慢查询和表结构设计挖的坑。这篇指南我按“20个核心技巧”为主线把索引、SQL写法、表结构、事务锁、配置、监控一条龙讲透每一条都附上我实际验证过的方案和排查思路。不管你是刚接手一个慢到怀疑人生的老系统还是想在开发阶段就把隐患摁死这篇都值得你花半小时读完——读完直接照着做效果立竿见影。1. 先把思路捋清楚性能优化不是“调参数”而是“系统作战”很多人一谈MySQL性能优化第一反应就是改innodb_buffer_pool_size或者纠结max_connections该设多少。我见过最离谱的一次有人把buffer_pool调到机器物理内存的90%结果直接OOM整个业务挂了半小时。调参是最容易的也是最不该先做的。真正的性能优化是一个排查链路先从慢查询日志里找到最耗时的SQL再用EXPLAIN看执行计划发现问题往往集中在三处——索引没走对、查询写了太多无用功、锁竞争太严重。这三处解决掉性能至少提升一个数量级。之后再谈配置、谈硬件才有意义。我一般把优化分成五个层次按优先级排层次优化对象成本收益第一层SQL语句与索引低极高通常10倍以上第二层表结构设计中高影响全生命周期第三层事务与锁机制中高并发场景明显第四层MySQL配置参数低中需要结合业务第五层硬件与架构高高最后手段这个顺序很重要。先做前三层再动配置和硬件否则你花大价钱升级了机器慢查询还是慢查询只是从“特别慢”变成“稍微慢”。这篇文章的20个技巧我就按这个优先级来排每一层都给出可直接复制的操作。2. 20个核心技巧逐条拆解从SQL到架构的完整打法2.1 索引设计从“随便建”到“按需建”技巧1-3技巧1优先为WHERE、JOIN、ORDER BY的列建索引这条看似基础但执行不到位的人特别多。我见过生产库里有几十个索引但核心查询的WHERE条件列一个索引都没有——因为建索引的人只给主键和唯一键建了。判断标准很简单打开慢查询日志找出那些rows_examined特别大的SQLWHERE条件里的列、JOIN的连接列、ORDER BY排序的列这三类就是索引的“第一优先级”。技巧2联合索引要遵循“最左前缀”原则联合索引是新手最容易犯迷糊的地方。(a, b, c)联合索引实际上等于建了(a)、(a, b)、(a, b, c)三个索引。所以查询里如果只用了b列或者c列索引就用不上。我常用的一个口诀联合索引的列顺序按“等值条件列优先、范围条件列其次、排序列最后”来排。注意这条口诀不是绝对的。如果某个列是范围查询比如、、BETWEEN把它放在前面会导致后面的列索引失效。例如(a, b)查询WHERE a 100 AND b 1b的等值条件用不上索引。遇到这种把等值条件列放前面更稳。技巧3覆盖索引能“让数据在索引里直接查到”覆盖索引是我个人最推荐的一项优化。它指的是查询的SELECT列、WHERE列、ORDER BY列全部包含在同一个索引内这样MySQL引擎只需要扫索引页不需要回表查聚簇索引。举个例子表里有id, user_id, order_amount三列查询SELECT user_id, order_amount FROM orders WHERE id 123如果建个(id, user_id, order_amount)的索引这条查询全程不碰数据行。实测下来覆盖索引能把查询耗时从几十毫秒压到几毫秒尤其在数据量千万级的时候。怎么判断是否命中了覆盖索引EXPLAIN输出里Extra列显示Using index就是命中了。2.2 查询语句写法同样的结果不同的代价技巧4-6技巧4只查需要的字段不要无脑SELECT *这条我强调过无数次。SELECT *会把所有列的数据从存储引擎捞出来传到Server层再做过滤——哪怕你只关心其中一列。数据行宽了回表成本、网络传输成本、排序临时文件体积全都会变大。有人反驳说“我们表就5个字段无所谓”等表扩到30个字段、数据量到千万级你就知道SELECT *有多疼了。只列出你真正用到的字段是零成本的优化。技巧5分页查询别用LIMIT 1000000, 20大分页是经典性能杀手。LIMIT 1000000, 20意味着MySQL要扫描前1000020行然后丢弃前1000000行代价极高。我常用的替代方案有两个延迟关联先只查出主键ID再用主键JOIN回原表取数据。基于游标的分页记住上一页最后一条ID用WHERE id 上一个ID ORDER BY id LIMIT 20。第二种方案最彻底但要求排序字段唯一且有序第一种方案通用性更强。实际项目中我用延迟关联把一次5秒的深分页查询降到了200毫秒。技巧6用EXPLAIN看执行计划慢之前就发现问题我要求团队里任何人写SQL之前必须先把EXPLAIN跑一遍。重点看四个字段type最好到ref或rangeALL是全表扫描要警惕、key实际用到的索引、rows预估扫描行数、Extra有没有Using filesort、Using temporary。一旦看到Using filesort或Using temporary就要立刻警觉这条SQL在排序或者建临时表数据量一大必慢。实操心得rows是预估数不一定准但rows和实际慢查询日志里的Rows_examined差距太大的时候说明统计信息过旧跑一次ANALYZE TABLE刷新统计信息往往能解决。2.3 表结构设计字段选错了后面全是债技巧7-9技巧7字段类型宁小勿大能定长尽量定长很多表设计者习惯性地上来就VARCHAR(255)主键一律BIGINT时间字段全部DATETIME。实际上状态、枚举、小小的数字用TINYINT就行别用INT。IP地址用INT UNSIGNED存配合INET_ATON/INET_NTOA转换比VARCHAR(45)省不少空间。时间字段能用TIMESTAMP就别用DATETIME——前者4字节后者8字节同样的索引前者能存更多key page扫描更快。技巧8避免NULL列的滥用理论上MySQL对NULL有处理逻辑索引对NULL的处理也有限制。我见过有人设计表时几乎所有字段都允许NULL结果每个查询都得额外判断。能用NOT NULL默认值的就设上默认值比如状态默认0、时间默认CURRENT_TIMESTAMP。这能减少存储层的判断也能避免WHERE col IS NULL走不好索引的问题。技巧9不要过度拆分表也不要单表无限膨胀很多人一听性能优化就想着“分库分表”。分库分表是最后的手段它带来的分布式事务、跨表JOIN查询、全局唯一ID问题任何一个都比性能问题更难处理。单表数据量在千万级以下、索引合理的情况下MySQL完全扛得住。真到需要分的时候优先考虑按时间归档历史数据、用分区表都比直接上中间件稳妥。2.4 让MySQL“少干活”缓存、排序与聚合技巧10-13技巧10排序别让数据库硬扛能走索引就走索引ORDER BY的列如果不在索引里MySQL就得把数据放到内存或磁盘做filesort。数据量小没事数据量大就直接让CPU和IO飙高。解决思路给排序列建索引或者减少排序的数据集先过滤再排序。技巧11GROUP BY和DISTINCT要看清“去重”逻辑热搜词里有“mysql的or能去重吗”这个问题其实就是DISTINCT和GROUP BY的区别DISTINCT是对查询结果去重GROUP BY是分组后再聚合。它们在执行计划里都可能产生临时表。优化方式给GROUP BY的列建索引或者用GROUP BY替代DISTINCT时注意索引匹配情况。另外UNION默认自带去重效果但会有排序去重的开销如果你业务上能接受重复数据用UNION ALL替代UNION性能提升极其显著。技巧12大结果集聚合考虑拆分批次比如统计一张千万级订单表的月销售额一个SUM下去可能扫描全表。我的做法是如果业务对实时性要求不高就建立汇总表按小时/天定时增量统计如果必须实时就缩小扫描范围比如只扫描当天的分区并配合覆盖索引。技巧13再利用OPTIMIZER_TRACE分析“优化器没选对索引”有的SQL明明有索引执行计划就是不走。我遇到过很多次是因为统计信息不准或者条件里写了函数导致索引失效。排查这类问题的利器是OPTIMIZER_TRACESET optimizer_traceenabledon; -- 执行你的查询 SELECT * FROM information_schema.OPTIMIZER_TRACE;它能显示优化器为什么会选择某个执行计划以及为什么放弃了某个索引。这个信息在常规的EXPLAIN里是看不到的。2.5 事务、锁与并发控制技巧14-16技巧14事务要短平快别把业务逻辑塞进事务里我踩过一个特别典型的坑代码里一个大事务包含几万行数据的更新还调了外部接口接口超时3秒事务就开了3秒以上直接导致连接池耗尽整个服务雪崩。事务的原则是能拆短就拆短只把必须原子化的操作放进去。长事务会持有锁不释放阻塞其他事务还让undo log无限膨胀。技巧15合理使用索引减少锁范围InnoDB的行锁是建立在索引上的。如果你的WHERE条件没有索引MySQL会走全表扫描把所有匹配的行都锁住——实际上因为扫描全表几乎相当于锁了全表。我处理过一个死锁案例根因就是DELETE语句的WHERE条件列没索引导致间隙锁范围扩大两个事务互相锁等待。给筛选列加索引是降低锁竞争最直接的手段。技巧16了解锁分类死锁了才知道往哪查MySQL的锁大致分为表锁、行锁、间隙锁、意向锁。间隙锁是RR隔离级别下防治幻读用的但它也是死锁的头号源头。排查死锁的固定流程SHOW ENGINE INNODB STATUS;看输出的LATEST DETECTED DEADLOCK段里面会记录两个事务各自的SQL和持有/等待的锁。我把这个输出取关键字“lock_mode”、“waiting”过一遍基本能定位到是哪两条SQL互相打架。日常预防死锁的方法多个事务访问同一组表的时候按相同顺序操作更新数据尽量走主键或唯一索引。2.6 配置与架构层面的兜底优化技巧17-20技巧17innodb_buffer_pool_size是内存里最值钱的一分钱这个参数决定InnoDB缓存表数据和索引的内存大小。建议设为机器物理内存的50%-70%但要留出足够余量给操作系统和连接线程。设置完用下面的SQL验证命中率SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read%;计算(Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests如果命中率低于95%说明缓存池太小或者数据访问太分散。技巧18慢查询日志必须常开阈值设在1秒以内很多人在生产上把long_query_time设成5秒、10秒等于把慢查询日志当摆设。我建议开发环境设成0.1秒生产也至少设1秒这样你能提前感知到索引失效、数据量膨胀等趋势。开启方法slow_query_log ON slow_query_log_file /var/log/mysql/slow.log long_query_time 1 log_queries_not_using_indexes ONlog_queries_not_using_indexes是我特别推荐的它会把所有没走索引的查询全部记录下来哪怕查询本身很快——这些才是潜在的地雷。技巧19连接数不是越大越好连接池要配合把max_connections从默认151调到1000看起来很豪横实际上每条连接都占用内存和线程资源连接数一上来CPU就飙了。真正要做的是应用层连接池控制活跃连接数比如HikariCP默认10个就够数据库端max_connections只作为兜底上限。我见过一个诡异的问题MySQL CPU不高但应用响应极慢最后发现是连接池配置了200个全挤在SLEEP状态把线程调度拖垮了。技巧20从EXPLAIN到PROFILING用数据说话遇到难缠的慢查询我会开PROFILING看每个阶段的耗时占比SET profiling 1; -- 执行慢SQL SHOW PROFILES; SHOW PROFILE FOR QUERY 1;输出里能看到Sending data、Sorting result、Creating tmp table各花了多少时间。比如我看到Sorting result占比70%就知道排序是瓶颈优先给排序列加索引看到Sending data占比高则考虑是不是扫描行数太多、需要优化WHERE条件。3. 实战排查一次慢查询的完整“缉凶”过程3.1 从慢查询日志里找“嫌疑犯”我最近处理的一个项目业务方反馈“报表查询越来越慢”慢查询日志里一眼就看到这条SELECT a.id, a.order_no, b.user_name, SUM(a.amount) FROM orders a LEFT JOIN users b ON a.user_id b.id WHERE a.created_at BETWEEN 2024-06-01 AND 2024-06-30 GROUP BY a.user_id ORDER BY SUM(a.amount) DESC LIMIT 50;日志显示扫描了800万行耗时11秒。这是个很典型的问题集大表JOIN、范围查询、GROUP BY聚合、ORDER BY排序全齐了。3.2 用EXPLAIN锁定问题点跑一遍EXPLAIN结果是这样的关键信息字段值说明typeALLorders表全表扫描rows8000000预估全表ExtraUsing temporary; Using filesort临时表文件排序两个信号非常明确ALL说明没用上索引Using temporary; Using filesort说明聚合和排序都在磁盘临时表里完成。3.3 一步步修复每步都验证效果第一步给created_at加索引ALTER TABLE orders ADD INDEX idx_created_at (created_at);但EXPLAIN依然显示rows很大——因为范围查询还是扫了全月的数据有600万行查询降到6秒。这不够。第二步改成覆盖索引我把索引改成(created_at, user_id, amount)让聚合需要的列都在索引里避免回表。查询降到2.5秒。但Using filesort还在因为排序的SUM(amount)是计算出来的索引帮不上忙。第三步去掉不必要的LEFT JOIN我发现user_name只是展示字段不需要参与过滤。于是改写为先聚合订单再关联用户SELECT t.id, t.order_no, u.user_name, t.total_amount FROM ( SELECT id, order_no, user_id, SUM(amount) AS total_amount FROM orders WHERE created_at BETWEEN 2024-06-01 AND 2024-06-30 GROUP BY user_id ORDER BY total_amount DESC LIMIT 50 ) t LEFT JOIN users u ON t.user_id u.id;子查询里只处理订单表聚合完只剩少量结果再JOIN用户表。这个版本跑到了300毫秒以内提速接近40倍。这个案例我复盘过很多次核心教训是不要一上来就调配置先用EXPLAIN把SQL本身的问题揪出来。这个查询改完索引结构SQL写法再用慢查询日志复查Rows_examined从800万降到60万问题彻底解决。4. 高频故障场景复盘锁、索引失效、深分页4.1 锁等待与死锁一条UPDATE引发的“雪崩”之前线上出现过一次大量“Lock wait timeout exceeded”报错。排查过程先SHOW ENGINE INNODB STATUS看锁信息发现两个事务都在争用同一张表的同一行。进一步看代码逻辑A事务先UPDATE订单再UPDATE用户B事务先UPDATE用户再UPDATE订单——两个事务的加锁顺序相反死锁条件成立。解决方案分两步应用层统一加锁顺序所有事务都先操作用户表再操作订单表。数据库层把隔离级别从REPEATABLE READ降到READ COMMITTED如果业务允许减少间隙锁的冲突概率。这能显著降低死锁发生频率但要注意需要业务侧确认“不可重复读”的影响可控。4.2 索引失效的四个“隐形杀手”我归纳了日常最容易导致索引失效的四个写法你们可以对照自查写法例子后果对索引列使用函数WHERE DATE(created_at) 2024-06-01索引失效全表扫描隐式类型转换WHERE phone 13800138000phone是VARCHAR类型转换导致索引失效前导模糊查询WHERE name LIKE %张无法用索引树定位OR连接非索引列WHERE id 1 OR status 0优化器可能放弃索引修复方式很简单函数式写法改成范围条件created_at 2024-06-01 AND created_at 2024-06-02字符串字段查询时带上引号前导模糊查询考虑全文索引OR的两边都能用索引时优化器才会考虑走索引。4.3 深分页LIMIT 500000, 20的性能拐点有个订单列表接口用户翻到第100页就开始卡。SQL长这样SELECT * FROM orders WHERE status 1 ORDER BY create_time DESC LIMIT 500000, 20;这条查询要排序后扫描50万行再丢弃。我改成延迟关联的写法SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders WHERE status 1 ORDER BY create_time DESC LIMIT 500000, 20 ) t ON o.id t.id;内层只查主键ID走create_time索引扫描的代价小很多外层用主键回表取20行。实测接口从4秒降到300毫秒。5. 常见问题速查表照着排查就行症状可能原因排查命令/动作解决方案CPU持续100%慢SQL、无索引扫全表慢日志、SHOW PROCESSLIST优化索引与SQL连接数打满长事务持有连接SHOW PROCESSLIST看SLEEP状态缩短事务、调整连接池死锁频繁加锁顺序不一致、间隙锁冲突SHOW ENGINE INNODB STATUS统一加锁顺序、降隔离级别查询越来越慢数据量膨胀、索引失效慢日志对比Rows_examined重新分析执行计划、整理索引分页越翻越慢深分页扫描过大EXPLAIN看rows延迟关联、游标分页磁盘IO高缓存命中率低、排序溢出Innodb_buffer_pool_read%调大buffer pool、优化排序主从延迟大事务、DDL阻塞SHOW SLAVE STATUS看Seconds_Behind_Master拆分大事务、错峰执行DDL这张表我贴在公司项目组的wiki上每次有人报“数据库慢”先让对表自查解决率很高。6. 额外建议把性能优化“前置”而不是“救火”在我自己的项目里加了三条强制规矩第一任何人提交SQL之前导出EXPLAIN贴到代码评审里第二所有新索引要说明是为哪条慢查询服务的避免无效索引泛滥第三每张核心表每季度跑一次ANALYZE TABLE和慢日志复盘。这套机制比任何单次优化都管用它让性能问题在开发阶段就被挡住。最后分享一个小技巧你在优化一个查询时改一步、验证一步、记录一步不要想着一次性把SQL、索引、配置全改完。我见过太多人一口气加了三个索引、改了两条SQL、调了四个参数结果变快了都不知道是哪一步的功劳回滚时更是灾难。性能优化的每一步都应该有数据支撑用EXPLAIN的rows、用慢查询日志的耗时做前后对比这比任何玄学都靠谱。 
长河君 · 2026/10/9 3:36:38 · 阅读 1.2k MySQL性能优化实战：20个核心技巧从索引设计到慢查询排查
MySQL性能优化这件事我做了快十年踩过的坑比很多人见过的表都多。坦白讲大部分性能问题根本轮不到“调参”和“换硬件”十有八九是慢查询和表结构设计挖的坑。这篇指南我按“20个核心技巧”为主线把索引、SQL写法、表结构、事务锁、配置、监控一条龙讲透每一条都附上我实际验证过的方案和排查思路。不管你是刚接手一个慢到怀疑人生的老系统还是想在开发阶段就把隐患摁死这篇都值得你花半小时读完——读完直接照着做效果立竿见影。1. 先把思路捋清楚性能优化不是“调参数”而是“系统作战”很多人一谈MySQL性能优化第一反应就是改innodb_buffer_pool_size或者纠结max_connections该设多少。我见过最离谱的一次有人把buffer_pool调到机器物理内存的90%结果直接OOM整个业务挂了半小时。调参是最容易的也是最不该先做的。真正的性能优化是一个排查链路先从慢查询日志里找到最耗时的SQL再用EXPLAIN看执行计划发现问题往往集中在三处——索引没走对、查询写了太多无用功、锁竞争太严重。这三处解决掉性能至少提升一个数量级。之后再谈配置、谈硬件才有意义。我一般把优化分成五个层次按优先级排层次优化对象成本收益第一层SQL语句与索引低极高通常10倍以上第二层表结构设计中高影响全生命周期第三层事务与锁机制中高并发场景明显第四层MySQL配置参数低中需要结合业务第五层硬件与架构高高最后手段这个顺序很重要。先做前三层再动配置和硬件否则你花大价钱升级了机器慢查询还是慢查询只是从“特别慢”变成“稍微慢”。这篇文章的20个技巧我就按这个优先级来排每一层都给出可直接复制的操作。2. 20个核心技巧逐条拆解从SQL到架构的完整打法2.1 索引设计从“随便建”到“按需建”技巧1-3技巧1优先为WHERE、JOIN、ORDER BY的列建索引这条看似基础但执行不到位的人特别多。我见过生产库里有几十个索引但核心查询的WHERE条件列一个索引都没有——因为建索引的人只给主键和唯一键建了。判断标准很简单打开慢查询日志找出那些rows_examined特别大的SQLWHERE条件里的列、JOIN的连接列、ORDER BY排序的列这三类就是索引的“第一优先级”。技巧2联合索引要遵循“最左前缀”原则联合索引是新手最容易犯迷糊的地方。(a, b, c)联合索引实际上等于建了(a)、(a, b)、(a, b, c)三个索引。所以查询里如果只用了b列或者c列索引就用不上。我常用的一个口诀联合索引的列顺序按“等值条件列优先、范围条件列其次、排序列最后”来排。注意这条口诀不是绝对的。如果某个列是范围查询比如、、BETWEEN把它放在前面会导致后面的列索引失效。例如(a, b)查询WHERE a 100 AND b 1b的等值条件用不上索引。遇到这种把等值条件列放前面更稳。技巧3覆盖索引能“让数据在索引里直接查到”覆盖索引是我个人最推荐的一项优化。它指的是查询的SELECT列、WHERE列、ORDER BY列全部包含在同一个索引内这样MySQL引擎只需要扫索引页不需要回表查聚簇索引。举个例子表里有id, user_id, order_amount三列查询SELECT user_id, order_amount FROM orders WHERE id 123如果建个(id, user_id, order_amount)的索引这条查询全程不碰数据行。实测下来覆盖索引能把查询耗时从几十毫秒压到几毫秒尤其在数据量千万级的时候。怎么判断是否命中了覆盖索引EXPLAIN输出里Extra列显示Using index就是命中了。2.2 查询语句写法同样的结果不同的代价技巧4-6技巧4只查需要的字段不要无脑SELECT *这条我强调过无数次。SELECT *会把所有列的数据从存储引擎捞出来传到Server层再做过滤——哪怕你只关心其中一列。数据行宽了回表成本、网络传输成本、排序临时文件体积全都会变大。有人反驳说“我们表就5个字段无所谓”等表扩到30个字段、数据量到千万级你就知道SELECT *有多疼了。只列出你真正用到的字段是零成本的优化。技巧5分页查询别用LIMIT 1000000, 20大分页是经典性能杀手。LIMIT 1000000, 20意味着MySQL要扫描前1000020行然后丢弃前1000000行代价极高。我常用的替代方案有两个延迟关联先只查出主键ID再用主键JOIN回原表取数据。基于游标的分页记住上一页最后一条ID用WHERE id 上一个ID ORDER BY id LIMIT 20。第二种方案最彻底但要求排序字段唯一且有序第一种方案通用性更强。实际项目中我用延迟关联把一次5秒的深分页查询降到了200毫秒。技巧6用EXPLAIN看执行计划慢之前就发现问题我要求团队里任何人写SQL之前必须先把EXPLAIN跑一遍。重点看四个字段type最好到ref或rangeALL是全表扫描要警惕、key实际用到的索引、rows预估扫描行数、Extra有没有Using filesort、Using temporary。一旦看到Using filesort或Using temporary就要立刻警觉这条SQL在排序或者建临时表数据量一大必慢。实操心得rows是预估数不一定准但rows和实际慢查询日志里的Rows_examined差距太大的时候说明统计信息过旧跑一次ANALYZE TABLE刷新统计信息往往能解决。2.3 表结构设计字段选错了后面全是债技巧7-9技巧7字段类型宁小勿大能定长尽量定长很多表设计者习惯性地上来就VARCHAR(255)主键一律BIGINT时间字段全部DATETIME。实际上状态、枚举、小小的数字用TINYINT就行别用INT。IP地址用INT UNSIGNED存配合INET_ATON/INET_NTOA转换比VARCHAR(45)省不少空间。时间字段能用TIMESTAMP就别用DATETIME——前者4字节后者8字节同样的索引前者能存更多key page扫描更快。技巧8避免NULL列的滥用理论上MySQL对NULL有处理逻辑索引对NULL的处理也有限制。我见过有人设计表时几乎所有字段都允许NULL结果每个查询都得额外判断。能用NOT NULL默认值的就设上默认值比如状态默认0、时间默认CURRENT_TIMESTAMP。这能减少存储层的判断也能避免WHERE col IS NULL走不好索引的问题。技巧9不要过度拆分表也不要单表无限膨胀很多人一听性能优化就想着“分库分表”。分库分表是最后的手段它带来的分布式事务、跨表JOIN查询、全局唯一ID问题任何一个都比性能问题更难处理。单表数据量在千万级以下、索引合理的情况下MySQL完全扛得住。真到需要分的时候优先考虑按时间归档历史数据、用分区表都比直接上中间件稳妥。2.4 让MySQL“少干活”缓存、排序与聚合技巧10-13技巧10排序别让数据库硬扛能走索引就走索引ORDER BY的列如果不在索引里MySQL就得把数据放到内存或磁盘做filesort。数据量小没事数据量大就直接让CPU和IO飙高。解决思路给排序列建索引或者减少排序的数据集先过滤再排序。技巧11GROUP BY和DISTINCT要看清“去重”逻辑热搜词里有“mysql的or能去重吗”这个问题其实就是DISTINCT和GROUP BY的区别DISTINCT是对查询结果去重GROUP BY是分组后再聚合。它们在执行计划里都可能产生临时表。优化方式给GROUP BY的列建索引或者用GROUP BY替代DISTINCT时注意索引匹配情况。另外UNION默认自带去重效果但会有排序去重的开销如果你业务上能接受重复数据用UNION ALL替代UNION性能提升极其显著。技巧12大结果集聚合考虑拆分批次比如统计一张千万级订单表的月销售额一个SUM下去可能扫描全表。我的做法是如果业务对实时性要求不高就建立汇总表按小时/天定时增量统计如果必须实时就缩小扫描范围比如只扫描当天的分区并配合覆盖索引。技巧13再利用OPTIMIZER_TRACE分析“优化器没选对索引”有的SQL明明有索引执行计划就是不走。我遇到过很多次是因为统计信息不准或者条件里写了函数导致索引失效。排查这类问题的利器是OPTIMIZER_TRACESET optimizer_traceenabledon; -- 执行你的查询 SELECT * FROM information_schema.OPTIMIZER_TRACE;它能显示优化器为什么会选择某个执行计划以及为什么放弃了某个索引。这个信息在常规的EXPLAIN里是看不到的。2.5 事务、锁与并发控制技巧14-16技巧14事务要短平快别把业务逻辑塞进事务里我踩过一个特别典型的坑代码里一个大事务包含几万行数据的更新还调了外部接口接口超时3秒事务就开了3秒以上直接导致连接池耗尽整个服务雪崩。事务的原则是能拆短就拆短只把必须原子化的操作放进去。长事务会持有锁不释放阻塞其他事务还让undo log无限膨胀。技巧15合理使用索引减少锁范围InnoDB的行锁是建立在索引上的。如果你的WHERE条件没有索引MySQL会走全表扫描把所有匹配的行都锁住——实际上因为扫描全表几乎相当于锁了全表。我处理过一个死锁案例根因就是DELETE语句的WHERE条件列没索引导致间隙锁范围扩大两个事务互相锁等待。给筛选列加索引是降低锁竞争最直接的手段。技巧16了解锁分类死锁了才知道往哪查MySQL的锁大致分为表锁、行锁、间隙锁、意向锁。间隙锁是RR隔离级别下防治幻读用的但它也是死锁的头号源头。排查死锁的固定流程SHOW ENGINE INNODB STATUS;看输出的LATEST DETECTED DEADLOCK段里面会记录两个事务各自的SQL和持有/等待的锁。我把这个输出取关键字“lock_mode”、“waiting”过一遍基本能定位到是哪两条SQL互相打架。日常预防死锁的方法多个事务访问同一组表的时候按相同顺序操作更新数据尽量走主键或唯一索引。2.6 配置与架构层面的兜底优化技巧17-20技巧17innodb_buffer_pool_size是内存里最值钱的一分钱这个参数决定InnoDB缓存表数据和索引的内存大小。建议设为机器物理内存的50%-70%但要留出足够余量给操作系统和连接线程。设置完用下面的SQL验证命中率SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read%;计算(Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests如果命中率低于95%说明缓存池太小或者数据访问太分散。技巧18慢查询日志必须常开阈值设在1秒以内很多人在生产上把long_query_time设成5秒、10秒等于把慢查询日志当摆设。我建议开发环境设成0.1秒生产也至少设1秒这样你能提前感知到索引失效、数据量膨胀等趋势。开启方法slow_query_log ON slow_query_log_file /var/log/mysql/slow.log long_query_time 1 log_queries_not_using_indexes ONlog_queries_not_using_indexes是我特别推荐的它会把所有没走索引的查询全部记录下来哪怕查询本身很快——这些才是潜在的地雷。技巧19连接数不是越大越好连接池要配合把max_connections从默认151调到1000看起来很豪横实际上每条连接都占用内存和线程资源连接数一上来CPU就飙了。真正要做的是应用层连接池控制活跃连接数比如HikariCP默认10个就够数据库端max_connections只作为兜底上限。我见过一个诡异的问题MySQL CPU不高但应用响应极慢最后发现是连接池配置了200个全挤在SLEEP状态把线程调度拖垮了。技巧20从EXPLAIN到PROFILING用数据说话遇到难缠的慢查询我会开PROFILING看每个阶段的耗时占比SET profiling 1; -- 执行慢SQL SHOW PROFILES; SHOW PROFILE FOR QUERY 1;输出里能看到Sending data、Sorting result、Creating tmp table各花了多少时间。比如我看到Sorting result占比70%就知道排序是瓶颈优先给排序列加索引看到Sending data占比高则考虑是不是扫描行数太多、需要优化WHERE条件。3. 实战排查一次慢查询的完整“缉凶”过程3.1 从慢查询日志里找“嫌疑犯”我最近处理的一个项目业务方反馈“报表查询越来越慢”慢查询日志里一眼就看到这条SELECT a.id, a.order_no, b.user_name, SUM(a.amount) FROM orders a LEFT JOIN users b ON a.user_id b.id WHERE a.created_at BETWEEN 2024-06-01 AND 2024-06-30 GROUP BY a.user_id ORDER BY SUM(a.amount) DESC LIMIT 50;日志显示扫描了800万行耗时11秒。这是个很典型的问题集大表JOIN、范围查询、GROUP BY聚合、ORDER BY排序全齐了。3.2 用EXPLAIN锁定问题点跑一遍EXPLAIN结果是这样的关键信息字段值说明typeALLorders表全表扫描rows8000000预估全表ExtraUsing temporary; Using filesort临时表文件排序两个信号非常明确ALL说明没用上索引Using temporary; Using filesort说明聚合和排序都在磁盘临时表里完成。3.3 一步步修复每步都验证效果第一步给created_at加索引ALTER TABLE orders ADD INDEX idx_created_at (created_at);但EXPLAIN依然显示rows很大——因为范围查询还是扫了全月的数据有600万行查询降到6秒。这不够。第二步改成覆盖索引我把索引改成(created_at, user_id, amount)让聚合需要的列都在索引里避免回表。查询降到2.5秒。但Using filesort还在因为排序的SUM(amount)是计算出来的索引帮不上忙。第三步去掉不必要的LEFT JOIN我发现user_name只是展示字段不需要参与过滤。于是改写为先聚合订单再关联用户SELECT t.id, t.order_no, u.user_name, t.total_amount FROM ( SELECT id, order_no, user_id, SUM(amount) AS total_amount FROM orders WHERE created_at BETWEEN 2024-06-01 AND 2024-06-30 GROUP BY user_id ORDER BY total_amount DESC LIMIT 50 ) t LEFT JOIN users u ON t.user_id u.id;子查询里只处理订单表聚合完只剩少量结果再JOIN用户表。这个版本跑到了300毫秒以内提速接近40倍。这个案例我复盘过很多次核心教训是不要一上来就调配置先用EXPLAIN把SQL本身的问题揪出来。这个查询改完索引结构SQL写法再用慢查询日志复查Rows_examined从800万降到60万问题彻底解决。4. 高频故障场景复盘锁、索引失效、深分页4.1 锁等待与死锁一条UPDATE引发的“雪崩”之前线上出现过一次大量“Lock wait timeout exceeded”报错。排查过程先SHOW ENGINE INNODB STATUS看锁信息发现两个事务都在争用同一张表的同一行。进一步看代码逻辑A事务先UPDATE订单再UPDATE用户B事务先UPDATE用户再UPDATE订单——两个事务的加锁顺序相反死锁条件成立。解决方案分两步应用层统一加锁顺序所有事务都先操作用户表再操作订单表。数据库层把隔离级别从REPEATABLE READ降到READ COMMITTED如果业务允许减少间隙锁的冲突概率。这能显著降低死锁发生频率但要注意需要业务侧确认“不可重复读”的影响可控。4.2 索引失效的四个“隐形杀手”我归纳了日常最容易导致索引失效的四个写法你们可以对照自查写法例子后果对索引列使用函数WHERE DATE(created_at) 2024-06-01索引失效全表扫描隐式类型转换WHERE phone 13800138000phone是VARCHAR类型转换导致索引失效前导模糊查询WHERE name LIKE %张无法用索引树定位OR连接非索引列WHERE id 1 OR status 0优化器可能放弃索引修复方式很简单函数式写法改成范围条件created_at 2024-06-01 AND created_at 2024-06-02字符串字段查询时带上引号前导模糊查询考虑全文索引OR的两边都能用索引时优化器才会考虑走索引。4.3 深分页LIMIT 500000, 20的性能拐点有个订单列表接口用户翻到第100页就开始卡。SQL长这样SELECT * FROM orders WHERE status 1 ORDER BY create_time DESC LIMIT 500000, 20;这条查询要排序后扫描50万行再丢弃。我改成延迟关联的写法SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders WHERE status 1 ORDER BY create_time DESC LIMIT 500000, 20 ) t ON o.id t.id;内层只查主键ID走create_time索引扫描的代价小很多外层用主键回表取20行。实测接口从4秒降到300毫秒。5. 常见问题速查表照着排查就行症状可能原因排查命令/动作解决方案CPU持续100%慢SQL、无索引扫全表慢日志、SHOW PROCESSLIST优化索引与SQL连接数打满长事务持有连接SHOW PROCESSLIST看SLEEP状态缩短事务、调整连接池死锁频繁加锁顺序不一致、间隙锁冲突SHOW ENGINE INNODB STATUS统一加锁顺序、降隔离级别查询越来越慢数据量膨胀、索引失效慢日志对比Rows_examined重新分析执行计划、整理索引分页越翻越慢深分页扫描过大EXPLAIN看rows延迟关联、游标分页磁盘IO高缓存命中率低、排序溢出Innodb_buffer_pool_read%调大buffer pool、优化排序主从延迟大事务、DDL阻塞SHOW SLAVE STATUS看Seconds_Behind_Master拆分大事务、错峰执行DDL这张表我贴在公司项目组的wiki上每次有人报“数据库慢”先让对表自查解决率很高。6. 额外建议把性能优化“前置”而不是“救火”在我自己的项目里加了三条强制规矩第一任何人提交SQL之前导出EXPLAIN贴到代码评审里第二所有新索引要说明是为哪条慢查询服务的避免无效索引泛滥第三每张核心表每季度跑一次ANALYZE TABLE和慢日志复盘。这套机制比任何单次优化都管用它让性能问题在开发阶段就被挡住。最后分享一个小技巧你在优化一个查询时改一步、验证一步、记录一步不要想着一次性把SQL、索引、配置全改完。我见过太多人一口气加了三个索引、改了两条SQL、调了四个参数结果变快了都不知道是哪一步的功劳回滚时更是灾难。性能优化的每一步都应该有数据支撑用EXPLAIN的rows、用慢查询日志的耗时做前后对比这比任何玄学都靠谱。