在使用msyql進(jìn)行模糊查詢的時(shí)候，很自然的會(huì)用到like語(yǔ)句砚殿，通常情況下，在數(shù)據(jù)量小的時(shí)候芝囤，不容易看出查詢的效率似炎，但在數(shù)據(jù)量達(dá)到百萬級(jí)，千萬級(jí)的時(shí)候悯姊，查詢的效率就很容易顯現(xiàn)出來羡藐。這個(gè)時(shí)候查詢的效率就顯得很重要！
一般情況下like模糊查詢的寫法為（field已建立索引）：
SELECT`column`FROM`table`WHERE`field`like'%keyword%';
上面的語(yǔ)句用explain解釋來看悯许，SQL語(yǔ)句并未用到索引仆嗦，而且是全表搜索，如果在數(shù)據(jù)量超大的時(shí)候先壕，可想而知最后的效率會(huì)是這樣
對(duì)比下面的寫法：
SELECT`column`FROM`table`WHERE`field`like'keyword%';
這樣的寫法用explain解釋看到瘩扼，SQL語(yǔ)句使用了索引谆甜，搜索的效率大大的提高了！
但是有的時(shí)候集绰，我們?cè)谧瞿：樵兊臅r(shí)候规辱，并非要想查詢的關(guān)鍵詞都在開頭，所以如果不是特別的要求栽燕，"keywork%"并不合適所有的模糊查詢
我在網(wǎng)上搜索時(shí)發(fā)現(xiàn)很多mysql函數(shù)用來解決這個(gè)問題罕袋，我測(cè)試出來的結(jié)果是跟like相比并沒有任何優(yōu)勢(shì)。
1.LOCATE（'substr',str,pos）方法
SELECT`column`FROM`table`WHERELOCATE('keyword', `field`)>0
2.POSITION('substr' IN `field`)方法
SELECT`column`FROM`table`WHEREPOSITION('keyword'IN`filed`)
3.INSTR(`str`,'substr')方法
SELECT`column`FROM`table`WHEREINSTR(`field`,'keyword')>0
這幾種方法都試過后碍岔，發(fā)現(xiàn)百萬級(jí)別數(shù)據(jù)以上浴讯，時(shí)間是跟like差不多，并沒有解決問題蔼啦，因?yàn)槎紱]走到索引榆纽。
這種情況下想要實(shí)現(xiàn)后幾位模糊查詢并且速度要快，在此我想了兩個(gè)辦法询吴，一個(gè)是不需要mysql版本支持掠河，一個(gè)需要mysql5.7版本以上
第一種方法：新增一列字段，那個(gè)字段是你需要實(shí)現(xiàn)模糊查詢的倒序猛计，也就是原本是ABCD唠摹，那列字段就是DCBA
然后在那個(gè)字段添上索引
UPDATE tbl_ser_apply a set order_no_desc = REVERSE (SUBSTRING(a.order_no, -6))
ALTER TABLE `tbl_ser_apply` ADD INDEX order_no_desc ( `order_no_desc` )
我這邊設(shè)的是后六位? 也就是我把之前字段的后6位倒序后存入新的字段，也可以整個(gè)字段倒序后存入新的字段
select a.*,a1.id as id2,a1.order_no as orderNo2,a1.tran_amt as tranAmt2,a1.fee_amt as feeAmt2,a1.repayment_date_req as repaymentDateReq2
? ,a1.status as status2,a1.create_time as createTime2,a1.update_time as updateTime2
? from (
? select tsa.id,tsa.order_no as orderNo,tsa.repayment_date_req as repaymentDateReq,tsa.`status`,tsa.fee_state as feeState,tsa.repayment_flag as repaymentFlag,
? tsa.capital_return_flag as capitalReturnFlag,tsa.tran_amt as tranAmt,tsa.fee_amt as feeAmt,tsa.capital_returned_amont as capitalReturnedAmont,
? tsa.wait_amt as waitAmt,tsa.back_charge_amt as backChargeAmt,tsa.create_time as createTime,tui.real_name as realName,tui.mobile_no as mobileNo,
? tc.bank_card_no as bankCardNo,tmi.merchant_name as merchantName,tui.mer_no as merNo,tsa.reserved1 as reserved1,tsa.parent_id as parentId,tc.bank_name as bankName,tc.holder_name as holderName,
? tc.certificate_no as certificateNo
? from tbl_ser_apply as tsa LEFT JOIN tbl_user_info as tui on tsa.userid=tui.id LEFT JOIN tbl_merchant_inf as tmi on
? tmi.merchant_no=tui.mer_no? LEFT JOIN tbl_cusinfo tc on tc.id=tsa.cusInf_id where tsa.order_no_desc like? REVERSE('%372191')
? ORDER BY tsa.create_time desc ) a? LEFT JOIN tbl_ser_apply a1 on? a.parentId=a1.id
我的整個(gè)sql是這樣的
實(shí)際上最后查詢的時(shí)候是這樣
where tsa.order_no_desc like? REVERSE('%372191')
需要修改sql和java代碼奉瘤，查詢的是新增反向字段勾拉，而不是原來的字段
這樣就能實(shí)現(xiàn)走索引
原來的sql不走索引的情況下查詢出來需要20S，優(yōu)化后只需要0.049S
這種方法適合mysql5.7以下版本盗温，這樣能大大加快模糊查詢速度藕赞，而且能到1000W以上應(yīng)該都是沒問題的
第二種方法需要mysql5.7以上版本支持，用到虛擬列的方法卖局，原理跟上述方法一樣
alter table tbl_ser_apply add column virtual_col varchar(20) as (REVERSE (SUBSTRING(tbl_ser_apply.order_no, -6)));
ALTER TABLE `tbl_ser_apply` ADD INDEX ?virtual_col ( `?virtual_col` )
在MySQL 5.7中斧蜕，支持兩種Generated Column，即Virtual Generated Column和Stored Generated Column砚偶，前者只將Generated Column保存在數(shù)據(jù)字典中（表的元數(shù)據(jù)）批销，并不會(huì)將這一列數(shù)據(jù)持久化到磁盤上；后者會(huì)將Generated Column持久化到磁盤上染坯，而不是每次讀取的時(shí)候計(jì)算所得均芽。很明顯，后者存放了可以通過已有數(shù)據(jù)計(jì)算而得的數(shù)據(jù)单鹿，需要更多的磁盤空間掀宋，與Virtual Column相比并沒有優(yōu)勢(shì)，因此，MySQL 5.7中劲妙，不指定Generated Column的類型湃鹊，默認(rèn)是Virtual Column。
如果需要Stored Generated Golumn的話是趴，可能在Virtual Generated Column上建立索引更加合適
綜上涛舍，一般情況下，都使用Virtual Generated Column唆途，這也是MySQL默認(rèn)的方式
語(yǔ)法：
[ GENERATED ALWAYS ] AS ( ) [ VIRTUAL|STORED ]
[ UNIQUE [KEY] ] [ [PRIMARY] KEY ] [ NOT NULL ] [ COMMENT ]
這樣做比上一個(gè)方法好的地方是富雅，不需要修改java代碼，只需要修改很小一部分的sql語(yǔ)句即可肛搬，上一個(gè)方法其實(shí)實(shí)現(xiàn)后要修改的java代碼要不少没佑，而且每次新增修改刪除時(shí)，都要加上這個(gè)字段的代碼温赔，而新增虛擬列的話蛤奢，那一列的字段是自動(dòng)添加修改，通過計(jì)算得出的陶贼，所以代碼完全不需要修改啤贩，只需要修改操作原來字段的sql即可。
select a.*,a1.id as id2,a1.order_no as orderNo2,a1.tran_amt as tranAmt2,a1.fee_amt as feeAmt2,a1.repayment_date_req as repaymentDateReq2
? ,a1.status as status2,a1.create_time as createTime2,a1.update_time as updateTime2
? from (
? select tsa.id,tsa.order_no as orderNo,tsa.repayment_date_req as repaymentDateReq,tsa.`status`,tsa.fee_state as feeState,tsa.repayment_flag as repaymentFlag,
? tsa.capital_return_flag as capitalReturnFlag,tsa.tran_amt as tranAmt,tsa.fee_amt as feeAmt,tsa.capital_returned_amont as capitalReturnedAmont,
? tsa.wait_amt as waitAmt,tsa.back_charge_amt as backChargeAmt,tsa.create_time as createTime,tui.real_name as realName,tui.mobile_no as mobileNo,
? tc.bank_card_no as bankCardNo,tmi.merchant_name as merchantName,tui.mer_no as merNo,tsa.reserved1 as reserved1,tsa.parent_id as parentId,tc.bank_name as bankName,tc.holder_name as holderName,
? tc.certificate_no as certificateNo
? from tbl_ser_apply as tsa LEFT JOIN tbl_user_info as tui on tsa.userid=tui.id LEFT JOIN tbl_merchant_inf as tmi on
? tmi.merchant_no=tui.mer_no? LEFT JOIN tbl_cusinfo tc on tc.id=tsa.cusInf_id where tsa.virtual_col like? '372191%'
? ORDER BY tsa.create_time desc ) a? LEFT JOIN tbl_ser_apply a1 on? a.parentId=a1.id
