一.like查询与索引 在oracle里的一个超级大的表中,我们的where条件的列有建索引的话,会走索引唯一扫描INDEX UNIQUE SCAN。如select * from table where code = 'Cod25',而如下这些语句哪些会走索引呢? Sql代码
经验证: 当然,如果select * from table where code like 'Cod%' 查询的结果就是所有记录,走索引和full table scaN的结果是一样的,所以也将是全表扫描。可以换成select * from table where code like 'Code2%'或者 select count(*) from table where code like 'Cod%'试试,应该不会是全表扫描。 二.优化like查询 1.经上面测试,like查询结果如下: 1)在oracle中,可以用instr,这样查询效果很好,速度很快。 Sql代码
2)在mysql中,可以用locate和position函数,如table.field like '%AAA%'可以改为locate('AAA', table.field) > 0或POSITION('AAA' IN table.field)>0。 LOCATE(substr,str)、POSITION(substr IN str):返回子串 substr 在字符串 str 中第一次出现的位置。如果子串 substr 在 str 中不存在,返回值为 0。 3)在sql server中,可以给字段建立全文索引,用contains来检索数据,CONTAINS用法,可以参考:http://bijian1013./blog/2232872 b.查询%xx的记录 Sql代码
在执行的时候,执行计划显示,消耗值,io值,cpu值均非常大,原因是like后面前模糊查询导致索引失效,进行全表扫描。 Sql代码
使用翻转函数+like前模糊查询+建立翻转函数索引=走翻转函数索引,不走全扫描。有效降低消耗值,io值,cpu值这三个指标,尤其是io值的降低。 建函数索引:create index p_idx on table(instr(code,'Code2'));需进一步说明的是,这样的话,只有where instr(code,'Code2')才会走INDEX RANGE SCAN,其它如where instr(code, 'Code3')会走INDEX FAST FULL SCAN甚至TABLE ACCESS FULL。 另外,select * from table where upper(code) = 'abcD',会走TABLE ACCESS FULL。如果建函数索引create index idx_upper on table(upper(code));之后,将会是INDEX RANGE SCAN,如下所示:
PS:一般索引和函数索引的区别 1.一般的索引: Sql代码
当执行SELECT * FROM TABLE1 WHERE COLUMN1 = XXX 时会用到索引。 2.函数索引: Sql代码
当执行SELECT * FROM TABLE1 WHERE SUBSTR(COLUMN1,0,5) = XXX 时会用到索引。但执行SELECT * FROM TABLE1 WHERE COLUMN1 = XXX时是不会用到索引的。一般情况下是最好不用建函数索引。 |
|