工作問題總結一：由資料查詢慢來淺談下oracle中的like和instr函數的模糊查詢效率問題

標籤：blog   http   ar   os   使用   sp   java   資料   on   

　　今天上午10點左右，boss告訴我們生產環境一客戶在某功能下錄入資訊時，出現載入資料很慢的情況，因為嚴重影響了客戶使用，就讓我們趕緊查實原因，組長和我就根據領導提供的使用者登入系統，找到出問題的那個功能，選擇買方和銀行資訊後載入限額資訊時組長的電腦出現的反應是瀏覽器卡死，我的電腦上是去趟廁所回來坐下來之後才載入出來，我靠3分鐘左右的時間，崩潰！！！  趕緊查代碼  經查詢代碼發現，載入的資料是從千萬量級（有2321w餘條）的表中查詢，查詢語句中使用了like操作符，經研究發現確實是因為查詢的sql中使用like操作符造成。查詢sql如下：

　　select sum(t.a*t.b) from table t where t.c = ‘A‘ and t.d like ‘AM%‘ and t.e = ‘B‘.... 

　　其中表t的d欄位值的開頭2個字母表達某種類型的資料 有AM、BM、CM等幾類資料，且d欄位的其他位均為0-9的數字

      下邊是分析問題的步驟：
      1.經查詢該表的索引策略，該表的為 d > c > e  （備忘：d、c、e為table表的欄位）
      2.將d的索引失效，之後用上述語句查詢，速度很快，但是對於select * from table t where t.d = ‘AM1245‘的使用無疑會是災難，因為變成全表檢索了
      3.然後使d的索引生效，查詢該表的預設的索引策略依舊為  d > c > e
      4.使用instr(t.d,‘AM‘)>0 替換sql語句中的t.d like ‘AM%‘,發現效率很快。 -- 在生產環境驗證的結果是使用like查詢三分鐘 是用instr後瞬間查詢出來
      由此問題，引出了本文的重點,即instr與like的模糊查詢效率問題

      經問度娘得知：

　　　  使用Oracle的instr函數與索引配合提高模糊查詢的效率。　　

          一般來說，在Oracle資料庫中，我們對tb表的name欄位進行模糊查詢會採用下面兩種方式：
               1.select * from tb where name like ‘%XX%‘;
               2.select * from tb where instr(name,‘XX‘)>0;
          若是在name欄位上沒有加索引，兩者效率差不多，基本沒有區別。
         為提高效率，我們在name欄位上可以加上非唯一性索引：
              create index idx_tb_name on tb(name);
         這樣，再使用 select  * from tb where instr(name,‘XX‘)>0;這樣的語句查詢，效率可以提高不少，表資料量越大時兩者差別越大（我們遇到的問題就是屬於這種問題）。但也要顧及到name欄位加上索引後DML語句會使索引資料重新排序的影響。

 

　　本文參看文章：http://wenku.baidu.com/link?url=7C70-3mTzeKx2IdVjEdhgHKK8m5elJE5CYyPNivfzj4w8eU9snrbNMNMrfOH6kPZ-G150BXJVIJaHciHsTw-i9erGMtMwRIGRzKXp-rAlmK

 

解決該問題涉獵到的知識點：

　　1.oracle長條圖

　　　　相關介紹：參看 http://czmmiao.iteye.com/blog/1484298

　　　　　                  http://blog.csdn.net/javacoffe/article/details/5578206

      2.索引策略

            相關的介紹請問度娘。

      3.oracle 最佳化器

　　　　相關介紹：參看 http://www.cnblogs.com/dongzhiquan/archive/2012/01/20/2328365.html

      4.oracle中的cluster  -- oracle中的聚集

　　　　相關介紹：參看 http://blog.csdn.net/thunder09/article/details/5003675

　　　 

 

工作問題總結一：由資料查詢慢來淺談下oracle中的like和instr函數的模糊查詢效率問題