mysql之like查询优化:
最近写项目的时候，有跨表做 like 查询的操作，主表大概有十万条数据 副表大概有 四十万的数据，差不多 1:4的关系，表结构如下： t_products
| 字段名 | 类型 | 说明 |
|---|---|---|
| id | int | ID |
| name | string | 名称 |
t_product_attributes
| 字段名 | 类型 | 说明 |
|---|---|---|
| id | int | ID |
| product_id | int | 产品ID |
| attribute | string | 属性 |
我用的laravel框架， 第一次直接 whereHas 通过 like 查询，结果 接口直接超时，报nginx 504，这是标准的 nginx 后端响应超时问题，所以想着不跨表查询，于是在 t_products 表 加了个字段 properties (text类型)，准备把副表中的属性全部用逗号追加起来，然后这样操作以后做like查询，果真要快很多，至少不会报超时问题，但是通过 explain 检查以后发现， type 为 All 查询，也就意味着要全表扫描，这样肯定不行，所以还是想用索引，最后想到用 全文索引(FullText)
通过如下命令给表添加全文索引:
ALTER TABLE t_products ADD FULLTEXT INDEX t_products (properties) with parser ngram;
查询：
①原生 select * from t_products where (match(properties) against('蓝色')) and name like '%手机%';
②对于laravel框架，需要whereRaw操作： if (isset($params['attribute'])) { $query = $query->whereRaw("match(properties) against('".$params['attribute']."')"); }
通过explain 检查发现 type 为 fulltext，证明用到了full-text-index，响应时间也更快
关于全文索引(FULLTEXT indexes):
FULLTEXT索引仅支持 InnoDB和 MyISAM表，并且只能包含 CHAR、 VARCHAR和 TEXT列。索引总是发生在整个列上；不支持列前缀索引，如果指定，任何前缀长度都将被忽略。 在MySQL 5.6版本以前,只有MyISAM存储引擎支持全文引擎， 在5.6版本中,InnoDB加入了对全文索引的支持,但是不支持中文全文索引， 在5.7.6版本,MySQL内置了ngram全文解析器,用来支持亚洲语种的分词，并且对MyISAM和InnoDB引擎有效。
在使用前请确认自己的mysql版本, 可以使用 select version() 查看mysql的版本。
在命令行执行：
show VARIABLES like '%innodb_ft_%';
Variable_name Value innodb_ft_aux_table // 设置调式表
innodb_ft_cache_size 8000000 //定义每个表的全文索引内存大小
innodb_ft_enable_diag_print OFF
innodb_ft_enable_stopword ON //是否允许使用停止词
innodb_ft_max_token_size 84 //默认84，表示最大84个字符作为一个关键词，限制该值可减少全文索引的大小
innodb_ft_min_token_size 3 //默认3，表示最小3个字符作为一个关键词，增大该值可减少全文索引的大小
innodb_ft_num_word_optimize 2000
innodb_ft_result_cache_limit 2000000000
innodb_ft_server_stopword_table
innodb_ft_sort_pll_degree 2
innodb_ft_total_cache_size 640000000 //定义所有表的全文索引内存大小
innodb_ft_user_stopword_table //定义的停止表，默认为 information_schema.INNODB_FT_DEFAULT_STOPWORD ，可以select * from information_schema.INNODB_FT_DEFAULT_STOPWORD,查看所有的停止词
可以根据实际情况调大 innodb_ft_result_cache_limit 的值
对于innodDb存储引擎，要使用全文索引就必须用到如下格式(需要加 with parser ngram 参数)的命令创建索引： ALTER TABLE t_products ADD FULLTEXT INDEX t_products (properties) with parser ngram;
关于 ngram：
在全文索引中，n-gram就是一段文字里面连续的n个字的序列，当使用 ngram 分词解析器以后，innodb_ft_min_token_size和innodb_ft_max_token_size 无效
关于全文索引模式：
全文索引主要包括 自然语言模式 IN NATURAL LANGUAGE MODE(默认模式，可以不写) 和布尔模式 IN BOOLEAN MODE 其中boolean模式中,
“+”表示必须包含
“-”表示必须排除
“>”表示出现该单词时增加相关性
“<”表示出现该单词时降低相关性
“*”表示通配符
“~”允许出现该单词，但是出现时相关性为负
“""”表示短语 //该模式下 和 IN NATURAL LANGUAGE MODE 类似
还有一种是：WITH QUERY EXPANSION 这个模式没用过 这种模式的查询分为两个阶段： 第一阶段：根据搜索的单词进行全文索引查询。 第二阶段：根据第一阶段产生的分词再进行一次全文检索的查询
模式使用：
直接在against()参数后空格连接即可
比如上面的查询可以改为： if (isset($params['attribute'])) { $query = $query->whereRaw('match(properties) against("'.$params['attribute'].'" IN BOOLEAN MODE)'); }
注意：
在使用过程中发现，如果 against 里只有一个字，那么可能查不到结果，原因是： 默认的 ngram_token_size 大小为2，这个值定义了分词的大小，这个值可以在 mysql的配置文件里，[mysqld]下重新配置,分词的SIZE越小，索引的体积就越大，所以要根据自身情况来设置合适的大小。 通过 show variables like '%ngram%'; 可以查看默认值为2
通过select match against 来计算全文索引相关性：
select id as '数据ID', match(properties) against ('红色') as '全文索引相关性' from t_products where (match(properties) against('红色')) and name like '%手机%';
关于停止词表：
SET GLOBAL innodb_ft_user_stopword_table = "test/my_innodb_ft_stopword"; //test为数据库，my_innodb_ft_stopword 为test库下的表，虽然执行成功但是通过：show VARIABLES like '%innodb_ft_%'; 查看还是没有值 select * from information_schema.innodb_ft_index_table;