This is the 8th day of my participation in the August More Text Challenge. For details, see:August is more challenging

How count(*) is implemented

  1. MyISAM – the total number of rows in the table exists on disk – return this number directly – efficient
  2. InnoDB – data is read from the engine row by row – accumulates counts

Why doesn’t InnoDB save numbers as well?

Because of multi-version concurrency control (MVCC) – InnoDB table “how many rows should be returned” is also not sure InnoDB transaction involves – default isolation level is repeatable read – MVCC implementation – each row of records to determine whether it is visible to the session – read data row by row to judge, Only visible rows can be used to calculate the total number of rows in a table “based on this query.”

InnoDB – Index organization table – Primary key index tree leaves are data, The leaves of a normal index tree are primary keys – a normal index tree is much smaller than the primary key index tree – count(*) the result of traversing the index tree is the same logically – the MySQL optimizer will find the smallest tree to traverse – as long as the logic is correct, Minimize the amount of data scanned – one of the general principles involved in database systems

The TABLE_ROWS in the show table status command output is used to show how many rows the table currently has. The value of the index statistics is estimated by sampling. – TABLE_ROWS is estimated by sampling

summary

  1. MyISAM table count(*) is fast – does not support transactions;
  2. The show table status command returns quickly – inaccurate;
  3. Count (*) traverses the entire table between InnoDB tables – results are accurate but cause performance problems.

Count yourself – find a place to store the number of rows in the operation record sheet

  1. Keep the count in the cache system

The frequently updated library cache system supports the Redis service to hold the total number of rows in the table. The Redis count increases by one every time a row is inserted into the table. Redis count decreases by 1 every time a row is deleted – read and update operations are fast – Cache system may lose updated Redis data will not be permanently stored in memory – Find a place to persist values periodically – updates may still be lost Redis restarted because of an exception] – run a separate count(*) in the database to get the true count – write the value back to Redis

The way counts are stored in the cache system is not just a matter of missing updates. Even if Redis is working properly, this value is logically inaccurate

  1. Save the count in the database

Putting this count directly into a separate count table C in the database solves the crash problem. InnoDB supports crash recovery without losing data

Different uses of count

  1. Return the total number of rows in the result set that satisfy the condition – count(*), count(primary id), count(1)

  2. Return the total number of rows in which this field is not NULL.