GreatSQL社区

搜索

[待回复] 为什么SHOW TABLE STATUS 不更新

132 2 2024-2-28 18:20
本帖最后由 vatebur 于 2024-2-28 18:26 编辑

表数据1千万条,以下方法都试过了,information_schema.tables里的行数还是不对(7百万)
1.optimize table
2.ANALYZE TABLE
3.innodb_stats_on_metadata + SELECT count(*)
4. information_schema_stats_expiry

error_log里没看到相关的日志

  1. mysql> use ds4;

  2. Reading table information for completion of table and column names

  3. You can turn off this feature to get a quicker startup with -A



  4. Database changed

  5. mysql> SHOW VARIABLES LIKE 'information_schema_stats_expiry';

  6. +---------------------------------+-------+

  7. | Variable_name          | Value |

  8. +---------------------------------+-------+

  9. | information_schema_stats_expiry | 86400 |

  10. +---------------------------------+-------+

  11. 1 row in set (0.00 sec)



  12. mysql> set session information_schema_stats_expiry = 0;

  13. Query OK, 0 rows affected (0.00 sec)



  14. mysql>  show table status like 'ds_test_table'\G

  15. *************************** 1. row ***************************

  16. ​      Name: ds_test_table

  17. ​     Engine: InnoDB

  18. ​    Version: 10

  19.    Row_format: Dynamic

  20. ​      Rows: 6553584

  21. Avg_row_length: 9375

  22.   Data_length: 61444456448

  23. Max_data_length: 0

  24.   Index_length: 0

  25.    Data_free: 4194304

  26. Auto_increment: NULL

  27.   Create_time: 2024-02-28 04:28:13

  28.   Update_time: NULL

  29.    Check_time: NULL

  30.    Collation: utf8mb4_bin

  31. ​    Checksum: NULL

  32. Create_options:

  33. ​    Comment:

  34. 1 row in set (0.00 sec)



  35. mysql> SELECT count(*) FROM ds4.ds_test_table;

  36. +----------+

  37. | count(*) |

  38. +----------+

  39. | 10000000 |

  40. +----------+

  41. 1 row in set (2.11 sec)



  42. mysql>

  43. mysql> ANALYZE TABLE ds4.ds_test_table;

  44. +---------------------+---------+----------+----------+

  45. | Table        | Op    | Msg_type | Msg_text |

  46. +---------------------+---------+----------+----------+

  47. | ds4.ds_test_table | analyze | status  | OK    |

  48. +---------------------+---------+----------+----------+

  49. 1 row in set (0.01 sec)



  50. mysql>  show table status like 'ds_test_table'\G

  51. *************************** 1. row ***************************

  52. ​      Name: ds_test_table

  53. ​     Engine: InnoDB

  54. ​    Version: 10

  55.    Row_format: Dynamic

  56. ​      Rows: 6553584

  57. Avg_row_length: 9375

  58.   Data_length: 61444456448

  59. Max_data_length: 0

  60.   Index_length: 0

  61.    Data_free: 4194304

  62. Auto_increment: NULL

  63.   Create_time: 2024-02-28 04:28:13

  64.   Update_time: NULL

  65.    Check_time: NULL

  66.    Collation: utf8mb4_bin

  67. ​    Checksum: NULL

  68. Create_options:

  69. ​    Comment:

  70. 1 row in set (0.00 sec)



  71. mysql> select /* set_var(information_schema_stats_expiry = 1) */ * from information_schema.tables where table_schema='ds4' and table_name = 'ds_test_table'

  72.   -> ;

  73. +---------------+--------------+----------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+

  74. | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME   | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME     | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT |

  75. +---------------+--------------+----------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+

  76. | def      | ds4     | ds_test_table | BASE TABLE | InnoDB |    10 | Dynamic   |   6553584 |      9375 | 61444456448 |        0 |       0 |  4194304 |      NULL | 2024-02-28 04:28:13 | NULL     | NULL    | utf8mb4_bin   |   NULL |         |        |

  77. +---------------+--------------+----------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+

  78. 1 row in set (0.01 sec)



  79. mysql> SHOW VARIABLES LIKE 'information_schema_stats_expiry';

  80. +---------------------------------+-------+

  81. | Variable_name          | Value |

  82. +---------------------------------+-------+

  83. | information_schema_stats_expiry | 0   |

  84. +---------------------------------+-------+

  85. 1 row in set (0.00 sec)



  86. mysql>
复制代码



  1. CREATE TABLE `ds_test_table` (
  2.   `A0` bigint NOT NULL,
  3.   `A1` double DEFAULT NULL,
  4.   `A2` decimal(40,0) DEFAULT NULL,
  5.   `A3` double DEFAULT NULL,
  6.   `A4` decimal(5,2) DEFAULT NULL,
  7.   `A5` smallint DEFAULT NULL,
  8.   `A6` int DEFAULT NULL,
  9.   `A7` bigint DEFAULT NULL,
  10.   `A8` decimal(19,0) DEFAULT NULL,
  11.   `A9` decimal(38,0) DEFAULT NULL,
  12.   `A10` decimal(40,0) DEFAULT NULL,
  13.   `A11` datetime DEFAULT NULL,
  14.   `A12` datetime(6) DEFAULT NULL,
  15.   `A13` datetime DEFAULT NULL,
  16.   `A14` datetime(3) DEFAULT NULL,
  17.   `A15` datetime(6) DEFAULT NULL,
  18.   `A17` char(1) COLLATE utf8mb4_bin DEFAULT NULL,
  19.   `A18` char(1) COLLATE utf8mb4_bin DEFAULT NULL,
  20.   `A19` char(255) COLLATE utf8mb4_bin DEFAULT NULL,
  21.   `A20` varchar(1000) COLLATE utf8mb4_bin DEFAULT NULL,
  22.   `A21` varchar(1) COLLATE utf8mb4_bin DEFAULT NULL,
  23.   `A22` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
  24.   `A23` varchar(100) COLLATE utf8mb4_bin DEFAULT NULL,
  25.   `A24` varchar(200) COLLATE utf8mb4_bin DEFAULT NULL,
  26.   `A25` varchar(300) COLLATE utf8mb4_bin DEFAULT NULL,
  27.   `A26` varchar(400) COLLATE utf8mb4_bin DEFAULT NULL,
  28.   `A27` varchar(500) COLLATE utf8mb4_bin DEFAULT NULL,
  29.   `A28` varchar(600) COLLATE utf8mb4_bin DEFAULT NULL,
  30.   `A29` varchar(1000) COLLATE utf8mb4_bin DEFAULT NULL,
  31.   PRIMARY KEY (`A0`)
  32. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
复制代码



全部回复(2)
KAiTO 2024-2-28 18:50:25
INFORMATION_SCHEMA.TABLES表中的TABLE_ROWS本来就是不准确的,与实际值相差可达 40 到 50%
yejr 2024-3-4 09:16:23
问题分析过程已整理成文章:为什么SHOW TABLE STATUS显示Rows少了40% https://mp.weixin.qq.com/s/QMZ7O0gfs81zXo69F1bHdQ
vatebur

4

主题

5

博客

62

贡献

注册会员

Rank: 2

积分
80

助人为乐(铜)

合作电话:010-64087828

社区邮箱:greatsql@greatdb.com

社区公众号
社区小助手
QQ群
GMT+8, 2024-4-22 14:30 , Processed in 0.015916 second(s), 9 queries , Redis On.
快速回复 返回顶部 返回列表