查找使用频率低的索引

OceanBase 技术问题
#1

【 使用环境 】生产环境
【 OB or 其他组件 】ob
【 使用版本 】4251
【问题描述】DBA_INDEX_USAGE 关联dba_objects ,返回599条,再关联 information_schema.STATISTICS 后返回8900条
【复现路径】问题出现前后相关操作
【附件及日志】推荐使用OceanBase敏捷诊断工具obdiag收集诊断信息,详情参见链接（右键跳转查看）：
select t.owner, t.object_name,c.table_name from
(select b.object_name,b.owner from DBA_INDEX_USAGE a left join dba_objects b on a.object_id=b.object_id where a.owner not in(‘mysql’,‘test’,‘oceanbase’,‘information_schema’) and total_exec_count<2 and last_used <‘2025-06-09 15:49:00’) t join
( select table_schema, index_name,table_name from information_schema.STATISTICS group by table_schema, index_name,table_name) c on t.object_name=c.index_name and t.owner=c.table_schema
group by t.owner, t.object_name,c.table_name

【备注】基于 LLM 和开源文档 RAG 的论坛小助手已开放测试，在发帖时输入 [@论坛小助手] 即可召唤小助手，欢迎试用！

1 个赞
#3

根因分析场景: 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 性能相关的诊断信息，并对查询性能问题进行深入分析。

为了更好地了解问题并提供解决方案，请您提供以下信息：

  1. 查询 DBA_INDEX_USAGE 和 information_schema.STATISTICS 表的具体 SQL 语句是什么？
  2. 是否在查询中使用了 JOIN、子查询或其他复杂操作？如果有的话，能否提供具体的查询逻辑？
  3. 数据库版本是多少？是否存在索引统计信息不准确的情况？
  4. 是否对相关表执行过 ANALYZE 操作以更新统计信息？
  5. 查询返回结果数量差异较大的具体场景是怎样的？例如，是否在特定时间段或特定条件下出现此问题？
  6. 是否可以提供 trace_id 或者具体的执行计划（EXPLAIN 输出）以供进一步分析？

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

1 个赞
#4

image
image1896×782 98.9 KB

image
image1055×860 86.5 KB

1 个赞
#5

根据您提供的信息和图片内容，以下是对问题的详细分析与解答：

问题背景

  1. 目标：查找使用频率低的索引。

  2. SQL 查询逻辑：

    • 首先从 DBA_INDEX_USAGE 表中筛选出满足条件（total_exec_count < 2 和 last_used < '2025-06-09 15:49:00'）的索引。
    • 然后将结果与 dba_objects 表关联，获取索引的 owner 和 object_name。
    • 最后将上述结果与 information_schema.STATISTICS 表关联，进一步筛选出符合条件的索引。

  3. 问题描述：

    • 第一步查询返回了 599 条记录。
    • 第二步查询返回了 8900 条记录。
    • 您对结果数量差异较大的现象感到困惑。

分析与解答

1. 数据来源与表结构分析

  • DBA_INDEX_USAGE 表：

    • 记录了索引的使用情况，包括 total_exec_count（执行次数）和 last_used（最后一次使用时间）。
    • 该表的数据来源于 OceanBase 的统计信息，可能需要定期更新以确保准确性。

  • dba_objects 表：

    • 包含数据库对象的基本信息，如 object_id、object_name 和 owner。
    • 通过 object_id 将 DBA_INDEX_USAGE 表与 dba_objects 表关联。

  • information_schema.STATISTICS 表：

    • 提供了表和索引的统计信息，包括 table_schema、index_name 和 table_name。
    • 该表的数据也可能需要通过 ANALYZE 命令更新以确保准确性。

2. 结果数量差异的原因分析

根据您的 SQL 查询逻辑，结果数量差异较大的原因可能有以下几种：

  1. 数据重复或冗余：

    • 在 information_schema.STATISTICS 表中，可能存在多个相同的 index_name 或 table_name。
    • 这会导致在关联时产生笛卡尔积效应，从而导致结果数量大幅增加。

  2. 过滤条件不足：

    • 在第二步查询中，未对 information_schema.STATISTICS 表进行充分过滤。
    • 例如，未排除系统表（如 mysql、test、oceanbase 等），这可能导致无关数据被包含在结果中。

  3. 统计信息不准确：

    • 如果 DBA_INDEX_USAGE 或 information_schema.STATISTICS 表中的统计信息未及时更新，可能会导致查询结果不符合预期。
    • 建议定期执行 ANALYZE 命令以更新统计信息。

3. 解决方案

以下是优化查询和解决问题的具体步骤：

  1. 检查统计信息是否准确：

    • 执行以下命令更新统计信息：
      ANALYZE TABLE dba_objects;
ANALYZE TABLE information_schema.STATISTICS;

  2. 优化查询逻辑：

    • 在关联 information_schema.STATISTICS 表时，增加过滤条件以减少无关数据的影响。例如：
      SELECT t.owner, t.object_name, c.table_name
FROM (
    SELECT b.object_name, b.owner
    FROM DBA_INDEX_USAGE a
    LEFT JOIN dba_objects b ON a.object_id = b.object_id
    WHERE a.owner NOT IN ('mysql', 'test', 'oceanbase', 'information_schema')
      AND total_exec_count < 2
      AND last_used < '2025-06-09 15:49:00'
) t
JOIN (
    SELECT table_schema, index_name, table_name
    FROM information_schema.STATISTICS
    WHERE table_schema NOT IN ('mysql', 'test', 'oceanbase', 'information_schema')
) c
ON t.object_name = c.index_name AND t.owner = c.table_schema
GROUP BY t.owner, t.object_name, c.table_name;

  3. 验证结果：

    • 执行优化后的查询，观察返回结果的数量是否合理。
    • 如果仍有异常，可以进一步检查 information_schema.STATISTICS 表是否存在重复数据。

  4. 排查重复数据：

    • 检查 information_schema.STATISTICS 表中是否存在重复的 index_name 或 table_name：
      SELECT table_schema, index_name, COUNT(*)
FROM information_schema.STATISTICS
GROUP BY table_schema, index_name
HAVING COUNT(*) > 1;

4. 总结

  • 结果数量差异较大的主要原因可能是数据重复或过滤条件不足。
  • 通过更新统计信息、优化查询逻辑和排查重复数据，可以有效解决此问题。
  • 如果问题仍未解决，建议使用 obdiag 工具收集诊断信息，并联系 OceanBase 技术支持团队获取进一步帮助。

希望以上解答对您有所帮助！如果还有其他问题，请随时提问。

更多信息请查看:

  1. 查看 Locality 变更记录
  2. 查看数据备份进度

(小助手的答复已结束，如未能解决您的问题，请继续提问并等待其他同学的回复，谢谢！)

1 个赞
#6

遇到了是什么问题呢

1 个赞
#7

:+1: :+1:

#8

我上面那个sql对吗?返回的结果过多,正常应该只返回500条