backup - show table status 会显示表中的确切行吗？

Question

我在数据库中有一张表，它的空间大约为 90 GB。我试图计算表中的行数 select count(idNewsNotification) from notification结果是

4982397

1 row in set (59 min 48.35 sec)

但是当我使用 show table status like <table_name> 查询时它表明

 Engine: InnoDB
 Version: 10
 Row_format: Compact
 Rows: 8631267
 Avg_row_length: 11237
 Data_length: 96996425728
 Max_data_length: 0
 Index_length: 175374336
 Data_free: 0
 Auto_increment: NULL
 .....

知道表中记录数的完美程序是什么？

Answer 1

您的计数花费了一个小时的事实意味着您正在运行 InnoDB(它不会缓存此数据)。

因此，表格状态是基于其他因素的近似值，不可信。

count(*) 会很准确，但等待起来很痛苦。

来自 the doc :

Some storage engines, such as MyISAM, store the exact count. For other storage engines, such as InnoDB, this value is an approximation, and may vary from the actual value by as much as 40 to 50%. In such cases, use SELECT COUNT(*) to obtain an accurate count.