前言
在SQL优化相关资料中,通常可以看到一个建议:用UNION来代替OR
比如采用 OR 语句:
SELECT * FROM a WHERE a.x = y or a.m = n;
采用 UNION 语句,返回的结果同上面的一样,但是速度要快些:
SELECT * FROM a WHERE a.x = y
UNION
SELECT * FROM a WHERE a.m = n;
但是,这个结论真的就是正确的吗?
准备
首先,测试的版本是mysql8
SELECT version();
然后,创建一个表
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`age` int(11) DEFAULT NULL,
`uname` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`intro` varchar(600) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_age` (`age`) USING BTREE
) ENGINE=InnoDB;
最后准备测试数据
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i<=1000000
DO
insert into `user` values (i, MOD(i-1,100) + 1, CONCAT('name', i),
REPEAT(CONCAT('intro', i), 40));
SET i=i+1;
END WHILE ;
commit;
END
开始测试
测试主要分三种情况,分别为两边的条件都不是索引、一个是索引、都是索引。
- 都不是索引
使用OR:
SELECT * from `user` WHERE uname = "name9999" OR intro LIKE "intro805intro805%"
先看执行计划:
真实执行时间:
使用UNION:
SELECT * from `user` WHERE uname = "name9999"
UNION
SELECT * from `user` WHERE intro LIKE "intro805intro805%"
先看执行计划:
真实执行时间:
通过上面的测试,我们可以看到,在两边条件都不是索引的情况下,UNION 执行了2次的全表扫描,消耗将近是 OR 的两倍。
- 一个是索引
使用OR:
SELECT * from `user` WHERE age = 99 OR uname = "name9999"
执行计划:
真实执行时间:
使用UNION:
SELECT * from `user` WHERE age = 99
UNION
SELECT * from `user` WHERE uname = "name9999";
执行计划:
真实执行时间:
这种情况,UNION是做了1次全表扫描加上一次索引查询,但是OR是只做了1次全表扫描。因为数据量比较大,因此在执行时间上,UNION也明显高出OR很多。
- 都是索引
使用OR:
SELECT * from `user` WHERE age = 99 or id = 1
执行计划:
真实执行时间:
使用UNION:
SELECT * from `user` WHERE age = 99
UNION
SELECT * from `user` WHERE id=1
执行计划:
真实执行时间:
在这种情况下,虽然两种语句的执行方式不一样,但是执行时间是差不多的。
在使用OR的执行计划中,我们看到它的type是index_merge,Extra是Using union(idx_age,PRIMARY)。index merge(索引合并)是mysql5.1引入的一个特性,MySQL在分析执行计划时发现走单个索引的过滤效果都不是很好,对多个索引分别进行条件扫描,然后将它们各自的结果进行合并(intersect/union)。经过这个优化以后,其实OR的执行过程和UNION已经有异曲同工之妙了。