我处理过太多“MySQL 变慢了怎么办”的问题，也见过太多人第一反应就是调my.cnf、加缓存、上分库分表，结果折腾一圈下来，瓶颈压根不在这。有一条 SQL 把线上接口拖到 3 秒，排查之后发现就是少了一个普通索引；也有项目把连接池调到 500，数据库线程切换开销反而把 CPU 打满。MySQL 数据库优化这件事，说到底是三个字：找瓶颈。本文不是教科书式的参数罗列，我会按照真实排查的顺序，把从索引设计、慢查询分析、EXPLAIN 解读，到配置与架构取舍的完整思路拆开讲。适合后端开发、运维和刚接触 MySQL 调优的初级 DBA，你完全可以照着这个排查链路去处理自己项目里的慢查询。
1. 先别急着调参数：把瓶颈定位这步做扎实
很多优化失败的案例，根源在于还没搞清楚“慢在哪一环节”就开始动手。MySQL 一条查询从客户端发出到最后返回结果，要经过连接层、解析器、优化器、存储引擎、操作系统磁盘这几个环节，每个环节的瓶颈表现完全不一样，改法也完全不同。
1.1 慢查询背后：CPU 打满和磁盘排队是两回事
先说两个我实际遇到过的场景。
第一个场景：接口变慢，top一开，多个 CPU core 接近 100%，iostat的%util却很低。这种情况通常意味着查询本身在做大量计算，比如扫描了海量行做过滤、排序、分组、类型转换，典型原因就是缺索引或 SQL 写法让索引失效，数据库不得不把几百万行捞出来一层层算。此时你把innodb_buffer_pool_size翻倍，毫无意义。
第二个场景：top里 CPU 不高，但iowait高得吓人，磁盘await时间长。这种情况多半是数据量已经超过内存缓冲池能容纳的范围，或者查询要访问的数据没有缓存在 buffer pool 里，每次都要落到磁盘。还有可能是排序临时文件、binlog 刷盘等操作带来的 IO 压力。此时调大buffer_pool、优化刷盘策略才有意义。
1.2 我自己固定的一套优化优先级
踩过几轮坑之后，我给自己定了一条排序规则，也建议你照着来：
- SQL 与索引优化：零成本、见效最快，能解决 80% 的性能问题。
- Schema 结构优化：改字段类型、拆分大字段、合理冗余，解决另 10% 的问题。
- MySQL 配置优化：调 buffer、刷盘策略、连接数，属于放大已有能力的操作，边际收益有限。
- 架构层优化：加缓存、读写分离、分库分表，成本高、风险大，是最后手段。
为什么把配置放在这么后面？因为大多数线上慢查询，优化器根本没选到好索引，或者 SQL 本身要处理太多没必要的数据，参数再大也只是把“垃圾处理能力”变大，垃圾还是在。
1.3 定位瓶颈需要盯住的三类观测手段
我平时排查一个“数据库变慢”的工单，基本不会上来就翻配置，而是按顺序看这些：
SHOW PROCESSLIST：先看当下有没有大量长时间Sending data、Sorting result、Copying to tmp table状态的会话。状态本身就说明了瓶颈环节。SLOW QUERY LOG：看历史慢查询分布，避免只处理当下单个偶发问题。performance_schema和sys schema：MySQL 5.7+ 自带的诊断视图非常实用。比如sys.session可以看连接来源，sys.io_by_thread_by_latency可以看出哪些线程在等 IO。
我之前处理过一个“每天凌晨 3 点数据库卡死”的问题，SHOW PROCESSLIST看到大量Sending data，顺着sys.session查到来源是某个后台报表任务，SQL 里对一张 8000 万行的表做了COUNT(DISTINCT)且无索引，最终解决方案就是给字段加索引，并把这个报表任务挪到从库跑。整个排查时间不到半小时，如果当时先去调max_connections，只会让问题更严重。
2. 索引设计：收益最高也最容易出错的一层
索引是老生常谈，但绝大多数“建了索引还是慢”的案例，问题都出在理解不完整。我只讲实战中最核心的三件事：回表与覆盖索引、联合索引字段顺序、索引失效的常见写法。
2.1 回表与覆盖索引：为什么SELECT *在优化时那么扎眼
InnoDB 的索引结构是 B+ 树，主键索引的叶子节点存的是整行数据，普通索引（二级索引）的叶子节点存的是主键值。也就是说，你通过普通索引查到一行数据时，如果查询的列不在这个索引里，数据库还得拿着主键再回主键索引查一次，这叫回表。
回表本身不一定慢，但如果查询命中了大量行，每一行都要回表，IO 次数就是翻倍的。举个具体例子：
SELECT id, order_no, status FROM orders WHERE status = 1;如果只在status上建了单列索引，而order_no不在索引里，那么每行匹配记录都要回表取order_no。更尴尬的是，status = 1可能命中了 20 万行，这 20 万次回表就是实打实的随机 IO。
解决方案是覆盖索引：让查询要的所有列都包含在同一个索引中，这样索引叶子节点上就有全部数据，不需要回表。比如可以建联合索引(status, order_no)，此时status = 1命中的记录，在索引里直接就能取出order_no。
这也是我坚持在生产 SQL 里少写SELECT *的原因之一——*必然要求所有列，几乎不可能被覆盖索引完全覆盖。只查询业务需要的列，才有机会利用覆盖索引消除大量回表。
2.2 联合索引字段顺序：最左前缀和区分度怎么权衡
联合索引的字段顺序直接决定索引能否被用到、用得多高效。很多人背了“最左前缀”原则，但实际设计时还是会纠结：到底哪个字段放前面？
我的经验有三条：
- 等值条件字段放前面。
WHERE status = 1 AND created_at > '2024-01-01'，把status放前面，等值匹配后再用索引范围扫描created_at，效率最高。 - 区分度高的字段优先。如果两个都是等值条件，区分度高的放前面，能更快缩小扫描范围。比如
WHERE user_id = 123 AND status = 1，user_id区分度远高于status，就应该(user_id, status)。 - 用于排序的字段尽量放进联合索引。
ORDER BY id DESC这类排序如果走文件排序，代价不低。如果联合索引的末尾正好是排序字段，且排序方向一致，MySQL 可能直接按索引顺序读取，消除filesort。
举一个真实调优的案例。某订单列表接口的 SQL 长这样：
SELECT id, order_no, amount FROM orders WHERE user_id = 1001 AND status = 1 ORDER BY create_time DESC LIMIT 20;原索引是单列(user_id)。查询计划显示用到了user_id，但Extra里出现Using filesort，因为create_time不在索引中，得对匹配出的所有行排序。把索引改成(user_id, status, create_time)后，等值条件命中走索引，排序也能直接按索引顺序读取，filesort消失，查询时间从 80 毫秒降到 2 毫秒。
有人会问：status区分度那么差，放中间会不会浪费？这里要理解，联合索引的生效顺序是：从最左字段开始，能匹配就继续往下走。只要最左字段是等值匹配，MySQL 就可以顺着 B+ 树定位到很小一个范围，后续字段即便区分度低，也只是在同一小范围内继续过滤，代价很小。
2.3 索引失效的写法：不是索引不工作，是你让它没法工作
我总结了几个高频踩坑写法，任何一个都会让 MySQL 放弃走索引：
- 隐式类型转换。字段是
VARCHAR，查询条件却传入数字：WHERE phone = 13800138000。MySQL 会把字段转成数字再比较，导致索引失效。反过来，字段是INT，条件传字符串反而问题不大，但为了统一，建议参数类型和字段类型严格一致。 - 对索引字段使用函数或表达式。
WHERE DATE(create_time) = '2024-01-01'会让索引失效。正确做法是写成范围条件：WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02'。 LIKE '%keyword'左模糊。前缀不确定，B+ 树就没法定位，索引自然用不上。如果业务确实需要后缀搜索，考虑使用全文索引或搜索引擎。OR连接非索引列。WHERE status = 1 OR remark = 'x'，只要remark没索引，整个条件就可能退化成全表扫描。尽量改成UNION ALL，或者保证每个 OR 分支都有索引。!=、NOT IN等否定条件。这类条件往往让优化器认为扫描比例太高，选择全表扫描。
判断 SQL 有没有走对索引，不要靠猜，直接看执行计划。
3. EXPLAIN 别只盯着type列：这些字段才是排查关键
EXPLAIN是每个做 MySQL 优化的人必须熟练掌握的工具。但很多人只会看type，见到ALL就认为“全表扫描了，完蛋”，见到range就觉得“还行”。实际上，type只是表象，真正决定性能的还得结合rows、filtered、Extra一起来看。
3.1rows和filtered：优化器估算的扫描行数到底怎么看
EXPLAIN输出里的rows是优化器估算的需要读取的行数，filtered表示经过 WHERE 条件过滤后剩余行数的百分比。两者相乘大致就是最终返回的行数规模。
举个例子：
EXPLAIN SELECT * FROM orders WHERE status = 1 AND amount > 100;如果结果里type = ref、rows = 500000、filtered = 10，意味着即使走到了索引，预估扫描行数仍有 50 万行，过滤后 5 万行。这个量级对一次在线查询来说已经不小了。这时候该思考的是：能不能再缩小扫描范围？比如在(status, amount)上建联合索引，让范围条件也参与索引定位，rows就会明显下降。
我见过不少案例：type是漂亮的ref，但rows几十万，接口照样慢。优化完看rows从几十万降到几百，才真正解决问题。
3.2Extra列里的三个危险信号
Extra列有很多取值，优先级最高的是这三个：
Using filesort：查询结果集需要额外排序，数据量大时非常拖性能。解决办法就是上文说的，把排序字段纳入索引。Using temporary：查询需要使用临时表，常见于GROUP BY和DISTINCT。数据量一大，临时表会落到磁盘，性能急剧下降。优化方向是让GROUP BY字段走索引，或者改写 SQL。Using index：这是好消息，表示查询用到了覆盖索引，不需要回表。如果Extra同时出现Using where; Using index，说明既用索引过滤又无需回表，是最理想的状态之一。
我遇到过一条统计 SQL：
SELECT user_id, COUNT(*) FROM order_detail GROUP BY user_id;EXPLAIN显示type = index，Extra = Using index; Using temporary; Using filesort。表面走了索引，但临时表和文件排序都来了。原因是这条 SQL 只查user_id和COUNT(*)，如果有一个以user_id开头的索引，理论上可以走覆盖索引避免回表，但分组和排序仍然需要。最终我调整了业务逻辑，把高频统计提前在事务里维护到冗余计数表，查询直接取数，耗时从秒级降到毫秒级。
3.3 从执行计划到修复：一个 JOIN 查询的完整复盘
有一回线上反馈订单详情页打开要 15 秒，SQL 大致是：
SELECT o.id, o.order_no, u.nickname FROM orders o LEFT JOIN users u ON o.user_id = u.id WHERE o.status = 1 ORDER BY o.id DESC LIMIT 20;EXPLAIN结果里，orders表type = ALL、rows = 8000000，users表type = ALL、rows = 2000000。两个表都全表扫描，再排序，不慢才怪。
排查链路是这样的：先看orders.status是否有索引——有，但选择性太低，优化器认为再用索引过滤也剩很多行，干脆全表扫。再看users.id是主键，理论上 JOIN 应该走eq_ref，为什么是ALL？仔细一看，EXPLAIN第一行是驱动表orders，第二行是被驱动表users，但由于驱动表全表扫描产生的行数估算过大，优化器认为嵌套循环代价高，直接选择了另一种低效的执行方式。
修复方案分两步：第一步，在orders(status, id)上建联合索引，让WHERE status = 1 ORDER BY id DESC LIMIT 20能直接从索引里顺序取 20 行；第二步，LEFT JOIN改成INNER JOIN（业务允许的情况下），让优化器有更多执行计划可选。改完后查询时间基本在几十毫秒上下。
这类问题给我们的教训是：执行计划是优化器基于当前索引统计信息算出来的，索引设计不合理，执行计划就不可能好。
4. 慢查询日志与分析链路：让问题自己现形
优化不能永远靠“线上慢了才被动排查”。把慢查询日志开好、定期分析，是 MySQL 性能管理的基本功。
4.1 慢查询日志怎么开，阈值设多少
慢查询日志默认关闭，可以用以下配置开启：
slow_query_log = ON slow_query_log_file = /var/log/mysql/mysql-slow.log long_query_time = 1 log_queries_not_using_indexes = ONlong_query_time = 1表示超过 1 秒的 SQL 会被记录。注意，这个值是“累计执行时间”，但对一个高频接口而言，即使单条只要 500 毫秒，如果每秒调用 100 次，数据库压力也很大。所以我一般建议：
- 开发环境：
long_query_time设为 0.1 秒，便于尽早发现性能隐患； - 生产环境：先设 1 秒跑上一两周，观察整体分布；如果业务量很大，再压到 0.5 秒。
log_queries_not_using_indexes这个开关很值得开，它会把没走索引的 SQL 也记下来。但开了之后日志量会变大，建议配合log_throttle_queries_not_using_indexes限制每分钟最多记录多少条同类型 SQL，不然磁盘容易被打满。
4.2 分析工具的选择：自带的够用，专业的上更值
MySQL 自带mysqldumpslow，可以按执行次数、耗时等维度做聚合。比如：
mysqldumpslow -s t -t 20 /var/log/mysql/mysql-slow.log这条命令按平均耗时排序，输出前 20 条。mysqldumpslow的优点是零依赖，能快速找出“最慢的几条”。缺点是它按 SQL 文本归类的能力比较粗糙，参数值不同会导致同一类 SQL 被拆成多条。
如果你有权限装 Percona Toolkit，pt-query-digest是分析慢查询日志的利器。它能把带不同参数值的 SQL 归一化归类，输出每个指纹的总耗时、执行次数、平均耗时，还带执行计划建议。实际使用中，它能非常快地帮你找出“单条不慢但量极大”的 SQL——这类才是拖垮数据库的隐形杀手。
4.3 一次“单条不慢但量极大”的实战复盘
有一段时间某服务数据库 CPU 时不时冲到 80%，看慢日志单条 SQL 最慢也就 1.2 秒，这个量级看起来不致命。我拿pt-query-digest分析后发现，有一条 SQL 每分钟执行几万次，总计耗时在所有 SQL 中占比超过 50%。
这条 SQL 是典型的 N+1 问题：应用层先查出一批订单，再循环查每个订单的详情，每次查询几百条到几千条不等，量一叠加，数据库直接被打爆。修复方式很直接：把循环内查询改成一次IN查询，或者使用 JOIN 一次取完。改造前后，同样业务场景下的数据库 CPU 使用率下降了 70% 以上。
从那以后我养成了一个习惯：分析慢查询日志时，从来不看单条最慢的，而是看总耗时 Top 20，这一眼就能识别出真正值得优化的 SQL。
5. 配置优化：真正值得动的参数就那几个
我见过很多my.cnf被改得面目全非的项目，各种参数堆得满满当当，实际效果却没多大。MySQL 配置参数动辄几百个，你不需要全部理解，但下面这几个是真正能在常规场景下带来明显收益的。
5.1innodb_buffer_pool_size：这只“内存大鱼”该怎么喂
InnoDB 的 Buffer Pool 是 MySQL 缓存表和索引数据的内存区域，相当于数据库自己的page cache。读请求命中了 Buffer Pool，就不用落盘；没命中，就要做磁盘 IO。
一个很实用的确认方法：
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';重点关注Innodb_buffer_pool_read_requests（总读请求次数）和Innodb_buffer_pool_reads（从磁盘读的次数），命中率通常建议 99% 以上。如果达不到，优先调大 Buffer Pool —— 当然，服务器内存是前提。
Buffer Pool 设置多少合适？经验值：单独部署 MySQL 的机器，可以给到物理内存的 50%~70%，但要留出足够内存给数据库连接、排序缓冲、OS 文件缓存以及其他进程。比如一台 64GB 内存只跑 MySQL 的机器，我给 40GB 是一个比较稳妥的取值范围。MySQL 5.7+ 支持在线调整：
SET GLOBAL innodb_buffer_pool_size = 40 * 1024 * 1024 * 1024;生产环境先在线调观察几分钟，确认内存没问题再写进配置文件，比直接改配置重启要安全得多。
5.2 刷盘策略：安全和性能的平衡点
innodb_flush_log_at_trx_commit这个参数决定了事务提交时 redo log 的刷盘方式：
1：每次事务提交都要把 redo log 刷到磁盘。最安全，任何崩溃场景下数据不丢，但磁盘 IO 压力大。2：每次事务提交只把 redo log 写到 OS 缓存，由 OS 定期刷盘。性能比1好，但操作系统崩溃时可能丢最近 1 秒的数据。0：每秒刷一次，数据库崩溃可能会丢最多 1 秒的数据。
我之前把一个订单量很大的业务从1改成2，配合sync_binlog=1000（二进制日志每 1000 次提交刷一次），写入性能提升非常明显。但这样做的前提是业务可以接受“极端情况丢极少数据”，金融、支付这类场景建议老老实实保持双 1（innodb_flush_log_at_trx_commit=1和sync_binlog=1）。
5.3 连接数：调它前先看看连接池
很多人一看到Too many connections就盲目调大max_connections。但连接数是把双刃剑：连接太多，线程切换和内存占用成本会反噬性能。
正确的处理顺序是这样的：
- 先用连接池（比如应用侧配置最小连接数、最大连接数），限制应用对数据库的实际占用。我之前遇到过应用连接池最大空闲连接设置过大，大量空闲连接把数据库连接数耗尽，调连接池参数比调数据库参数更治本。
- 如果确认数据库端确实需要更多连接，再渐进式调大
max_connections，每次加 50 到 100，观察Threads_connected和 CPU 负载。
关于 session 级参数，比如sort_buffer_size、tmp_table_size，我强烈建议不要调到太大的全局值。它们不是共享缓存，而是每个连接单独分配的。假设sort_buffer_size配 256MB，200 个并发连接同时排序，理论内存占用就是 50GB，直接把机器打死。保持默认偏小，遇到确实要复杂排序的 SQL，优先优化 SQL 本身。
6. Schema 设计：那些在源头上决定性能的隐藏细节
如果说索引解决的是“查得快不快”，Schema 设计解决的就是“能不能少查一点、查起来费不费劲”。很多性能问题在表结构建好的那一刻就注定了。
6.1 字段类型选择：别让数据库为你的“差不多”买单
在字段类型上，我见过太多为了省事做出的不当选择：
- 能用
INT就别用VARCHAR。存状态、类型、枚举值的字段，用整型比字符串在索引比较和存储空间上都有优势。 VARCHAR长度别拍脑袋给 255。VARCHAR(255)在排序时使用的内存和比较成本比VARCHAR(10)高。长度够用就好，比如手机号给VARCHAR(20)足够，注释字段 255 也不为过。- 时间类型优先
DATETIME或TIMESTAMP，别用字符串存时间。字符串存的时间无法走范围查询的索引优化，时区和格式处理也费劲。 - IP 地址不要用
VARCHAR存。可以用INT UNSIGNED，但更推荐用VARBINARY(16)存 IPv6，或者干脆把 IP 解析交给应用层，数据库只存整型或二进制。
有一次我优化一个用户表，原表把用户性别存成VARCHAR(20)，里面全是 "male"、"female"、空字符串。改成TINYINT表示枚举后，配合联合索引，相关查询的扫描行数和排序代价都降下来了。这种改动对业务代码有侵入，但对大型表来说收益非常可观。
6.2 大表归档与分区：别把分区当成万能解药
大表问题最普遍的表现是：单表数据量到了几千万甚至上亿，普通索引的 B+ 树层数变高，缓冲池命中率下降，写入和查询双双变慢。这时第一个思路不是分库分表，而是归档。
我们处理过一张订单流水表，月增量 300 万行，三年下来 1 亿多行。业务上只关心近三个月的在途数据和最近一年的历史查询，于是做了两步：
- 核心业务查询只走单独的“近期订单表”，保持千万级以下；
- 超过一年的数据按月份归档到历史库，提供独立的查询入口。
这个方案实施后，主表稳定在 3000 万行以内，查询性能恢复稳定。
分区表也要说一嘴。很多人碰到大表就“按时间分个区”，但分区表在 MySQL 里一直没有完全解决查询优化、索引维护的额外开销。如果查询条件里没有带上分区键，反而可能导致扫描全部分区，比不加分区还慢。除非你有明确的时间范围查询模式，并且分区键一定出现在查询条件中，否则优先做业务拆分和归档。
6.3 规范化与冗余的取舍：少两张 JOIN，快十倍
三范式教导我们要拆分表，避免数据冗余。但在互联网业务场景，过度拆分往往会让核心查询需要JOIN五六张表，这时性能损失比冗余大得多。
我调过一个报表统计场景：业务上需要把订单表、订单明细表、商品表、门店表四张表JOIN起来算销售额。单表数据量都不大，但四表关联后的中间结果集很大，查询总是 3 秒以上。后来在订单表上冗余了“门店名称”和“商品名称”这两个字段，查询变成单表过滤加一次聚合，耗时降到 100 毫秒以内。
所谓“冗余”并不是鼓励你乱存数据，核心原则是：把高频读取、低频变更的字段冗余到高频查询的主表里。如果冗余字段本身经常更新，那就要权衡维护成本和查询收益了。
7. 缓存与读写分离：分库分表之前最后的红利空间
当索引和配置都优化过了，仍然是硬瓶颈，这时候才轮到架构层出手。但架构改造的性价比和风险要仔细衡量。
7.1 缓存层：不是所有数据都值得进缓存
引入缓存是成本最低的架构优化，但有个前提：缓存的数据必须具有“读多写少”和“一致性容忍度较高”的特征。商品详情、用户基本信息、配置类数据就很合适；库存、余额这类强一致数据，硬往缓存里塞会造成大量缓存一致性难题。
缓存设计时最容易犯的错是缓存粒度过粗。比如把整个订单列表缓存 5 分钟，用户下单后看不到新订单，体感就会差。更合适的做法是缓存“热点单条数据”或“最近 N 条订单 ID 列表”，并配合订单变更时主动删除或更新缓存。
我习惯在新增缓存前先回答三个问题：这个数据被读取的频率是写入的多少倍？能容忍多少秒的不一致？缓存失效后能否直接从数据库重建？三个问题的答案如果都能接受，缓存才值得上。
7.2 读写分离：主从延迟比想象中更容易踩坑
读写分离能分担主库的读压力，但引出的最大问题是主从延迟。MySQL 主从复制默认是异步的，主库写入后，从库可能延迟几十毫秒甚至更久。偏偏很多业务对“刚提交的数据立刻能读到”有天然要求。
我处理过一个典型的“读写分离后刚下的订单查不到”问题。解决方案不是提升硬件，而是做分级读取策略：当前登录用户自己的数据强制读主库，其他用户、非实时类的查询才走从库。这个策略以很小的代码成本解决了绝大部分延迟问题。
另外，从库延迟检测不能只看SHOW SLAVE STATUS里的Seconds_Behind_Master，这个值在某些场景下会失灵，比如从库 IO 线程正常但 SQL 线程卡住时。我更建议额外监控从库的Read_Master_Log_Pos与Exec_Master_Log_Pos的差值来自定义延迟判断。
7.3 分库分表：最后一道闸门，能不开就不开
分库分表是性能优化金字塔的塔尖，但它也意味着代码改造、分布式事务、跨库查询、全局主键策略等一堆复杂问题。坦率地说，绝大多数项目根本走不到这一步。
什么时候才值得考虑？我的判断标准很简单：单实例 MySQL 在合理配置下已经跑满资源，且通过缓存、读写分离、数据归档都无法把负载降下来，业务量还在持续增长。换句话说，先榨干单库的能力，再上分库分表。
如果真到了这一步，建议优先从“按业务垂直拆分”开始，比如把订单、用户、商品拆到不同库，而不是一上来就搞水平分表。垂直拆分的代码侵入和运维复杂度远低于水平分表，也能满足大部分场景的扩容需求。
最后聊点实在的。我做 MySQL 优化这些年，最大的感受是：优化是一门排查科学，不是配置堆砌学。每次有人拿着一堆参数来问我该怎么调，我都建议先把慢日志和EXPLAIN拿出来看看。索引建对了、SQL 写好了，数据库自然会松一口气。如果非要分享一条个人经验，那就是：每次改配置之前，先问自己三个问题——这条 SQL 能不能少扫点行，能不能不走回表，能不能减少查询次数。能答出其中任何一个“能”，配置就先放一放，改代码和索引去。
