学习
实践
活动
专区
工具
TVP
写文章
专栏首页「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

本文参与 腾讯云自媒体分享计划 ,欢迎热爱写作的你一起参与!

作者:叶金荣
原始发表时间:2020-05-11
如有侵权,请联系 cloudcommunity@tencent.com 删除。
登录 后参与评论
0 条评论

相关文章

  • MySQL 8.0.31并行构建索引特性管窥

    MySQL 8.0.31于2022.10.11发布了,比我预计的日期早了一周,先赞一个。

    老叶茶馆
  • mysql系列:全网最全索引类型汇总「建议收藏」

    除了常见的普通索引,唯一索引,组合索引,大家还能说一下mysql中有哪些其他类型的索引吗?

    全栈程序员站长
  • MySQL8.0大表秒加字段,是真的吗?

    很早就听说 MySQL8.0 支持快速加列,可以实现大表秒级加字段。笔者自己本地也有8.0环境,但一直未进行测试。本篇文章我们就一起来看下 MySQL8.0 快...

    MySQL技术
  • MySQL 模糊查询再也不用like+%了

    点击上方“芋道源码”,选择“设为星标” 管她前浪,还是后浪? 能浪的浪,才是好浪! 每天 10:33 更新文章,每天掉亿点点头发... 源码精品专栏 原创 |...

    芋道源码
  • 虾皮二面:MySQL 支持哪些存储引擎?MyISAM 和 InnoDB 的区别是什么?

    你好,我是 Guide。分享一道群友面试虾皮遇到的 MySQL 面试真题。原面试题如下:

    Guide哥
  • 资源丨MySQL故障排查思路方法PPT&视频&24问答

    昨晚,墨天轮邀请到MySQL技术顾问崔虎龙做了题为《一小时掌握MySQL故障排查思路方法》的直播分享,引起了大家的广泛关注,直播后很多小伙伴来找小编询问PPT、...

    数据和云
  • MySQL查漏补缺

    .example_responsive_1 { width: 200px; height: 50px; } @media(min-width: 290px)...

    草堂笺
  • Mysql删除表数据,表文件大小不变

    首先明确一个概念,innodb表包含两部分,表结构定义和数据,Mysql8.0以前表结构定义存放在.frm为后缀的文件里,而Mysql8.0版本以后允许表结构定...

    小土豆Yuki
  • 面试必问之mysql基础

    提示:使用哪一种引擎要根据需要灵活选择,一个数据库中多个表可以使用不同的引擎以满足各种性能和实际需求。使用合适的存储引擎将会提高整个数据库的性能。

    一笠风雨任生平
  • MySQL8.0大表秒加字段,是真的吗?

    很早就听说 MySQL8.0 支持快速加列,可以实现大表秒级加字段。笔者自己本地也有8.0环境,但一直未进行测试。本篇文章我们就一起来看下 MySQL8.0 快...

    码农架构
  • MySQL 8.0 需要调整哪些参数

    首先列出一个 8.0 my.cnf 的实例,这个只针对普通的单实例或者主从环境,对于其他环境,比如 MGR,可能有更多的一些参数需要调整。并且也不是最完整,最终...

    数据库交流
  • Mysql中MyISAM引擎和InnoDB引擎的比较

    结论 如果不清楚自己应该用什么引擎,那么请选择InnoDB,Mysql5.5+的版本默认引擎都是InnoDB,早期的Mysql版本默认的引擎是MyISAM --...

    老七Linux
  • 手把手搭建生产可用的Nacos集群

    本节详细探讨如何搭建一个生产可用的Nacos集群。讨论的内容主要包括:使用MySQL作为存储持久化数据,以及如何搭建Nacos集群。

    用户1516716
  • 美团面试官:讲清楚MySQL结构体系,立马发offer

    继续和大家分享,我去上海美团面试遇到的技术问题,当时,回答的也是马马虎虎的,不能说不好,也不能说好,反正就是没有给面试官一种爽的感觉。

    田维常
  • 不同数据库的特点_简述数据库的特点

    文档由一组key value组成。文档是动态模式,这意味着同一集合里的文档不需要有相同的字段和结构。在关系型数据库中table中的每一条记录相当于MongoDB...

    全栈程序员站长
  • 两千字揭密 MySQL 8.0.19 三大索引新功能:隐藏索引,降序索引,函数索引

    导读:本文详细介绍 MySQL 8.0.19 三大索引新功能,隐藏索引,降序索引,函数索引,结合其他同仁的技术应用案例,进一步进行验证改编,最后总结心得,希望对...

    数据和云
  • 转载|MySQL Online DDL,还是要谨慎

    其他的DDL操作相对比较少,所以本文就不讨论了。 此外,本文也不讨论非InnoDB引擎以及非普通索引(如全文索引、空间索引)的场景。

    田帅萌
  • 来,看看MySQL 5.6, 5.7, 8.0的新特性

    对于MySQL的历史,相信很多人早已耳熟能详,这里就不要赘述。下面仅从产品特性的角度梳理其发展过程中的里程碑事件。

    星哥玩云
  • MySQL Operator for Kubernetes

    MySQL Operator for Kubernetes是一个用于在Kubernetes集群里面管理安装InnoDB Cluster的工具。它现在已经开源在G...

    MySQLSE

扫码关注腾讯云开发者

领取腾讯云代金券