%xxx%这种方式对于数据量少的时候，我们倒可以随意用，但是数据量大的时候，我们就体验到了查询性能的问题，像老化的车子艰难趴着坡一样，并且这种方式并未使用到索引，而是全表扫描
mysql 高效模糊查询 代替like而对于xxx% 或者%xxx方式，explain一下可以发现查询使用到了索引，性能提升了不少，当然这种方式不适用与所有的查询场景。
可以采取以下的函数进行查询。
LOCATE('substr',str,pos)方法
POSITION('substr' IN field)方法
INSTR(str,'substr')方法
查询效率比如果：table.field like '%AAA%' 可以改为locate ('AAA' , table.field) > 0
返回子串substr在字符串str第一个出现的位置，如果substr不是在str里面，返回0.
mysql> select LOCATE('bar', 'foobarbar'); -> 4 mysql> select LOCATE('xbar', 'foobar'); -> 0
该函数是多字节可靠的。
返回子串substr在字符串str第一个出现的位置，从位置pos开始。如果substr不是在str里面，返回0。
mysql> select LOCATE('bar', 'foobarbar',5); -> 7mysql like模糊查询提高效率的奇葩方法
一张表大概40万左右的数据，用like模糊查询title字段，很慢，title字段已经建立了索引，mysql 对 someTitle% 这样的模糊查询在有索引的前提下是很快的。
所以下面这两台sql语句差别就很大了
$sql1 = "...... title like someTitle%" (话费0.001秒)
$sql2 = "...... title like %someTitle%" (话费0.8秒)
这两句的效率相差了800倍，这很可观啊。
所以我有个想法：在不用分词的方法的前提下，把存储的title字段，加一个特别的前缀，比如"im_prefix"，比如一条记录的title="我是标题党"，那么存储的时候就存储为"im_prefix我是标题党"。
这样一来，我们要模糊查找"标题党"这个关键词的时候，就把sql写成这样：
$sql1 = "...... title like im_prefix%标题党%" (花费0.001秒)，前台显示数据的时候，自然把取到的title过滤掉"im_prefix"这个前缀了

在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

前言
一直以来，对于搜索时模糊匹配的优化一直是个让人头疼的问题，好在强大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 Featurespg_trgmpg_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。
以上
本文只是简单的介绍许多细节并未做深入的分析，欢迎留言指教或者讨论