Oracle中like、instr和regexp_like性能的讨论总结

1、regexp_like功能强大，但效率比like要差；

2、若使用like的字段上无索引，且匹配字符串前后都有%，则instr效率高；

3、若使用like的字段上有索引，且匹配字符串前后都有%，若仅操作索引字段则like效率高，若有其他字段则instr效率高，可能的原因是仅操作索引字段不会全表扫描而是采用全索引扫描；

4、若使用like的字段上有索引，且仅匹配字符串结尾有%，则like效率高，因为like使用到了索引，而instr会造成索引失效走且表扫描；

综上所述，like和instr哪个效率高要视情况而定，两者效率均高于regexp_like。

【实验】

注：本实验转自网络，但原出处无法查实故未添加引用链接，若有侵权请联系我。

例如：t表中将近有1100万数据，我们要进行字符串匹配，经过实际测试发现，like与instr函数在性能上有一定差别，测试结果如下：

SQL> set timing on
SQL> select count(*) from t where instr(title,'手册')>0;
  COUNT(*)
----------
65881
Elapsed: 00:00:11.04
SQL> select count(*) from t where title like '%手册%';
  COUNT(*)
----------
65881
Elapsed: 00:00:31.47
SQL> select count(*) from t where instr(title,'手册')=0;
  COUNT(*)
----------
11554580
Elapsed: 00:00:11.31
SQL> select count(*) from t where title not like '%手册%';
  COUNT(*)
----------
11554580

另外一个2亿多的表，使用8个并行，使用like查询很久都不出来结果，但使用instr函数4分钟即完成查找，性能是相当的好。这些小技巧用好，工作效率提高不少。

说明：instr(title, 'aaa')>0功能相当于like，instr(title, 'aaa')=0功能相当于not like，但是效率不同。

若在title字段上加非唯一性索引：

create index idx_t_ title on t(title);

这样，再使用

select * from t where instr(title,'手册')>0;

这样的语句查询，效率可以提高不少，表数据量越大时instr优势越明显。

若不带其他字段，如下：

select count(1) from t where title like '%手册%';

select count(1) from t where instr(title,'手册')>0;

结果发现，like的效率要大大优于instr。