Simple analysis of cardinality anomaly in Mysql _mysql

Some time ago, early in the morning, the alarm was received, warning PHP-FPM the number of processes exceeded the threshold. The final discovery was that a SQL was not used to the index, resulting in slow execution of the database query, which eventually led to an increase in the number of php-fpm processes. The cardinality is eventually updated with the Analyze Table feed_comment_info_id_0000 command before the index can be used again.
The troubleshooting process is as follows:
SQL statement:

Select ID from feed_comment_info_id_0000 where obj_id=101 and type=1;

Index Information:

Show index from feed_comment_info_id_0000 +---------------------------+------------+----------+--------------+---- ---------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type |
Comment | +---------------------------+------------+----------+--------------+-------------+-----------+-------------+--- -------+--------+------+------------+---------+
| feed_comment_info_id_0000 | 0 | PRIMARY | 1 | ID | A | 6216 | NULL |   NULL | | 
Btree | | | feed_comment_info_id_0000 | 1 | Obj_type | 1 | obj_id | A | 6216 | NULL |   NULL | | 
Btree | | | feed_comment_info_id_0000 | 1 | Obj_type | 2 | Type | A | 6216 | NULL | NULL | YES | 
Btree | | | feed_comment_info_id_0000 | 1 | user_id | 1 | user_id | A | 6216 | NULL |   NULL | | 
Btree | | +---------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 5 rows in Set (0.00 sec)

 

When viewed through Explian, SQL is found to be using primary key primary instead of Obj_type index. Look at the cardinality value of the index through show index, which is twice times the actual data. The cardinality value is not normal, so the value is calculated from the Analyzea Table command. After the command has been executed, you can use the index.

Cardinality explanation
explanation of official documents:
An estimate of the number of unique values in the index. This is updated by running ANALYZE TABLE or myisamchk-a. Cardinality's counted based on statistics stored as integers, s o The value is not necessarily exact even for small tables. The higher the cardinality, the greater the chance that MySQL uses the index when doing
To sum up:
1, it represents an estimate of the number of unique values in the index. If it is the MyISAM engine, this value is an accurate value. If it is the InnoDB engine, this value is an estimated value and may be different each time the show index is executed.
2, the index is created (except primary key), MyISAM table cardinality value of Null,innodb table cardinality value is approximately the number of rows;
3, the size of the value will affect the selection of the index
4, when the index is created, the value of the MyISAM table cardinality Null,innodb table cardinality is approximately the number of rows.
5, can update a table or MYSQLCHECK-AA through analyze table to update the entire database
6, you can view the value by show index