MySQL数据库性能优化中常用的方法网上搜出来能堆一箩筐但真正拿到生产环境用得上的其实就那几板斧。我做了这么多年后端处理过大大小小几十起数据库变慢的故障最深的感受是优化不是一上来就调参数、加索引而是先把瓶颈找准再对症下药。这篇文章我打算按实际排查和优化的顺序来讲先讲怎么定位慢在哪再讲索引和SQL这些性价比最高的手段然后才是配置参数与架构调整。内容不会太底层也不会绕术语适合正在接手一个慢数据库、或者想系统梳理MySQL优化思路的后端开发、运维同学参考。1. 性能优化的前提先搞懂瓶颈到底在哪一层很多人一听到数据库性能差第一反应就是加索引、调缓存结果折腾半天没效果回头一看真正的问题可能只是某条SQL没走索引或者是连接数被打满了。我的习惯是性能优化永远从定位开始而不是从猜测开始。1.1 先开慢查询日志把真实案底捞出来慢查询日志是MySQL自查的第一工具。它会把执行时间超过阈值的SQL原原本本记下来让你知道哪些是真正拖垮系统的“元凶”。我接手过的很多项目慢查询日志根本没开。第一次排查时先补上这一段配置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全部记录。如果一开始业务量比较大可以暂时设成 2 或 3先看最严重的。log_queries_not_using_indexes这个参数很值得开可以把那些没走索引的SQL也捞出来很多隐藏的性能问题就是这么发现的。日志别只开一天建议至少采集 3 到 7 天。我见过有同事只看了一个小时的慢日志就开始优化结果优化方向完全跑偏。数据量足够之后用mysqldumpslow或者干脆用pt-query-digest做聚合分析把耗时的SQL按总执行时间排序基本就能锁定七八成的问题。这里有个细节慢日志会消耗一点IO生产环境一般建议long_query_time不要设成 0否则所有语句都会记录日志量会非常恐怖。通常 1 秒是一个比较平衡的阈值。1.2 EXPLAIN 和 SHOW PROCESSLIST 配合使用拿到慢SQL之后第一件事是执行EXPLAIN查看执行计划。很多新人只知道执行一下看到结果不会读其实关键就那几列type、key、rows、Extra。type至少要达到ref或range如果是ALL说明在做全表扫描这是最常见的问题。key实际用到的索引如果是NULL说明索引没生效。rows预估扫描的行数这个数字越大说明查询越笨重。Extra看到Using filesort或Using temporary就要警惕了这意味着额外的排序或临时表。除了 EXPLAINSHOW PROCESSLIST也很有用。数据库卡顿的时候执行一下能看到当前正在运行的会话有没有长时间不释放的锁、有没有大事务卡在某个状态。我曾经遇到过一次线上问题就是一条UPDATE没带WHERE条件结果把整张表锁了快十分钟用SHOW PROCESSLIST一眼就看出来了。2. 索引优化性能优化的第一板斧索引就是数据库的“目录”没有索引的全表扫描相当于一本五百页的书让你从第一页翻到最后一页找一句话。真实业务场景里80%的慢SQL都能靠索引解决所以这是优化成本最低、收益最高的手段。2.1 为什么索引比调参数更优先有些同学一上来就调innodb_buffer_pool_size觉得把内存调大就快了。这其实有点本末倒置。如果一条SQL要扫描两百万行就算数据全在内存里也是两百万次判断CPU照样跑满。但如果你建对了索引扫描量可能直接降到几百行这才是数量级的提升。索引内部是B树结构简单理解就是按顺序排好的一棵多叉树查找时间复杂度大概是O(log n)。我刚才拿目录类比可能还不太准确更准确的比喻是字典里按拼音查字你能一下翻到那一页附近而不是从第一页开始逐页看。InnoDB 的索引分两种聚簇索引和二级索引。聚簇索引就是主键索引叶子节点存整行数据二级索引的叶子节点存主键值。所以如果你通过二级索引查数据先查到主键再用主键去聚簇索引里找完整行这个过程叫“回表”。回表次数多的话性能也会受拖累所以引出覆盖索引的概念让二级索引叶子节点“覆盖”你要查询的字段就可以直接返回不用回表。2.2 联合索引设计字段顺序真的不能乱工作中使用最多的就是联合索引。联合索引设计得好一个索引能顶好几个索引。这里最核心的原则是“最左前缀原则”MySQL会从联合索引的最左边字段开始匹配跳过任何字段后续的索引字段就失效了。举个例子假设表里有user_id、status、created_at三个字段查询场景是“按用户查某状态下最近创建的记录”SELECT order_id, amount FROM orders WHERE user_id 1001 AND status 1 ORDER BY created_at DESC LIMIT 20;这种场景联合索引的字段顺序我建议设计成(user_id, status, created_at)。为什么user_id放最前面因为它是等值条件区分度最高能直接过滤掉绝大部分数据。等值条件的字段一般放在联合索引前面其后可以放排序字段这样排序也能直接走索引避免Using filesort。这里有个经验不要在每个字段上都单独建索引很多开发习惯给每个字段各建一个单列索引以为索引越多越好实际上MySQL一次查询通常只能选择一个索引多余的索引白白增加写入压力。联合索引能用就优先用联合索引。2.3 EXPLAIN 实战一个案例看懂执行计划说个我实际处理过的案例。有一个订单列表查询线上执行要 3 秒多SQL 大概是这样的SELECT * FROM orders WHERE status 0 ORDER BY created_at DESC LIMIT 20;执行EXPLAIN之后关键信息是type ALL全表扫描key NULL没有可用索引rows 2600000预估扫描 260 万行Extra Using filesort文件排序也扛上了。这个status 0是筛选条件但我没有把status放在联合索引最前面因为status的区分度太低了可能 90% 的订单都是这个状态MySQL 优化器发现即使走索引也要查出一大半数据干脆全表扫描。后来我调整了查询条件加了时间范围同时建立(created_at, status)联合索引让排序走索引扫描行数降到几千查询时间压到了 50 毫秒以内。这个案例说明EXPLAIN 的rows一旦很高就要想想是不是查询条件本身考虑了全部历史数据。很多线上问题不是因为“没有索引”而是因为“查询条件范围太大索引帮不上忙”。2.4 常见的索引失效写法索引建好了也要防止它失效。这几类写法是高频雷区对索引字段使用函数例如WHERE YEAR(created_at) 2023索引直接失效应该改成created_at 2023-01-01 AND created_at 2024-01-01。隐式类型转换比如字段类型是字符串查询条件写数字MySQL 会默认做转换索引也可能失效。LIKE 前置百分号LIKE %关键字无法使用索引但LIKE 关键字%可用。OR 条件连接非索引字段可能导致整个条件不走索引。这些都不是玄学只要理解索引是一棵“有序树”就能想明白任何破坏字段有序性的操作都会让树查找失效。3. SQL 语句优化不改表结构也能提速很多时候表结构历史包袱太重不能随便改那就在 SQL 层面动手。这些改动不需要迁移数据风险小见效也快。3.1 SELECT * 的隐性成本SELECT *是新手最爱但它带来的问题是多个层面的。一是把不需要的大字段比如TEXT、BLOB也查出来增加网络传输二是不利于覆盖索引优化你要的字段如果都在索引里MySQL 直接返回即可但*往往迫使它回表。我一般建议把查询字段按需列出。比如订单列表页只需要id、order_no、amount、created_at就不要把用户的收货地址、备注这些冗余大字段也拉出来。数据库优化就是积少成多一条 SQL 省几十 KB一分钟被调用几千次节省的网络和内存开销就非常可观。3.2 JOIN 和子查询的取舍MySQL 对子查询的优化能力这几年进步了不少但有些写法依然会产生临时表比如在WHERE IN里放一个子查询数据量大时优化器会把它物化成临时表再关联性能往往不好。我更推荐用JOIN改写。早期我优化过一个带子查询的报表SQL类似这样SELECT id, user_name FROM users WHERE id IN ( SELECT user_id FROM orders WHERE status 1 );数据量上来之后这条语句执行要好几秒。我改成了内连接SELECT DISTINCT u.id, u.user_name FROM users u INNER JOIN orders o ON o.user_id u.id WHERE o.status 1;关联表之前还有一个原则小表驱动大表。也就是用小结果集 join 大结果集减少循环次数。虽然 MySQL 优化器会自动调整顺序但复杂场景下它也会选错这时候你可以在必要时用STRAIGHT_JOIN指定顺序但尽量少用它属于比较硬核的手段。3.3 深分页优化别再用 LIMIT 十万条分页功能谁都会写但经典的坑是深分页。比如LIMIT 100000, 20MySQL 不是只读 20 条就返回而是先从头数出 100020 条再丢掉前 100000 条这个成本高得离谱。优化方案常用两种第一种是游标分页适合按主键或唯一递增字段排序的场景把LIMIT换成WHERE id 上次查询的最大id ORDER BY id LIMIT 20。这个方式最推荐性能极其稳定。第二种如果必须用传统分页可以先把主键查出来再回表取数据SELECT id, order_no, amount FROM orders JOIN ( SELECT id FROM orders ORDER BY created_at DESC LIMIT 100000, 20 ) tmp ON tmp.id orders.id;这种写法结合覆盖索引子查询只扫主键和排序字段速度能快不少。但本质上深分页都是反模式的真正高频的场景最好还是转用游标分页。3.4 WHERE 条件别做运算在 WHERE 条件里对字段做运算会导致索引失效这一点我反复跟人强调。比如WHERE amount 10 2000amount字段加上 10 之后就不是原始值了索引自然用不上。应该把运算挪到右侧WHERE amount 1990。还有一个容易被忽略的问题大批量插入或更新时不要一个事务里塞太多数据。单个事务太大会撑大 redo log、长时间占用行锁导致其他会话阻塞。我见过上线的“批量更新”脚本一次更新几十万行结果整个业务库被锁住前端接口全部超时。后来拆成每批 500 行问题立刻消失。4. 配置参数调优把 MySQL 该吃的东西喂饱索引和SQL都梳理完之后还有一类优化属于“基础环境”调整。MySQL 默认配置偏保守特别是用于测试机或者个人电脑的配置几乎都是为最小资源占用设计的。生产环境如果把内存等相关参数调到合理位置性能提升也很明显。4.1 innodb_buffer_pool_size最关键的内存参数这个参数控制 InnoDB 的缓存池大小用来缓存索引和数据页。物理内存允许的情况下建议设成内存的 60% 到 75%。比如一台 32G 内存的数据库服务器可以设成 20G 或 24G但要给操作系统和文件缓存留出余量不要贸然全吃。有人问到底怎么判断不够用看命中率比较直接SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read_requests; SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_reads;用read_requests除以read_requests reads就能得到缓存命中率一般 99% 以上是比较健康的。如果明显低于这个值说明缓存池偏小或者索引设计有问题导致扫描数据量过大。innodb_buffer_pool_size 20G innodb_buffer_pool_instances 8instances把缓冲池拆成多份可以减少并发访问时对单个缓冲池的争抢通常设置在 8 到 16 之间即可但每一份不小于 1G否则可能反而影响性能。4.2 日志与事务参数安全与性能的取舍事务日志和刷盘策略对写入性能影响很大。关键参数是innodb_flush_log_at_trx_commit值为 1每次事务提交都刷盘最安全性能最差值为 2每次提交只写到操作系统缓存每秒刷一次盘性能明显提升但极端断电可能丢失 1 秒数据值为 0由系统控制刷盘性能最好风险也最高。我见过很多生产库设成 2配合sync_binlog1在性能和数据安全之间能取得一个平衡。如果你的业务对数据一致性要求极严那就老老实实用 1别为了性能拿数据冒险。innodb_log_file_size决定 redo log 的大小。日志太小写入频繁时就会触发日志切换造成不必要的刷盘。一般生产环境可以设置成 1G 或 2GMySQL 8.0.30 之后的版本用innodb_redo_log_capacity来配置容量比如设置成8G日常使用更省心。4.3 连接数与会话层面的参数max_connections不是越大越好。每个连接都会消耗线程、内存连接数太大反而会拖垮数据库。默认 151 偶尔不够用但直接调到 2000 很容易出问题正确做法是结合应用层连接池调整一般不超过 500 比较合理。同时配合thread_cache_size缓存空闲线程供新连接复用减少线程频繁创建销毁的开销。会话级别的参数比如sort_buffer_size、join_buffer_size不要一上来就调得很大因为它们是每个线程单独分配的全局累积起来很恐怖。我见过有人把sort_buffer_size调到 64M结果 100 个连接并发排序内存直接爆掉。正确的思路是保持合理值比如sort_buffer_size 2M就够大部分场景。这里贴一份可用于 16G 内存数据库上的常规配置参考[mysqld] innodb_buffer_pool_size 10G innodb_buffer_pool_instances 8 innodb_log_file_size 1G innodb_flush_log_at_trx_commit 2 max_connections 300 thread_cache_size 64 sort_buffer_size 2M join_buffer_size 2M注意有些参数是动态的可以用SET GLOBAL在线修改有些必须修改配置文件后重启比如innodb_buffer_pool_size和大多数日志参数。线上环境改动之前最好先在测试环境验证并做好配置文件备份。5. 架构层面优化与常见问题排查记录到了这一步如果单机优化已经做到位但数据库仍然是瓶颈那就需要考虑架构层面的拆分了。但架构手段复杂度高我始终建议先把单机和SQL层面榨干再上架构。5.1 读写分离与分库分表别过早引入读写分离适合“读多写少”的业务。把主库的压力分流到从库能明显降低主库负载。但引入后要处理主从延迟比较常见的做法是把强一致性的读请求继续走主库弱一致性场景走从库。分库分表则要等到单表数据量特别大、索引维护成本剧增的时候再考虑。通常单表超过千万级并且持续增长就要开始做方案了。分库分表的代价很大分布式ID、跨库查询、事务一致性都要重新设计如果没有专业团队不建议当作第一选择。一个更务实的方案是先考虑历史数据归档。很多业务表里 80% 的数据是旧的、基本不查的把冷数据迁移到历史表主表保持在小数据量性能自然就回来了。5.2 缓存层给数据库减负的好帮手业务查询有典型热点的场景引入 Redis 这类缓存非常有用。我建议把缓存设计成旁路缓存模式先查缓存命中直接返回没命中查数据库再回填缓存。要注意缓存穿透、击穿、雪崩这三个经典问题缓存穿透查询的数据不存在每次都打到DB可以在缓存里存空值或者用布隆过滤器拦截。缓存击穿热点 key 过期瞬间大量请求打到DB可以用互斥锁或者逻辑过期方案。缓存雪崩大批 key 同时过期DB 压力瞬间拉满可以把过期时间加一个随机偏移量。缓存不是越多越好写操作频繁的数据如果也塞缓存反而会制造大量缓存一致性问题。5.3 慢查询排查流程整理成速查表最后把我的排查流程整理成一张表遇到问题照着走就行排查步骤操作关键点1. 开启慢日志SET GLOBAL slow_query_log ON;阈值从1秒开始2. 提取慢SQLmysqldumpslow -s t mysql-slow.log按总耗时排序3. 分析执行计划EXPLAIN SQL看 type、key、rows、Extra4. 查看运行状态SHOW PROCESSLIST;查长事务、锁等待5. 检查索引命中观察 key 字段是否是 NULL / 失效写法6. 调整SQL写法减少回表、去掉函数运算必要时拆分查询7. 调整配置参数检查 buffer pool、日志参数按物理内存合理分配8. 架构兜底读写分离、缓存、归档在单机优化之后再做我每次处理慢库问题都按这套流程来基本能在半小时内锁定核心原因。上次有个线上事故同事急急忙忙要加索引我拉着先看了SHOW PROCESSLIST结果发现是某个凌晨跑批任务锁了大表根本不是查询语句的问题白折腾了半小时。做MySQL性能优化这么多年我的体会是没有一劳永逸的银弹任何优化方案都要结合业务实际。索引不是越多越好配置不是越高越好架构也不是越复杂越好。你需要的是一套能快速定位问题的方法以及足够的耐心去验证每一次改动。先把慢查询日志和EXPLAIN用熟练你就能解决绝大多数慢库问题。
