讓左模糊查詢也能走索引

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

有一個(gè)USER_NAME字段的索引

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

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

執(zhí)行計(jì)劃如下：

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

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

此時(shí)如果是要模糊搜索出用戶名后幾位有杰這個(gè)詞的所有用戶信息，可以對(duì)REVERSE_USER_NAME字段做右模糊查詢，效果其實(shí)就是和對(duì)USER_NAME字段做左模糊查詢是一樣的，因?yàn)槎叩膬?nèi)容是相反的，結(jié)果如下：

SELECT * from USER_INFO where REVERSE_USER_NAME like '杰%'

執(zhí)行計(jì)劃如下：

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

模糊查詢（like、instr）

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

1. like

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

2. instr

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

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

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

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

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

3的補(bǔ)充講解

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

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

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

最新評(píng)論