mysql如何讓左模糊查詢也能走索引

Posted on by

讓左模糊查詢也能走索引

測試表USER_INFO表數據以及結構如下

有一個USER_NAME字段的索引

有個業務需求，需要模糊搜索出用戶名後幾位有傑這個詞的所有用戶信息，這時候不可能說為瞭一個搜索就引入ES，但是如果sql使用左模糊查詢的話，根據索引的最左匹配原則，該sql語句是不可能使用到idx_user_name索引的，如下：

EXPLAIN SELECT * from USER_INFO where USER_NAME like '%傑'

執行計劃如下：

可以發現是用不到索引的。

需要做模糊匹配，又要用到索引，索引的最左匹配原則更是不能被打破，這時候可以增加一個字段，這個字段的內容等於USER_NAME字段內容的反轉，同時加上這個字段的相關索引，如下：

此時如果是要模糊搜索出用戶名後幾位有傑這個詞的所有用戶信息，可以對REVERSE_USER_NAME字段做右模糊查詢，效果其實就是和對USER_NAME字段做左模糊查詢是一樣的，因為二者的內容是相反的，結果如下：

SELECT * from USER_INFO where REVERSE_USER_NAME like '傑%'

執行計劃如下：

小結一下：索引的最左匹配原則不能打破，那麼要讓左匹配也走索引的話，換個思路，讓右匹配的效果和左匹配一樣就好瞭，同時右匹配又能走索引，間接達到瞭左模糊查詢也能走索引的目的。

模糊查詢（like、instr）

SQL中經常會遇到模糊查詢，現在模糊查詢正常、最常用的有兩種，一種是like、另一種是instr，這兩種單單是簡單的搜索，instr的效率是比like要高的（這也得看%在哪兒瞭）。

1. like

like中分右模糊、左模糊，右模糊比如’abc%‘時,掃描索引,高效。當模糊查詢含左模糊時,比如’%abc’,進行全表掃描,低效。當然更別提’%abc%'瞭。

2. instr

instr(字段名, string)，instr的使用也很簡單，就是填寫一下字段名，然後與後面需要查找的內容相關。這個比like的左模糊效率要高，但是要比右模糊還是相差不多的（因為在instr不分左右模糊）。

3. A>=’’ and A<’’

今天在搜索查找之時還找到瞭這麼一個查找方法，這個方法要比上面的instr效率還要高，不過這個方法局限性還是比較高的。例如：

select * from formtable_main_200_dt1 where hth >= '16040610' and hth < '16040611'

這個方法隻適用於字符型字段，且除瞭我們想要的字段外，還得加上一個超過此類型的，並且他隻支持右模糊。但是在這幾個當中就右模糊而言，他的效率是最高的。

3的補充講解

在數據中進行字符的比較時發現自己並不是對此瞭解，這裡記下這裡字符的比較是比較的哪裡。

這裡的比較是比較的ASCII，但這不是比較的總的ASCII，而是一個字符一個字符的比較，例如我這裡有數據庫的字段為’123456123’，而要比較的還有’123456223’，這裡進行比較，當到瞭61的1和62的2時就可以比較出大小瞭。

以上為個人經驗，希望能給大傢一個參考，也希望大傢多多支持WalkonNet。

推薦閱讀：