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

AntTech_TQ7QKY · 2024 年9 月 29 日 14:38

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

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

论坛小助手 · 2024 年9 月 29 日 14:39

从您的问题来看，您希望了解如何在 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 执行情况的详细信息。

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

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

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

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

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

旭辉 · 2024 年9 月 29 日 14:49

和小助手的理解类似，这个问题更适合通过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

AntTech_TQ7QKY · 2024 年9 月 29 日 15:24

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

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

论坛小助手 · 2024 年9 月 29 日 15:25

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

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

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

如何转换

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

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

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

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

查看执行计划的方法

使用 EXPLAIN 语句
```
EXPLAIN SELECT ... FROM your_table_name ...
```
通过 EXPLAIN 输出可以看到查询计划中是否包含了对列存的访问。
通过 SQL 优化器提示
可以使用 SQL 优化器提示来强制查询走特定的存储路径，例如：
```
SELECT /*+ read_from_storage(t1:column) */ ... FROM your_table_name ...
```
查看系统视图
OceanBase 提供了一些系统视图来监控查询执行情况，例如 GV$SQL_PLAN，可以帮助您了解查询的实际执行路径。

注意事项

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

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

详情请参考:

AntTech_TQ7QKY · 2024 年9 月 29 日 16:02

@论坛小助手通过 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

论坛小助手 · 2024 年9 月 29 日 16:03

根据您提供的 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 文档。

详情请参考:

皇甫侯 · 2024 年9 月 29 日 20:03

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

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

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

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

如何转换

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

查看执行计划的方法

注意事项

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

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

Outputs & filters:

解读 EXPLAIN 输出

基本结构

Outputs & filters

Access

其他信息

总结

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