hashパーティション
パーティション数=10
100万件と1000万件のテーブルを作成
(8.0.29)
drop table tab1;
create table tab1(
col1 int
)
partition by hash(col1)
(partition p00,
partition p01,
partition p02,
partition p03,
partition p04,
partition p05,
partition p06,
partition p07,
partition p08,
partition p09)
;
drop table tab2;
create table tab2(
col1 int
)
partition by hash(col1)
(partition p00,
partition p01,
partition p02,
partition p03,
partition p04,
partition p05,
partition p06,
partition p07,
partition p08,
partition p09)
;
drop procedure proc1;
delimiter //
create procedure proc1()
begin
declare i int;
set i = 1;
start transaction;
while i <= 1000000 do
insert into tab1 select floor(rand() * 1000000)+1;
set i = i + 1;
end while;
commit;
end
//
delimiter ;
call proc1();
drop procedure proc2;
delimiter //
create procedure proc2()
begin
declare i int;
set i = 1;
start transaction;
while i <= 10000000 do
insert into tab2 select floor(rand() * 10000000)+1;
set i = i + 1;
end while;
commit;
end
//
delimiter ;
call proc2();
select count(*) from tab1;
select count(*) from tab1 partition(p00);
select count(*) from tab2;
select count(*) from tab2 partition(p00);
analyze table tab1;
analyze table tab2;
pager cat /dev/null
nopager
explain format=tree
select * from tab1 inner join tab2
using (col1)
;
explain format=tree
select * from tab1 partition(p00) inner join tab2 partition(p00) using (col1)
union all
select * from tab1 partition(p01) inner join tab2 partition(p01) using (col1)
union all
select * from tab1 partition(p02) inner join tab2 partition(p02) using (col1)
union all
select * from tab1 partition(p03) inner join tab2 partition(p03) using (col1)
union all
select * from tab1 partition(p04) inner join tab2 partition(p04) using (col1)
union all
select * from tab1 partition(p05) inner join tab2 partition(p05) using (col1)
union all
select * from tab1 partition(p06) inner join tab2 partition(p06) using (col1)
union all
select * from tab1 partition(p07) inner join tab2 partition(p07) using (col1)
union all
select * from tab1 partition(p08) inner join tab2 partition(p08) using (col1)
union all
select * from tab1 partition(p09) inner join tab2 partition(p09) using (col1)
;
-- 結果
パーティションテーブルの場合
7.28 sec
パーティションテーブルで、SQLレベルでパーティション毎に結合した場合
6.81 sec
非パーティションテーブルの場合
6.96 sec
SQL書き換えでパーティション毎に結合してもほとんど変化なし
(19c)
デフォルトでパーティションワイズ結合をサポートしている
(14)
create table tab1(
col1 int
)
partition by hash(col1);
create table tab1p00 partition of tab1 for values with (modulus 10,remainder 0);
create table tab1p01 partition of tab1 for values with (modulus 10,remainder 1);
create table tab1p02 partition of tab1 for values with (modulus 10,remainder 2);
create table tab1p03 partition of tab1 for values with (modulus 10,remainder 3);
create table tab1p04 partition of tab1 for values with (modulus 10,remainder 4);
create table tab1p05 partition of tab1 for values with (modulus 10,remainder 5);
create table tab1p06 partition of tab1 for values with (modulus 10,remainder 6);
create table tab1p07 partition of tab1 for values with (modulus 10,remainder 7);
create table tab1p08 partition of tab1 for values with (modulus 10,remainder 8);
create table tab1p09 partition of tab1 for values with (modulus 10,remainder 9);
create table tab2(
col1 int
)
partition by hash(col1);
create table tab2p00 partition of tab2 for values with (modulus 10,remainder 0);
create table tab2p01 partition of tab2 for values with (modulus 10,remainder 1);
create table tab2p02 partition of tab2 for values with (modulus 10,remainder 2);
create table tab2p03 partition of tab2 for values with (modulus 10,remainder 3);
create table tab2p04 partition of tab2 for values with (modulus 10,remainder 4);
create table tab2p05 partition of tab2 for values with (modulus 10,remainder 5);
create table tab2p06 partition of tab2 for values with (modulus 10,remainder 6);
create table tab2p07 partition of tab2 for values with (modulus 10,remainder 7);
create table tab2p08 partition of tab2 for values with (modulus 10,remainder 8);
create table tab2p09 partition of tab2 for values with (modulus 10,remainder 9);
\pset pager 0
\d+ tab1
\d+ tab2
insert into tab1 select floor(random() * 1000000)+1 from generate_series(1,1000000);
insert into tab2 select floor(random() * 10000000)+1 from generate_series(1,10000000);
select count(*) from tab1;
select count(*) from tab1p00;
select count(*) from tab2;
select count(*) from tab2p00;
analyze tab1;
analyze tab2;
explain analyze
select * from tab1 inner join tab2
using (col1)
;
set enable_partitionwise_join to on;
set enable_partitionwise_join to off;
show enable_partitionwise_join;
explain analyze
select * from tab1p00 inner join tab2p00 using (col1)
union all
select * from tab1p01 inner join tab2p01 using (col1)
union all
select * from tab1p02 inner join tab2p02 using (col1)
union all
select * from tab1p03 inner join tab2p03 using (col1)
union all
select * from tab1p04 inner join tab2p04 using (col1)
union all
select * from tab1p05 inner join tab2p05 using (col1)
union all
select * from tab1p06 inner join tab2p06 using (col1)
union all
select * from tab1p07 inner join tab2p07 using (col1)
union all
select * from tab1p08 inner join tab2p08 using (col1)
union all
select * from tab1p09 inner join tab2p09 using (col1)
;
-- 結果
パーティションテーブルで、enable_partitionwise_join がoffの場合
Planning Time: 0.162 ms
Execution Time: 3585.929 ms
パーティションテーブルで、enable_partitionwise_join がonの場合
Planning Time: 0.323 ms
Execution Time: 2749.646 ms
パーティションテーブルで、SQLレベルでパーティション毎に結合した場合
Planning Time: 0.368 ms
Execution Time: 2664.152 ms
非パーティションテーブルの場合
Planning Time: 0.132 ms
Execution Time: 3700.041 ms
パーティションワイズ結合の効果はすこしある。
また、SQL書き換えでパーティション毎に結合した場合とenable_partitionwise_joinをonにした場合は同等
(2019)
特定パーティションのみのデータ取得方法がない模様のため、検証不可
