mysql大规模数据检索优化

港控/mmm° 2022-12-22 11:21 124阅读 0赞

业务中遇到如下场景：每天有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条数据的情况下：![image.png][]一次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][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就可以看到分词结果了![image.png][image.png 1]

可以发现，默认的分词规则下，`&、=、.`都会被用于分词规则，建立索引，而非以`key=val`这样一个整体来分词建索引，当我们用`key=val`检索的时候，大概率是得不到想要的结果的，val部分单独拿出来检索可能匹配出很多不合要求的结果。

这里的需求其实是只按照&来分词建索引，那么有两种方案：

1.  根据业务特点设计trick，如key和val中都不会出现`_`，而经测试`_`不会被fulltext默认分词引擎用于分词，可以将此字段中的`=、.`分别用 `__、_`来替换，检索的时候用同样的方案替换后检索
2.  使用自定义的分词引擎，后续会提到

这两种方案根据具体项目环境选择一种，都算是解决了。\[ 图简单当然是第一种好了:) \]

3.2 fulltext index 中文分词

qua的模糊匹配搞定了，现在看content字段的模糊匹配，在3.1中创建的测试表中插入一条中文句子`这是太空探测器在枯寂的宇宙中捕捉到的一幅极其震撼的画面`，再用其中的分词索引一下:

![image.png][image.png 2]可以发现，居然查不任何结果，而用like模糊匹配是有结果的。用3.1中提到的方法查一下索引内容：

![image.png][image.png 3]

可以发现，并没有像我们想象中的为中文分词建倒排索引，而是把整个句子作为一个分词了。这是因为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![image.png][image.png 4]也就是说用一个汉字去检索是查不到结果的，至少要两字词语。如果有需求变更的话，也在启动mysql的时候***我要说话***

mysqld --ngram_token_size=2

或者修改mysql配置文件***我要说话***

[mysqld] 
    ngram_token_size=2

来修改这个token长度***我要说话***

在这张400W大小的表里边，对content字段分别用like和fulltext索引查询速度对比如下：![image.png][image.png 5]***我要说话***

效果十分显著。***我要说话***

此外，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][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索引，然后查询对比：![image.png][image.png 6]发现加上普通的btree索引后，查询的速度已经很不错了，已经不需要额外的优化手段。***我要说话***

## 4.2多字段时间范围检索 ##

我面临的业务场景中，实际上是4.1中的单字段时间检索，不过在网上查资料的时候，发现`start_time > time1 and/or end_time < time2`这种多字段时间范围检索，在某些情况下并没有那么简单。话不多说，先试一把，还是构造一个包含`start_time、end_time`，两个字段的表，插入400W条数据，测试：![image.png][image.png 7]这结果，有点不忍直视了，说好的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本身输出的信息中也可见端倪：![image.png][image.png 8]***我要说话***

前者用索引就搞定了，所以速度飞快，而后者用了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'))));

![image.png][image.png 9]耗时回到了8ms！***我要说话***

根据不同的时间区间组合规则，检索函数应该在`MBRContains、MBRWithin、MBRIntersects、MBREqual、MBROverlaps、MBRTouches、MBRDisjoint`之间灵活选择，各函数范围图示如下：![image.png][image.png 10](图片来自于[这里][Link 1])***我要说话***

# 5.分页统计count #

在我的业务场景下，分页统计最麻烦的一点就是总量统计，难点在于：1.单表数据量大 2.按天分表，表数量太多。无任何检索条件下的单表总量count，由于单表数据量也是挺大的，速度并不是很快![image.png][image.png 11]***我要说话***

这没法接受啊，好在目前的业务场景下，数据是只会不断写入，不会修改、删除，所以这里引入一个trick，数据用一个自增的int型id作为主键，每次需要count全表的时候，查出最新一条数据的id即可，耗时1ms…***我要说话***

而在经过上面一通建索引之后，带索引字段的count数量已经很快了，这里在代码中按日期异步分批count，然后综合，速度上也还是可以接受的。

[image.png]: /images/20221120/28cc096995cf465eaf393fe550ccae88.png
[https_dev.mysql.com_doc_refman_5.7_en_innodb-information-schema-fulltext_index-tables.html]: https://dev.mysql.com/doc/refman/5.7/en/innodb-information-schema-fulltext_index-tables.html
[image.png 1]: /images/20221120/dcc4e6a6242746c2a44ba492b7fc4daf.png
[image.png 2]: /images/20221120/dafba9c57a2d425c9c91a1f710eb9f01.png
[image.png 3]: /images/20221120/ef6987fd177f4965a024f272554c8006.png
[image.png 4]: /images/20221120/692f4c8e29ba43128b6b8816fcb63522.png
[image.png 5]: /images/20221120/f7d80413201b47ce851cc75839e47ee7.png
[https_dev.mysql.com_doc_refman_5.7_en_writing-full-text-plugins.html]: https://dev.mysql.com/doc/refman/5.7/en/writing-full-text-plugins.html
[image.png 6]: /images/20221120/3c99955bb4064031b690a323ca0a3ac6.png
[image.png 7]: /images/20221120/7200fc2fdfa84f89811b2b827dba7ad0.png
[image.png 8]: /images/20221120/a8b4f140e3944942a9a519d0ee0c61d8.png
[image.png 9]: /images/20221120/e263dc52602c4816bf68a625ac615384.png
[image.png 10]: https://img-blog.csdnimg.cn/img_convert/7a53ab9aea92332f7ad35b37f4584876.png
[Link 1]: /images/20221120/501a81b364834f26ac99e2eca9a3ea92.png
[image.png 11]: /images/20221120/0e1177ca3d7e4c7c99c8331a4954d612.png