MySQL索引不生效的解決辦法

公司服務用的mysql,最近在查詢時時間很慢,經常會上10多秒,查看了一下查詢的執行計劃,發現索引沒有生效。

存儲引擎使用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)

?

? 版權聲明
THE END
喜歡就支持一下吧
點贊9 分享