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

2022-11-23 14:01:39

讓左模糊查詢也能走索引

有個業務需求，需要模糊搜尋出使用者名稱後幾位有傑這個詞的所有使用者資訊，這時候不可能說為了一個搜尋就引入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 '傑%'

執行計劃如下：

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

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

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

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

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

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

這個方法只適用於字元型欄位，且除了我們想要的欄位外，還得加上一個超過此型別的，並且他只支援右模糊。但是在這幾個當中就右模糊而言，他的效率是最高的。

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

這裡的比較是比較的ASCII，但這不是比較的總的ASCII，而是一個字元一個字元的比較，例如我這裡有資料庫的欄位為’123456123’，而要比較的還有’123456223’，這裡進行比較，當到了61的1和62的2時就可以比較出大小了。

以上為個人經驗，希望能給大家一個參考，也希望大家多多支援it145.com。