PolarDB：CREATE TABLE (Mode AUTO)

Jenis Partisi

Partisi HASH

Jenis partisi HASH dalam PolarDB-X mencakup partisi HASH dan partisi KEY. Partisi HASH dan KEY didukung oleh sintaks asli MySQL. PolarDB-X kompatibel secara sintaksis dengan sintaks partisi HASH dan KEY yang digunakan untuk membuat tabel terpartisi di MySQL untuk memberikan kemampuan manajemen partisi yang fleksibel dan kuat seperti pemisahan, penggabungan, dan migrasi serta mendukung pemisahan partisi panas dalam kunci partisi vektor. Namun, PolarDB-X merutekan data ke partisi dengan cara yang berbeda dari MySQL. PolarDB-X menentukan ulang rute data dalam partisi HASH dan KEY. Untuk informasi lebih lanjut tentang sintaks berbagai jenis partisi, lihat Jenis Partisi. Tabel berikut mencantumkan perbedaan antara partisi KEY dan partisi HASH.

Example 1-1:KEY partitioning

CREATE TABLE key_tbl(
 id bigint not null auto_increment,
 bid int,
 name varchar(30),
 birthday datetime not null,
 primary key(id)
)
PARTITION BY KEY(name, id)
PARTITIONS 8;

## Pernyataan SQL berikut memenuhi kondisi pemangkasan partisi dan hanya memindai data dalam satu partisi.
SELECT id from key_tbl where name='Jack';

Jika data dalam kolom nama tidak terdistribusi secara merata atau data tertentu sering diakses, Anda dapat menggunakan kolom kunci partisi lain dalam vektor, seperti kolom id, untuk membagi partisi demi optimalisasi kinerja. Untuk informasi lebih lanjut, lihat ALTER TABLEGROUP.

Jika kunci partisi vektor terdiri dari N kolom kunci partisi dan data dirutekan ke K (1 ≤ K ≤ N) kolom kunci partisi pertama, Anda perlu menentukan kondisi untuk pemangkasan partisi hanya berdasarkan K kolom pertama dari kunci partisi vektor dalam klausa WHERE dari pernyataan SQL.

Example 1-2: HASH partitioning

CREATE TABLE hash_tbl(
 id bigint not null auto_increment,
 bid int,
 name varchar(30),
 birthday datetime not null,
 primary key(id)
)
partition by hash(id)
partitions 8;

CREATE TABLE hash_tbl_todays(
 id bigint not null auto_increment,
 bid int,
 name varchar(30),
 birthday datetime not null,
 primary key(id)
)
PARTITION BY HASH(TO_DAYS(birthday))
PARTITIONS 8;

Fungsi partisi berikut didukung oleh PolarDB-X:

• YEAR

• MONTH

• DAYOFMONTH

• DAYOFWEEK

• DAYOFYEAR

• TO_DAYS

• TO_MONTHS

• TO_WEEKS

• TO_SECONDS

• UNIX_TIMESTAMP

• SUBSTR/SUBSTRING

Oleh karena itu, kecuali tipe data kolom kunci partisi fungsi SUBSTR atau SUBSTRING harus berupa STRING, tipe data kolom kunci partisi fungsi partisi lainnya harus berupa tipe berikut: DATE, DATETIME, dan TIMESTAMP.

Example 1-3: Extended HASH partitioning

CREATE TABLE hash_tbl2(
 id bigint not null auto_increment,
 bid int,
 name varchar(30),
 birthday datetime not null,
 primary key(id)
)
PARTITION BY HASH(name, birthday)
PARTITIONS 8;

Jika Anda menggunakan jenis partisi HASH untuk mempartisi tabel, kunci partisi vektor digunakan. Data dalam tabel dirutekan ke partisi berdasarkan nilai hash dari semua kolom kunci partisi. Anda harus menentukan kondisi untuk pemangkasan partisi berdasarkan semua kolom kunci partisi dalam klausa WHERE dari pernyataan SQL. Dalam contoh berikut, SQL1 memenuhi kondisi pemangkasan partisi tabel hash_tbl2, dan SQL2 tidak memenuhi kondisi pemangkasan partisi tabel hash_tbl2.

## Pernyataan SQL berikut SQL1 memenuhi kondisi pemangkasan partisi. Saat pernyataan dieksekusi, hanya partisi yang ditentukan yang dipindai.
SELECT id from hash_tbl2 where name='Jack' and birthday='1990-11-11';

## Pernyataan SQL berikut SQL2 tidak memenuhi kondisi pemangkasan partisi. Saat pernyataan dieksekusi, semua partisi dipindai.
SELECT id from hash_tbl2 where name='Jack';

Dalam partisi HASH, data dirutekan berdasarkan nilai hash dari semua kolom kunci partisi. Oleh karena itu, data lebih merata didistribusikan dalam partisi HASH dibandingkan dalam partisi KEY di mana data dirutekan berdasarkan nilai hash dari hanya kolom tertentu dari kunci partisi vektor. Partisi HASH tidak mendukung pemisahan partisi panas karena semua kolom kunci partisi digunakan untuk partisi data dan tidak ada kolom kunci partisi yang tersedia untuk pemisahan partisi panas.

Limits

• Batasan pada tipe data

  • Tipe bilangan bulat: BIGINT, BIGINT UNSIGNED, INT UNSIGNED, INT, MEDIUMINT, MEDIUMINT UNSIGNED, SMALLINT, SMALLINT UNSIGNED, TINYINT, dan TINYINT UNSIGNED.

  • Tipe tanggal dan waktu: DATETIME, DATE, dan TIMESTAMP.

  • Tipe string: CHAR dan VARCHAR.

• Batasan pada sintaks

  • Jika kunci partisi tabel adalah kunci partisi kolom tunggal dan tipe data kolom kunci partisi adalah DATETIME, DATE, atau TIMESTAMP, fungsi partisi tertentu didukung.

  • Jika kunci partisi tabel adalah kunci partisi vektor, fungsi partisi dan pemisahan partisi panas tidak didukung.

  • Secara default, tabel terpartisi dapat berisi hingga 8.192 partisi.

  • Secara default, kunci partisi dapat terdiri dari hingga lima kolom kunci partisi.

Data balancing

• Partisi KEY dan HASH diimplementasikan berdasarkan algoritma hashing konsisten bawaan MurmurHash3. Algoritma ini telah diuji secara luas di industri dan terbukti memiliki tabrakan data rendah dan kinerja tinggi.

• Saat menggunakan partisi KEY atau HASH, distribusi data di seluruh partisi yang berbeda menjadi seimbang ketika jumlah nilai berbeda dari kunci partisi lebih besar dari 3.000 berdasarkan algoritma MurmurHash3. Data didistribusikan secara lebih seimbang ketika kunci partisi mencakup lebih banyak nilai berbeda.

Partisi RANGE

Jenis partisi RANGE dalam PolarDB-X mencakup partisi RANGE dan partisi RANGE COLUMNS. Partisi RANGE dan RANGE COLUMNS didukung oleh sintaks MySQL asli. Untuk informasi lebih lanjut tentang sintaks berbagai jenis partisi, lihat Jenis Partisi. Tabel berikut mencantumkan perbedaan antara partisi RANGE dan partisi RANGE COLUMNS.

Example 2-1: RANGE COLUMNS partitioning

Partisi RANGE COLUMNS mendukung kunci partisi vektor tetapi tidak mendukung fungsi partisi. Misalnya, Anda dapat mengeksekusi pernyataan berikut untuk membuat tabel yang dipartisi berdasarkan rentang nilai yang ditentukan dalam kolom order_id dan order_time:

CREATE TABLE orders(
 order_id int,
 order_time datetime not null)
PARTITION BY range columns(order_id,order_time)
(
  PARTITION p1 VALUES LESS THAN (10000,'2021-01-01'),
  PARTITION p2 VALUES LESS THAN (20000,'2021-01-01'),
  PARTITION p3 VALUES LESS THAN (30000,'2021-01-01'),
  PARTITION p4 VALUES LESS THAN (40000,'2021-01-01'),
  PARTITION p5 VALUES LESS THAN (50000,'2021-01-01'),
  PARTITION p6 VALUES LESS THAN (MAXVALUE,MAXVALUE)
);

Example 2-2: RANGE partitioning

Partisi RANGE hanya mendukung kunci partisi kolom tunggal. Jika Anda ingin menggunakan kolom yang berisi nilai tanggal dan waktu sebagai kolom kunci partisi, Anda harus menggunakan ekspresi yang berisi fungsi partisi seperti YEAR, TO_DAYS, TO_SECONDS, atau MONTH untuk mengonversi nilai tanggal dan waktu dalam kolom tersebut menjadi nilai integer.

Misalnya, Anda dapat mengeksekusi pernyataan berikut untuk membuat tabel yang dipartisi berdasarkan rentang nilai dalam kolom order_time dan menyimpan data dalam partisi yang berbeda per kuartal:

CREATE TABLE orders_todays(
 id int,
 order_time datetime not null)
PARTITION BY RANGE(to_days(order_time))
(
  PARTITION p1 VALUES LESS THAN (to_days('2021-01-01')),
  PARTITION p2 VALUES LESS THAN (to_days('2021-04-01')),
  PARTITION p3 VALUES LESS THAN (to_days('2021-07-01')),
  PARTITION p4 VALUES LESS THAN (to_days('2021-10-01')),
  PARTITION p5 VALUES LESS THAN (to_days('2022-01-01')),
  PARTITION p6 VALUES LESS THAN (MAXVALUE)
);

Limits

• Batasan pada tipe data

  • Tipe bilangan bulat: BIGINT, BIGINT UNSIGNED, INT UNSIGNED, INT, MEDIUMINT, MEDIUMINT UNSIGNED, SMALLINT, SMALLINT UNSIGNED, TINYINT, dan TINYINT UNSIGNED.

  • Tipe tanggal dan waktu: DATETIME dan DATE.

  • Tipe string: CHAR dan VARCHAR.

• Batasan pada sintaks

  • Partisi RANGE COLUMNS dan partisi RANGE tidak mendukung NULL sebagai batas nilai rentang.

  • Partisi RANGE COLUMNS tidak mendukung kolom tipe TIMESTAMP sebagai kolom kunci partisi.

  • Dalam partisi RANGE, hanya kolom yang berisi nilai integer yang dapat digunakan sebagai kolom kunci partisi. Untuk menggunakan kolom tipe TIMESTAMP sebagai kolom kunci partisi, Anda harus menggunakan fungsi partisi UNIX_TIMSTAMP untuk mengonversi nilai timestamp ke timestamp dalam zona waktu yang sama.

  • Partisi RANGE tidak mendukung pemisahan partisi panas.

  • Jika nilai NULL ditentukan dalam permintaan SQL, nilai NULL dianggap sebagai nilai minimum selama rute data.

  • Secara default, tabel terpartisi dapat berisi hingga 8.192 partisi.

  • Secara default, kunci partisi dapat terdiri dari hingga lima kolom kunci partisi.

Partisi LIST

Jenis partisi LIST dalam PolarDB-X mencakup partisi LIST dan partisi LIST COLUMNS. Partisi LIST dan LIST COLUMNS didukung oleh sintaks MySQL asli. Untuk informasi lebih lanjut tentang sintaks berbagai jenis partisi, lihat Jenis Partisi. Partisi LIST dan LIST COLUMNS dalam PolarDB-X juga mendukung partisi DEFAULT. Tabel berikut mencantumkan perbedaan antara partisi LIST dan partisi LIST COLUMNS.

Example 3-1: LIST COLUMNS partitioning

Partisi LIST COLUMNS mendukung kunci partisi vektor tetapi tidak mendukung fungsi partisi. Misalnya, Anda dapat mengeksekusi pernyataan berikut untuk membuat tabel yang dipartisi berdasarkan nilai dalam kolom negara dan kota:

CREATE TABLE orders_region(
 id int,
 country varchar(64),
 city varchar(64),
 order_time datetime not null)
PARTITION BY LIST COLUMNS(country,city)
(
  PARTITION p1 VALUES IN (('China','Hangzhou'), ('China','Beijing')),
  PARTITION p2 VALUES IN (('United States','NewYork'),('United States','Chicago')),
  PARTITION p3 VALUES IN (('Russian','Moscow'))
);

Example 3-2: LIST partitioning

Partisi LIST hanya mendukung kunci partisi kolom tunggal. Jika Anda ingin menggunakan kolom yang berisi nilai tanggal dan waktu sebagai kolom kunci partisi, Anda harus menggunakan fungsi partisi seperti YEAR, MONTH, DAYOFMONTH, TO_DAYS, atau TO_SECONDS untuk mengonversi nilai timestamp dalam kolom kunci partisi menjadi integer.

Misalnya, Anda dapat mengeksekusi pernyataan berikut untuk membuat tabel yang dipartisi berdasarkan nilai dalam kolom order_time dan menyimpan data dalam partisi yang berbeda per tahun:

CREATE TABLE orders_years(
 id int,
 country varchar(64),
 city varchar(64),
 order_time datetime not null)
PARTITION BY LIST(YEAR(order_time))
(
  PARTITION p1 VALUES IN (1990,1991,1992,1993,1994,1995,1996,1997,1998,1999),
  PARTITION p2 VALUES IN (2000,2001,2002,2003,2004,2005,2006,2007,2008,2009),
  PARTITION p3 VALUES IN (2010,2011,2012,2013,2014,2015,2016,2017,2018,2019)
);

Example 3-3: LIST COLUMNS partitioning or LIST partitioning with a DEFAULT partition

PolarDB-X memungkinkan Anda membuat partisi LIST COLUMNS atau LIST dengan partisi DEFAULT. Data yang tidak didefinisikan dalam partisi umum dirutekan ke partisi DEFAULT.

CREATE TABLE orders_region(
 id int,
 country varchar(64),
 city varchar(64),
 order_time datetime not null)
PARTITION BY LIST COLUMNS(country,city)
(
  PARTITION p1 VALUES IN (('China','Hangzhou'), ('China','Beijing')),
  PARTITION p2 VALUES IN (('United States','NewYork'),('United States','Chicago')),
  PARTITION p3 VALUES IN (('Russian','Moscow')),
  PARTITION pd VALUES IN (DEFAULT)
);

CREATE TABLE orders_years(
 id int,
 country varchar(64),
 city varchar(64),
 order_time datetime not null)
PARTITION BY LIST(YEAR(order_time))
(
  PARTITION p1 VALUES IN (1990,1991,1992,1993,1994,1995,1996,1997,1998,1999),
  PARTITION p2 VALUES IN (2000,2001,2002,2003,2004,2005,2006,2007,2008,2009),
  PARTITION p3 VALUES IN (2010,2011,2012,2013,2014,2015,2016,2017,2018,2019),
  PARTITION pd VALUES IN (DEFAULT)
);

Limits

• Batasan pada tipe data

  • Tipe bilangan bulat: BIGINT, BIGINT UNSIGNED, INT UNSIGNED, INT, MEDIUMINT, MEDIUMINT UNSIGNED, SMALLINT, SMALLINT UNSIGNED, TINYINT, dan TINYINT UNSIGNED.

  • Tipe tanggal dan waktu: DATETIME dan DATE.

  • Tipe string: CHAR dan VARCHAR.

• Batasan pada sintaks

  • Partisi LIST COLUMNS tidak mendukung kolom tipe TIMESTAMP sebagai kolom kunci partisi.

  • Dalam partisi LIST, hanya kolom yang berisi nilai integer yang dapat digunakan sebagai kolom kunci partisi.

  • Partisi LIST COLUMNS dan partisi LIST tidak mendukung pemisahan partisi panas.

  • Secara default, tabel terpartisi dapat berisi hingga 8.192 partisi.

  • Secara default, kunci partisi dapat terdiri dari hingga lima kolom kunci partisi.

Partisi COHASH

Jenis partisi COHASH eksklusif untuk PolarDB-X.

Versi yang Didukung

Versi instance harus 5.4.18-17047709 atau lebih baru.

Skenario

Jenis partisi COHASH cocok untuk skenario berikut:

Tabel bisnis berisi satu atau lebih kolom serupa. Misalnya, tabel berisi kolom c1 dan c2 di mana empat karakter terakhir dari nilai kolom selalu sama. Anda ingin mempartisi tabel berdasarkan kolom-kolom tersebut dan menyertakan kondisi query ekuivalen dari kolom c1 atau c2 dalam query. Anda juga ingin merutekan kondisi tersebut ke partisi yang sama.

Untuk menggunakan jenis partisi ini dalam kasus ini, Anda harus menjaga kesamaan antara nilai kolom kunci partisi yang berbeda dalam tabel yang dipartisi.

Contoh 4-1: Gunakan fungsi partisi untuk menentukan kesamaan antara nilai kolom kunci partisi yang berbeda dalam tabel yang dipartisi COHASH

Anggaplah kolom order_id dan buyer_id dari tabel pesanan berisi empat karakter terakhir yang sama dalam baris yang sama. Jika Anda ingin mempartisi tabel pesanan berdasarkan enam karakter terakhir dalam kolom order_id dan buyer_id, dan ingin kondisi ekuivalen dari kolom dalam baris yang sama dirutekan ke partisi yang sama, eksekusi pernyataan berikut:

CREATE TABLE t_orders(
 id bigint not null auto_increment, 
 order_id bigint, 
 buyer_id bigint,
 order_time datetime not null,
 primary key(id)
) 
PARTITION BY CO_HASH(
 	RIGHT('order_id',6) /* Partisi tabel berdasarkan enam karakter terakhir dari nilai kolom c1.*/,
 	RIGHT('buyer_id',6) /* Partisi tabel berdasarkan enam karakter terakhir dari nilai kolom c2.*/
) 
PARTITIONS 8;

Contoh 4-2: Gunakan syntactic sugar RANGE_HASH untuk memigrasikan instance PolarDB-X 1.0 ke instance PolarDB-X 2.0

Anda ingin memigrasikan instance PolarDB-X 1.0 ke instance PolarDB-X 2.0. Instance sumber memiliki tabel pesanan yang menggunakan skema sharding range_hash. Tabel tersebut didefinisikan dengan sintaks DBPARTITION BY RANGE_HASH(`order_id`,`buyer_id`, 6). Definisi tabel partisi yang sesuai untuk tabel dalam database instance tujuan pada mode AUTO adalah sebagai berikut:

CREATE TABLE orders(
 id bigint not null auto_increment, 
 buyer_id bigint,
 order_id bigint,
 ...
 primary key(id)
) 
PARTITION BY RANGE_HASH(order_id, buyer_Id,6) 
PARTITIONS 8;

PolarDB-X secara otomatis mengonversi sintaks RANGE_HASH menjadi definisi partisi COHASH yang sesuai. Sebagai contoh, klausa RANGE_HASH(order_id, buyer_Id, 6) dalam pernyataan di atas dikonversi oleh PolarDB-X menjadi definisi partisi COHASH berikut:

CREATE TABLE orders(
 id bigint not null auto_increment, 
 buyer_id bigint,
 order_id bigint,
 ...
 primary key(id)
) 
PARTITION BY CO_HASH(
 	RIGHT('order_id',6) /* Partisi tabel berdasarkan enam karakter terakhir dari nilai kolom c1.*/,
 	RIGHT('buyer_id',6) /* Partisi tabel berdasarkan enam karakter terakhir dari nilai kolom c2.*/
) 
PARTITIONS 8;

Perbandingan Partisi COHASH, HASH, dan KEY

Tipe partisi COHASH serupa dengan tipe partisi HASH dan KEY. Tabel berikut menyajikan perbandingan antara tipe-tipe tersebut.

Catatan Penggunaan

• Anda harus memastikan kesamaan antara nilai kolom kunci partisi yang berbeda. Tabel partisi hanya memeriksa hasil rute. Nilai kolom kunci partisi yang berbeda dalam tabel COHASH dikelola oleh Anda. Oleh karena itu, setiap baris dalam tabel harus dirutekan ke partisi yang sama berdasarkan nilai kolom kunci partisi tersebut. Namun, kesamaan antara nilai kolom kunci partisi yang berbeda dapat terganggu. PolarDB-X hanya memvalidasi hasil rute dan tidak memeriksa kesamaannya.

  Sebagai contoh, jika empat karakter terakhir dari nilai kolom c1 dan c2 harus sama, dan c1=1001234 serta c2=1320 dirutekan ke shard 0, maka pernyataan insert (c1,c2) values (100234,1320) akan didukung. Namun, empat karakter terakhir dari nilai kolom tersebut berbeda.

• Batasan diberlakukan saat pernyataan DML digunakan untuk memodifikasi kolom kunci partisi. Nilai kolom kunci partisi dalam tabel COHASH serupa. Untuk mencegah distribusi data yang salah, PolarDB-X memberlakukan batasan berikut ketika Anda menggunakan pernyataan DML untuk memodifikasi nilai kolom kunci partisi:

  • Jika nilai kolom kunci partisi yang berbeda dalam baris yang sama pada klausa VALUES dari pernyataan INSERT atau REPLACE dirutekan ke partisi yang berbeda, nilai tersebut tidak dapat dimasukkan dan kesalahan akan dilaporkan.

  • Jika Anda ingin menggunakan klausa SET dari pernyataan UPDATE atau UPSERT untuk memodifikasi beberapa kolom kunci partisi, Anda harus memodifikasi semua kolom kunci partisi. Sebagai contoh, jika kolom c1 dan c2 adalah kolom kunci partisi, Anda dapat menggunakan pernyataan UPDATE t1 SET c1='xx',c2='yy' WHERE id=1 untuk memodifikasi nilai kolom. Jika setelah menggunakan klausa SET untuk memodifikasi beberapa kolom kunci partisi, nilai baru dari kolom tersebut dirutekan ke partisi yang berbeda, pernyataan UPDATE atau UPSERT akan dilarang dan kesalahan dilaporkan.

  • Setelah Anda menggunakan jenis partisi COHASH untuk tabel GSI, jika operasi DML seperti INSERT atau UPDATE pada tabel utama menyebabkan nilai kolom kunci partisi yang berbeda dalam baris yang sama dari tabel GSI dirutekan ke partisi yang berbeda, operasi tersebut juga dilarang dan kesalahan dilaporkan.

• Awalan kolom kunci partisi dalam tabel yang dipartisi COHASH adalah 0. Nilai kolom kunci partisi yang berbeda dalam tabel yang dipartisi COHASH cenderung serupa. Oleh karena itu, kolom-kolom tersebut harus didefinisikan menggunakan fungsi partisi seperti SUBSTR, LEFT, atau RIGHT. Setelah beberapa digit dari tipe integer dipotong, awalan 0 dihasilkan. Sebagai contoh, jika Anda menentukan bahwa empat karakter terakhir dari nilai kolom c1 dan c2 sama, dengan c1=1000034 dan c2=34, maka empat karakter terakhir dari nilai kolom c1 adalah '0034'. Setelah semua nilai asli kolom kunci partisi dalam tabel yang dipartisi COHASH yang menggunakan tipe integer dipotong, nilai asli secara otomatis dikonversi menjadi integer sesuai dengan nilai baru sebelum dirutekan. Dengan demikian, string '0034' dikonversi menjadi '34'. Selanjutnya, hash dari string '34' dihitung dan dirutekan ke partisi. Dengan cara ini, awalan dari string '0034' diproses secara otomatis.

Batasan Fungsi Partisi

• RIGHT

• LEFT

• SUBSTR

Batasan pada Tipe Data

• Tipe bilangan bulat: BIGINT, BIGINT UNSIGNED, INT, INT UNSIGNED, MEDIUMINT, MEDIUMINT UNSIGNED, SMALLINT, SMALLINT UNSIGNED, TINYINT, dan TINYINT UNSIGNED.

• Tipe titik tetap: DECIMAL. Bagian desimal dari tipe data ini harus bernilai nol.

• Tipe tanggal dan waktu: Tidak didukung.

• Tipe string: CHAR dan VARCHAR.

Batasan Sintaks

• Jika fungsi partisi digunakan pada kolom kunci partisi, beberapa fungsi partisi bersarang tidak dapat diterapkan pada kolom tersebut. Contoh: SUBSTR(SUBSTR(c1,-6),-4).

• Saat menggunakan syntactic sugar RANGE_HASH, nilai panjang terakhir tidak boleh negatif.

• Tipe semua kolom kunci partisi harus identik dalam hal berikut:

  • Charsets dan collation.

  • Definisi panjang dan presisi.

• Secara default, tabel yang dipartisi dapat memiliki hingga 8.192 partisi.

• Secara default, kunci partisi dapat mencakup hingga lima kolom kunci partisi.