SQL使用模糊查詢like '%ABC' 和 like '%ABC%'的優化 

---

---

編輯手記: 今天繼續學習SQL優化的技巧。感謝劉永甫專家，本文授權轉自“老虎劉談SQL優化”。

一般情況下，sql中使用col_name like 'ABC%‘的情況才能使用到col_name欄位上的索引。那麼如果是col_name like '%ABC%'的情況，能否使用索引呢？

答案是：可以使用索引，但是需要改寫SQL並建立reverse函式索引。

具體如何實現？聽專家為你揭曉。

一、col_name like '%ABC'時的優化方法

Test case：

    Create table t1 as select * from dba_objects;

    Create index idx_t1_objectname1 on t1(object_name);

在正常情況下，百分號在後面，可以使用索引：

select object_name from t1 where object_name like ‘DBA%';

百分號在前面，不能使用索引：

select object_name from t1 where object_name like '%LIB';

解決方法

create index idx_t1_objectname2 on t1(reverse(object_name));

select object_name from t1 where reverse(object_name) like reverse('%LIB');

我們看執行計劃：

改寫後SQL走了索引。

二、col_name like '%ABC%'時的優化方法

一般認為這種情況是不能使用索引的，但還是有一些優化方法可以使用。

有三種情況：

1、ABC始終從字串開始的某個固定位置出現，可以建立函式索引進行優化

2、ABC始終從字串結尾的某個固定位置出現，可以建立函式組合索引進行優化

3、ABC在字串中位置不固定，可以通過改寫SQL進行優化

情況1、先建立substr函式索引，再使用like ‘ABC%’。

假如ABC從字串第五位出現：

Test Case：

create index idx_substr_t1_objname on t1 (substr(object_name,5,30));

select object_id,object_type,object_name from t1

where substr(object_name,5,30) like 'TAB%';

情況2、先建立reverse+substr組合函式索引，再使用like reverse‘%ABC’。

假如ABC從字串倒數第五位出現：

Test Case：

Create index idx_t1_reverse2 on t1(reverse(substr(object_name,1,length(object_name)-4)));

select object_id,object_name,object_type from t1 

where reverse(substr(object_name,1,length(object_name)-4)) like reverse('%TAB_COL');

情況3、這種情況需要like的欄位上存在普通索引，主要在SQL的寫法上做改進。

原來的SQL是這樣寫的：

Select object_id,object_type,object_name from t1

where object_name like '%ABC%‘;

改寫後的SQL是這樣的：

Select object_id ,object_type,object_name from t1 

Where object_name in

(select object_name from t1 where object_name like ‘%ABC%’);

Test Case：

create index idx_t1_object_name on t1 (object_name);

Select object_id,object_type,object_name from t1

where object_name like '%TABCOL%';

此時SQL的執行計劃是t1 表做全表掃描。

Select object_id,object_type,object_name from t1

Where object_name in

(select object_name from t1 where object_name like '%TABCOL%');

改寫後的SQL執行計劃是索引全掃描加索引回表操作：

優化原理

用索引全掃描取代表的全掃描。因為索引全掃描的代價是全表掃描的1/N (即索引塊數與資料塊數的比例），表越大，優化效果越明顯。

改寫後SQL的執行計劃，根據索引再回表的代價要看符合條件的記錄數多少：如果in子查詢返回的記錄數很少，那麼優化的效果就相當於效率提高了N倍；如果in子查詢返回的記錄數較多，兩種SQL的效能區別就不是很明顯了。

---

---

About Me

............................................................................................................................... ● 本文轉載自老虎劉談SQL優化，感謝劉永甫專家的奉獻 ● 本文在itpub（http://blog.itpub.net/26736162）、部落格園（http://www.cnblogs.com/lhrbest）和個人微信公眾號（xiaomaimiaolhr）上有同步更新 ● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/ ● 本文部落格園地址：http://www.cnblogs.com/lhrbest ● 本文pdf版及小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/ ● 資料庫筆試面試題庫及解答：http://blog.itpub.net/26736162/viewspace-2134706/ ● QQ群：230161599     微信群：私聊 ● 聯絡我請加QQ好友(646634621)，註明新增緣由 ● 於 2017-05-09 09:00 ~ 2017-05-30 22:00 在魔都完成 ● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解 ● 版權所有，歡迎分享本文，轉載請保留出處 ............................................................................................................................... 拿起手機使用微信客戶端掃描下邊的左邊圖片來關注小麥苗的微信公眾號：xiaomaimiaolhr，掃描右邊的二維碼加入小麥苗的QQ群，學習最實用的資料庫技術。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2139039/，如需轉載，請註明出處，否則將追究法律責任。