cara mengubah jenis tabel dan kebijakan partisi (termasuk fungsi partisi atau kolom kunci partisi) - PolarDB

Ketika volume data dalam satu tabel besar hingga memperlambat kueri, atau kebijakan partisi awal yang tidak tepat menyebabkan hot spot data, skema tabel asli dapat menjadi bottleneck performa. Untuk mengatasi masalah ini, PolarDB for Xscale menyediakan fitur online untuk mengubah jenis tabel dan kebijakan partisi. Fitur ini memungkinkan Anda mendistribusikan ulang data tanpa mengganggu bisnis karena tidak mengunci tabel atau memblokir operasi Data Manipulation Language (DML). Anda dapat mengonversi tabel antara jenis single, broadcast, dan partisi, serta menyesuaikan kunci partisi atau algoritma partisi yang sudah ada. Hal ini mengoptimalkan tata letak data dan meningkatkan performa serta skalabilitas database secara keseluruhan.

Penting

Operasi ini merupakan tugas Data Definition Language (DDL) berat yang melibatkan migrasi data penuh dan redistribusi data secara online.
Saat dijalankan, tugas ini mengonsumsi sumber daya CPU, I/O, dan jaringan yang signifikan sehingga dapat memengaruhi performa instans. Waktu yang dibutuhkan sebanding dengan volume data.
Jalankan operasi ini pada jam non-sibuk dan gunakan perintah manajemen tugas untuk memantau progresnya.

Penerapan

Sebelum melakukan perubahan ini, pastikan lingkungan Anda memenuhi kondisi berikut.

Mode database: Fitur ini hanya berlaku untuk database dalam mode AUTO.
Versi instans: Hanya instans PolarDB-X 2.0 dengan versi mesin minor 5.4.13 atau lebih baru yang didukung.
Indeks sekunder global (GSI): Untuk mengubah partisi pada tabel partisi yang memiliki GSI, Anda memerlukan instans PolarDB-X 2.0 dengan versi mesin minor 5.4.14 atau lebih baru. Selama perubahan, data GSI juga didistribusikan ulang.

Catatan

Untuk informasi tentang aturan penamaan versi instans, lihat Catatan rilis.
Untuk informasi tentang cara melihat versi instans, lihat Melihat dan memperbarui versi instans.

Cara kerja

DDL Online: Semua operasi perubahan dijalankan secara online. Tabel asli tidak dikunci, sehingga operasi DML dan SELECT dapat berjalan seperti biasa untuk memastikan kelangsungan bisnis.
Redistribusi data: Inti dari perubahan jenis tabel atau kebijakan partisi adalah migrasi data fisik. Contohnya:
- Saat Anda mengonversi tabel single menjadi tabel partisi, data didistribusikan dari satu node data (DN) ke beberapa DN.
- Saat Anda mengubah kunci partisi, data diseimbangkan ulang di antara DN berdasarkan kebijakan partisi baru. Proses inilah penyebab utama konsumsi sumber daya.
Atomisitas dan rollback: Seluruh tugas DDL bersifat atomik. Jika tugas gagal karena konflik kunci unik, ruang disk tidak mencukupi, atau alasan lain, sistem memastikan bahwa data tabel asli tetap utuh dan bisnis Anda tidak terpengaruh. Anda dapat melakukan rollback tugas yang gagal menggunakan perintah CANCEL DDL.

Mengubah jenis tabel

PolarDB for Xscale mendukung tiga jenis tabel: tabel single, tabel broadcast, dan tabel partisi. Anda dapat mengonversi tabel antar jenis ini secara online seiring perubahan skenario bisnis Anda. Untuk informasi selengkapnya, lihat CREATE TABLE (mode AUTO).

Catatan

Saat Anda mengubah jenis tabel, jika tabel asli adalah tabel partisi KEY (di mana kunci partisi sama dengan kunci primer) dan Anda mengonversinya menjadi tabel standar, tabel tersebut kehilangan fitur-fitur partisi kunci primernya, seperti kebijakan partisi otomatis dan aturan konversi indeks. Untuk informasi selengkapnya, lihat Sharding otomatis dalam mode AUTO.

Mengonversi tabel single atau tabel broadcast menjadi tabel partisi

Skenario ini cocok ketika volume data tabel single terus bertambah hingga menjadi atau akan segera menjadi bottleneck performa. Anda dapat membaginya secara horizontal untuk meningkatkan skalabilitas dan performa kueri.

Sintaks

Gunakan pernyataan ALTER TABLE dan tentukan kebijakan partisi baru. Untuk informasi lebih lanjut tentang sintaks partition_options, lihat Tabel partisi.

ALTER TABLE table_name PARTITION BY partition_options;

Contoh

Buat tabel contoh bernama t_order.

CREATE TABLE t_order (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `order_id` varchar(20) DEFAULT NULL,
  `buyer_id` varchar(20) DEFAULT NULL,
  `seller_id` varchar(20) DEFAULT NULL,
  `order_snapshot` longtext DEFAULT NULL,
  `order_detail` longtext DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `l_i_order` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Volume data tabel single t_order meningkat akibat pertumbuhan bisnis. Konversikan menjadi tabel partisi dengan order_id sebagai kunci partisi.

-- Mengonversi tabel single t_order menjadi tabel partisi KEY dengan order_id sebagai kunci partisi.
ALTER TABLE t_order PARTITION BY KEY(`order_id`);

-- Anda juga dapat menentukan jumlah partisi selama konversi.
ALTER TABLE t_order PARTITION BY KEY(`order_id`) PARTITIONS 8;

Mengonversi tabel partisi atau tabel single menjadi tabel broadcast

Skenario ini cocok untuk tabel dengan volume data kecil dan relatif statis yang sering digabungkan (join) dengan tabel besar, seperti tabel konfigurasi dan tabel kamus. Dengan mereplikasi data tabel ke setiap node data (DN), Anda dapat menghilangkan overhead jaringan dari join lintas database dan meningkatkan performa kueri join.

Sintaks

Gunakan pernyataan ALTER TABLE dan tentukan klausa BROADCAST.

ALTER TABLE table_name BROADCAST;

Catatan

Tabel broadcast menyimpan replika data di setiap DN, sehingga mengonsumsi lebih banyak ruang penyimpanan.

Contoh

Buat tabel contoh bernama t_order_1.

CREATE TABLE t_order_1 (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `order_id` varchar(20) DEFAULT NULL,
  `buyer_id` varchar(20) DEFAULT NULL,
  `seller_id` varchar(20) DEFAULT NULL,
  `order_snapshot` longtext DEFAULT NULL,
  `order_detail` longtext DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `l_i_order` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Konversikan tabel t_order_1 menjadi tabel broadcast.
```
ALTER TABLE t_order_1 BROADCAST;
```

Mengonversi tabel partisi atau tabel broadcast menjadi tabel single

Skenario ini cocok untuk tabel dengan volume data kecil yang tidak memerlukan skalabilitas horizontal. Anda dapat mengonsolidasikan data tabel ke satu node data (DN) untuk menyederhanakan skema tabel.

Sintaks

Gunakan pernyataan ALTER TABLE dan tentukan klausa SINGLE.

ALTER TABLE table_name SINGLE;

Catatan

Saat Anda mengonversi tabel partisi menjadi tabel single, data dari semua shard dikonsolidasikan ke satu DN. Pastikan node target memiliki ruang disk yang cukup untuk mencegah kegagalan operasi akibat ruang tidak mencukupi.

Contoh

Buat tabel contoh bernama t_order_2.

CREATE TABLE t_order_2 (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `order_id` varchar(20) DEFAULT NULL,
  `buyer_id` varchar(20) DEFAULT NULL,
  `seller_id` varchar(20) DEFAULT NULL,
  `order_snapshot` longtext DEFAULT NULL,
  `order_detail` longtext DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `l_i_order` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY KEY(`order_id`)
PARTITIONS 8;

Konversikan tabel t_order_2 menjadi tabel single.
```
ALTER TABLE t_order_2 SINGLE;
```

Mengubah kebijakan partisi tabel partisi

Saat kebijakan partisi yang ada tidak lagi memenuhi kebutuhan bisnis, Anda dapat menyesuaikannya secara online. Ini berguna untuk mengatasi masalah seperti kesenjangan data akibat pemilihan kunci partisi yang buruk atau perubahan pola kueri. Anda dapat memodifikasi kunci partisi, fungsi partisi, atau jumlah partisi untuk mengoptimalkan ulang distribusi data fisik, sehingga membantu mengatasi hot spot data dan menyesuaikan diri dengan model bisnis baru.

Sintaks

Gunakan pernyataan ALTER TABLE dan tentukan kebijakan partisi baru.

ALTER TABLE tbl_name PARTITION BY new_partition_options;

Catatan

Jika tabel asli adalah tabel partisi KEY dan Anda mengubah kebijakan partisi sehingga kunci partisi baru bukan kunci primer, tabel tersebut kehilangan fitur-fitur partisi kunci primernya, seperti pembuatan partisi otomatis. Untuk informasi selengkapnya, lihat Sharding otomatis dalam mode AUTO.
Setelah Anda mengubah kebijakan partisi, jika kunci primer tidak mencakup semua kolom kunci partisi, kunci primer tersebut menjadi kunci primer lokal yang hanya dapat menjamin keunikan dalam satu partisi, bukan secara global. Untuk informasi selengkapnya, lihat Kunci primer dan kunci unik (mode AUTO).

Contoh

Mengubah kunci partisi

Tabel bernama t_order_3 saat ini dipartisi berdasarkan order_id. Namun, sebagian besar kueri didasarkan pada buyer_id, yang menyebabkan banyak kueri lintas shard. Anda dapat mengubah kunci partisi menjadi buyer_id dan menetapkan jumlah partisi menjadi 16.

Lihat skema tabel asli.

CREATE TABLE t_order_3 (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `order_id` varchar(20) DEFAULT NULL,
  `buyer_id` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
PARTITION BY KEY(`order_id`)
PARTITIONS 8;

Jalankan perubahan.

ALTER TABLE t_order_3 PARTITION BY KEY(buyer_id) PARTITIONS 16;

Lihat skema tabel setelah perubahan. Sistem secara otomatis membuat indeks kunci partisi baru bernama auto_shard_key_buyer_id.

SHOW FULL CREATE TABLE  t_order_3;

+-----------+-----------------------------------------------------------------------+
| Table     | Create Table                                                          |
+-----------+-----------------------------------------------------------------------+
| t_order_3 | CREATE TABLE `t_order_3` (
        `id` bigint NOT NULL AUTO_INCREMENT,
        `order_id` varchar(20) DEFAULT NULL,
        `buyer_id` varchar(20) DEFAULT NULL,
        PRIMARY KEY (`id`),
        LOCAL KEY `auto_shard_key_buyer_id` USING BTREE (`buyer_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 DEFAULT COLLATE = utf8mb4_0900_ai_ci
PARTITION BY KEY(`buyer_id`)
PARTITIONS 16
/* tablegroup = `tg12` */ |
+-----------+---------------------------------------------------------------------+

Mengubah strategi partisi

Tabel bernama t_order_4 saat ini dipartisi berdasarkan RANGE pada kolom id. Namun, sebagian besar kueri didasarkan pada order_id dan buyer_id. Anda dapat mengubah partisi menjadi partisi KEY dengan order_id dan buyer_id sebagai kunci partisi, dan menetapkan jumlah partisi menjadi 16.

Lihat skema tabel asli.

CREATE TABLE t_order_4 (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `order_id` varchar(20) DEFAULT NULL,
  `buyer_id` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
PARTITION BY RANGE(`id`)
(
  PARTITION p1 VALUES LESS THAN (100),
  PARTITION p2 VALUES LESS THAN (1000),
  PARTITION P3 VALUES LESS THAN MAXVALUE
);

Jalankan perubahan.

ALTER TABLE t_order_4 PARTITION BY KEY(order_id, buyer_id) PARTITIONS 16;

Lihat skema tabel setelah perubahan. Sistem secara otomatis membuat indeks kunci partisi baru bernama auto_shard_key_order_id_buyer_id.

SHOW FULL CREATE TABLE  t_order_4;

+-----------+--------------------------------------------------------------------------------+
| Table     | Create Table                                                                   |
+-----------+--------------------------------------------------------------------------------+
| t_order_4 | CREATE TABLE `t_order_4` (
        `id` bigint NOT NULL AUTO_INCREMENT,
        `order_id` varchar(20) DEFAULT NULL,
        `buyer_id` varchar(20) DEFAULT NULL,
        PRIMARY KEY (`id`),
        LOCAL KEY `auto_shard_key_order_id_buyer_id` USING BTREE (`order_id`, `buyer_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb3
PARTITION BY KEY(`order_id`,`buyer_id`)
PARTITIONS 16
/* tablegroup = `tg15` */ |
+-----------+------------------------------------------------------------------------------+

Menambah jumlah partisi

Saat volume data bertambah, Anda dapat menambah jumlah partisi untuk mendistribusikan data lebih luas.

Lihat skema tabel asli.

CREATE TABLE t_order_5 (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `order_id` varchar(20) DEFAULT NULL,
  `buyer_id` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
PARTITION BY KEY(`order_id`)
PARTITIONS 8;

Jalankan perubahan.

ALTER TABLE t_order_5 PARTITION BY KEY(order_id) PARTITIONS 128;

Setelah perubahan, jumlah partisi ditingkatkan dari 8 menjadi 128.

SHOW FULL CREATE TABLE  t_order_5;

+-----------+----------------------------------------------------------+
| Table     | Create Table                                             |
+-----------+----------------------------------------------------------+
| t_order_4 | CREATE TABLE `t_order_5` (
        `id` bigint NOT NULL AUTO_INCREMENT,
        `order_id` varchar(20) DEFAULT NULL,
        `buyer_id` varchar(20) DEFAULT NULL,
        PRIMARY KEY (`id`),
        LOCAL KEY `auto_shard_key_order_id` USING BTREE (`order_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb3
PARTITION BY KEY(`order_id`)
PARTITIONS 128
/* tablegroup = `tg13` */ |
+-----------+---------------------------------------------------------+

Manajemen tugas dan penanganan darurat

Untuk tugas DDL redistribusi data yang memakan waktu lama, Anda dapat menggunakan perintah berikut untuk manajemen siklus hidup penuh.

Memantau progres tugas: Anda dapat menggunakan perintah SHOW DDL untuk melihat daftar tugas DDL saat ini dan historis beserta statusnya.
Menjeda dan melanjutkan tugas: Jika tugas DDL sangat memengaruhi performa selama jam sibuk, Anda dapat menjeda tugas tersebut dan melanjutkannya pada jam non-sibuk.
```
-- Menjeda tugas. Dapatkan JobId dari hasil SHOW DDL.
PAUSE DDL <JobId>;

-- Melanjutkan tugas.
CONTINUE DDL <JobId>;
```
Menghentikan dan melakukan rollback tugas: Jika tugas DDL gagal atau Anda ingin membatalkan tugas yang sedang berjalan, Anda dapat menggunakan perintah CANCEL DDL. Operasi ini melakukan rollback semua perubahan dan mengembalikan skema serta data tabel ke kondisi sebelum tugas dimulai.
```
-- Melakukan rollback tugas. Dapatkan JobId dari hasil SHOW DDL.
CANCEL DDL <JobId>;
```

Peluncuran

Waktu eksekusi: Jalankan operasi ini pada jam non-sibuk untuk meminimalkan potensi dampak performa terhadap bisnis online Anda.
Perencanaan kapasitas: Sebelum menjalankan perubahan, evaluasi apakah node data target (DN) memiliki ruang disk, CPU, dan sumber daya operasi input/output per detik (IOPS) yang cukup. Hal ini terutama penting untuk skenario seperti mengonversi tabel partisi menjadi tabel single atau redistribusi data drastis, guna mencegah kegagalan tugas akibat sumber daya tidak mencukupi.
Cadangan sebelum operasi: Meskipun tugas DDL yang gagal dapat di-rollback, kami menyarankan Anda membuat cadangan data penuh sebelum melakukan perubahan besar apa pun di lingkungan produksi.
Verifikasi: Setelah tugas selesai, Anda dapat menggunakan SHOW CREATE TABLE <tablename> untuk melihat definisi tabel baru atau menggunakan SHOW TOPOLOGY untuk melihat topologi sharding data baru.

FAQ

Mengapa tugas DDL untuk mengubah kunci partisi gagal? Apa yang harus saya lakukan?

Alasan umum kegagalan meliputi crash instans, ruang disk tidak mencukupi pada node target, atau konflik indeks unik selama migrasi data. Tugas yang gagal tidak merusak data tabel asli atau memblokir operasi DML dan kueri normal. Anda dapat menggunakan SHOW DDL untuk menemukan penyebab kegagalan. Setelah menyelesaikan masalah tersebut (misalnya, dengan membersihkan ruang disk atau menangani data yang bertentangan), Anda dapat menggunakan perintah CANCEL DDL untuk melakukan rollback tugas yang gagal, lalu mencoba menjalankannya kembali.

Apakah operasi seperti mengubah kebijakan partisi selalu memakan waktu lama? Apakah menambahkan partisi kosong baru juga lambat?

Tidak, tidak semua operasi partisi berat. Mengubah jenis tabel atau kebijakan partisi memakan waktu lama karena melibatkan migrasi data penuh. Namun, menambahkan partisi kosong di masa depan ke tabel partisi RANGE/LIST (misalnya, menggunakan ALTER TABLE ... ADD PARTITION) biasanya merupakan operasi metadata yang selesai dalam hitungan detik dengan dampak minimal terhadap bisnis Anda.

Jika indeks sudah ada pada kunci partisi baru, apakah sistem tetap membuat indeks auto_shard_key_... secara otomatis?

Sistem memeriksa apakah kolom kunci partisi baru merupakan awalan paling kiri dari indeks yang sudah ada. Jika ya, sistem menggunakan kembali indeks tersebut untuk pemangkasan partisi dan tidak membuat indeks baru. Jika tidak, sistem secara otomatis membuat indeks lokal baru untuk memastikan performa kueri.

Bagaimana cara memeriksa apakah database berada dalam mode AUTO atau mode DRDS?

Anda dapat menjalankan SHOW CREATE DATABASE <database_name>. Properti MODE dalam hasilnya menunjukkan mode database.