数据库大表查询改走ES查询方案笔记

最新推荐文章于 2024-09-10 16:09:06 发布

zhangbohun

最新推荐文章于 2024-09-10 16:09:06 发布

阅读量746

点赞数 7

文章标签：笔记 java elasticsearch mysql

本文链接：https://blog.csdn.net/zhangbohun/article/details/141093163

版权

0 本文主要涉及

将某业务场景 MySQL 数据库查询逻辑改造为走 ES 查询实现，包括数据同步方案& ES 慢查询原因分析处理

1 业务背景说明

具体现状：单次业务处理查询数据量可能较大，在高并发场景下，8核16G MySQL数据库CPU&IO压力极大，压测多用户场景，每个用户400条符合条件数据，上层接口QPS只能支持到80/s，RT到了300ms；
方案前提：共约两亿多数据，MySQL数据库已做分表，查询场景已经过索引优化虽然没有达到最优三星索引但已经很难优化，该查询场景是用户维度且查询结果时效性相对较高，所以排除了缓存优化的方案，走ES查询存在一定延迟但业务上可接受

2 改造方案简介

2.1 数据同步方案&实现

2.1.1全量历史数据同步

通过脚本程序循环批量从 BD 查询写入 ES
要注意历史数据循环读取时最好能通过 id 作为游标作为查询条件读取，不能直接分页读取，会有深度分页问题，具体方案：程序实现获取每次查询到的数据最后一个 id，然后下一次查询设置条件大于这个 id，然后用 limit 的语法来把查询细分成多个小段，然后用多线程方式来批量查询并写入ES

2.1.2实时数据同步

基于中间件组封装的 Canal-Server 服务监听数据库 binlog 然后通过 MQ消息消费把数据同步写入 ES

要注意 MySQL 数据库 binlog 配置需要为 ROW 模式，且 binlog_row_image 参数为 FULL，也就是 binlog 中会记录修改行所有列数据，再用 ES 的 RestHighLevelClient 通过 docAsUpsert 方式插入 ES 实现覆盖更新，这样就可以实现开启实时同步后再进行历史数据同步，直到所有数据完成同步，不会出现数据遗漏，可保证最终一致（理论上在全量数据同步过程中读取完 DB 数据后，DB 数据产生变更而后全量同步程序写入 ES 晚于实时同步写入 ES 可能导致数据为旧版本，所以这里要有一个实时同步和全量同步共用的变更数据缓存，不用很大只记录 id 和数据变更时间即可，根据数据变更时间判断控制只写入最新版数据）

show variables like 'binlog_format'

Variable_name        Value
binlog_format        ROW

show global variables like "binlog_row_image%";

Variable_name        Value
binlog_row_image     FULL

2.2 ES查询方案&实现

1 配置了和原 DB 查询数据 SQL 相同逻辑 ES 查询模板，实现相关查询逻辑，替代原 DB 查询方法
2 兜底处理，设置 Nacos 配置新老查询逻辑切换开关，以及 userid 维度配置，防止线上数据查询异常无法处理
3 在数据同步完成测试阶段发生了个小插曲，ES 查询效率比预估的慢很多了，每次查询需要近100ms 远远慢于之前预估的 10ms 内，经过分析排查确认是 ES 索引映射字段类型不合适导致，一些 status、type 状态类型字段使用了 Long 类型，只需要等值判断过滤条件但却走了不合理的范围查询

慢查询问题：
通过 Kibana 分析功能 Search Profiler 可以看到 status、type 等字段，是用的PointRangeQuery，查询总耗时较多，尤其 build_scorer 阶段耗时很大

截图略
一开始索引设置的 status、type 等字段设置为 Long 类型，在5.0版本以后 ES 底层使用的Lucene 引入了重新设计的数值类型的索引结构，不再采用倒排索，而是使用了更适合范围查找的 Block K-d Tree（一种有序树结构），实际 TermQuery 被转换为了 PointRangeQuery，但是 status、type 等字段实际都是只有几种值的字段，查询时没法利用 Block K-d Tree 优势进行范围查询而只是在创建 scorer 对象的时将满足查询条件的 docid 查出来够造成 docid 集合 bitset（build_scorer耗时），通过这个 bitset 再和其他查询结果进行关联处理

相关资料：
https://www.elastic.co/cn/blog/elasticsearch-query-execution-order
Query Planning for Range Queries in Elasticsearch | Elastic Blog
https://elasticsearch.cn/article/446
https://blog.csdn.net/ahwsk/article/details/104657123
https://elasticsearch.cn/question/3253
https://www.51cto.com/article/711044.html

后续将 status、type 等字段改为了 keyword 类型，走普通的 TermQuery 查询，每次查询基本保持在毫秒级