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 | [color=red]fulltext[/color] | 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