我有一个很大的数据库表。 我需要确切知道该表在很短的时间内包含多少行。 用户将在我网站的每个页面上每次都请求该号码。 这不是一个罕见的要求。
我有以下方法：
- 使用主键，因此使用MAX（id），我已经有了行数。但这是O（n）。在我的表上，> 1mio的行速度很慢。
- 每当插入项目时，我都可以在文本文件中增加一个数字
- 我可以切换到MyISAM，因为它可以保存行数COUNT，但是问题是选择速度较慢。在我的应用中，我主要选择大量金额。
- SELECT COUNT（*）FROM table / SELECT * FROM table ORDER BY id DESC LIMIT 1;
- 但是对于非常大的数据库表，它们非常缓慢或混乱。
我应该使用哪种方法，或者有更好的方法？
If you need perfect accuracy,
select count(id) from tableorselect count(1) from tableis the only way. And I'd recommend against switching to MyISAM.If you can stand an approximation,
table_rowsfrominformation_schema.tables. I find this sufficient for UI purposes such as "Showing 40 of 10393".Finally, you can cache the value from
select count(id)and periodically update it. Whether this will be more accurate thaninformation_schema.tablesI can't say. You'll have to do some testing. Caching can greatly speed upcountqueries which also have a filter likeselect count(id) from table where this = 'that'.You might be tempted to use the
auto_incrementvalue frominformation_schema.tables. This can only provide you with an upper bound. It will always over-estimate the number of rows because...idwill move the auto_increment value toid+ 1.For example,
insert into selectwill do this, as will inserting and rolling back.如果您从不删除表中的行，并且auto_increment PK和auto_increment增量/偏移量均为1，请执行以下操作：
and parse the auto_increment value out of that. Every other way will be either slow (count()/max()) or an approximation (
SHOW INDEX FROM table_name;or from (information_schema.TABLES).Caveat: The
SHOW CREATE TABLEwill not be accurate if you delete rows from the table or have INSERT+ROLLBACK or experience deadlocks against INSERTs (that will allocate auto_increment values but not add actual records.