讓左模糊查詢也能走索引

測試表USER_INFO表數(shù)據(jù)以及結(jié)構(gòu)如下

有一個USER_NAME字段的索引

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

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

執(zhí)行計劃如下：

可以發(fā)現(xiàn)是用不到索引的。

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

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

SELECT * from USER_INFO where REVERSE_USER_NAME like '杰%'

執(zhí)行計劃如下：

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

模糊查詢（like、instr）

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

1. like

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

2. instr

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

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

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

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

這個方法只適用于字符型字段，且除了我們想要的字段外，還得加上一個超過此類型的，并且他只支持右模糊。但是在這幾個當(dāng)中就右模糊而言，他的效率是最高的。

3的補充講解

在數(shù)據(jù)中進行字符的比較時發(fā)現(xiàn)自己并不是對此了解，這里記下這里字符的比較是比較的哪里。

這里的比較是比較的ASCII，但這不是比較的總的ASCII，而是一個字符一個字符的比較，例如我這里有數(shù)據(jù)庫的字段為’123456123’，而要比較的還有’123456223’，這里進行比較，當(dāng)?shù)搅?1的1和62的2時就可以比較出大小了。

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

最新評論