原创 叶金荣 老叶茶馆 今天
暂不建议。
- 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.
最后，个人建议现在还不适合在MySQL里玩全文索引，先继续保持观望吧。
