Simple parsing of cardinality exceptions in MySQL

  • 2020-10-07 18:54:02
  • OfStack

Some time ago, I received an alarm at 1 a.m. warning that the number of ES1en-ES2en processes exceeded the threshold. Finally, it was found that one sql did not use the index, which led to the slow execution of database query, and finally resulted in the increase of the number of ES4en-ES5en processes. Cardinality was finally updated with the analyze table feed_comment_info_id_0000 command before the index could be used again.
The screening process is as follows:
sql statements:


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 from explian, it is found that sql USES the primary key PRIMARY instead of the obj_type index. Looking at the Cardinality value of the index through show index, it is found that this value is twice the actual data. It was felt that the Cardinality value was no longer normal, so the analyzea table command was used to re-calculate the value. Once the command is executed, the index is available.

Cardinality explain
Explanation of the official document:
An estimate of the number of unique values in the index. This is updated by running ANALYZE TABLE or myisamchk -a. Cardinality is counted based on statistics stored as integers, so 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
Conclusion 1:
1. It represents an estimate of the number of unique values in the index. If it is an myisam engine, this value is an exact value. If it is an innodb engine, this value is an estimated value that may change each time show index is executed
2. When Index is created (except primary key), the value of Cardinality for MyISAM is null, and the value of Cardinality for InnoDB is about the number of rows;
3. The size of the value will affect the selection of the index
4. When Index is created, the value of TABLE Cardinality of MyISAM is null, and the value of table Cardinality of InnoDB is about the number of rows.
5. One table can be updated via Analyze table or ES65en-ES66en to update the whole database
6. You can check the value of show index


Related articles: