mysql 中 REGEXP 和IN 的效率对比

实例

为了探索这个问题，新建一个表 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倍，确实在效率上有数量级的差距！