全部产品
云市场
云游戏

SHOW GLOBAL INDEX

更新时间:2020-08-07 20:19:05

PolarDB-X 从 5.4.1 版本开始支持使用全局二级索引,由于全局二级索引可以使用不同于主表的拆分方式,因此在 PolarDB-X 中的实现是一个独立的逻辑表,与普通的局部索引不同。

为了能单独查看已创建或创建中的全局二级索引,PolarDB-X 提供了 SHOW GLOBAL INDEX 语句。

语法:

  1. SHOW GLOBAL {INDEX | INDEXES} [FROM [schema_name.]tbl_name]

schema_name 和 tbl_name 是可选的,用于过滤表名或查看其它数据库上表的信息。

样例:

  1. show global index; # 查询当前数据库上所有表的全局二级索引信息
  2. show global index from xxx_tb; # 查询当前数据库上 xxx_tb 的全局二级索引信息
  3. show global index from xxx_db.xxx_tb; # 查询 xxx_db 上 xxx_tb 的全局二级索引信息(跨库查询)

返回结果样例:

  1. mysql> show global index;
  2. +---------------------+----------------------+------------+-------------------------------+----------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+----------+
  3. | SCHEMA | TABLE | NON_UNIQUE | KEY_NAME | INDEX_NAMES | COVERING_NAMES | INDEX_TYPE | DB_PARTITION_KEY | DB_PARTITION_POLICY | DB_PARTITION_COUNT | TB_PARTITION_KEY | TB_PARTITION_POLICY | TB_PARTITION_COUNT | STATUS |
  4. +---------------------+----------------------+------------+-------------------------------+----------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+----------+
  5. | XXXX_DRDS_LOCAL_APP | full_gsi_ddl_renamed | 1 | g_i_c_ddl_c_blob_long_renamed | c_blob_long | id, c_bit_1, c_bit_8, c_bit_16, c_bit_32, c_bit_64, c_tinyint_1, c_tinyint_1_un, c_tinyint_4, c_tinyint_4_un, c_tinyint_8, c_tinyint_8_un, c_smallint_16, c_smallint_16_un, c_mediumint_1, c_mediumint_24, c_mediumint_24_un, c_int_1, c_int_32, c_int_32_un, c_bigint_1, c_bigint_64, c_bigint_64_un, c_decimal, c_decimal_pr, c_float, c_float_pr, c_float_un, c_double, c_double_pr, c_double_un, c_date, c_datetime, c_datetime_3, c_datetime_6, c_timestamp_1, c_timestamp_3, c_time, c_time_1, c_time_3, c_time_6, c_year, c_year_4, c_char, c_varchar, c_binary, c_varbinary, c_blob_tiny, c_blob_medium, c_text_tiny, c_text, c_text_medium, c_text_long, c_enum, c_set, c_json, c_point, c_linestring, c_polygon, c_multipoint, c_multilinestring, c_multipolygon, c_geometrycollection, c_geometory | NULL | c_blob_long | HASH | 4 | c_blob_long | HASH | 3 | PUBLIC |
  6. | XXXX_DRDS_LOCAL_APP | full_gsi_ddl_renamed | 1 | g_i_c_ddl_c_mediumint_1 | c_mediumint_1 | id, c_bit_1, c_bit_8, c_bit_16, c_bit_32, c_bit_64, c_tinyint_1, c_tinyint_1_un, c_tinyint_4, c_tinyint_4_un, c_tinyint_8, c_tinyint_8_un, c_smallint_16, c_smallint_16_un, c_mediumint_24, c_mediumint_24_un, c_int_1, c_int_32, c_int_32_un, c_bigint_1, c_bigint_64, c_bigint_64_un, c_decimal, c_decimal_pr, c_float, c_float_pr, c_float_un, c_double, c_double_pr, c_double_un, c_date, c_datetime, c_datetime_3, c_datetime_6, c_timestamp_1, c_timestamp_3, c_time, c_time_1, c_time_3, c_time_6, c_year, c_year_4, c_char, c_varchar, c_binary, c_varbinary, c_blob_tiny, c_blob_medium, c_blob_long, c_text_tiny, c_text, c_text_medium, c_text_long, c_enum, c_set, c_json, c_point, c_linestring, c_polygon, c_multipoint, c_multilinestring, c_multipolygon, c_geometrycollection, c_geometory, c_smallint_1, c_timestamp_6 | NULL | c_mediumint_1 | HASH | 4 | c_mediumint_1 | HASH | 3 | PUBLIC |
  7. | XXXX_DRDS_LOCAL_APP | full_gsi_ddl_renamed | 1 | g_i_c_ddl_c_smallint_16_un | c_smallint_16_un, c_time_1 | id, c_bit_1, c_bit_8, c_bit_16, c_bit_32, c_bit_64, c_tinyint_1, c_tinyint_1_un, c_tinyint_4, c_tinyint_4_un, c_tinyint_8, c_tinyint_8_un, c_smallint_16, c_mediumint_1, c_mediumint_24, c_mediumint_24_un, c_int_1, c_int_32, c_int_32_un, c_bigint_1, c_bigint_64, c_bigint_64_un, c_decimal, c_decimal_pr, c_float, c_float_pr, c_float_un, c_double, c_double_pr, c_double_un, c_date, c_datetime, c_datetime_3, c_datetime_6, c_timestamp_1, c_timestamp_3, c_time, c_time_3, c_time_6, c_year, c_year_4, c_char, c_varchar, c_binary, c_varbinary, c_blob_tiny, c_blob_medium, c_blob_long, c_text_tiny, c_text, c_text_medium, c_text_long, c_enum, c_set, c_json, c_point, c_linestring, c_polygon, c_multipoint, c_multilinestring, c_multipolygon, c_geometrycollection, c_geometory | NULL | c_smallint_16_un | HASH | 4 | c_smallint_16_un | HASH | 3 | PUBLIC |
  8. | XXXX_DRDS_LOCAL_APP | t_order | 0 | g_i_seller | seller_id | id, order_id | HASH | seller_id | HASH | 4 | seller_id | HASH | 2 | CREATING |
  9. +---------------------+----------------------+------------+-------------------------------+----------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+----------+
  10. 4 rows in set (0.01 sec)

列名说明:

列名 描述
SCHEMA 库名
TABLE 表名
NON_UNIQUE 1:普通全局二级索引 0:唯一约束全局二级索引
KEY_NAME 索引名
INDEX_NAMES 索引列
COVERING_NAMES 覆盖列
INDEX_TYPE 索引类型(未指定、BTREE、HASH)
DB_PARTITION_KEY 分库拆分键
DB_PARTITION_POLICY 分库拆分函数
DB_PARTITION_COUNT 分库数量
TB_PARTITION_KEY 分表拆分键
TB_PARTITION_POLICY 分表拆分函数
TB_PARTITION_COUNT 分表数
STATUS 当前状态(CREATING、DELETE_ONLY、WRITE_ONLY、WRITE_REORG、PUBLIC、ABSENT)