如何通过系统表查看集群表上的hash索引

有没有系统表可以查看都有哪些表创建有hash索引?


2 回答

8a数据库中的information_schema.COLUMNS表中记录了某列是否是索引列,可以通过如下sql查找所有的索引字段:
select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLUMN_KEY from information_schema.COLUMNS where COLUMN_KEY='MUL' and TABLE_SCHEMA<>'gbase';
gbase> select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLUMN_KEY from information_schema.COLUMNS where COLUMN_KEY='MUL' and TABLE_SCHEMA<>'gbase';
+--------------+------------+-------------+------------+
| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | COLUMN_KEY |
+--------------+------------+-------------+------------+
| db1          | t3         | c1          | MUL        |
| test         | aaa        | a           | MUL        |
+--------------+------------+-------------+------------+
2 rows in set (Elapsed: 00:00:00.01)
获取具体的表名称后,可以通过如下sql查看该表索引的具体情况:
gbase> show index from db1.t3;
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+-------------+---------+
| Table | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type  | Comment |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+-------------+---------+
| t3    |          1 | idx_t3_c3 |            1 | c1          | NULL      |        NULL |     NULL | NULL   | YES  | GLOBAL HASH |         |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+-------------+---------+
1 row in set (Elapsed: 00:00:00.00)

2018-9-11 11:09
查看information_schema.statistics
gbase> SELECT TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,index_name,index_type FROM information_schema.statistics where TABLE_SCHEMA='db1';
+--------------+------------+-------------+------------+-------------+
| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | index_name | index_type  |
+--------------+------------+-------------+------------+-------------+
| db1          | t3         | c1          | idx_t3_c3  | GLOBAL HASH |
+--------------+------------+-------------+------------+-------------+
1 row in set (Elapsed: 00:00:00.00)

2018-9-11 11:11

撰写回答

您需要登录后才可以回帖 登录 | 立即注册

提问者

发布108
回答300

相关问题

相关资料