最近几天我连续处理了两起线上生产事故根源全是SQL性能问题。一个是没走索引的全表扫描另一个是滥用子查询导致临时表疯狂膨胀最后数据库CPU直接被打满接口超时报警轰炸了整个值班群。处理完这两起事故我最大的感受是SQL优化这门手艺很多人在原则层面都知道一落到具体场景就抓瞎。这篇文章我想把索引策略和查询调优的完整链路从头到尾拆一遍把我踩过的坑、验证过的方法、以及从慢SQL日志中总结出的规律全部沉淀下来。内容会涉及MySQL和Oracle两种主流数据库的差异点覆盖存储引擎选择、索引底层原理、索引失效全场景、慢SQL定位手段、查询改写技巧和并行SQL的实战经验。这不是给新手看的概念科普而是可以按图索骥的排查手册和调优清单。1. 整体设计思路慢SQL诊断不能靠猜要有完整方法论1.1 性能问题的三层漏斗索引、SQL写法、数据库架构面对任何一条慢SQL我建议你先建立一个三层漏斗的排查思维。第一层是索引策略层检查这条SQL能不能走索引、走了索引之后有没有回表、索引选择性够不够高。第二层是SQL写法层检查是不是查询条件里做了函数包裹、隐式类型转换、前缀模糊匹配这些写法哪怕有索引也可能绕过索引。第三层是数据库架构层涉及表数据量级、表设计合理性、数据库配置参数、硬件资源瓶颈这些因素决定了同样一条SQL在相同索引策略下的最终表现。这个排查顺序不能乱。我见过很多人在慢SQL出现后第一反应是加内存、升CPU结果加完配置SQL还是慢最后执行计划一放出来连索引都没建。先看索引和SQL写法这两层是成本最低的优化手段改个SQL、加个索引可能几秒钟完成而架构调整往往需要停机变更影响面大得多。从经验看线上90%的慢SQL问题出在前两层。我自己维护过的业务系统里最典型的场景是订单表和用户表数据量到千万级别后前端查询接口开始变慢慢SQL日志里捞出来的语句几乎全是索引策略不合理造成的。所以SQL优化首要任务永远是先把执行计划打出来看看每一张表的访问路径是什么。1.2 优化前后必须回答的三个问题每次做SQL优化我都会强迫自己回答三个问题。第一个问题这条SQL的执行计划到底是什么是全表扫描还是索引范围扫描有没有出现Using filesort、Using temporary这样的危险信号。第二个问题优化目标是什么是要把响应时间从5秒降到500毫秒以内还是把CPU消耗降下来或者让查询能够稳定撑住峰值流量。目标不同方案完全不同。第三个问题怎么验证优化有效不能在测试环境随便跑一下就说优化完成必须准备好压测数据、真实数据分布甚至要在灰度环境里观察一段时间。这里有个很重要的习惯拿到慢SQL先不要急着改代码先去数据库里把执行计划捞出来盖上执行计划看SQL再盖上SQL看执行计划反复对照。很多问题在执行计划面前是一目了然的。比如我排查过一条多表关联的慢SQL耗时6秒执行计划显示驱动表选错了小表被放在被驱动位置导致大表被反复扫描。这种情况下改SQL的join顺序或加hint几秒钟就能把耗时拉回300毫秒根本不需要动索引。1.3 不同数据库MySQL/Oracle的优化侧重点差异MySQL和Oracle虽然都是关系型数据库但SQL优化的侧重点有明显差异。MySQL的优化器相对简单对复杂SQL的支持和改写能力有限所以更依赖好的索引设计和简洁的SQL写法。Oracle拥有成熟的CBO优化器基于成本的优化器它对统计信息的依赖度非常高很多时候你重写SQL半天不如重新收集一次统计信息效果明显。Oracle还支持和并行SQL、物化视图、分区裁剪等高级特性在大型数仓场景中优势明显。具体到实战层面MySQL里我很少用hint因为优化器选择不佳的时候我更倾向于通过调整SQL写法来引导优化器。而Oracle环境下在处理大批量数据查询时合理使用并行提示能带来几十倍的性能提升但并行度过高又会引发资源争抢这个问题后文会有专门的实操章节展开。理解这些差异能帮你准确判断一个优化方案在MySQL里有效换到Oracle里是否依然成立避免盲目迁移经验。2. 索引策略核心从存储引擎机制到索引选型全景2.1 MySQL存储引擎对比InnoDB为什么是默认首选MySQL的存储引擎曾经百花齐放MyISAM、InnoDB、Memory、Archive各有拥趸但现在新业务上线我几乎只用InnoDB。原因在于InnoDB支持事务、行级锁、外键和崩溃恢复而MyISAM只有表级锁不支持事务写入并发一大就全线阻塞。你可以这么理解MyISAM像共享单车一个人骑很自由人一多就乱套InnoDB像私家车每辆车都有自己的车道堵车概率大幅下降。InnoDB的底层数据结构是B树所有数据都存储在聚簇索引clustered index中。聚簇索引的叶子节点直接保存整行数据因此通过主键查询时一次索引定位就能拿到完整记录。而MyISAM的索引和数据文件是分离的索引叶子节点存储的是数据行的物理地址查询时需要先查索引再定位数据逻辑上多了一层跳转。这也是为什么相同条件下InnoDB主键查询通常比MyISAM更高效的原因。从运维角度看InnoDB的缓冲池buffer pool设计也非常关键。它能将热点数据和索引页缓存在内存中大幅减少磁盘I/O。你调优InnoDB时第一个要看的参数就是innodb_buffer_pool_size一般建议设置为服务器物理内存的70%左右。我在生产环境中曾把这个参数从4GB提到32GB同样的业务流量磁盘读请求骤降80%效果立竿见影。把存储引擎的执行机制搞清楚再看索引和SQL问题很多疑惑会自然打开。2.2 主键索引和唯一索引差别不只是空值主键索引和唯一索引是面试题里的常客但真正在表结构设计时能清楚区分的人并不多。主键索引Primary Key的约束是表中每一行数据都拥有唯一标识且不允许为NULL。唯一索引Unique Key的约束是索引列的值必须唯一但允许空值存在而且在MySQL中唯一索引允许多个NULL值。有一个容易忽略的细节主键索引在InnoDB里承载着数据物理排列的逻辑。因为InnoDB是聚簇索引组织表数据行的物理顺序由主键决定所以主键的选择直接决定插入性能。用自增整型主键是最优选择因为新数据总是追加在索引树的末尾不需要频繁触发页分裂。相反如果使用UUID这类随机值作为主键每次插入都会让B树做大量节点分裂和重组写入性能会急剧恶化。我接手过一个表主键是32位UUID每秒插入只有几百条改成自增ID后直接能到每秒上万条差别极为悬殊。从查询功能上看主键索引和唯一索引都能保证唯一性但如果查询条件本身是业务字段而不是主键比如用户表的手机号此时用唯一索引更合适。手机号可以允许未来出现某种极端情况下的NULL业务上通常不出现但设计上唯一索引给你留了这个余地。实际开发中还有一个原则所有表必须有主键没有主键的InnoDB表会自动生成一个不可见的6字节rowid作为聚簇索引这会让你失去按业务主键进行高效查询和定位的能力。2.3 索引选择性高选择性字段才能体现索引威力索引不是建得越多越好而是要关注索引列的选择性Selectivity。选择性定义为索引列去重后的值数量与总行数的比值。假如一张用户表有100万条记录性别列只有“男”“女”两个值那选择性是2/100万极低。用性别列建索引查询结果依然可能返回50万行数据库优化器大概率会走全表扫描因为索引树遍历回表的成本比全表扫还高。这样的索引就是典型无效索引白白占用存储空间还拖慢写入速度。高选择性的典型是主键、订单号、身份证号这类几乎每行都唯一的列。实战中联合索引的建立也要权衡每个字段的选择性。经验法则是区分度高的列放在联合索引的最左侧区分度低的列放在后面。比如订单查询经常按“用户ID 订单状态 创建时间”用户ID选择性高应该放在最左订单状态只有几个枚举值选择性低放在后面作为补充过滤条件。判断一个字段是否适合建索引最简单的办法是执行这个SQL对比结果集数量SELECT COUNT(DISTINCT column_name) / COUNT(*) AS selectivity FROM table_name;选择性低于20%的字段除非作为联合索引的辅助列否则单独建索引意义不大。这条规则在日前的慢SQL分析中又一次得到验证——一个状态字段单独建了索引却被优化器冷落全表扫描依旧换成联合索引后立竿见影。2.4 联合索引设计最左前缀原则和列顺序的黄金法则联合索引是把多个列合起来建立一个索引树通过最左前缀原则来匹配查询条件。所谓最左前缀原则可以这样理解联合索引像一本多级目录第一列是页数第二列是章节第三列是具体小节。查询如果你不告诉目录第一列的筛选条件比如不带第一列直接用第二列去查那这个索引就失效了因为B树按第一列有序排布跳过了第一列第二列的顺序完全无法利用。实际设计联合索引我的法则很简单先看等值查询条件哪些列是等值匹配的按区分度从高到低排再看排序字段ORDER BY和GROUP BY涉及的列能放进索引就尽量放进去因为索引天然有序这可以消除filesort最后才是范围查询字段范围查询一旦命中该字段之后的索引列将无法继续用于过滤。一句话总结等值条件优先排序字段居中范围字段放最后同时遵守最左前缀。举个例子表里有字段a、b、c最常见的查询是WHERE a ? AND b ? ORDER BY c。那么建议索引顺序是(a, b, c)。这种情况下查询第一次就能用a定位再用b精确缩小范围最后用c排序时直接走索引顺序连排序文件都不用生成。如果把索引建成(a, c, b)排序字段c虽然能复用但b这个等值条件会出现在range之后索引效果打折。我经常用EXPLAIN里的rows字段对比不同索引顺序的效果字段顺序对预估扫描行数影响非常大这种实验做几次就能建立起设计直觉。2.5 覆盖索引与回表查询为什么少了这步查询快十倍InnoDB使用聚簇索引组织数据二级索引非主键索引的叶子节点存储的是索引列和主键值。当你用二级索引查询时需要先找到主键值再用主键去聚簇索引中找回整行数据这个过程叫回表。回表是一次额外的I/O操作数据量大时性能损耗极其明显。覆盖索引是一种特殊的索引设计查询所需的所有字段都能从二级索引中直接获取无需回表。这条SQL就是典型SELECT user_id, order_status FROM orders WHERE user_id 12345;如果orders表有联合索引(user_id, order_status)那么查询条件匹配二级索引后叶子节点上已经包括user_id和order_status两个字段直接返回即可不需要再回表。相比建成单列索引user_id后再回表取order_status性能差异在数据量大时可以到10倍甚至更高。设计覆盖索引时要注意一个反直觉现象SELECT的字段越少越好。频繁出现SELECT *的场景是覆盖索引的杀手。反正查全表字段二级索引永远覆盖不了只能回表。所以SQL优化的第一步往往是审视SELECT的字段列表把不需要的列全部去掉这既是网络传输的优化也是回表优化的前置条件。3. 慢SQL定位与查询调优从执行计划到改写实操3.1 慢查询日志配置与关键参数解读想做慢SQL优化第一步是知道哪些SQL慢。MySQL的慢查询日志Slow Query Log是定位慢SQL的第一利器。常规配置方式如下-- 查看当前慢查询日志状态 SHOW VARIABLES LIKE slow_query_log; -- 开启慢查询日志 SET GLOBAL slow_query_log ON; -- 设置慢查询阈值单位为秒 SET GLOBAL long_query_time 1; -- 设置日志文件路径 SET GLOBAL slow_query_log_file /var/log/mysql/slow.log;生产库的阈值一般设为1秒或者0.5秒。不是说所有慢SQL都一定要低于这个值而是我们需要通过日志把超过预期响应时间的SQL捞出来。配合pt-query-digest这类工具可以按总耗时、执行次数、平均耗时进行排行快速找出TOP N问题SQL。除了执行时间和扫描行数慢查询日志很重要的价值在于帮你建立业务SQL画像。我观察过线上业务慢SQL的分布80%集中少数几条固定的SQL模板上它们占用的资源远超想象。把这些模板优化掉整体数据库负载立刻下了一个台阶。所以建立“慢SQL日报”机制很有必要每天早上花十分钟看昨天的TOP10慢SQL一个季度坚持下来数据库性能问题基本能消灭大半。基础操作之外也建议启用performance_schema或者sys库的statements_with_runtimes_in_95th_percentile视图这类数据能帮你分析慢SQL的执行计划明细。3.2 EXPLAIN执行计划深度阅读type、rows和Extra一网打尽拿到慢SQL后第一动作是用EXPLAIN看执行计划。EXPLAIN的结果表里最关键的字段有这几个type、rows、filtered、Extra。type字段标识访问类型性能从好到差依次是system、const、eq_ref、ref、range、index、ALL。看到type为ALL意味着全表扫描必须警惕。看到type为index意味着全索引扫描也不一定是好事说明优化器最终选择了遍历整个索引树而不是表但如果扫描范围还是很大性能依然不理想。range代表范围扫描是常见的优化目标ref代表通过普通索引等值匹配是健康的访问方式eq_ref和const是非常理想的访问方式多见于主键或唯一索引匹配。rows字段是优化器估算的需要扫描的行数这个值越小越好。如果实际查询很快但rows很大说明优化器估算有偏差建议更新统计信息或者调整表结构。Extra字段是执行计划中最有信息量的部分常见的危险信号包括Using filesort查询需要额外排序性能消耗大、Using temporary查询使用了临时表常见于GROUP BY和部分子查询、Using index condition索引条件下推5.6及以后版本的优化手段以及Using where存储引擎返回数据后再过滤通常说明索引利用不充分。我常用EXPLAIN加FORMATJSON或EXPLAIN ANALYZE来获取更精确的耗时和扫描行数。MySQL 8.0的EXPLAIN ANALYZE直接给出每个步骤的实际执行时间这对判断瓶颈是索引还是排序还是连接有极大帮助。有一个容易踩的坑只看执行计划默认输出会忽略limit的影响而加了limit的SQL可能实际只扫描很少的行就返回了所以必须结合rows和实际响应时间一起判断。3.3 索引失效全场景复盘哪些写法会让好索引变废铁索引失效的场景我大概总结了十类。以下每一条都来自真实线上事故或复现实验。第一类对索引列使用函数或表达式计算。比如WHERE DATE(create_time) 2025-01-01这种写法导致索引列被函数包裹MySQL无法使用B树上有序排列的值做快速查找只能全表扫描。解决办法是改成范围查询上面的SQL等价于WHERE create_time 2025-01-01 AND create_time 2025-01-02这样索引就能生效。第二类隐式类型转换。如果表的id字段是varchar类型查询条件却写成WHERE id 10086MySQL会自动把字符串列转成数字再比较这导致索引失效。解决思路保证查询条件的字段类型和表字段类型一致或者强制使用字符串写法WHERE id 10086。有意思的是从MySQL 5.7开始某些场景下隐式转换不一定让索引完全失效但效果依然不可靠最好从写代码的习惯上根除。第三类前导模糊匹配。WHERE name LIKE %核心%。因为MySQL的B树索引是有序排列的无法利用字符串中间或尾部的模糊匹配只能用前匹配LIKE 核心%。业务若是真的需要中间匹配建议引入全文检索或ES而不是指望索引。第四类OR连接的查询条件如果有一个条件列没有索引那么整个条件就无法走索引。比如WHERE user_id 1 OR user_name 张三只有user_id有索引user_name没索引优化器可能选择全表扫描。解决方法是把OR改写为UNION前提是两边的条件都能独立走索引SELECT * FROM t WHERE user_id 1 UNION ALL SELECT * FROM t WHERE user_name 张三;第五类联合索引不满足最左前缀。这是最普遍的问题。表上有联合索引(a, b)查询条件却只写了b索引直接失效。有些开发者以为建了联合索引等于同时有(a, b)和(b, a)两个单列索引这是错误认知。第六类范围查询后面的索引列失效。WHERE a 1 AND b 10 AND c 2如果联合索引是(a, b, c)那么范围条件b之后的c列无法使用索引进行精确定位。设计联合索引时把范围查询的列尽量放在最后就是这个原因。第七类对索引列进行运算操作比如WHERE a 1 5这种写法无法利用索引。SQL优化时优先把运算移到等号右侧改成WHERE a 4。第八类列排序和索引顺序不匹配导致filesort。比如索引是(a, b)但ORDER BY b, a顺序反了索引无法直接用于排序。第九类NOT IN和NOT EXISTS效率陷阱。这类操作通常不走索引尤其是在数据分布不均的情况下。能用LEFT JOIN/IS NULL的方式改写往往效果更好。第十类索引列参与了JOIN且连接字段的排序规则或字符集不一致。这种情况下MySQL无法直接关联索引可能放弃索引而用hash join或block nested-loop。这十类场景我建议每位开发都打印一份贴在工位上。排查慢SQL时用这十条清单逐条比对命中概率极高。3.4 查询改写技巧子查询、JOIN和排序的优化方向SQL改写的本质是引导优化器选择更高效的执行路径。第一个高频问题是子查询滥用。MySQL 5.7之前IN子查询很可能会生成临时表导致性能急剧下降。现在8.0版本优化器已经能自动把半连接转化为更优执行计划但某些复杂嵌套子查询依然要手动改写。一个经典案例查最近一个月有订单的用户用IN子查询SELECT id, name FROM users WHERE id IN ( SELECT user_id FROM orders WHERE create_time DATE_SUB(NOW(), INTERVAL 30 DAY) );如果orders表数据量巨大子查询返回的user_id列表可能膨胀到几万条以上此时改成JOIN去重会更稳定SELECT DISTINCT u.id, u.name FROM users u JOIN orders o ON u.id o.user_id WHERE o.create_time DATE_SUB(NOW(), INTERVAL 30 DAY);第二个高频问题是JOIN顺序和驱动表选择。MySQL优化器通常选择小表驱动大表。当大表和小表的连接字段都有索引时执行计划基本没问题但如果没有索引MySQL可能选择块嵌套循环BNL模式在特定场景下伤害很大。判断标准小表能不能作为驱动表、大表的连接字段有没有索引。查询条件里能过滤行数最多的表放在最前面驱动是经验法则。第三个高频问题是分页排序。经典的深分页问题LIMIT 100000, 20MySQL要扫描前100020行再丢弃前100000行效率极低。解决办法有两种利用覆盖索引查出主键再回表查详情或者记录上一页的游标位置用WHERE id last_id LIMIT 20代替OFFSET。第一种的SQL形态是这样SELECT a.* FROM orders a JOIN (SELECT id FROM orders ORDER BY create_time DESC LIMIT 100000, 20) b ON a.id b.id;这个写法的子查询利用覆盖索引避免回表扫描只产出20个主键后再回原表取数性能提升非常显著。第四个需要关注的是GROUP BY和DISTINCT。GROUP BY天然带排序如果你只需要分组结果而不关注顺序加上ORDER BY NULL可以取消排序浪费。DISTINCT的实现也依赖排序或临时表数据量大时要考虑是否存在更优的改写方式。3.5 并行SQL优化什么时候该用什么时候千万别用并行SQL优化大多出现在Oracle和SQL Server环境中MySQL的并行查询能力在8.0版本依然很弱InnoDB的并行读和并行DML依然受限。主流的并行场景集中在Oracle的数据仓库和报表系统。Oracle中可以通过下面的形式启用并行SELECT /* PARALLEL(4) */ COUNT(*) FROM large_table WHERE status PENDING;遇到特别大的表并行扫描可以把一个需要20分钟跑完的统计SQL压缩到2分钟。但并行不是银弹它消耗的是系统整体资源。并行度开多少取决于数据库所在主机的CPU核心数量、I/O能力和当前负载。在OLTP交易系统上开并行往往一波查询就能打满CPU影响所有在线业务。我建议并行SQL只在明确的分析型查询中使用且最好在会话级别开启不要全局打开。更关键的是并行度的设定。经验公式并行度不要超过CPU核心数的一半。比如64核的数据库服务器并行度设16到32比较合理。如果并行度开得过高等待和调度开销反而会吞掉并行的收益。测试时一定要做多组对照比如并行度2、4、8、16各自跑一遍画出耗时曲线找到拐点。我遇到过环境里并行度设了64结果一张大表查询从3秒变成9秒因为系统被I/O等待拖死。Oracle里还有一批和并行相关的参数值得关注parallel_degree_limit限制最大并行度、parallel_max_servers并行服务进程上限、parallel_min_time_threshold低于该估算耗时则不启用并行。合理组合这些参数能防止并行失控。MySQL 8.0虽然引入了innodb_parallel_read_threads参数但主要作用于聚簇索引的扫描效果远不如Oracle的并行执行引擎也别指望在MySQL上靠并行解决所有慢查询。3.6 COUNT和SUM这类聚合SQL的优化手段很多慢SQL并不是复杂查询就是一句简单的COUNT。表数据量到了千万级别COUNT(*)可能需要扫全索引耗时数秒。这里有几个优化思路。第一个是使用近似值业务上如果允许误差直接使用SHOW TABLE STATUS里的rows字段它是优化器估算的行数执行成本几乎为零。第二个是维护计数器表利用事务在单独的计数器表中更新总行数每次查询直接读取计数器复杂度很低收益极大。第三个是分区裁剪如果表做了分区而查询条件能裁剪到特定分区COUNT只扫对应分区性能也会大幅提升。SUM和AVG这类聚合函数配合GROUP BY时要重点关注是否创建了覆盖索引。比如统计每个人最近的订单金额若查询字段是user_id和amount联合索引(user_id, amount)能让整个聚合过程只走索引无需回表。加上索引之后这类聚合SQL往往能实现几倍到几十倍的性能提升。值得提醒的是MySQL 8.0.13之后支持了窗口函数很多以前用GROUP BY和JOIN绕来绕去的复杂需求可以直接用窗口函数优雅解决。虽然窗口函数不一定更快但写法和执行计划更清晰方便后续调优。如果遇到数据量很大的聚合场景可以使用物化视图Oracle支持完好的特性、预计算表定时任务刷新汇总表等架构手段做更彻底的优化。4. 常见问题与排查技巧实录别让调优变成二次事故4.1 排查步骤速查表从异常日志到索引设计的黄金八步这里梳理一套完整的排查流程沙盘推演一遍从慢查询日志捞出目标SQL记录总执行次数、平均耗时、扫描行数。EXPLAIN查看执行计划确认type、rows、Extra的关键信息。用清单逐条排除索引失效情况确认索引设计是否有问题。如果执行计划显示全表扫描先别急着加索引检查该表的查询模式分布为最频繁使用的查询条件设计联合索引。评估覆盖索引可能性看能否减少回表。分析业务逻辑思考SQL本身是否可以被重写比如子查询改JOIN、深分页改游标。在测试环境用真实数据量验证新SQL和索引效果对比优化前后的执行计划、响应时间、扫描行数。灰度上线通过监控确认慢SQL消失且无其他性能反噬。这套流程虽然朴素但能完成90%的常规SQL优化任务。优化SQL最忌讳“一次性改完就直接上线”常见理解错误是本地表几万行跑得飞快就认定线上千万行也没问题。4.2 索引失效问题定位实例一个真实慢SQL的排查全过程前阵子我们线上一个查询接口反复超时慢SQL大概长这样SELECT order_id, amount, create_time FROM orders WHERE DATE_FORMAT(create_time, %Y-%m-%d) 2025-03-15 AND status SUCCESS ORDER BY amount DESC LIMIT 50;orders表当时有3000万行create_time和status上有两个独立索引。第一次打开EXPLAINtype是ALLrows显示近千万。索引失效原因清晰可见对create_time使用了DATE_FORMAT函数导致索引无法使用status虽有索引但选择性太低只有三种取值优化器认为全扫更便宜。我把SQL改写成范围查询同时调整了索引方案SELECT order_id, amount, create_time FROM orders WHERE create_time 2025-03-15 00:00:00 AND create_time 2025-03-16 00:00:00 AND status SUCCESS ORDER BY amount DESC LIMIT 50;这个改写让create_time能走索引但新问题来了ORDER BY amount没走索引Extra里出现了Using filesort。于是我在orders表新建了联合索引(status, create_time, amount)查询条件里status是等值create_time是范围amount用于排序这个索引既过滤了大部分数据又直接供排序使用。优化后同一个查询的响应时间从4.5秒降到80毫秒。整个过程加起来不到半小时可见大多数索引和写法层面的问题根本不至于上升到买机器换架构的层面。4.3 索引误建与冗余索引副作用也要纳入考虑索引不是免费的午餐。每张表的每个索引在写入数据时必须同步更新这带来额外的写放大和存储开销。我们在优化读查询的同时必须评估对INSERT、UPDATE、DELETE性能的影响。有一个运维场景让我记忆犹新为了给一个报表查询加索引一张每天千万级写入的流水表多了三个二级索引结果正常写入性能从每秒8000条掉到4000条主从复制延迟都拉起来了。最后不得已改成定时归档只保留最近一天索引。此后我设计索引时总会先估算写入频率和索引数量之间的平衡。冗余索引是另一个隐蔽问题。比如表上已经存在联合索引(a, b)又建了单列索引(a)那么后者就是冗余的。MySQL不会主动提醒你哪个索引多余需要借助工具分析比如pt-duplicate-key-checker定期扫描并删除冗余索引。减少冗余索引既节约空间也减少写入开销这个优化点的ROI非常高。4.4 优化器统计信息与SQL缓存两个容易被忽略的变量优化器基于统计信息做成本决策。当表数据变化剧烈或统计信息长时间未更新执行计划可能严重偏离实际。MySQL在开启innodb_stats_auto_recalc后会自动触发统计信息更新但某些批量删除数据的场景下统计信息可能失真。遇到明显偏差时手动执行ANALYZE TABLE table_name可强制更新统计信息。Oracle里则需要DBMS_STATS.GATHER_TABLE_STATS。我处理过一次诡异事故一条SQL之前走索引要2毫秒某天突然全表扫描要600毫秒排查半天发现是统计信息没跟上数据分布的变化ANALYZE之后执行计划恢复正常最终用了不到一分钟解决。SQL缓存则是老生常谈但依然重要的话题。MySQL 8.0彻底移除了查询缓存因为并发写场景下缓存命中率低且维护代价大。Oracle的共享池在软解析、硬解析之间还存在大量逻辑合理设置SGA参数能减少解析开销。但要注意执行计划缓存和结果缓存是两个概念。很多开发者以为查询第一次慢、第二次快就是有缓存兜底实际上在MySQL 8.0下完全是重复计算没有结果缓存可用。所以优化SQL时绝不要寄希望于“跑热了就好”要保证每条SQL在上线前就具备良好执行计划。4.5 工具箱推荐EXPLAIN、慢查询分析脚本和日常监控工欲善其事必先利其器。我日常依赖的工具主要有四类。第一类是执行计划分析工具MySQL的EXPLAIN、EXPLAIN ANALYZEOracle的DBMS_XPLAN.DISPLAY_CURSOR都能准确拿到实际执行计划。第二类是慢查询日志分析工具pt-query-digest是Percona Toolkit中的明星产品能自动汇总同类SQL模板输出执行次数、平均耗时、总耗时排行非常方便。第三类是索引分析工具pt-duplicate-key-checker负责发现冗余索引sys.schema_unused_indexes视图可以查出长期未使用的索引。第四类是整库监控工具Prometheus加上mysqld_exporter、Grafana的经典组合可以把慢SQL数量、锁等待、磁盘I/O全部可视化能帮你快速判断数据库的整体健康状态。日常监控建议按天粒度记录几个关键指标平均慢查询数、最高慢查询数、慢查询Top5的SQL文本、平均锁等待时间、临时表数量。这些指标可以写成一个定时脚本每天输出一份报表。坚持观察两周基本能发现自己业务的性能规律。5. 实战心得总结调优经验的边界与取舍也许你会注意到整篇文章我始终在强调三个词执行计划、索引设计、SQL改写。SQL优化的门道再多本质就是对这三件事的持续打磨。我在实际项目中还摸索出一个道理优化不能只盯单条SQL要回到业务视角看整体负载。有时一条SQL优化到极致但业务访问模式没有变化整体吞吐量依然上不去这时就要考虑增加缓存层、分库分表或者异步化。回到最原始的问题SQL优化为什么重要因为在互联网架构里数据库往往是系统中最脆弱也最贵的一环。加机器是扩容优化SQL是节流两者要配合。每次和大促流量对抗后复盘我都会发现那些撑住核心链路的系统底层SQL一定足够干净执行计划稳定索引管理有序。这里再分享两个容易被忽视的小习惯。第一个是每次做表结构变更时顺手更新表结构文档把索引设计意图写清楚避免后来者重复踩坑。第二个是建立慢SQL回归测试机制把每次优化的SQL和响应时间基线记录在案作为后续性能回归的对照依据。长期坚持下去你手头就有了一个不断增长的自有知识库。数据库优化是持久战没有一劳永逸的方案。表的数据量会涨业务模型会变索引的热点也会转移。所以每隔一段时间就要重新审视线上的SQL表现淘汰失效的索引调整不合理的查询。只要保持这套方法论循环运转数据库的性能问题基本都在可控范围之内。