如何直接查索引表的内容

本文介绍在 OceanBase 数据库中如何直接查索引表的内容。

OceanBase 数据库中如何直接查索引表的内容

OceanBase 数据库中通过查询 __all_virtual_table 表获取索引表名称，并通过在 session 会话级别设置参数 ob_enable_index_direct_select 为 1 允许用户直接查询索引表，该参数默认值 0，示例如下。

1. 创建表。

   obclient [mysql]> CREATE TABLE test (c1 INT, c2 DATE);
   Query OK, 0 rows affected (0.038 sec)
   

2. 创建索引。

   obclient [mysql]> create index indx_c1_test on test(c1);
   Query OK, 0 rows affected (0.435 sec)
   

3. 创建插入数据的存储过程。

   obclient [mysql]> delimiter $$
   obclient [mysql]> create procedure proc_test01 (in insertcount int)
       begin
       declare i int default 1;
       label:while i<=insertcount do
       insert into test (c1,c2) values (i+FLOOR(RAND() * 100),'2025-05-01');
       set i=i+1;
       end while label;
       end $$
   Query OK, 0 rows affected (0.016 sec)
   

4. 调用存储过程。

   obclient [mysql]> delimiter ;
   obclient [mysql]>
   obclient [mysql]> call proc_test01(10);
   Query OK, 0 rows affected (0.034 sec)
   

5. 提交插入数据。

   obclient [mysql]> commit;
   Query OK, 0 rows affected (0.000 sec)
   

6. 通过内部表 __all_virtual_table 获取 _idx 开头索引表名称。

   obclient [mysql]> select tenant_id,data_table_id,table_name,table_id,database_id,table_type from oceanbase.__all_virtual_table where table_name like '%__idx%';
   

   输出内容如下：

   obclient [mysql]> select tenant_id,data_table_id,table_name,table_id,database_id,table_type from oceanbase.__all_virtual_table where table_name like '%__idx%';
   +-----------+---------------+---------------------------------------------------------+---------------+---------------+------------+
   | tenant_id | data_table_id | table_name                                              | table_id      | database_id   | table_type |
   +-----------+---------------+---------------------------------------------------------+---------------+---------------+------------+
   |         1 | 1099511628090 | __idx_1099511628090_idx_data_table_id                   | 1099511637773 | 1099511627777 |          5 |
   |         1 | 1099511628058 | __idx_1099511628058_idx_data_table_id                   | 1099511637774 | 1099511627777 |          5 |
   |         1 | 1099511627890 | __idx_1099511627890_idx_data_table_id                   | 1099511637775 | 1099511627777 |          5 |
   |         1 | 1099511639791 | __idx_1099511639791_all_virtual_replica_task_i1         | 1099511642767 | 1099511627777 |          5 |
   |         1 | 1099511638807 | __idx_1099511638807_all_virtual_sql_audit_i1            | 1099511642768 | 1099511627777 |          5 |
   |         1 | 1099511638797 | __idx_1099511638797_all_virtual_sysstat_i1              | 1099511642769 | 1099511627777 |          5 |
   |         1 | 1099511638796 | __idx_1099511638796_all_virtual_sesstat_i1              | 1099511642770 | 1099511627777 |          5 |
   |         1 | 1099511638793 | __idx_1099511638793_all_virtual_system_event_i1         | 1099511642771 | 1099511627777 |          5 |
   |         1 | 1099511638791 | __idx_1099511638791_all_virtual_session_wait_history_i1 | 1099511642772 | 1099511627777 |          5 |
   |         1 | 1099511638790 | __idx_1099511638790_all_virtual_session_wait_i1         | 1099511642773 | 1099511627777 |          5 |
   |         1 | 1099511638789 | __idx_1099511638789_all_virtual_session_event_i1        | 1099511642774 | 1099511627777 |          5 |
   |         1 | 1099511638779 |  __idx_1099511638779_all_virtual_plan_cache_stat_i1      | 1099511642775 | 1099511627777 |          5 |
   |         1 | 1099511642820 | __idx_1099511642820_all_virtual_system_event_i1         | 1099511647769 | 1099511627782 |          5 |
   |         1 | 1099511642819 | __idx_1099511642819_all_virtual_sysstat_i1              | 1099511647770 | 1099511627782 |          5 |
   |         1 | 1099511642818 | __idx_1099511642818_all_virtual_sesstat_i1              | 1099511647771 | 1099511627782 |          5 |
   |         1 | 1099511642813 | __idx_1099511642813_all_virtual_session_wait_history_i1 | 1099511647772 | 1099511627782 |          5 |
   |         1 | 1099511642812 | __idx_1099511642812_all_virtual_session_wait_i1         | 1099511647773 | 1099511627782 |          5 |
   |         1 | 1099511642810 | __idx_1099511642810_all_virtual_plan_cache_stat_i1      | 1099511647774 | 1099511627782 |          5 |
   |         1 | 1099511642785 | __idx_1099511642785_all_virtual_sql_audit_i1            | 1099511647775 | 1099511627782 |          5 |
   |         1 | 1099511677779 | __idx_1099511677779_indx_c1_test                        | 1099511677780 | 1099511627779 |          5 |
   +-----------+---------------+---------------------------------------------------------+---------------+---------------+------------+
   20 rows in set (0.005 sec)
   

7. 在 session 会话级别设置参数 ob_enable_index_direct_select，可直接查询索引表内容。

   obclient [mysql]> set ob_enable_index_direct_select = 1;
   Query OK, 0 rows affected (0.000 sec)
   

   查询索引表：

   obclient [mysql]> select * from __idx_1099511677779_indx_c1_test;
   

   输出内容如下：

   +------+
   | c1   |
   +------+
   |    9 |
   |   26 |
   |   33 |
   |   36 |
   |   43 |
   |   66 |
   |   72 |
   |   78 |
   |   90 |
   |   93 |
   +------+
   10 rows in set (0.001 sec)
   

有关直接查索引表的内容参数参考文档 ob_enable_index_direct_select。

适用版本

OceanBase 数据库所有版本。