近期收到服务器报警，发现有条sql在执行的时候，耗时很长竟然能有30s多。
数据库版本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文章的发布时间。

这条sql看起来很复杂，怕出现慢查询所以explain看了下执行计划。

执行计划

如图可见，索引走的是城市+类别(index_city_id_category)的联合索引。
那根据索引坐下group by，看下每个小类别有多少条数据。

SELECT city_id ,count(*) from news_basic WHERE   category IN (43,52,1201,1202,1203,1204)  GROUP BY city_id

image.png
image.png

可以看出，热门城市文章数较多，检索可能会较慢，但万条数据还算ok。city_id=380的城市应该是个冷门城市，小分类下只有一百条。那为什么sql为什么执行了那么长时间呢？？
30s上下的执行时间应该是扫描了百万级数据。

我开始怀疑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

好嘛，索引都走publish_time，扫描行数到底291万行……

优化方法

优化方法比较常规，可以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);

两种优化的结果都很明显，查询时间达到0.089s和 0.015s。
explain 计划显示都使用了索引index_city_id_category。

union子句的执行计划

最后

目前的困惑在于如果explain给出的结果是有错误的，那之后优化sql语句时可以相信的工具是什么呢……
查了许久，没有google到mysql做union操作时explain结果不正确的相关案例。
但是在git上发现了类似例子：有人分享了索引失效选择time索引的案例，也是扫描了百万行数据。很可惜的是该例子没有做union操作，也无法对比union的explain结果。