The table in the article probably contains hundreds to tens of millions of data.
There are two columns in the table: ID, name
There are three indexes: primary key index, name, and ID
Count (*) does not go to the primary key index but the normal (secondary) index, why?
EXPLAIN select count(id) from Student
Select id, but go to secondary index instead of overwrite index
A secondary index tree is much smaller than a primary key index tree because the leaf nodes of a primary key index tree are data and the leaf nodes of a secondary index tree are primary keys. For the count(*) operation, the optimizer will find the smallest tree to traverse, so when you have a secondary index, it will be faster
You can view the index length using the following SQL:
select
database_name,
table_name,
index_name,
round((stat_value*@@innodb_page_size)/1024/1024.2) SizeMB,
round(((100/(SELECT INDEX_LENGTH FROM INFORMATION_SCHEMA.TABLES t WHERE t.TABLE_NAME = iis.table_name and t.TABLE_SCHEMA = iis.database_name))*(stat_value*@@innodb_page_size)), 2) `Percentage`
from mysql.innodb_index_stats iis
where stat_name='size'
and table_name = 'your_table'
and database_name = 'your_database_name'
Copy the code
Perform the above SQL query on a simple table with 100 million rows as follows:
The query results are as follows:
select count(*) from Student FORCE INDEX(PRIMARY)
The query duration is 174.490 seconds
select count(*) from Student FORCE INDEX(idx_name)
The query time is 171.265 seconds
select count(*) from Student FORCE INDEX(idx_id)
The query duration is about 37 seconds
After reading the mySQL brochure, I think the reasons are as follows: The cluster index in general on the leaf node, a page will contain more records, so that the whole B + tree size will be smaller, certain conditions in the query to check the cluster index, the analysis of the MySQL optimizing process will think the cluster index will be faster, so as to select the cluster index This blog also provides certain ideas, I think write very good: Juejin. Cn/post / 684490…