MySQL模糊查询like优化，再也⽤不着like+%了
我们都知道 InnoDB 在模糊查询数据时使⽤ "%xx" 会导致索引失效，但有时需求就是如此，类似这样的需求还有很多，例如，搜索引擎需要根基⽤户数据的关键字进⾏全⽂查，电⼦商务⽹站需要根据⽤户的查询条件，在可能需要在商品的详细介绍中进⾏查，这些都不是
B+树索引能很好完成的⼯作。
通过数值⽐较，范围过滤等就可以完成绝⼤多数我们需要的查询了。但是，如果希望通过关键字的匹配来进⾏查询过滤，那么就需要基于相似度的查询，⽽不是原来的精确数值⽐较，全⽂索引就是为这种场景设计的。
全⽂索引（Full-Text Search）是将存储于数据库中的整本书或整篇⽂章中的任意信息查出来的技术。它可以根据需要获得全⽂中有关章、节、段、句、词等信息，也可以进⾏各种统计和分析。
在早期的 MySQL 中，InnoDB 并不⽀持全⽂检索技术，从 MySQL 5.6 开始，InnoDB 开始⽀持全⽂检索。
倒排索引
index与match举例讲解
全⽂检索通常使⽤倒排索引（inverted index）来实现，倒排索引同 B+Tree ⼀样，也是⼀种索引结构。它在辅助表中存储了单词与单词⾃⾝在⼀个或多个⽂档中所在位置之间的映射，这通常利⽤关联数组实现，拥有两种表现形式：
inverted file index：{单词，单词所在⽂档的id}
full inverted index：{单词，（单词所在⽂档的id，再具体⽂档中的位置）}
上图为 inverted file index 关联数组，可以看到其中单词"code"存在于⽂档1,4中，这样存储再进⾏全⽂查询就简单了，可以直接根据Documents 得到包含查询关键字的⽂档；⽽ full inverted index 存储的是对，即（DocumentId,Position），因此其存储的倒排索引如下图，如关键字"code"存在于⽂档1的第6个单词和⽂档4的第8个单词。
相⽐之下，full inverted index 占⽤了更多的空间，但是能更好的定位数据，并扩充⼀些其他搜索特性。
全⽂检索
创建全⽂索引
1、创建表时创建全⽂索引语法如下：
CREATE TABLE table_name ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, author VARCHAR(200),
title VARCHAR(200), content TEXT(500), FULLTEXT full_index_name (col_name) ) ENGINE=InnoDB;
输⼊查询语句：
SELECT table_id, name, space from INFORMATION_SCHEMA.INNODB_TABLES
WHERE name LIKE 'test/%';
上述六个索引表构成倒排索引，称为辅助索引表。当传⼊的⽂档被标记化时，单个词与位置信息和关联的DOC_ID，根据单词的第⼀个字符的字符集排序权重，在六个索引表中对单词进⾏完全排序和分区。
2、在已创建的表上创建全⽂索引语法如下：
CREATE FULLTEXT INDEX full_index_name ON table_name(col_name);
使⽤全⽂索引
MySQL 数据库⽀持全⽂检索的查询，全⽂索引只能在 InnoDB 或 MyISAM 的表上使⽤，并且只能⽤于创建 char,varchar,text 类型的列。
其语法如下：
MATCH(col1,col2,...) AGAINST(expr[search_modifier])
search_modifier:
{
IN NATURAL LANGUAGE MODE
| IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
| IN BOOLEAN MODE
| WITH QUERY EXPANSION
}
全⽂搜索使⽤ MATCH()AGAINST()语法进⾏，其中，MATCH()采⽤逗号分隔的列表，命名要搜索的列。AGAINST()接收⼀个要搜索的字符串，以及⼀个要执⾏的搜索类型的可选修饰符。全⽂检索分为三种类型：⾃然语⾔搜索、布尔搜索、，下⾯将对各种查询模式进⾏介绍。
Natural Language
⾃然语⾔搜索将搜索字符串解释为⾃然⼈类语⾔中的短语，MATCH()默认采⽤ Natural Language 模式，其表⽰查询带有指定关键字的⽂档。
接下来结合demo来更好的理解Natural Language
SELECT
count(*) AS count
FROM
`fts_articles`
WHERE
MATCH ( title, body ) AGAINST ( 'MySQL' );
上述语句，查询 title,body 列中包含 'MySQL' 关键字的⾏数量。上述语句还可以这样写：
SELECT
count(IF(MATCH ( title, body )
against ( 'MySQL' ), 1, NULL )) AS count
FROM
`fts_articles`;
上述两种语句虽然得到的结果是⼀样的，但从内部运⾏来看，第⼆句SQL的执⾏速度更快些，因为第⼀句SQL（基于where索引查询的⽅式）还需要进⾏相关性的排序统计，⽽第⼆种⽅式是不需要的。
还可以通过SQL语句查询相关性：
SELECT
*,
MATCH ( title, body ) against ( 'MySQL' ) AS Relevance
FROM
fts_articles;
相关性的计算依据以下四个条件：
word 是否在⽂档中出现
word 在⽂档中出现的次数
word 在索引列中的数量
多少个⽂档包含该 word
对于 InnoDB 存储引擎的全⽂检索，还需要考虑以下的因素：
查询的 word 在 stopword 列中，忽略该字符串的查询
查询的 word 的字符长度是否在区间 [innodb_ft_min_token_size,innodb_ft_max_token_size] 内
如果词在 stopword 中，则不对该词进⾏查询，如对 'for' 这个词进⾏查询，结果如下所⽰：
SELECT
*,
MATCH ( title, body ) against ( 'for' ) AS Relevance
FROM
fts_articles;
可以看到，'for'虽然在⽂档 2，4中出现，但由于其是 stopword ，故其相关性为0
参数 innodb_ft_min_token_size 和 innodb_ft_max_token_size 控制 InnoDB 引擎查询字符的长度，当长度⼩
于 innodb_ft_min_token_size 或者长度⼤于 innodb_ft_max_token_size 时，会忽略该词的搜索。在 InnoDB 引擎中，参
数 innodb_ft_min_token_size 的默认值是3，innodb_ft_max_token_size的默认值是84
Boolean
布尔搜索使⽤特殊查询语⾔的规则来解释搜索字符串，该字符串包含要搜索的词，它还可以包含指定要求的运算符，例如匹配⾏中必须存在或不存在某个词，或者它的权重应⾼于或低于通常情况。
例如，下⾯的语句要求查询有字符串"Pease"但没有"hot"的⽂档，其中+和-分别表⽰单词必须存在，或者⼀定不存在。
select * from fts_test where MATCH(content) AGAINST('+Pease -hot' IN BOOLEAN MODE);
Boolean 全⽂检索⽀持的类型包括：
+：表⽰该 word 必须存在
-：表⽰该 word 必须不存在
(no operator)表⽰该 word 是可选的，但是如果出现，其相关性会更⾼
@distance表⽰查询的多个单词之间的距离是否在 distance 之内，distance 的单位是字节，这种全⽂检索的查询也称为 Proximity Search，如 MATCH(context) AGAINST('"Pease hot"@30' IN BOOLEAN MODE)语句表⽰字符串 Pease 和 hot 之间的距离需在30字节内
>：表⽰出现该单词时增加相关性
<：表⽰出现该单词时降低相关性
~：表⽰允许出现该单词，但出现时相关性为负
* ：表⽰以该单词开头的单词，如 lik*，表⽰可以是 lik，like，likes
" ：表⽰短语
下⾯是⼀些demo，看看 Boolean Mode 是如何使⽤的。
demo1：+ -
SELECT
*
FROM
`fts_articles`
WHERE
MATCH ( title, body ) AGAINST ( '+MySQL -YourSQL' IN BOOLEAN MODE );
上述语句，查询的是包含 'MySQL' 但不包含 'YourSQL' 的信息
demo2：no operator
SELECT
*
FROM
`fts_articles`
WHERE
MATCH ( title, body ) AGAINST ( 'MySQL IBM' IN BOOLEAN MODE );
上述语句，查询的 'MySQL IBM' 没有 '+'，'-'的标识，代表 word 是可选的，如果出现，其相关性会更⾼
demo3：@
SELECT
*
FROM
`fts_articles`
WHERE
MATCH ( title, body ) AGAINST ( '"DB2 IBM"@3' IN BOOLEAN MODE );
上述语句，代表 "DB2" ，"IBM"两个词之间的距离在3字节之内
demo4：> <
SELECT
*
FROM
`fts_articles`
WHERE
MATCH ( title, body ) AGAINST ( '+MySQL +(>database <DBMS)' IN BOOLEAN MODE );
上述语句，查询同时包含 'MySQL'，'database'，'DBMS' 的⾏信息，但不包含'DBMS'的⾏的相关性⾼于包含'DBMS'的⾏。
demo5: ~
SELECT
*
FROM
`fts_articles`
WHERE
MATCH ( title, body ) AGAINST ( 'MySQL ~database' IN BOOLEAN MODE );
上述语句，查询包含 'MySQL' 的⾏，但如果该⾏同时包含 'database'，则降低相关性。

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