关键字：rds 数据库、rds功能、读写分离

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

全文索引的检索和普通检索的语法不同，普通检索一般类似下面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)