实例
为了探索这个问题,新建一个表 t_person
CREATE TABLE `NewTable` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`p_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL ,
`ctime` datetime NULL DEFAULT NULL ,
`utime` datetime NULL DEFAULT NULL ,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_general_ci
AUTO_INCREMENT=10001
ROW_FORMAT=COMPACT
;
然后插入一万条数据:
drop procedure if exists my_procedure;
create procedure my_procedure()
begin
DECLARE n int DEFAULT 1;
WHILE n < 10001 DO
insert into t_person (p_name,ctime,utime) value (CONCAT('name_',n),NOW(), NOW());
set n = n + 1;
END WHILE;
end
CALL my_procedure();
先用REGEXP检索:
SELECT id
FROM t_person
WHERE 'name_9010,name_9011,name_9012,name_9013,name_9014,name_9015,name_9016, name_9017,name_9018,name_9019,
name_9020,name_9021,name_9022,name_9023,name_9024,name_9025,name_9026, name_9027,name_9028,name_9029' REGEXP p_name
耗时:49ms
用IN检索:
SELECT id
FROM t_person
WHERE p_name in ( 'name_9010','name_9011','name_9012','name_9013','name_9014','name_9015','name_9016', 'name_9017','name_9018','name_9019',
'name_9020','name_9021','name_9022','name_9023','name_9024','name_9025','name_9026', 'name_9027','name_9028','name_9029')
耗时:7ms
总结:
从上面简单的实验可以看出,用IN的效率是要高于REGEXP的。在实际Java项目中,使用mybatis中的foreach可以很方便的用IN检索,例如:
mybatis:
<select id="selectDevices">
select id
from t_device
where serial_number not in
<foreach collection="deviceSerialNumbers" item="deviceSerialNumber" index="index" open="(" close=")" separator=",">
#{deviceSerialNumber}
</foreach>
</select>
dao
List<String> selectDevices(@Param("deviceSerialNumbers") List<String> deviceSerialNumbers);
后记
前面的内容是在家里写的,由于家里没有项目环境,就只是简单的测试了一下。今天来公司后把用到的REGEXP都替换为IN,检索速度提升了10~15倍,确实在效率上有数量级的差距!