MySQL innodb 全文索引使用

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

2、創(chuàng)建表

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、創(chuàng)建索引

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、查詢索引詳細(xì)情況

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()函數(shù)中指定
1、IN NATURAL LANGUAGE MODE    expr就是要搜尋的字符串。 
2、IN NATURAL MODE WITH QUERY EXPANSION 第一次用給定的短語搜索，第二次使用給定的短語結(jié)合第一次搜索返回結(jié)果中相關(guān)性非常高的一些行進(jìn)行搜索。
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)