MySQL 全文索引
问题：如下语句不走索引，效率低，该如何解决？
Select * From itzone_yg where name like "%x123%";
①. MySQL 5.6 中支持的新特性，MySQL 5.7.6 之后增加了自然语言处理能力（ngram 插件），更加强大
②. 目前只有 char，varchar，text 类型的字段可以创建全文索引
③. 全文索引目前只支持 MySQL 的 InnoDB 与 MyISAM 引擎
生产实际问题（全文索引）
问题：现在有一个大表，一亿条数据，没有全文索引，如何整改？
答：建议直接创建 Fulltext 索引，不建议重新创建表再导数据
全文索引相关参数
# mysql 全文索引能找到的词默认最小长度是 4 个字节，过小会失去意义
mysql> show variables like 'ft_min_word_len';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| ft_min_word_len | 4 |
+-----------------+-------+
1 row in set (0.00 sec)
# ngram 分词大小（中文字数）
# 有关全文解析器 ngram 对文本分词的链接 https://www.cnblogs.com/li-sx/p/10141191.html
mysql> show variables like 'ngram%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| ngram_token_size | 2 |
+------------------+-------+
1 row in set (0.00 sec)
# 分词过程如下
信息系统 ===> 信息，系统，息系，信统，息统，信系
创建全文索引
# 创建全文索引方法
mysql> alter table itzone_yg add fulltext index idx_itzone_yg_identifier(identifier) with parser ngram;
Query OK, 0 rows affected, 1 warning (1.48 sec)
Records: 0 Duplicates: 0 Warnings: 1
网剧搜索案例 - 如何使用全文索引
# 创建表结构
mysql> create table itetz(
-> id int primary key auto_increment not null,
-> title varchar(300),
-> content text
-> ) engine=innodb default charset=utf8;
Query OK, 0 rows affected (0.02 sec)
# 创建全文索引
mysql> alter table itetz add fulltext index idx_itetz_title_content(title,content) with parser ngram;
Query OK, 0 rows affected, 1 warning (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 1
# 插入数据
INSERT INTO `itetz` VALUES (1,'进击的巨人(动漫)','107年前（743年），世界上突然出现了人类的天敌“巨人”。面临着生存危机而残存下来的人类>逃到了一个地方，盖起了三重巨大的城墙。人们在这隔绝的环境里享受了一百多年的和平，直到艾伦·耶格尔十岁那年，60米高的“超大型巨人”突然出现
，以压倒性的力量破坏城门，其后瞬间消失，凶残的巨人们成群的冲进墙内捕食人类。'),(2,'暮光之城(电视剧)','传说太阳下山后，有恶鬼出没吃人>。亦有猎鬼人斩杀恶鬼、保护人们。'),(3,'七大罪(动漫)','故事发生于布里塔利亚大陆上的里昂尼丝王国，国王预言传说的圣战将再启，王国的圣骑>士们为了备战而进行军备强化，进而发动政变囚禁国王、压榨奴役国民。担忧着国家现状的第三王女伊丽莎白，将救国的希望寄托于传说中的逆贼——由>七名凶恶的大罪人组成的最强骑士团七大罪，从而独自踏上寻找的旅程。注定无果的旅程中，疲惫不堪的伊丽莎白误入某个酒馆，被身为店主的金发少>年救下，这名少年正是七大罪的团长梅利奥达斯。在将公主从追兵的危机中解救出来后，他们踏上了寻找昔日的同伴、拯救王国未来的旅途。'),(4,'赘
婿(电视剧)','主人公宁毅（郭麒麟 饰）出身微寒，却始终积极进取，从赋诗、从商，到习武、齐家，从最初安逸度日的赘婿，逐渐承担起更多的责>任，帮苏檀儿（宋轶 饰）一起创业，又助身边的亲朋好友们实现了各自的理想，济人困厄、授人以渔。后面临家国大事，宁毅一行人屡次身处险境，但
最终靠勇气和智慧守护了霖安城。正所谓“小小赘婿，大大天地”，宁毅从一名只想独善其身的赘婿，成长为真正胸怀天下之人，而其他角色也都有着各>自的成长，最终为大的时局贡献了自己的力量 ');
# 设置全文检索的表源 库 / 表
mysql> SET GLOBAL innodb_ft_aux_table = 'ethanz/itetz';
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like "innodb_ft_aux_table";
+---------------------+--------------+
| Variable_name | Value |
+---------------------+--------------+
| innodb_ft_aux_table | ethanz/itetz |
+---------------------+--------------+
1 row in set (0.00 sec)
# 查看分词效果
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
+--------+--------------+-------------+-----------+--------+----------+
| WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+--------+--------------+-------------+-----------+--------+----------+
| (动 | 2 | 4 | 2 | 2 | 15 |
| (动 | 2 | 4 | 2 | 4 | 9 |
| (电 | 3 | 3 | 1 | 3 | 12 |
| 07 | 2 | 2 | 1 | 2 | 25 |
| 0米 | 2 | 2 | 1 | 2 | 306 |
| 10 | 2 | 2 | 1 | 2 | 24 |
| 3年 | 2 | 2 | 1 | 2 | 38 |
| 43 | 2 | 2 | 1 | 2 | 37 |
| 60 | 2 | 2 | 1 | 2 | 305 |
............
..............................
..............................................
| ，有 | 3 | 3 | 1 | 3 | 45 |
| ，济 | 5 | 5 | 1 | 5 | 300 |
| ，王 | 4 | 4 | 1 | 4 | 117 |
| ，疲 | 4 | 4 | 1 | 4 | 456 |
| ，盖 | 2 | 2 | 1 | 2 | 168 |
| ，直 | 2 | 2 | 1 | 2 | 264 |
| ，而 | 5 | 5 | 1 | 5 | 564 |
| ，被 | 4 | 4 | 1 | 4 | 504 |
| ，这 | 4 | 4 | 1 | 4 | 543 |
| ，进 | 4 | 4 | 1 | 4 | 174 |
| ，逐 | 5 | 5 | 1 | 5 | 167 |
+--------+--------------+-------------+-----------+--------+----------+
650 rows in set (0.00 sec)
# 当 Optimize 这个表后，INNODB_FT_INDEX_CACHE 表中的数据将会存入 INNODB_FT_INDEX_TABLE 表中
# 要先将 innodb_optimize_fulltext_only 参数设置为 On
# https://dev.mysql.com/doc/refman/5.6/en/information-schema-innodb-ft-index-table-table.html
mysql> SET GLOBAL innodb_optimize_fulltext_only=ON;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM information_schema.INNODB_FT_INDEX_TABLE;
Empty set (0.00 sec)
# 使用 optimize 语句即可落盘
mysql> optimize table itetz;
+--------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------+----------+----------+----------+
| ethanz.itetz | optimize | status | OK |
+--------------+----------+----------+----------+
1 row in set (0.01 sec)
mysql> SELECT * FROM information_schema.INNODB_FT_INDEX_TABLE limit 5;
+------+--------------+-------------+-----------+--------+----------+
| WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+------+--------------+-------------+-----------+--------+----------+
| (动 | 2 | 4 | 2 | 2 | 15 |
| (动 | 2 | 4 | 2 | 4 | 9 |
| (电 | 3 | 3 | 1 | 3 | 12 |
| 07 | 2 | 2 | 1 | 2 | 25 |
| 0米 | 2 | 2 | 1 | 2 | 306 |
+------+--------------+-------------+-----------+--------+----------+
5 rows in set (0.00 sec)
# 落盘后，Cache 表中无数据
mysql> select * from information_schema.INNODB_FT_INDEX_CACHE limit 5;
Empty set (0.00 sec)
自然语言模式查询
# ============================= 自然语言模式下的检索 ========================= #
mysql> select count(*) from itetz where match(title,content) against('动漫' in natural language mode);
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
# 列出相关度，“动漫恶鬼” 四个字排列在随机排列后的排列组合，与第二条记录中的全文索引相关性最大
mysql> select id,match(title,content) against('动漫恶鬼' in natural language mode) from itetz;
+----+-----------------------------------------------------------------------+
| id | match(title,content) against('动漫恶鬼' in natural language mode) |
+----+-----------------------------------------------------------------------+
| 1 | 0.0906190574169159 |
| 2 | 0.7249524593353271 |
| 3 | 0.0906190574169159 |
| 4 | 0 |
+----+-----------------------------------------------------------------------+
4 rows in set (0.00 sec)
布尔模式查询
# =============================== 布尔语言模式下的检索 ========================= #
# 检索既有电视，又有恶鬼的记录
mysql> select id,match(title,content) against('+电视 +恶鬼' in boolean mode) from itetz;
+----+-----------------------------------------------------------------+
| id | match(title,content) against('+电视 +恶鬼' in boolean mode) |
+----+-----------------------------------------------------------------+
| 1 | 0 |
| 2 | 0.8155715465545654 |
| 3 | 0 |
| 4 | 0 |
+----+-----------------------------------------------------------------+
# 反观自然语言模式下的检索就会匹配到无关记录
mysql> select id,match(title,content) against('电视恶鬼' in natural language mode) from itetz;
+----+-----------------------------------------------------------------------+
| id | match(title,content) against('电视恶鬼' in natural language mode) |
+----+-----------------------------------------------------------------------+
| 1 | 0 |
| 2 | 0.8155715465545654 |
| 3 | 0 |
| 4 | 0.0906190574169159 |
+----+-----------------------------------------------------------------------+
4 rows in set (0.00 sec)
# 检索有电视，但是无恶鬼的记录
# 先找出没有恶鬼（-恶鬼），再找电视（+电视），效率高
mysql> select id,match(title,content) against('-恶鬼 +电视' in boolean mode) from itetz;
+----+-----------------------------------------------------------------+
| id | match(title,content) against('-恶鬼 +电视' in boolean mode) |
+----+-----------------------------------------------------------------+
| 1 | 0 |
| 2 | 0 |
| 3 | 0 |
| 4 | 0.0906190574169159 |
+----+-----------------------------------------------------------------+
4 rows in set (0.00 sec)
# 增加相关度，带有恶鬼的记录相关度会明显增加，但不带恶鬼的记录不会被判断为绝对无关
mysql> select id,match(title,content) against('>恶鬼 +电视' in boolean mode) from itetz;
+----+-----------------------------------------------------------------+
| id | match(title,content) against('>恶鬼 +电视' in boolean mode) |
+----+-----------------------------------------------------------------+
| 1 | 0 |
| 2 | 1.8155715465545654 |
| 3 | 0 |
| 4 | 0.0906190574169159 |
+----+-----------------------------------------------------------------+
4 rows in set (0.00 sec)
# 增加相关度后，查找到带有恶鬼的记录的顺序也会靠前
mysql> select * from itetz where match(title,content) against('>恶鬼 +电视' in boolean mode)\G
*************************** 1. row ***************************
id: 2
title: 暮光之城（电视剧）
content: 传说太阳下山后，有恶鬼出没吃人。亦有猎鬼人斩杀恶鬼、保护人们。
*************************** 2. row ***************************
id: 4
title: 赘婿（电视剧）
content: 主人公宁毅（郭麒麟 饰）出身微寒，却始终积极进取，从赋诗、从商，到习武、齐家，从最初安逸度日的赘婿，逐渐承担起更多的责任，帮苏檀儿（宋轶 饰）一起创业，又助身边的亲朋好友们实现了各自的理想，济人困厄、授人以渔。后面临家国大事，宁毅一行人屡次身处险境，但最终靠勇气和智慧守护了霖安城。正所谓“小小赘婿，大大天地”，宁毅从一名只想独善其身的赘婿，成长为真正胸怀天下之人，而其他角色也都有着各自的成长，最终为大的时局贡献了自己的力量
2 rows in set (0.00 sec)
# 降低相关度，带有恶鬼的记录相关度会明显降低，甚至为负，同样更好理解，不带恶鬼的记录不会被判断为绝对无关
mysql> select id,match(title,content) against('<恶鬼 +电视' in boolean mode) from itetz;
+----+-----------------------------------------------------------------+
| id | match(title,content) against('<恶鬼 +电视' in boolean mode) |
+----+-----------------------------------------------------------------+
| 1 | 0 |
| 2 | -0.18442848324775696 |
| 3 | 0 |
| 4 | 0.0906190574169159 |
+----+-----------------------------------------------------------------+
4 rows in set (0.00 sec)
# 降低相关度后，查找到带有恶鬼的记录的顺序也会靠后
mysql> select * from itetz where match(title,content) against('<恶鬼 +电视' in boolean mode)\G
*************************** 1. row ***************************
id: 4
title: 赘婿（电视剧）
content: 主人公宁毅（郭麒麟 饰）出身微寒，却始终积极进取，从赋诗、从商，到习武、齐家，从最初安逸度日的赘婿，逐渐承担起更多的责任，帮苏檀儿（宋轶 饰）一起创业，又助身边的亲朋好友们实现了各自的理想，济人困厄、授人以渔。后面临家国大事，宁毅一行人屡次身处险境，但最终靠勇气和智慧守护了霖安城。正所谓“小小赘婿，大大天地”，宁毅从一名只想独善其身的赘婿，成长为真正胸怀天下之人，而其他角色也都有着各自的成长，最终为大的时局贡献了自己的力量
*************************** 2. row ***************************
id: 2
title: 暮光之城（电视剧）
content: 传说太阳下山后，有恶鬼出没吃人。亦有猎鬼人斩杀恶鬼、保护人们。
2 rows in set (0.00 sec)
# ============================= 扩展模式下的检索 =========================== #
# 由于本表数据有限，无法模拟扩展模式下的检索
# 下面引用 https://www.dbmng.com/art-1200.html 内容
扩展模式查询（转载）
全文搜索的查询扩展模式将进行两个阶段的搜索：
-
第阶段搜索和普通的自然语言搜索一样
-
在第一次搜索里找到的相关程度最高的数据行里的单词将被用在第二阶段自然语言搜索
因为搜索单词的集合变大了，所以在最终结果里往往会多出一些在第一阶段没被找到、但与第一阶段的检索结果有一定关系的数据行
要想进行这种搜索，需要在搜索字符串的后面加上WITH QUERY EXPANSION 即可，以下是查询扩展案例
# 有趣的查询扩展
# 第一条查询命令将进行一次自然语言搜索，第二条查询命令将进行一次查询扩展搜索
# 第二条这次多找到了一个数据行，但该数据行不包含原始搜索字符串里的任何单词，为什么？
# 该数据行会被匹配出来的原因是它包含单词"candle"，这个单词出现在了被自然语言搜索找到的某个数据行里
# ============================= 扩展查询模式下的检索 ========================= #
mysql> SELECT * FROM apothegm
-> WHERE MATCH(attribution, phrase)
-> AGAINST('bell book');
+-----------------------+------------------------------------+
| attribution | phrase |
+-----------------------+------------------------------------+
| Miguel de Cervantes | Bell, book, and candle |
| Alexander Graham Bell | Mr. Watson, come here. I want you! |
+-----------------------+------------------------------------+
mysql> SELECT * FROM apothegm
-> WHERE MATCH(attribution, phrase)
-> AGAINST('bell book' WITH QUERY EXPANSION);
+-----------------------+---------------------------------------+
| attribution | phrase |
+-----------------------+---------------------------------------+
| Miguel de Cervantes | Bell, book, and candle |
| Alexander Graham Bell | Mr. Watson, come here. I want you! |
| Robert Burton | I light my candle from their torches. |
+-----------------------+---------------------------------------+
记录成长过程
