AI赋能SQL优化：百倍性能差距一秒识破

一条SQL在数据库A运行缓慢，而在数据库B表现正常，执行时间差异超过百倍。研发将问题交由AI处理，视频展示了AI自动定位并辅助修复的全过程。

AI赋能SQL优化：百倍性能差距一秒识破

差异初步分析

1、索引差异：

• 检查 BB_TABLE1 表的 NAME 列是否有索引。

• 如果241 库的 NAME 列有索引，而 178 库没有索引，会导致查询性能差异显著。

2、数据量差异：

• 检查两个库中 BB_TABLE1 表的总数据量是否不同。

• 如果 178 库的数据量远大于 241 库，可能导致查询时间更长。

3、数据库性能差异：

• 检查两个数据库的硬件配置（如 CPU、内存、存储性能）。

• 178 库可能负载较高，或者资源分配较少。

4、统计信息：

• 检查两个库中表的统计信息是否最新。

• 如果 178 库的统计信息过时，可能导致优化器选择低效的执行计划。

5、网络延迟：

• 178 库的网络延迟可能较高（如 192.168.5.178 的网络环境较差）。

6、表结构或数据分布：

• 检查两个库中 BB_TABLE1 表的结构是否完全一致（如分区、压缩等）。

• 数据分布是否不同（如 178 库的 NAME 列值分布更分散）。

• 建议的排查步骤

  检查索引：

  • 在 178 库和 241 库中分别查询 BB_TABLE1 表的索引信息，确认 NAME 列是否有索引。

  检查数据量：

  • 查询两个库中 BB_TABLE1 表的总行数。

  检查执行计划：

  • 在 178 库和 241 库中分别执行 EXPLAIN PLAN FOR 命令，查看执行计划差异。

  检查统计信息：

  • 确认两个库中表的统计信息是否最新。

  检查数据库负载：

  • 检查两个数据库的 AWR 报告或性能监控数据，确认是否存在资源瓶颈。
  • 如果需要进一步分析，可以提供以下信息：
  • 两个库中 BB_TABLE1 表的索引信息。
  • 两个库中 BB_TABLE1 表的总行数。
    执行计划对比。

深入分析锁定原因

1、 索引差异

• 178库：
  BB_TABLE1 表的 NAME 列没有索引。
  执行计划显示为 TABLE ACCESS FULL（全表扫描），扫描了 3,576 个逻辑块（Cost=3576）。

• 241库：
  BB_TABLE1 表的 NAME 列有索引 IXYUE。
  执行计划显示为 INDEX RANGE SCAN（索引范围扫描），仅扫描了 3 个逻辑块（Cost=3）。

• 结论：
  241 库由于 NAME 列有索引，查询时直接通过索引定位数据，性能显著提升（3ms）。
  178 库由于缺少索引，需要全表扫描 1,500,000 行数据，导致性能较差（324ms）。

2、数据量

• 两个库中 BB_TABLE1 表的总行数均为 1,500,000 行，数据量一致。
• 排除数据量差异对性能的影响。

3、执行计划对比

• 178库：
  执行计划为全表扫描（TABLE ACCESS FULL）。
  动态统计信息（dynamic sampling）被启用，但未能优化查询。

• 241库：
  执行计划为索引范围扫描（INDEX RANGE SCAN）。
  索引 IXYUE 显著减少了扫描范围。

• 结论：
  178 库的查询性能瓶颈主要源于缺少索引，导致全表扫描。
  241 库的索引设计合理，查询高效。

4、 统计信息

• 178 库的执行计划显示使用了动态统计信息（dynamic sampling (level=2)），说明统计信息可能未及时更新或优化器无法准确估算。
• 241库的统计信息可能更准确，优化器能够正确选择索引。

5、数据库性能指标

• 178 库的全表扫描查询耗时 324ms，而 241 库的索引扫描仅耗时 3ms。
• 如果 178 库的硬件资源（如 I/O 性能）较差，可能进一步放大性能差异。
• 建议：检查 178 库的 I/O 延迟和 CPU 使用率，确认是否存在硬件瓶颈。

6、 其他原因

• 网络延迟：178 库的网络延迟可能较高，但主要影响的是连接建立时间，而非查询执行时间。
• 表结构差异：两个库的表结构完全一致，排除表设计差异的影响。

最终结论

1、 主要原因

• 178库：NAME 列缺少索引，导致查询必须全表扫描，性能较差。
• 241库：NAME 列有索引，查询通过索引快速定位数据，性能极佳。

2、 次要原因

• 178 库的统计信息可能需要更新。
• 178 库的硬件资源（如 I/O）可能较差。

解决方案

1、在 178 库的 NAME 列上创建索引：
CREATE INDEX A103.IDX_BB_TABLE1_NAME ON A103.BB_TABLE1(NAME);

2、更新统计信息：
EXEC DBMS_STATS.GATHER_TABLE_STATS(‘A103’, ‘BB_TABLE1’);

3、检查硬件资源：
如果 178 库的 I/O 或 CPU 资源不足，考虑优化硬件配置。

4、监控长期性能：
创建索引后，重新运行查询并监控性能是否达到预期。

结论

通过创建索引信息和更新统计信息后，两库执行时间仅相差2毫秒，这2毫秒的差异，可能源于178库的I/O 或 CPU 资源不足