前言
在写一些需求的时候发现SQL查询时间有点长,就想着优化一下。因为语句中存在 or 条件查询。第一想法便是 or 去掉,看到很多文章是通过 union 查询来替换掉 or 查询。但是实践下来发现并不是最优,便记录下来。
环境说明
MySQL版本:5.6.28-cdb2016-log
表数据总量:3571131
索引字段:
最初的查询语句:
select userid,relatedid,operation,description,
FROM_UNIXTIME(dateline,"%Y-%m-%d %H:%i:%s") as dateline,number,credittype
from uc_user_credits_log_202112
where (userid=2089340 and credittype='gold')
or (relatedid=2089340 and credittype='gold')
order by dateline desc,logid desc limit 10 offset 0
Explain 查询结果:
查询时间: 3.058s
可以看到命中条数 一百万多。。。 所以这里即使索引生效了也没什么用。
一、为什么要用 union 替换 or ?
用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描. 注意, 以上规则只针对多个索引列有效. 如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低。
⬆️ 上面这是大部分让我们使用 union 替换 or 方法思路
我进行了如下测试:
EXPLAIN select * from (
select userid,relatedid,operation,description,logid,FROM_UNIXTIME(dateline,"%Y-%m-%d %H:%i:%s") as dateline,number,credittype from
uc_user_credits_log_202112 as a where userid=2089340 and credittype='gold'
UNION ALL
select userid,relatedid,operation,description,logid,FROM_UNIXTIME(dateline,"%Y-%m-%d %H:%i:%s") as dateline,number,credittype from
uc_user_credits_log_202112 as b where relatedid=2089340 and credittype='gold'
) a order by a.dateline desc limit 10 offset 0
Explain 查询结果:
查询时间为: 1.602s
可以看出,这里索引是命中的,从2万多行中进行查询到相关数据。最后用临时表进行数据的合并 。确实变快了,节省了1秒多。
二、使用 union 是否是最优?
不是的。
下面我尝试了不使用 union 直接使用 or 但是改变一下查询语句
select userid,relatedid,operation,description,
FROM_UNIXTIME(dateline,"%Y-%m-%d %H:%i:%s") as dateline,number,credittype
from uc_user_credits_log_202112
where (userid=2089340 or relatedid=2089340) and credittype='gold'
order by dateline desc,logid desc limit 10 offset 0
Explain 查询结果:
查询时间:0.124s
似乎和我们想的不一样 可以看到从十万数据中查询,但是时间却比 union 还要快?这就让我有点困惑了。
便想着可能是因为用户记录数量的问题,然后查询记录数量从高到低试了3个,发现结果还是一样。
总结
基本上我们能确定,在几百万数据的时候。在两个字段都是用索引的情况下,用户用 or 进行查询也会出现索引生效的情况。在这种情况下并不一定要使用 union 替换 or 。反而效率并没有 or 的效率高。