【mysql】通过information_schema.tables查询表的统计信息

1 查询表的统计信息

information_schema.tables 是 MySQL 中的一个系统视图，包含数据库中所有表的信息。

如何查询当前数据库的所有表信息：

SELECT * FROM information_schema.tables WHERE table_schema = DATABASE();

返回的字段有：

字段名	含义
TABLE_CATALOG	表所属的目录名，通常为 'def'（默认）
TABLE_SCHEMA	表所属的数据库名
TABLE_NAME	表名
TABLE_TYPE	表类型：'BASE TABLE'（普通表）或 'VIEW'（视图）
ENGINE	存储引擎（如 InnoDB、MyISAM）
VERSION	版本信息（通常为10）
ROW_FORMAT	行格式（Compact、Dynamic等）
TABLE_ROWS	行数（InnoDB为估算值，不精确）
AVG_ROW_LENGTH	平均行长度（字节）
DATA_LENGTH	数据总长度（字节）
MAX_DATA_LENGTH	最大数据长度（字节）
INDEX_LENGTH	索引总长度（字节）
DATA_FREE	已分配但未使用的空间（字节）
AUTO_INCREMENT	下一个AUTO_INCREMENT值（可能为NULL）
CREATE_TIME	表创建时间
UPDATE_TIME	表最后更新时间（对InnoDB可能为NULL）
CHECK_TIME	最后检查时间（对MyISAM）
TABLE_COLLATION	表的默认字符集和排序规则（如utf8mb4_unicode_ci）
CHECKSUM	校验和值（如果启用）
CREATE_OPTIONS	创建表时的额外选项
TABLE_COMMENT	表注释

那么我们就可以获取我们感兴趣的表信息，比如：

-- 查询 table1 表的行数、数据长度、创建时间
SELECT
    table_name,
    table_rows,
    (data_length+index_length)/1024/1024 AS sizeMB,
    create_time
FROM information_schema.tables
WHERE
    table_schema = DATABASE()
    AND table_name='table1';

2 InnoDB 的自动统计机制

（1）非实时性

你会发现，当你向表中插入一批数据后，再查询 information_schema.tables 信息，其行数、数据长度等值并没有改变。

这是由于 MySQL 的统计信息并不是实时的，InnoDB 存储引擎为了提高性能、减少对数据库操作的影响，采用了异步统计信息收集机制，我们查询到的信息并不能表示表当前的实时状态。

统计信息的刷新时机：

• 第一次打开一个表时
• 当执行某些 DDL 操作时（如创建索引、重建表等）
• 当超过一定时间或数据变化达到一定比例时（由参数控制）

（2）非精确性

同时， InnoDB 采用采样的方式来估计这些统计数据，而不是每次都进行精确计算，所以统计信息本身存在一定的误差。

可以查看统计信息更新的相关参数：

SHOW VARIABLES LIKE 'innodb_stats%';

关键参数：

• innodb_stats_auto_recalc：是否启用自动重新计算（默认 ON）
• innodb_stats_persistent：是否持久化统计信息（默认 ON，8.0+）
• innodb_stats_persistent_sample_pages：采样页数（默认 20）， 增加此值可以提高统计信息的准确性，但也会增加计算成本
• innodb_stats_transient_sample_pages：非持久化统计的采样页数
• innodb_stats_on_metadata：在 SHOW TABLE STATUS、SHOW INDEX、查询 information_schema.tables 等元数据时，是否触发统计信息的更新（默认OFF）

（3）如何获取更接近实时的统计信息

可以通过执行 ANALYZE TABLE 命令来强制刷新统计信息。不过需要注意，这可能会对正在运行的查询产生影响（尤其对大型表），因此在生产环境谨慎使用。Is ANALYZE TABLE Safe on a Busy MySQL Database Server?

ANALYZE TABLE table1;