Kueri Data Historis dengan Flashback di PolarDB MySQL - PolarDB

Fitur flashback query memungkinkan Anda melakukan kueri terhadap kluster, database, atau tabel pada titik waktu tertentu di masa lalu secara efisien.

Prasyarat

Kluster PolarDB Anda harus memenuhi salah satu persyaratan versi berikut:
- PolarDB for MySQL 8.0.2 dengan revisi versi 8.0.2.2.21 atau lebih baru
- PolarDB for MySQL 8.0.1 dengan revisi versi 8.0.1.1.32 atau lebih baru
- PolarDB for MySQL 5.7 dengan revisi versi 5.7.1.0.25 atau lebih baru
- PolarDB for MySQL 5.6 dengan revisi versi 5.6.1.0.36 atau lebih baru
Untuk informasi lebih lanjut tentang cara melihat versi kluster Anda, lihat Query the version number.
Fitur flashback query mengharuskan Anda mengaktifkan parameter innodb_backquery_enable, yang secara default dinonaktifkan. Sebelum dapat menggunakan fitur ini, Anda harus mengaktifkan parameter innodb_backquery_enable pada halaman Parameters kluster PolarDB Anda.
Catatan
Jika Anda menjalankan flashback query sebelum mengaktifkan parameter innodb_backquery_enable, sistem akan mengembalikan error ERROR 1815 (HY000): Internal error: the backquery_time set is out of range, too old.

Catatan penggunaan

Gunakan flashback query untuk skenario tabel tunggal. Hindari penggunaannya untuk kueri kompleks, seperti yang melibatkan JOIN atau subkueri.
Gunakan primary key untuk flashback query. Indeks sekunder tidak didukung. Jika Anda menggunakan indeks sekunder, kueri akan dikonversi menjadi full table scan, yang menurunkan performa kueri.
Log Undo menyimpan data historis. Saat Anda mengaktifkan fitur flashback query, ruang tabel Undo akan bertambah dalam rentang waktu yang ditetapkan oleh innodb_backquery_window. Ruang tabel juga dapat bertambah dalam skenario BLOB. Pertumbuhan ini dapat sedikit menurunkan performa penulisan.
Saat Anda menjalankan flashback query, jumlah log Undo meningkat. Catatan yang ditandai untuk dihapus mungkin tidak segera dihapus. Hal ini dapat mengurangi kecepatan kueri dalam beberapa kasus.
Satu catatan dapat memiliki maksimal 100.000 versi historis. Jika Anda menjalankan flashback query pada catatan yang melebihi batas ini, sistem akan mengembalikan error record undo history version exceed limit.
Setelah Anda menjalankan operasi DDL, Anda tidak dapat menjalankan flashback query terhadap data yang ada sebelum operasi tersebut. Jika Anda mencoba mengkueri data ini, sistem mungkin mengembalikan error Backquery primary key invisible.
Anda dapat menggunakan flashback query untuk melihat tabel yang dihapus setelah fitur diaktifkan. Anda tidak dapat menggunakan flashback query untuk melihat tabel yang dihapus sebelum fitur diaktifkan.

Flashback query hanya berlaku untuk pembacaan snapshot. Penggunaan lock read akan mengembalikan error: This query in backquery is not a consistent read, please check.. Berikut adalah contoh pernyataan lock read umum:

/*Menambahkan S LOCK pada tingkat isolasi REPEATABLE-READ atau lebih tinggi*/
INSERT INTO t1 SELECT * FROM t2 

/*Menambahkan S LOCK pada tingkat isolasi REPEATABLE-READ atau lebih tinggi*/
REPLACE INTO t1 SELECT * FROM t2 

/*Menambahkan S LOCK pada tingkat isolasi REPEATABLE-READ atau lebih tinggi*/
UPDATE t SET ... FROM (SELECT ...) AS h 

/*Menambahkan S LOCK pada tingkat isolasi REPEATABLE-READ atau lebih tinggi*/
CREATE TABLE t1 AS SELECT * FROM t2 

/*S LOCK*/
UPDATE t1 JOIN (SELECT ...) t2 ON ... SET ... 

/*X LOCK*/
SELECT * FROM t FOR UPDATE 

/*S LOCK*/
SELECT * FROM t LOCK IN SHARE MODE

Sintaks

Flashback query tabel tunggal

SELECT column_name_list FROM table_name AS OF TIMESTAMP time_expr alias WHERE...;

Flashback query multi-tabel

SELECT column_name_list FROM table1_name AS OF TIMESTAMP time_expr alias1,table2_name AS OF TIMESTAMP time_expr alias2 WHERE... ;

Flashback query multi-tabel dengan JOIN

SELECT column_name_list FROM table1_name AS OF TIMESTAMP time_expr alias1 
JOIN table2_name AS OF TIMESTAMP time_expr alias2 ON join_cond1 
JOIN table3_name AS OF TIMESTAMP time_expr alias3 ON join_cond2
WHERE...;

Tabel berikut menjelaskan parameter dalam sintaks tersebut.

Nama Parameter	Wajib	Deskripsi
column_name_list	Ya	Nama kolom yang akan dikueri.
table_name table1_name table2_name table3_name	Ya	Nama tabel.
time_expr	Ya	Timestamp untuk flashback. Nilai ini dapat berupa string waktu atau fungsi waktu. Hanya ekspresi konstan yang didukung. Nama kolom tidak diperbolehkan. Contoh: String waktu: '2021-08-31 14:00:00'. Fungsi waktu: `FROM_UNIXTIMESTAMP(unix_timestamp('2024-01-01 00:00:00'))`, `CONVERT(unix_timestamp('2024-01-01 00:00:00'), DATETIME)`.
alias alias1 alias2 alias3	Tidak	Alias untuk tabel.
join_cond1 join_cond2	Ya	Kondisi JOIN.

Parameter

PolarDB menyediakan parameter berikut untuk mengontrol fitur flashback:

Nama Parameter	Tipe Data	Deskripsi
loose_innodb_backquery_enable	BOOL	Mengaktifkan atau menonaktifkan fitur flashback query. Nilai yang valid: ON: Mengaktifkan fitur. OFF: Menonaktifkan fitur. (Default)
loose_innodb_backquery_window	ULONG	Rentang waktu selama flashback query didukung. Rentang nilai: 1 hingga 604800. Unit: detik. Nilai default: 86400.
loose_innodb_backquery_capacity_limit	ULONG	Batas kapasitas log Undo untuk flashback query. Jika kapasitas log Undo mencapai batas ini, rentang waktu flashback akan dipersingkat. Rentang nilai: 100 hingga 200000000. Unit: MB. Nilai default: 100000000.

Contoh

Bagian ini memberikan contoh flashback query tabel tunggal.

Siapkan data uji:

Pada 2021-08-31 13:51, buat tabel products dan masukkan data.

create table products (
       prod_id bigint(10) primary key NOT NULL,
       prod_name varchar(20) NOT NULL,
       cust_id bigint(10) NULL,
       createtime datetime NOT NULL DEFAULT NOW()
);

INSERT INTO  products(prod_id,prod_name,cust_id,createtime)
values
(101,'Book',1,NOW()),(102,'Apple',1,NOW()),(103,'Beef',2,NOW()),(104,'Bread',3,NOW()),(105,'Cheese',4,NOW());

Kueri data dalam tabel products:

SELECT * FROM products;
+---------+-----------+---------+---------------------+
| prod_id | prod_name | cust_id | createtime          |
+---------+-----------+---------+---------------------+
|     101 | Book      |       1 | 2021-08-31 13:51:22 |
|     102 | Apple     |       1 | 2021-08-31 13:51:24 |
|     103 | Beef      |       2 | 2021-08-31 13:51:26 |
|     104 | Bread     |       3 | 2021-08-31 13:51:27 |
|     105 | Cheese    |       4 | 2021-08-31 13:51:29 |
+---------+-----------+---------+---------------------+
5 rows in set (0.00 sec)

Perbarui data uji:

Pada 2021-08-31 14:18, perbarui data dalam tabel products.

UPDATE products SET prod_id = 110, createtime = NOW() WHERE prod_name = "Book";
UPDATE products SET prod_id = 119, createtime = NOW() WHERE prod_name = "Apple";

SELECT * FROM products;
+---------+-----------+---------+---------------------+
| prod_id | prod_name | cust_id | createtime          |
+---------+-----------+---------+---------------------+
|     103 | Beef      |       2 | 2021-08-31 13:51:26 |
|     104 | Bread     |       3 | 2021-08-31 13:51:27 |
|     105 | Cheese    |       4 | 2021-08-31 13:51:29 |
|     110 | Book      |       1 | 2021-08-31 14:18:21 |
|     119 | Apple     |       1 | 2021-08-31 14:18:22 |
+---------+-----------+---------+---------------------+
5 rows in set (0.00 sec)

Jalankan flashback query:

Lihat data dalam tabel products pada titik waktu 2021-08-31 14:00:00.

SELECT * FROM products AS of TIMESTAMP '2021-08-31 14:00:00';
+---------+-----------+---------+---------------------+
| prod_id | prod_name | cust_id | createtime          |
+---------+-----------+---------+---------------------+
|     101 | Book      |       1 | 2021-08-31 13:51:22 |
|     102 | Apple     |       1 | 2021-08-31 13:51:24 |
|     103 | Beef      |       2 | 2021-08-31 13:51:26 |
|     104 | Bread     |       3 | 2021-08-31 13:51:27 |
|     105 | Cheese    |       4 | 2021-08-31 13:51:29 |
+---------+-----------+---------+---------------------+
5 rows in set (0.00 sec)