如何利用 rowid 在OceanBase 中處理大表時提效

本文作者：張瑞遠，現主要從事電信級IT系統及數據庫的規劃設計、架構設計、運維實施、運維服務、故障處理、性能優化等工作，曾經從事銀行、證券數倉設計、開發、優化類工作，持有Orale OCM,MySQL OCP及國產代表數據庫認證。獲得包括 OceanBase OBCE、Oracle OCP 11g 等專業技能與認證。

前言：

了解數據庫優化技巧的朋友應當知道，在Oracle數據庫中，處理大表的更新或刪除時，常常借助rowid切片，這一方法能有效減少undo，支持并行執行，降低IO負載，從而提升效率。

同樣地，OceanBase也支持rowid切片，相關知識的詳細介紹可參閱以下OceanBase知識庫鏈接。

https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000209970?back=kb

這個方法使用過程中也要注意一些事情，可以先了解下ob的rowid是怎么生成的，嚴軍老師的博客有比較詳細的介紹，鏈接如下

Oracle 和 OceanBase中ROWID的區別

1731983977

所以如果更新字段中包括主鍵的話，通過rowid切片然后去批量更新的話，可能會產生沖突和部分切片的數據變化。

案例：

注意事項

1.更新列盡量不包括主鍵

2.切片大小盡量在2W-10W左右

3.更新盡量減少涉及分區（如果并發高涉及分區多，3.x很容易觸發4023的報錯，雖然是等待clog滑動窗口不是直接報錯，但是效率會變的很差）

4.對于無法確定分區拆分的場景，減少并發（限制并發和每個切片的數據量也可以避免4013內存不足的發生）

5.表上的觸發器盡量關閉，復雜和大量的觸發器對于效率影響很大

比如我們需要更新USERCS.TABLECS 的col1字段，并且確定該表無其他業務操作，而且數據基本分布在666，777的分區里，那么shell腳本如下

#!/bin/sh
export ip_a=xxx.xx.xx.xx            --數據庫ip
export tenant_a=obcstenant          --數據庫業務租戶
export colony_a=obcscluster         --數據庫集群
export colony_pw='xadsadqwdasf'     --數據庫密碼
echo "#### BEGIN ALTER PASSWD AND TBALEMODE  `date +%Y%m%d%H%M%S`####"
obclient -h${ip_a} -P2883 -usys@${tenant_a}#${colony_a} -p${colony_pw} -c <<EOF 
alter user USERCS identified by USERCS123;                            --因為測試環境為了避免影響以及方便添加了修改密碼的操作
EOF
echo "#### END ALTER PASSWD AND TBALEMODE  `date +%Y%m%d%H%M%S`####"echo "#### CREATE ABOUT TRIGGER SQLFILE `date +%Y%m%d%H%M%S`####"     --關閉和打開觸發器的sql語句
> /usr/syncscript/custcs/disable_trigger.sql
> /usr/syncscript/custcs/enable_trigger.sql
obclient -h${ip_a} -P2883 -usys@${tenant_a}#${colony_a} -p${colony_pw} -c  -N <<!|tee >> /usr/syncscript/custcs/disable_trigger.sql
select  'alter trigger  '||OWNER||'.'||TRIGGER_NAME||' DISABLE;'  from  dba_triggers where TABLE_OWNER='USERCS' and TABLE_NAME in ('TABLECS')  and  STATUS='ENABLED';
!
obclient -h${ip_a} -P2883 -usys@${tenant_a}#${colony_a} -p${colony_pw} -c  -N <<!|tee >> /usr/syncscript/custcs/enable_trigger.sql
select  'alter trigger  '||OWNER||'.'||TRIGGER_NAME||' enable;'  from  dba_triggers where TABLE_OWNER='USERCS' and TABLE_NAME in ('TABLECS')  and  STATUS='ENABLED';
!
echo "#### END ABOUT TRIGGER SQLFILE `date +%Y%m%d%H%M%S`####"echo "#### BEGIN DISABLE TRIGGER `date +%Y%m%d%H%M%S`####"               --關閉觸發器
obclient -h${ip_a} -P2883 -usys@${tenant_a}#${colony_a} -p${colony_pw} -c <<EOF 
source /usr/syncscript/custcs/disable_trigger.sql
EOF
echo "#### END DISABLE TRIGGER `date +%Y%m%d%H%M%S`####"echo "#### BEGIN CREATE TABLECS SQLFILE `date +%Y%m%d%H%M%S`####"         --生成USERCS.TABLECS根據rowid切片的sql
export des_table=USERCS.TABLECS 
export des_file=TABLECS
export des_count=1000                                                     --整個表切成1000份， 可以先count一下表看下數據量，確保每個分片不會太大不會太小，group分區鍵看下數據分布
export des_sh_name=subscssss                                              --生成批量腳本的前綴名稱
>/usr/syncscript/custcs/${des_file}.sql
obclient -h${ip_a} -P2883 -uUSERCS@${tenant_a}#${colony_a} -p'USERCS123' -c  -N <<!|tee >> /usr/syncscript/custcs/${des_file}.sql     --生成USERCS.TABLECS根據rowid切片的sql
with a as
(select  str as ob_id,rownum rn from  (select ob_rowid,decode(mod(rownum,trunc((select count(*) from ${des_table} where partition_id in (666,777) )/${des_count})),0,''||rowid,'') str,rownum seq from (select  rowid ob_rowid from ${des_table} where partition_id in (666,777) order by 1 )) where str is not null order by seq)
select  'update  USERCS.TABLECS t  set COL1=''xxxxxxxxxxx''  where partition_id in (666,777) and rowid '||case when  rc=0 then '< '''||OB_ID1||'''; commit;'  when rc is null then '>= '''||OB_ID1||'''; commit;' else '>= '''||OB_ID1||''' and rowid<'''||ob_id2||'''; commit;' end as  sql1from  (
select ob_id ob_id1,'' ob_id2,rn,0 as rc  from  a where rn=1 union all
select a1.ob_id ob_id1,a2.ob_id,a1.rn rownum1,a2.rn rownum2   from  a a1 left join a a2 on a1.rn=a2.rn-1) order by rn;
!export cnt3=`cat /usr/syncscript/custcs/${des_file}.sql|wc -l`    --統計生成的sql數量
export des_ma=`expr $cnt3 / 13`                                   --拆分為13份腳本echo "#### BEGIN  NO666,777 TABLECS DATA  `date +%Y%m%d%H%M%S`####"       --先確認了非666，777分區的數據很少，可以單獨更新
obclient -h${ip_a} -P2883 -uUSERCS@${tenant_a}#${colony_a} -p'USERCS123' -c <<EOF 
update  USERCS.TABLECS t  set COL1=''xxxxxxxxxxx'' where  partition_id not  in (666,777);
EOF
echo "#### END  NO666,777 TABLECS DATA  `date +%Y%m%d%H%M%S`####"split -d -l ${des_ma} ${des_file}.sql   ${des_sh_name} &&ls|grep ${des_sh_name}|xargs -i mv {} {}.sh        --拆分rowid切分的sql文件成13個腳本
ls|grep ${des_sh_name}|xargs -i sed -i "1iobclient -h${ip_a} -P2883 -uUSERCS@${tenant_a}#${colony_a} -p'USERCS123' -c <<EOF " {}
ls|grep ${des_sh_name}|xargs -i sed -i "1i#!/bin/sh"  {}
find . -name  "${des_sh_name}*"|xargs -i sed  -i '$a\EOF'  {}
find . -name  "${des_sh_name}*"|xargs -i sed  -i '$a\echo "####{} END `date +%Y%m%d%H%M%S`"'  {}
echo "#### END CREATE SQLFILE `date +%Y%m%d%H%M%S`####"
echo "#### BEGIN RUN DESEN `date +%Y%m%d%H%M%S`####"
export cn=`ls|grep ${des_sh_name}|wc -l`                            --并發度控制，因為這里拆分的腳本只有13個，所以有多少腳本設置的多少并發，如果單分區大表或者無法按照分區拆分的大表可以多分一些切片，多分些腳本，cn并發寫成固定的值比如5
ls|grep ${des_sh_name}|xargs -i -P ${cn} sh {} &                    --并發執行生成的這些腳本
wait
rm -rf /usr/syncscript/custcs/${des_sh_name}*
echo "#### END RUN TABLECS DESEN `date +%Y%m%d%H%M%S`####"echo "#### BEGIN ENABLE TRIGGER `date +%Y%m%d%H%M%S`####"           --打開觸發器   
obclient -h${ip_a} -P2883 -usys@${tenant_a}#${colony_a} -p${colony_pw} -c <<EOF 
source /usr/syncscript/custcs/enable_trigger.sql
EOF
echo "#### END ENABLE TRIGGER `date +%Y%m%d%H%M%S`####"

上面寫了如果單分區大表或者無法按照分區拆分的大表可以多分一些切片，多分些腳本，cn并發寫成固定的值比如5，案例如下

echo "#### BEGIN CREATE TABLECS2 SQLFILE `date +%Y%m%d%H%M%S`####"
export des_table=USERCS1.TABLECS2 
export des_file=TABLECS2
export des_count=2000                             --按照rowid切分為2000份
export des_sh_name=tbsfcssss
>/usr/syncscript/custcs/${des_file}.sql
obclient -h${ip_a} -P2883 -uUSERCS1@${tenant_a}#${colony_a} -p'USERCS1123' -c  -N <<!|tee >> /usr/syncscript/custcs/${des_file}.sql
with a as
(select  str as ob_id,rownum rn from  (select ob_rowid,decode(mod(rownum,trunc((select count(*) from ${des_table} )/${des_count})),0,''||rowid,'') str,rownum seq from (select  rowid ob_rowid from ${des_table}  order by 1 )) where str is not null order by seq)
select  'update  USERCS1.TABLECS2 t  set  COL2=''ASDASD'' where rowid '||case when  rc=0 then '< '''||OB_ID1||'''; commit;'  when rc is null then '>= '''||OB_ID1||'''; commit;' else '>= '''||OB_ID1||''' and rowid<'''||ob_id2||'''; commit;' end as  sql1from  (
select ob_id ob_id1,'' ob_id2,rn,0 as rc  from  a where rn=1 union all
select a1.ob_id ob_id1,a2.ob_id,a1.rn rownum1,a2.rn rownum2   from  a a1 left join a a2 on a1.rn=a2.rn-1) order by rn;
!export cnt3=`cat /usr/syncscript/custcs/${des_file}.sql|wc -l`
export des_ma=`expr $cnt3 / 200`                            -- 切分成200個腳本          split -d -l ${des_ma} ${des_file}.sql   ${des_sh_name} &&ls|grep ${des_sh_name}|xargs -i mv {} {}.sh 
ls|grep ${des_sh_name}|xargs -i sed -i "1iobclient -h${ip_a} -P2883 -uUSERCS1@${tenant_a}#${colony_a} -p'USERCS1123' -c <<EOF " {}
ls|grep ${des_sh_name}|xargs -i sed -i "1i#!/bin/sh"  {}
find . -name  "${des_sh_name}*"|xargs -i sed  -i '$a\EOF'  {}
find . -name  "${des_sh_name}*"|xargs -i sed  -i '$a\echo "####{} END `date +%Y%m%d%H%M%S`"'  {}
echo "#### END CREATE SQLFILE `date +%Y%m%d%H%M%S`####"
echo "#### BEGIN RUN DESEN `date +%Y%m%d%H%M%S`####"
export cn=5                                                 --按照并發5去執行切分后的腳本
ls|grep ${des_sh_name}|xargs -i -P ${cn} sh {} &
wait
rm -rf /usr/syncscript/custcs/${des_sh_name}*
echo "#### END RUN TABLECS2 DESEN `date +%Y%m%d%H%M%S`####"