hive中大表关联小表用join还是where 大表和小表关联

转载

mob64ca1412ee79 2023-11-10 13:56:16

文章标签 小表驱动大表 MySQL 执行计划执行时间执行效率 文章分类 Hive 大数据

前言

在数据库查询中，经常用到表关联，听到最多的规则是 “小表驱动大表”。那么问题来了

什么是小表驱动大表？
为什么要用小表驱动大表？
怎么区分那个是驱动表与被驱动表？
JOIN查询如何选择驱动表与被驱动表 ?
索引应该建在驱动表还是被驱动表？

1.什么是小表驱动大表？

小表驱动大表指的是用小的数据集驱动大得数据集。

2.为什么要用小表驱动大表？

例如：现有两个表A与B ，表A有200条数据，表B有20万条数据 ;
按照循环的概念举个例子

小表驱动大表 > A驱动表，B被驱动表

for(200条){
   for(20万条){
     ...
   }
 }

大表驱动小表 > B驱动表，A被驱动表

for(20万){
   for(200条){
    ...
   }
 }

总结：

如果小的循环在外层，对于表连接来说就只连接200次 ;
如果大的循环在外层，则需要进行20万次表连接，从而浪费资源，增加消耗 ;

综上:
小表驱动大表的主要目的是通过**减少表连接创建的次数，**加快查询速度。

3.怎么区分那个是驱动表与被驱动表？

通过EXPLAIN查看SQL语句的执行计划可以判断在谁是驱动表，EXPLAIN语句分析出来的第一行的表即是驱动表 ;

4.JOIN查询如何选择驱动表与被驱动表 ?

在JOIN查询中经常用到的 inner join、left join、right join
问题解答：
1.当使用left join时，左表是驱动表，右表是被驱动表 ;
2.当使用right join时，右表时驱动表，左表是被驱动表 ;
3.当使用inner join时，mysql会选择数据量比较小的表作为驱动表，大表作为被驱动表 ;

测试结论：
测试环境配置：MYSQL 5.7
数据准备：
创建两张测试表大表 user_big_info ，测试数据400万条, 小表user_small_info ,测试数据200万条 ;

CREATE TABLE `user_small_info` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `user_id` varchar(32) NOT NULL COMMENT '用户唯一标识',
  `username` varchar(32) NOT NULL DEFAULT '' COMMENT '用户名',
  `password` varchar(255) NOT NULL DEFAULT '' COMMENT '密码',
  `real_name` varchar(32) NOT NULL DEFAULT '' COMMENT '真实姓名',
  `phone` varchar(32) NOT NULL DEFAULT '' COMMENT '手机号码',
  `remarks` varchar(255) NOT NULL DEFAULT '' COMMENT '备注',
  `status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '状态 1-启用 2-禁用 ',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_user_id` (`user_id`) USING BTREE,
  KEY `idx_username` (`username`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3000001 DEFAULT CHARSET=utf8 COMMENT='用户表';

LEFT JOIN 测试

小表驱动大表

hive中大表关联小表用join还是where 大表和小表关联_小表驱动大表