我处理过太多“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 schemaMySQL 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 1user_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说明既用索引过滤又无需回表是最理想的状态之一。我遇到过一条统计 SQLSELECT user_id, COUNT(*) FROM order_detail GROUP BY user_id;EXPLAIN显示type indexExtra 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 8000000users表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 Toolkitpt-query-digest是分析慢查询日志的利器。它能把带不同参数值的 SQL 归一化归类输出每个指纹的总耗时、执行次数、平均耗时还带执行计划建议。实际使用中它能非常快地帮你找出“单条不慢但量极大”的 SQL——这类才是拖垮数据库的隐形杀手。4.3 一次“单条不慢但量极大”的实战复盘有一段时间某服务数据库 CPU 时不时冲到 80%看慢日志单条 SQL 最慢也就 1.2 秒这个量级看起来不致命。我拿pt-query-digest分析后发现有一条 SQL 每分钟执行几万次总计耗时在所有 SQL 中占比超过 50%。这条 SQL 是典型的 N1 问题应用层先查出一批订单再循环查每个订单的详情每次查询几百条到几千条不等量一叠加数据库直接被打爆。修复方式很直接把循环内查询改成一次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_binlog1000二进制日志每 1000 次提交刷一次写入性能提升非常明显。但这样做的前提是业务可以接受“极端情况丢极少数据”金融、支付这类场景建议老老实实保持双 1innodb_flush_log_at_trx_commit1和sync_binlog1。5.3 连接数调它前先看看连接池很多人一看到Too many connections就盲目调大max_connections。但连接数是把双刃剑连接太多线程切换和内存占用成本会反噬性能。正确的处理顺序是这样的先用连接池比如应用侧配置最小连接数、最大连接数限制应用对数据库的实际占用。我之前遇到过应用连接池最大空闲连接设置过大大量空闲连接把数据库连接数耗尽调连接池参数比调数据库参数更治本。如果确认数据库端确实需要更多连接再渐进式调大max_connections每次加 50 到 100观察Threads_connected和 CPU 负载。关于 session 级参数比如sort_buffer_size、tmp_table_size我强烈建议不要调到太大的全局值。它们不是共享缓存而是每个连接单独分配的。假设sort_buffer_size配 256MB200 个并发连接同时排序理论内存占用就是 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 能不能少扫点行能不能不走回表能不能减少查询次数。能答出其中任何一个“能”配置就先放一放改代码和索引去。