MySQL性能优化一直是后端开发和DBA绕不开的核心话题。很多项目初期跑得飞快一旦数据量过了千万级或者并发一上来慢查询、锁等待、CPU飙升各种问题就全冒出来了。这篇内容我从实际运维和调优的角度把常用的优化手段、排查思路和踩坑经验系统梳理一遍内容偏实战直接对着问题就能用。1. 性能问题定位先搞清楚瓶颈在哪很多人在优化时容易犯一个错误上来就改配置、加索引结果一顿操作猛如虎问题反而更严重。性能优化的第一步永远是定位瓶颈而不是盲目动手。1.1 监控指标怎么看MySQL的监控指标不少但真正需要重点盯的就那几类QPS/TPS每秒查询数和事务数。这个指标反映的是数据库的吞吐能力。如果QPS很高但响应时间还行说明数据库还能扛如果QPS不高但响应时间很长说明有慢查询拖累或者锁竞争严重。慢查询数量超过long_query_time阈值的SQL数量。这是最直接的优化线索来源。我习惯把阈值设为1秒超过1秒的查询都值得关注。生产环境建议同时开启log_queries_not_using_indexes没走索引的查询也会被记录下来这类SQL往往是隐患。InnoDB行锁等待通过SHOW ENGINE INNODB STATUS可以查看当前是否有锁等待。锁等待会导致事务串行化并发能力直线下降。如果看到大量LOCK WAIT相关的信息说明业务逻辑或索引设计有问题。临时表和排序如果SHOW GLOBAL STATUS LIKE Created_tmp_disk_tables这个值持续快速增长说明有大量查询在磁盘上创建临时表这类SQL通常需要优化。Buffer Pool命中率InnoDB的缓冲池命中率应该长期维持在99%以上。如果低于这个值说明内存配置不足或者数据访问模式有问题。1.2 用Explain定位慢SQL拿到慢SQL之后第一件事就是加EXPLAIN看执行计划。这里我建议直接使用EXPLAIN ANALYZEMySQL 8.0.18以上版本支持它不仅能显示执行计划还能给出实际执行时间和各步骤的行数比单纯看EXPLAIN的输出直观得多。重点看几个字段type列从好到差依次是systemconsteq_refrefrangeindexALL。看到ALL全表扫描或者index全索引扫描就要高度警惕。key列实际用到的索引。如果为NULL说明没走索引。rows列预估扫描的行数。这个值必须和实际数据量对比如果预估行数明显偏大可能是统计信息过期需要ANALYZE TABLE刷新。Extra列出现Using filesort说明排序没走索引出现Using temporary说明用了临时表这两者都是优化重点。1.3 基准测试做对比优化完成之后怎么证明有效不能靠感觉要用数据说话。建议每次优化前后跑同一组基准测试。工具用sysbench或者mysqlslap都行重点测三组场景只读并发、混合读写、高并发写入。例如在优化前后各跑一轮128线程的混合读写压测一轮5分钟对比TPS和99分位延迟。只有这两个指标都有明显改善优化才算真正生效。2. 索引优化实战最核心也最容易出错索引是MySQL性能优化的重中之重可以说80%的慢查询都能通过索引解决。但索引不是加得越多越好加错了反而拖慢写入。2.1 联合索引的最左前缀原则很多新手搞不懂联合索引为什么有时候走索引有时候不走。原因就是最左前缀原则MySQL只能从联合索引的最左列开始连续匹配。举个例子索引(a, b, c)查询条件只有b和c时无法使用这个索引。查询条件是a和c时a能走索引但c用不上因为中间隔着b。这里有个常见误区以为只要查询条件里有索引的第一个字段就行。实际上WHERE a 1 AND c 2虽然走了索引但只用了a这一列做等值匹配c是在回表之后才过滤的。如果你经常这么查更合理的索引是(a, c)去掉中间的b。2.2 覆盖索引的妙用覆盖索引是指查询的所有字段都包含在索引中不需要回表。这是优化查询的利器尤其适合高频查询。建一个联合索引(user_id, status, create_time)那么查询SELECT status, create_time FROM orders WHERE user_id 123就能直接通过索引返回结果完全不需要访问聚簇索引。我在实际项目中见过一个经典优化案例某个统计接口原来执行要800ms因为要回表查好几万行数据。后来把查询字段全部放进联合索引里利用覆盖索引优化直接把响应时间降到20ms以内。需要注意的是覆盖索引不宜盲目扩大。索引本质上是拿空间换时间如果为了覆盖索引把几十个字段都塞进去索引体积膨胀反而影响性能。2.3 索引失效的典型场景2.3.1 函数操作会导致索引失效对索引列使用函数会让索引失效。比如WHERE DATE(create_time) 2024-01-01MySQL无法直接使用create_time上的索引。正确写法是WHERE create_time 2024-01-01 AND create_time 2024-01-02让查询条件落在索引列本身的比较操作上。2.3.2 隐式类型转换如果字段是varchar类型查询条件写成WHERE phone 13800001111数字MySQL会把字段转换成数字再比较索引直接失效。改写为WHERE phone 13800001111字符串即可恢复。这条我踩过不止一次坑尤其是对外接口传参时经常因为类型不匹配导致本该走索引的查询全表扫描。还有一种隐藏的类型转换是字符集不一致两张表关联时如果关联字段分别是utf8mb4和utf8MySQL需要做转换索引也会失效。2.3.3 OR条件的陷阱WHERE a 1 OR b 2这种写法如果在a和b上分别建了单列索引MySQL 5.0之后引入了索引合并优化可能还能用。但如果是WHERE a 1 OR c 2c没有索引那整条查询只能用全表扫描。稳妥做法是用UNION ALL拆成两条WHERE a 1 UNION ALL WHERE c 2前提是a和c上都有索引且业务上允许重复时用UNION。2.3.4 LIKE前缀模糊匹配LIKE %abc无法使用索引因为需要从索引中间开始匹配。但LIKE abc%是可以走索引的。如果业务确实需要后缀模糊搜索考虑反向存储把字符串反转后存储查询时LIKE REVERSE(abc) || %就可以走索引了。2.3.5 不等于操作!或在某些情况下会导致索引失效因为B树索引天然支持等值和范围查询但对于不等于优化器可能认为扫描索引代价比全表还高尤其是在重复值较多的情况下。如果确实需要不等于查询且数据量很大可以拆成两次查询WHERE a 5 UNION ALL WHERE a 5。2.4 索引设计的取舍策略索引设计要结合业务查询模式来定不能机械套公式。我的经验是每个表索引数控制在5个以内过多索引会严重拖慢写入和UPDATE操作因为每次数据变更都要同步维护所有索引。区分度高、经常作为查询条件的字段优先建索引。区分度可以通过SELECT COUNT(DISTINCT col)/COUNT(*) FROM table来评估低于0.1的字段建索引效果很差。唯一索引可以用来兜底业务唯一性约束同时也能提升查询性能但要注意业务写入重复数据时唯一索引冲突会带来额外的锁开销。索引字段顺序有讲究把等值查询的字段放在前面范围查询的字段放在后面这样范围查询还能用上索引后面的字段进行排序避免Using filesort。3. SQL写法与执行计划调优索引有了SQL写得不合理照样性能拉胯。这一节说几个高频场景的优化技巧。3.1 深分页问题怎么破经典的LIMIT 1000000, 20会导致MySQL扫描1000020行然后丢弃前1000000行慢得离谱。常用的两种解法延迟关联SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders WHERE user_id 123 ORDER BY id DESC LIMIT 1000000, 20 ) tmp ON o.id tmp.id;子查询里只查主键id索引覆盖的情况下扫描就会快很多然后再用主键关联回原表取完整数据。游标分页键集分页适用于基于排序字段的分页把LIMIT换成基于上一页最后一条记录的定位SELECT * FROM orders WHERE user_id 123 AND id 1000000 ORDER BY id DESC LIMIT 20;这种方式每一页都只扫描20条性能非常稳定。3.2 避免SELECT *的隐藏收益SELECT *在数据量大时有两重危害一是需要回表查所有字段如果表宽I/O开销大二是无法使用覆盖索引优化。优化原则是只select需要的字段。一个真实案例原先SELECT * FROM order_items WHERE order_id ?执行需要250ms改写为只查业务必需的5个字段并把查询字段做成覆盖索引后执行时间降到5ms。3.3 关联查询和子查询的取舍在MySQL中子查询性能不总是比JOIN差特别是MySQL 5.6之后优化器做了大量改进。但有一点是确定的IN子查询和EXISTS在某些场景下性能差异巨大。经验法则外层表数据量小、内层表数据量大时用EXISTS更好外层表数据量大、内层表数据量小时用IN更好。MySQL优化器会自动做物化优化但这两类写法在复杂SQL里依然可能产生不一样的结果。JOIN优化要特别注意驱动表的选择。小表驱动大表是基本原则。如果两张表各100万行用小表作为驱动表只需要扫描100万次去匹配大表的索引反过来大表作为驱动表就要扫描大表100万次去做嵌套循环代价完全不同。通过STRAIGHT_JOIN可以强制指定驱动表顺序但一般不建议手动干预先让优化器自己选遇到明显离谱的情况再考虑。3.4 聚合查询优化GROUP BY经常伴随Using temporary; Using filesort因为MySQL需要把分组字段排序后建临时表。优化手段确保GROUP BY字段上有索引这样MySQL可以直接顺序扫描索引完成分组避免临时表和排序。如果只需要去重统计考虑用COUNT(DISTINCT)配合精确的索引。对超大表的统计需求如果允许数据延迟考虑用汇总表物化视图的思路定期把统计结果刷入汇总表。比如每天的订单统计凌晨跑一次任务把前一天的数据聚合好业务查询直接读汇总表响应时间从几十秒降到几毫秒。4. 参数配置与硬件资源调整花钱花在刀刃上参数配置优化虽然不如索引优化立竿见影但能让数据库在同等硬件下发挥更强性能。4.1 InnoDB缓冲池大小怎么算innodb_buffer_pool_size是MySQL最重要的性能参数它决定InnoDB缓存数据页和索引页的内存大小。业界经验值是物理内存的60%-75%左右但还需要考虑操作系统本身需要内存、连接数占用的内存、排序和临时表占用的内存。一个8核32G的机器MySQL实例可以考虑分配20G预留一部分给系统和连接。注意修改这个参数不是立即生效的需要在配置文件中修改并重启服务。MySQL 5.7及以上版本支持在线调整SET GLOBAL innodb_buffer_pool_size 20 * 1024 * 1024 * 1024;实际调整后建议观察SHOW ENGINE INNODB STATUS中的Buffer pool hit rate如果命中率过低还需要继续增大。4.2 连接数配置别踩坑max_connections默认151很多系统在高峰期会报Too many connections。但盲目调大也有风险每个连接都会占用内存和资源连接数太多反而拖垮MySQL。合理做法是先分析业务并发量估算高峰期同时执行的请求数再结合单连接内存开销约1-2MB计算出合理的max_connections。同时打开连接池应用层控制避免频繁创建和销毁连接。如果确实需要支撑大量连接考虑加一层中间件做连接复用应用连接池配置合理的话通常几百并发对MySQL来说压力不大。4.3 日志参数性能与安全的平衡innodb_flush_log_at_trx_commit这个参数有三个取值0每秒刷新日志到磁盘性能最好但进程崩溃可能丢1秒数据。1每次事务提交都刷新最安全但写入性能下降。2每次提交只写入操作系统缓存每秒刷新一次磁盘性能和安全的折中。对金融相关的业务必须用1不能为了性能牺牲数据安全。对普通业务可以视情况设置为2但前提是要能接受最多1秒的数据丢失风险。4.4 磁盘I/O优化如果压测时观察到iowait很高说明磁盘I/O是瓶颈。此时做几件事确认innodb_io_capacity参数设置是否合理对于SSD可以调到2000以上让InnoDB更积极地做预读和刷新。把二进制日志和数据文件分开存储分散I/O压力。如果预算允许使用NVMe SSD性能提升非常明显。我测试过同一套SQL在SATA SSD和NVMe上跑查询速度差距能有数倍。5. 表结构设计与归档策略预防胜于治疗很多性能问题在表设计阶段就埋下了运行期再优化有些为时已晚。所以有必要提一下设计侧的思路。5.1 冷热数据分离业务数据有生命周期不可能全量数据都保持高热访问。订单表里90%以上的都是历史订单没必要和最近订单混在一起。常用的做法是按时间维度做分区表。比如把订单表按月分区三个月内的数据在高性能分区更老的数据在低速分区。查询时带上时间条件分区裁剪Partition Pruning会直接跳过无关分区大幅减少扫描量。如果数据增长很快可以考虑定期归档把超过一年的数据迁移到归档表或者冷存储从主表删除。这个步骤对业务影响最小因为老数据已经不在活跃查询范围内。我在实际项目中做过一次订单表归档从5000万行缩到800万行主表查询性能提升非常明显。5.2 字段类型选择字段类型是表设计的基础。经验法则能用INT不用VARCHAR能用SMALLINT不用INT。例如状态字段用TINYINT而不是VARCHAR(10)存中文不仅省空间查询也更快。DATETIME和TIMESTAMP的选择上如果有跨时区需求用DATETIME否则TIMESTAMP更省空间。避免使用TEXT和BLOB存储在业务表中尤其是参与索引和排序的字段。大字段会导致聚簇索引的体积迅速膨胀回表成本飙升。大内容建议单独拆表用主键关联。所有字段尽量都加NOT NULL约束。NULL值的查询和索引处理都比空字符串复杂还可能引发统计信息不准。5.3 冗余字段设计第三范式的设计在OLTP场景下不一定是好的适度冗余可以大幅提升查询性能。举个实际的例子订单表和用户表关联查询每次都要JOIN获取用户名。如果在订单表冗余一个user_name字段虽然破坏了范式但用户信息基本不修改这个冗余是值得的可以省掉大量JOIN操作。冗余的原则字段不变或者极少变更、查询高频、数据量不大、无并发一致性问题。满足这些条件才做冗余设计。6. 常见问题排查与避坑实录最后这部分分享一些典型的故障场景和排查思路都是实际操作中遇到过的。6.1 慢查询排查完整流程一次典型的慢查询排查应该是这样走第一步开启慢查询日志并设置阈值持续观察一段时间收集慢SQL列表。第二步拿到慢SQL后先用EXPLAIN分析执行计划看是否走索引、扫描行数、是否有临时表和排序。第三步如果走了索引但还是慢看是不是回表太多。比如一条查询匹配了5万行索引记录每行都要回表那执行时间大概率不理想。此时考虑覆盖索引。第四步如果覆盖索引也加了还是慢看是不是数据量太大。千万级数据走普通索引扫描5万行在低配机器上可能就是500ms。此时需要回归业务需求看是否能减少结果集范围或者利用汇总表方案。第五步如果查询本身无法优化看是否能从业务侧减少对实时数据的要求比如加缓存层。这套流程走一遍90%以上的慢查询都能解决。6.2 并发写入导致的死锁死锁不是索引问题而是加锁顺序的问题。典型场景两个事务分别更新table_a和table_b但顺序相反导致互相等待。排查死锁的方法是SHOW ENGINE INNODB STATUS查看LATEST DETECTED DEADLOCK部分里面会显示两个事务的加锁顺序和等待的锁。常用的解决方案业务操作多个表时始终按固定的顺序加锁。比如总是先更新table_a再更新table_b从根上避免循环等待。尽量让事务短小减少持锁时间。大事务容易与其他事务产生锁冲突。如果批量更新注意UPDATE语句的WHERE条件尽量走索引否则会锁住大量行甚至整表。这一点容易被忽视UPDATE全表扫描的锁开销比SELECT严重得多。6.3 参数改完没生效MySQL有些参数是动态的改完立即生效有些是静态的必须改配置文件并重启。容易踩坑的是混合场景比如innodb_buffer_pool_size动态修改后设置文件和实际运行值不一致下次重启会回到默认值。所以任何动态修改都需要同步更新配置文件。还有一个坑连接池长连接在使用SET GLOBAL修改参数后旧连接不会感知新值。比如调整了sql_mode或max_allowed_packet老连接依然用旧配置。这一般不是大问题但要注意在变更后重启连接池或者重启MySQL服务。6.4 索引碎片问题MySQL的索引在频繁删除和更新之后会产生碎片导致统计信息不准和I/O效率下降。表现为明明走了索引但执行时间和rows预估明显不符合实际情况。解决方案是定期执行OPTIMIZE TABLE重建表空间消除碎片。注意这个操作会锁表在线业务需要在低峰期执行。MySQL 5.7及以上支持在线DDL但OPTIMIZE TABLE依然会短暂锁表。或者用ALTER TABLE table_name ENGINEInnoDB重建表。根据我个人的经验用量不是很大的表每季度或者半年做一次就够了高频写入的表每个月检查一次碎片率。写在最后的几点心得MySQL性能优化不是一个一次性的动作而是伴随业务发展的持续过程。数据量在增长、业务模式在变化昨天还合理的索引设计明天可能就是瓶颈。所以做优化的时候不但要解决当下问题更要建立一套监控和评估机制让后续的变更能及时发现、及时响应。我的习惯是每次上线新功能前先看SQL执行计划再评估对数据库的并发压力。有时候一条看似简单的SQL在数据量增长后会成为压垮数据库的最后一根稻草。多留个心眼多做一次EXPLAIN成本极低收益却很大。