OceanBase的查询缓存是如何调整的？

OceanBase 技术问题
#1

一个复杂SQL在Mysql中查询 第一个19秒，第二次和以后只需要6秒左右。

但是在OB中，第一次需要4秒，第二次还是4秒， 并不会感觉应用了缓存

@论坛小助手

1 个赞
#3

从您的问题来看，您希望了解如何在 OceanBase 数据库中通过调整查询缓存（Plan Cache）来优化重复 SQL 查询的性能。这通常涉及到如何有效地利用 Plan Cache 来避免不必要的 SQL 重解析，从而提高数据库的整体吞吐量。

不过，根据您提供的信息，这个问题更适合通过配置和调优来解决，而不是直接使用 obdiag 工具进行日志采集或根因分析。尽管如此，如果您遇到了与 Plan Cache 相关的具体问题，如性能下降或计划选择不当等情况，obdiag 也可以帮助我们更好地理解问题所在。

由于您的问题是关于如何调整而非具体的故障排查，这里没有直接适用的 obdiag 场景。但如果您正在经历与 Plan Cache 相关的具体问题，比如某些 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’}”

请替换上述命令中的 db_connect 参数为您实际的数据库连接信息，并确保 trace_id 是您想要诊断的 SQL 的跟踪 ID。这将帮助我们收集到有关 SQL 执行情况的详细信息。

为了更好地帮助您，请提供一些额外的信息：

  1. 您是否观察到了特定的 SQL 性能下降？如果有，请提供该 SQL 的示例。
  2. 您是否已经启用了 SQL 的跟踪？如果没有，建议先开启 SQL 跟踪，这样我们可以更容易地分析 SQL 的执行计划。
  3. 您是否注意到某些 SQL 在多次执行时性能表现不一致？
  4. 您当前的 OceanBase 版本是什么？
  5. 是否有最近的数据结构或表结构上的变更？

以上信息将有助于我们更准确地定位问题，并给出针对性的优化建议。

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

  1. SQL 调优典型场景和案例
  2. 列存 FAQ
  3. 计划缓存概述

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

1 个赞
#4

和小助手的理解类似，这个问题更适合通过SQL调优的分析方式来看

可以使用obdiag 采集下sql优化所需的相关信息

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

https://www.oceanbase.com/docs/common-obdiag-cn-1000000001326882

1 个赞
#5

@论坛小助手 行存列存冗余表，在实际生产使用过程中，切换行存变为冗余表不会影响任何操作嘛？不会出现数据错误或后面无法新增表字段的情况，

如何查看查询是走的列存还是行存

#6

针对您提出的关于行存列存冗余表在实际生产环境中的使用问题，以下是详细的解答：

切换行存为行存列存冗余表的影响

在 OceanBase 中，将现有的行存表转换为行存列存冗余表（即添加列存副本）通常不会直接影响到表的操作，也不会导致数据错误或后续无法新增字段的情况。这是因为转换过程是在后台进行的，不会阻塞正常的读写操作。 [4], [6]

如何转换

转换行存表为行存列存冗余表可以通过 ALTER TABLE 语句实现：

ALTER TABLE your_table_name WITH COLUMN GROUP(all columns, each column);

如何查看查询是走的列存还是行存

要确定查询是使用行存还是列存，可以利用 SQL 优化器生成的执行计划。OceanBase 的 SQL 优化器会根据查询的特点自动选择最适合的存储方式。通常，对于简单的点查询（Point Get），优化器倾向于选择行存；而对于涉及大量数据的聚合或筛选查询，则更可能选择列存。 [3]

查看执行计划的方法

  1. 使用 EXPLAIN 语句

    EXPLAIN SELECT ... FROM your_table_name ...

    通过 EXPLAIN 输出可以看到查询计划中是否包含了对列存的访问。

  2. 通过 SQL 优化器提示
    可以使用 SQL 优化器提示来强制查询走特定的存储路径，例如：

    SELECT /*+ read_from_storage(t1:column) */ ... FROM your_table_name ...

  3. 查看系统视图
    OceanBase 提供了一些系统视图来监控查询执行情况，例如 GV$SQL_PLAN，可以帮助您了解查询的实际执行路径。

注意事项

  • 合并操作：在转换为行存列存冗余表后，如果进行了大量数据的导入或更新，建议执行一次合并操作以改善读取性能。 [4]
  • 统计信息收集：定期收集统计信息有助于优化器做出更好的决策。 [4]

综上所述，将行存表转换为行存列存冗余表是一个安全的过程，不会导致数据错误或影响后续的表结构变更。通过上述方法，您可以检查查询是否使用了列存，并根据实际情况调整查询策略。

详情请参考:

  1. 列存 FAQ
  2. 数据表设计最佳实践
  3. 行列混存
  4. 创建表
  5. 数据表概述
  6. 更改表
#7

@论坛小助手 通过 EXPLAIN 输出可以看到查询计划中是否包含了对列存的访问。Explain的输出如何解读？

=================================================================
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|

|0 |COLUMN TABLE FULL SCAN|store_shop_user|2 |15283 |

Outputs & filters:

0 - output([store_shop_user.id], [store_shop_user.shop_id], [store_shop_user.update_time], [store_shop_user.create_by], [store_shop_user.create_time],
[store_shop_user.update_by], [store_shop_user.link_phone], [store_shop_user.delivery_phone], [store_shop_user.link_name], [store_shop_user.member_source],
[store_shop_user.is_centre_user], [store_shop_user.external_id], [store_shop_user.status], [store_shop_user.open_id], [store_shop_user.ali_user_id], [store_shop_user.user_record_id],
[store_shop_user.appid], [store_shop_user.xcx_open_id], [store_shop_user.user_info]), filter([cast(store_shop_user.link_phone, DECIMAL(-1, -1)) = cast(13628191212,
DECIMAL(11, 0))], [store_shop_user.appid = ‘wxdcbc850a2a767d2f’ OR store_shop_user.shop_id = 20044]), rowset=16
access([store_shop_user.id], [store_shop_user.link_phone], [store_shop_user.appid], [store_shop_user.shop_id], [store_shop_user.update_time], [store_shop_user.create_by],
[store_shop_user.create_time], [store_shop_user.update_by], [store_shop_user.delivery_phone], [store_shop_user.link_name], [store_shop_user.member_source],
[store_shop_user.is_centre_user], [store_shop_user.external_id], [store_shop_user.status], [store_shop_user.open_id], [store_shop_user.ali_user_id], [store_shop_user.user_record_id],
[store_shop_user.xcx_open_id], [store_shop_user.user_info]), partitions(p0)
is_index_back=false, is_global_index=false, filter_before_indexback[false,false],
range_key([store_shop_user.id]), range(MIN ; MAX)always true

#8

根据您提供的 EXPLAIN 输出，我们可以逐步解读其含义。这个输出展示了查询计划中的各个算子及其属性，帮助我们理解查询是如何被执行的。以下是详细的解释：

解读 EXPLAIN 输出

基本结构

=================================================================
|ID|OPERATOR              |NAME           |EST.ROWS|EST.TIME(us)|
=================================================================
|0 |COLUMN TABLE FULL SCAN|store_shop_user|2       |15283       |
  • ID：算子的唯一标识符，用于构建执行计划树。
  • OPERATOR：执行的算子类型，这里是 COLUMN TABLE FULL SCAN，表示这是一个针对列存表的全表扫描。
  • NAME：表名，这里是 store_shop_user。
  • EST.ROWS：预计返回的行数，这里是 2 行。
  • EST.TIME(us)：预计执行时间（微秒），这里是 15283 微秒。

Outputs & filters

Outputs & filters:
0 - output([store_shop_user.id], [store_shop_user.shop_id], [store_shop_user.update_time], [store_shop_user.create_by], [store_shop_user.create_time],
[store_shop_user.update_by], [store_shop_user.link_phone], [store_shop_user.delivery_phone], [store_shop_user.link_name], [store_shop_user.member_source],
[store_shop_user.is_centre_user], [store_shop_user.external_id], [store_shop_user.status], [store_shop_user.open_id], [store_shop_user.ali_user_id], [store_shop_user.user_record_id],
[store_shop_user.appid], [store_shop_user.xcx_open_id], [store_shop_user.user_info]), 
filter([cast(store_shop_user.link_phone, DECIMAL(-1, -1)) = cast(13628191212, DECIMAL(11, 0))], [store_shop_user.appid = ‘wxdcbc850a2a767d2f’ OR store_shop_user.shop_id = 20044]), rowset=16
  • output：输出的列列表，这里列出了所有被查询的列。
  • filter：过滤条件，这里有两个条件：
    • cast(store_shop_user.link_phone, DECIMAL(-1, -1)) = cast(13628191212, DECIMAL(11, 0))：将 link_phone 转换为 DECIMAL 类型并与 13628191212 进行比较。
    • store_shop_user.appid = ‘wxdcbc850a2a767d2f’ OR store_shop_user.shop_id = 20044：appid 等于 wxdcbc850a2a767d2f 或 shop_id 等于 20044。

Access

access([store_shop_user.id], [store_shop_user.link_phone], [store_shop_user.appid], [store_shop_user.shop_id], [store_shop_user.update_time], [store_shop_user.create_by],
[store_shop_user.create_time], [store_shop_user.update_by], [store_shop_user.delivery_phone], [store_shop_user.link_name], [store_shop_user.member_source],
[store_shop_user.is_centre_user], [store_shop_user.external_id], [store_shop_user.status], [store_shop_user.open_id], [store_shop_user.ali_user_id], [store_shop_user.user_record_id],
[store_shop_user.xcx_open_id], [store_shop_user.user_info]), partitions(p0)
  • access：访问的列列表，这里列出了所有需要访问的列。
  • partitions(p0)：访问的分区，这里是 p0 分区。

其他信息

is_index_back=false, is_global_index=false, filter_before_indexback[false,false],
range_key([store_shop_user.id]), range(MIN ; MAX)always true
  • is_index_back：是否使用索引回填，这里为 false。
  • is_global_index：是否使用全局索引，这里为 false。
  • filter_before_indexback：是否在索引回填前过滤，这里为 [false,false]。
  • range_key：范围键，这里是 store_shop_user.id。
  • range：范围，这里是 (MIN ; MAX)，表示全范围扫描。

总结

根据 EXPLAIN 输出，我们可以看出这是一个针对列存表 store_shop_user 的全表扫描操作。查询计划中包含了输出的列、过滤条件以及访问的列等信息。这些信息帮助我们理解查询是如何被执行的，并且可以用来优化查询性能。

更多关于 EXPLAIN 的详细信息，请参考 OceanBase 文档。

详情请参考:

  1. 实时执行计划展示
  2. 执行计划缓存内存管理
  3. SQL 执行计划简介
#9

目前OB没有结果集缓存，只有执行计划缓存。。对于SQL是否命中执行计划缓存可以查看GV$sql_audit中的is_hit_plan来判断，1是命中。0是没有命中

image
image1876×234 40.9 KB

https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000001053020