mysql 全文索引

mysql大表查询的时候，'String%'模糊查询可以使用B+树类型的索引prefix，然而'String%'匹配模式在应用中不是我们所需要的，往往需要'%String%'，这是我们可以考虑使用FULLTEXT索引，INNODE是以红黑树来，存储全文索引，下面我们就来测试一下全文索引。
首先建表：
CREATE TABLE fts_a(
FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
body TEXT,
PRIMARY KEY (FTS_DOC_ID)
);
插入记录：
INSERT INTO `test`.`fts_a` (`FTS_DOC_ID`, `body`) VALUES ('1', 'some one like you');
INSERT INTO `test`.`fts_a` (`FTS_DOC_ID`, `body`) VALUES ('2', 'you can you up');
INSERT INTO `test`.`fts_a` (`FTS_DOC_ID`, `body`) VALUES ('3', 'I like your style');
INSERT INTO `test`.`fts_a` (`FTS_DOC_ID`, `body`) VALUES ('4', 'one day ,i see you');
创建全文索引：
mysql> CREATE FULLTEXT INDEX idx_fts ON fts_a(body);
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0
查看索引：
mysql> show index from fts_a;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| fts_a |          0 | PRIMARY  |            1 | FTS_DOC_ID  | A         |           4 | NULL     | NULL   |      | BTREE      |         |               |
| fts_a |          1 | idx_fts  |            1 | body        | NULL      |           4 | NULL     | NULL   | YES  | FULLTEXT   |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set

设置索引参数：
mysql> SET GLOBAL innodb_ft_aux_table='test/fts_a';
Query OK, 0 rows affected
查看全文索引（倒排索引）信息：
mysql> select * from fts_a;
+------------+--------------------+
| FTS_DOC_ID | body               |
+------------+--------------------+
|          1 | some one like you  |
|          2 | you can you up     |
|          3 | I like your style  |
|          4 | one day ,i see you |
+------------+--------------------+
4 rows in set
mysql> select * from information_schema.INNODB_FT_INDEX_TABLE;
+-------+--------------+-------------+-----------+--------+----------+
| WORD  | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+-------+--------------+-------------+-----------+--------+----------+
| can   |            2 |           2 |         1 |      2 |        4 |
| day   |            4 |           4 |         1 |      4 |        4 |
| like  |            1 |           3 |         2 |      1 |        9 |
| like  |            1 |           3 |         2 |      3 |        2 |
| one   |            1 |           4 |         2 |      1 |        5 |
| one   |            1 |           4 |         2 |      4 |        0 |
| see   |            4 |           4 |         1 |      4 |       11 |
| some  |            1 |           1 |         1 |      1 |        0 |
| style |            3 |           3 |         1 |      3 |       12 |
| you   |            1 |           4 |         3 |      1 |       14 |
| you   |            1 |           4 |         3 |      2 |        0 |
| you   |            1 |           4 |         3 |      2 |        8 |
| you   |            1 |           4 |         3 |      4 |       15 |
| your  |            3 |           3 |         1 |      3 |        7 |
+-------+--------------+-------------+-----------+--------+----------+
14 rows in set
删除记录innodb并不会立即删除索引，要进行优化操作，测试如下
mysql> DELETE FROM fts_a WHERE fts_doc_id=4;
Query OK, 1 row affected

mysql> SELECT * FROM information_schema.INNODB_FT_DELETED;
+--------+
| DOC_ID |
+--------+
|      4 |
+--------+
1 row in set
优化：
mysql> SET GLOBAL innodb_optimize_fulltext_only=1;
Query OK, 0 rows affected

mysql> OPTIMIZE TABLE test.fts_a;
+------------+----------+----------+----------+
| Table      | Op       | Msg_type | Msg_text |
+------------+----------+----------+----------+
| test.fts_a | optimize | status   | OK       |
+------------+----------+----------+----------+
1 row in set

mysql> SELECT * FROM information_schema.INNODB_FT_DELETED;
+--------+
| DOC_ID |
+--------+
|      4 |
+--------+
1 row in set

mysql> SELECT * FROM information_schema.INNODB_FT_BEING_DELETED;
+--------+
| DOC_ID |
+--------+
|      4 |
+--------+
1 row in set
利用全文索引查询记录：
mysql> SELECT * FROM fts_a WHERE MATCH(body) AGAINST ('like' IN NATURAL LANGUAGE MODE);
+------------+-------------------+
| FTS_DOC_ID | body              |
+------------+-------------------+
|          1 | some one like you |
|          3 | I like your style |
+------------+-------------------+
2 rows in set
从查询解释我们可以看出使用个全文索引
mysql> EXPLAIN SELECT * FROM fts_a WHERE MATCH(body) AGAINST ('like' IN NATURAL LANGUAGE MODE);
+----+-------------+-------+----------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type     | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+----------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | fts_a | fulltext | idx_fts       | idx_fts | 0       | NULL |    1 | Using where |
+----+-------------+-------+----------+---------------+---------+---------+------+------+-------------+
1 row in set
查询文档相关性
mysql>
SELECT FTS_DOC_ID,body,MATCH(body) AGAINST ('like' IN NATURAL LANGUAGE MODE) AS Relevance FROM fts_a ;
+------------+-------------------+--------------------+
| FTS_DOC_ID | body              | Relevance          |
+------------+-------------------+--------------------+
|          1 | some one like you | 0.0906190574169159 |
|          2 | you can you up    |                  0 |
|          3 | I like your style | 0.0906190574169159 |
|          5 | hell girls        |                  0 |
+------------+-------------------+--------------------+
4 rows in set
查询存在like和you的文档
mysql> SELECT * FROM fts_a WHERE MATCH(body) AGAINST ('+like +you' IN BOOLEAN MODE);
+------------+-------------------+
| FTS_DOC_ID | body              |
+------------+-------------------+
|          1 | some one like you |
+------------+-------------------+
1 row in set
查看一般匹配查询，并没有使用索引
mysql> EXPLAIN SELECT * FROM fts_a WHERE body LIKE '%like%';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | fts_a | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set