近期收到服務器報警环戈，發(fā)現(xiàn)有條sql在執(zhí)行的時候，耗時很長竟然能有30s多。
數(shù)據(jù)庫版本5.6.28 千康。sql 如下：
-- focus news 庫
SELECT*FROM news_basic WHERE id !=18762279 AND
(publish_time< '2017-06-02 09:31:25' OR (publish_time='2017-06-02 09:31:25' AND id< 18762279))
AND STATUS IN (1,7) AND source IN (11,12,13) AND city_id=380
AND category IN (43,52,1201,1202,1203,1204) ORDER BY publish_time DESC,id DESC LIMIT 1)
UNION ALL (
SELECT*FROM news_basic WHERE id !=18762279 AND
(publish_time> '2017-06-02 09:31:25' OR (publish_time='2017-06-02 09:31:25' AND id> 18762279))
AND STATUS IN (1,7) AND source IN (11,12,13) AND city_id=380
AND category IN (43,52,1201,1202,1203,1204) ORDER BY publish_time ASC,id ASC LIMIT 1);
這條sql 功能是找到id=18762279的文章的相鄰的上下篇文章各一篇悴务。其中2017-06-02 09:31:25是id=18762279文章的發(fā)布時間睹限。
這條sql看起來很復雜，怕出現(xiàn)慢查詢所以explain看了下執(zhí)行計劃讯檐。
如圖可見羡疗，索引走的是城市+類別(index_city_id_category)的聯(lián)合索引。
那根據(jù)索引坐下group by裂垦，看下每個小類別有多少條數(shù)據(jù)顺囊。
SELECT city_id ,count(*) from news_basic WHERE category IN (43,52,1201,1202,1203,1204) GROUP BY city_id
可以看出，熱門城市文章數(shù)較多蕉拢，檢索可能會較慢特碳，但萬條數(shù)據(jù)還算ok。city_id=380的城市應該是個冷門城市晕换，小分類下只有一百條午乓。那為什么sql為什么執(zhí)行了那么長時間呢？闸准？
30s上下的執(zhí)行時間應該是掃描了百萬級數(shù)據(jù)益愈。
我開始懷疑mysql 的explain在“騙人”。
mysql根本沒走指定的索引夷家！
那我對union的兩個子句坐下explain怎么樣蒸其。
EXPLAIN
SELECT*FROM news_basic WHERE id !=18762279 AND (publish_time< '2017-06-02 09:31:25' OR (publish_time='2017-06-02 09:31:25' AND id< 18762279)) AND STATUS IN (1,7) AND source IN (11,12,13) AND city_id=380 AND category IN (43,52,1201,1202,1203,1204) ORDER BY publish_time DESC,id DESC LIMIT 1;
EXPLAIN
SELECT*FROM news_basic WHERE id !=18762279 AND (publish_time> '2017-06-02 09:31:25' OR (publish_time='2017-06-02 09:31:25' AND id> 18762279)) AND STATUS IN (1,7) AND source IN (11,12,13) AND city_id=380 AND category IN (43,52,1201,1202,1203,1204) ORDER BY publish_time ASC,id ASC LIMIT 1;
好嘛，索引都走publish_time库快，掃描行數(shù)到底291萬行……
優(yōu)化方法
優(yōu)化方法比較常規(guī)摸袁，可以force index，也可以ignore index（publish_time）义屏。
(
SELECT*FROM news_basic force index(index_city_id_category) WHERE id !=18762279 AND (publish_time< '2017-06-02 09:31:25' OR (publish_time='2017-06-02 09:31:25' AND id< 18762279)) AND STATUS IN (1,7) AND source IN (11,12,13) AND city_id=380 AND category IN (43,52,1201,1202,1203,1204) ORDER BY publish_time DESC,id DESC LIMIT 1) UNION ALL (
SELECT*FROM news_basic force index(index_city_id_category) WHERE id !=18762279 AND (publish_time> '2017-06-02 09:31:25' OR (publish_time='2017-06-02 09:31:25' AND id> 18762279)) AND STATUS IN (1,7) AND source IN (11,12,13) AND city_id=380 AND category IN (43,52,1201,1202,1203,1204) ORDER BY publish_time ASC,id ASC LIMIT 1);
(
SELECT*FROM news_basic IGNORE INDEX (publish_time) WHERE id !=18762279 AND (publish_time< '2017-06-02 09:31:25' OR (publish_time='2017-06-02 09:31:25' AND id< 18762279)) AND STATUS IN (1,7) AND source IN (11,12,13) AND city_id=380 AND category IN (43,52,1201,1202,1203,1204) ORDER BY publish_time DESC,id DESC LIMIT 1) UNION ALL (
SELECT*FROM news_basic IGNORE INDEX (publish_time) WHERE id !=18762279 AND (publish_time> '2017-06-02 09:31:25' OR (publish_time='2017-06-02 09:31:25' AND id> 18762279)) AND STATUS IN (1,7) AND source IN (11,12,13) AND city_id=380 AND category IN (43,52,1201,1202,1203,1204) ORDER BY publish_time ASC,id ASC LIMIT 1);
兩種優(yōu)化的結(jié)果都很明顯靠汁，查詢時間達到0.089s和 0.015s蜂大。
explain 計劃顯示都使用了索引index_city_id_category。
最后
目前的困惑在于如果explain給出的結(jié)果是有錯誤的蝶怔，那之后優(yōu)化sql語句時可以相信的工具是什么呢……
查了許久奶浦，沒有google到mysql做union操作時explain結(jié)果不正確的相關案例。
但是在git上發(fā)現(xiàn)了類似例子：有人分享了索引失效選擇time索引的案例踢星，也是掃描了百萬行數(shù)據(jù)澳叉。很可惜的是該例子沒有做union操作，也無法對比union的explain結(jié)果斩狱。
