sql优化方案
执行计划sql查询type是range但是rows扫描行数太多怎么办extra使用索引下推typerange说明索引用对了但rows太大说明索引“ selectivity选择性”不够或者我们的查询范围本身就太大了。Extra里出现Using index condition索引下推ICP是个好消息说明MySQL已经尽力在存储引擎层帮你过滤数据了。但面对几十万甚至上百万的rows依然会很慢。 第一步确认“扫描行数多”的根本原因先用EXPLAIN看两个关键信息这决定了你的优化方向看key_len索引长度如果你的联合索引是(a, b, c)但WHERE条件里只用了a那么key_len就只算a的长度。后果索引只定位到了a这个大范围然后在这个大范围内扫描所有行去匹配b和c。看filtered过滤百分比如果rows100万但filtered5%说明扫描100万行最终只返回5万行有95%的扫描是无效的这是最需要优化的信号。️ 第二步四种实战优化方案按优先级排序方案一调整联合索引顺序最有效0成本如果rows大是因为索引没有完全匹配查询条件优先调整索引列的顺序让它覆盖你的WHERE条件。核心原则将“等值查询”的列放在最前面把“范围查询, , BETWEEN”的列放在最后面。示例当前SQLWHERE a 1 AND b 10 AND c 5当前索引(a, b, c)。因为b是范围查询c的索引就失效了扫描的行数就是a1且b10的所有数据。优化将索引改为(a, c, b)。这样a和c都能精准匹配只有b是范围扫描扫描行数会大幅下降。方案二使用“覆盖索引” 延迟关联针对回表严重rows大的另一个隐形杀手是回表。即使索引过滤出100万行数据回表100万次到主键取数据I/O开销极大。如果Extra里有Using index condition说明没有Using index覆盖索引。核心思路利用“子查询”先走索引覆盖只查出主键ID再通过主键去关联取全部数据。SQL改写示例sql-- 原SQL回表严重 SELECT * FROM orders WHERE create_time BETWEEN 2023-01-01 AND 2023-04-01 AND status 1; -- 优化后延迟关联 SELECT * FROM orders o INNER JOIN ( SELECT id FROM orders WHERE create_time BETWEEN 2023-01-01 AND 2023-04-01 AND status 1 -- 这里只查主键覆盖索引避免回表速度极快 ) t ON o.id t.id;注意子查询里要建立一个(create_time, status, id)的联合索引确保子查询是全索引覆盖的。方案三在应用层做“分页游标”改写业务驱动如果你的range查询是为了“拉取近三个月所有订单”这是典型的大数据量导出场景不应该用limit做深分页也不适合一次性查出。做法放弃OFFSET改用游标查询。sql-- 每次只查上一次的最大ID SELECT * FROM orders WHERE create_time BETWEEN 2023-01-01 AND 2023-04-01 AND id #{lastMaxId} ORDER BY id ASC LIMIT 1000;效果让type变成range或ref但rows严格限制在LIMIT范围内彻底解决了扫描行数问题。方案四强制通过“索引提示”引导优化器特殊情况有时候MySQL优化器判断失误认为全表扫描比你的range索引快可能因为统计信息不准。做法使用FORCE INDEX (idx_name)强制指定索引。sqlSELECT * FROM orders FORCE INDEX (idx_create_time) WHERE create_time BETWEEN ...;注意这种方法不推荐作为长期方案因为数据分布变化后强制索引可能变得更慢只适合紧急救火。 关于“索引下推 (ICP)”的特别说明你看到Using index condition说明MySQL 5.6 的特性已生效。这意味着存储引擎层会利用索引中的status字段假设在联合索引里先把不满足条件的数据过滤掉再回表。现状你已经有了ICP说明存储引擎层已经尽力了。瓶颈现在的瓶颈大概率在回表和网络传输上。所以你接下来的优化重点应该放在上面的方案二覆盖索引延迟关联和方案三游标分批上。 总结与建议针对你的情况我给出一句口诀如果业务逻辑允许优先用游标分页方案三这是最彻底的根治手段。如果必须一次性返回结果集优先调整联合索引顺序方案一并改写SQL用延迟关联方案二让子查询走覆盖索引大幅减少回表开销。最后千万记得用EXPLAIN验证优化后的结果重点关注rows是否下降以及Extra里是否出现了Using index这意味着覆盖索引是最理想的状态。如果行数依然很大那就要反思这个“近三个月”的查询范围本身是否合理是否应该加一些强制性的业务限制比如只查询最近100页的数据。这是很多性能问题背后的根本原因。