join大表和小表对查询性能的影响和索引类型的选择

原创 2018年04月16日 16:54:31

join大表和小表对查询性能的影响和索引类型的选择

示例数据库采用PostgreSQL

1.创建数据表和相关函数

drop function if exists gen_random_gps(bigint);
drop function if exists gps_save(bigint,bigint,timestamptz,geometry);
drop function if exists gen_random_plate(int,int);
drop function if exists gen_random_az(int);
drop function if exists gen_random_num(int);
drop table if exists  vehgps;
drop table if exists  vehicles;

/****************************************************************************************
    车辆基本信息表
drop table if exists  vehicles;
****************************************************************************************/
create table vehicles(
    objectid int not null,                       --唯一编号
    numplate text not null,                      --车牌号
    describe text,                               --备注
  generate timestamptz default now() not null, --注册时间
    constraint pk_vehicles_objectid primary key (objectid)
);
create index idx_vehicles_numplate on vehicles(numplate);
/****************************************************************************************
    车辆GPS轨迹表
drop table if exists  vehiclegps;
****************************************************************************************/
create table if not exists vehiclegps(
    objectid bigint not null,                           --唯一编号
    vehid int not null,                                 --车辆编号,外键
    generate timestamptz default now() not null,        --GPS时间,实际应用应按此字段分表
    constraint pk_vehiclegps_objectid primary key (objectid),
    constraint fk_vehiclegps_vehid foreign key(vehid)  references vehicles(objectid) on delete cascade
);
create index idx_vehiclegps_vehid on vehiclegps(vehid); --车辆编号
select AddGeometryColumn ('vehiclegps','geom',4326,'POINT',2); -- GPS位置,GPS采用4326 ,类型为点,二维坐标
create index idx_vehiclegps_generate on vehiclegps using brin(generate)  with (pages_per_range='1');
/****************************************************************************************
    车辆基本信息函数
drop function if exists gen_random_plate(int,int);
drop function if exists gen_random_az(int);
drop function if exists gen_random_num(int);
****************************************************************************************/
--生成指定位数的数字
create or replace function gen_random_num(int)
    returns text
as $$
	select string_agg(chr((random()*(57-48)+48 )::integer) , '')  from generate_series(1,$1);
$$ language sql;
--生成指定位数的大写A-Z
create or replace function gen_random_az(int)
    returns text
as $$
	select string_agg(chr((random()*(90-65)+65 )::integer) , '')  from generate_series(1,$1);
$$ language sql;
--随机生成车牌号
create or replace function gen_random_plate(int default 1,int default 5)
    returns text
as $$
	with cte as(
		select array['浙','粤','京','津','冀','晋','蒙','辽','黑','沪','吉','苏','皖','赣','鲁','豫','鄂','湘','桂','琼','渝','川','贵','云','藏','陕','甘','青','宁'] as provinces
	),provinces as(
		select array_to_string(array[provinces[(random()*(29-1)+1)::integer],gen_random_az($1)],'') as pro,
				gen_random_num($2) as num
			from cte
	) select array_to_string(array[pro,num],'-') from provinces
$$ language sql;
/****************************************************************************************
    随机插入车辆GPS轨迹函数,调用一次插件1千条记录,gps时间控制在2018-01-01至2018-04-16之间
drop function if exists gen_random_gps(bigint);
drop function if exists gps_save(bigint,bigint,timestamptz,geometry);
****************************************************************************************/
create or replace function gen_random_gps(bigint)
    returns table(objectid bigint,vehid bigint,generate timestamptz,geom geometry)
as $$
	with cte as(
		select start,(start + 999) as end,
			extract(epoch from '2018-01-01'::timestamptz) as sdate,
			extract(epoch from '2018-04-16'::timestamptz) as edata,
			(select array[min(objectid),max(objectid)] from vehicles) as vehid			
		from (select $1 as start) as tmp
	),points as(
		select id,
			((random()*(vehid[2]-vehid[1])+vehid[1])::bigint) as vehid, --车辆id是连续的才这么干
			to_timestamp((random()*(edata-sdate)+sdate)) as gpstime,
			ST_SetSRID(ST_Point(
				round((random()*(135.085831-73.406586)+73.406586)::numeric,6),
				round((random()*(53.880950-3.408477)+3.408477)::numeric,6)
			),4326) as geom
		from cte,generate_series(cte.start,cte.end) as id
	)select * from points
$$ language sql;
--保存GPS轨迹
create or replace function gps_save(bigint,bigint,timestamptz,geometry)
    returns bigint
as $$
	insert into vehiclegps(objectid,vehid,generate,geom) values($1,$2,$3,$4) returning objectid;
$$ language sql;

2.插入测试数据

/****************************************************************************************
    随机插入车辆基本信息测试数据(50W)
****************************************************************************************/
insert into vehicles  select id, gen_random_plate() from generate_series(1,500000) as id;
/****************************************************************************************
    随机插入车辆GPS轨迹测试数据(10000000W)
****************************************************************************************/
do $$
	declare
		v_id bigint;
	begin
		for i in 1..10000 loop
			select max(id) into v_id from (select gps_save(objectid,vehid,generate,geom) as id from gen_random_gps((i-1)*1000 + 1)) as tmp;
			raise notice  '%', v_id;
		end loop;
	end;
$$;

为了不影响执行计划数据插入完成后运行vacuum

vacuum full freeze VERBOSE vehicles;
vacuum full freeze VERBOSE vehiclegps;

3.在本案例中b树索引和brin范围索引比较

3.1 测试brin类型索引

在本案例中,因为单位时间内的gps数据非常密集,brin类型索引只有在一个非常小的范围内(大约3小时以内的数据,返回的数据约为14000行,执行时间约为360ms,以实测为准,误差基本不会太大)才有效(注意pages_per_range已经设置为1).

drop index if exists  idx_vehiclegps_generate;
create index idx_vehiclegps_generate on vehiclegps using brin(generate) with (pages_per_range='1');
--测试获取3小时25分内的数据
explain (analyze,verbose,costs,buffers,timing)
select * from vehiclegps
where generate>=('2018-01-01 12:00:00'::timestamptz) and generate<('2018-01-01 15:25:00'::timestamptz)
--测试获取一天内的数据
explain (analyze,verbose,costs,buffers,timing)
select * from vehiclegps
where generate>=('2018-01-01 12:00:00'::timestamptz) and generate<('2018-01-01 15:25:00'::timestamptz)

3.2 测试B树索引

在本案例中,B对索引表现优于brin.

drop index if exists  idx_vehiclegps_generate;
create index idx_vehiclegps_generate on vehiclegps(generate);
--测试获取3小时25分内的数据
explain (analyze,verbose,costs,buffers,timing)
select * from vehiclegps
where generate>=('2018-01-01 12:00:00'::timestamptz) and generate<('2018-01-01 15:25:00'::timestamptz)
--测试获取一天内的数据
explain (analyze,verbose,costs,buffers,timing)
select * from vehiclegps
where generate>=('2018-01-01 12:00:00'::timestamptz) and generate<('2018-01-01 15:25:00'::timestamptz)

本章小结

  • 任何事物都有适用范围,实际如何使用要根据业务需求测试后选择.
  • brin类型的索引适用于数据比较稀疏,跨度比较大的应用
  • B树索引适用于大多数应用

4.join查询

根据第3章的结论,后面的示例采用B树索引.

4.1 大表join小表

/****************************************************************************************
    大表join小表
****************************************************************************************/
explain (analyze,verbose,costs,buffers,timing)
select gps.* from vehiclegps as gps
inner join vehicles as veh on veh.objectid=gps.vehid
where gps.generate>=('2018-01-01'::timestamptz) and gps.generate<('2018-01-02'::timestamptz)

4.2 小表join大表

explain (analyze,verbose,costs,buffers,timing)
select gps.* from vehicles as veh
inner join vehiclegps as gps on gps.vehid=veh.objectid
where gps.generate>=('2018-01-01'::timestamptz) and gps.generate<('2018-01-02'::timestamptz)

本章小结

  • 基本没有区别.

Hive中小表与大表关联(join)的性能分析

经常看到一些Hive优化的建议中说当小表与大表做关联时,把小表写在前面,这样可以使Hive的关联速度更快,提到的原因都是说因为小表可以先放到内存中,然后大表的每条记录再去内存中检测,最终完成关联查询。...
  • wisgood
  • wisgood
  • 2014年01月01日 21:59
  • 4220

了解MySQL联表查询中的驱动表,优化查询,以小表驱动大表

一、为什么要用小表驱动大表 1、驱动表的定义 当进行多表连接查询时, [驱动表] 的定义为: 1)指定了联接条件时,满足查询条件的记录行数少的表为[驱动表] 2)未指定联接...
  • dc2222333
  • dc2222333
  • 2017年10月14日 15:41
  • 1817

spark-大表join优化方案

数据量: 1~2G左右的表与3~4T的大表进行Join拆分 将任务数据分为多个结果RDD,将各个RDD的数据写入临时的hdfs目录,最后合并调整并行度和shuffle参数 spark-submi...
  • qq_16038125
  • qq_16038125
  • 2017年06月09日 14:04
  • 1898

Nexted-loop join中小表驱动大表的原因分析

nexted-loop join: outer_iterator = SELECT A.xx,A.c FROM A WHERE A.xx IN (5,6); outer_row = outer_i...
  • light_language
  • light_language
  • 2017年07月31日 20:53
  • 474

hive join 优化 --小表join大表

1、小、大表 join 在小表和大表进行join时,将小表放在前边,效率会高,hive会将小表进行缓存。 2、mapjoin 使用mapjoin将小表放入内存,在map端和大表逐一匹配,从而省去red...
  • smile0198
  • smile0198
  • 2014年10月25日 21:49
  • 4956

29 超大表与超小表HASH JOIN优化方法--优化主题系列

select * from a,b where a.id=b.id; a表100GB b表50MB 如何优化??   select *,(select * from b wherea.id=b....
  • leo0805
  • leo0805
  • 2018年01月11日 17:53
  • 90

【慢查询优化】连表查询注意谁是驱动表&搞不清楚谁join谁更好时放手让mysql自行判定

写在前面的话:    不要求每个人一定理解 联表查询(join/left join/inner join等)时的mysql运算过程;    不要求每个人一定知道线上(现在或未来)哪张表数据...
  • doris_crazy
  • doris_crazy
  • 2016年06月28日 13:55
  • 1422

sparksql优化1(小表大表关联优化 & union替换or)

----原语句(运行18min) INSERT into TABLE schema.dstable SELECT bb.ip FROM (SELECT ip, sum(click) cl...
  • xjping0794
  • xjping0794
  • 2017年11月07日 18:03
  • 541

OLAP 大表和小表并行hash join

一个表50MB 一个表10GB 50M表做驱动表,放在PGA里 这时候慢在对对 10g 的全表扫描 对10个G扫描块 需要开并行 我有这样一个算法 ...
  • zhaoyangjian724
  • zhaoyangjian724
  • 2014年03月01日 20:15
  • 1013

20亿与20亿表关联优化方法(超级大表与超级大表join优化方法)

记得5年前遇到一个SQL,就是一个简单的两表关联,SQL跑了差不多一天一夜,这两个表都非常巨大,每个表都有几十个G,数据量每个表有20多亿,表的字段也特别多。 相信大家也知道SQL慢在哪里了,单个进...
  • robinson1988
  • robinson1988
  • 2016年02月27日 21:57
  • 14927
收藏助手
不良信息举报
您举报文章:join大表和小表对查询性能的影响和索引类型的选择
举报原因:
原因补充:

(最多只允许输入30个字)