MySQL 8.0 InnoDB全文索引可用于生产环境吗

原创

叶金荣 2021-07-14 14:44:53 ©著作权

文章标签 MySQL 文章分类 MySQL 数据库

©著作权归作者所有：来自51CTO博客作者叶金荣的原创作品，请联系作者获取转载授权，否则将追究法律责任

暂不建议。

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

0. 背景介绍

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

1. 环境准备

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

ngram_token_size = 1
ft_min_word_len = 1
innodb_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 status\G
           Name: t_fulltxt
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 136554
 Avg_row_length: 109696   --行平均长度107KB，太大了
    Data_length: 14574764032
Max_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: fulltext
possible_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 1
procs -----------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 score
from t2 where MATCH(bdata) AGAINST('时间' IN NATURAL LANGUAGE MODE) and
match(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的描述

Operation	Instant	In Place	Rebuilds Table	Permits Concurrent DML	Only Modifies Metadata
Adding a FULLTEXT index	No	Yes*	No*	No	No

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里玩全文索引，先继续保持观望吧。

全文完。