mysql 实战项目全文索引小联欢

大背景

  • 项目背景:某海外移动互联网产品,英文环境
  • 搜索内容:人名、标签、内容描述
  • 数据规模:千万级别

搜素领域现在开口闭口都是ElasticSearch的天下了,各种NLP也都是基于ES的定制接口,在%like%被唾弃的性能面前,已经很少看到用mysql做一些模糊比较的场合了,除了like%这种走索引的除外,innodb1.2提供了FTSearch,使用的倒排索引在一定程度上解决了全文索引中的性能问题,在数据量不是太大,并且时全文索引支持的英文环境,在基础设施不是很齐全的情况考虑使用mysql全文索引做一些简单的人名等小文本的查询。

全文索引的一些概念

在使用全文索引之前,需要对这块有个比较深刻的了解,便于后期优化或者在更大数据量迁移至ES上能有个全面的了解。

倒排索引

innodb使用的是一种倒排索引 inverted index来实现,同b+树一样,也是一种索引结构,通过辅助表aux table来存储单词的内容,在innodb中索引的存储形式使用full invertedindex(单词,(单词所在文档的id,在具体文档中的位置)),这些可以通过后面的表的结构来体现。

fulltext的字段类型

fulltext支持varchar,text,longtext字段类型,对该字段创建fulltext索引即可,但是一张表只能有一个全文索引,多个字段全文索引要累加 fulltext(a,b,c),必须使用相同的排序和字符集

辅助表

innodb在infomation_schema中设置了和全文索引相关的表,可以对innodb设置的部分参数:

INFORMATION_SCHEMA库中与InnoDB全文索引相关的表如下:

  • INNODB_SYS_INDEXES
  • INNODB_SYS_TABLES
  • INNODB_FT_CONFIG
  • INNODB_FT_INDEX_TABLE
  • INNODB_FT_INDEX_CACHE
  • INNODB_FT_DEFAULT_STOPWORD
  • INNODB_FT_DELETED
  • INNODB_FT_BEING_DELETED

解释

  • Ø INNODB_SYS_INDEXES:提供了InnoDB索引的状态信息。
  • Ø INNODB_SYS_TABLES:提供了InnoDB表的状态信息。
  • Ø INNODB_FT_CONFIG:显示一个InnoDB表的FULLTEXT索引及其> > * 相关处理的元数据。
  • Ø INNODB_FT_INDEX_TABLE:转化后的索引信息用于处理基于InnoDB表FULLTEXT索引的文本搜索。一般用于调试诊断目的。使用该表前需先配置innodb_ft_aux_table配置选项,将其指定为想要查看的含FULLTEXT索引的InnoDB表,选项值的格式为database_name/table_name。配置了该选项后INNODB_FT_INDEX_TABLE,INNODB_FT_INDEX_CACHE, INNODB_FT_CONFIG, INNODB_FT_DELETED和INNODB_FT_BEING_DELETED表将被填充与innodb_ft_aux_table配置选项指定的表关联的搜索索引相关信息。
  • Ø INNODB_FT_INDEX_CACHE:向含FULLTEXT索引的InnoDB表插入数据后新插入数据转后的索引信息。表结构与INNODB_FT_INDEX_TABLE一致。为含FULLTEXT索引的InnoDB表执行DML操作期间重组索引开销很大,因此将新插入的被索引的词单独存储于该表中,当且仅当为InnoDB表执行OPTIMIZE TABLE语句后才将新的转换后的索引信息与原有的主索引信息合并。使用该表前需先配置innodb_ft_aux_table配置选项。
  • Ø INNODB_FT_DEFAULT_STOPWORD:在InnoDB表上创建FULLTEXT索引所使用的默认停止字表。
    Ø INNODB_FT_DELETED:记录了从InnoDB表FULLTEXT索引中删除的行。为了避免为InnoDB的FULLTEXT索引执行DML操作期间重组索引的高开销,新删除的词的信息单独存储于此表。当且仅当为此InnoDB表执行了OPTIMIZE TABLE操作后才会从主搜索索引中移除已删除的词信息。使用该表前需先配置innodb_ft_aux_table选项。
  • Ø INNODB_FT_BEING_DELETED:为含FULLTEXT索引的InnoDB表执行OPTIMIZE TABLE操作时会根据INNODB_FT_DELETED表中记录的文档ID从InnoDB表的FULLTEXT索引中删除相应的索引信息。而INNOFB_FT_BEING_DELETED表用于记录正在被删除的信息,用于监控和调试目的

新增

create table t1 (a int auto_increment primary key, b text, fulltext(b));

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> insert into t1 values (NULL, 'hello, welcome to mysql world');
Query OK, 1 row affected (1.87 sec)
mysql> set global innodb_ft_aux_table = 'test/t1';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from INNODB_FT_INDEX_CACHE;
+---------+--------------+-------------+-----------+--------+----------+
| WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+---------+--------------+-------------+-----------+--------+----------+
| hello | 2 | 2 | 1 | 2 | 0 |
| mysql | 2 | 2 | 1 | 2 | 18 |
| welcome | 2 | 2 | 1 | 2 | 7 |
| world | 2 | 2 | 1 | 2 | 24 |
+---------+--------------+-------------+-----------+--------+----------+
4 rows in set (0.00 sec)

ps:mysql> select * from infomation_schema .INNODB_FT_INDEX_TABLE;
这里innodb有一个同步机制,提交的索引并不会立即同步到这张表中,而是在满足特定情况下才会同步,所以这里通过cache表来查看同样可以得到内容。

在插入一条记录时,对应的堆栈如下:

1
2
3
row_insert_for_mysql
|--> row_insert_for_mysql_using_ins_graph
|--> fts_trx_add_op // state = FTS_INSERT

更新和删除

删除操作不会直接从全文索引里直接删除,因此依然可以从INNODB_FT_INDEX_CACHE中查到分词信息。

1
2
3
4
5
ha_innobase::delete_row
|--> row_update_for_mysql
|--> row_update_for_mysql_using_upd_graph
|--> row_fts_update_or_delete
|--> fts_trx_add_op // state = FTS_DELETE

更新非全文索引列,不会修改FTS_DOC_ID列的值。如果更新了全文索引列,在InnoDB的实现是删除老的DOC,并插入新的DOC。

1
2
3
4
5
6
7
ha_innobase::update_row
|--> row_update_for_mysql
|--> row_update_for_mysql_using_upd_graph
|--> row_fts_update_or_delete
|--> row_fts_do_update
|--> fts_trx_add_op // state = FTS_DELETE
|--> fts_trx_add_op // state = FTS_INSERT

查询全文索引

对于全文索引的查询,采用新的接口函数,分为两步:

  1. 根据检索词搜集符合条件的doc id。
1
2
3
4
5
JOIN::optimize
|--> init_ftfuncs
|--> Item_func_match::init_search
|--> ha_innobase::ft_init_ext
|--> fts_query

在搜集满足查询条件的doc id时,首先读取DELETED表中记录的doc id,这些doc id随后被用做过滤。

  1. 根据搜集到的doc id,找到对应的记录,使用的索引是dict_table_t::fts_doc_id_index,也就是建立在隐藏列FTS_DOC_ID上的唯一索引。
1
2
3
4
5
6
sub_select
|--> join_ft_read_first
|--> ha_innobase::ft_init
|--> ha_innobase::ft_read
|--> join_ft_read_next
|--> ha_innobase::ft_read

检索出的顺序是按照rank排序的,这里影响的4个相关性分别是:

  1. work是否在文档出现
  2. 出现的次数
  3. 在索引的数量
  4. 多少个文档包含

rank算法如下:

1
2
3
4
${IDF} = log10( ${total_records} / ${matching_records} ) // total_records表示总的行记录数,matching_records表示匹配到检索字的行记录数
${TF} 表示单词在文档中出现的次数
${rank} = ${TF} * ${IDF} * ${IDF}

检索类型也分为3类:

  1. 自然模式:简单的查询匹配
  2. 布尔模式:与或非形式的组合匹配
  3. 扩展模式:比较复杂,没有做深入研究

这里还有一个概念是stopword,可以通过stopword参数自定义一些stopword,该类词类似保留字,不做相关处理,默认36个保留字。

检索语法:MATCH (…) AGAINST (…)

简单项目实战

用户名检索:用户存在自己的名字和昵称,分别需要对用户名和昵称进行检索,可以使用组合全文索引或者另外新增一列进行(会增加行上的长度),分别适用不同的场景,这里使用后者模拟。

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> show create table user\G;
*************************** 1. row ***************************
Table: user
Create Table: CREATE TABLE `user` (
`uid` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(128) NOT NULL,
`nickname` varchar(64) NOT NULL,
`names` text,
PRIMARY KEY (`uid`),
KEY `username` (`username`),
FULLTEXT KEY `names_ft` (`names`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
1 row in set (0.03 sec)

模拟一些数据进去

1
2
mysql> insert into user (username,nickname,names) values ('xumin','fotoplace','xumin fotoplace pony');
Query OK, 1 row affected (0.04 sec)

再来查看一下数据库中的全文索引的情况
(这里有一些老数据):

1
2
3
4
5
6
7
8
9
10
11
12
set global innodb_ft_aux_table = 'test/user';
select * from information_schema.INNODB_FT_INDEX_CACHE;
# WORD, FIRST_DOC_ID, LAST_DOC_ID, DOC_COUNT, DOC_ID, POSITION
'james', '7', '7', '1', '7', '5'
'min', '5', '5', '1', '5', '3'
'name', '8', '8', '1', '8', '8'
'someone', '8', '8', '1', '8', '0'
'pony', '6', '7', '2', '6', '0'
'pony', '6', '7', '2', '7', '0'
'xu', '5', '6', '2', '5', '0'
'xu', '5', '6', '2', '6', '5'

使用match进行检索下吧:

mysql> select * from user where match(names) against (‘fotoplace’);
+—–+———-+———–+———————-+
| uid | username | nickname | names |
+—–+———-+———–+———————-+
| 5 | xumin | fotoplace | xumin fotoplace pony |
+—–+———-+———–+———————-+
1 row in set (0.71 sec)

这里使用的是自然模式,IN NATURAL LANGUAGE MODE可以省略,完整的sql是:

1
2
3
4
5
6
7
mysql> select * from user where match(names) against ('fotoplace' IN NATURAL LANGUAGE MODE);
+-----+----------+-----------+----------------------+
| uid | username | nickname | names |
+-----+----------+-----------+----------------------+
| 5 | xumin | fotoplace | xumin fotoplace pony |
+-----+----------+-----------+----------------------+
1 row in set (0.04 sec)

使用explain来查看下全文索引的解释

1
2
3
4
5
6
7
mysql> explain select * from user where match(names) against ('fotoplace' IN NATURAL LANGUAGE MODE);
+----+-------------+-------+----------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+----------+---------------+----------+---------+------+------+-------------+
| 1 | SIMPLE | user | fulltext | names_ft | names_ft | 0 | NULL | 1 | Using where |
+----+-------------+-------+----------+---------------+----------+---------+------+------+-------------+
1 row in set (0.04 sec)

布尔模式下的查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> select * from user where match(names) against ('fotoplace xumin' IN BOOLEAN MODE);
+-----+----------+-----------+----------------------+
| uid | username | nickname | names |
+-----+----------+-----------+----------------------+
| 5 | xumin | fotoplace | xumin fotoplace pony |
+-----+----------+-----------+----------------------+
1 row in set (0.56 sec)
mysql> select * from user where match(names) against ('fotoplace+xumin' IN BOOLEAN MODE);
+-----+----------+-----------+----------------------+
| uid | username | nickname | names |
+-----+----------+-----------+----------------------+
| 5 | xumin | fotoplace | xumin fotoplace pony |
+-----+----------+-----------+----------------------+
1 row in set (0.04 sec)
mysql> select * from user where match(names) against ('+fotoplace+xumin' IN BOOLEAN MODE);
+-----+----------+-----------+----------------------+
| uid | username | nickname | names |
+-----+----------+-----------+----------------------+
| 5 | xumin | fotoplace | xumin fotoplace pony |
+-----+----------+-----------+----------------------+
1 row in set (0.03 sec)

布尔模式会存在以下几种情况:

    • 必须存在
    • 必须排除
  • () 可选
  • @distance 多个单词之间的距离是否在n单位字节
  • 增加相关性

  • < 降低相关性
  • ~ 允许出现该单词,相关性为负
    • 以单词开头的单词
      ‘’表示短语

中文支持

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> insert into user (username,nickname,names) values ('徐敏','j360','j360 xumin fotoplace pony 徐敏 敏');
Query OK, 1 row affected (0.04 sec)
mysql> select * from user where match(names) against ('徐');
Empty set (0.03 sec)
mysql> select * from user where match(names) against ('敏');
+-----+----------+----------+--------------------------------------+
| uid | username | nickname | names |
+-----+----------+----------+--------------------------------------+
| 6 | 徐敏 | j360 | j360 xumin fotoplace pony 徐敏 敏 |
+-----+----------+----------+--------------------------------------+
1 row in set (0.04 sec)

可以看一下information.schema架构下的索引内容,可以看出中文可以被搜素,只是需要自己对中文进行中文分词,这也是额外增加一列的目的,否则建议尽量使用原有字段进行索引。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
mysql> select * from information_schema.INNODB_FT_INDEX_CACHE;
+-----------+--------------+-------------+-----------+--------+----------+
| WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+-----------+--------------+-------------+-----------+--------+----------+
| fotoplace | 5 | 6 | 2 | 5 | 6 |
| fotoplace | 5 | 6 | 2 | 6 | 11 |
| j360 | 6 | 6 | 1 | 6 | 0 |
| james | 3 | 3 | 1 | 3 | 5 |
| min | 1 | 1 | 1 | 1 | 3 |
| name | 4 | 4 | 1 | 4 | 8 |
| pony | 5 | 6 | 2 | 5 | 16 |
| pony | 5 | 6 | 2 | 6 | 21 |
| someone | 4 | 4 | 1 | 4 | 0 |
| tony | 2 | 3 | 2 | 2 | 0 |
| tony | 2 | 3 | 2 | 3 | 0 |
| xu | 1 | 2 | 2 | 1 | 0 |
| xu | 1 | 2 | 2 | 2 | 5 |
| xumin | 5 | 6 | 2 | 5 | 0 |
| xumin | 5 | 6 | 2 | 6 | 5 |
| 徐敏 | 6 | 6 | 1 | 6 | 26 |
| 敏 | 6 | 6 | 1 | 6 | 33 |
+-----------+--------------+-------------+-----------+--------+----------+

通过java程序分词可以看阿里大牛的博客:
11大Java开源中文分词器的使用方法和分词效果对比
https://my.oschina.net/apdplat/blog/412921

参考:

http://mysqlserverteam.com/rankings-with-innodb-full-text-search/
http://blog.csdn.net/zyz511919766/article/details/12780173
http://www.kancloud.cn/taobaomysql/monthly/81107