mysqllikein效率_MySqllike模糊查询优化后缀模糊查询优化，
虚拟列⾛索引优。。。
在使⽤msyql进⾏模糊查询的时候，很⾃然的会⽤到like语句，通常情况下，在数据量⼩的时候，不容易看出查询的效率，但在数据量达到百万级，千万级的时候，查询的效率就很容易显现出来。这个时候查询的效率就显得很重要！
⼀般情况下like模糊查询的写法为(field已建⽴索引)：
SELECT`column`FROM`table`WHERE`field`like'%keyword%';
上⾯的语句⽤explain解释来看，SQL语句并未⽤到索引，⽽且是全表搜索，如果在数据量超⼤的时候，可想⽽知最后的效率会是这样
对⽐下⾯的写法：
SELECT`column`FROM`table`WHERE`field`like'keyword%';
这样的写法⽤explain解释看到，SQL语句使⽤了索引，搜索的效率⼤⼤的提⾼了！
但是有的时候，我们在做模糊查询的时候，并⾮要想查询的关键词都在开头，所以如果不是特别的要求，"keywork%"并不合适所有的模糊查询
我在⽹上搜索时发现很多mysql函数⽤来解决这个问题，我测试出来的结果是跟like相⽐并没有任何优势。
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
这⼏种⽅法都试过后，发现百万级别数据以上，时间是跟like差不多，并没有解决问题，因为都没⾛到索引。
这种情况下想要实现后⼏位模糊查询并且速度要快，在此我想了两个办法，⼀个是不需要mysql版本⽀持，⼀个需要mysql5.7版本以上
第⼀种⽅法：新增⼀列字段，那个字段是你需要实现模糊查询的倒序，也就是原本是ABCD，那列字段就是DCBA
然后在那个字段添上索引
UPDATE tbl_ser_apply a set order_no_desc = REVERSE (der_no, -6))
ALTER TABLE `tbl_ser_apply` ADD INDEX order_no_desc ( `order_no_desc` )
我这边设的是后六位  也就是我把之前字段的后6位倒序后存⼊新的字段，也可以整个字段倒序后存⼊新的字段
select a.*,a1.id as der_no as an_amt as tranAmt2,a1.fee_amt as payment_date_req as repaymentDateReq2
,a1.status as ate_time as createTime2,a1.update_time as updateTime2
from (
select tsa.der_no as payment_date_req as repaymentDateReq,tsa.`status`,tsa.fee_state as
payment_flag as repaymentFlag,
tsa.capital_return_flag as an_amt as tranAmt,tsa.fee_amt as feeAmt,tsa.capital_returned_amont as capitalReturnedAmont,
tsa.wait_amt as waitAmt,tsa.back_charge_amt as ate_time as al_name as bile_no as mobileNo,
tc.bank_card_no as hant_name as _no as served1 as
reserved1,tsa.parent_id as parentId,tc.bank_name as bankName,tc.holder_name as holderName,
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
REVERSE('%372191')
ORDER ate_time desc ) a  LEFT JOIN tbl_ser_apply a1 on  a.parentId=a1.id
我的整个sql是这样的
实际上最后查询的时候是这样
der_no_desc like  REVERSE('%372191')
需要修改sql和java代码，查询的是新增反向字段，⽽不是原来的字段
这样就能实现⾛索引
原来的sql不⾛索引的情况下查询出来需要20S，优化后只需要0.049Sjoin和in哪个查询更快
这种⽅法适合mysql5.7以下版本，这样能⼤⼤加快模糊查询速度，⽽且能到1000W以上应该都是没问题的
第⼆种⽅法需要mysql5.7以上版本⽀持，⽤到虚拟列的⽅法，原理跟上述⽅法⼀样
alter table tbl_ser_apply add column virtual_col varchar(20) as (REVERSE (SUBSTRING(tbl_der_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保存在数据字典中(表的元数据)，并不会将这⼀列数据持久化到磁盘上；后者会将Generated Column持久化到磁盘上，⽽不是每次读取的时候计算所得。很明显，后者存放了可以通过已有数据计算⽽得的数据，需要更多的磁盘空间，与Virtual Column相⽐并没有优势，因此，MySQL 5.7中，不指定Generated Column的类型，默认是Virtual Column。
如果需要Stored Generated Golumn的话，可能在Virtual Generated Column上建⽴索引更加合适
综上，⼀般情况下，都使⽤Virtual Generated Column，这也是MySQL默认的⽅式
语法：
[ GENERATED ALWAYS ] AS ( ) [ VIRTUAL|STORED ]
[ UNIQUE [KEY] ] [ [PRIMARY] KEY ] [ NOT NULL ] [ COMMENT ]
这样做⽐上⼀个⽅法好的地⽅是，不需要修改java代码，只需要修改很⼩⼀部分的sql语句即可，上⼀个⽅法其实实现后要修改的java代码要不少，⽽且每次新增修改删除时，都要加上这个字段的代码，⽽新增虚拟列的话，那⼀列的字段是⾃动添加修改，通过计算得出的，所以代码完全不需要修改，只需要修改操作原来字段的sql即可。
select a.*,a1.id as der_no as an_amt as tranAmt2,a1.fee_amt as payment_date_req as repaymentDateReq2
,a1.status as ate_time as createTime2,a1.update_time as updateTime2
from (
select tsa.der_no as payment_date_req as repaymentDateReq,tsa.`status`,tsa.fee_state as
payment_flag as repaymentFlag,
tsa.capital_return_flag as an_amt as tranAmt,tsa.fee_amt as feeAmt,tsa.capital_returned_amont as capitalReturnedAmont,

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。