我处理过不少数据库性能告警最头疼的不是慢而是该做的都做了——索引建了内存加了连接数也调上去了可业务一到高峰期数据库还是第一个倒下。后来我把这类问题重新梳理发现数据库优化从来不是单一动作而是一套有顺序、有取舍的方法组合。这篇内容整理了八种我实际验证过的数据库优化方法顺序按性价比和改动成本大致排了一下特别适合正在跟慢查询、锁等待、连接打满这类问题死磕的开发者。很多资料会把优化拆成索引、SQL、架构、参数四块但我觉得不够细光索引这一块就够写几千字。而且容易漏掉一个关键前提就是先搞清楚系统到底慢在哪。所以我开篇先讲定位手段这算不上八种方法之一但没有这个步骤后面七种方法都会变成盲人摸象。1. 优化前先做定位慢查询日志和执行计划是绕不开的诊断工具很多人一上来就调参数、加索引连到底哪里慢都不清楚。我见过一个团队把数据库连接数从200调到2000问题不但没解决反而把内存和线程调度打爆最后只能回滚配置。数据库优化第一步永远是定位先找到那个占用资源最凶的SQL或最不合理的数据访问模式后面的所有操作才有意义。1.1 慢查询日志怎么开阈值怎么定慢查询日志是最朴素也最直接的入口。以MySQL为例打开它只需要两条命令SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;第一行打开日志开关第二行把慢的标准设为1秒。设置之后超过1秒的查询都会被记录到日志文件里。阈值怎么定我建议从1秒起步如果业务本身压力很大可以先定2秒再逐步收紧。日志里除了SQL文本还有执行时间、锁等待时间、返回行数和扫描行数这几个关键字段几天下来看一遍基本能把系统里最耗时的几条语句筛出来。需要提醒的是慢查询日志只关注单条SQL的执行时间。如果一条SQL执行0.9秒但业务调用了上百次累积耗时依然很恐怖。所以除了看慢日志还要配合统计模式把SQL按模板聚合既关注单次很慢也关注高频但不慢这两类都是优化对象。1.2 EXPLAIN执行计划到底在看什么慢日志告诉你哪条SQL慢EXPLAIN告诉你为什么慢。对最慢的SQL执行一句EXPLAIN SELECT id, user_name, order_amount FROM t_order WHERE status 0 ORDER BY create_at DESC LIMIT 20;结果里要优先看这几个字段字段含义重点关注type访问类型ALL和index最差range、ref可接受const、eq_ref是理想情况key实际使用的索引不是possible_keys一定要看keyrows预估扫描行数跟查询结果量级差距越大问题越明显Extra附加信息出现Using filesort、Using temporary需要重点排查我经常举这样一个例子同样一条订单列表查询type为ALL、rows为80万、Extra里挂着Using filesort和type为ref、rows为20、Extra为空执行时间完全不是一回事。执行计划看多了你会形成一种直觉只要看到ALL和filesort同时出现基本就能断定这条SQL需要重构而不是简单加个索引就能解决。1.3 真慢和假慢要分开看曾经有个夜间的性能问题排查了很久最后发现是凌晨的批处理任务在跟业务请求抢资源数据库本身并不慢。所以定位环节还要结合时序监控CPU高说明计算和扫描量大IO高说明缓冲池命中率低物理读多线程和连接数高说明SQL长时间占住连接没释放。这三种情况对应的优化手段完全不同先分清楚再动手否则极容易白忙一场。2. 第一种方法索引优化——建索引容易让索引真正生效很难定位做完接下来就是逐个击破。排在第一位的一定是索引优化因为它的性价比最高改动也最小。绝大多数慢查询问题根源都出在索引设计或索引使用上。2.1 最左前缀原则是联合索引的命门联合索引(a, b, c)它的本质是先按a排序a相同再按b排序b相同再按c排序。这就带来一个强制约束查询条件里如果用了b和c唯独没用a索引基本失效。比如WHERE b 1 AND c 2优化器大概率会走全表扫描因为索引的最左列没有值参与匹配后续的b、c排序信息在B树上根本无法使用。反过来说如果条件只带了a索引可以生效带了a和b也可以带了a、b、c自然也可以。所以设计联合索引前先问自己业务里最常见的等值查询是哪几列把最常作为过滤条件的列放最左边而不是把唯一性高的列一窝蜂堆上去。另外一个经常被忽略的点ORDER BY也可以使用联合索引。如果排序字段恰好是索引的后续列就可能避免filesort。比如用户表经常按status过滤再按create_at排序那么(status, create_at)就比单建status索引更聪明两个条件都能吃上索引。2.2 覆盖索引如何省掉回表有索引并不是终点。数据库里主键索引和二级索引是分开的二级索引的叶子节点存的是主键值你通过二级索引找到主键后还要再到主键索引树里取整行数据这个过程叫回表。回表不是问题问题是回表的次数太多。比如一条SQL扫了5万条二级索引记录就要执行5万次按主键查询性能自然上不去。覆盖索引的意思是你要查询的列已经全部包含在二级索引里不需要再回表。最典型的场景是SELECT id FROM t WHERE a 1只要索引包含a和id数据库直接扫二级索引就能返回。日常开发中我建议把高频查询需要的列尽量纳入索引设计但不要贪多索引列过多会明显增加写入成本。2.3 区分度低的字段不要盲目建索引我见过有人给性别字段建索引结果查询计划还是全表扫描因为过滤后还剩一半数据优化器根本不认为索引有价值。索引的选择性等于不同值的数量除以总行数性别这种字段选择性极低建了也是白建。建索引优先选区分度高的列比如订单号、用户ID、状态加创建时间的组合效果才明显。冗余索引是另一个容易踩的坑。已经有(a, b)索引再建一个(a)索引就是冗余前者完全可以覆盖后者的场景。索引不是越多越好每多一个索引写入时就要多维护一棵B树日志量也会变大。对于写入密集的业务索引过多带来的写放大可能比查询收益更明显。2.4 索引失效的常见场景汇总场景原因建议WHERE name LIKE %张%前导模糊查询无法利用索引改为前缀模糊或考虑全文索引WHERE DATE(create_at) 2025-01-01对列做了函数运算破坏索引有序性用范围查询 create_at 和 WHERE phone 13800138000隐式类型转换字符串列被比较为数字保持类型一致OR连接非索引列无法同时走多个索引用UNION拆开或建复合索引优化器预估全表扫描更快数据量小或统计信息过期更新统计信息必要时强制索引这些坑我基本都踩过最隐蔽的是隐式类型转换。当时有个表phone字段是varchar查询条件写成phone 13800138000看着没毛病但数据库会把字符串列转成数字再比较索引直接失效。后来翻出原因很简单建表时图省事把手机号存成了varchar而应用层传的是整数类型每次查询都来一次全表转换。这提醒我类型设计的问题会在后续很长时间里持续放大后面我会单独展开。3. 第二种方法SQL改写——同样的需求写法不同性能差出几十倍索引加对了SQL写法跟不上也白搭。这一章我总结几种在实际项目中收益最明显的改写方式。它们的共同特征是不需要改表结构不需要动架构只是在写法上换个更聪明的姿势执行效率就能提升几个量级。3.1 不写SELECT *就是省钱很多人写查询习惯性SELECT *感觉省事。但实际开销是实实在在的*会取出表结构里的所有列首先是网络传输数据量变大其次是很可能让覆盖索引失效因为要的列里总有索引没覆盖的列只能回表去拿全字段。我通常要求核心查询只select需要的字段特别是不要在在线交易环境里一次性把几个TEXT字段捞出来。比如订单列表页只需要订单号、金额、状态、创建时间那就只查这四列。如果你把整个订单行包括支付回调信息、商家备注都查出来每条记录多传几十上千字节一百万的查询累积起来就是几百MB的无效网络和内存开销。别小看这类改写高并发下收益非常稳定。3.2 深分页的延迟关联分页查询在数据量变大后会出现一个经典问题LIMIT 100000, 20。数据库会先把前面10万条记录扫出来再丢掉只返回第100001到100020条扫描代价跟页码成正比。更合理的做法是用延迟关联SELECT t1.id, t1.user_name, t1.order_amount FROM t_order t1 INNER JOIN ( SELECT id FROM t_order ORDER BY create_at DESC LIMIT 100000, 20 ) t2 ON t1.id t2.id;内层子查询只返回主键id借助覆盖索引把定位速度做得很快外层再按主键回表取列。实测在百万级数据量下同样的分页查询优化前后耗时能从两秒多降到零点几秒。数据量更大时还可以考虑用游标分页WHERE create_at 上一页最后一条时间 ORDER BY create_at DESC LIMIT 20这也是互联网产品里很常见的加载更多方案。3.3 IN、EXISTS、JOIN的取舍小表驱动大表是早期经验之谈但现在优化器已经很聪明很多情况下会自动转换。真正需要人工干预的是几个明显场景子查询返回结果集特别大时IN往往表现不好关联条件上缺索引JOIN和EXISTS都会变成灾难。一般经验是能用JOIN表达的关联查询尽量用JOIN减少子查询带来的临时表开销但也要注意别从一个极端走到另一个极端多表大结果集JOIN如果条件写漏产生笛卡尔积数据量会指数级增长。一条原则很实用不管用IN、EXISTS还是JOIN一定要在关联字段上有索引并且用执行计划确认扫描行数在可控范围。SQL改写不是套模板而是每次都要拿执行计划验证。3.4 OR条件与UNION改写WHERE a 1 OR b 2这种写法如果a和b都有索引数据库也可能只能走其中一个索引另一个条件退化为全表扫描最终耗时以最差分支为准。常见的改写是拆成两个查询用UNION ALL合并SELECT * FROM t_order WHERE a 1 UNION ALL SELECT * FROM t_order WHERE b 2;但要注意确认两个分支不会产生重复数据否则老老实实用UNION去重。这个改写的前提是两个条件都能独立走索引必须用执行计划验证不能无脑套。如果其中一个分支仍然走全表扫描UNION改写就没有意义。3.5 别在条件里写函数这其实和索引失效是同一件事但值得单独拿出来说。WHERE DATE_FORMAT(create_at, %Y-%m-%d) 2025-01-01这种写法即使create_at上有索引也用不上。正确写法是范围条件SELECT * FROM t_order WHERE create_at 2025-01-01 00:00:00 AND create_at 2025-01-02 00:00:00;函数运算放在列上相当于把一棵有序树的每个节点都临时加工一遍优化器完全无法利用索引的顺序性。这条规则可以推而广之对列做运算、对列做类型转换、对列做隐式函数调用都是索引杀手。4. 第三种和第四种方法表结构精简与冷热分离SQL改写解决的是同一套数据怎么查得快表结构优化解决的是数据本身怎么存更合理。这一章我把表结构精简和冷热分离放在一起讲因为它们都要改表但思路完全不同前者是让单表更苗条后者是让数据总量变小。4.1 字段类型选择是性价比最高的基本功一个表几十个字段类型选得对不对往往决定表膨胀速度和查询耗时的底线。能用整数不用字符串ID、状态、数量这类字段优先用INT/BIGINT用字符串存储数字不仅空间浪费还会引发前面说的隐式类型转换。固定长度用CHAR可变长度用VARCHARVARCHAR按内容长度存储但会额外占用1到2字节记录长度CHAR在长度波动小的场景下反而省去长度记录。金额用DECIMAL别用FLOAT/DOUBLE浮点数有精度损失账算不对比性能问题更严重。日期用DATE/DATETIME不存时间戳字符串DATETIME配合日期函数也更方便走索引。大字段单独拎出来TEXT/BLOB这类字段会占用大量行空间很多场景下并不是每次查询都需要建议放到扩展表用主键关联。4.2 范式不是铁律适当冗余能省一次JOIN几乎每个开发者都被教育过第三范式但业务开发中过度范式化会制造大量JOIN。举个例子订单详情页需要商品名、商品图片、订单金额如果严格按照范式设计每次查询都要关联商品表、订单表、明细表至少三张表。更现实的做法是在订单表里冗余一个商品快照字段下单时把名称和单价存进去。这样做有两个好处。第一避免商品信息后续变动影响历史订单快照天然具备历史不变性第二把三次JOIN变成单表查询在线场景的响应时间会明显缩短。宽表和范式本来就是数据架构里的两种风格OLTP场景下适度冗余带来的收益非常稳定。4.3 垂直拆分把大而全拆成小而专当单表字段太多、部分字段又极少使用垂直拆分是最直接的办法。一个用户表如果塞了头像、昵称、密码、积分、收藏数、设备信息等几十列主键索引的每一行都会很大数据页能装下的行数变少扫描和缓存效率都会下降。我会把核心身份字段留在主表其他低频或大字段放到扩展表用user_id一对一关联。主表更紧凑热点查询的数据页命中率更高扩展表只在真正需要那些字段时才被访问。这个操作对线上流量大的表效果很明显但要注意拆表之后的应用层改写成本别为了理论上的完美把业务代码搞复杂。4.4 冷热分离历史数据别跟线上抢资源另一种拆表是冷热分离。很多业务的数据有明显的时效性比如订单、登录日志、操作记录一个月内的数据是热点一年前的数据几乎没人访问。我的做法是给表加归档机制定期把超过三个月的数据迁到归档库或归档表线上表只保留活跃数据查询时如果需要历史数据再走专门的归档查询入口。线上表体量被压到健康水平之后索引深度变浅统计信息也更精确查询性能会稳定不少。分区表是冷热分离的一种数据库原生实现按时间分区写入和查询可以通过分区裁剪只访问相关分区。但要注意分区不是万能的分区键选错或者单分区数据量依然悬殊效果会很差。归档比分区更彻底因为它会降低逻辑表的总数据量不只是逻辑裁剪。5. 第五种方法缓存兜底——不是数据库不够快而是重复查询太多数据库优化的第五种方法是加缓存。很多时候数据库不是真的慢而是同样的热点数据被查了成百上千次每个查询都走一遍磁盘和索引自然扛不住。引入缓存的目的就是把重复请求拦在数据库外面。5.1 什么时候值得加缓存判断标准很简单查询次数高、命中重复度高、数据一致性要求没那么苛刻。典型场景包括配置信息、分类列表、商品详情、用户会话信息。相反如果数据每秒都在变化且变化必须立刻被所有请求看到缓存的意义就很小一致性维护的代价会超过收益。我给团队定的原则是先看慢查询报表里的重复度如果同样的SQL一天执行上万次返回结果却没怎么变这就是缓存的最佳目标。用数据说话不要凭感觉决定缓存什么东西。5.2 Cache Aside模式怎么用实际项目里我用得最熟的是Cache Aside旁路缓存策略。读请求先查缓存没有就去数据库查查到后写回缓存并返回写请求更新数据库后删除对应的缓存而不是更新缓存。为什么是删除而不是更新因为更新缓存要考虑多字段、并发、失败重试等各种复杂情况删除缓存更简单让下一次读请求自然把新数据填回去不容易埋一致性的坑。这个模式有一个经典小细节先更新数据库还是先删缓存。更稳妥的做法是更新数据库后删除缓存并且对删除失败做补偿比如延迟重试。如果先删缓存再更新数据库更新期间可能有读请求把旧数据重新填回缓存导致一段时间内读到旧值。5.3 缓存穿透、击穿、雪崩要分别处理这三个问题经常被混在一起但成因和对策完全不同。穿透请求查的数据在数据库里根本不存在每次都穿透缓存打到数据库。应对方法是缓存空值或者用布隆过滤器在更靠前的位置拦截不存在的key。击穿某个热点key在缓存过期的一瞬间被大量请求同时打到数据库数据库压力瞬间爆炸。应对方式是热点key加互斥锁只让一个请求去回源其余请求等缓存重建。雪崩大量key在同一时间过期波及范围比击穿大得多。最常见的手段是过期时间加随机偏移让key过期时间错开同时保证缓存服务本身的高可用。5.4 不要迷信缓存缓存系统接入门槛不高但很容易被滥用。把所有表都塞进缓存缓存和数据库的一致性会成为噩梦把缓存存活时间设得很长数据变更后的脏读会持续很久。我建议缓存的对象是明显高频率且可容忍秒级延迟的数据过期时间控制在分钟级别并做好缓存更新链路监控。缓存是数据库的助手不是另一个数据库。6. 第六种和第七种方法读写分离与分库分表把单库压力摊开如果单机数据库已经接近极限加索引、优化SQL、上缓存都没有足够空间就该考虑架构层面的优化了。这里有两种方法读写分离解决的是单机读写争抢资源分库分表解决的是单表数据量过大和单库连接压力。6.1 读写分离怎么落地大多数业务的读流量远大于写流量如果把读和写都压在一台主库上写事务会被大量只读查询拖慢。读写分离的思路很简单主库负责写从库拷贝主库的日志并回放应用层把所有查询路由到从库。实现方式有两条路一条是在数据访问层自己维护主从两套数据源根据操作类型选择另一条是引入数据库代理中间件让中间层自动识别读写请求并分发。主从复制带来的第一个问题就是延迟。从库的同步是异步的刚写完主库的数据从库可能还没追上此时读请求会拿到旧数据。常规解法是对一致性要求高的操作强制走主库或者让从库追上一个位点后再返回。对可以容忍延迟的业务比如排行榜、计数类展示从库延迟几秒完全可接受。6.2 分库分表是手段不是目标分库分表看起来高大上代价也最重。做之前先想清楚是不是单表数据量真的到了几千万行索引深度和统计成本明显上升是不是单库连接数和磁盘IO已经用满如果数据量还在可控范围优化表结构和SQL更划算。一旦决定分就要面对分布式事务、跨库JOIN、全局唯一ID、分页聚合这些复杂度整套系统会从技术栈复杂度直接跳一个台阶。水平分表最常见的方式有两种。一种是按哈希取模分片比如user_id % 32把数据均匀打散到32张表另一种是按时间范围分片比如按月份分表适合日志和流水类数据。哈希分片数据分布均匀但扩容时要迁移大量数据时间分片天然适合冷热归档但可能出现最新分片特别热的问题。分片键的选择是分库分表设计中最核心的决策必须跟着最频繁的查询条件走否则为了查一个不包含分片键的条件需要扫描全部分片。6.3 分库分表之后的三个新问题拆完之后原来一个JOIN就能解决的事现在要么在应用层组装要么用中间件做拼装性能都比单表差很多原来一条INSERT就是一个本地事务现在可能涉及多个分片要么引入分布式事务方案要么从设计上规避跨分片事务原来一个ORDER BY加LIMIT是全局排序现在要先在各分片排序再合并分页越深归并成本越高。这些复杂度不会因为中间件而消失只是被隐藏了一部分。我的建议是分库分表尽量晚做但设计时要为未来留好余地。比如唯一主键从一开始就用全局ID方案而不是依赖单库自增这样将来真到了分库分表那天能少很多改造工作。7. 第八种方法数据库自身参数与连接池调优到了这里索引、SQL、表结构、缓存和架构都尽量优化了还剩下最后一块数据库运行参数。这部分最容易被忽略因为它不是写代码但调错了也很容易翻车。7.1 连接池不是越大越好一个非常常见的误解是数据库连接不够就加连接数。实际上连接数过高时CPU大量花在线程上下文切换上数据库可能更快崩溃。连接池大小的经验公式大致是连接数约等于CPU核心数乘以2再加上有效磁盘数不同负载类型会有浮动。一个四核单盘的主库连接池建议在8到10左右而不是默认动辄几百。应用侧连接池和数据库侧的最大连接数要一起看应用侧设太小会抛连接等待异常设太大又会让数据库侧形同虚设。我曾经见过一台机器连接数打到上千线程切换开销占用了大量CPU业务请求排队反而更严重。7.2 缓冲池和刷盘参数以InnoDB为例最关键的参数是缓冲池大小它决定了多少数据页可以缓存在内存里。这个值通常设置为物理内存的60%到80%剩余留给操作系统和连接线程。如果这个值过小你会看到磁盘读比例很高同样的SQL查了一遍还要再查第二遍。调整后需要重启数据库生效所以建议在业务低峰期操作并观察缓存命中率的变化。刷盘参数里最常见的是事务日志刷新策略。设置为最安全模式每次事务提交都刷到磁盘设置为更快但要牺牲部分安全性的模式可以提高吞吐但会有丢最后几秒日志的风险。很多高性能配置都是往性能靠但数据库这种系统安全性和性能要权衡着来不要为了刷高并发牺牲可靠性。7.3 事务和锁的调优慢SQL易发现长事务更难防参数和连接之外事务与锁的调优往往决定系统有没有慢性病。一个长事务会持有行锁或间隙锁很长时间下游所有等这一行的请求都会堆积在锁等待里。慢SQL日志看的是单条语句的耗时锁等待可能来自别的事务所以排查时不能只看自己的SQL要看数据库当前的事务列表和锁等待信息。常见解法是把大事务拆小别在一个事务里做太多无关操作尽量用等值条件锁定少的行读多写少的报表类查询放到从库执行避免跟在线事务抢锁。隔离级别同样影响锁的范围默认的可重复读会加间隙锁如果业务可以接受读已提交隔离级别改成它通常在并发上更宽松。但隔离级别必须由业务一致性需求决定不能为了并发放宽而出现幻读等隐蔽问题。7.4 定期维护统计信息、碎片、慢查询周报参数调优不是一次性的数据库运维需要固定节奏。表统计信息长期不更新优化器可能选错索引导致一条本来很快的SQL突然变慢频繁删除和更新会让表产生碎片扫描效率下降可以考虑在低峰期执行表优化操作重建表的存储布局。我习惯每周固定做一次慢查询报告把新增的高耗时SQL按模板聚合排一次序同时核对缓存命中率和主从延迟趋势。数据库优化不是做完八种方法就一劳永逸它更像持续迭代的看护过程每次版本上线后都应该重新审视数据访问模式有没有变化。最后说一点个人体会。这八种方法并不是并列关系优先级要跟着业务阶段走小库小表先把慢日志和执行计划看明白再动手优化索引和SQL数据量上来之后才谈缓存、读写分离和分库分表参数调优任何时候都可以做但要拿监控数据说话别盲目抄别人配置。我踩过的坑里最贵的一条是没做定位就动手改了半个月配置最后发现只是一条查漏数据的SQL不会利用索引。所以不管你打算用其中哪种方法都建议先把执行计划这一课补上。