mysql全文索引中文分词在java中的实现_MySQL 8.0 InnoDB全文索引可用于生产环境吗

原创 叶金荣 老叶茶馆 今天

暂不建议。

• 0. 背景介绍
• 1. 环境准备
• 2. 导入数据
• 3. 全文搜索
• 4. 放弃治疗

0. 背景介绍

MySQL从5.6版本开始，InnoDB也支持全文索引(fulltext)，从5.7开始新增ngram插件以支持对中文的全文索引，以及用MeCab解析日文。为了验证全文搜索的效果，我做了个简单的测试。

1. 环境准备

本次测试我采用的是MySQL 8.0.19版本。另外，即便有了ngram这个中文分词插件，但其实分词效果还是不太理想，所以我修改了几个参数：

ngram_token_size = 1ft_min_word_len = 1innodb_ft_min_token_size = 1

也就是最短的分词长度为1。

另外，innodb-buffer-pool-size我设置为10GB。

创建测试表：

[root@yejr.run]> CREATE TABLE `t_fulltxt` (  id int unsigned NOT NULL,  author varchar(100) NOT NULL,  title varchar(100) NOT NULL,  summary varchar(255) NOT NULL,  bdata longtext,  PRIMARY KEY (`id`),  FULLTEXT KEY `k1` (`title`,`summary`,`bdata`) WITH PARSER ngram) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. 导入数据

我在某小说网站上下载了一批数据，并每个文件进行切分，保证每条记录里的中文数据不超过96KB，这样一个大文件就会被切分成多次导入。最后，共导入了约14万条数据，看下表统计信息：

[root@yejr.run]> show table statusG           Name: t_fulltxt         Engine: InnoDB        Version: 10     Row_format: Dynamic           Rows: 136554 Avg_row_length: 109696   --行平均长度107KB，太大了    Data_length: 14574764032Max_data_length: 0   Index_length: 5275648      Data_free: 5242880 Auto_increment: NULL    Create_time: 2020-04-29 20:27:44    Update_time: 2020-04-30 01:25:20     Check_time: NULL      Collation: utf8mb4_0900_ai_ci       Checksum: NULL Create_options:        Comment:#表空间文件有15GB[root@yejr.run]# ls -l test/t_fulltxt.ibd-rw-r----- 1 mysql mysql 15145631744 Apr 30 01:28 test/t_fulltxt.ibd#索引文件加起来比表空间文件还要大(去掉部分无关信息)..  1216348160 .. test/fts_000000000000050b_00000000000001b8_index_1.ibd..      114688 .. test/fts_000000000000050b_00000000000001b8_index_2.ibd..      114688 .. test/fts_000000000000050b_00000000000001b8_index_3.ibd..      114688 .. test/fts_000000000000050b_00000000000001b8_index_4.ibd..      114688 .. test/fts_000000000000050b_00000000000001b8_index_5.ibd.. 15544090624 .. test/fts_000000000000050b_00000000000001b8_index_6.ibd..      114688 .. test/fts_000000000000050b_being_deleted.ibd..      114688 .. test/fts_000000000000050b_being_deleted_cache.ibd..      114688 .. test/fts_000000000000050b_config.ibd..      114688 .. test/fts_000000000000050b_deleted.ibd..      114688 .. test/fts_000000000000050b_deleted_cache.ibd

索引文件都已经超过数据文件了，也超过了innodb buffer pool，可以预见其搜索的性能可能也不会太好。

3. 全文搜索

我拿几个关键词进行搜索测试，发现SQL执行耗时都特别大，不过搜索的相关性也还算有一定保证。先看下SQL的执行计划

[root@yejr.run]> EXPLAIN select id,cid,left(title,10) as title,left(author,10) as author,left(summary,10) as summary,instr(bdata,'艾伦') as pos,            match(title,summary,bdata) AGAINST('艾伦' IN BOOLEAN MODE) as score            from t_fulltxt where MATCH(title,summary,bdata) AGAINST('艾伦' IN NATURAL LANGUAGE MODE) and            match(title,summary,bdata) AGAINST('艾伦' IN BOOLEAN MODE) >0 order by score LIMIT 3;*************************** 1. row ***************************                       id: 1  select_type: SIMPLE        table: t_fulltxt   partitions: NULL         type: fulltextpossible_keys: k1          key: k1      key_len: 0          ref: const         rows: 1     filtered: 100.00        Extra: Using where; Ft_hints: no_ranking; Using filesort

上面这条SQL的目的是按全文搜索相关性，从小到大排序，取最小的前三条记录。再看这条SQL的实际耗时

# Query_time: 127.635140  Lock_time: 0.000238 Rows_sent: 3  Rows_examined: 52415

如果换成一个比较常见的词组搜索，则耗时要大很多：

# Query_time: 520.929179  Lock_time: 0.000142 Rows_sent: 3  Rows_examined: 141045...where MATCH(title,summary,bdata) AGAINST('时间' IN NATURAL LANGUAGE MODE)...

另外，我们注意到，无论是在查看SQL执行计划，还是在执行搜索时，一开始都有这个状态

FULLTEXT initialization

The server is preparing to perform a natural-language full-text search.

如果是执行SQL查询，那么这个状态持续的时间会更久一些，有时甚至达到了几十上百秒。

在SQL执行期间，看了下服务器的负载数据

[root@yejr.run]# vmstat -S m 1procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu----- r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st 0  1    969    177      0   2386    0    0 155360     0 3859 5548  1  2 74 23  0 0  1    969    173      0   2389    0    0 158420   262 4123 5873  1  2 74 23  0 1  0    969    171      0   2400    0    0 146852  3071 4270 6085  1  4 71 24  0 0  1    969    173      0   2398    0    0 106900  3160 3019 4765  1  2 74 24  0

物理I/O读太大了，看起来可能是因为innodb buffer pool不够，所以比较慢。不过，关键词查询过一次后，后面的查询就会相对快很多，例如上面的两个关键词在后面的查询大概只需要耗时1.5秒。然鹅，这是在表中数据不再发生变化的前提下。but，当表中数据发生变化后，再次执行查询的耗时又比较高了。这就像MySQL 8.0之前的Query Cache那样，让人很不爽了。

既然是因为innodb buffer pool不够导致比较慢，那么如果换成小表是不是会快一些呢。于是从原来的表中取1万条数据，插入到新表中。新标的全文索引也只有bdata列，不再包含title, summary两个列。新表大小1.1GB，索引文件1.4GB，都没超过innodb buffer pool。再次执行全文搜索查询，这次的的耗时的确提升了很多：

# Query_time: 0.349465  Lock_time: 0.000165 Rows_sent: 3  Rows_examined: 3...select id,cid,left(title,10) as title,left(author,10) as author,left(summary,10) as summary,instr(bdata,'时间') as pos,match(bdata) AGAINST('时间' IN BOOLEAN MODE) as scorefrom t2 where MATCH(bdata) AGAINST('时间' IN NATURAL LANGUAGE MODE) andmatch(bdata) AGAINST('时间' IN BOOLEAN MODE) >0 LIMIT 3;

这个SQL相对于之前去掉了对score排序，所以相对还是快了点。不过，反复执行多次同样的SQL，执行耗时没办法继续下降了，基本上都维持这个值左右。用profile查看这个SQL的耗时，发现大部分是在 FULLTEXT initialization 阶段：

+--------------------------------+----------+| Status                         | Duration |+--------------------------------+----------+...| FULLTEXT initialization        | 0.343885 || executing                      | 0.003677 |...

4. 放弃治疗吧

从上面的几个简单测试中也能看出来MySQL的全文索引效率还是比较低，才十几万的数据量，每次出现新关键词的第一次查询耗时几乎无法承受，虽然后面重复查询好了一些，但如果是并发度稍高的场景里，估计又要歇菜了。即便是已经把整个全文索引都加载到innodb buffer pool中了，数据量也才一万条，查询效率也还是不如意。

测试下来，全文索引有几点不足之处：

• 虽然支持online ddl，但在DDL期间，会阻塞DML请求(此时只能只读查询)。
• 首次创建全文索引时非常慢，因为此时需要重建整张表(见下方文档中的解释)。重建过程中，生成的临时文件是原表的数倍大小。
• 按照文档中的说法，创建第二个全文索引应该不再需要重建整张表了，会快一些了。但实测依然要重建，还是慢的不能接受。
• 官方文档中几乎没有关于全文搜索优化的内容。

MySQL文档中关于全文索引Online DDL的描述

OperationInstantIn PlaceRebuilds TablePermits Concurrent DMLOnly Modifies MetadataAdding a FULLTEXT indexNoYes*No*NoNo

Adding a FULLTEXT index

Adding the first FULLTEXT index rebuilds the table if there is no user-defined FTSDOCID column. Additional FULLTEXT indexes may be added without rebuilding the table.

文档链接：https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html#online-ddl-index-operations

最后，个人建议现在还不适合在MySQL里玩全文索引，先继续保持观望吧。