时间:2021-07-01 10:21:17 帮助过:18人阅读
存储引擎使用InnoDB。
一开始在主库查询,一直很好奇为什么索引不生效,切换到备库之后,发现备库是有效的。
开始考虑是不是因为索引出问题,后对索引重建,发现效率高了不少。
简单记录一下对比。
- 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 |
- +----+-------------+---------+-------+---------------+------+---------+------+----------+-------------+
- row in set (0.00 sec)
上面是主库的执行计划。
对比一下备库的执行计划。
- 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 |
- +----+-------------+---------+-------+---------------+----------+---------+------+------+-------------+
- row in set (0.00 sec)
可以看出,备库在查询时适应到索引 status_2。
执行如下的命令之后,问题解决。
- 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 |
- +------------------+----------+----------+-------------------------------------------------------------------+
- rows in set (47.13 sec)
第二天来看,查询再一次变慢,有点好奇是不是有新数据写入导致索引不更新。
以上就是mysql索引不生效的解决方法的详细内容,更多请关注Gxl网其它相关文章!