Join背景介绍

Join是数据库查询永远绕不开的话题,传统查询SQL技术总体可以分为简单操作(过滤操作-where、排序操作-limit等),聚合操作-groupby以及Join操作等。其中Join操作是最复杂、代价最大的操作类型,也是OLAP场景中使用相对较多的操作。因此很有必要对其进行深入研究。

 

另外,从业务层面来讲,用户在数仓建设的时候也会涉及Join使用的问题。通常情况下,数据仓库中的表一般会分为“低层次表”和“高层次表”。

 

所谓“低层次表”,就是数据源导入数仓之后直接生成的表,单表列值较少,一般可以明显归为维度表或事实表,表和表之间大多存在外健依赖,所以查询起来会遇到大量Join运算,查询效率很差。而“高层次表”是在“低层次表”的基础上加工转换而来,通常做法是使用SQL语句将需要Join的表预先进行合并形成“宽表”,在宽表上的查询不需要执行大量Join,效率很高。但宽表缺点是数据会有大量冗余,且相对生成较滞后,查询结果可能并不及时。

 

为了获得时效性更高的查询结果,大多数场景都需要进行复杂的Join操作。Join操作之所以复杂,主要是通常情况下其时间空间复杂度高,且有很多算法,在不同场景下需要选择特定算法才能获得最好的优化效果。本文将介绍SparkSQL所支持的几种常见的Join算法及其适用场景。

 

  1. mysql-5.7.14-winx64\bin配置到Path中
  2. 在解压路径下复制my-default.ini,修改名称为my.ini
  3. 在my.ini添加如下

     [mysqld]
    
     basedir=C:\\software\Mysql\mysql-5.7.14-winx64
    
     datadir=C:\\software\Mysql\mysql-5.7.14-winx64\data
    
     port=3306
    
  4. basedir:是上述mysql的解压路径

  5. datadir:后续初始化等数据都会保存在该目录下,在该文件目录下新建data文件夹

  6. 以管理员身份运行windows 命令行
  7. 进入mysql的解压缩bin目录
  8. 执行mysql –initialize –user=mysql –console
  9. 运行命令:mysqld –install [安装mysql服务]
  10. 如果已经存在—执行mysqld –remove
  11. 重新安装mysql服务,运行命令mysqld –install
  12. 命令行输入 mysql -u root -p,错误描述:error 1045 (28000)
  13. 在my.ini文件中在[mysqld]后一行加入skip-grant-tables
  14. 此时,关闭mysql服务,再重新启动
  15. net stop mysql
  16. net start mysql
  17. 不需输入密码,直接enter
  18. 选择mysql数据库:执行use mysql
  19. 对表user执行update操作:update user set authentication_string =
    ‘root’ where user=’root’
  20. 操作成功。退出mysql
  21. 重启mysql服务

 

 1 [root@zlm3 07:55:44 /usr/local/mysql/bin]
 2 #kill -9 7328
 3 
 4 [root@zlm3 07:57:15 /usr/local/mysql/bin]
 5 #/usr/local/mysql/bin/mysqld_safe: line 198:  7328 Killed                  nohup /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3306/my.cnf --basedir=/usr/local/mysql --datadir=/data/mysql/mysql3306/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=error.log --open-files-limit=65535 --pid-file=mysql.pid --socket=/tmp/mysql3306.sock --port=3306 < /dev/null > /dev/null 2>&1
 6 2018-06-04T05:57:15.076914Z mysqld_safe Number of processes running now: 0
 7 2018-06-04T05:57:15.083092Z mysqld_safe mysqld restarted
 8 ^C
 9 
10 [root@zlm3 07:57:20 /usr/local/mysql/bin]
11 #ps aux|grep mysqld
12 root      6307  0.0  0.1 113256  1676 pts/0    S    07:55   0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql3306/my.cnf
13 mysql     7385  2.4 17.7 1081288 180624 pts/0  Sl   07:57   0:00 /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3306/my.cnf --basedir=/usr/local/mysql --datadir=/data/mysql/mysql3306/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=error.log --open-files-limit=65535 --pid-file=mysql.pid --socket=/tmp/mysql3306.sock --port=3306
14 root      7419  0.0  0.0 112640   960 pts/0    R+   07:57   0:00 grep --color=auto mysqld
15 
16 [root@zlm3 07:57:24 /usr/local/mysql/bin]
17 #pkill mysqld
18 
19 [root@zlm3 07:57:37 /usr/local/mysql/bin]
20 #2018-06-04T05:57:38.957789Z mysqld_safe mysqld from pid file /data/mysql/mysql3306/data/mysql.pid ended
21 ^C
22 [1]+  Done                    mysqld_safe --defaults-file=/data/mysql/mysql3306/my.cnf
23 
24 [root@zlm3 07:57:48 /usr/local/mysql/bin]
25 #ps aux|grep mysqld
26 root      7439  0.0  0.0 112640   956 pts/0    R+   07:57   0:00 grep --color=auto mysqld
27 
28 [root@zlm3 07:57:55 /usr/local/mysql/bin]
29 #

Join常见分类以及基本实现机制

当前SparkSQL支持三种Join算法:shuffle hash join、broadcast hash
join以及sort merge join。其中前两者归根到底都属于hash join,只不过在hash
join之前需要先shuffle还是先broadcast。其实,hash
join算法来自于传统数据库,而shuffle和broadcast是大数据的皮(分布式),两者一结合就成了大数据的算法了。因此可以说,大数据的根就是传统数据库。既然hash
join是“内核”,那就刨出来看看,看完把“皮”再分析一下。

 

hash join

先来看看这样一条SQL语句:select * from order,item where item.id =
order.i_id,很简单一个Join节点,参与join的两张表是item和order,join
key分别是item.id以及order.i_id。现在假设这个Join采用的是hash
join算法,整个过程会经历三步:

  1. 确定Build Table以及Probe Table:这个概念比较重要,Build
    Table使用join key构建Hash Table,而Probe Table使用join
    key进行探测,探测成功就可以join在一起。通常情况下,小表会作为Build
    Table,大表作为Probe Table。此事例中item为Build Table,order为Probe
    Table。
  2. 构建Hash Table:依次读取Build
    Table(item)的数据,对于每一行数据根据join
    key(item.id)进行hash,hash到对应的Bucket,生成hash
    table中的一条记录。数据缓存在内存中,如果内存放不下需要dump到外存。
  3. 探测:再依次扫描Probe
    Table(order)的数据,使用相同的hash函数映射Hash
    Table中的记录,映射成功之后再检查join条件(item.id =
    order.i_id),如果匹配成功就可以将两者join在一起。

图片 1

 

基本流程可以参考上图,这里有两个小问题需要关注:

  1. hash join性能如何?很显然,hash
    join基本都只扫描两表一次,可以认为o(a+b),较之最极端的笛卡尔集运算a*b,不知甩了多少条街。
  2. 为什么Build Table选择小表?道理很简单,因为构建的Hash
    Table最好能全部加载在内存,效率最高;这也决定了hash
    join算法只适合至少一个小表的join场景,对于两个大表的join场景并不适用。

上文说过,hash
join是传统数据库中的单机join算法,在分布式环境下需要经过一定的分布式改造,就是尽可能利用分布式计算资源进行并行化计算,提高总体效率。hash
join分布式改造一般有两种经典方案:

  1. broadcast hash
    join:将其中一张小表广播分发到另一张大表所在的分区节点上,分别并发地与其上的分区记录进行hash
    join。broadcast适用于小表很小,可以直接广播的场景。
  2. shuffler hash
    join:一旦小表数据量较大,此时就不再适合进行广播分发。这种情况下,可以根据join
    key相同必然分区相同的原理,将两张表分别按照join
    key进行重新组织分区,这样就可以将join分而治之,划分为很多小join,充分利用集群资源并行化。

下面分别进行详细讲解。

someone
said that mysqld_safe will not be supported in the future release,but
what i’ve seen is the version 8.0 official document is that it still be
recommended:

本文来自 网易云社区 。

4.3.2 mysqld_safe — MySQL Server Startup Script

mysqld_safe is
the recommended way to start a mysqld server
on Unix. mysqld_safe adds
some safety features such as restarting the server when an error occurs
and logging runtime information to an error log. A description of error
logging is given later in this section.

 来源:  


 

summary:

   
another buddy in zst techique wechat group said that it will be messy in
troubleshooting while using mysqld_safe to startup mysqld,’cause in
some case,the mysqld_safe may lead to the ceaseless restarting of
mysqld.furthermore,it may destroy the evidences and logs which can be
diagnosted by DBAs.anyhow,in my opinion it depends:

  • if the
    bussines continuity is the first thing you need to consider,i
    recommend to use mysqld_safe method.
  • if your
    monitor system is strong enough or the application on the MySQL
    server is not so important such as enterprise management system,BBS
    system,i recommend to use mysqld method.

总结

Join操作是数据库和大数据计算中的高级特性,因为其独特的复杂性,很少有同学能够讲清楚其中的原理。本文试图带大家真正走进Join的世界,了解常用的几种Join算法以及各自的适用场景。后面两篇文章将会在此基础上不断深入Join内部,一点一点地揭开它的面纱,敬请关注!

 

本文已由作者范欣欣授权网易云社区发布,原文链接:SparkSQL大数据实战:揭开Join的神秘面纱

 

sort merge join

SparkSQL对两张大表join采用了全新的算法-sort-merge
join,如下图所示,整个过程分为三个步骤:

图片 2

 

  1. shuffle阶段:将两张大表根据join
    key进行重新分区,两张表数据会分布到整个集群,以便分布式并行处理。
  2. sort阶段:对单个分区节点的两表数据,分别进行排序。
  3. merge阶段:对排好序的两张分区表数据执行join操作。join操作很简单,分别遍历两个有序序列,碰到相同join
    key就merge输出,否则取更小一边。如下图所示:

图片 3

 

经过上文的分析,很明显可以得出来这几种Join的代价关系:cost(broadcast
hash join) < cost(shuffle hash join) < cost(sort merge
join),数据仓库设计时最好避免大表与大表的join查询,SparkSQL也可以根据内存资源、带宽资源适量将参数spark.sql.autoBroadcastJoinThreshold调大,让更多join实际执行为broadcast
hash join。

   
here we can see,there’re two processes running,one is
mysqld_safe,another one is the mysqld.even if you use “kill -9 7328” to
stop the mysqld process,but subsequently you’ll find that the mysqld
will startup again soon,unless you kill mysqld process by using “pkill
mysqld” as below:

Join操作是数据库和大数据计算中的高级特性,大多数场景都需要进行复杂的Join操作,本文从原理层面介绍了SparkSQL支持的常见Join算法及其适用场景。

For some
Linux platforms, MySQL installation from RPM or Debian packages includes
systemd support for managing MySQL server startup and shutdown. On these
platforms, mysqld_safe is
not installed because it is unnecessary. For more information,
see Section 2.5.9, “Managing MySQL Server with
systemd”.

shuffle hash join

在大数据条件下如果一张表很小,执行join操作最优的选择无疑是broadcast hash
join,效率最高。但是一旦小表数据量增大,广播所需内存、带宽等资源必然就会太大,broadcast
hash join就不再是最优方案。此时可以按照join
key进行分区,根据key相同必然分区相同的原理,就可以将大表join分而治之,划分为很多小表的join,充分利用集群资源并行化。如下图所示,shuffle
hash join也可以分为两步:

  1. shuffle阶段:分别将两个表按照join key进行分区,将相同join
    key的记录重分布到同一节点,两张表的数据会被重分布到集群中所有节点。这个过程称为shuffle。
  2. hash join阶段:每个分区节点上的数据单独执行单机hash join算法。

图片 4

 

看到这里,可以初步总结出来如果两张小表join可以直接使用单机版hash
join;如果一张大表join一张极小表,可以选择broadcast hash
join算法;而如果是一张大表join一张小表,则可以选择shuffle hash
join算法;那如果是两张大表进行join呢?

   
today,one buddy in IMG wechat group 2 asked “why i’ve installed the
MySQL 5.7 on linux server,but there’s no mysqld_safe command at
all?”so,here i’d like to post this article to say something about
it.first of all,let’s see the command parameter and usage:

发表评论

电子邮件地址不会被公开。 必填项已用*标注