基于湖库一体架构,统一管理结构化、半结构化与非结构化等多模态数据,一个系统承载事务处理、实时分析与 AI 工作负载。
查看索引
更新时间:2026-07-15 20:42:47
可以使用 SHOW INDEX/INDEXES/KEYS 语句查看表的索引。
语句如下:
SHOW [EXTENDED] {INDEX | INDEXES | KEYS}
{FROM | IN} table_name
[{FROM | IN} database_name];
语句中:
EXTENDED:展示隐藏的索引。table_name:待查询的索引表。database_name:待查询表所属的数据库。
示例如下:
查看表
test的索引。obclient [test]> SHOW INDEX FROM test;查询结果如下:
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+ | test | 1 | IDX | 1 | id | A | NULL | NULL | NULL | YES | BTREE | available | | YES | NULL | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+ 1 row in set相关字段说明如下:
Non_unique:如果索引不能包括重复值则为0,如果可以则为1。也就是通常所说的唯一索引。Key_name:索引名。Seq_in_index:索引中的列序列号,1或者2,用于表明该索引在联合索引中的顺序。Column_name:索引的列名。Collation:表示列以什么方式存储在索引中。Cardinality:基数的意思,表示索引中唯一值的数目的估计值。Sub_part:前缀索引,如果列只是被部分地编入索引,则为被编入索引的字符的数目。如果整列被编入索引,则为NULL。Packed:指示关键字如何被压缩。如果没有被压缩,则为NULL。Null:表示索引值是否可以为空。Index_type:表示索引类型。当前仅支持BTREE类型。Comment:表示该索引是否可用。Index_comment:该索引的注释信息。Visible:表示该索引是否可见。
查看表
test中包括隐藏索引在内的所有索引。obclient [test]> SHOW EXTENDED INDEX FROM test;查询结果如下:
+-------+------------+----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +-------+------------+----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+ | test | 0 | PRIMARY | 1 | __pk_increment | A | NULL | NULL | NULL | | BTREE | available | | YES | NULL | | test | 0 | PRIMARY | 2 | id | A | NULL | NULL | NULL | | BTREE | available | | YES | NULL | | test | 0 | PRIMARY | 3 | name | A | NULL | NULL | NULL | | BTREE | available | | YES | NULL | | test | 1 | IDX | 1 | id | A | NULL | NULL | NULL | YES | BTREE | available | | YES | NULL | | test | 1 | IDX | 2 | __pk_increment | A | NULL | NULL | NULL | | BTREE | available | | YES | NULL | +-------+------------+----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+ 5 rows in set
更多 SHOW INDEX 语句的介绍,请参见 SHOW。
使用视图查看索引信息
可以使用视图 information_schema.STATISTICS 查看表索引信息,包含表名、是否是唯一索引、索引名、索引编号、列名等。
可以使用视图 oceanbase.DBA_OB_TABLE_LOCATIONS 查看索引是全局索引(
GLOBAL)还是局部索引(LOCAL)。
示例如下:
查看表
tbl1下有哪些视图。SELECT TABLE_SCHEMA, TABLE_NAME, SEQ_IN_INDEX, INDEX_NAME, COLUMN_NAME FROM information_schema.STATISTICS WHERE TABLE_NAME = 'tbl1';返回结果如下:
+--------------+------------+--------------+------------+-------------+ | TABLE_SCHEMA | TABLE_NAME | SEQ_IN_INDEX | INDEX_NAME | COLUMN_NAME | +--------------+------------+--------------+------------+-------------+ | test | tbl1 | 1 | tbl1_idx1 | col1 | | test | tbl1 | 2 | tbl1_idx1 | col2 | | test | tbl1 | 1 | tbl1_idx2 | col3 | +--------------+------------+--------------+------------+-------------+ 3 rows in set查看索引
tbl1_idx1是全局索引(GLOBAL)还是局部索引(LOCAL)。SELECT DATABASE_NAME, INDEX_NAME, INDEX_TYPE FROM oceanbase.DBA_OB_TABLE_LOCATIONS WHERE INDEX_NAME = 'tbl1_idx1';返回结果如下:
+---------------+------------+------------+ | DATABASE_NAME | INDEX_NAME | INDEX_TYPE | +---------------+------------+------------+ | test | tbl1_idx1 | LOCAL | +---------------+------------+------------+ 1 row in set从视图
information_schema.STATISTICS和oceanbase.DBA_OB_TABLE_LOCATIONS中获取表tbl1的索引信息。SELECT ss.TABLE_SCHEMA, ss.TABLE_NAME, ss.SEQ_IN_INDEX, ss.INDEX_NAME, ss.COLUMN_NAME, ls.INDEX_TYPE FROM information_schema.STATISTICS ss, oceanbase.DBA_OB_TABLE_LOCATIONS ls WHERE ss.TABLE_SCHEMA = ls.DATABASE_NAME AND ss.INDEX_NAME = ls.INDEX_NAME AND ss.TABLE_NAME = 'tbl1';返回结果如下:
+--------------+------------+--------------+------------+-------------+------------+ | TABLE_SCHEMA | TABLE_NAME | SEQ_IN_INDEX | INDEX_NAME | COLUMN_NAME | INDEX_TYPE | +--------------+------------+--------------+------------+-------------+------------+ | test | tbl1 | 2 | tbl1_idx1 | col2 | LOCAL | | test | tbl1 | 1 | tbl1_idx1 | col1 | LOCAL | | test | tbl1 | 1 | tbl1_idx2 | col3 | LOCAL | +--------------+------------+--------------+------------+-------------+------------+ 3 rows in set
相关文档
更多 MySQL 模式下索引相关的内容,参见以下信息: