MySQL InnoDB获得有效的行数

我有一个很大的数据库表。 我需要确切知道该表在很短的时间内包含多少行。 用户将在我网站的每个页面上每次都请求该号码。 这不是一个罕见的要求。

我有以下方法:

  1. 使用主键,因此使用MAX(id),我已经有了行数。但这是O(n)。在我的表上,> 1mio的行速度很慢。
  2. 每当插入项目时,我都可以在文本文件中增加一个数字
  3. 我可以切换到MyISAM,因为它可以保存行数COUNT,但是问题是选择速度较慢。在我的应用中,我主要选择大量金额。
  4. SELECT COUNT(*)FROM table / SELECT * FROM table ORDER BY id DESC LIMIT 1;
  5. 但是对于非常大的数据库表,它们非常缓慢或混乱。

我应该使用哪种方法,或者有更好的方法?

评论
  • 深夜、徘徊
    深夜、徘徊 回复

    If you need perfect accuracy, select count(id) from table or select count(1) from table is the only way. And I'd recommend against switching to MyISAM.

    If you can stand an approximation, table_rows from information_schema.tables. I find this sufficient for UI purposes such as "Showing 40 of 10393".

    某些存储引擎(例如MyISAM)存储准确的计数。对于其他存储引擎,例如InnoDB,此值是一个近似值,可能与实际值相差40%至50%。在这种情况下,请使用SELECT COUNT(*)获得准确的计数。
    select table_rows
    from information_schema.tables
    where tables.table_schema = 'your database name'
      and tables.table_type = 'your table';
    

    Finally, you can cache the value from select count(id) and periodically update it. Whether this will be more accurate than information_schema.tables I can't say. You'll have to do some testing. Caching can greatly speed up count queries which also have a filter like select count(id) from table where this = 'that'.

    You might be tempted to use the auto_increment value from information_schema.tables. This can only provide you with an upper bound. It will always over-estimate the number of rows because...

    • Rows get deleted.
    • The auto_increment value can advance without inserting anything.
    • Manually inserting an id will move the auto_increment value to id + 1.

    For example, insert into select will do this, as will inserting and rolling back.

  • 禽小兽
    禽小兽 回复

    如果您从不删除表中的行,并且auto_increment PK和auto_increment增量/偏移量均为1,请执行以下操作:

    SHOW CREATE TABLE table_name;
    

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