将显示表状态显示表中的确切行吗? [英] will show table status display exact rows in the table?

问题描述

我在DB中有一张表，大约有90 GB的空间.我试图计数表中的行数 select count(idNewsNotification) from notification及其结果

I have a table in DB and it is approx 90 GB in space. I tried to count no of rows in the table select count(idNewsNotification) from notification and it resulted

4982397

1 row in set (59 min 48.35 sec)

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

But when I queried using show table status like <table_name> it is showing that

 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
 .....

知道表中记录数的最佳过程是什么?

What is the perfect procedure for knowing the no of records in the table ?

推荐答案

计数需要一个小时的事实意味着您正在运行InnoDB(该数据库不缓存此数据).

The fact that your count took an hour implies that you're running InnoDB (which doesn't cache this data).

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

As such, the table status is an approximation based on other factors, and is not to be trusted.

count(*)会准确，但是要等待很痛苦.

The count(*) would be accurate, but a pain to wait for.

来自文档:

某些存储引擎(例如MyISAM)存储准确的计数.对于其他 存储引擎(例如InnoDB)，该值是一个近似值，并且 可能与实际值相差40％到50％.在这种情况下， 使用SELECT COUNT(*)获得准确的计数.

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.

这篇关于将显示表状态显示表中的确切行吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！