--- title: 如何直接查索引表的内容-OceanBase数据库使用指南 description: 了解OceanBase数据库在实际应用中关于 如何直接查索引表的内容相关的常见问题和使用技巧，帮助您快速解决 如何直接查索引表的内容的难题。 --- 切换语言 - 中文站 - 简体中文 - International - English - 日本站 - 日本語 划线反馈 # 如何直接查索引表的内容 更新时间：2024-01-29 09:41 适用版本： V1.4.x、V2.1.x、V2.2.x、V3.1.x、V3.2.x、V4.0.x、V4.1.x、V4.2.x 内容类型：How-to 本文介绍在 OceanBase 数据库中如何直接查索引表的内容。 ## OceanBase 数据库中如何直接查索引表的内容 OceanBase 数据库中通过查询 `__all_virtual_table` 表获取索引表名称，并通过在 session 会话级别设置参数 `ob_enable_index_direct_select` 为 1 允许用户直接查询索引表，该参数默认值 0，示例如下。 1. 创建表。 ```shell obclient [mysql]> CREATE TABLE test (c1 INT, c2 DATE); Query OK, 0 rows affected (0.038 sec) ``` 2. 创建索引。 ```shell obclient [mysql]> create index indx_c1_test on test(c1); Query OK, 0 rows affected (0.435 sec) ``` 3. 创建插入数据的存储过程。 ```shell 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. 调用存储过程。 ```shell obclient [mysql]> delimiter ; obclient [mysql]> obclient [mysql]> call proc_test01(10); Query OK, 0 rows affected (0.034 sec) ``` 5. 提交插入数据。 ```shell obclient [mysql]> commit; Query OK, 0 rows affected (0.000 sec) ``` 6. 通过内部表 `__all_virtual_table` 获取 _idx 开头索引表名称。 ```shell 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%'; ``` 输出内容如下： ```shell 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`，可直接查询索引表内容。 ```shell obclient [mysql]> set ob_enable_index_direct_select = 1; Query OK, 0 rows affected (0.000 sec) ``` 查询索引表： ```shell obclient [mysql]> select * from __idx_1099511677779_indx_c1_test; ``` 输出内容如下： ```shell +------+ | c1 | +------+ | 9 | | 26 | | 33 | | 36 | | 43 | | 66 | | 72 | | 78 | | 90 | | 93 | +------+ 10 rows in set (0.001 sec) ``` 有关直接查索引表的内容参数参考文档 [ob_enable_index_direct_select](https://www.oceanbase.com/docs/enterprise-oceanbase-database-cn-10000000000354683)。 ## 适用版本 OceanBase 数据库所有版本。 上一篇 [OceanBase 数据库大表建索引超过临时文件元数据内存限制报错 4013](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000618487) 下一篇 [如何查看索引的定义，以及查看该索引究竟是全局索引还是非全局索引](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000207722) ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线