我做了这么多年的MySQL调优最深的体会是性能问题从来不是靠一个“大招”解决的而是靠一套组合拳。很多朋友一上来就问我“有没有什么参数调一下就能快十倍”说实话真有这种参数的话数据库厂商早就把它设为默认值了。真正可靠的优化路径是先把瓶颈找对再针对性地做索引、SQL、架构、配置这几层优化。这篇内容我来完整梳理一遍我做MySQL性能优化的实战思路把20个核心技巧拆开讲透。不管是刚入门的开发还是已经有一定经验的DBA读完应该都能有一套自己的排查套路和优化清单。我会把原理、实操、参数、坑点都揉在一起讲尽量说人话让你看完就能上手。1. 优化前的准备先找到病根再开药一上来就调参数、加索引那是瞎忙。MySQL性能优化的第一步永远是“定位”先搞清楚当前系统到底慢在哪里是某个SQL慢还是整体吞吐上不去还是并发一高就锁死。定位手段主要就两样慢查询日志和EXPLAIN。1.1 打开慢查询日志把“坏学生”揪出来慢查询日志是MySQL自带的“差生记录本”凡是执行时间超过阈值的SQL都会被记下来。很多线上环境默认没开这是很可惜的。你可以在不重启的情况下动态打开-- 查看当前状态 SHOW VARIABLES LIKE slow_query_log; SHOW VARIABLES LIKE long_query_time; -- 动态开启MySQL 8.0 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL log_queries_not_using_indexes ON;这里我用的是long_query_time 1也就是超过1秒的SQL都会被记录。具体阈值看业务如果是高并发核心系统建议0.5秒甚至0.2秒如果平时查询本身就重可以先从2秒开始逐步收紧。注意SET GLOBAL只对之后新建的连接生效所以要另开一个会话才查得到效果。还有一个容易被忽略的字段Rows_examined扫描行数。有时候一条SQL执行只要几十毫秒但扫描了几十万行这种SQL在数据量翻倍之后会突然变成慢查询。看日志不能只看耗时还得看扫描行数提前发现隐患。1.2 用EXPLAIN看懂执行计划别让SQL走弯路拿到慢SQL之后第一件事就是EXPLAIN看看MySQL到底是怎么执行这条SQL的。这里分享一个我实际排查过的案例EXPLAIN SELECT * FROM orders WHERE user_id 1001 ORDER BY created_at DESC LIMIT 10;如果type列显示ALL说明这是全表扫描key列是NULL说明没走任何索引。全表扫描的可怕之处在于它要一条条读数据数据量一大就崩。常见的type从好到差大概是system const eq_ref ref range index ALL。你至少要保证核心业务的查询走到ref或range如果看到ALL或index就得马上看索引有没有建对。Extra列同样关键。如果出现Using filesort说明排序没走索引数据量大时会在内存或磁盘上额外排序性能损耗不小。如果出现Using temporary说明查询用了临时表常见于GROUP BY或DISTINCT配合不当的情况。这两种都是优化信号后面都会详细说。2. 索引优化性能提升的第一杠杆索引是MySQL性能优化里性价比最高的一环。建对了索引一条300ms的SQL能直接降到5ms建错了索引不仅查询没变快还会拖慢写入。这一章我把索引的核心规则和实操细节讲清楚。2.1 复合索引的最左前缀原则顺序错了等于白建很多开发知道要建索引但不知道复合索引的顺序有讲究。MySQL的复合索引遵循“最左前缀”原则也就是查询条件里必须包含索引的最左列索引才会生效。举个实际例子。订单表常用查询是SELECT * FROM orders WHERE user_id 1001 AND status 1;如果建索引(status, user_id)这个查询用不上索引因为最左列status没出现在WHERE里。正确做法是建(user_id, status)。这里有个常见误区认为只要WHERE里包含这两列就行顺序无所谓。实际上MySQL 8.0之前对复合索引的使用非常依赖列顺序8.0引入了索引跳跃扫描Skip Scan但适用的场景有限不能指望它兜底。所以我的建议是把等值查询的列放前面把范围查询的列放后面。比如WHERE user_id ? AND created_at ?应该建(user_id, created_at)这样user_id能精确定位再在结果集里做范围过滤。2.2 覆盖索引让查询不用回表覆盖索引是指查询的列全部包含在索引中这样MySQL可以直接从索引拿到数据不用回表查聚簇索引。回表本身是一次随机IO数据量大时开销非常可观。举个例子。假设你有索引(user_id, status)执行SELECT user_id, status FROM orders WHERE user_id 1001 AND status 1;这条SQL的Extra会显示Using index意思就是覆盖索引生效。但如果把SELECT *改成查所有列索引里没有的列就得回表。所以一个实用的优化技巧是高频查询尽量把需要的列都塞进复合索引里用空间换时间。我踩过一个坑为了追求覆盖索引把一张表建了六七个复合索引结果每次写入要更新一堆索引树写入性能掉了近30%。后来我学乖了把索引控制在合理数量核心查询走覆盖索引非核心查询允许回表。这是个平衡问题不是越多越好。2.3 这些情况别用索引优化也要讲原则并不是所有场景都适合建索引下面几种情况建了索引反而添乱区分度低的列比如性别、状态字段只有两三个取值。MySQL优化器算下来觉得走索引还不如全表扫索引就等于白占空间。频繁更新的列。索引树要跟着数据一起更新写入热点列上挂索引等于每次写入都多几次磁盘IO。超长的VARCHAR列。索引长度有限制而且长列的索引树又大又慢。这种情况更适合用前缀索引比如只对前10个字符建索引。关于前缀索引操作很简单ALTER TABLE articles ADD INDEX idx_title (title(10));但要注意前缀索引不能用于覆盖索引和排序因为索引里存的是截断后的字符串。用之前要评估业务里对这条索引的使用方式别为了省空间牺牲了查询能力。3. SQL写法与查询设计同样的结果不一样的代价索引建好了SQL写法不对照样能把索引废掉。这一章讲SQL层面的优化技巧也是日常开发中最容易出问题的地方。3.1 SELECT * 与隐式类型转换两个经典坏习惯SELECT *是性能优化里最常被点名的问题之一。它有两个坏处一是查出来的列用不上白白增加了网络传输和内存消耗二是破坏了覆盖索引的生效条件。我见过很多系统明明索引设计得很好就因为SELECT *所有查询都在回表。正确的写法是只查你真正需要的列。比如订单列表页只需要order_no、amount、status就写这三个字段。别嫌啰嗦这不仅是性能问题也是代码可读性和接口稳定性的问题。另一个隐蔽的坑是隐式类型转换。当字段类型是VARCHAR但传入参数是数字时MySQL会自动把字段转成数字类型再比较这一转字段上的索引就失效了。看一个典型例子-- user_id 字段是 VARCHAR 类型传入数字 1001 SELECT * FROM users WHERE user_id 1001;这条SQL里的user_id是VARCHAR1001是数字MySQL会先对user_id做隐式转换导致索引失效变成全表扫描。解决方法是传入字符串SELECT * FROM users WHERE user_id 1001;排查这类问题也很简单用EXPLAIN看type列如果从ref掉到了ALL十有八九就是类型转换搞的鬼。3.2 分页优化与延迟关联深分页是性能杀手LIMIT分页越往后翻越慢这是所有做业务系统的人都遇到过的痛点。原因很好理解LIMIT 100000, 20需要先扫描前100020行再丢掉前100000行只留下最后20行。扫描的数据量跟偏移量成正比翻到第100页之后查询时间就会肉眼可见地涨。解决深分页问题的一个万能技巧是“延迟关联”也叫“子查询分页”。思路是先通过覆盖索引快速定位到需要的ID再用这些ID去关联原表拿完整数据-- 优化前深分页全表扫描 SELECT * FROM orders ORDER BY id LIMIT 100000, 20; -- 优化后先查ID再关联 SELECT o.* FROM orders o INNER JOIN (SELECT id FROM orders ORDER BY id LIMIT 100000, 20) t ON o.id t.id;内层子查询只查id列可以走覆盖索引速度非常快外层再用主键关联单行查询走聚簇索引整体性能提升明显。我实测过一个300万行的订单表同样的深分页查询优化前耗时1.8秒优化后只要40毫秒效果就是这么直接。如果分页还伴随排序比如ORDER BY created_at LIMIT 100000, 20我的建议是改造业务加一个“上一页最后一条记录的created_at”作为查询条件用范围查询代替偏移量。这是“键集分页”的思路数据量越大优势越明显。3.3 JOIN与子查询别迷信“连接比子查询快”网上流传着各种“JOIN比子查询快”的说法但这是个伪命题。MySQL的优化器会把部分子查询自动重写成JOIN所以两者很多时候是等价的。真正决定性能的是关联字段有没有索引以及驱动表的选取是否合理。以我的习惯来说能用JOIN的我会用JOIN但会注意两个原则一是被驱动表的关联字段必须有索引否则每行都要全表扫一遍二是小表驱动大表也就是用小结果集去驱动大结果集的扫描。-- user 是小表orders 是大表orders.user_id 必须有索引 SELECT u.name, o.order_no FROM user u INNER JOIN orders o ON u.id o.user_id WHERE u.status 1;这里的JOIN顺序通常由优化器决定但你可以通过STRAIGHT_JOIN强制顺序。不过说实话日常开发不建议手动干预优化器大多数时候比人聪明。只有在你确认优化器选错驱动表、且EXPLAIN结果有明显异常时才值得尝试。4. 锁与事务并发场景下的隐形瓶颈很多性能问题不是慢查询造成的而是锁等待。业务一并发各种锁的竞争就来了。这一章讲锁的分类、事务设计以及死锁的处理思路。4.1 锁的分类与监控搞清楚谁在卡谁MySQL的锁可以分为全局锁、表级锁、行级锁和间隙锁。日常开发里接触最多的是InnoDB的行级锁和间隙锁。行级锁并发性能好但间隙锁在RR可重复读隔离级别下会扩大锁范围容易引发锁等待。监控锁等待最直接的手段是查performance_schema-- 查看当前锁等待情况 SELECT * FROM performance_schema.data_lock_waits\G -- 查看InnoDB事务和锁信息 SELECT * FROM information_schema.innodb_trx\G常见的锁等待场景是事务A更新了一行没提交事务B也来更新同一行B就卡住了一直等到A提交或回滚。这种问题直接看innodb_trx表找到trx_state为RUNNING但长时间未提交的事务联系业务方处理就行。4.2 事务粒度与隔离级别别把事务写得又长又大事务是保证数据一致性的工具但用不好就是性能毒药。我见过最典型的问题是在一个事务里循环处理几千条数据每条都带一次查询和一次更新。这样的长事务会持有大量行锁导致其他事务大量阻塞还会让undo log膨胀影响数据库整体响应。优化方向很清晰事务只包裹真正需要原子操作的代码循环里的查询尽量提到事务外面。比如批量更新可以先查出来要更新的ID列表再一次性执行批量UPDATE。隔离级别的选择也要结合业务。MySQL默认是RR但实际上很多业务用RC读已提交就够了而且RC能减少间隙锁的使用降低死锁概率。如果确认业务可以接受RC可以考虑改SET GLOBAL transaction_isolation READ-COMMITTED;改之前一定要让业务方确认因为RC下同一个事务内两次查询可能看到不同结果对依赖可重复读的业务是不兼容的。4.3 死锁的产生与解决别慌MySQL会自动处理死锁是两个事务互相持有对方需要的锁又互不相让。MySQL的死锁检测机制会自动回滚其中一个事务让另一个继续执行。所以死锁本身不可怕可怕的是你的代码没有对死锁回滚做重试处理导致用户直接看到报错。我处理过的一个经典死锁场景是这样的-- 事务A UPDATE orders SET status 1 WHERE user_id 1001; UPDATE orders SET status 2 WHERE user_id 2002; -- 事务B UPDATE orders SET status 3 WHERE user_id 2002; UPDATE orders SET status 4 WHERE user_id 1001;两个事务都以相反的顺序更新同一批数据就很容易互相等锁。解决办法就是统一更新顺序比如都先更新user_id小的再更新大的。这个约定在业务代码层面就能解决不需要数据库层面做什么。另外一个思路是尽量缩短事务时间减少锁持有时间。锁持有时间越短死锁概率越低。如果排查到死锁频繁可以用SHOW ENGINE INNODB STATUS查看最近一次死锁的详细信息里面会列出两个事务分别持有什么锁、在等什么锁照着调整SQL顺序和事务大小就行。5. 参数配置与硬件基础给数据库调体质SQL和索引优化到一定程度之后就该看数据库本身的配置了。这一章讲InnoDB缓冲池、连接数和日志刷新策略这几个最关键的参数。注意这些参数不是越大越好也不是照抄网上的“万能配置”一定要结合机器内存和业务模型来定。5.1 InnoDB缓冲池让数据尽量留在内存里InnoDB缓冲池是MySQL读写数据的中转站所有数据页的读写都要经过它。缓冲池越大数据命中内存的概率越高磁盘IO就越少。对于专门的MySQL服务器一个常见的建议是把innodb_buffer_pool_size设为物理内存的70%左右。但要注意这只是个起点还要看机器上有没有跑其他服务。如果是云主机和业务应用部署在同一台机器上建议先从50%开始观察内存压力和命中率。看命中率的SQLSHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read_requests; SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_reads;命中率 读请求数 / (读请求数 实际读磁盘数)。如果命中率长期低于95%说明缓冲池可能偏小可以考虑调大。但要记住命中率不是唯一指标如果热点数据本身就不大命中率很高也不代表配置合理。5.2 连接数与连接池别让数据库被连接压垮连接数是另一个高频踩坑点。默认的max_connections是151很多并发稍高的系统就会报“Too many connections”。但直接把max_connections调到几千往往适得其反。因为每个连接都要占用内存和CPU资源连接数过多会让数据库整体响应变慢。我给一个务实的建议先算一下你的应用层连接池是怎么配置的。假设你的应用是Java的连接池最大50部署了4个实例那数据库至少需要200个连接。max_connections设置成“应用侧峰值连接数 管理维护余量”就够用了比如250到300。同时要注意wait_timeout和interactive_timeout。如果连接长期空闲不释放会白白占着连接数。可以适当调低这两个超时时间比如wait_timeout 60让空闲连接尽快释放。不过这个要根据业务特点来如果业务本身就有很多长连接过低的超时反而会导致频繁重连。5.3 日志刷新策略安全与性能的取舍innodb_flush_log_at_trx_commit是InnoDB最核心的权衡参数。它的取值和含义是参数值写入策略安全性性能1每次提交都刷盘最高最多丢1个事务最慢2每次提交只写操作系统缓存每秒刷盘中等操作系统崩溃可能丢1秒数据较快0每秒写入并刷盘低MySQL崩溃可能丢1秒数据最快默认值是1数据安全性最好但写入性能也最差。很多互联网公司对一致性要求不是极致苛刻的场景会改成2换来的写入性能提升非常明显。但凡是涉及钱、订单这类核心数据我强烈建议保持1别拿数据安全换性能。除了这个参数innodb_log_file_size也值得关注。日志文件太小会导致频繁触发日志切换和刷盘影响写入性能。MySQL 8.0里日志文件大小由一个innodb_redo_log_capacity参数控制默认是100MB对写入量大的业务来说偏小可以调到1GB或更大。调整之后要观察磁盘空间这个文件是预分配的。6. 高并发架构从单机到集群的升级路当单机MySQL的优化做到极致还是扛不住业务增长时就该从架构层面想办法了。这一章讲读写分离、分库分表和缓存这是走向高并发的三板斧。6.1 读写分离让主库专心写从库专心读大多数业务是读多写少读请求可能占90%以上。读写分离的思路非常简单主库负责写从库通过主从复制同步数据读请求打到从库上。我用得最多的部署方式是MySQL主从复制加MySQL Router或应用层读写分离。主从复制的配置不算复杂核心是binlog格式。MySQL 8.0默认是ROW格式配合GTID同步方式复制稳定性比传统的基于日志位置的方式好很多。一个必须注意的坑是主从延迟。从库的数据复制是异步的主库写完数据从库可能要几百毫秒甚至更久才能同步到。如果业务刚写完就要立刻读数据可能读不到。针对这个场景常见的做法是“强制路由”也就是写请求之后的读也走主库只有允许延迟的读才走从库。还有就是减少大事务和批量操作这些操作会拉长从库同步时间间接加剧延迟。6.2 分库分表最后的手段但必须懂当单表数据量到了几千万甚至上亿即使索引建得再好查询性能也会下降。这时候就该考虑分库分表了。分库分表的方案很多有垂直拆分和水平拆分。垂直拆分是把不同的业务表拆到不同的库里比如订单库、用户库、商品库各拆出来。水平拆分是把同一张表按某个规则拆到多个表或多个库里比如按用户ID取模分表。做分库分表最怕的是选错拆分键。拆分键选择的核心依据是“业务访问模式”也就是大部分查询是以哪个字段为条件。以订单表来说如果是C端用户查自己的订单就应该按user_id拆分如果是运营后台按照订单号查就应该按order_no拆分。一个表只能选一个拆分键其他字段的查询就会变成“扫全库”所以拆分键的选择要慎之又慎。分库分表还会带来一个经典难题全局唯一主键。单表的自增主键在分表后就不好使了。业界常见的方案是雪花算法、号段模式或者提前规划好ID生成器。这块一定要在上线前设计好否则后面改主键生成方案代价极大。6.3 缓存先行用Redis挡住热点读在分库分表之前还有一个性价比更高的方案加缓存。把高频查询的热点数据放到Redis里能挡掉大量数据库读请求。我在项目里用的模式是Cache Aside也就是先读缓存缓存没有就从数据库读然后把数据回填到缓存。写入时先更新数据库再删除缓存。这个模式看起来简单但里面有几个细节要注意。第一个细节是缓存击穿。某个热点key过期的一瞬间大量请求同时打到数据库。解决思路是加互斥锁只让一个请求去加载数据其他请求等待。也可以直接把热点key的过期时间拉长甚至设置永不过期。第二个细节是缓存雪崩。大量key同一时间过期导致数据库压力骤增。解决办法是在过期时间上加大随机值让过期时间分散开。比如基础过期时间5分钟再给每个key随机加0到60秒。第三个细节是缓存穿透。查询一个不存在的key缓存和数据库都没有请求直接打到数据库。解决办法是缓存空值并设置一个较短的过期时间。也可以用布隆过滤器先把存在的ID放到过滤器里查不到就快速返回不再打数据库。布隆过滤器实现起来稍复杂一些但效果很好。7. 实战问题排查与优化效果复盘这一章把我在实战中遇到的高频问题和排查思路整理成速查表再分享一个完整的优化案例过程。这部分内容来自一线踩坑的经验能帮你在遇到类似问题时少走弯路。7.1 实战排查速查表现象可能原因快速排查思路解决方向单条SQL突然变慢索引失效、统计信息过期EXPLAIN看type和key重建索引、ANALYZE TABLE整体响应变慢连接数打满、缓冲池命中率低查max_connections和Threads_connected调大连接数、优化连接池并发一高就死锁事务太长、更新顺序不一致SHOW ENGINE INNODB STATUS缩短事务、统一更新顺序主从延迟大大事务、DDL、从库性能差查Seconds_Behind_Master拆分大事务、升级从库写入性能差刷盘策略过于保守、索引过多检查innodb_flush_log_at_trx_commit权衡参数、精简索引内存占用过高缓冲池设置过大、连接数过多看内存监控调整缓冲池和连接数7.2 一个真实的优化案例全过程我在一个电商项目里遇到过典型的性能恶化过程订单表数据量到了800万行某天运营反馈后台订单列表打开要5秒以上。我先用了慢查询日志定位到最慢的SQL是一条ORDER BY created_at DESC LIMIT 20的深分页查询EXPLAIN显示type ALL走了全表扫描。原因是created_at虽然有索引但SELECT *导致覆盖索引失效排序也只能用Using filesort。我做的优化分了三步。第一步把查询SQL改为延迟关联方式先用覆盖索引查出主键ID列表再关联订单表取数据这一步直接让查询从1.8秒降到了80毫秒。第二步为运营后台最常用的筛选条件(status, created_at)建了复合索引让状态筛选和排序都能走索引。第三步把innodb_buffer_pool_size从默认的128M调到了4G命中率从80%提到了98%。优化之后后台列表接口从5秒降到了300毫秒以内。这个案例的关键不在于单独哪一步产生了奇效而在于先定位、再索引、再SQL、最后调参的完整链条。7.3 每个方案都要做效果验证优化完之后一定要验证效果而不是“感觉快了”。最简单的验证方式就是用EXPLAIN对比执行计划或者用PROFILING看执行耗时分布。SET profiling 1; SELECT * FROM orders WHERE user_id 1001 LIMIT 10; SHOW PROFILE FOR QUERY 1;SHOW PROFILE能看到整个查询在各阶段的耗时比如Sending data如果占比特别高说明数据传输或临时表操作偏重还能进一步优化。不过MySQL 8.0已经把SHOW PROFILE标记为废弃了官方推荐用performance_schema来替代但SHOW PROFILE在日常排查时仍然够用也很方便。有一点要提醒验证优化效果不能只在测试环境做一定在压测环境或线上低峰期做对比测试并且观察一段时间防止某些优化在数据分布变化后反而变差。根据我个人的习惯每次优化完我都会把“优化前耗时、优化后耗时、EXPLAIN前后对比”记录在一个文档里。这不仅是给自己积累经验也是将来做复盘和向团队汇报时最有说服力的材料。最后再分享一个实操心得MySQL性能优化最忌讳“一次改太多”。很多新手喜欢一口气调五六个参数、加四五个索引结果出了问题根本不知道是哪个改动引起的。正确的节奏是每次只改一个点验证一个点再改下一个点。这样每一步的效果都能量化出现问题也能快速回滚。记住这条原则你在性能优化这条路上能少踩一半的坑。