数据库对象管理MySQL模式查看索引

可以通过系统视图来查看索引，查看索引的视图有：USER_INDEXES、ALL_INDEXES、DBA_INDEXES、ALL_IND_COLUMNS、DBA_IND_COLUMNS 和 USER_IND_COLUMNS。

• 用于查看用户拥有的所有表的索引信息

  obclient> SELECT * FROM USER_INDEXES WHERE table_name='TEST';
  +-------------+------------+-------------+------------+------------+------------+-------------+---------------+-----------------+-----------+-----------+----------------+-------------+-------------+-------------+--------------+---------------+----------------+-----------+-----------------+----------+---------+--------+-------------+---------------+-------------------------+-------------------------+-------------------+--------+----------+-------------+---------------+--------+-----------+-------------+-----------+-----------+-----------+-------------+-------------+------------------+------------+----------+-------------------+------------+-----------+------------+--------------+---------------+-----------------+----------------+------------+-------------------------+---------+------------+-------------------+-----------------+
  | INDEX_NAME  | INDEX_TYPE | TABLE_OWNER | TABLE_NAME | TABLE_TYPE | UNIQUENESS | COMPRESSION | PREFIX_LENGTH | TABLESPACE_NAME | INI_TRANS | MAX_TRANS | INITIAL_EXTENT | NEXT_EXTENT | MIN_EXTENTS | MAX_EXTENTS | PCT_INCREASE | PCT_THRESHOLD | INCLUDE_COLUMN | FREELISTS | FREELIST_GROUPS | PCT_FREE | LOGGING | BLEVEL | LEAF_BLOCKS | DISTINCT_KEYS | AVG_LEAF_BLOCKS_PER_KEY | AVG_DATA_BLOCKS_PER_KEY | CLUSTERING_FACTOR | STATUS | NUM_ROWS | SAMPLE_SIZE | LAST_ANALYZED | DEGREE | INSTANCES | PARTITIONED | TEMPORARY | GENERATED | SECONDARY | BUFFER_POOL | FLASH_CACHE | CELL_FLASH_CACHE | USER_STATS | DURATION | PCT_DIRECT_ACCESS | ITYP_OWNER | ITYP_NAME | PARAMETERS | GLOBAL_STATS | DOMIDX_STATUS | DOMIDX_OPSTATUS | FUNCIDX_STATUS | JOIN_INDEX | IOT_REDUNDANT_PKEY_ELIM | DROPPED | VISIBILITY | DOMIDX_MANAGEMENT | SEGMENT_CREATED |
  +-------------+------------+-------------+------------+------------+------------+-------------+---------------+-----------------+-----------+-----------+----------------+-------------+-------------+-------------+--------------+---------------+----------------+-----------+-----------------+----------+---------+--------+-------------+---------------+-------------------------+-------------------------+-------------------+--------+----------+-------------+---------------+--------+-----------+-------------+-----------+-----------+-----------+-------------+-------------+------------------+------------+----------+-------------------+------------+-----------+------------+--------------+---------------+-----------------+----------------+------------+-------------------------+---------+------------+-------------------+-----------------+
  | T5_NAME_IND | NORMAL     | SYS         | TEST       | TABLE      | NONUNIQUE  | ENABLED     |          NULL | NULL            |      NULL |      NULL |           NULL |        NULL |        NULL |        NULL |         NULL |          NULL |           NULL |      NULL |            NULL |     NULL | NULL    |   NULL |        NULL |          NULL |                    NULL |                    NULL |              NULL | VALID  |     NULL |        NULL | NULL          | 1      | NULL      | NO          | NULL      | NULL      | NULL      | NULL        | NULL        | NULL             | NULL       | NULL     |              NULL | NULL       | NULL      | NULL       | NULL         | NULL          | NULL            | NULL           | NO         | NULL                    | NO      | VISIBLE    | NULL              | NULL            |
  +-------------+------------+-------------+------------+------------+------------+-------------+---------------+-----------------+-----------+-----------+----------------+-------------+-------------+-------------+--------------+---------------+----------------+-----------+-----------------+----------+---------+--------+-------------+---------------+-------------------------+-------------------------+-------------------+--------+----------+-------------+---------------+--------+-----------+-------------+-----------+-----------+-----------+-------------+-------------+------------------+------------+----------+-------------------+------------+-----------+------------+--------------+---------------+-----------------+----------------+------------+-------------------------+---------+------------+-------------------+-----------------+
  1 row in set (0.00 sec)
  复制

  TABLE_NAME 指定需要查询的表名为 TEST 。

  INDEX_TYPE 表示索引类型， NORMAL 代表的是普通索引。

  UNIQUENESS 表示索引是否唯一，该字段显示 NONUNIQUE 表示不唯一。

• 查看表的所有索引

  obclient> SELECT * FROM ALL_INDEXES WHERE table_name='TEST';
  +-------+-------------+------------+-------------+------------+------------+------------+-------------+---------------+-----------------+-----------+-----------+----------------+-------------+-------------+-------------+--------------+---------------+----------------+-----------+-----------------+----------+---------+--------+-------------+---------------+-------------------------+-------------------------+-------------------+--------+----------+-------------+---------------+--------+-----------+-------------+-----------+-----------+-----------+-------------+-------------+------------------+------------+----------+-------------------+------------+-----------+------------+--------------+---------------+-----------------+----------------+------------+-------------------------+---------+------------+-------------------+-----------------+
  | OWNER | INDEX_NAME  | INDEX_TYPE | TABLE_OWNER | TABLE_NAME | TABLE_TYPE | UNIQUENESS | COMPRESSION | PREFIX_LENGTH | TABLESPACE_NAME | INI_TRANS | MAX_TRANS | INITIAL_EXTENT | NEXT_EXTENT | MIN_EXTENTS | MAX_EXTENTS | PCT_INCREASE | PCT_THRESHOLD | INCLUDE_COLUMN | FREELISTS | FREELIST_GROUPS | PCT_FREE | LOGGING | BLEVEL | LEAF_BLOCKS | DISTINCT_KEYS | AVG_LEAF_BLOCKS_PER_KEY | AVG_DATA_BLOCKS_PER_KEY | CLUSTERING_FACTOR | STATUS | NUM_ROWS | SAMPLE_SIZE | LAST_ANALYZED | DEGREE | INSTANCES | PARTITIONED | TEMPORARY | GENERATED | SECONDARY | BUFFER_POOL | FLASH_CACHE | CELL_FLASH_CACHE | USER_STATS | DURATION | PCT_DIRECT_ACCESS | ITYP_OWNER | ITYP_NAME | PARAMETERS | GLOBAL_STATS | DOMIDX_STATUS | DOMIDX_OPSTATUS | FUNCIDX_STATUS | JOIN_INDEX | IOT_REDUNDANT_PKEY_ELIM | DROPPED | VISIBILITY | DOMIDX_MANAGEMENT | SEGMENT_CREATED |
  +-------+-------------+------------+-------------+------------+------------+------------+-------------+---------------+-----------------+-----------+-----------+----------------+-------------+-------------+-------------+--------------+---------------+----------------+-----------+-----------------+----------+---------+--------+-------------+---------------+-------------------------+-------------------------+-------------------+--------+----------+-------------+---------------+--------+-----------+-------------+-----------+-----------+-----------+-------------+-------------+------------------+------------+----------+-------------------+------------+-----------+------------+--------------+---------------+-----------------+----------------+------------+-------------------------+---------+------------+-------------------+-----------------+
  | SYS   | T5_NAME_IND | NORMAL     | SYS         | TEST       | TABLE      | NONUNIQUE  | ENABLED     |          NULL | NULL            |      NULL |      NULL |           NULL |        NULL |        NULL |        NULL |         NULL |          NULL |           NULL |      NULL |            NULL |     NULL | NULL    |   NULL |        NULL |          NULL |                    NULL |                    NULL |              NULL | VALID  |     NULL |        NULL | NULL          | 1      | NULL      | NO          | NULL      | NULL      | NULL      | NULL        | NULL        | NULL             | NULL       | NULL     |              NULL | NULL       | NULL      | NULL       | NULL         | NULL          | NULL            | NULL           | NO         | NULL                    | NO      | VISIBLE    | NULL              | NULL            |
  +-------+-------------+------------+-------------+------------+------------+------------+-------------+---------------+-----------------+-----------+-----------+----------------+-------------+-------------+-------------+--------------+---------------+----------------+-----------+-----------------+----------+---------+--------+-------------+---------------+-------------------------+-------------------------+-------------------+--------+----------+-------------+---------------+--------+-----------+-------------+-----------+-----------+-----------+-------------+-------------+------------------+------------+----------+-------------------+------------+-----------+------------+--------------+---------------+-----------------+----------------+------------+-------------------------+---------+------------+-------------------+-----------------+
  1 row in set (0.00 sec)
  复制

  COMPRESSION 字段信息代表是否开启索引压缩功能 默认是 ENABLED 状态。

  PREFIX_LENGTH 表示索引压缩中被压缩的字段数量。

  INCLUDE_COLUMN 字段包含在索引组织表主键（非溢出）索引中的最后一列的列 ID。

  DISTINCT_KEYS 代表不同索引值的数量。

  DROPPED 指示索引是否已被删除并且是否在回收站中。

• 用于查看数据库所有表的索引信息

  obclient> SELECT * FROM DBA_INDEXES ;
  +-------+---------------------------------------+------------+-------------+-------------+------------+------------+-------------+---------------+-----------------+-----------+-----------+----------------+-------------+-------------+-------------+--------------+---------------+----------------+-----------+-----------------+----------+---------+--------+-------------+---------------+-------------------------+-------------------------+-------------------+--------+----------+-------------+---------------+--------+-----------+-------------+-----------+-----------+-----------+-------------+-------------+------------------+------------+----------+-------------------+------------+-----------+------------+--------------+---------------+-----------------+----------------+------------+-------------------------+---------+------------+-------------------+-----------------+
  | OWNER | INDEX_NAME                            | INDEX_TYPE | TABLE_OWNER | TABLE_NAME  | TABLE_TYPE | UNIQUENESS | COMPRESSION | PREFIX_LENGTH | TABLESPACE_NAME | INI_TRANS | MAX_TRANS | INITIAL_EXTENT | NEXT_EXTENT | MIN_EXTENTS | MAX_EXTENTS | PCT_INCREASE | PCT_THRESHOLD | INCLUDE_COLUMN | FREELISTS | FREELIST_GROUPS | PCT_FREE | LOGGING | BLEVEL | LEAF_BLOCKS | DISTINCT_KEYS | AVG_LEAF_BLOCKS_PER_KEY | AVG_DATA_BLOCKS_PER_KEY | CLUSTERING_FACTOR | STATUS | NUM_ROWS | SAMPLE_SIZE | LAST_ANALYZED | DEGREE | INSTANCES | PARTITIONED | TEMPORARY | GENERATED | SECONDARY | BUFFER_POOL | FLASH_CACHE | CELL_FLASH_CACHE | USER_STATS | DURATION | PCT_DIRECT_ACCESS | ITYP_OWNER | ITYP_NAME | PARAMETERS | GLOBAL_STATS | DOMIDX_STATUS | DOMIDX_OPSTATUS | FUNCIDX_STATUS | JOIN_INDEX | IOT_REDUNDANT_PKEY_ELIM | DROPPED | VISIBILITY | DOMIDX_MANAGEMENT | SEGMENT_CREATED |
  +-------+---------------------------------------+------------+-------------+-------------+------------+------------+-------------+---------------+-----------------+-----------+-----------+----------------+-------------+-------------+-------------+--------------+---------------+----------------+-----------+-----------------+----------+---------+--------+-------------+---------------+-------------------------+-------------------------+-------------------+--------+----------+-------------+---------------+--------+-----------+-------------+-----------+-----------+-----------+-------------+-------------+------------------+------------+----------+-------------------+------------+-----------+------------+--------------+---------------+-----------------+----------------+------------+-------------------------+---------+------------+-------------------+-----------------+
  | SYS   | TABLE_NAME1_OBPK_1639997002986248     | NORMAL     | SYS         | TABLE_NAME1 | TABLE      | UNIQUE     | ENABLED     |          NULL | NULL            |      NULL |      NULL |           NULL |        NULL |        NULL |        NULL |         NULL |          NULL |           NULL |      NULL |            NULL |     NULL | NULL    |   NULL |        NULL |          NULL |                    NULL |                    NULL |              NULL | VALID  |     NULL |        NULL | NULL          | 1      | NULL      | NO          | NULL      | NULL      | NULL      | NULL        | NULL        | NULL             | NULL       | NULL     |              NULL | NULL       | NULL      | NULL       | NULL         | NULL          | NULL            | NULL           | NO         | NULL                    | NO      | VISIBLE    | NULL              | NULL            |
  +-------+---------------------------------------+------------+-------------+-------------+------------+------------+-------------+---------------+-----------------+-----------+-----------+----------------+-------------+-------------+-------------+--------------+---------------+----------------+-----------+-----------------+----------+---------+--------+-------------+---------------+-------------------------+-------------------------+-------------------+--------+----------+-------------+---------------+--------+-----------+-------------+-----------+-----------+-----------+-------------+-------------+------------------+------------+----------+-------------------+------------+-----------+------------+--------------+---------------+-----------------+----------------+------------+-------------------------+---------+------------+-------------------+-----------------+
  5 rows in set (0.01 sec)
  复制

  OWNER 该字段代表当前数据库的用户为 SYS。

  INDEX_TYPE 表示索引类型， NORMAL 代表的是普通索引。

  UNIQUENESS 表示唯一不允许重复的索引。

• 查看用户可访问的所有表的索引的索引列信息

  bclient> SELECT * FROM ALL_IND_COLUMNS ;
  +-------------+---------------------------------------+-------------+-------------+-------------+-----------------+---------------+-------------+---------+
  | INDEX_OWNER | INDEX_NAME                            | TABLE_OWNER | TABLE_NAME  | COLUMN_NAME | COLUMN_POSITION | COLUMN_LENGTH | CHAR_LENGTH | DESCEND |
  +-------------+---------------------------------------+-------------+-------------+-------------+-----------------+---------------+-------------+---------+
  | SYS         | TABLE_NAME1_OBPK_1639997002986248     | SYS         | TABLE_NAME1 | W_ID        |               1 |            22 |           0 | ASC     |
  +-------------+---------------------------------------+-------------+-------------+-------------+-----------------+---------------+-------------+---------+
  12 rows in set (0.02 sec)
  复制

  • DESCEND 该字段显示 ASC 表示为升序索引。

  • COLUMN_NAME 代表列名。

• 查看数据库所有表的索引的索引列信息

  obclient> SELECT * FROM DBA_IND_COLUMNS ;
  +-------------+---------------------------------------+-------------+-------------+-------------+-----------------+---------------+-------------+---------+
  | INDEX_OWNER | INDEX_NAME                            | TABLE_OWNER | TABLE_NAME  | COLUMN_NAME | COLUMN_POSITION | COLUMN_LENGTH | CHAR_LENGTH | DESCEND |
  +-------------+---------------------------------------+-------------+-------------+-------------+-----------------+---------------+-------------+---------+
  | SYS         | TABLE_NAME1_OBPK_1639997002986248     | SYS         | TABLE_NAME1 | W_ID        |               1 |            22 |           0 | ASC     |
  +-------------+---------------------------------------+-------------+-------------+-------------+-----------------+---------------+-------------+---------+
  12 rows in set (0.02 sec)
  复制

  • INDEX_OWNER 表示索引拥有者。

  • INDEX_NAME 索引名。

• 查看表的索引的详细信息

  obclient> SELECT * FROM USER_IND_COLUMNS WHERE table_name='TEST';
  +-------------+------------+-------------+-----------------+---------------+-------------+---------+
  | INDEX_NAME  | TABLE_NAME | COLUMN_NAME | COLUMN_POSITION | COLUMN_LENGTH | CHAR_LENGTH | DESCEND |
  +-------------+------------+-------------+-----------------+---------------+-------------+---------+
  | T5_NAME_IND | TEST       | NAME        |               1 |             1 |           1 | ASC     |
  +-------------+------------+-------------+-----------------+---------------+-------------+---------+
  1 row in set (0.02 sec)
  复制

  • COLUMN_POSITION 代表列位置点信息。

  • COLUMN_LENGTH 表示列长。

  • CHAR_LENGTH 表示字符长度。