Published on

同步MySQL到Elasticsearch的踩坑

Authors
  • avatar
    Name
    wellsleep (Liu Zheng)
    Twitter

在设计业务系统的过程中,由于 MySQL 单库对于千万或上亿行大数据量的查询,尤其是需要联表查询的效率非常不理想,此时除了优化现有数据库结构、优化慢查询之外,只有一种方式:通过分库分表,搭建分布式查询,进而提高数据库的读写效率。然而对分库分表的 MySQL 集群维护,并不是简单加机器节点能够达成的,而是需要 DBA 和后端开发充分沟通协作,这在有限的团队资源下,不太可行(比如并没有专职 DBA)。因此,考虑将关联型数据库中的数据实时同步到一个专门的数据库,用以实现快速横向扩展(增加分布式节点)并满足大量的只读查询。为了最大化的提高检索性能,需要数据库支持宽表(几十上百个列);同时为了简化运维,选择了 Elasticsearch。

从 MySQL 同步数据到 ES 却不是一帆风顺。首先 MySQL 作为生产数据库,会有大量的 CRUD 操作。如果通过自己写脚本 binlog 人工同步,一定存在可靠性和实时性的问题。如何保证生产数据以近实时的方式刷入 ES,有几种方案(阿里云自己的方案比较介绍):

  1. 使用 CloudCanel 这种开放的数据同步系统,自己在提供同步算力的物理机上安装好同步客户端,通过云平台的 GUI 界面录入传输配置,之后物理机自己完成同步操作;
  2. 阿里云用户使用 DTS (Data Transmission Service),理论上最优秀(毫秒级)的实时同步性能;
  3. ELK 套装用户使用 Logstash,通过 JDBC 连接 MySQL 后,定时读取数据,秒级同步;
  4. 阿里云用户使用 Dataworks 的「数据集成」服务,相当于接入到一个数据处理平台。其中数据传输需要花钱购买「资源组」,而数据处理的过程使用称为 MaxCompute 的 serverless 计算服务,通过累计计算时间收费。

这几种方案中,由于打算重新定义宽表,所以选择了第四种。然而在使用过程中发现,如果打算以秒级同步的方式拼接宽表(从五六个表中获得数据),会对 MySQL 数据库形成极大的查询压力,同时 MaxCompute 也并非为实时计算设计,更多是为离线同步而准备,拼一次最少 1 分钱,一天几万到几十万次拼接无法承受。最后决定,形成宽表的过程还是使用手动触发的方式(比如收到 MQ 后写逻辑在 ES 查询和写入)。因此如果只是为了将 MySQL 的数据按照表结构原封不动的同步到 ES,多种方案都可行,按最低配置(各不相同,有的 4c8g 有的 2c4g)计算成本:1 < 3 < 4 < 2。

不过 MaxCompute 为离线数据处理提供了一个新思路,大致是

graph LR
MySQL --> reader 
writer --> Elasticsearch
subgraph MaxCompute-周期运行
reader --> SQL --> writer
end

其中 reader / writer 可以接入各种数据形式,除了数据库还有 csv / txt 等通过 OSS 传入的文件。这应该是标准大数据开发中的一个重要步骤,只是目前还没有精力进一步研究。用 serverless 的思路,标准化地通过 MC SQL 语句处理各式各样的数据来源,再写入到不同的数据库中,不错的思路。

性能方面:在 ES 中试了一下用 python 脚本拼接宽表,对一个 400w 行的索引异步执行「两次查询一次写入」的组合,10000 次大约 26 秒。而通过 update-by-query 的方式「联表」,400w 条数据大概几秒到几十秒。而对 400w 行数据中某一字段做查询,全量返回 50 条命中文档的平均时间不超过 100ms,无比优秀。

ES 配置:2c4g * 3,阿里云服务