Cara meningkatkan performa kueri JOIN menggunakan kendala kunci primer dan kunci asing - AnalyticDB

Ketika tabel di-join tetapi kueri hanya membaca kolom dari salah satu tabel tersebut, AnalyticDB for MySQL dapat membuktikan bahwa join tersebut redundan dan menghapusnya dari rencana eksekusi — mengurangi latensi tanpa perubahan apa pun pada SQL Anda. Ini disebut join elimination, dan bergantung pada relasi kendala kunci primer (PK) dan kunci asing (FK) yang dideklarasikan.

Penting

Kendala FK dan PK di AnalyticDB for MySQL bersifat informasional saja — sistem tidak menegakkan integritas data. Anda bertanggung jawab untuk memastikan bahwa semua nilai FK mereferensikan nilai PK yang valid. Jika data Anda melanggar kendala yang dideklarasikan, kueri mungkin mengembalikan hasil yang salah setelah join elimination.

Prasyarat

Sebelum memulai, pastikan Anda telah memiliki:

Kluster AnalyticDB for MySQL yang berjalan versi V3.1.10 atau lebih baru

Untuk memeriksa versi minor kluster Edisi Data Lakehouse, jalankan SELECT adb_version();. Untuk melakukan upgrade versi minor, hubungi dukungan teknis.

Cara kerja join elimination

Pengoptimal menggunakan informasi kendala FK-PK untuk menentukan apakah suatu join mengubah set hasil. Jika kueri melakukan join antara tabel A (sisi FK) dan tabel B (sisi PK) pada pasangan kolom FK-PK yang dideklarasikan, dan kueri tidak memilih kolom apa pun dari tabel B, maka:

Setiap baris di A mencocokkan paling banyak satu baris di B (karena kolom di B merupakan PK).
Join tersebut tidak menyaring atau menggandakan baris dari A.
Oleh karena itu, join tersebut redundan — hasilnya identik dengan hanya memindai tabel A.

Ketika kondisi-kondisi ini terpenuhi, pengoptimal mengganti join tersebut dengan satu operasi TableScan pada tabel FK.

Aktifkan atau nonaktifkan perilaku ini per kueri menggunakan petunjuk /*+ PK_FK_DEPENDENCY_ENABLED*/:

Petunjuk	Efek
`/+ PK_FK_DEPENDENCY_ENABLED = true/`	Mengaktifkan join elimination berbasis FK-PK
`/+ PK_FK_DEPENDENCY_ENABLED = false/`	Menonaktifkan join elimination berbasis FK-PK

Deklarasikan kunci asing

Deklarasikan FK saat membuat tabel (CREATE TABLE) atau tambahkan dan hapus nanti (ALTER TABLE). Untuk detail sintaksis CREATE TABLE, lihat CREATE TABLE. Untuk detail sintaksis ALTER TABLE, lihat ALTER TABLE.

Batasan

Batasan	Detail
Hanya FK satu kolom	FK hanya dapat mereferensikan satu kolom. FK multi-kolom seperti `FOREIGN KEY (sr_item_sk, sr_ticket_number) REFERENCES store_sales(ss_item_sk, d_date_sk)` tidak didukung.
Tidak ada penegakan kendala data	AnalyticDB for MySQL tidak memverifikasi integritas data FK-PK. Pastikan data Anda memenuhi kendala yang dideklarasikan sebelum mengandalkan join elimination.
Tabel eksternal	Kendala FK tidak dapat ditambahkan ke tabel eksternal.
Perilaku DROP TABLE	Saat Anda menggunakan pernyataan `DROP TABLE` untuk menghapus kunci asing, AnalyticDB for MySQL tidak memeriksa operasi ini atau melaporkan error.
Satu FK per pernyataan ALTER TABLE	Untuk menambahkan beberapa FK dengan `ALTER TABLE`, jalankan pernyataan terpisah untuk setiap FK.

Deklarasikan FK dengan CREATE TABLE

FK dalam database yang sama

Buat database dan tabel utama:

CREATE DATABASE db;
USE db;
CREATE TABLE item
(
  i_item_sk bigint NOT NULL,
  i_current_price bigint,
  PRIMARY KEY(i_item_sk)
)
DISTRIBUTED BY HASH(i_item_sk);

Buat tabel dengan FK yang mereferensikan item:
Saat symbol tidak ditentukan, parser secara otomatis memberi nama kendala <column_name>_fk. Dalam contoh ini, kendala diberi nama sr_item_sk_fk.
```
CREATE TABLE store_returns
(
  sr_sale_id bigint,
  sr_store_sk bigint,
  sr_item_sk bigint NOT NULL,
  FOREIGN KEY (sr_item_sk) REFERENCES item (i_item_sk)
);
```

FK lintas database

Untuk mereferensikan tabel di database lain, tambahkan awalan nama database pada nama tabel:

CREATE DATABASE db2;
USE db2;
CREATE TABLE store_sales
(
  ss_sale_id bigint,
  ss_store_sk bigint,
  ss_item_sk bigint not null,
  CONSTRAINT fk_constraint FOREIGN KEY (ss_item_sk) REFERENCES db.item (i_item_sk)
);

Beberapa FK pada satu tabel

Sertakan beberapa klausa FOREIGN KEY dalam satu pernyataan CREATE TABLE:

Buat tabel-tabel yang direferensikan:

USE db;
CREATE TABLE customer
(
  i_customer_sk bigint NOT NULL,
  i_current_price bigint,
  PRIMARY KEY(i_customer_sk)
)
DISTRIBUTED BY HASH(i_customer_sk);

CREATE TABLE vendor
(
  id bigint primary key,
  name varchar(5) not null
);

Buat tabel dengan dua FK:

CREATE TABLE store_product
(
  sr_sale_id bigint,
  sr_store_sk bigint,
  sr_customer_sk bigint NOT NULL,
  FOREIGN KEY (sr_sale_id) REFERENCES vendor (id),
  FOREIGN KEY (sr_customer_sk) REFERENCES customer (i_customer_sk)
);

Deklarasikan FK dengan ALTER TABLE

Untuk menambahkan atau menghapus FK pada tabel yang sudah ada, gunakan ALTER TABLE. Setiap pernyataan hanya menangani satu FK; jalankan beberapa pernyataan untuk mengelola beberapa FK.

Tambahkan FK:

USE db;
ALTER TABLE store_returns ADD CONSTRAINT sr_item_fk FOREIGN KEY (sr_item_sk) REFERENCES item (i_item_sk);

Hapus FK:

USE db;
ALTER TABLE store_returns DROP FOREIGN KEY sr_item_sk_fk;

Tambahkan FK yang mereferensikan tabel di database lain:

-- Langkah 1: Buat tabel yang direferensikan di db2
USE db2;
CREATE TABLE store
(
  id bigint primary key,
  name varchar(5) not null
);

-- Langkah 2: Tambahkan FK dari db.store_returns ke db2.store
ALTER TABLE db.store_returns ADD FOREIGN KEY (sr_store_sk) REFERENCES store(id);

Periksa FK yang dideklarasikan

Gunakan SHOW CREATE TABLE untuk melihat definisi FK pada suatu tabel:

USE db;
SHOW CREATE TABLE store_returns;

Contoh output (pernyataan lain dihilangkan):

-- CONSTRAINT `sr_item_sk_fk` FOREIGN KEY (`sr_item_sk`) REFERENCES `db`.`item`(`i_item_sk`)

Terapkan join elimination

Setelah FK dideklarasikan, tambahkan petunjuk /*+ PK_FK_DEPENDENCY_ENABLED = true*/ ke kueri Anda. Pengoptimal akan menghapus join apa pun yang:

Kondisi join-nya sesuai dengan relasi FK-PK yang dideklarasikan, dan
Kueri tidak memilih kolom dari tabel PK yang di-join.

Rencana eksekusi yang dihasilkan hanya berisi TableScan pada tabel FK — tidak ada operator InnerJoin yang muncul.

Dua tabel dalam database yang sama

store_returns.sr_item_sk memiliki FK yang mereferensikan item.i_item_sk. Kueri hanya memilih kolom dari store_returns. Karena i_item_sk adalah PK, setiap nilai sr_item_sk mencocokkan paling banyak satu baris di item, sehingga join tersebut tidak mengubah set hasil. Pengoptimal mengeliminasinya.

/*+ PK_FK_DEPENDENCY_ENABLED = true*/
EXPLAIN
SELECT
  s.sr_sale_id,
  s.sr_store_sk,
  s.sr_item_sk
FROM
  store_returns s,
  item
WHERE
  sr_item_sk = i_item_sk;

Rencana eksekusi menunjukkan satu operasi TableScan — tidak ada operator InnerJoin:

+---------------+
| Plan Summary  |
+---------------+
 1- Output[ Query plan ] {Est rowCount: 1.0}
 2    -> Exchange[GATHER] {Est rowCount: 1.0}
 3        - TableScan {table: store_returns, Est rowCount: 1.0}

Dua tabel lintas database

Logika yang sama berlaku untuk relasi FK lintas database. db2.store_sales.ss_item_sk mereferensikan db.item.i_item_sk. Kueri hanya memilih kolom store_sales, sehingga join pada db.item redundan:

USE db2;
/*+ PK_FK_DEPENDENCY_ENABLED = true*/
EXPLAIN
SELECT
  s.ss_sale_id,
  s.ss_item_sk
FROM
  store_sales s,
  db.item
WHERE
  ss_item_sk = i_item_sk;

Contoh hasil:

+---------------+
| Plan Summary  |
+---------------+
 1- Output[ Query plan ] {Est rowCount: 1.0}
 2    -> Exchange[GATHER] {Est rowCount: 1.0}
 3        - TableScan {table: store_sales, Est rowCount: 1.0}

Beberapa tabel di-join

Ketika semua join dalam kueri redundan, pengoptimal mengeliminasi semuanya dalam satu langkah. Di sini, store_returns memiliki FK yang mereferensikan item dan db2.store, dan kueri hanya membaca kolom store_returns:

USE db;
/*+ PK_FK_DEPENDENCY_ENABLED = true*/
EXPLAIN
SELECT
  s.sr_sale_id,
  s.sr_store_sk,
  s.sr_item_sk
FROM
  store_returns s,
  item,
  db2.store
WHERE
  sr_item_sk = i_item_sk
  AND sr_store_sk = id;

Contoh hasil:

+---------------+
| Plan Summary  |
+---------------+
 1- Output[ Query plan ] {Est rowCount: 1.0}
 2    -> Exchange[GATHER] {Est rowCount: 1.0}
 3        - TableScan {table: store_returns, Est rowCount: 1.0}

Join dalam tampilan

Join elimination juga berfungsi ketika tampilan didefinisikan di atas beberapa tabel. Buat tampilan yang melakukan join antara store_returns dan item:

CREATE VIEW sr_item_v AS
SELECT
  s.sr_store_sk AS store_name,
  s.sr_sale_id AS sale_id,
  s.sr_item_sk AS sr_item_id,
  item.i_current_price AS item_price,
  item.i_item_sk as item_id
FROM
  store_returns s,
  item
WHERE
  sr_item_sk = i_item_sk;

Kueri terhadap tampilan yang tidak mereferensikan item_price (atau kolom lain yang hanya berasal dari item) memicu join elimination. Pengoptimal mengganti referensi ke item.i_item_sk dengan store_returns.sr_item_sk dan hanya memindai store_returns:

/*+ PK_FK_DEPENDENCY_ENABLED = true*/
EXPLAIN
SELECT store_name, sr_item_id, sale_id
FROM sr_item_v;

Contoh hasil:

+---------------+
| Plan Summary  |
+---------------+
 1- Output[ Query plan ] {Est rowCount: 1.0}
 2    -> Exchange[GATHER] {Est rowCount: 1.0}
 3        - TableScan {table: store_returns, Est rowCount: 1.0}

Ketika eliminasi tidak berlaku: jika kueri memilih kolom yang hanya dapat diselesaikan dari item (seperti item_price), join tersebut diperlukan dan pengoptimal mempertahankannya:

/*+ PK_FK_DEPENDENCY_ENABLED = true*/
EXPLAIN
SELECT store_name, sr_item_id, sale_id, item_price
FROM sr_item_v;

Contoh hasil — operator InnerJoin[Hash Join] muncul dalam rencana:

+---------------+
| Plan Summary  |
+---------------+
 1- Output[ Query plan ] {Est rowCount: 1.0}
 2    -> Exchange[GATHER] {Est rowCount: 1.0}
 3        -> InnerJoin[Hash Join] {Est rowCount: 1.0}
 4            -> Project {Est rowCount: 1.0}
 5                -> Exchange[REPARTITION] {Est rowCount: 1.0}
 6                    - TableScan {table: store_returns, Est rowCount: 1.0}
 7            -> LocalExchange[HASH] {Est rowCount: 1.0}
 8                -> ScanProject {table: item, Est rowCount: 1.0}
 9                    - TableScan {table: item, Est rowCount: 1.0}