左百分号模糊查询的优化

先创建一个实验表,只有歌曲名称一列

create table test(name varchar2(50));
insert into test values('童年');
insert into test values('家家有本难念的经');
insert into test values('清风月朗&如山如水亦如虹翻唱《因为爱情》');
insert into test values('爱的那份痛');
insert into test values('倒带');
insert into test values('套马杆');
insert into test values('爱忧伤');
insert into test values('其实很爱你');
insert into test values('弹起我心爱的土琵琶');
insert into test values('你是谁');
insert into test values('撒旦');
insert into test values('花楼恋之歌');
insert into test values('下马酒之歌');
insert into test values('我的柔情你永远不懂');
insert into test values('一个人的寂寞两个人的错');
insert into test values('贞观长歌');
insert into test values('说句心里话');
insert into test values('这一生回忆有你就足够');
insert into test values('禅语');
insert into test values('火苗');
insert into test values('牵手分手都是爱');
insert into test values('不能没有你');
insert into test values('爱情末班车');
insert into test values('我是否也在你心中');
insert into test values('别问我是谁');
insert into test values('春光美');
insert into test values('草原夜色美');
insert into test values('快乐老家');
insert into test values('相逢是首歌');
insert into test values('云河');
insert into test values('如果爱我成了你的负累');
insert into test values('和昨天说再见');
insert into test values('爱的音符');
insert into test values('我是爱情的俘虏.');
insert into test values('我爱你塞北的雪');
insert into test values('爱上离婚的女人');
insert into test values('爱情末班车');
insert into test values('MC小洪 我多想抱着你哭');
insert into test values('鱼和水的故事');
insert into test values('用情最深的人');

通常模糊查询有三种方式
1.左百分号
select * from test where name like '%之歌';
2.右百分号

select * from test where name like '之歌%';
3.双百分号
select * from test where name like '%之歌%';

右百分号的情况是可以走索引的.
"其余两种情况是不能走索引的."面试的时候都这么问,回答的时候也都这么答.
仿佛这都成了标准答案.
其实都还有进一步优化的空间.

比如左百分号的情况.
诚然,
select * from test where name like '%之歌';
这种SQL不走索引,但是可以使用Oracle的函数索引.
create index inx_name on test(reverse(name));

然后改写SQL
select * from test where reverse(name) like reverse('之歌')||'%';

如果是MySQL就复杂一点.因为MySQL不支持函数索引.

但是可以引入冗余列处理,
index_name专门存放name字段的反向值,然后对index_name进行索引,以便查询
alter table test add index_name varchar(50);
update test set index_name=reverse(name);
create index inx_name on test(index_name);

select * from test where index_name like concat(reverse('之歌'),'%');

但是冗余列的维护比较麻烦,新增记录和修改name的值,都需要重新计算index_name

至于双百分号的情况
有两种优化的手段
1.延迟关联
2.索引覆盖

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29254281/viewspace-1385050/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29254281/viewspace-1385050/