ApsaraDB for SelectDB：Langkah 3: Pelajari poin-poin kunci dalam desain database dan tabel

Gunakan model Unique

Dalam model Unique, jika kolom kunci memiliki nilai yang sama di beberapa baris, baris yang ditulis ke tabel nanti akan menimpa baris sebelumnya. Model Unique menyediakan dua metode implementasi: Merge on Read (MoR) dan Merge on Write (MoW).

Metode MoW sudah matang dan stabil serta memberikan performa query yang sangat baik. Oleh karena itu, kami merekomendasikan Anda menggunakan metode MoW dalam model Unique. Contoh berikut menggambarkan cara menggunakan metode MoW untuk mengimplementasikan model Unique. Untuk informasi lebih lanjut tentang metode MoR, lihat bagian MoR dari topik "Model Data".

CREATE TABLE IF NOT EXISTS orders
(
    `order_id` LARGEINT NOT NULL COMMENT "ID pesanan.",
    `order_time` DATETIME NOT NULL COMMENT "Waktu pesanan.",
    `customer_id` LARGEINT NOT NULL COMMENT "ID pengguna.",
    `total_amount` DOUBLE COMMENT "Jumlah total pesanan.",
    `status` VARCHAR(20) COMMENT "Status pesanan.",
    `payment_method` VARCHAR(20) COMMENT "Metode pembayaran.",
    `shipping_method` VARCHAR(20) COMMENT "Metode pengiriman.",
    `customer_city` VARCHAR(20) COMMENT "Kota tempat pengguna tinggal.",
    `customer_address` VARCHAR(500) COMMENT "Alamat pengguna."
)
UNIQUE KEY(`order_id`, `order_time`)
PARTITION BY RANGE(`order_time`) ()
DISTRIBUTED BY HASH(`order_id`)
PROPERTIES (
    "enable_unique_key_merge_on_write" = "true",
    "dynamic_partition.enable" = "true",
    "dynamic_partition.time_unit" = "DAY",
    "dynamic_partition.start" = "-7",
    "dynamic_partition.end" = "3",
    "dynamic_partition.prefix" = "p",
    "dynamic_partition.create_history_partition" = "true",
    "dynamic_partition.buckets" = "16"
);

Gunakan model Duplicate

Dalam model Duplicate, beberapa baris dengan nilai yang sama untuk kolom kunci disimpan dalam sistem pada saat yang sama. Model ini tidak mendukung pra-agregasi atau memerlukan kunci utama unik.

Misalnya, Anda dapat menggunakan model ini untuk mencatat dan menganalisis data log yang dihasilkan oleh sistem bisnis dan mengurutkan data berdasarkan waktu log, tipe log, dan kode kesalahan. Kode sampel berikut menunjukkan pernyataan SQL untuk membuat tabel log. Dalam contoh ini, model Duplicate dipilih untuk tabel log, dan data diurutkan berdasarkan bidang log_time, log_type, dan error_code.

CREATE TABLE IF NOT EXISTS log
(
    `log_time` DATETIME NOT NULL COMMENT "Waktu ketika log dihasilkan.",
    `log_type` INT NOT NULL COMMENT "Tipe log.",
    `error_code` INT COMMENT "Kode kesalahan.",
    `error_msg` VARCHAR(1024) COMMENT "Pesan kesalahan.",
    `op_id` BIGINT COMMENT "ID pemilik.",
    `op_time` DATETIME COMMENT "Waktu ketika kesalahan ditangani."
)
DUPLICATE KEY(`log_time`, `log_type`, `error_code`)
PARTITION BY RANGE(`log_time`) ()
DISTRIBUTED BY HASH(`log_type`)
PROPERTIES (
    "dynamic_partition.enable" = "true",
    "dynamic_partition.time_unit" = "DAY",
    "dynamic_partition.start" = "-7",
    "dynamic_partition.end" = "3",
    "dynamic_partition.prefix" = "p",
    "dynamic_partition.create_history_partition" = "true",
    "dynamic_partition.buckets" = "16"
);

Gunakan model Aggregate

CREATE TABLE IF NOT EXISTS user_behavior
(
    `user_id` LARGEINT NOT NULL COMMENT "ID pengguna.",
    `date` DATE NOT NULL COMMENT "Tanggal ketika data ditulis ke tabel.",
    `city` VARCHAR(20) COMMENT "Kota tempat pengguna tinggal.",
    `age` SMALLINT COMMENT "Usia pengguna.",
    `sex` TINYINT COMMENT "Jenis kelamin pengguna.",
    `last_visit_date` DATETIME REPLACE DEFAULT "1970-01-01 00:00:00" COMMENT "Terakhir kali pengguna berkunjung.",
    `cost` BIGINT SUM DEFAULT "0" COMMENT "Jumlah uang yang dibelanjakan pengguna.",
    `max_dwell_time` INT MAX DEFAULT "0" COMMENT "Waktu tinggal maksimum pengguna.",
    `min_dwell_time` INT MIN DEFAULT "99999" COMMENT "Waktu tinggal minimum pengguna."
)
AGGREGATE KEY(`user_id`, `date`, `city`, `age`, `sex`)
PARTITION BY RANGE(`date`) ()
DISTRIBUTED BY HASH(`user_id`)
PROPERTIES (
    "dynamic_partition.enable" = "true",
    "dynamic_partition.time_unit" = "DAY",
    "dynamic_partition.start" = "-7",
    "dynamic_partition.end" = "3",
    "dynamic_partition.prefix" = "p",
    "dynamic_partition.create_history_partition" = "true",
    "dynamic_partition.buckets" = "16"
);

Gunakan partisi rentang

Partisi rentang adalah metode partisi yang paling umum digunakan untuk mengelola data berdasarkan rentang bidang. Dalam skenario tipikal, sejumlah besar data deret waktu dipartisi berdasarkan waktu untuk mempermudah pengelolaan dan mengoptimalkan query.

Tujuan akhir dari partisi dan tableting adalah untuk membagi data secara masuk akal. Patuhi standar berikut saat menentukan aturan partisi:

• Jumlah data setiap tablet harus berada dalam rentang 1 GB hingga 10 GB.

• Granularitas partisi harus ditentukan berdasarkan jumlah data yang ingin Anda kelola. Misalnya, jika Anda ingin menghapus data log historis berdasarkan hari, granularitas partisi harian adalah sesuai.

Kode sampel berikut menunjukkan cara membuat tabel partisi, data di mana difilter berdasarkan rentang waktu dan data log historis dihapus berdasarkan waktu. Dalam contoh ini, bidang log_time digunakan sebagai kolom kunci partisi.

CREATE TABLE IF NOT EXISTS log
(
 `log_time` DATETIME NOT NULL COMMENT "Waktu ketika log dihasilkan.",
 `log_type` INT NOT NULL COMMENT "Tipe log.",
 `error_code` INT COMMENT "Kode kesalahan.",
 `error_msg` VARCHAR(1024) COMMENT "Pesan kesalahan.",
 `op_id` BIGINT COMMENT "ID pemilik.",
 `op_time` DATETIME COMMENT "Waktu ketika kesalahan ditangani."
)
DUPLICATE KEY(`log_time`, `log_type`, `error_code`)
PARTITION BY RANGE(`log_time`)
(
 PARTITION `p20240201` VALUES [("2024-02-01"), ("2024-02-02")),
 PARTITION `p20240202` VALUES [("2024-02-02"), ("2024-02-03")),
 PARTITION `p20240203` VALUES [("2024-02-03"), ("2024-02-04"))
)
DISTRIBUTED BY HASH(`log_type`)
PROPERTIES ();

Setelah tabel dibuat, Anda dapat menjalankan pernyataan SQL berikut untuk melihat informasi partisi tabel:

SHOW partitions FROM log;

p20240201: [("2024-02-01"), ("2024-02-02"))
p20240202: [("2024-02-02"), ("2024-02-03"))
p20240203: [("2024-02-03"), ("2024-02-04"))

Saat mengeksekusi pernyataan berikut untuk mengkueri data, partisi p20240202: [("2024-02-02"), ("2024-02-03")) diakses. Sistem tidak memindai data di dua partisi lainnya, sehingga mempercepat proses kueri.

SELECT * FROM orders WHERE order_time = '2024-02-02';

Gunakan partisi daftar

Partisi daftar membagi dan mengelola data berdasarkan nilai enumerasi kolom kunci partisi. Saat menanyakan data dari tabel partisi daftar, Anda dapat memangkas partisi sesuai dengan kondisi filter untuk meningkatkan performa query.

Anda dapat memilih kolom kunci partisi daftar berdasarkan bidang yang sering digunakan untuk mengelola data bisnis. Pastikan data didistribusikan secara merata ke partisi guna mencegah kesenjangan data yang signifikan.

Sebagai contoh, dalam skenario e-commerce terdapat sejumlah besar data pesanan. Dalam beberapa kasus, Anda mungkin ingin menanyakan dan menganalisis data pesanan berdasarkan kota tempat pengguna tinggal. Untuk mempermudah pengelolaan dan query data, Anda dapat menetapkan bidang customer_city sebagai kolom kunci partisi. Pada contoh ini, data pesanan didistribusikan di kota-kota berikut:

• Beijing, Shanghai, dan Hong Kong, Tiongkok: 6 GB

• New York dan San Francisco: 5 GB

• Tokyo: 5 GB

Kode sampel berikut menunjukkan cara membuat tabel partisi daftar untuk data pesanan:

CREATE TABLE IF NOT EXISTS orders
(
    `order_id` LARGEINT NOT NULL COMMENT "ID pesanan.",
    `order_time` DATETIME NOT NULL COMMENT "Waktu pesanan.",
    `customer_city` VARCHAR(20) COMMENT "Kota tempat pengguna tinggal.",
    `customer_id` LARGEINT NOT NULL COMMENT "ID pengguna.",
    `total_amount` DOUBLE COMMENT "Jumlah total pesanan.",
    `status` VARCHAR(20) COMMENT "Status pesanan.",
    `payment_method` VARCHAR(20) COMMENT "Metode pembayaran.",
    `shipping_method` VARCHAR(20) COMMENT "Metode pengiriman.",
    `customer_address` VARCHAR(500) COMMENT "Alamat pengguna."
)
UNIQUE KEY(`order_id`, `order_time`, `customer_city`)
PARTITION BY LIST(`customer_city`)
(
    PARTITION `p_cn` VALUES IN ("Beijing", "Shanghai", "Hong Kong"),
    PARTITION `p_usa` VALUES IN ("New York", "San Francisco"),
    PARTITION `p_jp` VALUES IN ("Tokyo")
)
DISTRIBUTED BY HASH(`order_id`) BUCKETS 16
PROPERTIES (
    "enable_unique_key_merge_on_write" = "true"
);

Setelah tabel dibuat, Anda dapat menjalankan pernyataan SQL berikut untuk melihat informasi partisi tabel. Tiga partisi secara otomatis dibuat untuk tabel tersebut:

SHOW partitions FROM orders;

p_cn: ("Beijing", "Shanghai", "Hong Kong")
p_usa: ("New York", "San Francisco")
p_jp: ("Tokyo")

Saat menjalankan pernyataan berikut untuk menanyakan data, partisi p_jp: ("Tokyo") tercapai. Sistem tidak memindai data di dua partisi lainnya, sehingga mempercepat query data.

SELECT * FROM orders WHERE customer_city = 'Tokyo';

Gunakan indeks terbalik

ApsaraDB for SelectDB mendukung indeks terbalik. Anda dapat menggunakan indeks terbalik untuk melakukan pencarian teks penuh pada data tipe TEXT dan menjalankan query ekuivalen atau rentang pada bidang biasa. Dengan cara ini, Anda dapat dengan cepat mengambil data yang memenuhi kondisi tertentu dari sejumlah besar data. Bagian ini menjelaskan cara membuat indeks terbalik. Untuk informasi lebih lanjut, lihat Indeks Terbalik.

Buat indeks terbalik saat Anda membuat tabel

Dalam skenario e-commerce, Anda sering perlu menanyakan informasi pesanan berdasarkan kata kunci seperti ID pengguna dan alamat pengguna. Dalam hal ini, Anda dapat membuat indeks terbalik pada bidang customer_id dan customer_address untuk mempercepat query. Kode sampel berikut menunjukkan cara membuat tabel untuk skenario seperti itu:

CREATE TABLE IF NOT EXISTS orders
(
    `order_id` LARGEINT NOT NULL COMMENT "ID pesanan.",
    `order_time` DATETIME NOT NULL COMMENT "Waktu pesanan.",
    `customer_id` LARGEINT NOT NULL COMMENT "ID pengguna.",
    `total_amount` DOUBLE COMMENT "Jumlah total pesanan.",
    `status` VARCHAR(20) COMMENT "Status pesanan.",
    `payment_method` VARCHAR(20) COMMENT "Metode pembayaran.",
    `shipping_method` VARCHAR(20) COMMENT "Metode pengiriman.",
    `customer_city` VARCHAR(20) COMMENT "Kota tempat pengguna tinggal.",
    `customer_address` VARCHAR(500) COMMENT "Alamat pengguna.",
    INDEX idx_customer_id (`customer_id`) USING INVERTED,
    INDEX idx_customer_address (`customer_address`) USING INVERTED PROPERTIES("parser" = "chinese")
)
UNIQUE KEY(`order_id`, `order_time`)
PARTITION BY RANGE(`order_time`) ()
DISTRIBUTED BY HASH(`order_id`)
PROPERTIES (
    "enable_unique_key_merge_on_write" = "true",
    "dynamic_partition.enable" = "true",
    "dynamic_partition.time_unit" = "DAY",
    "dynamic_partition.start" = "-7",
    "dynamic_partition.end" = "3",
    "dynamic_partition.prefix" = "p",
    "dynamic_partition.create_history_partition" = "true",
    "dynamic_partition.buckets" = "16"
);

Buat indeks terbalik pada kolom dalam tabel yang sudah ada

Dalam skenario e-commerce, Anda sering perlu menanyakan informasi pesanan berdasarkan ID pengguna. Namun, tidak ada indeks terbalik yang dibuat pada bidang customer_id saat Anda membuat tabel orders. Dalam hal ini, Anda dapat menjalankan pernyataan berikut untuk menambahkan indeks ke tabel:

ALTER TABLE orders ADD INDEX idx_customer_id (`customer_id`) USING INVERTED;

Gunakan indeks awalan

Indeks awalan dibuat pada satu atau lebih kolom kunci dalam data dasar yang diurutkan berdasarkan kolom kunci. Pada dasarnya, pengindeksan awalan adalah pencarian biner berdasarkan fitur pengurutan data. Indeks awalan adalah indeks bawaan yang secara otomatis dibuat oleh SelectDB setelah tabel dibuat.

Tidak ada sintaks khusus yang tersedia untuk mendefinisikan indeks awalan. Sistem memilih satu atau lebih bidang kolom kunci pertama dalam tabel sebagai indeks awalan. Panjang total indeks awalan tidak boleh melebihi 36 byte. Bidang kolom kunci setelah bidang tipe VARCHAR tidak ditambahkan ke indeks awalan.

Urutan bidang dalam tabel sangat penting karena menentukan bidang mana yang akan digunakan dalam indeks awalan. Kami sangat merekomendasikan Anda menentukan urutan bidang kolom kunci berdasarkan aturan berikut:

• Bidang kolom kunci dengan kardinalitas tinggi yang sering digunakan sebagai kondisi filter ditempatkan sebelum bidang lainnya. Misalnya, dalam bagian Gunakan Model Duplicate, bidang log_time ditempatkan sebelum bidang error_code.

• Bidang kolom kunci yang digunakan sebagai kondisi filter ekuivalen ditempatkan sebelum bidang kolom kunci yang digunakan sebagai kondisi filter rentang. Misalnya, dalam bagian Gunakan Indeks Terbalik, bidang order_time untuk penyaringan rentang ditempatkan setelah bidang order_id.

• Bidang tipe biasa ditempatkan sebelum bidang tipe VARCHAR. Misalnya, bidang kolom kunci tipe INT ditempatkan sebelum bidang kolom kunci tipe VARCHAR.

Contoh

Dalam bagian Gunakan Indeks Terbalik, tabel informasi pesanan menggunakan indeks awalan order_id+order_time. Jika kondisi query berisi bidang order_id atau berisi kedua bidang order_id dan order_time, kecepatan query meningkat secara signifikan. Kecepatan query di Contoh 1 lebih cepat daripada di Contoh 2.

Contoh 1

SELECT * FROM orders WHERE order_id = 1829239 and order_time = '2024-02-01';

Contoh 2

SELECT * FROM orders WHERE order_time = '2024-02-01';