相同的数据比 updata 比mysql慢

OceanBase 技术问题
#1

200条一批的数据进行 updata 时 OB 比 mysql0 慢三到四倍 ，表结构的区别在于 mysql 没有分区，OB 进行了分区，但是 OB 在进行 updata 时候 条件列有分区字段
表结构：
CREATE TABLE b_base_data_text (
id bigint(20) NOT NULL AUTO_INCREMENT COMMENT ‘主键’,
data_child_id bigint(20) NOT NULL DEFAULT ‘0’ COMMENT ‘子项id’,
name varchar(700) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT ‘附件名称 url名称’,
content longtext CHARACTER SET utf8mb4 DEFAULT NULL,
html longtext CHARACTER SET utf8mb4 DEFAULT NULL COMMENT ‘带样式式的正文内容’,
show_type varchar(255) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT ‘在微信小程序、 pc、百度小程序端等展示’,
sort int(11) DEFAULT NULL COMMENT ‘显示顺序’,
data_id bigint(20) DEFAULT NULL,
type int(11) DEFAULT NULL,
subitem_source varchar(100) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT ‘附件来源’,
column_name varchar(100) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
PRIMARY KEY (id, data_child_id),
KEY data_child_id (data_child_id),
KEY data_id (data_id),
KEY column_name (column_name),
KEY id (id, data_child_id)
) AUTO_INCREMENT = 611485797 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC COMMENT = ‘基础数据正文’
partition by hash(data_child_id)
(partition p0,
partition p1,
partition p2,
partition p3,
partition p4,
partition p5,
partition p6,
partition p7,
partition p8,
partition p9,
partition p10,
partition p11,
partition p12,
partition p13,
partition p14,
partition p15,
partition p16,
partition p17,
partition p18,
partition p19,
partition p20,
partition p21,
partition p22,
partition p23,
partition p24,
partition p25,
partition p26,
partition p27,
partition p28,
partition p29,
partition p30,
partition p31,
partition p32,
partition p33,
partition p34,
partition p35,
partition p36,
partition p37,
partition p38,
partition p39,
partition p40,
partition p41,
partition p42,
partition p43,
partition p44,
partition p45,
partition p46,
partition p47,
partition p48,
partition p49,
partition p50,
partition p51,
partition p52,
partition p53,
partition p54,
partition p55,
partition p56,
partition p57,
partition p58,
partition p59,
partition p60,
partition p61,
partition p62,
partition p63,
partition p64,
partition p65,
partition p66,
partition p67,
partition p68,
partition p69,
partition p70,
partition p71,
partition p72,
partition p73,
partition p74,
partition p75,
partition p76,
partition p77,
partition p78,
partition p79,
partition p80,
partition p81,
partition p82,
partition p83,
partition p84,
partition p85,
partition p86,
partition p87,
partition p88,
partition p89,
partition p90,
partition p91,
partition p92,
partition p93,
partition p94,
partition p95,
partition p96,
partition p97,
partition p98,
partition p99)

语句:

#3

sql:
UPDATE
b_base_data_text
SET
name = ?,
content = ?,
html = ?,
sort = ?,
subitem_source = ?
WHERE
id = ?
AND data_child_id = ?

#4

ob的版本号
1、 收集SQL性能问题信息
–根据时间和执行语句查询trace_id
select query_sql,svr_ip,TRACE_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 ‘%select%’;

obdiag gather scene run --scene=observer.perf_sql --env “{db_connect=’-hxx -Pxx -uxx -pxx -Dxx’, trace_id=‘xx’}”
https://www.oceanbase.com/docs/common-obdiag-cn-1000000002821559
2. 全链路诊断中的Show Trace功能，也能轻松发现性能瓶颈并进行进一步的分析和调优。
3. 以上两种方式可能并不是万能的，接下来可以按以下步骤尝试自查分析。
a. 通过查看gv$ob_sql_audit审计视图确认影响执行耗时的等待事件。
b. 再获取 SQL 执行计划EXPLAIN EXTENDED，查看执行计划并分析。
c. 获取 sql plan monitor 信息，全过程可以参看如下操作。

1.登录sys租户设置 sql_plan_monitor 参数
# 确认 sql_plan_monitor 已经打开
show parameters like 'enable_sql_audit';

# 如果 enable_sql_audit = False 则将其打开
alter system enable_sql_audit = true;

2. 登录业务租户，获取 sql 的执行计划
EXPLAIN EXTENDED sql语句;

3. 设置临时 trace 获取
SET ob_enable_show_trace=‘ON’;

4. 再次执行需要采集的 sql 语句

5. 获取上一步执行的 sql 的 trace_id 信息
select last_trace_id();

6. 临时关闭 plan monitor 数据，防止信息被覆盖
alter system enable_sql_audit = false;

7. 获取 plan monitor 的 sql ，将 xxxxx 替换为第 6 步返回的 trace_id ，以获取每个算子的吐行信息
select op_id, op, rows, rescan, threads, (close_time - open_time) open_dt, (last_row_eof_time-first_row_time) rowdt, open_time, close_time, first_row_time, last_row_eof_time FROM(select plan_line_id op_id, concat(lpad(' ', plan_depth, ' '), plan_operation) op, sum(output_rows) rows, sum(STARTS) rescan, min(first_refresh_time) open_time, max(last_refresh_time) close_time, min(first_change_time) first_row_time, max(last_change_time) last_row_eof_time, count(1) threads from gv$sql_plan_monitor where trace_id = '$trace_id' group by plan_line_id, plan_operation, plan_depth order by 1) a;
8 恢复 sql_audit 参数
alter system enable_sql_audit = true;