分布式数据库 Join 查询设计与实现浅析 | 京东云技术团队

京东云开发者
• 阅读 143

相对于单例数据库的查询操作,分布式数据查询会有很多技术难题。

本文记录 Mysql 分库分表 和 Elasticsearch Join 查询的实现思路,了解分布式场景数据处理的设计方案。
文章从常用的关系型数据库 MySQL 的分库分表Join 分析,再到非关系型 ElasticSearch 来分析 Join 实现策略。逐步深入Join 的实现机制。

①Mysql 分库分表 Join 查询场景

分库分表场景下,查询语句如何分发,数据如何组织。相较于NoSQL 数据库,Mysql 在SQL 规范的范围内,相对比较容易适配分布式场景。

基于 sharding-jdbc 中间件的方案,了解整个设计思路。

sharding-jdbc

  • sharding-jdbc 代理了原始的 datasource, 实现 jdbc 规范来完成分库分表的分发和组装,应用层无感知。
  • 执行流程:SQL解析 => 执行器优化 => SQL路由 => SQL改写 => SQL执行 => 结果归并 io.shardingsphere.core.executor.ExecutorEngine#execute
  • Join 语句的解析,决定了要分发 SQL 到哪些实例节点上。对应SQL路由。
  • SQL 改写就是要把原始(逻辑)表名,改为实际分片的表名。
  • 复杂情况下,Join 查询分发的最多执行的次数 = 数据库实例 × 表A分片数 × 表B分片数

Code Insight

示例代码工程:git@github.com:cluoHeadon/sharding-jdbc-demo.git

/**
 * 执行查询 SQL 切入点,从这里可以完整 debug 执行流程
 * @see ShardingPreparedStatement#execute()
 * @see ParsingSQLRouter#route(String, List, SQLStatement) Join 查询实际涉及哪些表,就是在路由规则里匹配得出来的。
 */
public boolean execute() throws SQLException {
    try {
        // 根据参数(决定分片)和具体的SQL 来匹配相关的实际 Table。
        Collection<PreparedStatementUnit> preparedStatementUnits = route();
        // 使用线程池,分发执行和结果归并。
        return new PreparedStatementExecutor(getConnection().getShardingContext().getExecutorEngine(), routeResult.getSqlStatement().getType(), preparedStatementUnits).execute();
    } finally {
        JDBCShardingRefreshHandler.build(routeResult, connection).execute();
        clearBatch();
    }
}

SQL 路由策略

启用 sql 打印,直观看到实际分发执行的 SQL

# 打印的代码,就是在上述route 得出 ExecutionUnits 后,打印的
sharding.jdbc.config.sharding.props.sql.show=true

sharding-jdbc 根据不同的SQL 语句,会有不同的路由策略。我们关注的 Join 查询,实际相关就是以下两种策略。

  • StandardRoutingEngine binding-tables 模式
  • ComplexRoutingEngine 最复杂的情况,笛卡尔组合关联关系
-- 参数不明,不能定位分片的情况
select * from order o inner join order_item oi on o.order_id = oi.order_id 

-- 路由结果
-- Actual SQL: db1 ::: select * from order_1 o inner join order_item_1 oi on o.order_id = oi.order_id 
-- Actual SQL: db1 ::: select * from order_1 o inner join order_item_0 oi on o.order_id = oi.order_id 
-- Actual SQL: db1 ::: select * from order_0 o inner join order_item_1 oi on o.order_id = oi.order_id 
-- Actual SQL: db1 ::: select * from order_0 o inner join order_item_0 oi on o.order_id = oi.order_id 
-- Actual SQL: db0 ::: select * from order_1 o inner join order_item_1 oi on o.order_id = oi.order_id 
-- Actual SQL: db0 ::: select * from order_1 o inner join order_item_0 oi on o.order_id = oi.order_id 
-- Actual SQL: db0 ::: select * from order_0 o inner join order_item_1 oi on o.order_id = oi.order_id 
-- Actual SQL: db0 ::: select * from order_0 o inner join order_item_0 oi on o.order_id = oi.order_id

②Elasticsearch Join 查询场景

首先,对于 NoSQL 数据库,要求 Join 查询,可以考虑是不是使用场景和用法有问题。

然后,不可避免的,有些场景需要这个功能。Join 查询的实现更贴近SQL 引擎。

基于 elasticsearch-sql 组件的方案,了解大概的实现思路。

elasticsearch-sql

  • 这是个elasticsearch 插件,通过提供http 服务实现类 SQL 查询的功能,高版本的elasticsearch 已经具备该功能⭐
  • 因为 elasticsearch 没有 Join 查询的特性,所以实现 SQL Join 功能,需要提供更加底层的功能,涉及到 Join 算法。

Code Insight

源码地址:git@github.com:NLPchina/elasticsearch-sql.git

/**
 * Execute the ActionRequest and returns the REST response using the channel.
 * @see ElasticDefaultRestExecutor#execute
 * @see ESJoinQueryActionFactory#createJoinAction Join 算法选择
 */
@Override
public void execute(Client client, Map<String, String> params, QueryAction queryAction, RestChannel channel) throws Exception{
    // sql parse
    SqlElasticRequestBuilder requestBuilder = queryAction.explain();

    // join 查询
    if(requestBuilder instanceof JoinRequestBuilder){
        // join 算法选择。包括:HashJoinElasticExecutor、NestedLoopsElasticExecutor
        // 如果关联条件为等值(Condition.OPEAR.EQ),则使用 HashJoinElasticExecutor
        ElasticJoinExecutor executor = ElasticJoinExecutor.createJoinExecutor(client,requestBuilder);
        executor.run();
        executor.sendResponse(channel);
    }
    // 其他类型查询 ...
}

③More Than Join

Join 算法

  • 常用三种 Join 算法:Nested Loop Join,Hash Join、 Merge Join
  • MySQL 只支持 NLJ 或其变种,8.0.18 版本后支持 Hash Join
  • NLJ 相当于两个嵌套循环,用第一张表做 Outter Loop,第二张表做 Inner Loop,Outter Loop 的每一条记录跟 Inner Loop 的记录作比较,最终符合条件的就将该数据记录。
  • Hash Join 分为两个阶段; build 构建阶段和 probe 探测阶段。
  • 可以使用Explain 查看 MySQL 使用哪种 Join 算法。 需要的语法关键字: FORMAT=JSON or FORMAT=Tree
EXPLAIN FORMAT=JSON  
SELECT * FROM
    sale_line_info u
    JOIN sale_line_manager o ON u.sale_line_code = o.sale_line_code;
{
    "query_block": {
        "select_id": 1,
        // 使用的join 算法: nested_loop
        "nested_loop": [
            // 涉及join 的表以及对应的 key,其他的信息与常用explain 类似
            {
                "table": {
                    "table_name": "o",
                    "access_type": "ALL"
                }
            },
            {
                "table": {
                    "table_name": "u",
                    "access_type": "ref"
                }
            }
        ]
    }
}

Elasticsearch Nested类型

分析Elasticsearch 业务数据以及使用场景,还有一种选择是直接存储关联信息的文档。在 Elasticsearch 中,是以完整文档形式提供查询和检索,彻底避开使用 Join 相关的技术。

这样就牵扯到关联是归属类型的数据还是公用类型的数据、关联数据量的大小、关联数据的更新频率等。这些都是使用 Nested 类型需要考虑的因素。

更多的使用方法,可以从网上和官网找到,不做赘述。
我们现在有个业务功能正好使用到 Nested类型, 在查询和优化过程中,解决了非常大的难题。

总结

通过运行原理分析,对于运行流程有了清晰和深入的认知。

对于中间件的优化和技术选型更加有目的性,使用上会更加谨慎和小心。

明确的筛选条件,更小的筛选范围,limit 取值数据,都可以减少计算陈本,提高性能。

参考

作者:京东物流 杨攀

来源:京东云开发者社区

点赞
收藏
评论区
推荐文章
Easter79 Easter79
2年前
sql join
1.1.1摘要Join是关系型数据库系统的重要操作之一,SQLServer中包含的常用Join:内联接、外联接和交叉联接等。如果我们想在两个或以上的表获取其中从一个表中的行与另一个表中的行匹配的数据,这时我们应该考虑使用Join,因为Join具体联接表或函数进行查询的特性本文将通过具体例子介绍SQL中的各种常用Join的特性和使
Stella981 Stella981
2年前
Flink SQL 如何实现数据流的 Join?
无论在OLAP还是OLTP领域,Join都是业务常会涉及到且优化规则比较复杂的SQL语句。对于离线计算而言,经过数据库领域多年的积累,Join语义以及实现已经十分成熟,然而对于近年来刚兴起的StreamingSQL来说Join却处于刚起步的状态。其中最为关键的问题在于Join的实现依赖于缓存整个数据集,而Strea
Stella981 Stella981
2年前
Nginx + lua +[memcached,redis]
精品案例1、Nginxluamemcached,redis实现网站灰度发布2、分库分表/基于Leaf组件实现的全球唯一ID(非UUID)3、Redis独立数据监控,实现订单超时操作/MQ死信操作SelectPollEpollReactor模型4、分布式任务调试Quartz应用
Wesley13 Wesley13
2年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Stella981 Stella981
2年前
Flink 双流 Join 的3种操作示例
在数据库中的静态表上做OLAP分析时,两表join是非常常见的操作。同理,在流式处理作业中,有时也需要在两条流上做join以获得更丰富的信息。FlinkDataStreamAPI为用户提供了3个算子来实现双流join,分别是:join()coGroup()intervalJoin()本文举例说明它们的使
Stella981 Stella981
2年前
SparkSQL的3种Join实现
引言Join是SQL语句中的常用操作,良好的表结构能够将数据分散在不同的表中,使其符合某种范式,减少表冗余、更新容错等。而建立表和表之间关系的最佳方式就是Join操作。对于Spark来说有3中Join的实现,每种Join对应着不同的应用场景:BroadcastHashJoin:适合一张较小的表和一张大表进行joinShuffleHash
Wesley13 Wesley13
2年前
560字带你彻底搞懂:MySQL的索引优化分析
正文一、SQL分析性能下降、SQL慢、执行时间长、等待时间长查询语句写得差索引失效关联查询太多join(设计缺陷)单值索引:在user表中给name属性创建索引,createindexidx\_nameonu
Wesley13 Wesley13
2年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
Stella981 Stella981
2年前
ELK学习笔记之ElasticSearch的索引详解
0x00ElasticSearch的索引和MySQL的索引方式对比Elasticsearch是通过Lucene的倒排索引技术实现比关系型数据库更快的过滤。特别是它对多条件的过滤支持非常好,比如年龄在18和30之间,性别为女性这样的组合查询。倒排索引很多地方都有介绍,但是其比关系型
Wesley13 Wesley13
2年前
mysql减少join的几种通用方法
1关于join只要参与过后台开发,必然都对join有一定的了解.我们使用join查询,主要为满足两方面的需求:No.需求说明典型相似操作效果对比1查询关联表内容,如主从表之间内容子查询不考虑索引的情况下,join查询效率一般优于前者;即使考虑索引,多数情况子查询的索引并不好设计2多表关系限制in