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倍，确实在效率上有数量级的差距！