MySQL innodb 全文索引使用

1、mysql 5.7 全文索引以下幾個參數(配置文件/etc/my.cnf)
#控制innodb全文檢索分詞的最小長度，如果設置為2那么一個漢字和一個字母將不能搜到
ngram_token_size=1
#存儲在InnoDB的FULLTEXT索引中的最小詞長，所說使用了ngram_token_size之后就不用innodb_ft_min_token_size了，但為了保險我兩個都設置了
innodb_ft_min_token_size=1
#最小分詞長度，一般修改為1
ft_min_word_len = 1

2、創建表

mysql> show create table s_test;
+--------+---------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                                                                   |
+--------+---------------------------------------------------------+
| s_test | CREATE TABLE `s_test` (
  `id` int(11) NOT NULL DEFAULT '0' COMMENT 'primary key',
  `uname` varchar(50) DEFAULT NULL COMMENT '用戶名',
  `dept` int(11) DEFAULT NULL COMMENT '部門組ID',
  `info` varchar(200) DEFAULT NULL COMMENT '其他信息'
) ENGINE=InnoDB DEFAULT CHARSET=utf8           |
+--------+--------------------------------------------------------+
1 row in set (0.00 sec)

3、創建索引

mysql> create fulltext index ix_ft_s_test_uname_info on s_test(uname,info) WITH PARSER ngram;
Query OK, 0 rows affected, 1 warning (2.68 sec)
Records: 0  Duplicates: 0  Warnings: 1

4、查詢這張表的所有索引

mysql> show index from s_test;
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name                | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| s_test |          1 | ix_ft_s_test_uname_info |            1 | uname       | NULL      |       99750 |     NULL | NULL   | YES  | FULLTEXT   |         |               |
| s_test |          1 | ix_ft_s_test_uname_info |            2 | info        | NULL      |       99750 |     NULL | NULL   | YES  | FULLTEXT   |         |               |
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

5、查詢索引詳細情況

mysql> select * from mysql.innodb_index_stats where database_name='mydb' and table_name='s_test' ;
+---------------+------------+------------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name       | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
+---------------+------------+------------------+---------------------+--------------+------------+-------------+-----------------------------------+
| mydb          | s_test     | FTS_DOC_ID_INDEX | 2015-10-03 14:07:18 | n_diff_pfx01 |     100672 |          20 | FTS_DOC_ID                        |
| mydb          | s_test     | FTS_DOC_ID_INDEX | 2015-10-03 14:07:18 | n_leaf_pages |        121 |        NULL | Number of leaf pages in the index |
| mydb          | s_test     | FTS_DOC_ID_INDEX | 2015-10-03 14:07:18 | size         |        161 |        NULL | Number of pages in the index      |
| mydb          | s_test     | GEN_CLUST_INDEX  | 2015-10-03 14:07:18 | n_diff_pfx01 |      99750 |          20 | DB_ROW_ID                         |
| mydb          | s_test     | GEN_CLUST_INDEX  | 2015-10-03 14:07:18 | n_leaf_pages |        525 |        NULL | Number of leaf pages in the index |
| mydb          | s_test     | GEN_CLUST_INDEX  | 2015-10-03 14:07:18 | size         |        545 |        NULL | Number of pages in the index      |
+---------------+------------+------------------+---------------------+--------------+------------+-------------+-----------------------------------+
6 rows in set (0.00 sec)

6、全索引查詢

通過在AGAINST()函數中指定
1、IN NATURAL LANGUAGE MODE    expr就是要搜尋的字符串。 
2、IN NATURAL MODE WITH QUERY EXPANSION 第一次用給定的短語搜索，第二次使用給定的短語結合第一次搜索返回結果中相關性非常高的一些行進行搜索。
3、IN BOOLEAN MODE expr里有特殊字符輔助特殊的搜尋語法

查詢只能按短語，不能使用中間字符
mysql> select count(*) from  s_test where MATCH(uname,info) AGAINST ('a*' IN BOOLEAN MODE);
+----------+
| count(*) |
+----------+
|     6238 |
+----------+
1 row in set (0.02 sec)