DISTINCT t.*导致执行计划变化且比DISTINCT t.id指定列名慢几十倍

zmix · 2025 年3 月 26 日 10:14

【使用环境】生产环境
【 OB or 其他组件】OB
【使用版本】4.2.1.10
【问题描述】清晰明确描述问题
【复现路径】DISTINCT t.*导致执行计划变化且比DISTINCT t.id指定列名慢几十倍
【附件及日志】

【备注】基于 LLM 和开源文档 RAG 的论坛小助手已开放测试，在发帖时输入 [@论坛小助手] 即可召唤小助手，欢迎试用！
执行慢执行计划.txt (5.9 KB)
执行快执行计划.txt (6.8 KB)

论坛小助手 · 2025 年3 月 26 日 10:14

根因分析场景: SQL性能问题
根因分析命令: obdiag rca run --scene=sql_performance_issue
日志采集场景: SQL性能问题
日志采集命令: obdiag gather scene run --scene=observer.perf_sql --env “{db_connect=’-h127.0.0.1 -P2881 -utest@test -p****** -Dtest’, trace_id=‘Yxx’}”

推荐使用 obdiag 工具进行根因分析和日志采集，以便确认问题所在。上述命令将帮助我们收集 SQL 执行相关的性能数据，并分析 DISTINCT t.* 导致执行计划变化且性能下降的原因。

麻烦您提供一些信息以便我们更好地帮助您：

是否可以提供具体的 SQL 语句以及表结构？这有助于我们更准确地定位问题。
当前执行计划是否有明显的变化？如果有，请提供两种执行计划的对比结果（可以通过 EXPLAIN 命令获取）。
数据量是否较大？如果是，请提供相关表的数据量和索引设计情况。
是否可以提供 trace_id 或者具体的执行时间范围，以便我们在日志中定位相关 SQL 的执行记录？
是否尝试过通过调整 SQL 写法或添加索引来优化性能？如果有，请描述具体的操作和效果。

附上敏捷诊断工具 obdiag 使用帮助链接

淇铭 · 2025 年3 月 26 日 10:34

执行计划没有办法看看着像是乱码了么？
1、执行快和慢的explain extended sql --保存在文本里不要脱敏

2、
日志采集场景: SQL性能问题
日志采集命令: obdiag gather scene run --scene=observer.perf_sql --env “{db_connect=’-h127.0.0.1 -P2881 -utest@test -p****** -Dtest’, trace_id=‘Yxx’}”

–根据时间和执行语句查询trace_id
select query_sql,svr_ip,TRACE_ID,client_ip,TENANT_NAME,user_name,DB_NAME,ELAPSED_TIME,RET_CODE,FROM_UNIXTIME(ROUND(REQUEST_TIME/1000/1000),’%Y-%m-%d %H:%i:%S’) from GV$OB_SQL_AUDIT
WHERE REQUEST_TIME>=‘2024-04-05 14:34:00’ and lower(query_sql) like ‘%select%’;

zmix · 2025 年3 月 26 日 11:24

慢的.txt (117.2 KB)
快的.txt (120.6 KB)
这是执行计划

论坛小助手 · 2025 年3 月 26 日 11:27

根据您提供的执行计划文件内容，我们可以对比两种 SQL 的执行计划差异，并分析性能问题的原因。以下是详细分析和建议：

1. 执行计划对比

**慢的执行计划（DISTINCT t.*）**

主要操作：
- 使用了 HASH JOIN 和 TABLE FULL SCAN。
- 存在多个子查询 (SUBPLAN SCAN) 和分组操作 (HASH GROUP BY)。
- 数据量较大时，TABLE FULL SCAN 和 HASH GROUP BY 的开销较高。
关键路径：
- 表 dsp_assets_data_approve_log 和表 a 进行了全表扫描。
- 结果集通过 HASH GROUP BY 去重。

快的执行计划（DISTINCT t.id）

主要操作：
- 使用了 NESTED-LOOP OUTER JOIN 和 MERGE GROUP BY。
- 子查询 (SUBPLAN SCAN) 和去重操作 (HASH DISTINCT) 更加高效。
关键路径：
- 只对 id 列进行去重，避免了对所有列的处理。
- 数据量较小，MERGE GROUP BY 和 NESTED-LOOP JOIN 的开销较低。

2. 性能差异原因

列选择的影响：
- DISTINCT t.* 需要对所有列进行去重，涉及更多的数据处理和内存消耗。
- DISTINCT t.id 只对单列进行去重，计算量显著减少。
执行计划的变化：
- DISTINCT t.* 导致优化器选择了更复杂的执行计划（如 HASH JOIN 和 TABLE FULL SCAN），增加了 I/O 和 CPU 开销。
- DISTINCT t.id 的执行计划更加简单，使用了更高效的 NESTED-LOOP JOIN 和 MERGE GROUP BY。
数据量的影响：
- 如果表中列较多或数据量较大，DISTINCT t.* 的开销会显著增加。

3. 解决方案

短期优化

明确需求，减少列选择：
- 如果只需要 id 列，直接使用 DISTINCT t.id。
- 如果需要其他列，可以通过子查询先去重 id，再关联其他列。例如：
```
SELECT t.*
FROM your_table t
WHERE t.id IN (
    SELECT DISTINCT id
    FROM your_table
);
```
添加索引：
- 在 id 列上创建唯一索引或普通索引，加速去重操作。
- 示例：
```
CREATE INDEX idx_id ON your_table (id);
```

长期优化

调整 SQL 写法：
- 避免使用 SELECT *，仅选择必要的列。
- 如果需要多列去重，考虑使用 GROUP BY 替代 DISTINCT。例如：
```
SELECT id, col1, col2
FROM your_table
GROUP BY id, col1, col2;
```
优化表设计：
- 如果表中存在冗余列或不必要的大字段，考虑拆分表或压缩存储。
监控和调优：
- 使用 EXPLAIN 分析执行计划，确保优化器选择了最优路径。
- 定期检查索引使用情况，避免无效索引。

4. 其他建议

采集诊断信息：
- 使用 obdiag 工具收集更多性能数据，定位具体瓶颈。例如：
```
obdiag rca run --scene=sql_performance_issue
```
联系技术支持：
- 如果问题仍未解决，可以提供详细的 SQL、表结构和执行计划，联系 OceanBase 技术支持团队获取进一步帮助。