cara merancang skema tabel AnalyticDB for MySQL, kunci distribusi, kunci partisi - AnalyticDB

Topik ini menjelaskan cara merancang skema tabel AnalyticDB for MySQL untuk mengoptimalkan kinerja. Skema mencakup jenis tabel, kunci distribusi, kunci partisi, kunci primer, dan kunci indeks terkluster.

Pilih jenis tabel

AnalyticDB for MySQL mendukung tabel replikasi dan tabel standar. Saat memilih jenis tabel, pertimbangkan hal-hal berikut:

Tabel replikasi menyimpan salinan data di setiap node kluster. Disarankan agar volume data dalam setiap tabel replikasi tidak melebihi 20.000 baris.
Tabel standar, juga dikenal sebagai tabel partisi, memanfaatkan kemampuan kueri sistem terdistribusi untuk meningkatkan kinerja kueri. Tabel standar dapat menyimpan data dalam jumlah besar, mulai dari puluhan juta hingga ratusan miliar baris.

Pilih kunci distribusi

Untuk mengimpor data inkremental, tentukan kunci distribusi dan kunci partisi saat membuat tabel standar. Hal ini memungkinkan sinkronisasi data inkremental. Saat membuat tabel, gunakan klausa DISTRIBUTED BY HASH(column_name,...) untuk menentukan kunci distribusi. Tabel kemudian di-shard berdasarkan nilai hash dari bidang column_name. Untuk informasi lebih lanjut, lihat CREATE TABLE.

Sintaks
```
DISTRIBUTED BY HASH(column_name,...)
```
Catatan penggunaan
- Pilih bidang yang nilainya tersebar merata sebagai kunci distribusi, seperti ID transaksi, ID perangkat, ID pengguna, atau kolom auto-increment.
  
  Catatan
  Jangan memilih bidang bertipe DATE, TIME, atau TIMESTAMP sebagai kunci distribusi. Bidang-bidang tersebut dapat menyebabkan kesenjangan data selama penulisan data dan menurunkan kinerja penulisan. Sebagian besar kueri dibatasi pada rentang waktu tertentu, misalnya hari atau bulan terakhir. Dalam kasus ini, data yang akan dikueri mungkin hanya ada di satu node saja, sehingga Anda tidak dapat memanfaatkan kemampuan pemrosesan semua node dalam database terdistribusi. Disarankan menggunakan bidang bertipe DATE atau TIME sebagai kunci subpartisi. Untuk informasi lebih lanjut, lihat Pilih kunci partisi.
- Untuk mengurangi data shuffle, pilih bidang yang digunakan untuk menggabungkan tabel (join) sebagai kunci distribusi. Misalnya, jika Anda ingin mengkueri pesanan historis berdasarkan pelanggan, pilih bidang customer_id sebagai kunci distribusi.
- Pilih bidang yang sering muncul dalam kondisi kueri sebagai kunci distribusi. Hal ini memungkinkan pemangkasan partisi berdasarkan kunci distribusi.
- Setiap tabel hanya dapat memiliki satu kunci distribusi. Kunci distribusi dapat terdiri dari satu atau beberapa bidang. Pilih sesedikit mungkin bidang agar kunci distribusi lebih fleksibel untuk berbagai kueri kompleks.
- Jika Anda tidak menentukan kunci distribusi saat membuat tabel, sistem akan menanganinya sebagai berikut:
  - Jika tabel memiliki kunci primer, AnalyticDB for MySQL menggunakan kunci primer sebagai kunci distribusi default.
  - Jika tabel tidak memiliki kunci primer, AnalyticDB for MySQL menambahkan bidang __adb_auto_id__ dan menggunakannya sebagai kunci primer sekaligus kunci distribusi.

Pilih kunci partisi

Jika satu shard berisi data dalam jumlah besar setelah Anda menentukan kunci distribusi, Anda dapat mempartisi lebih lanjut shard tersebut menggunakan kunci partisi. Anda juga dapat menyertakan kondisi filter untuk bidang subpartisi dalam klausa WHERE pernyataan kueri untuk memicu pemangkasan partisi, yang secara signifikan mengurangi jumlah data yang dipindai dan meningkatkan kinerja akses data. Saat membuat tabel, gunakan klausa PARTITION BY untuk menentukan subpartisi. Data kemudian dibagi sesuai spesifikasi. Untuk informasi lebih lanjut, lihat CREATE TABLE.

Sintaks
- Partisi tabel menggunakan nilai bidang column_name. Sintaksnya adalah sebagai berikut:
```
PARTITION BY VALUE(column_name)
```
- Partisi tabel menggunakan nilai bidang column_name yang dikonversi ke format tanggal %Y%m%d, seperti 20210101. Sintaksnya adalah sebagai berikut:
```
PARTITION BY VALUE{(DATE_FORMAT(column_name, '%Y%m%d'))|(FROM_UNIXTIME(column_name, '%Y%m%d'))}
```
- Partisi tabel menggunakan nilai bidang column_name yang dikonversi ke format tanggal %Y%m, seperti 202101. Sintaksnya adalah sebagai berikut:
```
PARTITION BY VALUE{(DATE_FORMAT(column_name, '%Y%m'))|(FROM_UNIXTIME(column_name, '%Y%m'))}
```
- Partisi tabel menggunakan nilai bidang column_name yang dikonversi ke format tanggal %Y, seperti 2021. Sintaksnya adalah sebagai berikut:
```
PARTITION BY VALUE{(DATE_FORMAT(column_name, '%Y'))|(FROM_UNIXTIME(column_name, '%Y'))}
```
Catatan penggunaan
- Ketika tabel berisi volume data besar, pemilihan subpartisi sangat penting. Jika tabel tidak memiliki subpartisi atau subpartisinya tidak dibagi dengan tepat, kinerja kluster AnalyticDB for MySQL dapat terganggu secara signifikan. Untuk informasi tentang cara mendiagnosis kesesuaian bidang partisi, lihat Diagnostik kewajaran bidang distribusi.
- Saat ini, partisi hanya didukung berdasarkan tahun, bulan, hari, atau nilai asli. Granularitas partisi yang terlalu besar atau terlalu kecil memengaruhi kinerja kueri dan penulisan, bahkan dapat memengaruhi stabilitas kluster AnalyticDB for MySQL.
- Usahakan agar subpartisi tetap dalam keadaan statis sebanyak mungkin. Pembaruan subpartisi secara berkala tidak disarankan. Misalnya, jika Anda memiliki skenario di mana beberapa subpartisi historis diperbarui setiap hari, pertimbangkan apakah bidang subpartisi yang digunakan sudah tepat.
- Anda dapat menggunakan kata kunci LIFECYCLE N untuk mengelola siklus hidup tabel. Partisi diurutkan, dan partisi yang melebihi N akan difilter.
  
  Penting
  Jumlah maksimum partisi yang didukung dalam setiap tabel terbatas. Oleh karena itu, data dalam tabel terpartisi tidak dapat disimpan selamanya. Untuk informasi lebih lanjut tentang batasan partisi, lihat Batasan.
  
  Jika muncul error yang menunjukkan bahwa jumlah partisi melebihi batas atas dan batas tersebut tidak dapat disesuaikan melalui konfigurasi, tingkatkan granularitas subpartisi (misalnya, ubah dari partisi harian menjadi bulanan) atau optimalkan desain kunci partisi untuk mengurangi jumlah total partisi.

Pilih kunci primer

Kunci primer berfungsi sebagai pengidentifikasi unik untuk setiap catatan. Saat membuat tabel, Anda dapat menggunakan klausa PRIMARY KEY untuk menentukan kunci primer. Untuk informasi lebih lanjut, lihat CREATE TABLE.

Sintaks
```
PRIMARY KEY (column_name,...)
```
Catatan penggunaan
- Hanya tabel yang memiliki kunci primer yang mendukung operasi pembaruan data, seperti DELETE dan UPDATE.
- Kunci primer tabel AnalyticDB for MySQL dapat berupa satu bidang atau kombinasi beberapa bidang. Untuk mencapai kinerja tabel yang lebih baik, disarankan menggunakan bidang numerik sebagai kunci primer dan meminimalkan jumlah bidang.
- Kunci primer harus mencakup kunci distribusi dan kunci partisi. Disarankan menempatkan kunci distribusi dan kunci partisi di awal kunci primer komposit. Kunci distribusi menentukan bagaimana data didistribusikan di seluruh shard, sedangkan kunci partisi membagi data berdasarkan rentang nilai dalam satu shard. Ketika kunci primer mencakup keduanya, pengoptimal kueri dapat menggunakan indeks kunci primer untuk menemukan data dalam shard dan partisi yang sesuai, sehingga memastikan kinerja kueri.

Pilih kunci indeks terkluster

Urutan logis nilai kunci dalam indeks terkluster menentukan urutan fisik baris yang sesuai dalam tabel. Saat memilih kunci indeks terkluster, pertimbangkan hal-hal berikut:

Setiap tabel hanya mendukung satu indeks terkluster. Untuk informasi tentang cara membuatnya, lihat CREATE TABLE.
Gunakan bidang yang selalu disertakan dalam kueri sebagai kunci indeks terkluster. Misalnya, dalam sistem informasi siswa di sekolah, setiap siswa hanya perlu melihat nilai akhir mereka sendiri. Dalam kasus ini, Anda dapat menentukan ID siswa sebagai indeks terkluster untuk memastikan lokalitas data dan meningkatkan kinerja kueri.
Indeks terkluster mengurutkan seluruh tabel, yang mengonsumsi sumber daya seperti CPU. Gunakan indeks terkluster secara bijak.
Ketika kueri berisi pengurutan DESC, Anda dapat menggunakan sintaks CLUSTERED KEY (col1 DESC, col2 DESC) dalam pernyataan CREATE TABLE untuk mendukung pengurutan DESC pada bidang yang sesuai secara efisien dan mengurangi overhead pengurutan tambahan selama eksekusi kueri.

Contoh

Buat tabel bernama customer yang memenuhi persyaratan berikut:

Partisi data tabel berdasarkan waktu login pelanggan (kolom login_time), dan konversi waktu login ke format tanggal %Y%m%d.
Pertahankan data hanya dari 30 partisi terakhir (siklus hidup adalah 30).
Distribusikan data berdasarkan ID pelanggan (kolom customer_id).
Tetapkan login_time, customer_id, phone_num sebagai kunci primer komposit.

Pernyataan CREATE TABLE adalah sebagai berikut:

CREATE TABLE customer (
customer_id bigint NOT NULL COMMENT 'ID Pelanggan',
customer_name varchar NOT NULL COMMENT 'Nama pelanggan',
phone_num bigint NOT NULL COMMENT 'Nomor telepon',
city_name varchar NOT NULL COMMENT 'Kota',
sex int NOT NULL COMMENT 'Jenis kelamin',
id_number varchar NOT NULL COMMENT 'Nomor KTP',
home_address varchar NOT NULL COMMENT 'Alamat rumah',
office_address varchar NOT NULL COMMENT 'Alamat kantor',
age int NOT NULL COMMENT 'Usia',
login_time timestamp NOT NULL COMMENT 'Waktu login',
PRIMARY KEY (login_time, customer_id, phone_num)
 )
DISTRIBUTED BY HASH(customer_id)
PARTITION BY VALUE(DATE_FORMAT(login_time, '%Y%m%d')) LIFECYCLE 30
COMMENT 'Tabel informasi pelanggan';

FAQ

Q: Setelah saya membuat subpartisi, bagaimana cara melihat semua subpartisi tabel beserta statistiknya?

A: Anda dapat menjalankan pernyataan SQL berikut untuk melihat semua subpartisi tabel beserta statistiknya:

SELECT partition_id, -- Nama partisi
          row_count, -- Total jumlah baris dalam partisi
          local_data_size, -- Ukuran penyimpanan lokal yang ditempati oleh partisi
          index_size, -- Ukuran indeks partisi
          pk_size, -- Ukuran indeks kunci primer partisi
          remote_data_size -- Ukuran penyimpanan remote yang ditempati oleh partisi
FROM information_schema.kepler_partitions
WHERE schema_name = '$DB'
 AND table_name ='$TABLE' 
 AND partition_id > 0;

Penting

Partisi dalam data inkremental yang belum memicu compaction tidak ditampilkan. Untuk melihat daftar real-time semua subpartisi, Anda dapat menjalankan pernyataan select distinct $partition_column from $db.$table;.

Q: Faktor apa saja yang memengaruhi jumlah shard? Apakah saya bisa mengubah jumlah shard sendiri?

A: Jumlah shard dihitung secara otomatis berdasarkan spesifikasi awal kluster saat dibuat. Anda tidak dapat mengubah jumlah shard.
Q: Apakah perubahan spesifikasi kluster memengaruhi jumlah shard?

A: Peningkatan atau penurunan spesifikasi kluster tidak memengaruhi jumlah shard.
Q: Apakah AnalyticDB for MySQL mendukung perubahan kunci distribusi atau kunci partisi?

A: Tidak. Untuk mengubah kunci distribusi atau kunci partisi, lihat ALTER TABLE.
Q: Persyaratan konsistensi apa saja yang harus dipenuhi oleh tabel dalam kelompok tabel yang sama?

A: Di AnalyticDB for MySQL, semua tabel dalam kelompok tabel yang sama harus memiliki jumlah partisi hash primer, partisi list sekunder, dan replika yang sama. Jika tidak, tabel-tabel tersebut tidak dapat ditambahkan ke kelompok tabel yang sama.