sql模糊查詢優化

❶ sql之模糊查詢的四種用法

MySql 使用 like關鍵字 進行模糊查詢，like關鍵字 通常用在 where條件查詢，like關鍵字 通常配合 %、_、[ ]、[^ ] 使用。

%表示任意0個或多個字元。可匹配任意類型和長度的字元，有些情況下若是中文，請使用兩個百分號（%%）表示。

注意：只能查詢連續的文字。【比如「深圳市福田區」可以查詢，「深圳市區」查詢不出來】

注意：此語句沒有那麼靈活，比較適用於固定的某個文字的查詢。

注意：此語句比較靈活，可以查詢相隔幾個文字，比較實用。【比如「深圳市福田區小學」可以查詢，「深圳市福區小」也可以查詢】

表示任意單個字元。匹配單個任意字元，它常用來限製表達式的字元長度語句。

表示括弧內所列字元中的一個（類似正則表達式）。指定一個字元、字元串或范圍，要求所匹配對象為它們中的任一個。

表示不在括弧所列之內的單個字元。其取值和 [] 相同，但它要求所匹配對象為指定字元以外的任一個字元。

❷ Sql優化-多like模糊查詢及根據時間排序

2020-04-21

記錄一次sql優化記錄：

環境：用的mysql版本  select Version();

優化過程：

用的是兩張表聯查，四個條件like查詢 ，根據時間排序降序

其中A，B表沒有大欄位，A表20萬多數據，B表50萬多條數據。語句如下：

EXPLAIN

SELECT A.bondId,A.sname,A.cname,A.secuCode,A. ISSUER,A.guarantor,B.underwriter AS infoSource

  FROM   A

  LEFT JOIN  B ON B.bondId = A.bondId

 WHERE B.agentType = 1

 AND B.underwriter = '有限公司'

 AND A.startDate <= '2020-04-21 18:02:10'

 AND A.endDate >= '2020-04-21 18:02:10'

 AND (

 A.cname LIKE '%%'  OR A.sname LIKE '%%'  OR A.secuCode LIKE '%%'

 OR A. ISSUER LIKE '%%'OR A.guarantor LIKE '%%')

 AND A.isValid = 1

 ORDER BY A.startDate DESC

 LIMIT 0, 20

這是2個表都沒有加索引的情況，從explain來看結果非常糟糕，都是全表掃描，並且產生臨時表同時有文件排序，效率肯定非常低。

首先嘗試在B表上建立一個聯合索引

可以考慮從關聯欄位及where條件欄位考慮（bondId, underwriter, agentType）

建一個聯合索引，試試。

ALTER TABLE B ADD INDEX bua_index(bondId, underwriter, agentType)

再explain看：

可以看到B表用到了我們剛剛建的聯合索引，並且額外信息是Using index ，type是ref級別的，效果比較理想，再來看A表。

Where條件中有多個like，這種情況下一般索引都是不可用的，所以必須用覆蓋索引解決，

由於又根據startDate排序，所以嘗試根據如下欄位建立聯合索引，同時查詢的欄位就是索引中的欄位（startDate, endDate,cname, sname, secuCode, issuer, guarantor）

ALTER TABLE A ADD INDEX index_scssig(startDate, endDate,cname, sname, secuCode, issuer, guarantor)

再次explain看看效果：

這樣乍看上去A表也用到了剛剛建的聯合索引，並且type是range級別雖然比ref差點，按理說應該也還可以，但是我執行sql語句，效率還是非常差，查詢耗時達到8s，並且偶爾還不止這個時間

究其原因，雖然使用了索引，但是extra裡面是Using index condition&Using where

回表操作了，我在想如果將extra優化成Using index效率肯定沒問題

故再進一步優化，還是從索引入手

在聯合索引上添加2個欄位isValid, bondId 再試試

ALTER TABLE A ADD INDEX index_scssig(isvalid,startDate, endDate,cname, sname, secuCode, issuer, guarantor,bondId)

再次explain:

這個結果就是我想要的，然後執行sql看看效率：

已經提升了很多了，但是我試了別的查詢條件偶爾時間會到3，4s,懷疑和自己的機器有關

在這這種多個like的or查詢mysql本身並不擅長，無奈坑爹的需要需要這樣，可能效率並不是非常的高，優化成這樣可以接受了。

最近對以前項目的慢查詢進行sql調優，感覺性能的下降往往還是sql語句及索引的建立的問題，explain是很有幫助，正確優化還是能極大提升效率的。

❸ SQL資料庫怎麼實現模糊查詢

1、首先，我們按下Ctrl+N創建一個SQL查詢。

❹ sql優化的N種方法

1.SQL語句中IN包含的值不應過多:
例如：select id from t where num in(1,2,3) 對於連續的數值，能用between就不要用in了； 實測速度差距不是很大.

2.SELECT語句務必指明欄位名稱:
禁止用 * 來查詢 ,禁止用 * 來查詢 ,禁止用 * 來查詢 , 查找哪個欄位,就寫具體的欄位.

select * from user_test WHERE address=15988;
select address from user_test WHERE address=15988;

3.只查詢一條數據的時候，使用limit 1
【這個很有用】

4.避免在where子句中對欄位進行null值判斷:
【實測：null值的判斷依然走了索引】
explain select uid from user_test WHERE phone is null;

5.避免在where子句中對欄位進行表達式操作:

6.對於聯合索引來說，要遵守最左前綴法則:
例如組合索引(id,name,sex) 使用的時候,可以id 或者id,name . 禁止直接name,或者sex.會導致聯合索引失敗

注意: id, name,sex 這三個欄位填寫順序不會有影響, mysql會自動優化成最左匹配的順序.

前三條sql都能命中索引,中間兩條由於不符合最左匹配原則,索引失效.

最後一條sql 由於有最左索引id 所以索引部分成功,部分失效. id欄位索引使用成功.

7.盡量使用inner join，避免left join：
如果連接方式是inner join，在沒有其他過濾條件的情況下MySQL會自動選擇小表作為驅動表，但是left join在驅動表的選擇上遵循的是左邊驅動右邊的原則，即left join左邊的表名為驅動表。
【實測：不是很准確，具體用explain測試】

8.注意范圍查詢語句:
對於聯合索引來說，如果存在范圍查詢，比如between、>、<等條件時，會造成後面的索引欄位失效。

解決辦法: 業務允許的情況下,使用 >= 或者<= 這樣不影響索引的使用.

explain select * from user_test where uid=10 and name='張三' and phone='13527748096';
explain select * from user_test where uid between( 1 and 10) and name ='張三' and phone='13527748096';

9.不建議使用%前綴模糊查詢:
例如 : LIKE「%name」或者LIKE「%name%」，這種查詢會導致索引失效而進行全表掃描。但是可以使用LIKE 「name%」。

explain select * from user_test where uid=10 and uid like "%1" ;
explain select * from user_test where uid=10 and uid like "1%" ;

10.在 where 子句中使用 or 來連接條件，如果or連接的條件有一方沒有索引,將導致引擎放棄使用索引而進行全表掃描
解決辦法: 將or連接的雙方都建立索引,就可以使用.

explain select * from user_test where uid=10 or name='張三';

11.應盡量避免在where子句中對欄位進行函數操作，這將導致引擎放棄使用索引而進行全表掃描。(此處存在疑點,我本人測試的時候,發現索引還是能使用到)

12.字元串類型的欄位 查詢的時候如果不加引號'' ,會導致自動進行隱式轉換,然後索引失效

❺ sql語句優化有什麼方法

1. 對查詢進行優化，應盡量避免全表掃描，首先應考慮在where及orderby涉及的列上建立索引。

2. 應盡量避免在where子句中對欄位進行null值判斷，否則將導致引擎放棄使用索引而進行全表掃描，如：
   selectidfromtwherenumisnull
   可以在num上設置默認值0，確保表中num列沒有null值，然後這樣查詢：
   selectidfromtwherenum=0

3. 應盡量避免在where子句中使用!=或<>操作符，否則將引擎放棄使用索引而進行全表掃描。

4. 應盡量避免在where子句中使用or來連接條件，否則將導致引擎放棄使用索引而進行全表掃描，如：
   selectidfromtwherenum=10ornum=20
   可以這樣查詢：
   selectidfromtwherenum=10
   unionall
   selectidfromtwherenum=20
   

5. in和notin也要慎用，否則會導致全表掃描，如：
   selectidfromtwherenumin(1,2,3)
   對於連續的數值，能用between就不要用in了：
   
   

6. 下面的查詢也將導致全表掃描：
   selectidfromtwherenamelike'%abc%'
   

7. 應盡量避免在where子句中對欄位進行表達式操作，這將導致引擎放棄使用索引而進行全表掃描。如：
   selectidfromtwherenum/2=100
   應改為:
   selectidfromtwherenum=100*2

8. 應盡量避免在where子句中對欄位進行函數操作，這將導致引擎放棄使用索引而進行全表掃描。如：
   selectidfromtwheresubstring(name,1,3)='abc'--name以abc開頭的id
   應改為:
   selectidfromtwherenamelike'abc%'
   

9. 不要在where子句中的「=」左邊進行函數、算術運算或其他表達式運算，否則系統將可能無法正確使用索引。
   

10. 在使用索引欄位作為條件時，如果該索引是復合索引，那麼必須使用到該索引中的第一個欄位作為條件時才能保證系統使用該索引，
    否則該索引將不會被使用，並且應盡可能的讓欄位順序與索引順序相一致。
    

11. 不要寫一些沒有意義的查詢，如需要生成一個空表結構：
    selectcol1,col2into#tfromtwhere1=0
    這類代碼不會返回任何結果集，但是會消耗系統資源的，應改成這樣：
    createtable#t(...)
    

12. 很多時候用exists代替in是一個好的選擇：
    selectnumfromawherenumin(selectnumfromb)
    用下面的語句替換：
    selectnumfromawhereexists(select1frombwherenum=a.num)
    

13. 並不是所有索引對查詢都有效，SQL是根據表中數據來進行查詢優化的，當索引列有大量數據重復時，SQL查詢可能不會去利用索引，
    如一表中有欄位sex，male、female幾乎各一半，那麼即使在sex上建了索引也對查詢效率起不了作用。