如何查看索引命中率

OceanBase 技术问题
#1

【 使用环境 】生产环境 or 测试环境
【 OB or 其他组件 】
【 使用版本 】
【问题描述】一张表上存在多个索引，如何检查索引的使用情况，然后可以对使用少的索引进行删除或者优化
【复现路径】问题出现前后相关操作
【附件及日志】推荐使用OceanBase敏捷诊断工具obdiag收集诊断信息,详情参见链接（右键跳转查看）：

【SOP系列 22 】——故障诊断第一步(自助诊断和诊断信息收集)

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

1 个赞
#3

在这个问题中，您询问的是关于索引使用情况的检查以及对低效索引进行优化或删除的方法。这并不直接对应于 obdiag 工具的日志采集或根因分析场景，因为 obdiag 更多是用于诊断和收集系统级别的信息，而不是直接用于性能调优或者索引管理。

不过，为了帮助您了解索引的使用情况并确定索引命中率，您可以执行以下步骤：

  1. 使用 SQL 查询来获取有关索引使用的信息。例如，可以查询 gv$sql_audit 视图以查看特定时间段内的 SQL 执行情况，包括是否使用了索引。
  2. 分析慢查询日志，找出那些执行时间较长且可能未有效利用索引的查询。
  3. 利用 OceanBase 提供的性能监控工具（如 OCP）来跟踪和分析索引的使用效率。

如果您确实遇到了与索引相关的性能问题，并且想要通过 obdiag 收集更多相关信息，那么可以考虑使用如下命令来进行 SQL 性能问题的日志采集：

日志采集场景: 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 和连接参数需要根据实际情况进行替换。

为了更好地帮助您解决这个问题，请提供以下信息：

  1. 您当前使用的 OceanBase 版本是什么？
  2. 是否已经有一些初步的数据表明哪些索引使用较少？如果有，请分享这些数据。
  3. 您是否有任何具体的查询语句怀疑其索引使用不当？如果有，请提供这些查询语句。
  4. 您是否已经在使用 OceanBase 的性能监控工具（如 OCP）来跟踪索引的使用情况？如果是的话，能否提供一些截图或报告片段？

希望以上信息能够帮助到您。如果还有其他疑问，欢迎继续提问。

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

1 个赞
#4

集群版本为4214

1 个赞
#5

根据您提供的信息，您的 OceanBase 集群版本为 4.2.1.4。为了检查索引的使用情况并确定索引命中率，您可以采取以下步骤：

1. 使用 SQL 查询获取索引使用信息

OceanBase 提供了系统视图来帮助管理员和开发者了解数据库内部的工作状态。对于索引使用情况，可以查询 gv$sql_audit 视图以查看特定时间段内的 SQL 执行情况，包括是否使用了索引。

SELECT 
    sql_id, 
    plan_hash_value, 
    obj_name AS table_name, 
    index_name, 
    COUNT(*) AS execution_count
FROM 
    gv$sql_audit
WHERE 
    sql_text NOT LIKE '%gv$sql_audit%' AND
    sql_text NOT LIKE '%sys%' AND
    sql_text NOT LIKE '%information_schema%'
GROUP BY 
    sql_id, 
    plan_hash_value, 
    obj_name, 
    index_name
ORDER BY 
    execution_count DESC;

这段 SQL 语句将返回每个 SQL 语句及其执行计划中使用的索引名称，并统计每个组合被执行了多少次。通过分析这些数据，您可以识别出哪些索引被频繁使用，哪些则很少或从未被使用。

2. 分析慢查询日志

如果存在性能瓶颈，通常可以从慢查询日志入手。OceanBase 的慢查询日志记录了执行时间较长的查询语句，这有助于发现那些可能未有效利用索引的查询。

要启用慢查询日志，请确保在配置文件中设置了适当的阈值（例如超过1秒）。然后定期检查这些日志文件，寻找潜在的问题。

3. 利用 OCP 进行监控

如果您正在使用 OceanBase Control Platform (OCP)，它提供了强大的性能监控功能，可以帮助您跟踪和分析索引的使用效率。通过图形界面，您可以直观地看到各个表和索引的访问模式，从而更容易做出优化决策。

4. 删除或优化低效索引

一旦确定了某些索引很少被使用或者完全没有必要，您可以考虑删除它们以减少维护开销。但在实际操作前，请务必先评估其影响，并确保有充分的理由证明删除是合理的。此外，也可以尝试调整现有索引的设计，比如增加覆盖列、重新组织索引结构等方法来提高查询性能。

注意事项

  • 备份：在进行任何更改之前，请确保已经对相关对象进行了适当的备份。
  • 测试环境验证：建议先在一个非生产环境中测试所有变更，确保不会引入新的问题。
  • 持续监控：即使完成了优化工作，也应继续监控系统的性能表现，以便及时应对可能出现的新挑战。

希望以上信息能够帮助到您。如果有更多具体问题或需要进一步的帮助，请随时告知！

具体信息可参考以下文档:

  1. OceanBase 术语
  2. GV$SESSION_LONGOPS
  3. OceanBase 数据库 V4.1.0
  4. 负载均衡
  5. OceanBase 数据库社区版 V4.0.0
  6. OceanBase 数据库社区版 V3.1.5

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

1 个赞
#6

根据DBA_OB_TABLE_LOCATIONS和DBA_OB_TABLET_REPLICAS这两个表的table_id可以查出某张表的所有索引。
DBA_OB_TABLE_LOCATIONS.INDEX_NAME

1 个赞
#7

ob 应该没有专门的视图去统计索引的命中率

#8

可以查询到索引的使用次数么

#9

这边暂时没查到有专门去统计索引命中次数的视图

1 个赞
#10

并没有这方面的视图

#11

:clap: :clap: 学习