mysql索引不奏效

发布时间:2019-05-13  栏目:sqlite  评论:0 Comments

商家庭服务务用的mysql,近些日子在询问时时间比非常慢,平日会上10多秒,查看了壹晃查询的实行安插,开掘索引未有奏效。

mysql索引不奏效,mysql索引

店四服务用的mysql,方今在询问时时间非常的慢,平日会上十多秒,查看了1晃查询的执行安顿,开掘索引未有奏效。

存款和储蓄引擎使用InnoDB。

 

1起头在主库查询,一直很好奇为啥索引不奏效,切换成备库之后,开采备库是实用的。

起来思虑是否因为索引出问题,后对索引重建,开采作用高了累累。

 

简轻松单记录一下对照。

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)

上边是主库的实施布置。

 

比较之下一下备库的实践布署。

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)

 

能够看看,备库在查询时适应到索引 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                                                                |
+------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (47.13 sec)

其次天来看,查询再3遍变慢,有一些好奇是否有新数据写入导致索引不立异。 

http://www.bkjia.com/Mysql/1226559.htmlwww.bkjia.comtruehttp://www.bkjia.com/Mysql/1226559.htmlTechArticlemysql索引不生效,mysql索引
集团劳动用的mysql,近来在询问时时间非常的慢,日常会上十多秒,查看了弹指间查询的实施安顿,发掘索引未有一蹴而就。…

累积引擎使用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 |
+----+-------------+---------+-------+---------------+------+---------+------+----------+-------------+
1 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 |
+----+-------------+---------+-------+---------------+----------+---------+------+------+-------------+
1 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                                                                |
+------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (47.13 sec)

第一天来看,查询再1遍变慢,有一点好奇是还是不是有新数据写入导致索引不更新。 

留下评论

网站地图xml地图