左百分號模糊查詢的優化

壹頁書發表於2014-12-30
原文網址 : http://blog.itpub.net/29254281/viewspace-1385050/
優化
先建立一個實驗表,只有歌曲名稱一列

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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章