mysql 8 MyISAM "SHOW TABLE STATUS"行计数

Question

我最近从 mySQL 5.6.34 -> 8.0.16(在 macOS 10.14.5 上)升级，我注意到从“SHOW TABLE STATUS”返回的行计数以及“information_schema”表。考虑这个简单的模式:

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `test` (`id`, `name`) VALUES
(1, 'one'),
(2, 'two'),
(3, 'three'),
(4, 'four'),
(5, 'five');

当我运行以下查询时，我看到了预期的输出:

SELECT * FROM test;
+----+-------+
| id | name  |
+----+-------+
|  1 | one   |
|  2 | two   |
|  3 | three |
|  4 | four  |
|  5 | five  |
+----+-------+

同样，当我运行以下查询时，我会看到预期的输出:

SELECT COUNT(*) FROM test;
+----------+
| COUNT(*) |
+----------+
|        5 |
+----------+

但是当我运行以下查询时:

    SHOW TABLE STATUS \G
*************************** 1. row ***************************
           Name: test
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 281474976710655
   Index_length: 1024
      Data_free: 0
 Auto_increment: 1
    Create_time: 2019-05-30 13:56:46
    Update_time: 2019-05-30 16:02:24
     Check_time: NULL
      Collation: utf8_unicode_ci
       Checksum: NULL
 Create_options: 
        Comment: 

似乎没有行(尽管有 5 行)。同样，当我运行时，我看到相同的结果:

SELECT table_name, table_rows FROM information_schema.tables WHERE table_schema = 'test';
    +------------+------------+
    | TABLE_NAME | TABLE_ROWS |
    +------------+------------+
    | test       |          0 |
    +------------+------------+

没有行？如果我向表中添加/删除行，计数不会改变。仅在我运行之后:

ANALYZE TABLE `test`

...我是否看到所有行计数都是正确的。我只在 mySQL 8 上看到这一点。在 mySQL 5 上一切都按预期工作。我知道使用 InnoDB 表进行精确行计数存在问题，但这些都是 MyISAM 表，它应该始终显示正确的行数。任何帮助表示赞赏。谢谢。

Answer 1

信息模式表发生了重大变化，incompatible changes in MySQL 8随着 global data dictionary 的引入:

Previously, INFORMATION_SCHEMA queries for table statistics in the STATISTICS and TABLES tables retrieved statistics directly from storage engines. As of MySQL 8.0, cached table statistics are used by default.

缓存由系统变量information_schema_stats_expiry控制:

Some INFORMATION_SCHEMA tables contain columns that provide table statistics:

[...] TABLES.TABLE_ROWS [...]

Those columns represent dynamic table metadata; that is, information that changes as table contents change.

By default, MySQL retrieves cached values for those columns from the mysql.index_stats and mysql.table_stats dictionary tables when the columns are queried, which is more efficient than retrieving statistics directly from the storage engine. If cached statistics are not available or have expired, MySQL retrieves the latest statistics from the storage engine and caches them in the mysql.index_stats and mysql.table_stats dictionary tables. Subsequent queries retrieve the cached statistics until the cached statistics expire.

[...]

To update cached values at any time for a given table, use ANALYZE TABLE.

To always retrieve the latest statistics directly from the storage engine and bypass cached values, set information_schema_stats_expiry to 0.

这与您的行为一致。

您可以在全局范围内将 information_schema_stats_expiry 设置为 0，或者在需要准确统计信息时按 session 设置。