MySQL :: MySQL 8.4 参考手册 :: 28.3.34 INFORMATION

8.4 中文镜像

MySQL 8.4 参考手册 · 中文镜像

正文为中文，英文原文默认隐藏。点击任意中文段落即可展开 / 收起对应英文原文。

打开官方英文原文 ↗

译文由机器翻译生成，仅供理解参考；有疑问请以官方英文原文为准。

MySQL 8.4 参考手册 / 28 个 INFORMATION_SCHEMA 表 / 28.3 INFORMATION_SCHEMA 总表 / 28.3.34 INFORMATION_SCHEMA 统计表

中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 74%

28.3.34 INFORMATION_SCHEMA STATISTICS 表28.3.34 The INFORMATION_SCHEMA STATISTICS Table

STATISTICS 表提供关于表索引的信息。The STATISTICS table provides information about table indexes.

STATISTICS 中表示表统计信息的列保存的是缓存值。information_schema_stats_expiry 系统变量定义了缓存的表统计信息过期前的时间周期。默认值是 86400 秒（24 小时）。如果没有缓存的统计信息或者统计信息已过期，则在查询表统计信息列时，统计信息将从存储引擎中获取。要随时更新给定表的缓存值，请使用 ANALYZE TABLE。要始终直接从存储引擎获取最新的统计信息，请设置 information_schema_stats_expiry=0。有关更多信息，请参见第 10.2.3 节，“优化 INFORMATION_SCHEMA 查询”。Columns in STATISTICS that represent table statistics hold cached values. The information_schema_stats_expiry system variable defines the period of time before cached table statistics expire. The default is 86400 seconds (24 hours). If there are no cached statistics or statistics have expired, statistics are retrieved from storage engines when querying table statistics columns. To update cached values at any time for a given table, use ANALYZE TABLE. To always retrieve the latest statistics directly from storage engines, set information_schema_stats_expiry=0. For more information, see Section 10.2.3, “Optimizing INFORMATION_SCHEMA Queries”.

Note

如果启用了 innodb_read_only 系统变量，ANALYZE TABLE 可能会失败，因为它无法更新使用 InnoDB 的数据字典中的统计表。对于更新键分布的 ANALYZE TABLE 操作，即使操作更新了表本身（例如，如果它是 MyISAM 表），也可能会失败。要获得更新的分布统计信息，请设置 information_schema_stats_expiry=0。If the innodb_read_only system variable is enabled, ANALYZE TABLE may fail because it cannot update statistics tables in the data dictionary, which use InnoDB. For ANALYZE TABLE operations that update the key distribution, failure may occur even if the operation updates the table itself (for example, if it is a MyISAM table). To obtain the updated distribution statistics, set information_schema_stats_expiry=0.

STATISTICS 表有以下列：The STATISTICS table has these columns:

TABLE_CATALOG
包含索引的表所属目录的名称。该值始终为 def。The name of the catalog to which the table containing the index belongs. This value is always def.
TABLE_SCHEMA
包含索引的表所属模式（数据库）的名称。The name of the schema (database) to which the table containing the index belongs.
TABLE_NAMETABLE_NAME
包含索引的表的名称。The name of the table containing the index.
NON_UNIQUE
如果索引不允许重复，则为 0；如果允许，则为 1。0 if the index cannot contain duplicates, 1 if it can.
INDEX_SCHEMA
索引所属模式（数据库）的名称。The name of the schema (database) to which the index belongs.
INDEX_NAME
索引的名称。如果索引是主键，则名称始终为 PRIMARY。The name of the index. If the index is the primary key, the name is always PRIMARY.
SEQ_IN_INDEX
索引中的列序号，从 1 开始。The column sequence number in the index, starting with 1.
COLUMN_NAMECOLUMN_NAME
列名。另请参见 EXPRESSION 列的描述。The column name. See also the description for the EXPRESSION column.
COLLATIONCOLLATION
列在索引中的排序方式。可以取值 A（升序）、D（降序）或 NULL（未排序）。How the column is sorted in the index. This can have values A (ascending), D (descending), or NULL (not sorted).
CARDINALITY
索引中唯一值的估计数量。要更新此数字，请执行 ANALYZE TABLE 或（针对 MyISAM 表） myisamchk -a。An estimate of the number of unique values in the index. To update this number, run ANALYZE TABLE or (for MyISAM tables) myisamchk -a.
CARDINALITY 是基于以整数形式存储的统计信息进行计数的，因此即使对于小表，该值也不一定精确。基数越高，MySQL 在进行连接操作时使用索引的可能性就越大。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
索引前缀。也就是说，如果列只是部分建立索引，则为已索引字符的数量；如果整个列都有索引，则为 NULL。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
前缀限制以字节为单位。然而，索引规范前缀 lengths 用于CREATE TABLE“， ALTER TABLE”， CREATE INDEX语句被解释为非二元字符串类型的字符数（CHARVARCHAR， TEXT以及二进制字符串的字节数类型（BINARY， VARBINARY BLOB）。在为使用多字节字符集的非二元字符串列指定前缀长度时，请考虑这一点。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.
有关索引前缀的更多信息，请参阅第 10.3.5 节，“列索引” 和第 15.1.15 节，“CREATE INDEX 语句”。For additional information about index prefixes, see Section 10.3.5, “Column Indexes”, and Section 15.1.15, “CREATE INDEX Statement”.
PACKED
指示键是如何打包的。如果没有打包，则为 NULL。Indicates how the key is packed. NULL if it is not.
NULLABLE
如果该列可能包含 NULL 值，则为 YES；如果不包含，则为 ''。Contains YES if the column may contain NULL values and '' if not.
INDEX_TYPE
使用的索引方法（BTREE、FULLTEXT、HASH、RTREE）。The index method used (BTREE, FULLTEXT, HASH, RTREE).
COMMENTCOMMENT
关于索引的信息，如果未在其自身列中描述，例如索引被禁用时显示 disabled。Information about the index not described in its own column, such as disabled if the index is disabled.
INDEX_COMMENT
为索引创建时提供的 COMMENT 属性的任何评论。Any comment provided for the index with a COMMENT attribute when the index was created.
IS_VISIBLE
索引是否对优化器可见。参见第 10.3.12 节，“不可见索引”。Whether the index is visible to the optimizer. See Section 10.3.12, “Invisible Indexes”.
EXPRESSIONEXPRESSION
MySQL 支持函数键部分（请参阅函数键部分），这会影响 COLUMN_NAME 和 EXPRESSION 列：MySQL supports functional key parts (see Functional Key Parts), which affects both the COLUMN_NAME and EXPRESSION columns:
- 对于非函数键部分，COLUMN_NAME 表示键部分索引的列，EXPRESSION 为 NULL。For a nonfunctional key part, COLUMN_NAME indicates the column indexed by the key part and EXPRESSION is NULL.
- 对于函数键部分，COLUMN_NAME 列为 NULL，EXPRESSION 表示键部分的表达式。For a functional key part, COLUMN_NAME column is NULL and EXPRESSION indicates the expression for the key part.

注意事项Notes

没有用于索引的标准 INFORMATION_SCHEMA 表。MySQL 的列列表类似于 SQL Server 2000 为 sp_statistics 返回的结果，只是 QUALIFIER 和 OWNER 分别被替换为 CATALOG 和 SCHEMA。There is no standard INFORMATION_SCHEMA table for indexes. The MySQL column list is similar to what SQL Server 2000 returns for sp_statistics, except that QUALIFIER and OWNER are replaced with CATALOG and SCHEMA, respectively.

有关表索引的信息也可以通过 SHOW INDEX 语句获取。参见第 15.7.7.23 节，“SHOW INDEX 语句”。以下语句是等效的：Information about table indexes is also available from the SHOW INDEX statement. See Section 15.7.7.23, “SHOW INDEX Statement”. The following statements are equivalent:

SELECT * FROM INFORMATION_SCHEMA.STATISTICS
  WHERE table_name = 'tbl_name'
  AND table_schema = 'db_name'

SHOW INDEX
  FROM tbl_name
  FROM db_name

关于生成的不可见主键列的信息默认在此表中可见。您可以通过设置 show_gipk_in_create_table_and_information_schema = OFF 来使这些信息隐藏。有关更多信息，请参见第 15.1.20.11 节，“生成的不可见主键”。Information about generated invisible primary key columns is visible in this table by default. You can cause such information to be hidden by setting show_gipk_in_create_table_and_information_schema = OFF. For more information, see Section 15.1.20.11, “Generated Invisible Primary Keys”.

首页上一级下一节