在MySQL中，模糊查询肯定要使用like关键字，然后在加 %%，是代表前模糊还是后模糊。数据量小的情况下，不容易看出查询的效率，但是数据量达到百万级，千万级甚至更高的时候，查询的效率就很容易显现出来了，此时，查询效率就显得很重要了，接下来，就要看你如何优化了。
 
 
前面讲过
 
MySQL - 如何提高SQL的查询效率（where条件优化）
 
全局检索
 
建立索引的情况下
 
一般情况下like模糊查询的写法为（field已建立索引）：
 
SELECT `column` FROM `table` WHERE `field` like '%keyword%';
 
上面的语句用explain解释来看，SQL语句并未用到索引，而且是全表搜索，如果在数据量超大的时候，可想而知最后的效率会是这样，和没有加索引是没什么区别的。
 
对比下面的写法：
 
SELECT `column` FROM `table` WHERE `field` like 'keyword%';
 
这样的写法用explain解释看到，SQL语句使用了索引，搜索的效率大大的提高了！
 
但是有的时候，我们在做模糊查询的时候，并非要想查询的关键词都在开头，所以如果不是特别的要求，"keywork%"并不合适所有的模糊查询
 
这个时候，我们可以考虑用其他的方法。
 
通过函数达到模糊查询的效果
 
LOCATE（'substr',str,pos）
 
举例：
 
SELECT LOCATE('xbar',`foobar`); 
###返回0 

SELECT LOCATE('bar',`foobarbar`); 
###返回4

SELECT LOCATE('bar',`foobarbar`,5);
###返回7
 
解释：
 
返回 substr 在 str 中第一次出现的位置，如果 substr 在 str 中不存在，返回值为 0 。
 
如果pos存在，返回 substr 在 str 第pos个位置后第一次出现的位置，如果 substr 在 str 中不存在，返回值为0。
 
解决模糊查询方案：
 
SELECT `column` FROM `table` WHERE LOCATE('keyword', `field`)>0
 
备注：keyword是要搜索的内容，field为被匹配的字段，查询出所有存在keyword的数据
 
POSITION('substr' IN `field`)
 
position可以看做是locate的别名，功能跟locate一样：
 
SELECT `column` FROM `table` WHERE POSITION('keyword' IN `filed`)
 
INSTR(`str`,'substr')
 
SELECT `column` FROM `table` WHERE INSTR(`field`, 'keyword' )>0 
 
FIND_IN_SET(str1,str2)
 
返回str2中str1所在的位置索引，其中str2必须以","分割开。
 
SELECT * FROM `person` WHERE FIND_IN_SET('apply',`name`);
 

 '01,05,0b,0e' like '%01%' 
 改成
 CHARINDEX('01', '01,05,0b,0e')>0

例子如下：
 SELECT ROW_NUMBER() OVER(ORDER BY OCCUR_DATE, SERIAL_NO) AS REC_NUM,
       SERIAL_NO, FRT_BIZ, CUST_CODE, CUST_NAME, CURR_STATUS, CURR_PHASE, 
       OP_USER, OP_NAME, CUACCT_CODE, INT_ORG,
       OCCUR_DATE, OCCUR_TIME, REMARK
       FROM FRT_BIZ_LOG
       WHERE OCCUR_DATE BETWEEN 20161001 AND 20161003
             AND (CHARINDEX(','+CONVERT(VARCHAR, INT_ORG)+',', ','+'0'+',') > 0 OR '0' IN ('0', '999') )
             AND CHARINDEX(FRT_BIZ, '01,05,0b,0e') > 0

在使用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代码，个人推荐使用第二种！

 
前言
 
 
      一直以来，对于搜索时模糊匹配的优化一直是个让人头疼的问题，好在强大pgsql提供了优化方案，下面就来简单谈一谈如何通过索引来优化模糊匹配    
 
 
案例
 
 
      我们有一张千万级数据的检查报告表，需要通过检查报告来模糊搜索某个条件，我们先创建如下索引：
 
 
 
  
CREATE INDEX lab_report_report_name_index ON lab_report USING btree (report_name);
 
 
 
 
      然后搜个简单的模糊匹配条件如 LIKE "血常规%"，可以发现查询计划生成如下，索引并没有被使用上，这是因为传统的btree索引并不支持模糊匹配
 
 
 
 
      查阅文档后发现，pgsql可以在Btree索引上指定操作符：text_pattern_ops、varchar_pattern_ops和 bpchar_pattern_ops，它们分别对应字段类型text、varchar和 char，官方解释为“它们与默认操作符类的区别是值的比较是严格按照字符进行而不是根据区域相关的排序规则。这使得这些操作符类适合于当一个数据库没有使用标准“C”区域时被使用在涉及模式匹配表达式（LIKE或POSIX正则表达式）的查询中。”， 有些抽象，我们先试试看。创建如下索引并查询刚才的条件 LIKE"血常规%"：（参考pgsql的文档 https://www.postgresql.org/docs/10/indexes-opclass.html）
 
 
 
  
CREATE INDEX lab_report_report_name_index ON lab.lab_report (report_name varchar_pattern_ops);
 
 
 
 
 
 
      发现确实可以走索引扫描 ，执行时间也从213ms优化到125ms，但是，如果搜索LIKE "%血常规%"就又会走全表扫描了！    这里我们引入本篇博客的主角"pg_trgm"和"pg_bigm"。
 
 
      创建这两个索引前分别需要引入如下两个扩展包 ：
 
 
 
  
CREATE EXTENSION pg_trgm;
CREATE EXTENSION pg_bigm;
 
 
 
 
 这两个索引的区别是：“pg_tigm”为pgsql官方提供的索引，"pg_tigm"为日本开发者提供。下面是详细的对比：（参考pg_bigm的文档 http://pgbigm.osdn.jp/pg_bigm_en-1-2.html）
 
 
Comparison with pg_trgm
 
 
The pg_trgm contrib module which provides full text search capability using 3-gram (trigram) model is included in PostgreSQL. The pg_bigm was developed based on the pg_trgm. They have the following differences:
 
 
Functionalities and Features
pg_trgm
pg_bigm
Phrase matching method for full text search
3-gram
2-gram
Available index
GIN and GiST
GIN only
Available text search operators
LIKE (~~), ILIKE (~~*), ~, ~*
LIKE only
Full text search for non-alphabetic language
(e.g., Japanese)
Not supported (*1)
Supported
Full text search with 1-2 characters keyword
Slow (*2)
Fast
Similarity search
Supported
Supported (version 1.1 or later)
Maximum indexed column size
238,609,291 Bytes (~228MB)
107,374,180 Bytes (~102MB)
 
(*1) You can use full text search for non-alphabetic language by commenting out KEEPONLYALNUM macro variable in contrib/pg_trgm/pg_trgm.h and rebuilding pg_trgm module. But pg_bigm provides faster non-alphabetic search than such a modified pg_trgm.
(*2) Because, in this search, only sequential scan or index full scan (not normal index scan) can run.
 
pg_bigm 1.1 or later can coexist with pg_trgm in the same database, but pg_bigm 1.0 cannot.
 
 
   如无特殊要求推荐使用"pg_bigm"，我们测试一下效果：
 
 
 
  
CREATE INDEX lab_report_report_name_index ON lab_report USING gin (report_name public.gin_bigm_ops);
 
 
 
 
 
 
 
可以使用位图索引扫描，对于本次案例，使用pg_trgm效果同pg_bigm。
 
 
以上
 
 
本文只是简单的介绍许多细节并未做深入的分析，欢迎留言指教或者讨论
 
 
 
 
转载于:https://www.cnblogs.com/y-yp/p/11218387.html