第28期：索引设计（使用全文索引）

上一篇介绍了全文索引的基本原理，这篇来讲讲如何更好的使用全文索引。

全文索引的检索和普通检索的语法不同，普通检索一般类似下面SQL：

select * from tb1 where id in (1,2);
select * from tb1 where id < 10;

过滤条件在WHERE子句后面，以一定的方式来拼接SQL，全文索引的使用有特定的语法：

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行：

 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"。

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，这是最优化的写法。

# SQL 1
select count(*) from fx where match(s1) against ('cluster' in natural language mode);

更加详细点，把SQL1 改成如下写法，把它命名为 SQL 2。

# SQL 2
select count(*) from fx where match(s1) against ('cluster' in natural language mode) >0;

把SQL 2 小改下，where 子句后面的过滤 Match against 调整到select 子句后面，把它命名为SQL 3。

# 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 过滤条件，所以全表扫描，性能最差。

# 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)>0\G
*************************** 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 fx\G
*************************** 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'的相关性倒序输出。

# 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.

#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 字段值列表：

+--------------------+
| 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：

# 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：

# 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。

# SQL 8
select s1 from fx where match(s1) against ('+mysql +oracle' in boolean mode);

再细化一些，增加点条件，也非常简单，比如检索关键词包含'mysql','oracle' 但是不包含（"postgresql","mongodb","sqlserver"）的记录，命名为SQL 9。

# SQL 9
SELECT 
    s1
FROM
    fx
WHERE
    MATCH (s1) AGAINST ('+mysql +oracle -postgresql -mongodb -sqlserver' IN BOOLEAN MODE);

再看一条SQL，关键词没有任何操作符号，不是指搜索关键词”mysql oracle" 而是搜索包含“mysql" 或者包含关键词”oracle"的记录。

# 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"的相关性提升，也就是排名靠前。

# 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条记录

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"关键词的记录，会把包含其他数据库关键词的结果也一起输出。

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全文索引的简单用法就介绍到此，后面会继续讲解如何提高全文索引结果的准确性以及全文索引的优化与中文插件的使用。

关于 MySQL 的技术内容，你们还有什么想知道的吗？赶紧留言告诉小编吧！

第28期：索引设计（使用全文索引）

爱可生开源社区

引用和评论

MySQL 死锁案例分析（1）插入意向锁