Cara menggunakan batasan kunci utama dan kunci asing untuk meningkatkan kinerja kueri join - AnalyticDB

Topik ini menjelaskan cara memanfaatkan hubungan batasan antara kunci utama dan kunci asing untuk menghilangkan join yang tidak diperlukan serta mengoptimalkan rencana eksekusi.

Prasyarat

Sebuah kluster AnalyticDB for MySQL versi V3.1.10 atau lebih baru telah dibuat.

Catatan

Ikhtisar

Dalam banyak kasus, volume data besar disimpan dalam tabel database berskala besar untuk melakukan operasi kueri dan analisis kompleks. Anda dapat menggunakan pernyataan JOIN untuk menggabungkan baris dari dua tabel atau lebih. Namun, operasi join dapat menurunkan kinerja kueri. Untuk mengoptimalkan kinerja, Anda dapat menggunakan eliminasi join. Salah satu metode optimasi adalah dengan menghilangkan join yang tidak perlu menggunakan batasan kunci utama dan kunci asing. Batasan tersebut memberikan informasi tentang hubungan antar-tabel untuk menghilangkan join yang tidak diperlukan, mengurangi waktu kueri, dan meningkatkan kinerja database.

AnalyticDB for MySQL memungkinkan Anda menggunakan petunjuk /*+ PK_FK_DEPENDENCY_ENABLED*/ untuk mengaktifkan atau menonaktifkan fitur penggunaan informasi batasan kunci utama dan kunci asing guna menghilangkan join yang tidak perlu.

/*+ PK_FK_DEPENDENCY_ENABLED = true*/: mengaktifkan fitur mendapatkan informasi batasan kunci utama dan kunci asing. Setelah diaktifkan, sistem menerapkan aturan eliminasi join berdasarkan informasi batasan tersebut.
/*+ PK_FK_DEPENDENCY_ENABLED = false*/: menonaktifkan fitur mendapatkan informasi batasan kunci utama dan kunci asing. Setelah dinonaktifkan, sistem tidak dapat menerapkan aturan eliminasi join berdasarkan informasi batasan tersebut.

Catatan

PK: kunci utama.
FK: kunci asing.

Deklarasikan kunci asing

Saat membuat tabel, gunakan pernyataan CREATE TABLE untuk menambahkan kunci asing.
Untuk tabel yang sudah ada, gunakan pernyataan ALTER TABLE untuk menambahkan atau menghapus kunci asing.

Penting

Anda tidak dapat menggunakan beberapa kolom sebagai kunci asing. Contoh: FOREIGN KEY (sr_item_sk, sr_ticket_number) REFERENCES store_sales(ss_item_sk,d_date_sk).
Anda harus memeriksa hubungan batasan data antara kunci utama dan kunci asing. AnalyticDB for MySQL tidak memeriksa batasan data.
Saat menggunakan pernyataan DROP TABLE untuk menghapus kunci asing, AnalyticDB for MySQL tidak memeriksa operasi ini atau melaporkan kesalahan.
Anda tidak dapat menambahkan batasan kunci asing untuk tabel eksternal.

Gunakan pernyataan CREATE TABLE untuk menambahkan kunci asing

Sintaksis

Untuk informasi tentang sintaksis, lihat CREATE TABLE.

Contoh

Buat database bernama db dan buat tabel bernama item di dalam database. Atur kunci utama menjadi i_item_sk.

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);

Gunakan pernyataan CREATE TABLE untuk menambahkan kunci asing.
- Tambahkan kunci asing untuk database yang sama.
  Buat tabel bernama store_returns di dalam database db. Gunakan klausa FOREIGN KEY untuk mengaitkan kolom sr_item_sk dari tabel store_returns dengan kolom kunci utama i_item_sk dari tabel item.
```
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)
);
```
  Catatan
  Parameter symbol tidak ditentukan. Dalam hal ini, parser secara otomatis menggunakan sr_item_sk_fk sebagai nama batasan kunci asing.
- Tambahkan kunci asing untuk database lain.
  Buat database bernama db2, buat tabel bernama store_sales di dalam database, lalu tambahkan kolom kunci asing bernama ss_item_sk.
```
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)
);
```

Gunakan pernyataan CREATE TABLE untuk mendeklarasikan beberapa kunci asing untuk tabel yang sama

Contoh:

Buat dua tabel bernama customer dan vendor.

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 bernama store_product. Kaitkan kolom sr_sale_id dari tabel store_product dengan kolom kunci utama id dari tabel vendor. Lalu, kaitkan kolom sr_customer_sk dari tabel store_product dengan kolom kunci utama i_customer_sk dari tabel customer.
```
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)
);
```

Kueri kunci asing

Gunakan pernyataan SHOW CREATE TABLE untuk menanyakan kunci asing.

Contoh:

USE db;
SHOW CREATE TABLE store_returns;

Hasil contoh:

-- Hasil (pernyataan lainnya dihilangkan):
-- CONSTRAINT `sr_item_sk_fk` FOREIGN KEY (`sr_item_sk`) REFERENCES `db`.`item`(`i_item_sk`)

Gunakan pernyataan ALTER TABLE untuk menambahkan atau menghapus kunci asing

Sintaksis

Untuk informasi tentang sintaksis, lihat ALTER TABLE.

Contoh

Dalam contoh-contoh berikut, database bernama db digunakan. Tabel tempat Anda ingin menambahkan kunci asing bernama store_returns. Tabel utama bernama item.

Hapus kunci asing dari tabel store_returns.

USE db;
ALTER TABLE store_returns DROP FOREIGN KEY sr_item_sk_fk;

Tambahkan kunci asing untuk tabel store_returns.

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

Gunakan pernyataan ALTER TABLE untuk mendeklarasikan beberapa kunci asing untuk tabel yang sama

Anda dapat menggunakan satu pernyataan ALTER TABLE untuk menambahkan satu kunci asing untuk sebuah tabel pada satu waktu. Untuk menambahkan beberapa kunci asing untuk sebuah tabel, jalankan beberapa pernyataan ALTER TABLE.

Contoh:

Buat tabel bernama store di dalam database db2.

USE db2;
CREATE TABLE store
(
  id bigint primary key,
  name varchar(5) not null
);

Tambahkan kunci asing untuk tabel store_returns di dalam database db.

ALTER TABLE db.store_returns ADD FOREIGN KEY (sr_store_sk) REFERENCES store(id);

Terapkan aturan eliminasi join

Jika Anda hanya ingin menanyakan data dari salah satu tabel yang digabungkan, Anda dapat menggunakan batasan kunci utama dan kunci asing untuk menghilangkan join yang tidak perlu, menyederhanakan rencana eksekusi, dan meningkatkan kinerja kueri. Eliminasi join umumnya digunakan dalam skenario berikut:

Dua tabel dalam database yang sama digabungkan

Sebagai contoh, Anda ingin menanyakan data dari tabel store_returns setelah Anda melakukan operasi join pada tabel store_returns dan item.

/*+ 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;

Hasil contoh:

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

Rencana eksekusi tidak berisi operator INNER JOIN. Dalam hal ini, pengoptimal dapat menggunakan hubungan batasan antara kolom sr_item_sk dan i_item_sk untuk menghilangkan join antara tabel store_returns dan item.

Dua tabel lintas database digabungkan

Sebagai contoh, Anda ingin menanyakan data dari tabel store_sales setelah Anda melakukan operasi join pada tabel item di dalam database db dan tabel store_sales di dalam database db2.

USE db2;
-- Pernyataan kueri contoh
/*+ 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;

Hasil contoh:

+---------------+
| Ringkasan Rencana  |
+---------------+
 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 digabungkan

Sebagai contoh, Anda ingin menanyakan data dari tabel store_returns setelah Anda melakukan operasi join pada tabel store_returns, item, dan store.

USE db;
-- Pernyataan kueri contoh
/*+ 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;

Hasil contoh:

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

Sebuah tampilan dibuat berdasarkan tabel-tabel yang digabungkan

Sebuah tampilan mungkin dibuat berdasarkan dua tabel atau lebih yang digabungkan. Saat Anda menggunakan tampilan dalam pernyataan kueri lain yang tidak merujuk informasi spesifik tentang tampilan, pengoptimal dapat menghilangkan join yang tidak perlu. Sebagai contoh, buat tampilan yang berisi informasi tentang tabel 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;

Jika kueri tidak merujuk kolom item_price dari tabel item, pengoptimal dapat memindai hanya tabel store_returns, bukan tabel item.

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

Hasil contoh:

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

Batasan kunci asing ada antara kolom item.i_item_sk dan store_returns.sr_item_sk. Pengoptimal dapat mengganti referensi ke kolom item.i_item_sk dengan referensi ke kolom store_returns.sr_item_sk. Dalam hal ini, pengoptimal dapat menanyakan data dari kolom tabel store_returns dan menghilangkan join yang tidak perlu.

Kasus pengecualian eliminasi join

Dalam contoh tampilan sebelumnya, jika kueri merujuk kolom tabel item, Anda tidak dapat menghilangkan join.

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

Hasil contoh:

+---------------+
| Ringkasan Rencana  |
+---------------+
 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}