Documentation Home
MySQL 5.7 参考手册

13.7.5.22 SHOW INDEX 语句

SHOW {INDEX | INDEXES | KEYS}
    {FROM | IN} tbl_name
    [{FROM | IN} db_name]
    [WHERE expr]

SHOW INDEX返回表索引信息。格式类似于 SQLStatisticsODBC 中的调用格式。此语句需要对表中的任何列具有一定的权限。 

mysql> SHOW INDEX FROM City\G
*************************** 1. row ***************************
        Table: city
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: ID
    Collation: A
  Cardinality: 4188
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: city
   Non_unique: 1
     Key_name: CountryCode
 Seq_in_index: 1
  Column_name: CountryCode
    Collation: A
  Cardinality: 232
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:

语法 的替代方法是. . 这两个语句是等价的： tbl_name FROM db_namedb_nametbl_name

SHOW INDEX FROM mytable FROM mydb;
SHOW INDEX FROM mydb.mytable;

该WHERE子句可以使用更一般的条件来选择行，如 第 24.8 节，“SHOW 语句的扩展”中所讨论的。

SHOW INDEX返回以下字段：

  • Table

    表的名称。

  • Non_unique

    如果索引不能包含重复项，则为 0，如果可以，则为 1。

  • Key_name

    索引的名称。如果索引是主键，则名称始终是PRIMARY。

  • Seq_in_index

    索引中的列序号，从 1 开始。

  • Column_name

    列的名称。

  • Collation

    列在索引中的排序方式。这可以有值 A（升序）或NULL （未排序）。

  • Cardinality

    索引中唯一值数量的估计。要更新此数字，请运行ANALYZE TABLE或（对于MyISAM表） myisamchk -a。

    Cardinality is counted based on statistics stored as integers, so the value is not necessarily exact even for small tables. The higher the cardinality, the greater the chance that MySQL uses the index when doing joins.

  • Sub_part

    The index prefix. That is, the number of indexed characters if the column is only partly indexed, NULL if the entire column is indexed.

    Note

    Prefix limits are measured in bytes. However, prefix lengths for index specifications in CREATE TABLE, ALTER TABLE, and CREATE INDEX statements are interpreted as number of characters for nonbinary string types (CHAR, VARCHAR, TEXT) and number of bytes for binary string types (BINARY, VARBINARY, BLOB). Take this into account when specifying a prefix length for a nonbinary string column that uses a multibyte character set.

    For additional information about index prefixes, see Section 8.3.4, “Column Indexes”, and Section 13.1.14, “CREATE INDEX Statement”.

  • Packed

    Indicates how the key is packed. NULL if it is not.

  • Null

    Contains YES if the column may contain NULL values and '' if not.

  • Index_type

    The index method used (BTREE, FULLTEXT, HASH, RTREE).

  • Comment

    Information about the index not described in its own column, such as disabled if the index is disabled.

  • Index_comment

    COMMENT创建索引时为具有属性的 索引提供的任何注释 。

有关表索引的信息也可从 INFORMATION_SCHEMA STATISTICS表中获得。请参阅 第 24.3.24 节，“INFORMATION_SCHEMA 统计表”。

您可以使用mysqlshow -k db_name tbl_name命令 列出表的索引。