内容简介:业务中遇到如下场景:每天有400W条左右的数据需要存储,随后使用的时候需要根据一批字段进行检索,且支持分类,其中包含普通字段的检索、模糊匹配、按时间范围检索, 需要支持三个月内的数据检索。mysql单表数据量在过千万之后,读写性能会下降的比较厉害,而该业务场景下,每天产生的时候都有400W条左右,算下来一个月得有1.2亿条数据,三个月就是3.6亿条。说起来,这个量级的数据的检索,用mysql来做,本身是一件不科学的事情,应该采用Elasticsearch等比较专业的检索引擎组件,但是Elasticsearc
业务中遇到如下场景:每天有400W条左右的数据需要存储,随后使用的时候需要根据一批字段进行检索,且支持分类,其中包含普通字段的检索、模糊匹配、按时间范围检索, 需要支持三个月内的数据检索。
1.存储和检索方案
mysql单表数据量在过千万之后,读写性能会下降的比较厉害,而该业务场景下,每天产生的时候都有400W条左右,算下来一个月得有1.2亿条数据,三个月就是3.6亿条。
说起来,这个量级的数据的检索,用 mysql 来做,本身是一件不科学的事情,应该采用Elasticsearch等比较专业的检索引擎组件,但是Elasticsearch中本身只适合用需要检索的字段来构建索引,而不适合把其它结构化数据也存到其中,最终不需要检索的数据还是计划存放于mysql。所以,还是计划在mysql上挣扎一下,看看能不能通过各种骚操作来达到一个基本可用的效果,作为Elasticsearch的降级方案,回头发现无法支撑需求,再删掉mysql的索引,升级为Elasticsearch的索引。
数据的存储问题是比较好解决,也没啥其它的选择,每天400W条数据,按天分表存储即可。
但是这种存储方案下,检索就成了个大问题。需要检索的字段有四类:guid(普通字段检索)、qua(英文模糊匹配)、content(中文模糊匹配)、create_time(时间段检索)。
总结一下,面临的问题有:普通字段检索、中/英文模糊匹配、按时间范围检索、分页统计。
2.普通字段检索
普通字段如guid等,只需要建上Normal的Hash/BTree索引,即可快速检索。
3.模糊匹配
有俩字段需要做模糊匹配检索,qua主要是字母数字标点组成: key1=val1&key2=val2&key3=3.32555&key4=0.325&key5=val5
;content内容为中文句子,需要用其中的词语模糊匹配。
直接用 field like ‘%keyword%’来做模糊匹配,在数据量比较小的时候是OK的,然而,在构造了单表400W条数据的情况下:
一次like匹配,时间是13秒,这是无法接受的,必须要做分词建倒排索引,mysql通过fulltext index来支持这种场景。
3.1 fulltext index
首先看 key1=val1&key2=val2&key3=3.32555&key4=0.325&key5=val5
, 需要用 key1=val1
、 key3=3.32555
这样的键值对来做模糊匹配,可以对此字段建fulltext索引。那么如何确定mysql确实建立了对的索引呢?
参考mysql关于这部分的文档 https://dev.mysql.com/doc/refman/5.7/en/innodb-information-schema-fulltext_index-tables.html
SET GLOBAL innodb_ft_aux_table = 'test/articles'; SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE LIMIT 50;
用上面的 sql 就可以看到分词结果了
可以发现,默认的分词规则下, &、=、.
都会被用于分词规则,建立索引,而非以 key=val
这样一个整体来分词建索引,当我们用 key=val
检索的时候,大概率是得不到想要的结果的,val部分单独拿出来检索可能匹配出很多不合要求的结果。
这里的需求其实是只按照&来分词建索引,那么有两种方案:
- 根据业务特点设计trick,如key和val中都不会出现
_
,而经测试_
不会被fulltext默认分词引擎用于分词,可以将此字段中的=、.
分别用__、_
来替换,检索的时候用同样的方案替换后检索 - 使用自定义的分词引擎,后续会提到
这两种方案根据具体项目环境选择一种,都算是解决了。[ 图简单当然是第一种好了:) ]
3.2 fulltext index 中文分词
qua的模糊匹配搞定了,现在看content字段的模糊匹配,在3.1中创建的测试表中插入一条中文句子 这是太空探测器在枯寂的宇宙中捕捉到的一幅极其震撼的画面
,再用其中的分词索引一下:
可以发现,居然查不任何结果,而用like模糊匹配是有结果的。用3.1中提到的方法查一下索引内容:
可以发现,并没有像我们想象中的为中文分词建倒排索引,而是把整个句子作为一个分词了。这是因为mysql的fulltext index默认是不支持中文编码的分词的,中文编码分词比英文复杂的多,英文只需要按空格、标点来分词就好,但是中文就必须分析语义了。。。
好在mysql5.7之后的版本,已经支持了fulltext的中文分词功能。
在创建表的时候
CREATE TABLE `t_t2` ( `id` int(8) NOT NULL AUTO_INCREMENT, `content` varchar(512) NOT NULL DEFAULT '', PRIMARY KEY (`id`,`content`), FULLTEXT KEY `ix_content` (`content`) WITH PARSER `ngram` ) ENGINE=InnoDB AUTO_INCREMENT=7440063 DEFAULT CHARSET=utf8;
或者修改表添加索引
alter table t_t2 add fulltext index ix_content(content) with parser ngram; create fulltext index idx_content on t_t2(content) with parser ngram;
在这里我从网上下载了几十本中文小说,然后将内容按行、按标点分割成了几百万条短句,插入一张测试表的中文分词字段中,再查询一下索引内容:
可以发现已经按词进行分词了,这里要注意的是默认最小分词长度是2
也就是说用一个汉字去检索是查不到结果的,至少要两字词语。如果有需求变更的话,也在启动mysql的时候
mysqld --ngram_token_size=2
或者修改mysql配置文件
[mysqld] ngram_token_size=2
来修改这个token长度
在这张400W大小的表里边,对content字段分别用like和fulltext索引查询速度对比如下:
效果十分显著。
此外,match against还支持boolean mode和natural language mode,against里边的关键词也支持各种条件组合,业务中使用有需求的时候可以去查阅文档了解一下。
3.3 fulltext 自定义分词
mysql5.7支持自定义分词插件,如果实在有难搞的特殊需求,可以自己开发、安装插件。教程见mysql文档 https://dev.mysql.com/doc/refman/5.7/en/writing-full-text-plugins.html
4.时间范围检索
时间字段有两种模式,一种是需要检索的表中字段只有一个, create_time
,检索方式是 time1 < create_time < time2
;另一种是表中字段有两个, start_time、end_time
,检索方式是 start_time > time1 and/or end_time < time2
4.1单时间字段检索
这里构造了两张表,一张的时间字段是不包含索引的,另一张的时间字段建上普通的btree索引,然后查询对比:
发现加上普通的btree索引后,查询的速度已经很不错了,已经不需要额外的优化手段。
4.2多字段时间范围检索
我面临的业务场景中,实际上是4.1中的单字段时间检索,不过在网上查资料的时候,发现 start_time > time1 and/or end_time < time2
这种多字段时间范围检索,在某些情况下并没有那么简单。
话不多说,先试一把,还是构造一个包含 start_time、end_time
,两个字段的表,插入400W条数据,测试:
这结果,有点不忍直视了,说好的btree索引范围查询效率高呢? 只是查了10条数据啊,查了俩btree,速度就慢了3个数量级?用optimizer_trace分析一下吧:
set optimizer_trace="enabled=on"; EXPLAIN SELECT SQL_NO_CACHE * from t_time_range where start_time>'2019-03-08 12:12:11' and end_time<'2019-03-08 12:12:30' limit 0,10; select * from information_schema.optimizer_trace;
截取结果的关键部分:
"range_scan_alternatives": [ { "index": "idx_start", "ranges": [ "0x99a290c30b < start_time" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 2060603, "cost": 2.47e6, "chosen": true }, { "index": "idx_end", "ranges": [ "NULL < end_time < 0x99a290c31e" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 2060603, "cost": 2.47e6, "chosen": false, "cause": "cost" } ]
这意味着什么呢? 可以看到start_time的索引被选用了,返回2060603条数据,而end_time的索引没有被使用,需要在mysql server中用 NULL < end_time < 0x99a290c31e
这个条件再去过滤那2060603条数据,所以为了找到这10条数据,做了2060603此数据比对,这速度…
从explain本身输出的信息中也可见端倪:
前者用索引就搞定了,所以速度飞快,而后者用了start_time的索引,再去using where,即去索引结果中扫描行。
利用mysql空间索引可以优化这种时间范围的检索。基本思路是,将每条数据的 start_time、end_time
转换为秒级时间戳,然后创建一个LineString字段,以start、end分别为起点和终点表示一个LineString。最终检索的时候,使用MBR空间检索函数来得到想要的记录,如MBRContains、MBRWithin、MBRIntersects、MBREqual、MBROverlaps、MBRTouches、MBRDisjoint等。
创建一张含有LineString字段和空间索引的表:
CREATE TABLE `t_time_range` ( `id` int(8) NOT NULL AUTO_INCREMENT, `start_time` datetime DEFAULT NULL, `end_time` datetime DEFAULT NULL, `time_range` linestring NOT NULL, PRIMARY KEY (`id`), KEY `idx_start` (`start_time`) USING BTREE, KEY `idx_end` (`end_time`) USING BTREE, SPATIAL KEY `idx_range` (`time_range`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
也可以通过修改表来添加spatial空间索引
create spatial index idx_range on t_time_range(time_range);
然后插入测试数据400W条,例子如下,LineString的起点、终点分别是start_time、end_time的时间戳:
insert t_time_range(start_time, end_time, time_range) values ('2019-04-10 13:00:00','2019-04-10 13:00:10',LineString(Point(-1, 1554872400), Point(1,1554872410)));
最后,使用空间检索函数:
SELECT SQL_NO_CACHE * from t_time_range where start_time>'2019-03-08 12:12:11' and MBRWithin(time_range, LineString(Point(-1, UNIX_TIMESTAMP('2019-03-08 12:12:11')), Point(1, UNIX_TIMESTAMP('2019-03-08 12:12:30'))));
耗时回到了8ms!
根据不同的时间区间组合规则,检索函数应该在 MBRContains、MBRWithin、MBRIntersects、MBREqual、MBROverlaps、MBRTouches、MBRDisjoint
之间灵活选择,各函数范围图示如下:
(图片来自于 这里 )
5.分页统计count
在我的业务场景下,分页统计最麻烦的一点就是总量统计,难点在于:1.单表数据量大 2.按天分表,表数量太多。
无任何检索条件下的单表总量count,由于单表数据量也是挺大的,速度并不是很快
这没法接受啊,好在目前的业务场景下,数据是只会不断写入,不会修改、删除,所以这里引入一个trick,数据用一个自增的int型id作为主键,每次需要count全表的时候,查出最新一条数据的id即可,耗时1ms…
而在经过上面一通建索引之后,带索引字段的count数量已经很快了,这里在代码中按日期异步分批count,然后综合,速度上也还是可以接受的。
以上所述就是小编给大家介绍的《mysql大规模数据检索优化》,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持!
猜你喜欢:- ElasticSearch 亿级数据检索深度优化
- Hibernate Search 5.10.0.Final 发布,数据检索框架
- Hibernate Search 5.11.0.Final 发布,数据检索框架
- Hibernate Search 5.9.0.beta1 发布,数据检索框架
- Hibernate Search 5.9.0.CR1 发布,数据检索框架
- Hibernate Search 5.9.0.CR1 发布,数据检索框架
本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们。
MD5 加密
MD5 加密工具
HSV CMYK 转换工具
HSV CMYK互换工具