count optimization problem sharing about innodb in mysql

  • 2020-05-15 02:18:25
  • OfStack

1. Use level 2 index to count:
For example, id is pk aid is secondary index

using


select count(*) from table where id >=0;
 or 
select count(*) from table;

The effect is similar to 1, both using pk indexes by default, and all requiring full table scans. Although the performance of the first option may be higher, there is no significant difference.

But if you use secondary, index


select count(*) from table where aid>=0;

It's a lot faster.

Why is the secondary index scan faster than the primary key scan? This requires knowing the difference between clustered index and secondary index for innodb.

clustered index of innodb stores primary key and row data in one place, while secondary index stores secondary index separately, and then there is a pointer to primary key.
Therefore, it is necessary to perform count(*) statistical tables to record the total amount when scanning up using secondary index, which is obviously faster.
While primary key is mainly used to scan the index and return the result records.


Related articles: