数据库又慢了接口超时告警一条接一条开发说是SQL写得有问题DBA说是表结构设计不合理运维说硬件也该换了——这种互相甩锅的场面干过几年后端和数据库的人应该都熟。调优这事确实容易让人一头雾水因为它没有一个万能公式不像修bug那样定位到某一行代码就行而是要在一个复杂的系统里找出真正的瓶颈点。我自己的体会是数据库调优与其说是一门技术不如说是一套排查问题的思路。你得有一个清晰的框架知道该从哪些角度去审视一套数据库系统然后按图索骥逐个排查才能少走弯路。这篇文章就把我这些年实际干活过程中总结的10个调优角度完整梳理一遍每个角度都会讲清楚原理、判断方法、实操手段和容易踩的坑。不管你是刚接触数据库的开发者还是正在处理线上故障的负责人这套框架都能帮你快速找到入口。1. 索引优化调优的第一优先级的理由索引永远是数据库调优里最先要查的东西。我见过太多性能问题根本原因就是一个本该走索引的查询在扫全表或者索引建了但没被用上。这不夸张至少一半以上的查询慢问题都出在索引这一层。1.1 索引为什么能提速底层逻辑先搞懂索引的核心数据结构通常是B树。为什么MySQL这些主流关系型数据库都选B树而不是哈希表或者普通二叉树因为B树有两个特性非常适合磁盘存储一个是矮胖层数少。一个几百万行的表索引树通常也就三四层查一条数据只需要几次磁盘IO。另一个是叶子节点通过指针串成了有序链表天然支持范围查询比如WHERE age 18 AND age 30这种条件找到起点之后沿着链表往后扫就行。不过索引不是建得越多越好。每建一个索引写入数据时就要多维护一棵B树插入、更新、删除的性能都会受影响。这就是为什么有的表写入慢查了一下发现上面挂了十几个索引。所以索引设计的原则是优先覆盖核心查询路径数量克制能用联合索引解决的就别建多个单列索引。1.2 怎么判断索引有没有生效大部分数据库都支持查看执行计划。MySQL里就是EXPLAIN用法很简单EXPLAIN SELECT * FROM orders WHERE user_id 123 AND status 1;看执行计划的时候重点看几个字段type从好到差依次是const、ref、range、index、ALL。如果看到ALL说明在扫全表这就是最直接的报警信号。key实际用到的索引名。如果为NULL说明没走任何索引。rows预估扫描的行数。这个数字越大查询通常越慢。Extra如果出现Using filesort或者Using temporary说明排序或去重用到了临时文件或临时表这种往往有优化空间。我这里有个真实案例。一张订单表有200万行数据业务方反馈按用户查订单特别慢要两三秒。用EXPLAIN一看type是ALL扫描行数100多万。表上其实有user_id的索引但查询条件里还带了一个status字段的等值条件加上原有的索引区分度不够优化器觉得不如直接全扫。后来把索引改成了联合索引(user_id, status)把两个条件都覆盖进去查询时间直接从2000多毫秒降到了30毫秒。1.3 几个实战中常见的索引失效场景即使表上有索引也不代表查询就一定会走。以下几种情况是我工作中反复遇到的也是排查时优先要检查的对索引列做了函数操作比如WHERE DATE(create_time) 2025-01-01索引就失效了。正确写法是WHERE create_time 2025-01-01 AND create_time 2025-01-02。隐式类型转换。user_id是varchar类型查询时传了数字123MySQL会先把列转成数字再比较索引就废了。联合索引不满足最左前缀原则。比如联合索引是(a, b, c)查询条件只要b和c索引无法命中。用LIKE %关键词这种前置通配符的模糊查询索引也无法使用。只能在条件允许的情况下改成LIKE 关键词%或者上全文索引。注意EXPLAIN看的是预估执行计划实际执行中MySQL会根据统计信息和实际情况调整。如果发现预估和实际差别很大可以试试ANALYZE TABLE更新一下统计信息再回头看执行计划。2. SQL语句改写不花一分钱配置的性能提升有些慢查询问题不在索引而在SQL本身的写法上。同一句查询用不同的写法性能差距可能有一个数量级。SQL改写是最便宜的优化手段不需要加硬件、不需要改架构改一行代码就见效。2.1 常见的SQL性能杀手第一个是SELECT *。这不仅是多返回了几个字段的问题更关键的是如果表上有覆盖索引SELECT *会让覆盖索引失效因为索引里不可能包含所有列数据库必须回表去查完整行数据。回表意味着额外的磁盘IO数据量大时性能影响很明显。第二个是深分页。很多后台管理系统的列表页习惯这么写SELECT * FROM orders ORDER BY id LIMIT 100000, 20;这种写法的性能问题在于数据库需要先扫描并丢弃前10万行再取后面的20行。数据往后翻得越深查询越慢。一个常见的优化手段是延迟关联或者叫游标分页SELECT * FROM orders WHERE id 100000 ORDER BY id LIMIT 20;前提是业务上能接受用id作为分页游标。对于“上一页最后一条记录的id”这种交互方式这个写法几乎是无损优化。第三个是子查询和JOIN的滥用。有些场景下子查询可以被改写成JOIN或者反过来。关键是要看执行计划不能凭感觉。MySQL的优化器对不同的写法可能有不同的处理方式同一句逻辑写法不同执行路径可能截然不同。2.2 改写SQL的一个完整实例我之前处理过一个报表查询业务方需要统计每个分类下的商品数量和销售额。原始的SQL用了三层嵌套子查询跑了接近10秒。我做了两步改写第一步把三层的子查询拆开看每一层的执行计划确定哪一层是最耗时的。第二步把最内层的子查询改写成JOINGROUP BY并给关联字段建上索引。改写后的SQL大概长这样SELECT c.category_name, COUNT(p.id), SUM(p.price) FROM categories c LEFT JOIN products p ON p.category_id c.id GROUP BY c.category_name;执行时间从10秒降到了0.5秒。这个案例给我的启发是SQL改写不是靠背所谓的“优化技巧”而是要看懂执行计划找出真正卡脖子的那一层然后对症下药。2.3 改写SQL的实操建议日常开发中我建议养成几个习惯写完SQL先EXPLAIN一下养成扫一眼执行计划的习惯。 尽量不要在循环里查数据库。比如查一个订单列表再循环查每个订单的明细这种N1查询问题要么用JOIN一把梭查出来要么先查列表再批量查明细在内存里做关联。 对OR条件谨慎处理。如果OR连接的是两个不同的索引列优化器很可能不走索引改成UNION ALL或者用IN代替有时会好很多。3. 表结构设计调优的胜负手在源头很多性能问题其实在设计表结构的时候就已经注定了。索引和SQL改写是在已经建好的表上做修补而表结构设计是从源头决定了一整套系统的性能基线和扩展空间。3.1 字段类型的选择直接决定存储和查询成本我见过很多表设计得过于“随意”该用整型的用字符串该用datetime的用varchar结果不仅浪费存储空间查询性能也受影响。几个常见的原则能用数值类型就不用字符串数值比较比字符串比较快得多。整数类型够用就用小的。TINYINT能存下的状态值没必要用INT更没必要用BIGINT。字符串类型要控制长度。VARCHAR(255)和VARCHAR(5000)即便存的内容一样短但在排序和临时表操作时的开销是不一样的。避免使用TEXT和BLOB这类大字段存储到业务主表里如果必须存建议拆到独立的扩展表按主键一对一关联。3.2 范式和反范式的平衡教科书里强调三范式但在实际业务中过度规范化反而会带来性能问题。举个例子订单表如果严格要求范式那么订单里的商品名称、单价都得通过商品ID去关联查询每次展示订单列表都要做多次JOIN。实际项目中为了查询性能在订单表里冗余一个product_name字段或者把订单总额冗余到订单表里这些都是典型的反范式设计。代价是需要维护数据一致性但换来的查询性能提升往往非常可观。我的经验是核心交易链路可以偏范式化保证一致性和灵活性查询展示链路可以偏反范式化牺牲一部分写操作复杂度换取读操作的高性能。3.3 字符集和排序规则必须统一这是一个容易被忽略的坑。如果多张表用了不同的字符集JOIN的时候数据库需要对字段做字符集转换索引也会失效。我曾经排查过一个慢查询两张表关联字段的字符集一个是utf8mb4一个是latin1执行计划直接显示全表扫描。后来把字符集统一成utf8mb4问题立刻解决。还有一点主键设计尽量不要用随机字符串。用自增ID或者雪花算法生成的趋势递增ID对InnoDB这种聚簇索引表来说写入时能减少页分裂顺序IO的效率远高于随机IO。4. 参数配置调优花最少的力气撬动最大的性能有时候SQL没问题、索引也建了、表结构也合理但数据库整体还是慢。这时候问题往往出在数据库实例的配置参数上。MySQL的默认配置是偏保守的因为它要在各种机器上都能跑起来但生产环境的机器资源通常远好于默认配置的预期。4.1 最重要的几个InnoDB参数InnoDB是MySQL默认的存储引擎它的参数对性能影响最大。第一个是innodb_buffer_pool_size这是InnoDB的缓存池用来缓存数据页和索引页。这个值设小了数据大部分都在磁盘上每次查询都要读磁盘性能自然上不去。业界一个常见的建议是设为服务器物理内存的60%到70%但前提是这台机器只跑数据库。如果机器上还部署了其他服务就要适当调低避免内存不够触发swap。第二个是innodb_log_file_size也就是redo log的文件大小。这个参数决定了对写操作的缓冲能力。设置太小日志文件频繁切换会导致频繁的磁盘刷写。但也不是越大越好因为崩溃恢复时需要重放日志文件太大恢复时间会变长。通常建议单个文件大小设为1GB到4GB之间配合innodb_log_files_in_group使用。第三个是innodb_flush_log_at_trx_commit。这个参数控制事务提交时多久刷一次redo log到磁盘。默认值是1安全性最高每次提交都刷盘但性能也最差。如果能容忍极端情况下丢失最后一秒左右的数据可以设为2性能提升非常明显。4.2 连接数相关的配置max_connections是很多人喜欢乱调的参数。一看到“Too many connections”报错就把它从200调到5000。这其实是个治标不治本的办法。每个连接都要占用线程栈、缓冲区等内存资源连接数开得越大每个连接能分到的资源反而越少性能可能更差。真正要做的是两件事第一合理评估应用的并发量设置一个够用且有余量的值第二在应用层面用连接池而不是每次请求都新建连接。连接池本身也有参数要调后面第9个角度我会专门讲。4.3 慢查询日志怎么开调优的第一步是知道慢在哪最直接的办法就是打开慢查询日志SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;long_query_time的单位是秒设成1就表示超过1秒的查询会被记录下来。注意这里有个坑long_query_time的默认值是10秒很多新人都不知道导致日志里什么都查不到以为没有慢查询其实只是阈值太高了。上线阶段可以临时把阈值设低一点比如0.5秒甚至0.1秒然后把慢查询日志关掉或者设置日志轮转避免日志文件无限膨胀把磁盘写满。5. 缓存层介入让数据库少干点活不管是索引、SQL改写还是参数调优本质上都是让数据库把同样的活干得更快。但还有一个更偷懒的思路能不能让数据库干脆少干活这就是缓存层存在的意义。5.1 多级缓存的整体思路以我做过的一个电商系统为例完整的缓存链路大概是这个样子的浏览器端有HTTP缓存CDN有边缘节点缓存应用层有本地缓存比如JVM的Caffeine再往外是一层Redis分布式缓存最后才落到数据库。每一层缓存命中不了才往下一层查。这样设计的好处是数据库实际承受的请求量可能只有总请求量的百分之一都不到。5.2 缓存的三个经典问题用缓存绕不开三个问题面试和实战都经常碰到缓存穿透。查询一个不存在的key缓存里没有数据库里也没有每次请求都直接打到数据库。如果一个恶意攻击者疯狂请求不存在的商品ID数据库压力会很大。解决办法是缓存空结果并设置较短的过期时间或者用布隆过滤器先过滤掉一定不存在的key。缓存击穿。某一个热点key在过期的瞬间大量请求同时打到数据库。解决办法是加互斥锁或者把热点key的过期时间设为永不过期后台异步更新。缓存雪崩。大量key在同一时间过期导致请求都落到数据库上。解决办法是给过期时间加一个随机值让过期时间分散开或者做多级缓存。5.3 缓存与数据库的数据一致性这是一个很多人纠结的点。最稳妥的方案是Cache Aside模式读的时候先读缓存读不到就读数据库再回填缓存写的时候先更新数据库再删缓存。为什么是“删缓存”而不是“更新缓存”因为更新缓存要考虑并发写的问题两个请求同时更新数据后更新的缓存值可能不是最终值而删除缓存的话下次读的时候自然会从数据库拉到最新值。当然删除缓存也有极端情况下的一致性问题但概率比较低。如果对一致性要求极高可以考虑引入消息队列在数据库更新后发送消息由消费者异步删除缓存。在绝大多数业务场景下Cache Aside加上合理的过期时间已经够用了。6. 分区表与分库分表数据量大到单表扛不住时当单表的数据量增长到一定程度不管怎么加索引、改SQL性能都会遇到瓶颈。这个时候就需要从“怎么让查询更快”转向“怎么让要查的数据更少”。分区和分表就是为了解决这个问题。6.1 什么时候需要分区MySQL的分区表是把一张逻辑表拆成多个物理存储分区但对应用来说还是一张表。常见的分区方式有RANGE分区、LIST分区、HASH分区和KEY分区。一个典型场景是按时间分区。日志表、订单表这类有明确时间维度的数据可以按月分区。查询时如果条件里带了分区字段优化器会自动做分区裁剪只扫描需要的那几个分区数据量直接减少一个数量级。分区也不是没有代价。如果查询条件里没有带分区字段那么所有分区都会被扫描性能可能比不分区的表还差。所以分区表的设计原则是业务查询必须高频使用到分区字段作为筛选条件。6.2 分库分表的关键决策分区解决不了写入能力的瓶颈当单库的写入TPS到了上限或者单表数据量到了几千万上亿就需要考虑分库分表了。分库分表最常见的是水平拆分也就是按某个字段通常是用户ID或订单ID的哈希值把数据分散到多张结构相同的表里。比如user_id % 16就可以均匀分到16张表。这里的关键是拆分键的选择因为它决定了后续所有查询的路径。如果拆分键是用户ID那么所有按用户ID查的请求都可以直接路由到对应的分表速度非常快。但如果业务上经常按订单ID查而订单ID跟用户ID没有直接关联那这个查询就得在所有分表上各查一遍再汇总结果复杂度会高很多。我的建议是分库分表是最后的方案因为它带来的不仅仅是技术复杂度还有跨分片的事务问题、全局唯一主键问题、排序分页问题、多表关联问题。这些问题每一个都不是省油的灯。能晚点拆就晚点拆能用缓存、分区表、归档历史数据等手段拖延扩容时间就尽量拖延。6.3 冷热数据分离在真正走上分库分表这条路之前还有一个性价比极高的中间方案——冷热数据分离。把一年前的订单迁移到历史订单表或者直接用归档工具定期把数据导出到单独的归档库。业务上大部分的在线查询都是查近期数据把冷数据挪走后热表的体量立刻降下来索引效率明显回升这个方案实施起来比分库分表简单得多而且效果好。7. 监控告警与性能分析没有数据支撑的调优都是耍流氓前面讲的都是具体的优化手段但调优不能靠拍脑袋。你得知道系统当前的状态是什么样的瓶颈到底在哪优化之后效果如何这些都需要监控数据来做支撑。我见过不少团队一上来就调参数、改SQL结果改完性能没有变化甚至更差了就是因为缺少一个完整的性能基线来对比。7.1 监控哪些核心指标数据库监控至少要覆盖以下几类指标性能类QPS、TPS、查询响应时间、慢查询数量、锁等待时间。资源类CPU使用率、内存使用率、磁盘IO的读写延迟和吞吐量、网络带宽。连接类当前连接数、活跃连接数、连接拒绝率。InnoDB引擎层缓冲池命中率、日志文件写入频率、行锁等待次数、死锁次数。主从复制类复制延迟时间、从库的IO和SQL线程状态。这些指标可以用Prometheus加上Grafana这套开源方案来采集和展示。数据库领域常用的exporter有mysqld_exporter直接暴露Metrics接口配置简单效果很好。7.2 用慢查询日志做性能分析除了实时监控慢查询日志是最重要的离线分析素材。前面提过开启慢查询日志的方法但真正要分析的时候不能光靠人眼去看。日志量一大需要工具来辅助分析。MySQL自带的mysqldumpslow工具可以按执行次数、耗时等维度汇总慢查询mysqldumpslow -s at -t 10 /var/log/mysql/mysql-slow.log这个命令的意思是把慢查询日志按平均耗时排序显示前10条。这是最快定位问题SQL的方式。另外还有一个工具叫pt-query-digest是Percona Toolkit里的一员分析功能更强大能给出每条SQL的响应时间分布、占比等适合做深度分析。7.3 全链路监控的重要性最后说一句只盯着数据库本身的监控是不够的。一个查询慢可能是数据库慢也可能是应用服务器的线程被堵塞了也可能是网络延迟高甚至可能是前端加载慢导致接口整体响应时间长。有条件的话尽量搭建全链路的监控体系从浏览器、网关、应用到数据库每一层的耗时都要能看到。这样定位问题的时候才能快速判断慢的环节在哪里而不是一上来就在数据库里折腾半天。8. 主从复制与读写分离扛住读多写少的流量单台数据库的性能再高也有天花板。当读流量远超写流量比如典型的读多写少的互联网业务一个非常有效的架构手段就是搭建主从复制做读写分离。8.1 主从复制的基本原理MySQL主从复制基于binlog实现。主库上所有写操作都会记录到binlog中从库通过IO线程拉取binlog并写入到自己的中继日志relay log再由SQL线程把中继日志里的操作在从库上重放一遍从而保持主从数据一致。复制的模式主要有两种。传统的是异步复制主库提交事务后不等待从库响应性能最好但有数据丢失的风险主库挂了之后从库可能少了最后一部分数据。另一种是半同步复制主库提交事务后至少等待一个从库确认接收到binlog才返回给客户端。安全性高了不少代价是性能略有损耗。8.2 读写分离的落地方式架构上应用层的读写分离一般有两种实现路径。一种是在代码层面做数据源的路由比如Spring框架里配置两个数据源写操作走主库读操作走从库。另一种是接入中间件比如ProxySQL、Mycat、ShardingSphere这类数据库代理应用只管连代理代理负责把读写请求分发到主库和从库。我个人的经验是刚开始流量不大的时候用代码层面做路由就够了额外引入中间件会增加架构复杂度。等到团队规模大了、业务复杂了再用中间件做统一管控也不迟。8.3 复制延迟是必须面对的坑读写分离最大的坑是复制延迟。主库刚写入一条数据从库还没来得及同步这时候读请求发了过去读到的是旧数据。这在一些实时性要求高的场景下是没法接受的。应对复制延迟的办法最常见的是强制部分读走主库。比如用户下单后立刻跳转到订单详情页这个订单详情的读请求强制走主库因为刚写完的数据大概率还没同步到从库。另一个办法是把从库的parallel_workers调大提升从库应用binlog的并行度从源头降低延迟。还有一个排查方向如果从库延迟一直很大先看是不是有长时间运行的查询在从库上执行导致SQL线程被卡住了。这种情况在从库上跑大报表任务的时候非常常见。9. 连接与线程池调优细节里的性能黑洞数据库的每一次连接都不是免费的。建立连接要经过TCP握手、身份验证、分配线程栈等一堆步骤开销相当可观。高并发场景下连接管理做得好不好对性能的影响非常直接。9.1 应用层的连接池怎么配以Java应用最常见的HikariCP为例它是个性能很好的连接池但拒绝服务式的默认配置其实不适合所有场景。连接池的核心参数是maximumPoolSize和minimumIdle。maximumPoolSize不是越大越好。每个连接背后数据库都要消耗内存和线程资源。连接过多反而会让数据库忙于上下文切换响应变慢。一个经验值是core线程数 * (1 等待时间 / 处理时间)。当然这只是估算公式实际还是要压测验证。我见过一个典型的案例某服务把连接池最大连接数配到了200数据库最大连接数才300多个服务实例连同一个数据库稍微一扩容数据库连接数直接打满所有请求超时。这是个非常经典的教训——连接池的配置必须结合数据库侧的总连接数做整体规划不能只盯着单个应用看。9.2 数据库侧的连接参数MySQL这边也有几个跟连接相关的参数值得关注。wait_timeout和interactive_timeout控制空闲连接的超时时间。设置太短连接容易被断开应用就要报错重连设置太长大量空闲连接占着资源不释放。通常建议设置在60秒到300秒之间配合连接池的心跳检测一起用。thread_cache_size控制线程缓存。如果应用频繁创建和销毁连接这个值配大一些可以减少线程创建的消耗。9.3 连接池预热还有一个容易被忽视的点是连接池预热。服务刚启动的时候连接池是空的前几个请求都要现建连接响应时间会明显偏慢。如果对初始化时间敏感可以在应用启动完成后主动执行几条简单的SQL把连接池填满。这个操作虽然简单但实测对降低冷启动时的请求延迟非常有效。10. 锁竞争与事务并发别让数据库内耗拖垮性能最后一个角度是数据库自身的并发控制能力。如果锁用不好数据库的资源都耗在处理锁等待和死锁上了真正的业务查询反而得不到资源。这个角度在并发量上来之后会变得越来越重要。10.1 InnoDB的锁机制要点InnoDB支持行级锁这是它能支撑高并发的重要前提。但行锁也分几种记录锁锁的是具体的索引记录间隙锁锁的是两条记录之间的区间防止幻读。间隙锁是InnoDB在RR可重复读隔离级别下解决幻读问题的重要手段但也容易导致锁范围扩大。一个很常见的锁竞争场景是这样的事务A更新了id IN (1, 2, 3)的三行数据还没提交事务B也想更新id IN (3, 4, 5)的数据其中id3这行被A锁住了B只能等待A提交或回滚。如果A持有锁的时间太长B的等待时间就会很长表现在业务上就是大量的请求超时。10.2 从哪些角度减少锁冲突一是让事务尽快提交。事务里不要做RPC调用、外部API请求这类耗时操作避免长时间持锁。二是尽量用等值条件去更新减少间隙锁的范围。三是控制事务的规模一次更新几千上万行不如拆成小批量提交减少单次锁的持有时间。死锁是另一个让人头皮发麻的问题。虽然死锁本身不可避免但可以通过观察死锁日志定位原因。MySQL里查看最近一次死锁信息SHOW ENGINE INNODB STATUS;重点看LATEST DETECTED DEADLOCK这一段里面会列出两个事务分别持有和等待哪些锁。绝大多数死锁都有一个模式两个事务加锁的顺序不一致。比如事务A先锁表1再锁表2事务B先锁表2再锁表1那就非常容易死锁。解决办法就是统一加锁顺序让所有事务都按照同样的顺序获取锁。10.3 隔离级别的选择隔离级别越高数据库需要做的并发控制就越多性能越低。默认的RR级别能避免幻读但间隙锁的范围更大锁竞争也更激烈。如果业务上能接受读已提交RC级别并发性能通常会好不少。MySQL官方在复制场景下也推荐使用RC因为RC级别下binlog的格式可以更简单。很多互联网公司都会在业务允许的情况下把隔离级别从RR改到RC换来更低的锁竞争和更高的并发吞吐。选择哪个隔离级别需要业务方和技术方一起权衡一致性和并发性的平衡点。没有统一答案只有基于业务场景的选择。讲到这里10个角度算是全部过了一遍。最后说一点我的个人体会调优不是一锤子买卖而是持续迭代的过程。每做一个优化都要用监控数据去验证效果看看QPS和响应时间是不是真的变好了有没有引入新的问题。我也见过有人为了调优而调优把一堆参数改得很激进结果系统崩了再改回来。这种折腾没有任何意义。如果你看完这篇文章不知道从哪里下手我建议你先做两件事第一打开慢查询日志把慢SQL捞出来第二用EXPLAIN看看这些慢SQL的执行计划。90%的情况下看完这两个东西下一步该做什么就清楚了。剩下的就是按这套框架逐个排查把优化当成体检一样定期做一遍。等你把前面这几个最容易见效的角度都过完了你对数据库的理解一定会比现在深一个层次。