update为啥不走主键

【 使用环境 】测试环境
【 OB or 其他组件 】ob
【 使用版本 】4218
【问题描述】跑批期间发现更新sql cpu占比太高，走全表扫描
【附件及日志】
sql语句：
update BS_GL_POST set STAT_CD=‘P’ , TRANS_TM = ‘2024-11-10 18:20:36.000892’
WHERE 1 = 1 AND BLORG_NO = ‘0611’ AND INDEX_NO BETWEEN ‘102030111’ and ‘102032110’
AND ( PTXSP_MID_ENTRY_FLAG != ‘Y’ OR PTXSP_MID_ENTRY_FLAG IS NULL ) AND STAT_CD=‘O’
AND POST_DT < ‘2024-06-18’ AND VIRTUAL_FLAG=‘N’；

图片1240×384 16.5 KB

dc0cd0c336d332786731087d42dd55a2521×1083 127 KB

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

针对您提到的“OceanBase 更新语句不走主键，导致 CPU 占比高，执行全表扫描”的问题，我们可以先使用 obdiag 工具来采集 SQL 性能相关的日志，以便进一步分析问题的原因。同时，根据提供的文档片段，这里有一些建议可以帮助您优化 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'}"

这条命令将帮助我们收集与 SQL 性能相关的日志，包括执行计划、SQL 审计日志等，以便分析为什么更新语句没有使用主键索引。

优化建议

1. 确保使用主键索引：更新语句应尽量使用主键索引，以减少全表扫描带来的性能开销。请检查您的 SQL 语句，确保 WHERE 子句中包含主键条件。
2. 检查执行计划：使用 EXPLAIN 命令查看 SQL 语句的执行计划，确保其使用了主键索引。例如：

   EXPLAIN EXTENDED UPDATE your_table SET column1 = value1 WHERE primary_key_column = value2;
   

3. 避免全表扫描：如果表的数据量较大，全表扫描会导致严重的性能问题。请确保 WHERE 子句中的条件能够利用索引。

需要您提供的信息

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

1. 具体的 SQL 语句：请提供导致 CPU 占比高的更新语句的具体内容。
2. 表结构：请提供涉及的表的结构信息，特别是主键和索引的定义。
3. 执行计划：请提供 EXPLAIN EXTENDED 命令的输出结果，以便我们了解当前的执行计划。
4. 系统配置：请提供 OceanBase 集群的版本信息和系统配置，包括 CPU、内存等硬件信息。
5. 负载情况：请描述当前系统的负载情况，是否有其他高负载的操作同时进行。

希望以上信息和建议对您有所帮助。如果您有任何疑问或需要进一步的帮助，请随时联系我们。

检索到的文档如下:

1. SQL 调优典型场景和案例
2. 使用 LOAD DATA 语句导入数据
3. 表扫描方式规范
4. 使用 LOAD DATA 语句旁路导入数据
5. 主键表和无主键表

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

根据您提供的信息和图片内容，您的 UPDATE 语句确实存在性能问题，主要表现为 CPU 占用高且执行了全表扫描。以下是详细的分析和优化建议：

问题分析

1. 全表扫描：从 EXPLAIN 输出的结果来看，UPDATE 语句没有使用主键索引，而是进行了全表扫描。这导致了较高的 CPU 占用和较长的执行时间。
2. 索引使用：您的表 BS_GL_POST 有多个索引，但 UPDATE 语句的 WHERE 子句没有充分利用这些索引，特别是主键索引。

优化建议

1. 确保使用主键索引：

   • 请确保 WHERE 子句中包含主键条件。如果主键条件无法满足查询需求，可以考虑添加复合索引。
   • 例如，如果 BLORG_NO, INDEX_NO, POST_DT, STAT_CD 组合起来可以唯一标识记录，可以考虑创建复合索引：

     CREATE INDEX idx_bs_gl_post ON BS_GL_POST (BLORG_NO, INDEX_NO, POST_DT, STAT_CD);
     

2. 检查执行计划：

   • 使用 EXPLAIN EXTENDED 命令查看 SQL 语句的执行计划，确保其使用了主键索引或其他合适的索引。
   • 例如：

     EXPLAIN EXTENDED UPDATE BS_GL_POST 
     SET STAT_CD = 'P', TRANS_TM = '2024-11-10 18:20:36.000892' 
     WHERE BLORG_NO = '0611' 
     AND INDEX_NO BETWEEN '102030111' AND '102032110' 
     AND (PTXSP_MID_ENTRY_FLAG != 'Y' OR PTXSP_MID_ENTRY_FLAG IS NULL) 
     AND STAT_CD = 'O' 
     AND POST_DT < '2024-06-18' 
     AND VIRTUAL_FLAG = 'N';
     

3. 优化索引：

   • 确保索引的列顺序与查询条件匹配。索引的前导列应该出现在 WHERE 子句的前面。
   • 例如，如果 BLORG_NO 和 INDEX_NO 经常一起使用，可以创建复合索引：

     CREATE INDEX idx_bs_gl_post_blorg_no_index_no ON BS_GL_POST (BLORG_NO, INDEX_NO);
     

4. 收集统计信息：

   • 收集表的统计信息可以帮助优化器生成更优的执行计划。
   • 例如：

     CALL dbms_stats.gather_table_stats('your_schema', 'BS_GL_POST');
     

示例

假设您创建了一个复合索引 idx_bs_gl_post_blorg_no_index_no，并重新检查执行计划：

CREATE INDEX idx_bs_gl_post_blorg_no_index_no ON BS_GL_POST (BLORG_NO, INDEX_NO);

EXPLAIN EXTENDED UPDATE BS_GL_POST 
SET STAT_CD = 'P', TRANS_TM = '2024-11-10 18:20:36.000892' 
WHERE BLORG_NO = '0611' 
AND INDEX_NO BETWEEN '102030111' AND '102032110' 
AND (PTXSP_MID_ENTRY_FLAG != 'Y' OR PTXSP_MID_ENTRY_FLAG IS NULL) 
AND STAT_CD = 'O' 
AND POST_DT < '2024-06-18' 
AND VIRTUAL_FLAG = 'N';

总结

通过确保 UPDATE 语句使用主键索引或合适的复合索引，可以显著提高查询性能，减少 CPU 占用。同时，定期收集统计信息也有助于优化器生成更优的执行计划。

希望以上建议对您有所帮助。如果您有任何疑问或需要进一步的帮助，请随时联系我们。

检索到的文档如下:

1. oceanbase.ALL_OB_EXTERNAL_TABLE_FILES
2. 日志概述
3. Hash Join
4. OceanBase Sysbench 性能测试报告
5. 统计信息和估行机制的使用

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

发一下 执行计划 尽量保存在文本里 看一下 再发一下 表结构信息 show create table BS_GL_POST
explain extended update BS_GL_POST set STAT_CD=‘P’ , TRANS_TM = ‘2024-11-10 18:20:36.000892’
WHERE 1 = 1 AND BLORG_NO = ‘0611’ AND INDEX_NO BETWEEN ‘102030111’ and ‘102032110’
AND ( PTXSP_MID_ENTRY_FLAG != ‘Y’ OR PTXSP_MID_ENTRY_FLAG IS NULL ) AND STAT_CD=‘O’
AND POST_DT < ‘2024-06-18’ AND VIRTUAL_FLAG=‘N’；

当时跑批的时候走不了range，现在执行计划可以走

你根据sqlid查一下 着是那个视图的信息 我看看 V$OB_PLAN_CACHE_PLAN_STAT V$OB_PLAN_CACHE_PLAN_EXPLAIN GV$SQL_PLAN_MONITOR

你发的文本信息 我没有看到 上面的更新的语句诊断信息

有 在下边
表结构下边

发错了

看着执行计划没有问题 查询一下我楼上发的 查询一下那几个视图信息
1、根据语句查询sql_id或者trace_id
select query_sql,svr_ip,TRACE_ID,sql_id,PLAN_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 ‘%update bs_gl_post set%’ order by elapsed_time desc
limit 10;
下面的信息 查一下看看
select * from V$OB_PLAN_CACHE_PLAN_STAT where sql_id=’’\G;
seelct * from V$OB_PLAN_CACHE_PLAN_EXPLAIN where PLAN_ID =’’\G;
select * from GV$SQL_PLAN_MONITOR where trace_id=’’\G;

没有问题 PHY_TABLE_SCAN这范围扫描算子

image2940×122 58.8 KB

好吧那就sql没问题 哪执行了几万ms 好难

1、数据量比较大 可以小事务批量执行 这样话可以很快 事务占用不会很长
2、建索引的时候 尽量是过滤条件基数比较小的放在最左边