MySql like模糊查询优化 后缀模糊查询走索引 百万级别数据优化

在使用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版本以上，我测试的数据都是200W条数据以上的。

 

第一种方法：新增一列字段，那个字段是你需要实现模糊查询的倒序，也就是原本是ABCD，那列字段就是DCBA

然后在那个字段添上索引

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` )

我这边设的是后六位  也就是我把之前字段的后6位倒序后存入新的字段，也可以整个字段倒序后存入新的字段

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

我的整个sql是这样的

实际上最后查询的时候是这样

where tsa.order_no_desc like  REVERSE('%372191')

相当于是查询你新增字段，sql上加上reverse函数，这样子%就在后面，而且能实现整个字段的模糊查询

这种做法需要修改sql和java代码，查询的是新增反向字段，而不是原来的字段

这样就能实现走索引

原来的sql不走索引的情况下查询出来需要20S，优化后只需要0.049S

这种方法适合mysql5.7以下版本，这样能大大加快模糊查询速度，而且能到1000W以上应该都是没问题的

第二种方法需要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保存在数据字典中（表的元数据），并不会将这一列数据持久化到磁盘上；后者会将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 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

这是我新增虚拟列后的查询语句，同样也是查询的虚拟列的字段。

相当于查的是这个where tsa.virtual_col like  '372191%'，这样也能实现%在后面，也就能走索引。

经过我的测试后，原来不走索引是20S 用第一种方法是0.049s 用第二种方法的话是0.1S  虽然慢了0.05S 那是计算数据的时间，但这样的方案已经大大缩短了模糊查询时间，而且不需要修改java代码，个人推荐使用第二种！