专栏首页「3306 Pai」社区MySQL 8.0 InnoDB全文索引可用于生产环境吗(续)

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

继续放弃治疗。

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

前文MySQL 8.0 InnoDB全文索引可用于生产环境吗中,简单介绍了MySQL 8.0中对InnoDB表进行全文搜索的性能测试,结论是不建议用于生产环境。

有同学建议调整测试模式,不对大文本/大对象列全文搜索,而是对类似标题这种短文本列进行全文搜索,代替 like'%关键词%' 这样的搜索方式。

本次就该建议进行测试,不过最终的结论也还是不建议使用全文搜索。

1. 环境准备

本次测试依旧采用MySQL 8.0.19版本。几个关键参数:

ngram_token_size = 1
ft_min_word_len = 1
innodb_ft_min_token_size = 1
innodb_ft_cache_size = 80000000
innodb_ft_total_cache_size = 1600000000
innodb_buffer_pool_size = 10737418240

新的测试表:

[root@yejr.run]> CREATE TABLE `t3` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `ltu` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `summary` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `k2` (`summary`) /*!50100 WITH PARSER `ngram` */
) ENGINE=InnoDB AUTO_INCREMENT=6449884 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

2. 导入数据

还是从小说网站上下载的数据进行切分,每次不超过200字节,一个大文件被切分成很多分导入。

最后,共导入6438121条数据,最短9个字符21字节,最长255字符757字节。

看下表统计信息:

[root@yejr.run]> show table status like 't3'\G
*************************** 1. row ***************************
           Name: t3
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 6434441
 Avg_row_length: 319
    Data_length: 2053111808
Max_data_length: 0
   Index_length: 114999296
      Data_free: 5242880
 Auto_increment: 6449884
    Create_time: 2020-05-10 19:23:12
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options: stats_sample_pages=100
        Comment:

#表空间文件有2.1GB
[root@yejr.run]# ls -l test/t3.ibd
-rw-r----- 1 mysql mysql 2197815296 May  9 14:37 test/t3.ibd

#索引文件加起来2.3GB
[root@yejr data01]# du -sch test/fts_0000000000000571_*
209M    test/fts_0000000000000571_0000000000000231_index_1.ibd
80K     test/fts_0000000000000571_0000000000000231_index_2.ibd
80K     test/fts_0000000000000571_0000000000000231_index_3.ibd
80K     test/fts_0000000000000571_0000000000000231_index_4.ibd
80K     test/fts_0000000000000571_0000000000000231_index_5.ibd
2.1G    test/fts_0000000000000571_0000000000000231_index_6.ibd
80K     test/fts_0000000000000571_being_deleted_cache.ibd
80K     test/fts_0000000000000571_being_deleted.ibd
80K     test/fts_0000000000000571_config.ibd
80K     test/fts_0000000000000571_deleted_cache.ibd
608K    test/fts_0000000000000571_deleted.ibd
2.3G    total

注意,上面输出的table status中 Index_length 不为 0,按理说这个表非聚集的二级索引,这里的值应该是 0 才对,后面再去确认什么原因,有知道的读者也请留言告知,谢谢。

文档中关于 Index_length 的解释:

For MyISAM, Index_length is the length of the index file, in bytes. For InnoDB, Index_length is the approximate amount of space allocated for non-clustered indexes, in bytes. Specifically, it is the sum of non-clustered index sizes, in pages, multiplied by the InnoDB page size. Refer to the notes at the end of this section for information regarding other storage engines.

3. 全文搜索测试

本次我根据导入的文章,随机找几个关键词进行测试。

第一个关键词:绿毒蛙。多次反复全文搜索查询,耗时约为 1.67 ~ 1.75秒 之间。

# Query_time: 1.691762  Rows_sent: 13  Rows_examined: 120473

利用profiling查看耗时最久的两个阶段

| FULLTEXT initialization        | 1.384282 |
| executing                      | 0.324287 |

改成 like'%绿毒蛙%' 模糊搜索,耗时基本稳定在 7.5 ~ 8.0秒 之间

# Query_time: 7.511337  Rows_sent: 13  Rows_examined: 6438121

profiling的结果

| executing                      | 7.511123 |

首个关键词扫描行数 120473,全文搜索胜出

第二个关键词:田大仁

这次的全文搜索耗时则慢了很多

# Query_time: 72.822958  Rows_sent: 2334  Rows_examined: 1850544
...

| FULLTEXT initialization        | 67.449839 |
| executing                      |  5.083728 |

这个关键词的扫描行数是 1850544,是第一个关键词的 15.36 倍。

LIKE模糊搜索耗时还是和第一个关键词差不多,7.5 ~ 8.0秒 之间。

此外,全文搜索时如果加上 LIMIT N 对性能提升并没有实质性帮助,而且在 slow query log 中记录的 Rows_examined 值也不准确

SELECT ... LIMIT 5;
...
# Query_time: 67.928363  Rows_sent: 5  Rows_examined: 211

profiling 的结果来看,在 FULLTEXT initialization 阶段的耗时占总耗时的98%,约66.59秒,只是在 executing 阶段提升了,加上 LIMIT5 之后,耗时从 5秒 降低到 0.7秒。

第三个关键词我选择了比较常见的"时间"。这次的全文搜索则要更久,总耗时 553.69秒

# Query_time: 553.693291  Rows_sent: 256491  Rows_examined: 1838688

即便加上 LIMIT 5也需要543秒

SELECT ... LIMIT 5;
...
# Query_time: 543.437429  Rows_sent: 5  Rows_examined: 10

用LIKE的模糊搜索耗时7.9秒

# Query_time: 7.900584  Rows_sent: 256475  Rows_examined: 6438121
...
select *,0 as score from t3 where summary like '%时间%';

4. 继续放弃治疗

从上面的几个简单测试中能看出来InnoDB的全文索引效率还不是太可靠,如果没有靠谱的分词库的话,还是不建议在生产环境上使用,个人愚见,欢迎批评指正 :)

全文完。


本文分享自微信公众号 - 3306pai(pai3306),作者:叶金荣

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-05-11

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 扩展你的复制集:MongoDB 4.0中从节点的非阻塞读操作

    MongoDB 4.0增加了一个能力,在副本处理写操作的同时可以由从节点(secondary)读取数据。为了理解这个的重要性,让我们看看4.0版本之前从节点是如...

    [3306 Pai ] 社区
  • RadonDB架构解析

    RadonDB在DTCC大会主会场宣布开源了, 一个期待已久的产品终于走进了开源社区。 感谢青云领导层的对技术贡献的情怀。

    [3306 Pai ] 社区
  • 「3306π」成都站资料大放送

    感受了成都的美食、美景、萌妹砸(某月表示不想走了)、 还有技术圈的同学热情同时,3306π成都站活动也结束了。让我们回顾一下,本次活动和演讲老师的精彩分享。

    [3306 Pai ] 社区
  • 「自然语言处理(NLP)论文推送」清华大学XQA数据集(含源码)806

    本篇主要给大家介绍两篇文章:一篇是清华大学发表的XQA,该篇文章主要是针对开放式问答构建了一个跨语言的开放式问答数据集,该数据集(训练集、测试集)主要包括九种语...

    ShuYini
  • 框架篇-Django博客应用-添加样式

    <input type="text" name="username" maxlength="254" id="id_username">

    小团子
  • 慧安金科黄铃:减少对标注数据的依赖,做规避用户隐私的AI风控丨镁客请讲

    慧安金科主要通过自主研发的半监督主动式机器学习技术来构建金融风控方面的预测模型和决策引擎。

    镁客网
  • Mac做java开发(五):​hadoop的几个简单操作

    •资源:在 YARN 的语境下,资源特指计算资源,包括CPU 和内存。计算机的每个进程都会占用一定的CPU 和内存,任务需要先向RM 申请到资源后才能获准在NM...

    用户5473628
  • Django+xadmin打造在线教育平台(九)

    代码 github下载 十二、首页和全局404,500配置 12.1.首页功能 Course添加一个字段 is_banner = models.Boolean...

    zhang_derek
  • 短信接口发送验证码倒计时以及提交验证

    项目中找回密码的功能: 忘记密码,登录页面点击忘记密码,跳转页面输入用户名,如果用户名已经绑定手机号,后台调用短信接口给该用户绑定的手机发送短信,如果没有绑定手...

    二十三年蝉
  • iOS自定义UICollectionView和UITableView单元格选中样式

    陈满iOS

扫码关注云+社区

领取腾讯云代金券