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 ANALYZE（MySQL 8.0.18以上版本支持），它不仅能显示执行计划，还能给出实际执行时间和各步骤的行数，比单纯看EXPLAIN的输出直观得多。
重点看几个字段：
type列：从好到差依次是system>const>eq_ref>ref>range>index>ALL。看到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-01'，MySQL无法直接使用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和utf8，MySQL需要做转换，索引也会失效。
2.3.3 OR条件的陷阱
WHERE a = 1 OR b = 2这种写法，如果在a和b上分别建了单列索引，MySQL 5.0之后引入了索引合并优化，可能还能用。但如果是WHERE a = 1 OR c = 2，c没有索引，那整条查询只能用全表扫描。稳妥做法是用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 ENGINE=InnoDB重建表。
根据我个人的经验，用量不是很大的表，每季度或者半年做一次就够了；高频写入的表，每个月检查一次碎片率。
写在最后的几点心得
MySQL性能优化不是一个一次性的动作，而是伴随业务发展的持续过程。数据量在增长、业务模式在变化，昨天还合理的索引设计，明天可能就是瓶颈。所以做优化的时候，不但要解决当下问题，更要建立一套监控和评估机制，让后续的变更能及时发现、及时响应。
我的习惯是每次上线新功能前，先看SQL执行计划，再评估对数据库的并发压力。有时候一条看似简单的SQL，在数据量增长后会成为压垮数据库的最后一根稻草。多留个心眼，多做一次EXPLAIN，成本极低，收益却很大。
