公司服務用的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
