All Products
Search

This Product

    PolarDB:CREATE TABLE (mode DRDS)

    Document Center

    PolarDB:CREATE TABLE (mode DRDS)

    Last Updated:Mar 28, 2026

    Dalam mode DRDS, PolarDB-X memperluas pernyataan standar MySQL CREATE TABLE dengan klausa untuk sharding database, sharding tabel, indeks sekunder global (GSIs), tabel shadow, dan lokasi penyimpanan. Topik ini mencakup sintaksis yang diperluas dan menyediakan contoh untuk setiap jenis tabel.

    Edisi Standar tidak mendukung sintaksis sharding database dan tabel.

    Sintaksis

    CREATE [SHADOW] TABLE [IF NOT EXISTS] tbl_name
    (create_definition, ...)
    [table_options]
    [drds_partition_options]

create_definition:
    col_name column_definition
  | mysql_create_definition
  | [UNIQUE] GLOBAL INDEX index_name [index_type] (index_sharding_col_name,...)
      [global_secondary_index_option]
      [index_option] ...

-- Opsi GSI
global_secondary_index_option:
    [COVERING (col_name,...)]
    [drds_partition_options]
    [VISIBLE|INVISIBLE]

-- Klausa sharding database dan tabel
drds_partition_options:
    DBPARTITION BY db_sharding_algorithm
    [TBPARTITION BY table_sharding_algorithm [TBPARTITIONS num]]
    [LOCALITY=locality_option]

db_sharding_algorithm:
    HASH([col_name])
  | {YYYYMM|YYYYWEEK|YYYYDD|YYYYMM_OPT|YYYYWEEK_OPT|YYYYDD_OPT}(col_name)
  | UNI_HASH(col_name)
  | RIGHT_SHIFT(col_name, n)
  | RANGE_HASH(col_name, col_name, n)

table_sharding_algorithm:
    HASH(col_name)
  | {MM|DD|WEEK|MMDD|YYYYMM|YYYYWEEK|YYYYDD|YYYYMM_OPT|YYYYWEEK_OPT|YYYYDD_OPT}(col_name)
  | UNI_HASH(col_name)
  | RIGHT_SHIFT(col_name, n)
  | RANGE_HASH(col_name, col_name, n)

-- Lokasi penyimpanan untuk tabel non-sharded
locality_option:
    'dn=storage_inst_id_list'

storage_inst_id_list:
    storage_inst_id[,storage_inst_id_list]

-- Sintaksis DDL MySQL
index_sharding_col_name:
    col_name [(length)] [ASC | DESC]

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'

index_type:
    USING {BTREE | HASH}
    Sintaksis DDL PolarDB-X didasarkan pada sintaksis MySQL. Topik ini hanya menjelaskan ekstensi dan perbedaan. Untuk sintaksis DDL MySQL lengkap, lihat dokumentasi MySQL.

    Perbedaan dari MySQL

    PolarDB-X dalam mode DRDS memperkenalkan ekstensi berikut yang tidak ada dalam MySQL standar:

    EkstensiDeskripsi
    DBPARTITION BYMengarahkan baris ke shard database fisik yang berbeda
    TBPARTITION BYMemecah lebih lanjut baris di dalam setiap shard database
    TBPARTITIONS numMenetapkan jumlah tabel fisik per shard database
    GLOBAL INDEXMendefinisikan indeks sekunder global (GSI) di seluruh semua shard
    UNIQUE GLOBAL INDEXMendefinisikan GSI dengan penerapan keunikan di seluruh semua shard
    COVERING (col_name,...)Menentukan kolom tambahan yang disimpan di tabel indeks GSI
    LOCALITYMenempelkan tabel non-sharded ke node data tertentu
    SHADOWMembuat tabel shadow untuk uji stres end-to-end
    Edisi Standar tidak mendukung DBPARTITION BY atau TBPARTITION BY.

    Klausa dan parameter sharding

    KlausaDeskripsiContoh
    DBPARTITION BY hash(col)Membagi data ke shard database menggunakan hash dari kolom yang ditentukanDBPARTITION BY hash(id)
    TBPARTITION BY hash(col)Opsional. Membagi data di dalam setiap shard database menggunakan hash dari kolom yang ditentukan. Jika dihilangkan, secara default berperilaku sama seperti DBPARTITION BYTBPARTITION BY hash(bid)
    TBPARTITION BY MM(col)Membagi di dalam setiap shard database berdasarkan bulan dalam tahun (1–12)TBPARTITION BY MM(actionDate)
    TBPARTITION BY DD(col)Shard-shard dalam setiap database shard dikelompokkan berdasarkan tanggal (1–31).TBPARTITION BY DD(actionDate)
    TBPARTITION BY WEEK(col)Shard dalam setiap database shard dikelompokkan berdasarkan hari dalam seminggu (1–7)TBPARTITION BY WEEK(actionDate)
    TBPARTITION BY MMDD(col)Shard dalam setiap database di-shard berdasarkan hari dalam setahun (1–365).TBPARTITION BY MMDD(actionDate)
    TBPARTITIONS numOpsional. Jumlah tabel fisik di setiap shard database. Default: 1TBPARTITIONS 3

    Untuk daftar lengkap fungsi sharding, lihat Fungsi sharding.

    Klausa dan parameter GSI

    KlausaDeskripsi
    [UNIQUE] GLOBAL INDEX index_nameMendefinisikan GSI. UNIQUE GLOBAL INDEX menerapkan keunikan di seluruh database
    index_nameNama GSI. Tabel indeks menggunakan nama yang sama
    index_typeJenis indeks lokal pada kunci shard di tabel indeks. Lihat dokumentasi MySQL
    index_sharding_col_nameKolom indeks, yaitu kolom kunci shard dari tabel indeks
    COVERING (col_name,...)Kolom covering yang disimpan di tabel indeks selain kolom indeks. Secara default, kolom kunci primer dan kolom kunci shard dari tabel dasar sudah termasuk
    drds_partition_optionsKlausa sharding untuk tabel indeks, menggunakan sintaksis yang sama seperti tabel dasar
    index_optionAtribut indeks lokal pada kunci shard. Lihat dokumentasi MySQL

    Untuk informasi lebih lanjut tentang GSI, lihat Cara menggunakan indeks sekunder global.

    Klausa SHADOW

    SHADOW membuat tabel shadow untuk uji stres end-to-end. Nama tabel shadow harus mengikuti aturan berikut:

    • Nama harus menggunakan _test_ sebagai awalan.

    • Bagian nama setelah awalan harus persis sama dengan nama tabel formal terkait.

    • Tabel formal harus sudah ada sebelum Anda membuat tabel shadow.

    Klausa LOCALITY

    LOCALITY menempatkan tabel non-sharded ke node data tertentu saat pembuatan.

    LOCALITY='dn=storage_inst_id_list'

    Catatan penggunaan:

    • Setelah menentukan lokasi penyimpanan dengan LOCALITY, Anda tidak dapat mengubahnya.

    • Lokasi penyimpanan tabel non-sharded bersifat independen dari lokasi penyimpanan database.

    • Jika Anda tidak menentukan LOCALITY saat membuat tabel non-sharded di instans PolarDB-X, tabel tersebut ditempatkan di node data acak. Tabel non-sharded berikutnya tanpa lokasi yang ditentukan akan ditempatkan di node data yang sama.

    Contoh

    Contoh berikut mengasumsikan instans PolarDB-X dengan delapan shard database.

    Buat tabel non-sharded

    Tabel non-sharded adalah tabel logis yang disimpan di satu lokasi fisik. Gunakan ini ketika Anda tidak perlu mendistribusikan data ke beberapa shard.

    CREATE TABLE single_tbl(
  id bigint not null auto_increment,
  name varchar(30),
  primary key(id)
);

    Verifikasi penempatan tabel dengan SHOW TOPOLOGY:

    SHOW TOPOLOGY FROM single_tbl;
    +------+------------------------------------------------------------------+------------+
| ID   | GROUP_NAME                                                       | TABLE_NAME |
+------+------------------------------------------------------------------+------------+
|    0 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | single_tbl |
+------+------------------------------------------------------------------+------------+
1 row in set (0.01 sec)

    Hasil menunjukkan tabel berada di satu shard database (database 0).

    Untuk menempatkan tabel ke node data tertentu, tentukan LOCALITY saat pembuatan. Contoh berikut membuat tabel di db1 dan menyimpannya di polardbx-storage-0-master alih-alih node default database db1:

    CREATE TABLE tb1 (id int) LOCALITY='dn=polardbx-storage-0-master';

    Verifikasi penempatan:

    SHOW TOPOLOGY FROM tb1;
    +----+------------------+------------+
| ID | GROUP_NAME       | TABLE_NAME |
+----+------------------+------------+
| 0  | DB1_000000_GROUP | tb1        |
+----+------------------+------------+
1 row in set
    Shard DB1_000000_GROUP mengonfirmasi bahwa tabel disimpan di node data yang ditentukan.

    Buat tabel dengan sharding database saja

    Sharding database mendistribusikan data ke beberapa shard database tanpa pemecahan lebih lanjut di dalam setiap shard. Gunakan ini ketika distribusi merata di seluruh database sudah cukup dan volume data per database masih dapat dikelola.

    CREATE TABLE multi_db_single_tbl(
  id bigint not null auto_increment,
  name varchar(30),
  primary key(id)
) DBPARTITION BY hash(id);

    Verifikasi topologi untuk memastikan satu tabel fisik per shard database:

    SHOW TOPOLOGY FROM multi_db_single_tbl;
    +------+------------------------------------------------------------------+---------------------+
| ID   | GROUP_NAME                                                       | TABLE_NAME          |
+------+------------------------------------------------------------------+---------------------+
|    0 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | multi_db_single_tbl |
|    1 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | multi_db_single_tbl |
|    2 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0002_RDS | multi_db_single_tbl |
|    3 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0003_RDS | multi_db_single_tbl |
|    4 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0004_RDS | multi_db_single_tbl |
|    5 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0005_RDS | multi_db_single_tbl |
|    6 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0006_RDS | multi_db_single_tbl |
|    7 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | multi_db_single_tbl |
+------+------------------------------------------------------------------+---------------------+
8 rows in set (0.01 sec)

    Buat tabel dengan sharding database dan sharding tabel

    Tambahkan TBPARTITION BY dan TBPARTITIONS ketika Anda perlu memecah data lebih lanjut di dalam setiap shard database. PolarDB-X mendukung sharding tabel berbasis hash, hash dua kolom, dan berbasis tanggal.

    Sharding berbasis hash

    Gunakan sharding berbasis hash untuk mendistribusikan baris secara merata ketika tidak ada pola akses berbasis waktu. Contoh berikut membagi data ke delapan shard database berdasarkan id, lalu membagi setiap shard menjadi tiga tabel fisik berdasarkan bid:

    CREATE TABLE multi_db_multi_tbl(
  id bigint not null auto_increment,
  bid int,
  name varchar(30),
  primary key(id)
) DBPARTITION BY hash(id) TBPARTITION BY hash(bid) TBPARTITIONS 3;

    Verifikasi topologi—tiga tabel fisik per shard database, total 24 tabel:

    SHOW TOPOLOGY FROM multi_db_multi_tbl;
    +------+------------------------------------------------------------------+-----------------------+
| ID   | GROUP_NAME                                                       | TABLE_NAME            |
+------+------------------------------------------------------------------+-----------------------+
|    0 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | multi_db_multi_tbl_00 |
|    1 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | multi_db_multi_tbl_01 |
|    2 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | multi_db_multi_tbl_02 |
|    3 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | multi_db_multi_tbl_03 |
|    4 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | multi_db_multi_tbl_04 |
|    5 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | multi_db_multi_tbl_05 |
|    6 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0002_RDS | multi_db_multi_tbl_06 |
|    7 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0002_RDS | multi_db_multi_tbl_07 |
|    8 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0002_RDS | multi_db_multi_tbl_08 |
|    9 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0003_RDS | multi_db_multi_tbl_09 |
|   10 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0003_RDS | multi_db_multi_tbl_10 |
|   11 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0003_RDS | multi_db_multi_tbl_11 |
|   12 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0004_RDS | multi_db_multi_tbl_12 |
|   13 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0004_RDS | multi_db_multi_tbl_13 |
|   14 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0004_RDS | multi_db_multi_tbl_14 |
|   15 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0005_RDS | multi_db_multi_tbl_15 |
|   16 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0005_RDS | multi_db_multi_tbl_16 |
|   17 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0005_RDS | multi_db_multi_tbl_17 |
|   18 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0006_RDS | multi_db_multi_tbl_18 |
|   19 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0006_RDS | multi_db_multi_tbl_19 |
|   20 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0006_RDS | multi_db_multi_tbl_20 |
|   21 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | multi_db_multi_tbl_21 |
|   22 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | multi_db_multi_tbl_22 |
|   23 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | multi_db_multi_tbl_23 |
+------+------------------------------------------------------------------+-----------------------+
24 rows in set (0.01 sec)

    Verifikasi aturan sharding untuk mengonfirmasi kunci partisi database dan tabel serta kebijakannya:

    SHOW RULE FROM multi_db_multi_tbl;
    +------+--------------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+
| ID   | TABLE_NAME         | BROADCAST | DB_PARTITION_KEY | DB_PARTITION_POLICY | DB_PARTITION_COUNT | TB_PARTITION_KEY | TB_PARTITION_POLICY | TB_PARTITION_COUNT |
+------+--------------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+
|    0 | multi_db_multi_tbl |         0 | id               | hash                | 8                  | bid              | hash                | 3                  |
+------+--------------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+
1 row in set (0.01 sec)

    Anda juga dapat menggabungkan sharding database dan tabel dengan atribut tabel MySQL lainnya:

    CREATE TABLE multi_db_multi_tbl(
  id bigint not null auto_increment,
  name varchar(30),
  primary key(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 DBPARTITION BY hash(id) TBPARTITION BY hash(id) TBPARTITIONS 3;

    Sharding hash dua kolom (RANGE_HASH)

    Gunakan RANGE_HASH ketika kueri mungkin datang dengan salah satu dari dua kunci shard, dan keduanya harus diarahkan ke shard fisik yang sama. Skenario umum adalah tabel pesanan yang di-shard berdasarkan ID pembeli dan ID pesanan, di mana kueri mungkin hanya menyediakan salah satu dari keduanya.

    Perilaku routing: RANGE_HASH(COL1, COL2, N) mengambil N karakter terakhir dari COL1 dan menghitung hash-nya. Jika COL1 tidak tersedia, sistem menggunakan COL2. Kedua kolom harus bertipe karakter atau numerik.

    Batasan:

    • Dua kunci shard tidak dapat diubah setelah tabel dibuat.

    • Jika dua kunci shard menghasilkan hash ke shard yang berbeda pada suatu baris, operasi insert gagal.

    CREATE TABLE test_order_tb (
  id bigint not null auto_increment,
  seller_id varchar(30) DEFAULT NULL,
  order_id varchar(30) DEFAULT NULL,
  buyer_id varchar(30) DEFAULT NULL,
  create_time datetime DEFAULT NULL,
  primary key(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
  DBPARTITION BY RANGE_HASH(buyer_id, order_id, 10)
  TBPARTITION BY RANGE_HASH(buyer_id, order_id, 10) TBPARTITIONS 3;

    Sharding berbasis tanggal

    Gunakan fungsi tanggal untuk data time-series di mana kueri hampir selalu dibatasi pada periode waktu tertentu. Setiap fungsi memetakan kolom tanggal ke tabel fisik berdasarkan unit kalender, sehingga kueri yang menentukan kolom tanggal hanya mengenai shard yang relevan.

    WEEK — shard berdasarkan hari dalam minggu (7 tabel per shard database)

    WEEK(actionDate) mengembalikan nilai DAY_OF_WEEK (1–7). Misalnya, 2017-02-27 (Senin) mengembalikan 2, sehingga baris tersebut masuk ke user_log_2. 2017-02-26 (Minggu) mengembalikan 1, sehingga baris tersebut masuk ke user_log_1.

    CREATE TABLE user_log(
  userId int,
  name varchar(30),
  operation varchar(30),
  actionDate DATE
) DBPARTITION BY hash(userId) TBPARTITION BY WEEK(actionDate) TBPARTITIONS 7;

    Verifikasi topologi—tujuh shard tabel per shard database, satu untuk setiap hari kerja:

    SHOW TOPOLOGY FROM user_log;
    +------+------------------------------------------------------------------+------------+
| ID   | GROUP_NAME                                                       | TABLE_NAME |
+------+------------------------------------------------------------------+------------+
|    0 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log_0 |
|    1 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log_1 |
|    2 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log_2 |
|    3 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log_3 |
|    4 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log_4 |
|    5 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log_5 |
|    6 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log_6 |
|    7 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | user_log_0 |
|    8 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | user_log_1 |
|    9 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | user_log_2 |
|   10 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | user_log_3 |
|   11 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | user_log_4 |
|   12 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | user_log_5 |
|   13 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | user_log_6 |
...
|   49 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log_0 |
|   50 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log_1 |
|   51 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log_2 |
|   52 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log_3 |
|   53 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log_4 |
|   54 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log_5 |
|   55 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log_6 |
+------+------------------------------------------------------------------+------------+
56 rows in set (0.01 sec)
    Tanda elipsis (...) digunakan untuk menghilangkan beberapa baris karena outputnya panjang.

    Verifikasi aturan sharding:

    SHOW RULE FROM user_log;
    +------+------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+
| ID   | TABLE_NAME | BROADCAST | DB_PARTITION_KEY | DB_PARTITION_POLICY | DB_PARTITION_COUNT | TB_PARTITION_KEY | TB_PARTITION_POLICY | TB_PARTITION_COUNT |
+------+------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+
|    0 | user_log   |         0 | userId           | hash                | 8                  | actionDate       | week                | 7                  |
+------+------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+
1 row in set (0.00 sec)

    Dengan kunci shard database dan kunci shard tabel yang keduanya didefinisikan, kueri yang menentukan kedua kolom tersebut diarahkan langsung ke tabel fisik yang sesuai.

    MM — shard berdasarkan bulan dalam tahun (12 tabel per shard database)

    MM(actionDate) mengembalikan nilai MONTH_OF_YEAR (01–12). Misalnya, 2017-02-27 mengembalikan 02, sehingga baris tersebut masuk ke user_log2_02. 2016-12-27 mengembalikan 12, dan karena 12 % 12 = 0, baris tersebut masuk ke user_log2_00.

    CREATE TABLE user_log2(
  userId int,
  name varchar(30),
  operation varchar(30),
  actionDate DATE
) DBPARTITION BY hash(userId) TBPARTITION BY MM(actionDate) TBPARTITIONS 12;

    Verifikasi topologi—12 shard tabel per shard database:

    SHOW TOPOLOGY FROM user_log2;
    +------+------------------------------------------------------------------+--------------+
| ID   | GROUP_NAME                                                       | TABLE_NAME   |
+------+------------------------------------------------------------------+--------------+
|    0 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log2_00 |
|    1 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log2_01 |
|    2 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log2_02 |
|    3 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log2_03 |
|    4 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log2_04 |
|    5 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log2_05 |
|    6 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log2_06 |
|    7 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log2_07 |
|    8 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log2_08 |
|    9 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log2_09 |
|   10 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log2_10 |
|   11 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log2_11 |
|   12 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | user_log2_00 |
|   13 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | user_log2_01 |
|   14 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | user_log2_02 |
|   15 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | user_log2_03 |
|   16 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | user_log2_04 |
|   17 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | user_log2_05 |
|   18 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | user_log2_06 |
|   19 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | user_log2_07 |
|   20 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | user_log2_08 |
|   21 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | user_log2_09 |
|   22 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | user_log2_10 |
|   23 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0001_RDS | user_log2_11 |
...
|   84 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log2_00 |
|   85 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log2_01 |
|   86 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log2_02 |
|   87 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log2_03 |
|   88 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log2_04 |
|   89 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log2_05 |
|   90 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log2_06 |
|   91 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log2_07 |
|   92 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log2_08 |
|   93 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log2_09 |
|   94 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log2_10 |
|   95 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log2_11 |
+------+------------------------------------------------------------------+--------------+
96 rows in set (0.02 sec)
    Tanda elipsis (...) digunakan untuk menghilangkan beberapa baris karena outputnya panjang.

    Verifikasi aturan sharding:

    SHOW RULE FROM user_log2;
    +------+------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+
| ID   | TABLE_NAME | BROADCAST | DB_PARTITION_KEY | DB_PARTITION_POLICY | DB_PARTITION_COUNT | TB_PARTITION_KEY | TB_PARTITION_POLICY | TB_PARTITION_COUNT |
+------+------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+
|    0 | user_log2  |         0 | userId           | hash                | 8                  | actionDate       | mm                  | 12                 |
+------+------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+
1 row in set (0.00 sec)

    DD — shard berdasarkan hari dalam bulan (31 tabel per shard database)

    DD(actionDate) mengembalikan nilai DAY_OF_MONTH (1–31). Misalnya, 2017-02-27 mengembalikan 27, sehingga baris tersebut masuk ke user_log_27.

    CREATE TABLE user_log3(
  userId int,
  name varchar(30),
  operation varchar(30),
  actionDate DATE
) DBPARTITION BY hash(userId) TBPARTITION BY DD(actionDate) TBPARTITIONS 31;

    Verifikasi topologi—31 shard tabel per shard database:

    SHOW TOPOLOGY FROM user_log3;
    +------+------------------------------------------------------------------+--------------+
| ID   | GROUP_NAME                                                       | TABLE_NAME   |
+------+------------------------------------------------------------------+--------------+
|    0 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_00 |
|    1 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_01 |
|    2 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_02 |
...
|   30 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log3_30 |
...
|  237 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log3_20 |
|  238 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log3_21 |
|  239 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log3_22 |
|  240 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log3_23 |
|  241 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log3_24 |
|  242 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log3_25 |
|  243 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log3_26 |
|  244 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log3_27 |
|  245 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log3_28 |
|  246 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log3_29 |
|  247 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log3_30 |
+------+------------------------------------------------------------------+--------------+
248 rows in set (0.01 sec)
    Tanda elipsis (...) digunakan untuk menghilangkan beberapa baris karena outputnya panjang.

    Verifikasi aturan sharding:

    SHOW RULE FROM user_log3;
    +------+------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+
| ID   | TABLE_NAME | BROADCAST | DB_PARTITION_KEY | DB_PARTITION_POLICY | DB_PARTITION_COUNT | TB_PARTITION_KEY | TB_PARTITION_POLICY | TB_PARTITION_COUNT |
+------+------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+
|    0 | user_log3  |         0 | userId           | hash                | 8                  | actionDate       | dd                  | 31                 |
+------+------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+
1 row in set (0.01 sec)

    MMDD — shard berdasarkan hari dalam tahun (maksimal 365 tabel per shard database)

    MMDD(actionDate) menghitung DAY_OF_YEAR % 365. Misalnya, 2017-02-27 mengembalikan 58, sehingga baris tersebut masuk ke user_log_58. Gunakan TBPARTITIONS 365 untuk granularitas maksimum, atau nilai yang lebih kecil untuk mengurangi jumlah total tabel.

    Contoh dengan 365 partisi (satu per hari dalam tahun):

    CREATE TABLE user_log4(
  userId int,
  name varchar(30),
  operation varchar(30),
  actionDate DATE
) DBPARTITION BY hash(userId) TBPARTITION BY MMDD(actionDate) TBPARTITIONS 365;

    Verifikasi topologi—365 shard tabel per shard database, total 2.920:

    SHOW TOPOLOGY FROM user_log4;
    +------+------------------------------------------------------------------+---------------+
| ID   | GROUP_NAME                                                       | TABLE_NAME    |
+------+------------------------------------------------------------------+---------------+
...
| 2896 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_341 |
| 2897 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_342 |
| 2898 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_343 |
| 2899 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_344 |
| 2900 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_345 |
| 2901 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_346 |
| 2902 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_347 |
| 2903 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_348 |
| 2904 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_349 |
| 2905 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_350 |
| 2906 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_351 |
| 2907 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_352 |
| 2908 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_353 |
| 2909 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_354 |
| 2910 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_355 |
| 2911 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_356 |
| 2912 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_357 |
| 2913 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_358 |
| 2914 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_359 |
| 2915 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_360 |
| 2916 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_361 |
| 2917 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_362 |
| 2918 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_363 |
| 2919 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log4_364 |
+------+------------------------------------------------------------------+---------------+
2920 rows in set (0.07 sec)
    Tanda elipsis (...) digunakan untuk menghilangkan beberapa baris karena outputnya panjang.

    Verifikasi aturan sharding:

    SHOW RULE FROM user_log4;
    +------+------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+
| ID   | TABLE_NAME | BROADCAST | DB_PARTITION_KEY | DB_PARTITION_POLICY | DB_PARTITION_COUNT | TB_PARTITION_KEY | TB_PARTITION_POLICY | TB_PARTITION_COUNT |
+------+------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+
|    0 | user_log4  |         0 | userId           | hash                | 8                  | actionDate       | mmdd                | 365                |
+------+------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+
1 row in set (0.02 sec)

    Contoh dengan 10 partisi (mengarahkan 365 hari ke 10 bucket menggunakan modulo):

    CREATE TABLE user_log5(
  userId int,
  name varchar(30),
  operation varchar(30),
  actionDate DATE
) DBPARTITION BY hash(userId) TBPARTITION BY MMDD(actionDate) TBPARTITIONS 10;

    Verifikasi topologi—10 shard tabel per shard database, total 80:

    SHOW TOPOLOGY FROM user_log5;
    +------+------------------------------------------------------------------+--------------+
| ID   | GROUP_NAME                                                       | TABLE_NAME   |
+------+------------------------------------------------------------------+--------------+
|    0 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log5_00 |
|    1 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log5_01 |
|    2 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log5_02 |
|    3 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log5_03 |
|    4 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log5_04 |
|    5 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log5_05 |
|    6 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log5_06 |
|    7 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log5_07 |
|    8 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log5_08 |
|    9 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0000_RDS | user_log5_09 |
...
|   70 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log5_00 |
|   71 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log5_01 |
|   72 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log5_02 |
|   73 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log5_03 |
|   74 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log5_04 |
|   75 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log5_05 |
|   76 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log5_06 |
|   77 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log5_07 |
|   78 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log5_08 |
|   79 | SANGUAN_TEST_123_1488766060743ACTJSANGUAN_TEST_123_WVVP_0007_RDS | user_log5_09 |
+------+------------------------------------------------------------------+--------------+
80 rows in set (0.02 sec)
    Tanda elipsis (...) digunakan untuk menghilangkan beberapa baris karena outputnya panjang.

    Verifikasi aturan sharding:

    SHOW RULE FROM user_log5;
    +------+------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+
| ID   | TABLE_NAME | BROADCAST | DB_PARTITION_KEY | DB_PARTITION_POLICY | DB_PARTITION_COUNT | TB_PARTITION_KEY | TB_PARTITION_POLICY | TB_PARTITION_COUNT |
+------+------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+
|    0 | user_log5  |         0 | userId           | hash                | 8                  | actionDate       | mmdd                | 10                 |
+------+------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+
1 row in set (0.01 sec)

    Definisikan GSI

    Indeks sekunder global (GSI) memelihara tabel indeks terpisah yang di-shard berdasarkan kunci yang berbeda dari tabel dasar, memungkinkan kueri efisien pada kolom non-kunci-shard tanpa pemindaian scatter-gather penuh. Contoh berikut mengasumsikan delapan shard database.

    Definisikan GSI non-unik

    Contoh berikut membuat t_order, di-shard berdasarkan order_id, dengan GSI g_i_seller di-shard berdasarkan seller_id. Hal ini memungkinkan kueri yang difilter berdasarkan seller_id menghindari pemindaian penuh di seluruh semua shard.

    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`),
  GLOBAL INDEX `g_i_seller`(`seller_id`) DBPARTITION BY hash(`seller_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 DBPARTITION BY hash(`order_id`);

    • t_order adalah tabel dasar, di-shard berdasarkan order_id.

    • g_i_seller adalah tabel indeks, di-shard berdasarkan seller_id. Tidak ada kolom covering yang ditentukan selain default.

    • Klausa GLOBAL INDEX g_i_seller(seller_id) DBPARTITION BY hash(seller_id) mendefinisikan GSI.

    Verifikasi indeks—kolom covering default adalah kunci primer (id) dan kunci shard tabel dasar (order_id):

    SHOW INDEX FROM t_order;
    +---------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----------+---------------+
| TABLE   | NON_UNIQUE | KEY_NAME                | SEQ_IN_INDEX | COLUMN_NAME | COLLATION | CARDINALITY | SUB_PART | PACKED | NULL | INDEX_TYPE | COMMENT  | INDEX_COMMENT |
+---------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----------+---------------+
| t_order |          0 | PRIMARY                 |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |          |               |
| t_order |          1 | auto_shard_key_order_id |            1 | order_id    | A         |           0 |     NULL | NULL   | YES  | BTREE      |          |               |
| t_order |          1 | g_i_seller              |            1 | seller_id   | NULL      |           0 |     NULL | NULL   | YES  | GLOBAL     | INDEX    |               |
| t_order |          1 | g_i_seller              |            2 | id          | NULL      |           0 |     NULL | NULL   |      | GLOBAL     | COVERING |               |
| t_order |          1 | g_i_seller              |            3 | order_id    | NULL      |           0 |     NULL | NULL   | YES  | GLOBAL     | COVERING |               |
+---------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----------+---------------+

    Kueri metadata GSI dengan SHOW GLOBAL INDEX:

    SHOW GLOBAL INDEX FROM t_order;
    +--------+---------+------------+------------+-------------+----------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+
| SCHEMA | TABLE   | NON_UNIQUE | KEY_NAME   | INDEX_NAMES | COVERING_NAMES | INDEX_TYPE | DB_PARTITION_KEY | DB_PARTITION_POLICY | DB_PARTITION_COUNT | TB_PARTITION_KEY | TB_PARTITION_POLICY | TB_PARTITION_COUNT | STATUS |
+--------+---------+------------+------------+-------------+----------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+
| d7     | t_order | 1          | g_i_seller | seller_id   | id, order_id   | NULL       | seller_id        | HASH                 | 8                  |                  | NULL                | NULL               | PUBLIC |
+--------+---------+------------+------------+-------------+----------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+

    Periksa skema tabel indeks. Tabel indeks berisi kunci primer, kunci shard, dan kolom covering default. Kolom kunci primer tidak memiliki AUTO_INCREMENT, dan tabel dasar tidak memiliki indeks lokal.

    SHOW CREATE TABLE g_i_seller;
    +------------+-----------------------------------------------------------+
| Table      | Create Table                                              |
+------------+-----------------------------------------------------------+
| g_i_seller | CREATE TABLE `g_i_seller` (
  `id` bigint(11) NOT NULL,
  `order_id` varchar(20) DEFAULT NULL,
  `seller_id` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `auto_shard_key_seller_id` (`seller_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 DBPARTITION BY hash(`seller_id`) |
+------------+-----------------------------------------------------------+

    Definisikan GSI unik

    GSI unik menerapkan keunikan kolom yang diindeks di seluruh semua shard database. Contoh berikut menambahkan GSI unik g_i_buyer pada buyer_id, dengan seller_id dan order_snapshot sebagai kolom covering eksplisit.

    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`),
  UNIQUE GLOBAL INDEX `g_i_buyer`(`buyer_id`) COVERING(`seller_id`, `order_snapshot`)
    DBPARTITION BY hash(`buyer_id`) TBPARTITION BY hash(`buyer_id`) TBPARTITIONS 3
) ENGINE=InnoDB DEFAULT CHARSET=utf8 DBPARTITION BY hash(`order_id`);

    • t_order adalah tabel dasar, di-shard berdasarkan order_id.

    • g_i_buyer adalah tabel indeks, di-shard berdasarkan buyer_id dengan tiga tabel fisik per shard database. Kolom covering mencakup seller_id dan order_snapshot selain default.

    • Klausa UNIQUE GLOBAL INDEX g_i_buyer(buyer_id) COVERING(seller_id, order_snapshot) DBPARTITION BY hash(buyer_id) TBPARTITION BY hash(buyer_id) TBPARTITIONS 3 mendefinisikan GSI unik.

    Verifikasi indeks. Kolom covering default (id, order_id) dan kolom covering eksplisit (seller_id, order_snapshot) semuanya ada:

    SHOW INDEX FROM t_order;
    +--------------+------------+-------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+----------+---------------+
| TABLE        | NON_UNIQUE | KEY_NAME                | SEQ_IN_INDEX | COLUMN_NAME    | COLLATION | CARDINALITY | SUB_PART | PACKED | NULL | INDEX_TYPE | COMMENT  | INDEX_COMMENT |
+--------------+------------+-------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+----------+---------------+
| t_order_dthb |          0 | PRIMARY                 |            1 | id             | A         |           0 |     NULL | NULL   |      | BTREE      |          |               |
| t_order_dthb |          1 | auto_shard_key_order_id |            1 | order_id       | A         |           0 |     NULL | NULL   | YES  | BTREE      |          |               |
| t_order      |          0 | g_i_buyer               |            1 | buyer_id       | NULL      |           0 |     NULL | NULL   | YES  | GLOBAL     | INDEX    |               |
| t_order      |          1 | g_i_buyer               |            2 | id             | NULL      |           0 |     NULL | NULL   |      | GLOBAL     | COVERING |               |
| t_order      |          1 | g_i_buyer               |            3 | order_id       | NULL      |           0 |     NULL | NULL   | YES  | GLOBAL     | COVERING |               |
| t_order      |          1 | g_i_buyer               |            4 | seller_id      | NULL      |           0 |     NULL | NULL   | YES  | GLOBAL     | COVERING |               |
| t_order      |          1 | g_i_buyer               |            5 | order_snapshot | NULL      |           0 |     NULL | NULL   | YES  | GLOBAL     | COVERING |               |
+--------------+------------+-------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+----------+---------------+

    Kueri metadata GSI:

    SHOW GLOBAL INDEX FROM t_order;
    +--------+---------+------------+-----------+-------------+-----------------------------------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+
| SCHEMA | TABLE   | NON_UNIQUE | KEY_NAME  | INDEX_NAMES | COVERING_NAMES                          | INDEX_TYPE | DB_PARTITION_KEY | DB_PARTITION_POLICY | DB_PARTITION_COUNT | TB_PARTITION_KEY | TB_PARTITION_POLICY | TB_PARTITION_COUNT | STATUS |
+--------+---------+------------+-----------+-------------+-----------------------------------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+
| d7     | t_order | 0          | g_i_buyer | buyer_id    | id, order_id, seller_id, order_snapshot | NULL       | buyer_id         | HASH                | 8                  | buyer_id         | HASH                | 3                  | PUBLIC |
+--------+---------+------------+-----------+-------------+-----------------------------------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+

    Periksa skema tabel indeks. Secara default, tabel dibuat untuk GSI unik guna memastikan nilai dalam kolom yang diindeks unik di seluruh database. Kolom kunci primer tidak memiliki AUTO_INCREMENT.

    SHOW CREATE TABLE g_i_buyer;
    +-----------+--------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                           |
+-----------+--------------------------------------------------------------------------------------------------------+
| g_i_buyer | CREATE TABLE `g_i_buyer` (
  `id` bigint(11) NOT NULL,
  `order_id` varchar(20) DEFAULT NULL,
  `buyer_id` varchar(20) DEFAULT NULL,
  `seller_id` varchar(20) DEFAULT NULL,
  `order_snapshot` longtext,
  PRIMARY KEY (`id`),
  UNIQUE KEY `auto_shard_key_buyer_id` (`buyer_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 DBPARTITION BY hash(`buyer_id`) TBPARTITION BY hash(`buyer_id`) TBPARTITIONS 3 |
+-----------+--------------------------------------------------------------------------------------------------------+

    Langkah selanjutnya