为什么慢查询那么多 看看这类SQL索引优化技巧

SQL优化是数据库性能保障的核心手段，其主要目标是消除慢查询并提升数据访问效率。多数慢查询的根源是SQL语句未能有效利用索引，我们通常凭经验为where条件和Join字段添加索引，却未以慢查询日志为基准做针对性监测与优化，导致索引失效风险避无可避。一个可落地的SQL优化方案往往包含三步：定位慢查询、解析执行计划、评估改动后的实际代价。

慢查询日志是SQL优化的起点

未经监测的SQL优化是盲目的。我们应优先开启数据库的慢查询日志或性能监控工具，从真实业务流量中筛出耗时异常的具体语句，优化才有明确对象。

MySQL的慢查询日志可通过参数控制，将超过指定阈值的查询记录到文件，再借助mysqldumpslow等工具汇总高频慢语句。据MySQL官方手册《The Slow Query Log》描述：慢查询日志记录了执行时间超过long_query_time阈值的SQL，默认阈值是10秒，但在高并发生产环境建议调整为1秒以内。

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

日志只是起点，后续需对慢语句逐条做执行计划分析。如果一条查询的执行计划中出现了全表扫描（ALL），且数据量达到数万行甚至更多，该SQL需要有明确理由才可容忍这种糟糕的访问类型。数据量较小的配置表偶尔全表扫描可接受，但核心业务大表必须避免。

索引失效的常见场景识别

索引并不会在每次查询时都生效，我们通常会因在索引列上附加函数、隐式转换或模糊查询等操作，使优化器放弃索引扫描。明确这些失效场景后，SQL优化才能对症下药。

下列表格汇总了几类高频索引失效场景及其危害级别。请在排查慢查询时逐一比对。

在索引列上附加函数是一个高频误区。例如在create_time上加了DATE函数，索引为该列建立的B+树将无法按原值定位数据。此场景的修复方案是将列值写入一个范围或使用等值条件：把函数运算放在输入参数一侧，即WHERE create_time >= '2025-01-01' AND create_time < '2025-01-02'。这种改写方法能保持索引列纯净，让优化器准确利用B+树查找能力。

执行计划中的访问类型解读

在SQL优化过程中，我们要把explain语句输出的访问类型作为核心判断依据，其含义简单直接：访问类型越靠前，扫描成本往往越低。实际业务中需要保证绝大多数查询能达到ref或range级别。

MySQL的EXPLAIN输出，在type列中呈现访问类型的优劣顺序为：system, const, eq_ref, ref, range, index, ALL。system和const表示表中最多一行匹配，属于最优访问路径；eq_ref常见于主键或唯一索引的等值关联；ref表示通过普通索引等值匹配；range表示索引范围扫描，适合处理in、between等条件；index级别表示全索引扫描，通常不如range高效；ALL代表全表扫描，当表数据量大时查询性能会急剧下降。

我们常用EXPLAIN确认SQL的访问路径、可能使用的候选索引，以及最终采用的索引。至于执行计划中是否有额外排序和临时表，同样决定着SQL的高负载环节。优化排序时，我们可以让排序字段与索引的列顺序保持一致，尽可能让索引自带顺序避免filesort。

EXPLAIN SELECT order_id, customer_id, amount
FROM orders
WHERE customer_id = 12345
ORDER BY created_at DESC;

执行该命令后，我们需要关注是否出现Using filesort。若出现，则需要尝试对orders表的(customer_id, created_at)建立联合索引，让WHERE与ORDER BY共用同一条索引路径，从而同时消除回表和排序开销。

JOIN与子查询的改写策略

关联查询和子查询的SQL优化核心在于减少扫描行数，我们可优先通过小表驱动大表、合理使用派生表合并来降低关联复杂度。在多数在线业务场景中，我们倾向使用内连接作为首选，因为它能天然过滤无匹配数据。

关联查询的驱动顺序并非由我们主观指定，而是由优化器基于表统计信息测算。优化器会优先选择行数较小的表作为驱动表。当统计信息不准确时，执行计划可能出现偏差，让我们手写ANALYZE TABLE重新收集统计信息。针对子查询，MySQL优化器会在较新版本中将部分子查询转换为半连接形式。据MySQL官方手册《Optimizing Subqueries, Derived Tables, and View References》说明：优化器将IN子查询转换为半连接或物化操作，以避免逐行执行子查询的代价。

我们在实际项目中发现，当in子查询列表较长时，将子查询改写为显式JOIN并附带一个相等条件，利于优化器选择更优执行路径。但改写必须保持结果集一致，防止出现笛卡尔积或重复行。如果业务允许重复数据存在，使用JOIN可以减少一次IN查询的临时表构造开销。

覆盖索引的实现路径

覆盖索引让辅助索引直接返回所需列，避免回表带来的随机I/O开销，是SQL优化中性价比极高的手段。要达成覆盖索引，我们只需要保证select的字段与where条件都被包含在某一条联合索引内。

例如电商订单表频繁按照商户号查询金额汇总，简单索引仅建立在merchant_id上时，查询金额列需要回表获取数据。若建立(merchant_id, amount)联合索引，查询语句将只扫描索引页而无须回表。据行业技术社区Percona Performance Blog的案例分析：覆盖索引通常可节省一次主键回表访问，在OLTP场景下查询性能可能提升数倍。

CREATE INDEX idx_merchant_amount ON orders(merchant_id, amount);

覆盖索引一旦建立，在统计金额的报表SQL中会立刻生效。但不要为所有查询都盲目增加字段，索引本身有存储成本，并且写路径会因维护索引而增加延迟。我们应该优先为高频聚合查询设计覆盖索引。

长事务与锁等待是隐形的SQL瓶颈

一条SQL执行缓慢，有时并非语句本身效率低，而是它被阻塞在前置事务持有的行锁上。这时候我们需要对事务并发控制和SQL执行体量同时做监控。

长事务持有行锁会阻塞其他写操作，导致业务线程堆积和数据延迟。查看MySQL当前锁等待信息，可以用performance_schema中的data_lock_waits表。据MySQL官方手册《Using Dynamic SQL》说明：要维护稳定的事务模式，应该避免在事务中执行远程调用或长时间的多表更新操作。

当锁等待发生时，可从information_schema.innodb_trx中查找当前活跃事务及运行时间，再通过kill命令终止长时间占用回滚段的事务，让堆积的SQL尽快恢复。事务期间的SQL优化重点是减少事务内单条语句的耗时。长事务的存活时间越短，整体锁冲突概率越低。

我们如何验证SQL优化的收益

SQL优化完成后需通过重复执行和压测对比，观察执行时间、扫描行数和锁等待时长的变化。只有量化收益后才能将优化方案稳妥上线。

针对线上的同一条慢SQL，可以在测试环境准备与生产等量的数据，并分别记录优化前的执行时间、explain中的key和rows。优化后再次执行同一条SQL，对比查询耗时和扫描行数。若执行时间下降超过一个数量级，且rows值大幅减少，优化效果便有了客观依据。

监控慢查询日志中同类SQL的出现频次，也是验证收益的有效方式。上线优化版本后，固定周期观察日志中对应模式的SQL数量。如果由高频变为低频或消失，说明该SQL已经由慢转快。若仍有少量慢查询，则继续比对explain输出，找到新的瓶颈列或重新设计索引。

SQL优化从来不是一次性工作。表体量增长、查询模式变化与索引统计信息更新都会让旧方案失效。按季度或半年做一次系统性的慢查询复查，能让我们的数据库始终运行在稳定性能区间。