上一篇介绍了全文索引的基本原理，这篇来讲讲如何更好的使用全文索引。
全文索引的检索和普通检索的语法不同，普通检索一般类似下面SQL：
代码语言：javascript复制select * from tb1 where id in (1,2);
select * from tb1 where id < 10;
过滤条件在WHERE子句后面，以一定的方式来拼接SQL，全文索引的使用有特定的语法：
代码语言：javascript复制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
}
本篇采用的示例表如下，表记录数10W行：
代码语言：javascript复制 CREATE TABLE `fx` (
`id` int NOT NULL AUTO_INCREMENT,
`s1` varchar(200) DEFAULT NULL,
`log_time` datetime DEFAULT NULL,
`s2` varchar(200) DEFAULT NULL,
`s3` text,
PRIMARY KEY (`id`),
KEY `idx_log_time` (`log_time`)
)
现在给表fx字段s1建立全文索引，随后做一个简单的搜索，关键词为“cluster"。
代码语言：javascript复制mysql> alter table fx add fulltext ft_s1(s1);
Query OK, 0 rows affected, 1 warning (5.94 sec)
Records: 0 Duplicates: 0 Warnings: 1
mysql> select count(*) from fx where match(s1) against ('cluster');
----------
| count(*) |
----------
| 9396 |
----------
1 row in set (0.01 sec)
全文索引有三种模式，分别为自然语言模式；查询扩展模式；布尔模式。
自然语言模式：
这是默认的模式，所以上面的语句实际上这样写，把它命名为SQL1， 这是最优化的写法。
代码语言：javascript复制# SQL 1
select count(*) from fx where match(s1) against ('cluster' in natural language mode);
更加详细点，把SQL1 改成如下写法，把它命名为 SQL 2。
代码语言：javascript复制# SQL 2
select count(*) from fx where match(s1) against ('cluster' in natural language mode) >0;
把SQL 2 小改下，where 子句后面的过滤 Match against 调整到select 子句后面，把它命名为SQL 3。
代码语言：javascript复制# SQL 3
select count(if(match(s1) against ('cluster' in natural language mode),1,null)) as "count(*)" from fx;
分别看下三个SQL 的执行计划：
SQL 1 最优，EXTRA栏结果显示这是一个最优化的查询；
SQL 2次之，走全文索引ft_s1;
SQL 3 没有WHERE 过滤条件，所以全表扫描，性能最差。
代码语言：javascript复制# SQL 1 执行计划
mysql> explain select count(*) from fx where match(s1) against ('cluster' in natural language mode)G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
...
Extra: Select tables optimized away
1 row in set, 1 warning (0.01 sec)
# SQL 2 执行计划
mysql> explain select count(*) from fx where match(s1) against ('cluster' in natural language mode)>0G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: fx
partitions: NULL
type: fulltext
possible_keys: ft_s1
key: ft_s1
key_len: 0
ref: const
rows: 1
filtered: 100.00
Extra: Using where; Ft_hints: rank > 0
1 row in set, 1 warning (0.01 sec)
# SQL 3 执行计划
mysql> explain select count(if(match(s1) against ('cluster' in natural language mode),1,null)) as "count(*)" from fxG
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: fx
partitions: NULL
type: ALL
...
rows: 99763
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.01 sec)
全文索引里有一个关键词叫**“相关性”**，指的是被搜索的关键词在每条记录里的匹配程度，相关性越高，搜索的数据越是准则。下面SQL 4，按照关键词'mysql'的相关性倒序输出。
代码语言：javascript复制# SQL 4
SELECT
MATCH (s1) AGAINST ('mysql' IN NATURAL LANGUAGE MODE) AS relevance
FROM
fx
WHERE
MATCH (s1) AGAINST ('mysql' IN NATURAL LANGUAGE MODE) > 0
此时查看SQL 4的执行计划，就发现与之前的几个SQL有一点不同，多了一个排序过程。
Extra: Using where; Ft_hints: sorted, rank > 0
那把相关性打印出来看看，执行下SQL 5.
代码语言：javascript复制#SQL 5
SELECT
s1, relevance
FROM
(SELECT
s1,
MATCH (s1) AGAINST ('mysql' IN NATURAL LANGUAGE MODE) AS relevance
FROM
fx
WHERE
MATCH (s1) AGAINST ('mysql' IN NATURAL LANGUAGE MODE)) outer_table
GROUP BY relevance;
以下是SQL5执行结果中的relevance 字段值列表：
代码语言：javascript复制 --------------------
| relevance |
--------------------
| 0.550973117351532 |
| 0.4591442346572876 |
| 0.367315411567688 |
| 0.275486558675766 |
| 0.183657705783844 |
| 0.091828852891922 |
--------------------
6 rows in set (0.54 sec)
上面的几条SQL都是针对单个关键词来检索，如果现在有多个关键字呢？比如同时搜索既有"mysql" 又有"oracle"的关键词？只需要把两个检索子句连起来就行，见SQL 6：
代码语言：javascript复制# SQL 6
SELECT
s1
FROM
fx
WHERE
MATCH (s1) AGAINST ('mysql' IN NATURAL LANGUAGE MODE)
AND MATCH (s1) AGAINST ('oracle' IN NATURAL LANGUAGE MODE)
或者是写成 SQL 7：
代码语言：javascript复制# SQL 7
SELECT
*
FROM
(SELECT
s1
FROM
fx
WHERE
MATCH (s1) AGAINST ('mysql' IN NATURAL LANGUAGE MODE)) outer_table
WHERE
MATCH (s1) AGAINST ('oracle' IN NATURAL LANGUAGE MODE)
那除了以上的写法，有没有更简便而且更优化的写法呢？这就是另外一个模式，布尔模式解决的问题。
布尔模式：
布尔模式有原生的操作符，可以处理多个关键词的过滤，比如把之前的SQL 6 和 SQL 7 改为布尔模式，命名为SQL 8。
代码语言：javascript复制# SQL 8
select s1 from fx where match(s1) against (' mysql oracle' in boolean mode);
再细化一些，增加点条件，也非常简单，比如 检索关键词包含'mysql','oracle' 但是不包含（"postgresql","mongodb","sqlserver"）的记录，命名为SQL 9。
代码语言：javascript复制# SQL 9
SELECT
s1
FROM
fx
WHERE
MATCH (s1) AGAINST (' mysql oracle -postgresql -mongodb -sqlserver' IN BOOLEAN MODE);
再看一条SQL，关键词没有任何操作符号，不是指搜索关键词”mysql oracle" 而是搜索包含“mysql" 或者 包含关键词”oracle"的记录。
代码语言：javascript复制# SQL 10
select s1 from fx where match(s1) against ('mysql oracle' in boolean mode);
以上SQL 8, SQL 9, SQL 10 涉及到的操作符为" ","-"," ",分别代表“and","not","or"。
布尔模式和自然语言模式不同的是，结果不会按照关键词的相关性排序。对于必须按照相关性排序的需求，还是考虑用自然语言模式。
布尔模式不仅可以过滤多个关键词，还可以主动干预搜索关键词的相关性排名。下面SQL 检索同时包含”mysql"和“oracle” 的关键词，并把其中包含关键词"postgresql"的相关性提升，也就是排名靠前。
代码语言：javascript复制# SQL 11
select s1 from fx where match(s1) against (' mysql oracle >postgresql' in boolean mode);
当然布尔模式还有很多妙用，这里就不一一展开了，下面看查询扩展模式
查询扩展模式：
这种模式的意义在于，让搜索结果所隐含的常识性关键词扩展输出。比如，要搜索关键词"database" 不仅会搜索“database"， 而且会把包含关键词"mysql","oracle"的记录也一并搜索。第一次是"database",另外一次是"mysql","oracle",完后把这两个结果合并在一起输出结果。
为了结果查看清晰，建立另外一张表，表结构和fx一样，不过只有8条记录
代码语言：javascript复制mysql> select s1 from fx_few;
----------------------------
| s1 |
----------------------------
| mysql oracle |
| mysql postgresql sqlserver |
| windows unix linux |
| man woman |
| lucy lily |
| sqlserver postgresql |
| oracle postgresql |
| mysql mongodb |
----------------------------
8 rows in set (0.00 sec)
用查询扩展模式查询包含”sqlserver"关键词的记录，会把包含其他数据库关键词的结果也一起输出。
代码语言：javascript复制mysql> select * from fx_few where match(s1) against ('sqlserver' with query expansion);
---- ---------------------------- ---------- ------ ------
| id | s1 | log_time | s2 | s3 |
---- ---------------------------- ---------- ------ ------
| 2 | mysql postgresql sqlserver | NULL | NULL | NULL |
| 6 | sqlserver postgresql | NULL | NULL | NULL |
| 1 | mysql oracle | NULL | NULL | NULL |
| 7 | oracle postgresql | NULL | NULL | NULL |
| 8 | mysql mongodb | NULL | NULL | NULL |
---- ---------------------------- ---------- ------ ------
5 rows in set (0.00 sec)
这篇关于MySQL全文索引的简单用法就介绍到此，后面会继续讲解如何提高全文索引结果的准确性以及全文索引的优化与中文插件的使用。
