概述
OCEANBASE中一个表的数据存储，主要会涉及 MEMORY TABLE,MINOR SSTABLE,MAJOR SSTABLE，本文通过查询数据字典视图来展示怎么分析表在内存，磁盘等资源的使用情况，如大小，宏块分配情况等，理解OB中的表是怎么存储的。
实验步骤
1、数据准备，我们创建一个表及2个索引
这里为了简单，只创建一个单表，分区表在部分数据字典表查询时，加上PARTITION_ID即可。
以下测试数据在ORACLE模式租户下完成。
建表，索引，insert部分基础数据
create table tab1(id number,name varchar2(30),addr varchar2(100),role_id number,primary key(id));
create index idx_tab1_name on tab1(name);
create index idx_tab1_role on tab1(role_id);
insert into tab1 select rownum id,‘test’||rownum,‘aaaaaaa’,mod(rownum,10) from dual connect by rownum<=1000000;
insert into tab1 select 10000001+rownum id,‘test’||(10000001+rownum),‘aaaaaaa’,mod(rownum,10) from dual connect by rownum<=1000000;
insert into tab1 select 10000002+rownum id,‘test’||(10000002+rownum),‘aaaaaaa’,mod(rownum,10) from dual connect by rownum<=1000000;
insert into tab1 select 10000003+rownum id,‘test’||(10000003+rownum),‘aaaaaaa’,mod(rownum,10) from dual connect by rownum<=1000000;
insert into tab1 select 10000004+rownum id,‘test’||(10000004+rownum),‘aaaaaaa’,mod(rownum,10) from dual connect by rownum<=1000000;
commit;
发起合并，生成major sstable
alter system major freeze;
查看merge状态
select * from __all_zone where name like ‘%merge%’;
update数据，生成增量 memory table数据
obclient [SYS]> update tab1 set name=name where id<=2000000;
Query OK, 2000000 rows affected (2 min 13.967 sec)
Rows matched: 2000000 Changed: 2000000 Warnings: 0
obclient [SYS]> commit;
Query OK, 0 rows affected (12.337 sec)
minior freeze 生成minior sstable
alter system minor freeze;
update数据，生成增量 memory table数据
update tab1 set name=name where id<=2000000;
commit;
2、查询表基础信息
查表table_id，用于后续数据字典查询,这里为了简单，我只通过表名过滤(因为无重复表名)，实际情况下需要加上用户信息。
select table_id from __all_virtual_table where table_name='tab1';
obclient [oceanbase]> select table_id from __all_virtual_table where table_name='tab1';
+------------------+
| table_id |
+------------------+
| 1103909674337105 |
+------------------+
1 row in set (0.010 sec)
obclient [oceanbase]> select table_id,table_name,table_type,index_type,data_table_id from __all_virtual_table where data_table_id=1103909674337105 or table_id=1103909674337105 order by table_id;
+------------------+--------------------------------------+------------+------------+------------------+
| table_id | table_name | table_type | index_type | data_table_id |
+------------------+--------------------------------------+------------+------------+------------------+
| 1103909674337105 | TAB1 | 3 | 0 | 0 |
| 1103909674337106 | __idx_1103909674337105_IDX_TAB1_NAME | 5 | 7 | 1103909674337105 |
| 1103909674337107 | __idx_1103909674337105_IDX_TAB1_ROLE | 5 | 7 | 1103909674337105 |
+------------------+--------------------------------------+------------+------------+------------------+
3 rows in set (0.017 sec)
表、索引在__all_virtual_table都有一条记录，表的data_table_id为0,索引的data_table_id为所属的表table_id
Index_type 的定义
0：NOT_INDEX,表
1：本地普通索引
2：本地唯一索引
3：全局普通索引
4：全局唯一索引
5：主键
6：全文索引
7：单分区表的全局普通索引，实际上优化成本地普通索引
8：单分区表的全局唯一索引，实际上优化成本地唯一索引
查询副本分布
select svr_ip,table_id,partition_id,role from __all_virtual_meta_table where table_id=1103909674337105;
obclient [oceanbase]> select svr_ip,table_id,partition_id,role from __all_virtual_meta_table where table_id=1103909674337105;
+---------------+------------------+--------------+------+
| svr_ip | table_id | partition_id | role |
+---------------+------------------+--------------+------+
| 192.168.56.36 | 1103909674337105 | 0 | 1 |
| 192.168.56.37 | 1103909674337105 | 0 | 2 |
| 192.168.56.38 | 1103909674337105 | 0 | 2 |
+---------------+------------------+--------------+------+
3 rows in set (0.015 sec)
3、观察表涉及存储使用
max_kept_major_version_number由于该配置项会保留旧的基线版本，进而占用额外的存储空间，且用户的整体使用率较低，从 V4.0.0 版本开始删除。
max_kept_major_version_number当前值为2，说明保留2份major sstable
obclient [oceanbase]> show parameters like 'max_kept_major_version_number';
+------+----------+---------------+----------+-------------------------------+-----------+-------+---------------------------------------------------------------------+-------------+---------+---------+-------------------+
| zone | svr_type | svr_ip | svr_port | name | data_type | value | info | section | scope | source | edit_level |
+------+----------+---------------+----------+-------------------------------+-----------+-------+---------------------------------------------------------------------+-------------+---------+---------+-------------------+
| z1 | observer | 192.168.56.36 | 2882 | max_kept_major_version_number | NULL | 2 | the maximum number of kept major versions Range: [1, 16] in integer | DAILY_MERGE | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
| z2 | observer | 192.168.56.37 | 2882 | max_kept_major_version_number | NULL | 2 | the maximum number of kept major versions Range: [1, 16] in integer | DAILY_MERGE | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
| z3 | observer | 192.168.56.38 | 2882 | max_kept_major_version_number | NULL | 2 | the maximum number of kept major versions Range: [1, 16] in integer | DAILY_MERGE | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
+------+----------+---------------+----------+-------------------------------+-----------+-------+---------------------------------------------------------------------+-------------+---------+---------+-------------------+
3 rows in set (0.031 sec)
select table_type,index_id,multi_version_start,round(size/1024/1024,2) size,is_active,contain_uncommitted_row from __all_virtual_table_mgr where table_id=1103909674337105 and partition_id=0 and svr_ip='192.168.56.36' order by table_type,index_id;
obclient [oceanbase]> select table_type,index_id,multi_version_start,round(size/1024/1024,2) size,is_active,contain_uncommitted_row from __all_virtual_table_mgr where table_id=1103909674337105 and partition_id=0 and svr_ip='192.168.56.36' order by table_type,index_id;
+------------+------------------+---------------------+---------+-----------+-------------------------+
| table_type | index_id | multi_version_start | size | is_active | contain_uncommitted_row |
+------------+------------------+---------------------+---------+-----------+-------------------------+
| 0 | 1103909674337105 | 1726816843353288 | 2030.00 | 1 | 1 |TAB1 memory table,2030MB
| 1 | 1103909674337105 | 1726754410525454 | 0.00 | 0 | 0 |TAB1 第1份major sstable，0MB
| 1 | 1103909674337105 | 1726816111614997 | 14.28 | 0 | 0 |TAB1 第2份major sstable，14.28MB
| 1 | 1103909674337106 | 1726813025143753 | 0.00 | 0 | 0 |IDX_TAB1_NAME 第1份major sstable，0MB
| 1 | 1103909674337106 | 1726816111614997 | 23.95 | 0 | 0 |IDX_TAB1_NAME 第2份major sstable，23.95MB
| 1 | 1103909674337107 | 1726816111614997 | 5.47 | 0 | 0 |IDX_TAB1_ROLE 第1份major sstable，5.47MB
| 1 | 1103909674337107 | 1726813025722627 | 0.00 | 0 | 0 |IDX_TAB1_ROLE 第2份major sstable，0MB
| 5 | 1103909674337105 | 1726816131618847 | 0.00 | 0 | 0 |
| 5 | 1103909674337106 | 1726816131618847 | 0.00 | 0 | 0 |
| 5 | 1103909674337107 | 1726816131618847 | 0.00 | 0 | 0 |
| 7 | 1103909674337105 | 1726816131618847 | 25.93 | 0 | 0 |TAB1 MINI_MINOR_SSTABLE
| 7 | 1103909674337105 | 1726816101606261 | 0.00 | 0 | 0 |TAB1 MINI_MINOR_SSTABLE
| 7 | 1103909674337105 | 1726814301056194 | 67.14 | 0 | 0 |TAB1 MINI_MINOR_SSTABLE
| 7 | 1103909674337106 | 1726816101606261 | 0.00 | 0 | 0 |IDX_TAB1_NAME MINI_MINOR_SSTABLE
| 7 | 1103909674337106 | 1726816131618847 | 25.29 | 0 | 0 |IDX_TAB1_NAME MINI_MINOR_SSTABLE
| 7 | 1103909674337106 | 1726814301056194 | 63.34 | 0 | 0 |IDX_TAB1_NAME MINI_MINOR_SSTABLE
| 7 | 1103909674337107 | 1726814301056194 | 45.00 | 0 | 0 |IDX_TAB1_ROLE MINI_MINOR_SSTABLE
| 7 | 1103909674337107 | 1726816101606261 | 0.00 | 0 | 0 |IDX_TAB1_ROLE MINI_MINOR_SSTABLE
| 7 | 1103909674337107 | 1726816131618847 | 0.00 | 0 | 0 |IDX_TAB1_ROLE MINI_MINOR_SSTABLE
+------------+------------------+---------------------+---------+-----------+-------------------------+
19 rows in set (0.093 sec)
enum TableType {
MEMTABLE = 0,
MAJOR_SSTABLE = 1,
MINOR_SSTABLE = 2, // obsoleted type after 2.2
TRANS_SSTABLE = 3, // new table type from 3.1
MULTI_VERSION_MINOR_SSTABLE = 4,
COMPLEMENT_MINOR_SSTABLE = 5, // new table type from 3.1
MULTI_VERSION_SPARSE_MINOR_SSTABLE = 6, // reserved table type
MINI_MINOR_SSTABLE = 7,
RESERVED_MINOR_SSTABLE = 8,
MAX_TABLE_TYPE
};
4、查看memory table信息
obclient [oceanbase]> select ip,table_id,partition_id,MULTI_VERSION_START,IS_ACTIVE,USED,HASH_ITEMS,BTREE_ITEMS from gv$memstore_info where table_id=1103909674337105;
+---------------+------------------+--------------+---------------------+-----------+------------+------------+-------------+
| ip | table_id | partition_id | MULTI_VERSION_START | IS_ACTIVE | USED | HASH_ITEMS | BTREE_ITEMS |
+---------------+------------------+--------------+---------------------+-----------+------------+------------+-------------+
| 192.168.56.38 | 1103909674337105 | 0 | 1726816843353288 | 1 | 2122317824 | 4023552 | 4000000 |
| 192.168.56.36 | 1103909674337105 | 0 | 1726816843353288 | 1 | 2128609280 | 4023552 | 4000000 |
| 192.168.56.37 | 1103909674337105 | 0 | 1726816843353288 | 1 | 2118123520 | 4023552 | 4000000 |
+---------------+------------------+--------------+---------------------+-----------+------------+------------+-------------+
3 rows in set (0.046 sec)
5、查看sstable信息
obclient [oceanbase]> select TABLE_TYPE, TABLE_ID,TABLE_NAME,INDEX_ID,MULTI_VERSION_START,SIZE,IS_ACTIVE,REF from gv$sstable where svr_ip='192.168.56.36' and TABLE_ID=1103909674337105 and partition_id=0;
+------------+------------------+------------+------------------+---------------------+------------+-----------+-----+
| TABLE_TYPE | TABLE_ID | TABLE_NAME | INDEX_ID | MULTI_VERSION_START | SIZE | IS_ACTIVE | REF |
+------------+------------------+------------+------------------+---------------------+------------+-----------+-----+
| 0 | 1103909674337105 | TAB1 | 1103909674337105 | 1726816843353288 | 2128609280 | 1 | 2 |
| 1 | 1103909674337105 | TAB1 | 1103909674337105 | 1726754410525454 | 0 | 0 | 2 |
| 1 | 1103909674337105 | TAB1 | 1103909674337105 | 1726816111614997 | 14970669 | 0 | 2 |
| 7 | 1103909674337105 | TAB1 | 1103909674337105 | 1726816101606261 | 0 | 0 | 2 |
| 7 | 1103909674337105 | TAB1 | 1103909674337105 | 1726816131618847 | 27188699 | 0 | 2 |
| 5 | 1103909674337105 | TAB1 | 1103909674337105 | 1726816131618847 | 0 | 0 | 2 |
| 1 | 1103909674337105 | TAB1 | 1103909674337106 | 1726813025143753 | 0 | 0 | 2 |
| 1 | 1103909674337105 | TAB1 | 1103909674337106 | 1726816111614997 | 25118452 | 0 | 2 |
| 7 | 1103909674337105 | TAB1 | 1103909674337106 | 1726816101606261 | 0 | 0 | 2 |
| 7 | 1103909674337105 | TAB1 | 1103909674337106 | 1726816131618847 | 26521178 | 0 | 2 |
| 5 | 1103909674337105 | TAB1 | 1103909674337106 | 1726816131618847 | 0 | 0 | 2 |
| 1 | 1103909674337105 | TAB1 | 1103909674337107 | 1726813025722627 | 0 | 0 | 2 |
| 1 | 1103909674337105 | TAB1 | 1103909674337107 | 1726816111614997 | 5736404 | 0 | 2 |
| 7 | 1103909674337105 | TAB1 | 1103909674337107 | 1726816101606261 | 0 | 0 | 2 |
| 7 | 1103909674337105 | TAB1 | 1103909674337107 | 1726816131618847 | 0 | 0 | 2 |
| 5 | 1103909674337105 | TAB1 | 1103909674337107 | 1726816131618847 | 0 | 0 | 2 |
+------------+------------------+------------+------------------+---------------------+------------+-----------+-----+
16 rows in set (0.086 sec)
6、查看sstable 宠块列表，及键值区间（是否是存储估行的数据来源）
obclient [oceanbase]> select column_id,column_name,rowkey_position,index_position,order_in_rowkey,is_hidden from __all_virtual_column where table_id=1103909674337105 order by rowkey_position,index_position;
+-----------+-------------+-----------------+----------------+-----------------+-----------+
| column_id | column_name | rowkey_position | index_position | order_in_rowkey | is_hidden |
+-----------+-------------+-----------------+----------------+-----------------+-----------+
| 17 | NAME | 0 | 0 | 0 | 0 |
| 18 | ADDR | 0 | 0 | 0 | 0 |
| 19 | ROLE_ID | 0 | 0 | 0 | 0 |
| 16 | ID | 1 | 0 | 0 | 0 |
+-----------+-------------+-----------------+----------------+-----------------+-----------+
4 rows in set (0.050 sec)
macro_range 为键值 ID 的范围
data_version,multi_version_start 都可以说明sstable数据的版本号
obclient [oceanbase]> select macro_idx_in_data_file,data_version,multi_version_start,data_seq,macro_range,row_count,macro_idx_in_data_file,occupy_size from __all_virtual_partition_sstable_macro_info where table_id=1103909674337105 and partition_id=0 and svr_ip='192.168.56.36' order by data_version,macro_idx_in_sstable;
+------------------------+-------------------+---------------------+----------+-------------------------------------------------------------+-----------+------------------------+-------------+
| macro_idx_in_data_file | data_version | multi_version_start | data_seq | macro_range | row_count | macro_idx_in_data_file | occupy_size |
+------------------------+-------------------+---------------------+----------+-------------------------------------------------------------+-----------+------------------------+-------------+
| 1658 | 11 | 1726816111614997 | 1048576 | (MIN ; 697104] | 697104 | 1658 | 2096076 |
| 1663 | 11 | 1726816111614997 | 1048577 | (697104 ; 1398021] | 700917 | 1663 | 2096369 |
| 1670 | 11 | 1726816111614997 | 1048578 | (1398021 ; 2099084] | 701063 | 1670 | 2094892 |
| 1671 | 11 | 1726816111614997 | 1048579 | (2099084 ; 2799691] | 700607 | 1671 | 2094177 |
| 1672 | 11 | 1726816111614997 | 1048580 | (2799691 ; 3498382] | 698691 | 1672 | 2093680 |
| 1673 | 11 | 1726816111614997 | 1048581 | (3498382 ; 4198438] | 700056 | 1673 | 2095880 |
| 1674 | 11 | 1726816111614997 | 1048582 | (4198438 ; 4898494] | 700056 | 1674 | 2095194 |
| 1675 | 11 | 1726816111614997 | 1048583 | (4898494 ; MAX] | 101506 | 1675 | 304401 |
| 1712 | 83893755106557952 | 1726816131618847 | 1048576 | (MIN ; 154328,-1726816828734988,0] | 154328 | 1712 | 2095596 |
| 1714 | 83893755106557952 | 1726816131618847 | 1048577 | (154328,-1726816828734988,0 ; 308504,-1726816828734988,0] | 154176 | 1714 | 2095038 |
| 1716 | 83893755106557952 | 1726816131618847 | 1048578 | (308504,-1726816828734988,0 ; 462680,-1726816828734988,0] | 154176 | 1716 | 2095042 |
| 1718 | 83893755106557952 | 1726816131618847 | 1048579 | (462680,-1726816828734988,0 ; 616856,-1726816828734988,0] | 154176 | 1718 | 2095034 |
| 1720 | 83893755106557952 | 1726816131618847 | 1048580 | (616856,-1726816828734988,0 ; 771032,-1726816828734988,0] | 154176 | 1720 | 2095036 |
| 1722 | 83893755106557952 | 1726816131618847 | 1048581 | (771032,-1726816828734988,0 ; 925208,-1726816828734988,0] | 154176 | 1722 | 2095013 |
| 1724 | 83893755106557952 | 1726816131618847 | 1048582 | (925208,-1726816828734988,0 ; 1079460,-1726816828734988,0] | 154252 | 1724 | 2097116 |
| 1726 | 83893755106557952 | 1726816131618847 | 1048583 | (1079460,-1726816828734988,0 ; 1233579,-1726816828734988,0] | 154119 | 1726 | 2096237 |
| 1728 | 83893755106557952 | 1726816131618847 | 1048584 | (1233579,-1726816828734988,0 ; 1387698,-1726816828734988,0] | 154119 | 1728 | 2096334 |
| 1730 | 83893755106557952 | 1726816131618847 | 1048585 | (1387698,-1726816828734988,0 ; 1541817,-1726816828734988,0] | 154119 | 1730 | 2096248 |
| 1734 | 83893755106557952 | 1726816131618847 | 1048586 | (1541817,-1726816828734988,0 ; 1695936,-1726816828734988,0] | 154119 | 1734 | 2096191 |
| 1735 | 83893755106557952 | 1726816131618847 | 1048587 | (1695936,-1726816828734988,0 ; 1850055,-1726816828734988,0] | 154119 | 1735 | 2096259 |
| 1736 | 83893755106557952 | 1726816131618847 | 1048588 | (1850055,-1726816828734988,0 ; MAX] | 149945 | 1736 | 2039555 |
+------------------------+-------------------+---------------------+----------+-------------------------------------------------------------+-----------+------------------------+-------------+
21 rows in set (0.058 sec)
multi_version_start 1726816111614997 为major sstable数据
multi_version_start 1726816131618847 为mini sstable数据
obclient [oceanbase]> select round(sum(occupy_size)/1024/1024) data_size,count(*)*2 macroblock_size from __all_virtual_partition_sstable_macro_info where table_id=1103909674337105 and partition_id=0 and svr_ip='192.168.56.36' order by data_version,macro_idx_in_sstable;
+-----------+-----------------+
| data_size | macroblock_size |
+-----------+-----------------+
| 40 | 42 |
+-----------+-----------------+
1 row in set (0.057 sec)
round(sum(occupy_size)/1024/1024) 为数据实际大小
count(*)*2 分配的宏块大小，实际占用空间大小
最后修改时间：2024-10-22 17:25:15
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
