.jpg)
公司服務(wù)用的mysql，最近在查詢時(shí)時(shí)間很慢，經(jīng)常會(huì)上10多秒，查看了一下查詢的執(zhí)行計(jì)劃，發(fā)現(xiàn)索引沒有生效。
存儲(chǔ)引擎使用InnoDB。
?一開始在主庫查詢，一直很好奇為什么索引不生效，切換到備庫之后，發(fā)現(xiàn)備庫是有效的。
開始考慮是不是因?yàn)樗饕鰡栴}，后對(duì)索引重建，發(fā)現(xiàn)效率高了不少。
?簡單記錄一下對(duì)比。
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)
上面是主庫的執(zhí)行計(jì)劃。
?對(duì)比一下備庫的執(zhí)行計(jì)劃。
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)
?可以看出，備庫在查詢時(shí)適應(yīng)到索引 status_2。
?執(zhí)行如下的命令之后，問題解決。
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)
第二天來看，查詢?cè)僖淮巫兟悬c(diǎn)好奇是不是有新數(shù)據(jù)寫入導(dǎo)致索引不更新。?
? 版權(quán)聲明
文章版權(quán)歸作者所有，未經(jīng)允許請(qǐng)勿轉(zhuǎn)載。
THE END
.png)
