开发者社区> xaubllxwtvaqiu> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

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

简介: 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 = 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万条数据,看下表统计信息:

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

3. 全文搜索

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

[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

上面这条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

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

# 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的描述

OperationInstantIn PlaceRebuilds TablePermits Concurrent DMLOnly Modifies Metadata
Adding a FULLTEXT indexNoYesNoNoNo

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


            </div>

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
MySQL 8.0 InnoDB全文索引可用于生产环境吗
MySQL 8.0 InnoDB全文索引可用于生产环境吗
15 0
MySQL 8.0 InnoDB全文索引可用于生产环境吗
MySQL 8.0 InnoDB全文索引可用于生产环境吗
23 0
使用“正则”实现不规则数据的中英文提取
使用“正则”实现不规则数据的中英文提取
19 0
MySql 8.0 字符编码utf8mb4小实验
MySql 8.0 字符编码utf8mb4小实验
263 0
浅谈第三方登录用户表结构设计方案
今天就以对接微信登录、QQ登录与苹果登录。来说说对第三方用户体系与我方系统用户体系的对接的一些可行性方案。
224 0
一文解决C程序的编译问题
对于C源码编译,大部分人都停留在./configure --prefix=XXX && make && make install这一步,大部分的程序都能顺利走完这一步,然后被安装到指定的文件下,小部分的程序会因为xxx不全而出错,然后你把这个问题放到搜索引擎上,就会找到一篇博客说用sudo apt-get/yum install xxx 后可以解决问题,然后问题解决了。
830 0
LeetCode: 221_Maximal Square | 二维0-1矩阵中计算包含1的最大正方形的面积 | Medium
题目: Given a 2D binary matrix filled with 0's and 1's, find the largest square containing all 1's and return its area. For example, given the following matrix: 1 0 1 0 0 1 0 1 1 1 1 1 1 1 1 1 0 0 1 0 Return 4. 解题思路:   这种包含最大、最小等含优化的字眼时,一般都需要用到动态规划进行求解。
928 0
7700
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载