Kapan menggunakan pembagian berdasarkan rentang - PolarDB

Pembagian berdasarkan rentang adalah metode yang efisien untuk mengelola data historis. Dalam pembagian ini, nilai batas digunakan untuk mendefinisikan rentang dan urutan partisi dalam tabel atau indeks.

Pembagian berdasarkan rentang sering digunakan untuk mengatur data pada interval waktu di kolom tipe DATE. Sebagian besar pernyataan SQL yang mengakses partisi rentang berfokus pada rentang waktu tertentu. Misalnya, jika setiap partisi mewakili data satu bulan, kueri untuk data periode 21-12 hanya akan memindai partisi Desember 2021. Metode optimisasi ini dikenal sebagai pemangkasan partisi.

Pembagian berdasarkan rentang juga cocok untuk skenario di mana data baru dimuat dan data lama dibersihkan secara berkala. Sebagai contoh, jendela bergulir biasanya dipertahankan untuk menanyakan data 36 bulan terakhir. Proses ini dapat disederhanakan dengan pembagian berdasarkan rentang. Untuk menambahkan data bulan baru, Anda dapat memuatnya ke tabel terpisah, membersihkannya, mengindeksnya, dan menjalankan pernyataan EXCHANGE PARTITION untuk menambahkannya ke tabel yang dibagi berdasarkan rentang, sementara tabel asli tetap dapat diakses. Setelah menambahkan partisi baru, Anda dapat menjalankan pernyataan DROP PARTITION untuk menghapus partisi bulan lalu.

Berikut adalah beberapa kasus penggunaan pembagian berdasarkan rentang:

Beberapa kolom sering dipindai berdasarkan rentang dalam tabel besar, seperti tabel pesanan ORDER atau jadwal pembelian LINEITEM. Pemangkasan partisi dapat diimplementasikan dengan membagi tabel pada kolom-kolom tersebut.
Jendela bergulir dipertahankan.
Operasi manajemen seperti pencadangan dan pemulihan pada tabel besar tidak dapat diselesaikan dalam rentang waktu tertentu, tetapi tabel besar dapat dibagi menjadi blok logis yang lebih kecil berdasarkan rentang.

Contoh: Buat tabel orders yang mencakup lebih dari sembilan tahun dan bagi berdasarkan rentang pada kolom o_orderdate. Bagilah data menjadi delapan tahun, dengan satu partisi untuk setiap tahun. Pemangkasan partisi dapat digunakan untuk menganalisis data penjualan dalam interval pendek. Metode jendela bergulir didukung.

CREATE TABLE `orders` (
  `o_orderkey` int(11) NOT NULL,
  `o_custkey` int(11) NOT NULL,
  `o_orderstatus` char(1) DEFAULT NULL,
  `o_totalprice` decimal(10,2) DEFAULT NULL,
  `o_orderDATE` date NOT NULL,
  `o_orderpriority` char(15) DEFAULT NULL,
  `o_clerk` char(15) DEFAULT NULL,
  `o_shippriority` int(11) DEFAULT NULL,
  `o_comment` varchar(79) DEFAULT NULL,
  PRIMARY KEY (`o_orderkey`,`o_orderDATE`,`o_custkey`),
  KEY `o_orderkey` (`o_orderkey`),
  KEY `i_o_custkey` (`o_custkey`),
  KEY `i_o_orderdate` (`o_orderDATE`)
) ENGINE=InnoDB
PARTITION BY RANGE (TO_DAYS(o_orderdate))
(PARTITION item1 VALUES LESS THAN (TO_DAYS('1992-01-01')),
 PARTITION item2 VALUES LESS THAN (TO_DAYS('1993-01-01')),
 PARTITION item3 VALUES LESS THAN (TO_DAYS('1994-01-01')),
 PARTITION item4 VALUES LESS THAN (TO_DAYS('1995-01-01')),
 PARTITION item5 VALUES LESS THAN (TO_DAYS('1996-01-01')),
 PARTITION item6 VALUES LESS THAN (TO_DAYS('1997-01-01')),
 PARTITION item7 VALUES LESS THAN (TO_DAYS('1998-01-01')),
 PARTITION item8 VALUES LESS THAN (TO_DAYS('1999-01-01')),
 PARTITION item9 VALUES LESS THAN (MAXVALUE));

 EXPLAIn select * from orders where o_orderDATE = '1992-03-01';
+----+-------------+--------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | orders | item2      | ref  | i_o_orderdate | i_o_orderdate | 3       | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+---------------+---------+-------+------+----------+-------+

Karena batasan tipe RANGE, pernyataan DDL asli mungkin tidak ditampilkan setelah Anda membagi tabel menggunakan TO_DAYS() dan menjalankan pernyataan SHOW CREATE TABLE. Contoh:

show create table orders;
| orders | CREATE TABLE `orders` (
  `o_orderkey` int(11) NOT NULL,
  `o_custkey` int(11) NOT NULL,
  `o_orderstatus` char(1) DEFAULT NULL,
  `o_totalprice` decimal(10,2) DEFAULT NULL,
  `o_orderDATE` date NOT NULL,
  `o_orderpriority` char(15) DEFAULT NULL,
  `o_clerk` char(15) DEFAULT NULL,
  `o_shippriority` int(11) DEFAULT NULL,
  `o_comment` varchar(79) DEFAULT NULL,
  PRIMARY KEY (`o_orderkey`,`o_orderDATE`,`o_custkey`),
  KEY `o_orderkey` (`o_orderkey`),
  KEY `i_o_custkey` (`o_custkey`),
  KEY `i_o_orderdate` (`o_orderDATE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (to_days(`o_orderDATE`))
(PARTITION item1 VALUES LESS THAN (727563),
 PARTITION item2 VALUES LESS THAN (727929),
 PARTITION item3 VALUES LESS THAN (728294),
 PARTITION item4 VALUES LESS THAN (728659),
 PARTITION item5 VALUES LESS THAN (729024),
 PARTITION item6 VALUES LESS THAN (729390),
 PARTITION item7 VALUES LESS THAN (729755),
 PARTITION item8 VALUES LESS THAN (730120),
 PARTITION item9 VALUES LESS THAN MAXVALUE) */ |

Pembagian berdasarkan rentang kolom merupakan ekstensi dari pembagian berdasarkan rentang. Beberapa nilai kolom didefinisikan untuk pembagian ini, dan kolom non-integer dapat dipilih sebagai kolom kunci partisi.

Pembagian berdasarkan rentang kolom berbeda dari pembagian berdasarkan rentang dalam beberapa hal berikut:

Dalam RANGE COLUMNS(), Anda hanya dapat menentukan nama kolom, bukan ekspresi.
RANGE COLUMNS() dapat mencakup satu atau lebih kolom. Pembagian berdasarkan rentang kolom membandingkan tupel alih-alih nilai skalar. Posisi baris dalam pembagian ini juga didasarkan pada perbandingan antar tupel.
Dalam pembagian berdasarkan rentang kolom, kolom kunci partisi dapat bertipe INTEGER, STRING, DATE, dan DATETIME.

Buat tabel yang dibagi berdasarkan rentang kolom:

CREATE TABLE `orders` (
  `o_orderkey` int(11) NOT NULL,
  `o_custkey` int(11) NOT NULL,
  `o_orderstatus` char(1) DEFAULT NULL,
  `o_totalprice` decimal(10,2) DEFAULT NULL,
  `o_orderDATE` date NOT NULL,
  `o_orderpriority` char(15) DEFAULT NULL,
  `o_clerk` char(15) DEFAULT NULL,
  `o_shippriority` int(11) DEFAULT NULL,
  `o_comment` varchar(79) DEFAULT NULL,
  PRIMARY KEY (`o_orderkey`,`o_orderDATE`,`o_custkey`),
  KEY `o_orderkey` (`o_orderkey`),
  KEY `i_o_custkey` (`o_custkey`),
  KEY `i_o_orderdate` (`o_orderDATE`)
) ENGINE=InnoDB
PARTITION BY RANGE COLUMNS(o_orderdate)
(PARTITION item1 VALUES LESS THAN ('1992-01-01'),
 PARTITION item2 VALUES LESS THAN ('1993-01-01'),
 PARTITION item3 VALUES LESS THAN ('1994-01-01'),
 PARTITION item4 VALUES LESS THAN ('1995-01-01'),
 PARTITION item5 VALUES LESS THAN ('1996-01-01'),
 PARTITION item6 VALUES LESS THAN ('1997-01-01'),
 PARTITION item7 VALUES LESS THAN ('1998-01-01'),
 PARTITION item8 VALUES LESS THAN ('1999-01-01'),
 PARTITION item9 VALUES LESS THAN (MAXVALUE));