SQL查询优化：预估行数与实际行数差异过大导致执行过慢如何解决

行数估计与实际行数偏差的诱因 #

• 统计信息过时：数据库维护的表、索引行数及数据分布统计长期未更新，优化器基于陈旧数据生成执行计划，是最常见的诱因。通常大表写入、删除、更新占比超过20%后未触发自动统计更新时，极易出现这类问题。
• 谓词逻辑超出统计覆盖范围：多列联合过滤、过滤列被自定义函数包裹、前缀带通配符的模糊查询、字段隐式类型转换等场景下，优化器无法基于现有单列统计信息准确计算过滤后的行数，只能采用默认估值规则，偏差会大幅升高。
• 临时对象统计缺失：查询中使用临时表、表变量时，多数数据库默认对这类对象的行数估值为固定值（比如SQL Server表变量默认估值为1行），如果实际存储数据量远大于默认值，就会出现极大偏差。
• 统计信息采样率不足：大表更新统计信息时如果采用低采样率，无法准确反映数据真实分布，尤其是存在倾斜数据时，低采样很容易漏算高频值的实际占比，导致估值偏离。
• 数据倾斜严重：某几个列值的出现频率远高于平均水平，统计信息未捕获到这类倾斜特征时，优化器会按照平均分布计算过滤行数，最终估值和实际值差异巨大。

相关优化建议 #

• 优先更新对应查询关联表的全量统计信息，不同数据库的参考命令如下：
  • MySQL：ANALYZE TABLE 表名;
  • SQL Server：UPDATE STATISTICS 表名 WITH FULLSCAN;
  • PostgreSQL：ANALYZE 表名;
• 检查查询语法，调整不合理的谓词写法：避免对过滤列使用函数包裹、避免隐式类型转换、避免前缀带通配符的模糊查询，让过滤条件可以匹配到现有统计信息。例如将WHERE DATE(create_time) = '2024-01-01'调整为WHERE create_time BETWEEN '2024-01-01 00:00:00' AND '2024-01-01 23:59:59'。
• 涉及多列联合过滤的场景，可以在对应列上创建多列统计信息或者联合索引，为优化器提供更精准的数据分布参考。
• 用到临时表、表变量的大数量查询，可以手动触发临时表统计信息更新，也可以根据实际场景替换为CTE、实体临时表存储，部分数据库还支持对表变量添加统计信息提示来修正估值。
• 存在严重数据倾斜的场景，可以针对高频倾斜值单独适配查询逻辑，或者开启数据库自带的倾斜查询优化配置。
• 确认估值偏差修复后，重新查看执行计划，检查连接顺序、索引选择是否已经被优化器调整到合理状态，针对大扫描量的节点可以单独添加适配索引减少数据扫描量。

查询统计截图 #

内容的提问来源于stack exchange，提问作者MorgoZ