The mysql index does not take effect.

The mysql index does not take effect.

Mysql used by the company's services has been slow recently during query. It often takes more than 10 seconds to check the query execution plan and find that the index does not take effect.

The storage engine uses InnoDB.

 

At the beginning, I was wondering why the index did not take effect in the master database. After switching to the slave database, I found that the slave database was valid.

I started to consider whether it was because of index problems. After re-indexing, I found that the efficiency was much higher.

 

Simple record comparison.

mysql> explain select * from runinfo where status in (0, 2, 1, 3, 4, 7, 9, 10);+----+-------------+---------+-------+---------------+------+---------+------+----------+-------------+| id | select_type | table   | type  | possible_keys | key  | key_len | ref  | rows     | Extra       |+----+-------------+---------+-------+---------------+------+---------+------+----------+-------------+|  1 | SIMPLE      | runinfo | All   | status_2      | NULL | NULL    | NULL |  2378055 | Using where |+----+-------------+---------+-------+---------------+------+---------+------+----------+-------------+1 row in set (0.00 sec)

The above is the execution plan of the master database.

 

Compare the execution plan of the slave database.

mysql> explain select * from runinfo where status in (0, 2, 1, 3, 4, 7, 9, 10);+----+-------------+---------+-------+---------------+----------+---------+------+------+-------------+| id | select_type | table   | type  | possible_keys | key      | key_len | ref  | rows | Extra       |+----+-------------+---------+-------+---------------+----------+---------+------+------+-------------+|  1 | SIMPLE      | runinfo | range | status_2      | status_2 | 4       | NULL |  116 | Using where |+----+-------------+---------+-------+---------------+----------+---------+------+------+-------------+1 row in set (0.00 sec)

 

It can be seen that the standby database adapts to the index status_2 during query.

 

Run the following command to solve the problem.

mysql> OPTIMIZE TABLE runinfo;+------------------+----------+----------+-------------------------------------------------------------------+| Table            | Op       | Msg_type | Msg_text                                                          |+------------------+----------+----------+-------------------------------------------------------------------+| schedule.runinfo | optimize | note     | Table does not support optimize, doing recreate + analyze instead || schedule.runinfo | optimize | status   | OK                                                                |+------------------+----------+----------+-------------------------------------------------------------------+2 rows in set (47.13 sec)

The next day, we can see that the query slows down again, and we are a little curious about whether the index is not updated due to new data writes.