Informasi latar belakang

SELECT
  key1,
  SUM(sales) as total_sales
FROM
  fact_table LEFT JOIN dimension_table ON fact_table.key1 = dimension_table.key1
GROUP BY
  fact_table.key1
ORDER BY
  total_sales
LIMIT 100;

Secara tradisional, saat menjalankan kueri IMCI-enabled di database PolarDB, Anda perlu menggunakan HASH JOIN diikuti oleh HASH GROUP BY. Dalam kedua operasi ini, key1 digunakan untuk membuat tabel hash. Paragraf berikut menjelaskan penggunaan key1, dengan catatan bahwa fact_table.key1 sama dengan dimension_table.key1.

1. HASH JOIN: menggunakan dimension_table.key1 untuk membuat tabel hash, dan menggunakan fact_table.key1 untuk menanyakan tabel hash dan mengeluarkan data.

2. HASH GROUP BY: menggunakan fact_table.key1 untuk membuat tabel hash lain, dan menggabungkan data dalam tabel baru ini.

Untuk meningkatkan efisiensi, diperlukan metode yang mengintegrasikan eksekusi HASH JOIN dan HASH GROUP BY ke dalam satu operator. Operator ini dapat menggunakan dimension_table.key1 untuk membuat tabel hash dan menggabungkan data, serta fact_table.key1 untuk menanyakan tabel dan menggabungkan data. Dengan menggunakan operator ini, tidak perlu membuat tabel hash tambahan. Berdasarkan konsep ini, operator GroupJoin diperkenalkan.

GroupJoin membantu menghilangkan kebutuhan untuk membuat tabel hash tambahan dan mengurangi ukuran data hasil antara. Operasi JOIN dapat menghasilkan set hasil dengan jumlah baris yang sangat besar. Hal ini terjadi karena satu baris dalam tabel dapat sesuai dengan beberapa baris di tabel lain. Dalam skenario terburuk, hasil penggabungan antara tabel N-baris dan tabel M-baris adalah set hasil yang berisi N × M baris. Ketika HASH JOIN dan HASH GROUP BY dilakukan secara berurutan, tabel hash N-baris dibuat, N × M × S baris data dikeluarkan, lalu tabel hash baru dibuat untuk agregasi data. Proses ini dapat menyebabkan pemborosan sumber daya. Dalam rumus untuk menghitung jumlah baris data, S menunjukkan selektivitas, dengan nilai berkisar antara 0 hingga 1. Pada contoh kode sebelumnya, LEFT OUTER JOIN dilakukan pada tabel fakta M-baris (tabel besar) dan tabel dimensi N-baris (tabel kecil), dengan key1 sebagai kunci unik. Dalam kasus ini, tabel hash N-baris dihasilkan, M baris data dikeluarkan, dan kemudian tabel hash lain dibuat untuk menyimpan M baris data agregat. Namun, jika GroupJoin diterapkan pada dua tabel yang sama, data digabungkan dan diagregasikan langsung di tabel hash N-baris. Pendekatan ini membutuhkan waktu lebih singkat serta mengonsumsi lebih sedikit sumber daya memori.

Untuk memanfaatkan keuntungan GroupJoin, PolarDB for MySQL menyediakan GroupJoin untuk operasi yang didukung IMCI.

Cara kerjanya

Ikhtisar

Dalam operasi yang didukung IMCI, GroupJoin dijalankan sebagai eksekusi gabungan dari HASH JOIN dan HASH GROUP BY.

1. Ketika GroupJoin dieksekusi, tabel kecil di sisi kiri pertama kali digunakan untuk membuat tabel hash. Data dari tabel sisi kiri diagregasikan saat tabel hash dibuat, yang menghasilkan efek yang sama seperti HashGroupby left_table.

2. Kemudian, tabel besar di sisi kanan digunakan untuk mencocokkan data dari tabel hash yang telah dibuat. Data yang memenuhi kondisi pencocokan diagregasikan dalam tabel hash, sedangkan data yang tidak memenuhi kondisi pencocokan diabaikan. Agregasi ini dianggap sebagai agregasi data terkait tabel sisi kanan.

Bagian ini memberikan gambaran umum tentang GroupJoin, sedangkan rinciannya dijelaskan pada bagian berikutnya.

Batasan

Untuk mengurangi kompleksitas pelaksanaan GroupJoin, PolarDB for MySQL menetapkan batasan berikut pada implementasinya:

1. Kunci GROUP BY harus sama dengan kunci join yang digunakan di satu sisi. Dalam beberapa kasus, kunci GROUP BY secara fungsional bergantung hanya pada sebagian dari kunci join.

2. Dalam skenario RIGHT JOIN + GROUP BY RIGHT, kunci kanan harus unik. Jika tidak, operasi mungkin berubah menjadi LEFT JOIN atau GROUP BY LEFT, atau GroupJoin tidak dapat digunakan.

3. Fungsi aggr hanya dapat merujuk pada tabel sisi kiri atau tabel sisi kanan pada satu waktu. GroupJoin tidak berlaku jika fungsi aggr dari operator GROUP BY merujuk pada kedua tabel sekaligus, seperti dalam kasus SUN(t1.a+t2.a).

Kode contoh

INNER JOIN/GROUP BY LEFT

Kode contoh:

l_table INNER JOIN r_table
ON l_table.key1 = r_table.key1
GROUP BY l_table.key1

1. Gunakan tabel sisi kiri untuk membuat tabel hash. Saat tabel hash dibuat, fungsi aggr dijalankan untuk menggabungkan data tabel sisi kiri. Gunakan hitungan ulang saat data tabel sisi kanan diagregasikan.

2. Saat tabel-tabel digabungkan, tabel sisi kanan digunakan untuk mencocokkan entri tabel hash. Jika tidak ada entri yang cocok dengan baris di tabel sisi kanan, baris tersebut diabaikan. Jika satu entri cocok, skor 1 ditambahkan ke hitungan ulang konteks aggr untuk tabel sisi kiri, dan fungsi aggr dijalankan untuk tabel sisi kanan.

3. Setelah JOIN dijalankan, hasil agregasi hanya dari entri tabel hash yang cocok dikeluarkan, dan yang tidak cocok diabaikan.

4. Hasil agregasi sama dengan hasil SUM(expr) dikalikan dengan hitungan ulang. Misalnya, jika hasil SUM(expr) adalah 200 dan hitungan ulang adalah 5, hasil agregasi adalah 1000.

INNER JOIN/GROUP BY RIGHT

Kode contoh:

l_table INNER JOIN r_table
ON l_table.key1 = r_table.key1
GROUP BY r_table.key1

Karena l_table.key1 sama dengan r_table.key1, kode contoh ini menggambarkan skenario INNER JOIN dan GROUP BY LEFT.

LEFT OUTER JOIN/GROUP BY LEFT

Kode contoh:

l_table LEFT OUTER JOIN r_table
ON l_table.key1 = r_table.key1
GROUP BY l_table.key1

1. Gunakan tabel kiri untuk membuat tabel hash. Saat tabel hash dibuat, fungsi aggr dijalankan untuk menggabungkan data tabel sisi kiri. Gunakan hitungan ulang saat data tabel sisi kanan diagregasikan.

2. Saat tabel-tabel digabungkan, tabel sisi kanan digunakan untuk mencocokkan entri tabel hash. Jika tidak ada entri yang cocok dengan baris di tabel sisi kanan, baris tersebut diabaikan. Jika satu entri cocok, skor 1 ditambahkan ke hitungan ulang konteks aggr untuk tabel sisi kiri, dan fungsi aggr dijalankan untuk tabel sisi kanan.

3. Skenario ini berbeda dari INNER JOIN dalam aspek berikut: Setelah JOIN dijalankan, entri yang tidak cocok dalam tabel hash terdaftar dalam kelompok terpisah, dan input fungsi aggr yang sesuai untuk tabel sisi kanan semuanya NULL.

LEFT OUTER JOIN/GROUP BY RIGHT

Kode contoh:

l_table LEFT OUTER JOIN r_table
ON l_table.key1 = r_table.key1
GROUP BY r_table.key1

1. Gunakan tabel sisi kiri untuk membuat tabel hash. Saat tabel hash dibuat, fungsi aggr dijalankan untuk menggabungkan data tabel sisi kiri. Saat fungsi aggr dijalankan untuk menggabungkan data tabel sisi kanan, hitungan ulang digunakan.

2. Saat tabel-tabel digabungkan, tabel sisi kanan digunakan untuk mencocokkan entri tabel hash. Jika tidak ada entri yang cocok dengan baris di tabel sisi kanan, baris tersebut diabaikan. Jika satu entri cocok, skor 1 ditambahkan ke hitungan ulang konteks aggr untuk tabel sisi kiri, dan fungsi aggr dijalankan untuk tabel sisi kanan.

3. Setelah JOIN dijalankan, entri yang cocok dalam tabel hash dikeluarkan, entri yang tidak cocok terdaftar dalam kelompok terpisah, dan input fungsi aggr yang sesuai untuk tabel sisi kanan semuanya NULL.

RIGHT OUTER JOIN/GROUP BY LEFT

Kode contoh:

l_table RIGHT OUTER JOIN r_table
ON l_table.key1 = r_table.key1
GROUP BY l_table.key1

1. Gunakan tabel sisi kiri untuk membuat tabel hash. Saat tabel hash dibuat, fungsi aggr dijalankan untuk menggabungkan data tabel sisi kiri. Saat fungsi aggr dijalankan untuk menggabungkan data tabel sisi kanan, hitungan ulang digunakan.

2. Saat tabel-tabel digabungkan, tabel sisi kanan digunakan untuk mencocokkan entri tabel hash. Jika entri cocok dengan baris di tabel sisi kanan, skor 1 ditambahkan ke hitungan ulang konteks aggr untuk tabel sisi kiri, dan hasil agregasi untuk tabel sisi kanan dihitung. Jika tidak ada entri yang cocok, semua baris yang tidak cocok di tabel sisi kanan terdaftar dalam kelompok terpisah, dan hasil agregasi yang sesuai untuk tabel sisi kiri semuanya NULL.

3. Setelah JOIN dijalankan, entri yang cocok dalam tabel hash dikeluarkan, dan entri yang tidak cocok diabaikan.

RIGHT OUTER JOIN/GROUP BY RIGHT

Kode contoh:

l_table RIGHT OUTER JOIN r_table
ON l_table.key1 = r_table.key1
GROUP BY r_table.key1

Batasan

r_table.key1 harus unik. Jika tidak, operasi join tidak valid. Jika Anda tidak yakin apakah r_table.key1 unik, ubah JOIN menjadi LEFT OUTER JOIN dan GROUP BY menjadi GROUP BY LEFT menggunakan optimizer.

Prosedur

1. Gunakan tabel kiri untuk membuat tabel hash. Saat tabel hash dibuat, fungsi aggr dijalankan untuk menggabungkan data tabel sisi kiri. Gunakan hitungan ulang saat data tabel sisi kanan diagregasikan.

2. Saat tabel-tabel digabungkan, tabel sisi kanan digunakan untuk mencocokkan data tabel hash. Jika entri cocok dengan baris di tabel sisi kanan, hasil agregasi tabel sisi kiri dan hasil agregasi tabel sisi kanan dikeluarkan. Jika tidak ada entri yang cocok, hasil agregasi juga dikeluarkan, tetapi hasil agregasi tabel sisi kiri semuanya NULL.

3. Setelah JOIN dijalankan, GroupJoin selesai dan tidak ada entri tabel hash yang perlu dikelola.

Pencurahan data saat GroupJoin digunakan

Saat GroupJoin dijalankan, data dituangkan dengan cara yang serupa seperti pada eksekusi gaya partisi HASH JOIN dan HASH GROUP BY. Berikut ini adalah detailnya:

1. Algoritma GroupJoin menggunakan gaya partisi.

2. Saat tabel sisi kiri digunakan untuk membuat tabel hash, beberapa partisi data disimpan di memori. Pernyataan untuk membuat tabel hash disediakan di bagian Sample code topik ini.

3. Beberapa partisi data lainnya dicurahkan ke file sementara di disk. Data tambahan yang ditulis ke partisi ini juga dicurahkan ke file sementara yang sesuai. Filter Bloom dibuat untuk partisi-partisi ini untuk membantu menyaring data yang tidak cocok di tabel sisi kanan secara efisien.

4. Setelah tabel hash dibuat, gunakan data tabel sisi kanan untuk mencocokkan data tabel hash.

   1. Jika partisi data yang akan dicari ada di memori, pernyataan di bagian Sample code dijalankan. Jika partisi data tidak ada di memori, filter Bloom digunakan untuk memeriksa apakah data yang dicari ada di partisi tertentu. Jika ya, data dicurahkan ke file sementara yang sesuai dengan partisi. Jika tidak, data diabaikan atau dikeluarkan.

   2. Setelah partisi data di memori dicari, sistem mencari partisi data di disk secara berurutan. Dalam contoh ini, setidaknya satu partisi data dapat dimasukkan ke disk dan tidak perlu dibagi menjadi partisi minor. Kemudian, pernyataan yang disediakan di bagian Sample code topik ini dijalankan.

Makalah terkait

Pada tahun 2011, makalah berjudul "Accelerating Queries with Group-By and Join by Groupjoin" (selanjutnya disebut paper_1) diterbitkan. Makalah ini menjelaskan kelayakan penggunaan GroupJoin dalam berbagai kueri dari sudut pandang teoretis, tetapi kurang memberikan detail implementasi. Paper_1 juga menguraikan batasan dan skenario penerapan GroupJoin, seperti saat berbagai fungsi agregasi perlu dijalankan. Penjelasan dalam makalah ini bersifat abstrak dan relatif sulit dipahami.

Pada tahun 2021, makalah berjudul "A Practical Approach to Groupjoin and Nested Aggregates" (selanjutnya disebut paper_2) diterbitkan. Makalah ini menjelaskan implementasi efisien GroupJoin dalam database in-memory dan mencakup sorotan berikut:

1. Cara menggunakan GroupJoin dalam algoritma decoupling subquery

Jika terdapat subquery seperti GROUP BY, Anda dapat menggunakan MagicSet untuk menghapus duplikat tabel dan menambahkan kombinasi JOIN serta GROUP BY guna mendekopling subquery yang berkorelasi. Dalam skenario ini, GroupJoin dapat digunakan. Algoritma decoupling serupa diterapkan dalam operasi IMCI-enabled PolarDB. Namun, rencana eksekusi di mana objek anak dibagi tidak dapat dihasilkan.

2. Cara Melakukan Penggabungan Cepat

Saat tabel hash dibuat, data dari tabel sisi kiri diagregasikan tanpa mempertahankan payload untuk setiap entri tabel hash, sehingga penggabungan data dapat dilakukan lebih cepat. Penggabungan cepat ini juga dapat dilakukan dalam operasi IMCI-enabled PolarDB.

3. Cara menggunakan memoisasi untuk menangani kueri bersamaan dan agregasi pada entri tabel hash

Dalam kasus ekstrem, selama hash probe, semua baris dari tabel sisi kanan cocok dengan entri yang sama dalam tabel hash. Dalam situasi ini, agregasi seperti SUM(2 x col) harus dilakukan untuk entri tersebut, dan konteks aggr digunakan berulang kali dalam proses agregasi. Sebagai contoh, jika SUM() dijalankan, nilai sum_value yang sama digunakan untuk menjumlahkan nilai secara berulang. Kinerja operasi dibatasi oleh konteks, bahkan dalam operasi penambahan untuk variabel atomik, apalagi penggunaan fungsi aggr umum. Untuk mengatasi batasan ini, makalah ini memperkenalkan solusi. Perintah CAS dijalankan untuk setiap entri guna menetapkan ID thread pemilik. Jika thread gagal memiliki entri, tabel hash lokal dibuat untuk melakukan perhitungan. Setelah itu, hasil perhitungan dari semua tabel lokal diagregasikan ke tabel hash global.

4. Alasan GroupJoin Tidak Berlaku untuk Semua Skenario

Dalam beberapa skenario, kombinasi JOIN dan GROUP BY lebih sesuai dibandingkan dengan GroupJoin. Contoh berikut menggambarkan situasi ini: Selektivitas di sisi kiri ditetapkan rendah, dan setelah hash probe selesai, sebagian besar baris di sisi kiri tidak terpilih. Akibatnya, dilema pun muncul.

• Jika penggabungan cepat dilakukan saat tabel hash dibangun, payload tidak perlu dipertahankan. Hal ini membantu menghemat sumber daya memori. Namun, karena selektivitas rendah, sebagian besar baris tidak akan terpilih, sehingga menyebabkan pemborosan dalam agregasi yang telah dilakukan sebelumnya.

• Jika tidak ada agregasi yang dilakukan sebelumnya, penggunaan memori akan memerlukan lebih banyak waktu, yang dapat dihindari dengan menggunakan penggabungan cepat.

Oleh karena itu, jika selektivitas rendah, metode berikut direkomendasikan: Setelah JOIN selesai, beberapa grup dihasilkan dan HASH BY GROUP digunakan untuk melakukan agregasi lokal. Makalah ini menjelaskan implementasi metode ini dalam berbagai skenario. Untuk menentukan jenis kueri yang sesuai untuk skenario tertentu, optimizer perlu menghitung selektivitas dan kardinalitas. Makalah ini juga merekomendasikan beberapa metode perhitungan yang digunakan oleh optimizer.

Untuk operasi IMCI-enabled di PolarDB, dilema sebelumnya tidak lagi menjadi masalah karena:

• Untuk operasi IMCI-enabled di PolarDB, tabel kecil umumnya digunakan untuk membuat tabel hash.

• Jika selektivitas rendah dan penggabungan cepat dilakukan, penghematan sumber daya memori hanya terjadi dengan mengorbankan waktu pada tabel kecil.

Oleh karena itu, selain skenario penggunaan RIGHT JOIN dan GROUP BY RIGHT yang telah disebutkan sebelumnya, GroupJoin selalu lebih efisien dibandingkan dengan kombinasi HASH JOIN dan HASH GROUP BY untuk operasi IMCI-enabled di PolarDB.

Berdasarkan penulis dan uji coba dalam makalah-makalah tersebut, kedua makalah yang disebutkan dalam topik ini berasal dari tim database hyper Universitas Ludwig Maximilian Munich. Selain database hyper, tidak ada jenis database lain yang diperkenalkan untuk mengimplementasikan GroupJoin. Namun, operasi "shared hash table" dapat dilakukan dengan pendekatan alternatif, yang akan dibahas di masa mendatang.

Gunakan GroupJoin dalam kueri TPC-H

TPC-H merupakan benchmark yang umum digunakan untuk menguji kemampuan pemrosesan analitik (AP) pada sistem AP. GroupJoin dapat diterapkan pada banyak dari 22 kueri dalam TPC-H. Namun, kecuali untuk TPC-H Q13, pernyataan kueri lainnya perlu dimodifikasi agar sesuai dengan GroupJoin.

Q13

GroupJoin dapat digunakan langsung dalam TPC-H Q13. Berikut adalah contoh kode untuk Q13:

select
    c_count,
    count(*) as custdist
from
    (
        select
            c_custkey,
            count(o_orderkey) as c_count
        from
            customer
            left outer join orders on c_custkey = o_custkey
            and o_comment not like '%pending%deposits%'
        group by
            c_custkey
    ) c_orders
group by
    c_count
order by
    custdist desc,
    c_count desc;

• Rencana eksekusi berikut digunakan jika GroupJoin tidak digunakan dalam operasi IMCI-enabled:

  

• Rencana eksekusi berikut digunakan ketika GroupJoin diterapkan dalam operasi yang mendukung IMCI:

  

Q3

Sebelum GroupJoin dapat digunakan dalam TPC-H Q3, perubahan berikut harus dilakukan. Kode contoh Q3:

select
    l_orderkey,
    sum(l_extendedprice * (1 - l_discount)) as revenue,
    o_orderdate,
    o_shippriority
from
    customer,
    orders,
    lineitem
where
    c_mktsegment = 'BUILDING'
    and c_custkey = o_custkey
    and l_orderkey = o_orderkey
    and o_orderdate < date '1995-03-15'
    and l_shipdate > date '1995-03-15'
group by
    l_orderkey,
    o_orderdate,
    o_shippriority
order by
    revenue desc,
    o_orderdate
limit
    10;

Rencana eksekusi berikut untuk Q3 dapat digunakan dalam operasi IMCI-enabled:

Dalam pernyataan ini, kunci pengelompokan adalah l_orderkey, o_orderdate, dan o_shippriority. Kunci pengelompokan berbeda dari semua kunci join, sehingga GroupJoin tidak dapat diterapkan. Setelah serangkaian deduksi, kesimpulan berikut dapat diambil:

1. INNER JOIN dilakukan pada tabel lineitem dan tabel orders. Predikat JOIN adalah l_orderkey=o_orderkey. Oleh karena itu, dalam set hasil operasi JOIN, l_orderkey sama dengan o_orderkey.

2. Karena l_orderkey sama dengan o_orderkey, GROUP BY l_orderkey, o_orderdate, o_shippriority setara dengan GROUP BY o_orderkey, o_orderdate, o_shippriority.

3. Karena o_orderkey adalah kunci utama tabel orders, o_orderdate dan o_shippriority bergantung secara fungsional pada setiap o_orderkey.

4. Karena kesimpulan sebelumnya, GROUP BY o_orderkey, o_orderdate, o_shippriority setara dengan GROUP BY o_orderkey.

Oleh karena itu, setelah Anda mengganti klausa GROUP BY di Q3 dengan GROUP BY o_orderkey, GroupJoin dapat digunakan. Contoh kode:KEY,TEMPTABLE3.SUM(LINETTEM.EXTENDEDPRTCE*1.00-LUNETEM._DLSCOL

Deduksi dependensi fungsional memberikan persyaratan bagi optimizer. Optimizer MySQL dapat membantu mendeduksi sebagian logika dependensi fungsional, tetapi tidak dapat menarik kesimpulan menggunakan GROUP BY o_orderkey sebagai ekuivalen. SQL SERVER dapat membantu mendeduksi penggunaan GROUP BY o_orderkey, yang secara teori cukup terbukti, namun belum sepenuhnya terbukti dalam praktik IMCI-enabled. Hal ini juga berlaku untuk Q3, Q4, Q10, Q13, Q18, Q20, dan Q21 dari TPC-H. Jika deduksi dapat dilakukan sepenuhnya, kunci pengelompokan GROUP BY akan dipersingkat, sehingga meningkatkan kecepatan agregasi data.

Q10

GroupJoin tidak dapat digunakan secara langsung dalam TPC-H Q10. Berikut adalah contoh kode untuk Q10:

select
    c_custkey,
    c_name,
    sum(l_extendedprice * (1 - l_discount)) as revenue,
    c_acctbal,
    n_name,
    c_address,
    c_phone,
    c_comment
from
    customer,
    orders,
    lineitem,
    nation
where
    c_custkey = o_custkey
    and l_orderkey = o_orderkey
    and o_orderdate >= date '1993-10-01'
    and o_orderdate < date '1993-10-01' + interval '3' month
    and l_returnflag = 'R'
    and c_nationkey = n_nationkey
group by
    c_custkey,
    c_name,
    c_acctbal,
    c_phone,
    n_name,
    c_address,
    c_comment
order by
    revenue desc
limit
    20;

Sebelum menggunakan GroupJoin di Q10, Anda harus melakukan modifikasi berikut.

1. Ganti kunci pengelompokan dengan c_custkey, yang merupakan kunci utama tabel customer. Prosedurnya mirip dengan yang ada di bagian Q3.

2. Urutan join harus disesuaikan agar operasi JOIN tabel customer berada di lapisan paling luar.

Modifikasi pertama selalu memberikan efek positif, sedangkan modifikasi kedua terkadang menimbulkan efek samping.

Q17

TPC-H Q17 mencakup subquery bersarang. Berikut adalah contoh kode untuk Q17:

select
    sum(l_extendedprice) / 7.0 as avg_yearly
from
    lineitem,
    part
where
    p_partkey = l_partkey
    and p_brand = 'Brand#44'
    and p_container = 'WRAP PKG'
    and l_quantity < (
        select
            0.2 * avg(l_quantity)
        from
            lineitem
        where
            l_partkey = p_partkey
    );

Beberapa metode dapat digunakan untuk mendekopling subquery. Dua gambar berikut menunjukkan dua rencana eksekusi berdasarkan dua algoritma decoupling untuk aggr skalar dalam skenario IMCI-enabled.

Rencana eksekusi ini tidak berlaku untuk operator GroupJoin. Jika Anda menggunakan MagicSet untuk mendekopling subquery, keadaan antara dapat dimasuki sebelum MagicSet dihapus. Keadaan tersebut sesuai untuk GroupJoin.

Proses ini juga diilustrasikan pada Gambar 3 paper_2.

Dalam hal ini, GroupJoin dapat digunakan. PolarDB sebagian mendukung MagicSet sebagai metode decoupling dalam skenario IMCI-enabled. Namun, dukungan ini belum sepenuhnya tersedia karena rencana eksekusi yang melibatkan objek anak dibagikan tidak dapat dihasilkan. Akibatnya, GroupJoin tidak dapat diterapkan untuk TPC-H Q17 dalam skenario IMCI-enabled.

Q18

GroupJoin dapat digunakan dalam TPC-H Q18 hanya jika rencana eksekusi dimodifikasi. Pada contoh ini, subquery IN dan klausa ORDER BY dihilangkan untuk menyederhanakan serta menggeneralisasi ilustrasi. Berikut adalah kode contoh Q18:

select
    c_name,
    c_custkey,
    o_orderkey,
    o_orderdate,
    o_totalprice,
    sum(l_quantity)
from
    customer,
    orders,
    lineitem
where
    c_custkey = o_custkey
    and o_orderkey = l_orderkey
group by
    c_name,
    c_custkey,
    o_orderkey,
    o_orderdate,
    o_totalprice

Untuk TPC-H Q17, modifikasi berikut dapat dilakukan:

1. Karena c_custkey adalah kunci utama tabel customer, c_name dapat bergantung secara fungsional pada c_custkey. Demikian pula, o_orderkey adalah kunci utama tabel orders, jadi o_orderdate dan o_totalprice dapat bergantung secara fungsional pada o_orderkey. Oleh karena itu, klausa GROUP BY di Q17 dapat dikonversi secara ekuivalen menjadi GROUP BY c_custkey, o_orderkey.

2. Predikat JOIN untuk tabel customer dan tabel orders adalah c_custkey=o_custkey, sehingga hasil join adalah baris di mana c_custkey sama dengan o_custkey.

3. Karena c_custkey sama dengan o_custkey, klausa GROUP BY dapat dikonversi secara ekuivalen menjadi GROUP BY o_custkey, o_orderkey.

4. Karena o_orderkey secara unik sesuai dengan o_custkey, GROUP BY o_custkey, o_orderkey dapat dikonversi menjadi GROUP BY o_orderkey.

Kode contoh berikut menunjukkan hasil kueri setelah dimodifikasi:

select
    ANY_VALUE(c_name),
    ANY_VALUE(c_custkey),
    o_orderkey,
    ANY_VALUE(o_orderdate),
    ANY_VALUE(o_totalprice),
    sum(l_quantity)
from
    customer,
    orders,
    lineitem
where
    c_custkey = o_custkey
    and o_orderkey = l_orderkey
group by
    o_orderkey

• Rencana eksekusi berikut digunakan apabila GroupJoin tidak digunakan:

  

• Rencana eksekusi berikut digunakan saat GroupJoin diterapkan:

  

Kueri yang dimodifikasi dapat digunakan untuk menyusun rencana eksekusi reguler karena kunci pengelompokan GROUP BY telah dipersingkat.

Q20

Subquery bersarang dalam TPC-H Q20 mirip dengan yang ada di Q17. MagicSet digunakan untuk mendekopling subquery. Sebelum MagicSet dihapus, keadaan antara yang sesuai untuk GroupJoin dimasuki.

select
...
and ps_availqty > (
    select
        0.5 * sum(l_quantity) < ! --- scalar aggr --->
    from
        lineitem
    where
        l_partkey = ps_partkey         < ! --- Correlated item 1 --->
        and l_suppkey = ps_suppkey     < ! --- Correlated item 2 --->
        and l_shipdate >= '1993-01-01'
        and l_shipdate < date_add('1993-01-01', interval '1' year)
)

Kueri lainnya

Berdasarkan paper_1 dan paper_2, GroupJoin dapat diterapkan pada Q5, Q9, Q16, dan Q21 setelah kueri dimodifikasi. Namun, metode modifikasi untuk keempat kueri tersebut tidak dijelaskan dalam makalah. Selain itu, rencana eksekusi untuk GroupJoin juga tidak tersedia di halaman web HyPer WebInterface tim database hyper (https://hyper-db.de/interface.html#).

Kinerja kueri

Banyak kueri TPC-H mencakup JOIN dan GROUP BY, yang dapat dioptimalkan menggunakan GroupJoin. Dalam paper_1, penulis membandingkan kinerja kueri sebelum dan sesudah penerapan GroupJoin pada kueri berikut: Q3, Q5, Q9, Q10, Q13, Q16, Q17, Q20, dan Q21.

Dalam perbandingan menggunakan 1GB data, latensi kueri TPC-H berkurang dari 1.932 ms menjadi 1.295 ms setelah penerapan GroupJoin.

Dalam paper_2, penulis menggunakan data sebesar 1 GB untuk membandingkan kinerja kueri sebelum dan sesudah penerapan GroupJoin pada Q3, Q13, Q17, dan Q18.

Informasi berikut menjelaskan arti garis dalam grafik garis sebelumnya:

1. Garis terpisah mewakili skenario di mana JOIN dan GROUP BY digunakan.

2. Garis eager mewakili skenario di mana penggabungan cepat dilakukan.

3. Garis memoizing mewakili skenario di mana memoizing digunakan untuk optimasi, yang dijelaskan di bagian sebelumnya. Grafik garis menunjukkan fenomena berikut dalam Q3, Q13, Q17, dan Q18:

   1. Kinerja kueri setelah menggunakan memoizing sebanding dengan penggunaan HASH JOIN dan HASH GROUP BY.

   2. Penggabungan cepat memberikan kinerja kueri terbaik hanya di Q13.

Grafik garis menunjukkan bahwa kinerja metode kueri bervariasi tergantung pada skenario kueri yang berbeda. Hal ini selaras dengan gagasan dalam makalah bahwa statistik harus disediakan untuk optimizer guna memilih rencana eksekusi optimal untuk GroupJoin. Menurut makalah tersebut, optimizer membantu memastikan penggunaan yang tepat dari GroupJoin dan algoritma terkait.

Namun, para ahli dari PolarDB tidak sependapat dengan gagasan dalam makalah tersebut terkait aspek-aspek berikut:

1. Makalah menggunakan throughput (tuples/s) untuk mengukur kualitas algoritma. Indikator ini juga digunakan dalam eksperimen PolarDB untuk skenario IMCI-enabled, tetapi menghasilkan hasil yang berbeda. Tabel berikut menjelaskan throughput GroupJoin dalam Q3, Q13, dan Q18.

   Kueri	HASH JOIN + HASH GROUP BY	GroupJoin
   Q3	130 MB	152 MB
   Q13	11 MB	33 MB
   Q18	315 MB	1 GB

   Catatan

   GroupJoin tidak berlaku untuk Q17 dalam skenario IMCI-enabled.

   Volume data yang digunakan dalam eksperimen makalah dan PolarDB sama, tetapi throughput untuk setiap kueri berbeda dalam dua eksperimen. Ini mungkin disebabkan oleh perbedaan dalam metode implementasi. Eksperimen PolarDB menunjukkan bahwa GroupJoin meningkatkan kinerja kueri kecuali ketika kombinasi RIGHT JOIN dan GROUP BY RIGHT digunakan.

2. Dalam hal kesimpulan yang dijelaskan di 3.a, eksperimen PolarDB menunjukkan bahwa hanya sedikit konten yang ada dalam kueri TPC-H. Oleh karena itu, tabel hash lokal jarang digunakan dalam skenario nyata, yang membuat metode yang didukung memoizing memberikan kinerja kueri serupa dengan metode HASH JOIN + HASH GROUP BY. Perbandingan di paper_2 tidak dapat menunjukkan efektivitas metode yang didukung memoizing. Dalam eksperimen PolarDB, konten diukur saat kunci langsung dilakukan.

Kesimpulan

GroupJoin membantu menghilangkan pekerjaan berulang selama runtime, dan dengan demikian meningkatkan kinerja kueri dalam beberapa skenario. Efek ini diverifikasi dalam penggunaan aktual. Dari sudut pandang ini, GroupJoin bisa berguna.

Namun, GroupJoin tidak cocok untuk semua skenario kueri. GroupJoin hanya berlaku jika kondisi berikut terpenuhi: Baik EQUAL JOIN maupun GROUP BY dilakukan, kunci pengelompokan sama dengan kunci join di satu sisi, dan batasan tertentu ditetapkan pada fungsi aggr dan metode implementasi. Hal ini membuat rencana eksekusi GroupJoin sulit dilaksanakan dan dipertahankan. Untuk pengembang, mereka mungkin lebih memilih metode yang dapat digunakan secara luas untuk meningkatkan efisiensi kueri SQL di semua skenario. Dari sudut pandang ini, GroupJoin tidak seefektif yang diharapkan.

Saat menggunakan GroupJoin, disarankan untuk menggeneralisasi rencana eksekusi GroupJoin agar dapat diterapkan pada berbagai skenario umum, sehingga penggunaannya dapat dimaksimalkan.