mysql - mysql分析表导致奇怪的行为

问题描述

我注意到我在运行时得到的表格总数：

SELECT TABLE_NAME, TABLE_ROWS FROM `information_schema`.`tables` WHERE `table_schema` = 'my_database';

导致总数不正确。我还读到这样做：

analyze table 'my_table';

将正确更新信息。但似乎情况并非如此。因此，要设置基线：

  mysql> select count(*) from my_table;
  +----------+
  | count(*) |
  +----------+
  |    61782 |
  +----------+
  1 row in set (0.01 sec)

接下来，统计数据显示：

  mysql> SELECT TABLE_NAME, TABLE_ROWS FROM `information_schema`.`tables` WHERE `table_schema` = 'my_database' AND TABLE_NAME = 'my_table';
  +--------------------+------------+
  | TABLE_NAME         | TABLE_ROWS |
  +--------------------+------------+
  | my_table           |      58378 |
  +--------------------+------------+
  1 row in set (0.00 sec)

显然，这是不对的，所以我再次执行分析并检查：

  mysql> analyze table my_table;
  +------------------------------+---------+----------+----------+
  | Table                        | Op      | Msg_type | Msg_text |
  +------------------------------+---------+----------+----------+
  | my_database.my_table         | analyze | status   | OK       |
  +------------------------------+---------+----------+----------+
  1 row in set (0.02 sec)

  mysql> SELECT TABLE_NAME, TABLE_ROWS FROM `information_schema`.`tables` WHERE `table_schema` = 'my_database' AND TABLE_NAME = 'my_table';
  +--------------------+------------+
  | TABLE_NAME         | TABLE_ROWS |
  +--------------------+------------+
  | my_table           |      56439 |
  +--------------------+------------+
  1 row in set (0.00 sec)

好吧，它改变了，但它仍然是错误的。所以我又做了几次：

  mysql> analyze table my_table;
  +------------------------------+---------+----------+----------+
  | Table                        | Op      | Msg_type | Msg_text |
  +------------------------------+---------+----------+----------+
  | my_database.my_table         | analyze | status   | OK       |
  +------------------------------+---------+----------+----------+
  1 row in set (0.02 sec)

  mysql> SELECT TABLE_NAME, TABLE_ROWS FROM `information_schema`.`tables` WHERE `table_schema` = 'my_database' AND TABLE_NAME = 'my_table';
  +--------------------+------------+
  | TABLE_NAME         | TABLE_ROWS |
  +--------------------+------------+
  | my_table           |      58766 |
  +--------------------+------------+
  1 row in set (0.00 sec)

  mysql> analyze table my_table;
  +------------------------------+---------+----------+----------+
  | Table                        | Op      | Msg_type | Msg_text |
  +------------------------------+---------+----------+----------+
  | my_database.my_table         | analyze | status   | OK       |
  +------------------------------+---------+----------+----------+
  1 row in set (0.02 sec)

  mysql> SELECT TABLE_NAME, TABLE_ROWS FROM `information_schema`.`tables` WHERE `table_schema` = 'my_database' AND TABLE_NAME = 'my_table';
  +--------------------+------------+
  | TABLE_NAME         | TABLE_ROWS |
  +--------------------+------------+
  | my_table           |      65749 |
  +--------------------+------------+
  1 row in set (0.00 sec)

  mysql> analyze table my_table;
  +------------------------------+---------+----------+----------+
  | Table                        | Op      | Msg_type | Msg_text |
  +------------------------------+---------+----------+----------+
  | my_database.my_table         | analyze | status   | OK       |
  +------------------------------+---------+----------+----------+
  1 row in set (0.02 sec)

  mysql> SELECT TABLE_NAME, TABLE_ROWS FROM `information_schema`.`tables` WHERE `table_schema` = 'my_database' AND TABLE_NAME = 'my_table';
  +--------------------+------------+
  | TABLE_NAME         | TABLE_ROWS |
  +--------------------+------------+
  | my_table           |      61870 |
  +--------------------+------------+
  1 row in set (0.00 sec)

而且，如您所见，我从来没有得到相同的数字，而且它们都不是实际的行数。我的问题是：这是否表明我的数据库有问题（它在所有表上都存在）或者这只是 MySQL 中的一个错误？

我的环境的详细信息：

服务器版本：5.6.43 MySQL Community Server (GPL) 所有表都是INNODB，字符集是utf8

标签： mysql

mysql - mysql分析表导致奇怪的行为

问题描述

解决方案

推荐阅读