MySQL数据库性能优化这件事，几乎每个后端团队都会遇到。业务跑着跑着，接口变慢了，数据库CPU飙高，慢SQL一条接一条，一到高峰期就提心吊胆。我接触过很多项目组，从单机小项目到分库分表的中型系统都踩过类似的坑。坦白说，MySQL优化的方法论并不复杂，核心就一句话：让数据库少干活。少扫描数据、少回表、少排序、少锁等待。这篇文章我把自己常用的优化路径完整捋一遍，包括先看什么、怎么定位、索引怎么建、SQL怎么改、参数怎么调、架构怎么做，适合正在被慢查询困扰的后端开发者，也适合刚接触数据库优化、想建立完整思路的读者。
1. 先定位再动手：慢查询、性能基线与常用工具
1.1 慢查询日志是第一步
很多人一听到“数据库慢”，第一反应就是改参数或者加内存，其实90%的情况下问题出在SQL和索引上，根本轮不到动配置。所以我的优化流程永远从定位开始，而定位的第一步就是打开慢查询日志。
SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 1;long_query_time设多少合适？生产环境一般设 1 秒，如果是核心交易库、接口响应要求很高，可以设 0.5 秒。不建议设太小，比如 0.1 秒，否则日志量会大到失去重点，全是些无意义的快查询。
日志位置可以通过SHOW VARIABLES LIKE 'slow_query_log_file';查看。打开之后，过一两天去翻日志，你会看到类似这样的记录：
# Query_time: 2.845239 Lock_time: 0.000032 Rows_sent: 10 Rows_examined: 1200000 SET timestamp=...; SELECT * FROM orders WHERE status = 0 AND create_time > '2024-01-01' ORDER BY id DESC LIMIT 10;这条日志信息量很大：查询只返回了 10 行，却扫描了 120 万行。说明什么？说明数据在磁盘里翻了 120 万行才捞出 10 条，典型的索引缺失或索引设计不合理。
慢日志多了以后，手动看太累，可以用自带工具归类：
mysqldumpslow -s at -t 10 /var/log/mysql/slow.log-s at表示按平均查询时间排序，-t 10取前 10 条。这样可以快速知道哪些SQL是真正的“销冠”。我见过不少团队连慢查询日志都没开，出了性能问题全靠猜，这种做法非常被动。建议所有核心业务库都打开慢查询日志，按月归档分析，长期跟踪才有数据、有方向。
1.2 性能指标先拉齐：别拿感觉当证据
慢日志解决的是“哪条SQL慢”的问题，但在动手之前，我习惯先把数据库的整体性能指标拉出来看一眼，确认瓶颈到底是CPU、磁盘IO、锁等待还是连接数不够。
常用的几个状态指标：
- QPS/TPS：
SHOW GLOBAL STATUS LIKE 'Questions'; - 当前连接数：
SHOW STATUS LIKE 'Threads_connected'; - InnoDB缓冲池命中率：
Innodb_buffer_pool_read_requests除以Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads - 磁盘临时表数量：
SHOW STATUS LIKE 'Created_tmp_disk_tables'; - 行锁等待次数：
SHOW STATUS LIKE 'Innodb_row_lock_waits';
还有一个非常实用的口径：看“扫描行数”和“返回行数”的比值。如果一条查询扫描了 50 万行只返回 20 行，那这个比值是 25000，说明索引设计大概率有问题。反过来，扫描 10 万行返回 10 万行，虽然绝对值大，但可能是合理的全表读取。
MySQL 5.7 以上可以用 sys 库快速拉出 SQL 消耗排行榜：
SELECT * FROM sys.statement_analysis LIMIT 10;这个视图会把平均扫描行数、平均耗时、执行次数都列出来，比纯看慢日志更全面。
不同瓶颈的表现也很有规律：
- CPU 高：逻辑读多，常见于全表扫描、大量排序、函数计算。
- 磁盘 IO 高：缓冲池命中率低、大量刷新脏页、或者慢日志落盘过频。
- 锁等待高：行锁争用、大事务长时间持有锁。
我的经验是：先看指标判断方向，再用慢日志和EXPLAIN精准定位。不要一上来就改innodb_buffer_pool_size这类参数，那是最后一步，不是第一步。
2. 索引优化：性价比最高的一步
2.1 B+树、回表和覆盖索引
MySQL 的 InnoDB 引擎用的是 B+ 树索引，可以把它理解成一本字典的目录页：你要查一个词，不用从第一页翻到最后一页，而是先查目录、再翻到具体页码。B+ 树把表数据按主键顺序组织起来，叶子节点存的是整行数据，这叫聚簇索引。
除了主键索引，其他索引都叫二级索引。二级索引的叶子节点不存整行数据，只存主键值。所以当你用WHERE name = '张三'去查，走的是 name 上的二级索引，找到主键后再回聚簇索引里取整行，这个过程叫“回表”。
回表是随机 IO，性能损耗很大。怎么避免？用覆盖索引——让索引里的字段覆盖你要查询的所有列。比如：
SELECT id, name FROM users WHERE name = '张三';如果name上有索引，而name和id都在索引里，那么 MySQL 可以直接从索引里拿到 id 和 name，不需要回表。EXPLAIN 里会出现Using index，意思是这个查询完全可以用索引覆盖。
所以我设计索引的时候，会先看查询要哪些字段，而不是只看 WHERE 条件。这往往是很多人忽略的点。
2.2 索引失效的常见场景
索引建了不等于一定生效。以下几个场景是我在线上排查时遇到最多的情况：
隐式类型转换。表里 phone 字段是 VARCHAR，查询写成WHERE phone = 13800000000，数字会被隐式转换成字符串，或者反过来，导致索引失效。我遇到过好几次，问题看起来一模一样，实际就是类型不匹配。解决办法是应用层把参数类型统一，或者用CAST显式转换。
函数包裹索引列。WHERE DATE(create_time) = '2024-01-01'会让 create_time 上的索引用不上。正确写法是范围查询：
WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02'这样不仅能用索引，而且范围还更清晰。
前导模糊匹配。LIKE '%关键词%'无法利用索引，因为 B+ 树的顺序性要求从左往右匹配。如果业务真的需要模糊搜索，建议走全文索引或外部搜索引擎，不要指望数据库。
OR 条件。WHERE status = 0 OR user_id = 123这种写法，如果两端字段上都有独立索引，MySQL 有可能走索引合并，但更多情况下会退化成全表扫描。性能敏感的查询，可以用UNION ALL拆开。
联合索引不满足最左前缀。建了(a, b, c)联合索引，查询只用到 b 或 c，索引就发挥不了作用。这一点在 2.3 里我会展开说。
每次写完 SQL，都养成习惯跑一下EXPLAIN，看 key 列是否为 NULL、type 是否为 ALL。这条习惯能帮你提前拦下大部分索引失效问题。
2.3 索引怎么设计
索引设计没有固定公式，但有三个基本原则。
第一，区分度高的字段放前面。COUNT(DISTINCT col) / COUNT(*)就是区分度。区分度高意味着通过这个字段能过滤掉大部分数据。比如订单表里，user_id区分度通常很高，status只有 0/1/2 几种，区分度很低。单独给 status 建索引往往效果很差，因为 1/3 的数据都长一个样。
第二，联合索引的顺序：等值字段优先，范围字段靠后。比如业务查询是WHERE user_id = ? AND status = ? ORDER BY create_time DESC，我会建：
ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time);为什么这个顺序？user_id和status都是等值查询，先过滤；create_time用于排序，放最后可以让排序也走索引，避免Using filesort。
第三，不要为了“万能”建一堆冗余索引。比如已经建了(a, b, c)，再建(a)就是冗余，因为(a, b, c)已经可以用 a 作为最左前缀。单表索引数量建议控制在 5-6 个以内。每个索引都要占用存储空间，写入时还要更新索引，过多的索引会让插入变慢。
我之前优化过一个订单流水表，查询条件是user_id + status + create_time，建联合索引后，一条原本扫描 120 万行的慢查询，变成扫描几百行，耗时从 2.8 秒降到 20 毫秒。这个效果不是靠调参能调出来的，就是索引的功劳。
3. SQL语句改写与执行计划解读
3.1 EXPLAIN怎么看
拿到一条慢SQL，我的固定动作是先EXPLAIN。不要急着改SQL，先看它现在的执行路径长什么样。
EXPLAIN SELECT * FROM orders WHERE status = 0 AND create_time > '2024-01-01' ORDER BY id DESC LIMIT 10;重点看四列：
- type：访问类型，性能从好到差是
const、eq_ref、ref、range、index、ALL。如果看到ALL，说明是全表扫描，基本可以确定索引没走对。 - key：实际用到的索引。如果为 NULL，说明没走任何索引。
- rows：预估扫描行数。这个数字和实际扫描行数越接近，说明优化器的判断越准。
- Extra：常见的有
Using filesort（文件排序）、Using temporary（临时表）、Using index（覆盖索引）、Using where（先取数据再过滤）。
我习惯先把rows和Extra放在一起看。如果 rows 上百万但最后只返回 10 条，说明查询像“在图书馆里一本书一本书地翻，最后只挑出 10 页”。这时候改索引或改写SQL的空间就很大。
MySQL 8.0 还可以用EXPLAIN ANALYZE看实际的执行时间和扫描行数，比EXPLAIN的预估值更准确，适合在测试环境排查复杂SQL时使用。
3.2 最常见的几种SQL改写手法
改SQL不是炫技，目的是减少扫描量、减少回表、减少排序和临时表。我常用的改写手段有这么几种。
SELECT * 改显式字段。只查需要的列，至少能让覆盖索引的命中率更高。尤其不要在大表上SELECT *，这会让 MySQL 不得不回表取整行。
深分页优化。这是后台列表页最常见的问题。LIMIT 1000000, 20这种写法会让 MySQL 扫描前 100 万行再丢弃，非常浪费。优化思路是“延迟关联”：
SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders WHERE status = 1 ORDER BY create_time DESC LIMIT 1000000, 20 ) t ON o.id = t.id;子查询只查 id 和 create_time，用覆盖索引快速定位到需要的 20 个 id，再回表取完整行。我在一个 600 万行的订单表上测试过，原 SQL 耗时 1.9 秒，改写后降到 0.3 秒。
OR 改 UNION ALL。条件字段上没有合适索引时，OR 容易触发全表扫描，拆成多个查询再合并，每个分支都可以独立走索引。
避免在索引列上做运算和函数。前端传参如果有格式要求，应用层先处理好再传，不要在 SQL 里靠函数兜底。
JOIN 字段类型保持一致。字符集不一致或者字段类型不一致，都会引发隐式转换，导致索引失效。关联表的时候先看两边字段定义是不是一模一样。
3.3 一个实际的慢SQL优化案例
说一个后台订单列表的案例。业务方反馈：翻到第 5000 页的时候，接口要等将近 2 秒。
原SQL大概是：
SELECT * FROM orders WHERE status = 1 ORDER BY create_time DESC LIMIT 100000, 20;orders 表在 status 和 create_time 上有联合索引，所以EXPLAIN能看到 key 不为空，type 是 range，rows 预估 12 万。问题出在LIMIT 100000——MySQL 需要先扫描 10 万行，再丢掉前 10 万行，只留下最后 20 行。
优化方案就是延迟关联，先把 id 拿出来，再关联原表取详情。改写之后，子查询走(status, create_time)联合索引，只扫描 100020 行里的索引条目，而不是回表取 10 万次。
再EXPLAIN一下，子查询的 rows 从 12 万降到 100020（其实还是那 10 万，但扫描的内容轻多了），耗时从 1.9 秒降到 0.3 秒。这个案例我后来在多个项目里都复用过，效果都很稳定。
提示：任何SQL改写，必须在测试环境验证结果集一致性。尤其分页、聚合、排序这类SQL，差一行数据都可能是事故。先记录旧SQL的结果集，再对比新SQL的结果集，两边一致才能上线。
4. 表结构与参数配置的取舍
4.1 表设计优化：类型、长度、范式
很多性能问题其实是表结构设计阶段埋下的雷。字段类型选得不好，索引再优化也救不回来。
能用整型就不要用字符串。状态、数量、类型这些字段，用 TINYINT、INT 比 VARCHAR 高效得多。整型比较快，占空间小，索引也更紧凑。
时间字段用 DATETIME 或 TIMESTAMP，不要用字符串存时间。我见过有人把时间存成'2024-01-01 12:00:00'的 VARCHAR，然后查询时用字符串比较。这样不仅无法高效范围查询，还容易因为格式不统一踩坑。用 DATETIME 之后，create_time > ?这种查询就能正常走索引。
TEXT/BLOB 大字段单独拆表。大字段会影响行存储的紧凑度，导致其他字段的查询效率下降。如果业务确实需要存长文本，建议拆到附属表，按主键关联查询。
范式与反范式要权衡。三范式不是教条。我优化过的订单查询场景里，订单表直接冗余了用户昵称，查询订单列表时少一次 JOIN 用户表。读多写少的场景，冗余字段换性能非常划算。代价是更新用户昵称时，需要额外更新订单表里的冗余字段，这个成本要提前评估。
分表要克制。单表数据量确实大了，先考虑索引优化和历史数据归档，不要一上来就分表。业务形态没定型之前分表，会引入中间件、分布式事务、跨表查询等一系列复杂度。我的经验是：单表数据量到千万级、索引和SQL优化都做过了还是慢，再考虑分表。
4.2 关键配置参数别乱调
很多人喜欢在网上找一份“最佳配置”直接粘到生产环境，这是很危险的做法。硬件环境不同、业务特征不同，参数最优解完全不同。我只说三个最核心、也最常被调错的参数。
innodb_buffer_pool_size是 InnoDB 的缓冲池，相当于数据库的“缓存仓库”，数据页和索引页都放在这里。这个参数通常建议设为物理内存的 60%-75%，但不是每台机器都合适。怎么判断？看命中率：
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests'; SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';命中率 = 读请求数 / (读请求数 + 读磁盘次数)。如果命中率低于 99%，内存又有富余，可以考虑逐步调大。注意是逐步，一次调太大可能和其他进程抢内存。
innodb_flush_log_at_trx_commit控制事务提交时日志的刷盘策略。值设为 1 时每次提交都刷磁盘，最安全，但性能开销最大；设为 0 时每秒刷一次，最快，但崩溃时可能丢最后一秒的数据；设为 2 是写入系统缓存，每秒刷盘，性能和数据安全性居中。
对账类业务、金融类业务，我建议老老实实用 1。日志、非核心业务，可以评估用 0 或 2，但必须让业务方知道数据丢失的容忍度。我见过一个团队为了性能把这个参数改成 0，结果一次断电丢了最近一秒的订单记录，业务方直接崩溃。
max_connections连接数上限。MySQL 默认值通常是 151，连接数调太高不一定好——每个连接都要消耗线程栈和缓冲区内存。线上如果经常报Too many connections，优先排查是不是有慢查询堆积、连接池配置不合理，而不是一味调高上限。
注意：参数调整一定基于监控数据，一次只改一个参数，改完观察性能变化再改下一个。复制网上的参数一顿乱改，生产环境 OOM 往往就是这么来的。
5. 架构层面：从单库到缓存的扩展路径
5.1 缓存要用得聪明：先顶住读压力
当数据库优化做到一定程度，单机性能已经到瓶颈，这时候要考虑架构层的手段。最先想到的通常是缓存。
缓存放哪里？Redis 是主流选择。使用模式我推荐 Cache-Aside：
- 读：先查缓存，命中直接返回；未命中查数据库，再把结果回填缓存。
- 写：先更新数据库，再删除缓存。
为什么写的时候是“删缓存”而不是“更新缓存”？因为更新缓存存在并发问题：两个请求同时写，后写的先更新缓存，先写的后更新，缓存里就留下了旧值。删除缓存则简单得多，下次读的时候自然会把新值回填进去。
用缓存还要处理三个经典问题：
- 缓存穿透：查询一个不存在的 key，缓存里没有，每次都打到数据库。解决方案是查不到结果也缓存一个空值，或者用布隆过滤器提前拦截。
- 缓存击穿：某个热点 key 过期瞬间，大量请求同时打到数据库。可以用互斥锁，或者让热点数据不过期。
- 缓存雪崩：大量 key 在同一时间到期，或者缓存实例宕机。过期时间加随机数可以避免同时过期。
我之前优化过一个商品详情接口，QPS 2000 时数据库连接池被打满，接口超时率很高。加上 Redis 缓存后，QPS 涨到 8000，数据库压力几乎降为 0。缓存对读多写少场景的提升是立竿见影的，但它不是银弹——写入频繁、每次写入后都要立刻读取的数据，缓存的意义就不大，反而增加一致性维护成本。
5.2 读写分离与主从延迟
读多写少的业务，数据库读压力大了以后，第二个常用手段是读写分离。
结构上就是主库负责写，从库负责读，可以挂多个从库分担读流量。实现方式可以自己写路由规则，也可以引入中间件。我的建议是：一开始不要上重中间件，先做好主从复制监控，应用层把写请求路由到主库、读请求路由到从库，等规模确实撑不住了再考虑中间件。
读写分离最大的坑是主从延迟。刚写完主库的数据，马上从从库读，可能读不到。解决办法：
- 关键实时性要求高的请求，强制走主库。
- 从库延迟过大时，自动摘掉该从库的读流量。
- 对延迟不敏感的业务，前端提示用户稍后刷新即可。
监控主从延迟用这条命令：
SHOW SLAVE STATUS\G看Seconds_Behind_Master字段，这个值越大说明延迟越严重。没有做好延迟监控就上线读写分离，容易出线上事故，这个问题我在不止一个团队见过。
5.3 冷热数据分离与归档
最后一个架构手段是冷热数据分离。本质上，业务数据里真正高频访问的只是很小一部分，大部分历史数据并不会被频繁读取，但躺在主表里会拖慢索引维护和全表扫描的速度。
做法是：主表只保留近 6 个月或 1 年的数据，历史数据定期归档到历史表、归档库或者数据仓库。应用层按时间参数分流查询。
我优化过一个订单查询系统，订单表有 8000 万行，查询近 3 个月订单的接口经常超 1 秒。归档了 6 个月之前的数据后，主表降到 1200 万行，查询响应时间稳定在 100 毫秒以内，全表扫描的代价也小了很多。归档操作本身要有监控，尽量在业务低峰期执行，避免大批量迁移占用数据库资源。
6. 常见问题与排查技巧实录
6.1 典型问题速查表
下面是我在实际排查中经常遇到的几类问题，整理成速查表，方便对应症状快速定位。
| 症状 | 可能原因 | 优先排查方向 |
|---|---|---|
| CPU 持续飙高 | 大量全表扫描、排序、热点行竞争 | SHOW PROCESSLIST、慢日志、EXPLAIN |
| 磁盘 IO 高 | 缓冲池命中率低、大量刷新脏页 | 查看Innodb_buffer_pool_reads、iostat |
| 连接数打满 | 慢查询堆积、连接池配置太小、连接泄漏 | processlist 状态、慢日志、连接池监控 |
| 单条 SQL 偶尔慢 | 统计信息过期、索引选择错误 | ANALYZE TABLE、重新 EXPLAIN |
| 固定时间段变慢 | 定时任务、备份任务、缓存集中过期 | 对比任务执行时间和慢SQL时间线 |
| 主从延迟持续增大 | 从库性能不足、大事务、网络延迟 | 查看Seconds_Behind_Master、开启并行复制 |
| 事务锁等待多 | 长事务、批量更新、热点行更新 | 查information_schema.innodb_trx |
这张表不是万能的，但能帮你从“不知道从哪里查起”快速进入定位状态。我每次处理线上问题都会先对号入座，再决定下一步动作。
6.2 避坑经验与工作习惯
最后分享几个我从实战里总结的习惯，这些习惯救过我很多次。
优化前先留证据。慢SQL文本、EXPLAIN结果、当前表结构、优化前后耗时，全部截图或记录存档。改完以后对照数据，才能判断优化是否有效。
上线顺序要稳。加索引、改SQL都要先在测试环境跑，验证结果集一致。生产环境操作要选低峰期，准备回滚方案。加索引本身不复杂，但大表加索引可能会锁表或长时间占用IO，需要评估影响。
定期ANALYZE TABLE。表数据变更剧烈时，MySQL 的统计信息可能失真，优化器会选错索引。定期执行ANALYZE TABLE可以刷新统计信息，成本很低，收益很大。
不要随便用FORCE INDEX。强制走某个索引，短期内可能变快，但一旦数据分布变化，那个索引可能变成最差选择，反而导致性能暴跌。FORCE INDEX只建议用来临时验证方案，不建议留到生产长期使用。
每月复盘慢查询 Top 10。数据一直在变，SQL 模式也在变。把优化当成持续过程，而不是一次性的救火行动。
我做了这么多年数据库相关工作，最深的感受是：MySQL 性能优化不是玄学，也不是靠某个参数一锤定音，而是一条“采集证据—定位瓶颈—做最小改动—验证效果”的闭环。索引和 SQL 写法是性价比最高的两块，缓存和架构是放大手段，参数调整只是最后的微调。遇到问题别慌，先记录下来，一步步拆，绝大多数慢查询都能在索引和 SQL 层面解决。最后再提醒一句：任何优化动作上线前都留好回滚余地，数据库层面的变更，谨慎永远不过分。
