Kelola IMCI tingkat tabel melalui DDL untuk analitik lebih cepat - PolarDB - Alibaba Cloud - PolarDB

Gunakan pernyataan DDL pada halaman ini untuk membuat dan menghapus Indeks Kolom dalam Memori (IMCI) tingkat tabel pada kluster PolarDB for MySQL.

Persyaratan versi

Kluster Anda harus menjalankan salah satu versi berikut:

PolarDB for MySQL 8.0.1, versi revisi 8.0.1.1.45 atau yang lebih baru
PolarDB for MySQL 8.0.2, versi revisi 8.0.2.2.27 atau yang lebih baru

Perintah untuk satu tabel

Buat IMCI tingkat tabel

CREATE COLUMNAR INDEX ON <db_name>.<table_name>;
CREATE COLUMNAR INDEX ON <table_name>;

Jika db_name dihilangkan, pernyataan tersebut menargetkan database dari sesi saat ini. Berbeda dengan indeks sekunder, Anda tidak perlu menentukan nama indeks atau kolom yang disertakan—IMCI secara otomatis mencakup semua kolom.

Untuk menghindari error ketika tabel sudah memiliki IMCI, tambahkan IF NOT EXISTS sebelum ON:

CREATE COLUMNAR INDEX IF NOT EXISTS ON <db_name>.<table_name>;

Secara default, pernyataan ini setara dengan:

ALTER TABLE <db_name>.<table_name> COMMENT 'COLUMNAR=1 <OLD_COMMENT>';

Ekivalensi di atas hanya berlaku jika allow_implicit_imci_alter_comment diatur ke ON (nilai default). Jika diatur ke OFF, IMCI dibuat tanpa memodifikasi komentar tabel. Untuk detailnya, lihat Buat IMCI tingkat tabel tanpa memodifikasi komentar tabel.

Untuk mengonfigurasi IMCI kustom alih-alih menerima semua kolom, lihat Gunakan atribut diperluas IMCI untuk menyesuaikan IMCI.

Verifikasi status pembuatan IMCI

Setelah membuat IMCI, periksa apakah IMCI tersebut siap dengan menjalankan:

SHOW IMCI INDEXES;

Bidang STATE menunjukkan status saat ini:

State	Description
`COMMITTED`	IMCI siap dan tersedia untuk query.
`RECOVERING`	IMCI sedang dibangun dari data yang ada.
`RECOVER_BUILDING`	IMCI sedang dibangun ulang setelah event pemulihan.

Hapus IMCI tingkat tabel

DROP COLUMNAR INDEX ON <db_name>.<table_name>;
DROP COLUMNAR INDEX ON <table_name>;

Jika db_name dihilangkan, pernyataan tersebut menargetkan database dari sesi saat ini. Anda tidak perlu menentukan nama indeks.

Untuk menghindari error ketika IMCI sudah dihapus, tambahkan IF EXISTS sebelum ON:

DROP COLUMNAR INDEX IF EXISTS ON <db_name>.<table_name>;

Pernyataan ini setara dengan menjalankan kedua perintah berikut:

ALTER TABLE <db_name>.<table_name> COMMENT 'COLUMNAR=0 <OLD_COMMENT>';
ALTER TABLE <db_name>.<table_name> COMMENT '<OLD_COMMENT>';

Operasi batch

Gunakan pernyataan berikut untuk membuat atau menghapus IMCI di seluruh tabel dalam sebuah database sekaligus.

Buat IMCI tingkat tabel secara batch

Tiga opsi sintaksis setara didukung:

-- Opsi 1
CREATE COLUMNAR INDEX FOR TABLES IN <db_name>;

-- Opsi 2
CREATE COLUMNAR INDEX FOR TABLES FROM <db_name>;

-- Opsi 3: stored procedure (paket dbms_imci)
-- dbms_imci: paket database untuk mengelola operasi IMCI
-- add_columnar_index: membuat IMCI untuk semua tabel di database yang ditentukan
CALL dbms_imci.add_columnar_index('<db_name>');

Ketiga opsi tersebut membuat IMCI tingkat tabel untuk setiap tabel di <db_name> yang belum memiliki IMCI.

Prosedur tersimpan menerima parameter opsional set_implicit. Atur ke 1 untuk menambahkan penanda implicit_imci ke database, sehingga tabel baru yang dibuat di database tersebut secara otomatis mendapatkan IMCI:

CALL dbms_imci.add_columnar_index('<db_name>', 1);

Contoh

Contoh berikut membuat IMCI untuk semua tabel di database tpch dan memeriksa hasilnya:

mysql> SHOW TABLES IN tpch;
+--------------------+
| Tables_in_tpch     |
+--------------------+
| customer           |
| lineitem           |
| nation             |
| orders             |
| part               |
| partsupp           |
| region             |
| revenue0           |
| supplier           |
+--------------------+
9 rows in set (0.01 sec)

mysql> SHOW IMCI INDEXES;
Empty set (0.02 sec)

mysql> CREATE COLUMNAR INDEX FOR TABLES IN tpch;
+------------+--------+
| Table_Name | Result |
+------------+--------+
| customer   | Ok     |
| lineitem   | Ok     |
| nation     | Ok     |
| orders     | Ok     |
| part       | Ok     |
| partsupp   | Ok     |
| region     | Ok     |
| supplier   | Ok     |
+------------+--------+
8 rows in set (0.56 sec)

-- Periksa bidang STATE. COMMITTED berarti IMCI siap.
-- RECOVERING dan RECOVER_BUILDING berarti IMCI masih sedang dibangun.
mysql> SHOW IMCI INDEXES;
+----------+-------------+------------+----------+-----------+-----------+---------+------------------+---------------------+-----------------------------+--------------+
| TABLE_ID | SCHEMA_NAME | TABLE_NAME | NUM_COLS | NUM_PACKS | PACK_SIZE | ROW_ID  | STATE            | STATE_UPDATE_AT     | CHECKPOINT_DATADIR          | WRITE_POLICY |
+----------+-------------+------------+----------+-----------+-----------+---------+------------------+---------------------+-----------------------------+--------------+
| 1080     | tpch        | region     | 3        | 0         | 65536     | 0       | RECOVERING       | 2024-09-24 18:50:16 | ./imci_1/imci_chkp_1080_258 | Tradeoff     |
| 1081     | tpch        | nation     | 4        | 1         | 65536     | 65536   | COMMITTED        | 2024-09-24 18:50:16 | ./imci_1/imci_chkp_1081_254 | Tradeoff     |
| 1082     | tpch        | part       | 9        | 64        | 65536     | 4194304 | RECOVER_BUILDING | 2024-09-24 18:50:16 | ./imci_1/imci_chkp_1082_256 | Tradeoff     |
| 1083     | tpch        | supplier   | 7        | 0         | 65536     | 0       | RECOVERING       | 2024-09-24 18:50:16 | ./imci_1/imci_chkp_1083_259 | Tradeoff     |
| 1084     | tpch        | partsupp   | 5        | 0         | 65536     | 0       | RECOVERING       | 2024-09-24 18:50:16 | ./imci_1/imci_chkp_1084_257 | Tradeoff     |
| 1085     | tpch        | customer   | 8        | 64        | 65536     | 4194304 | RECOVER_BUILDING | 2024-09-24 18:50:15 | ./imci_1/imci_chkp_1085_252 | Tradeoff     |
| 1086     | tpch        | orders     | 9        | 0         | 65536     | 0       | RECOVER_BUILDING | 2024-09-24 18:50:16 | ./imci_1/imci_chkp_1086_255 | Tradeoff     |
| 1087     | tpch        | lineitem   | 15       | 0         | 65536     | 0       | RECOVER_BUILDING | 2024-09-24 18:50:15 | ./imci_1/imci_chkp_1087_253 | Tradeoff     |
+----------+-------------+------------+----------+-----------+-----------+---------+------------------+---------------------+-----------------------------+--------------+
8 rows in set, 1 warning (0.07 sec)

Hapus IMCI tingkat tabel secara batch

Tiga opsi sintaksis setara didukung:

-- Opsi 1
DROP COLUMNAR INDEX FOR TABLES IN <db_name>;

-- Opsi 2
DROP COLUMNAR INDEX FOR TABLES FROM <db_name>;

-- Opsi 3: stored procedure (paket dbms_imci)
-- dbms_imci: paket database untuk mengelola operasi IMCI
-- drop_columnar_index: menghapus IMCI dari semua tabel di database yang ditentukan
CALL dbms_imci.drop_columnar_index('<db_name>');

Ketiga opsi tersebut menghapus IMCI tingkat tabel yang ada dari setiap tabel di <db_name>.

Prosedur tersimpan menerima parameter opsional reset_implicit. Atur ke 1 untuk menghapus penanda implicit_imci dari database, sehingga tabel baru tidak lagi secara otomatis mendapatkan IMCI:

CALL dbms_imci.drop_columnar_index('<db_name>', 1);

Contoh

mysql> SHOW IMCI INDEXES;
+----------+-------------+------------+----------+-----------+-----------+-----------+-----------+---------------------+-----------------------------+--------------+
| TABLE_ID | SCHEMA_NAME | TABLE_NAME | NUM_COLS | NUM_PACKS | PACK_SIZE | ROW_ID    | STATE     | STATE_UPDATE_AT     | CHECKPOINT_DATADIR          | WRITE_POLICY |
+----------+-------------+------------+----------+-----------+-----------+-----------+-----------+---------------------+-----------------------------+--------------+
| 1080     | tpch        | region     | 3        | 1         | 65536     | 65536     | COMMITTED | 2024-09-24 18:50:30 | ./imci_1/imci_chkp_1080_258 | Tradeoff     |
| 1081     | tpch        | nation     | 4        | 1         | 65536     | 65536     | COMMITTED | 2024-09-24 18:50:16 | ./imci_1/imci_chkp_1081_254 | Tradeoff     |
| 1082     | tpch        | part       | 9        | 306       | 65536     | 20054016  | COMMITTED | 2024-09-24 18:50:30 | ./imci_1/imci_chkp_1082_256 | Tradeoff     |
| 1083     | tpch        | supplier   | 7        | 16        | 65536     | 1048576   | COMMITTED | 2024-09-24 18:50:34 | ./imci_1/imci_chkp_1083_259 | Tradeoff     |
| 1084     | tpch        | partsupp   | 5        | 1221      | 65536     | 80019456  | COMMITTED | 2024-09-24 18:51:15 | ./imci_1/imci_chkp_1084_257 | Tradeoff     |
| 1085     | tpch        | customer   | 8        | 229       | 65536     | 15007744  | COMMITTED | 2024-09-24 18:50:28 | ./imci_1/imci_chkp_1085_252 | Tradeoff     |
| 1086     | tpch        | orders     | 9        | 2289      | 65536     | 150011904 | COMMITTED | 2024-09-24 18:51:23 | ./imci_1/imci_chkp_1086_255 | Tradeoff     |
| 1087     | tpch        | lineitem   | 15       | 9156      | 65536     | 600047616 | COMMITTED | 2024-09-24 18:54:16 | ./imci_1/imci_chkp_1087_253 | Tradeoff     |
+----------+-------------+------------+----------+-----------+-----------+-----------+-----------+---------------------+-----------------------------+--------------+
8 rows in set, 1 warning (1.09 sec)

mysql> CALL dbms_imci.drop_columnar_index('tpch');
+-------------+------------+--------+
| Object_Name | Operation  | Result |
+-------------+------------+--------+
| customer    | drop_index | Ok     |
| lineitem    | drop_index | Ok     |
| nation      | drop_index | Ok     |
| orders      | drop_index | Ok     |
| part        | drop_index | Ok     |
| partsupp    | drop_index | Ok     |
| region      | drop_index | Ok     |
| supplier    | drop_index | Ok     |
+-------------+------------+--------+
8 rows in set (0.33 sec)

mysql> SHOW IMCI INDEXES;
Empty set (0.27 sec)

Kode hasil operasi batch

Kolom Result dalam output operasi batch menunjukkan hasil untuk setiap tabel:

Kode hasil	Deskripsi
`Ok`	Operasi berhasil diselesaikan.
`Skip by unsupported`	Operasi tidak didukung untuk tabel ini.
`Skip by no change`	Target sudah ada atau status sudah sesuai — misalnya, IMCI sudah ada saat membuat, atau tidak ada IMCI saat menghapus.
`Skip by concurrent operation`	Konflik operasi konkuren mencegah eksekusi — misalnya, pernyataan DDL lain memegang metadata lock (MDL) pada tabel.
`Skip by not found`	Objek target tidak ditemukan.
`Skip by ACL deny`	Akun saat ini tidak memiliki izin yang diperlukan.
`Failed`	Operasi gagal.

Buat IMCI tingkat tabel tanpa memodifikasi komentar tabel

Secara default, CREATE COLUMNAR INDEX ON menandai IMCI dengan memperbarui komentar tabel (setara dengan COLUMNAR=1 dalam komentar). Untuk membuat IMCI tanpa menyentuh komentar tabel, atur parameter sesi allow_implicit_imci_alter_comment ke OFF.

Parameter	Level	Default	Deskripsi
`allow_implicit_imci_alter_comment`	Session	`ON`	Mengontrol apakah komentar tabel dimodifikasi saat IMCI dibuat. Atur ke `OFF` untuk membuat indeks kolom tanpa memperbarui komentar tabel.

Jika diatur ke OFF, parameter ini berlaku untuk ketiga jalur pembuatan:

CREATE TABLE saat loose_polar_enable_implicit_imci_with_create_table diatur ke ON
CREATE COLUMNAR INDEX ON <db>.<table> untuk pembuatan dinamis
Pembuatan batch melalui CREATE COLUMNAR INDEX FOR TABLES IN <db_name>

Contoh 1: Buat tabel dan secara otomatis tambahkan IMCI tanpa memodifikasi komentar tabel.

SET allow_implicit_imci_alter_comment = OFF;
SET GLOBAL polar_enable_implicit_imci_with_create_table = ON;
CREATE TABLE t1 (id INT PRIMARY KEY, name VARCHAR(100));

SHOW CREATE TABLE t1 FULL;
/*
CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  COLUMNAR INDEX (`id`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
*/

Contoh 2: Tambahkan IMCI secara dinamis ke tabel yang sudah ada tanpa memodifikasi komentar tabel.

CREATE TABLE t1 (id INT PRIMARY KEY, name VARCHAR(100));

SET allow_implicit_imci_alter_comment = OFF;
CREATE COLUMNAR INDEX ON test.t1;

SHOW CREATE TABLE t1 FULL;
/*
CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  COLUMNAR INDEX (`id`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
*/

Contoh 3: Buat IMCI secara batch di beberapa tabel tanpa memodifikasi komentar tabel.

CREATE TABLE t1 (id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE t2 (id INT PRIMARY KEY, code DOUBLE);

SET allow_implicit_imci_alter_comment = OFF;
CREATE COLUMNAR INDEX FOR TABLES IN test;
/*
Table_Name  Result
t1          Ok
t2          Ok
*/

SHOW CREATE TABLE t1 FULL;
/*
CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  COLUMNAR INDEX (`id`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
*/

SHOW CREATE TABLE t2 FULL;
/*
CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `code` double DEFAULT NULL,
  PRIMARY KEY (`id`),
  COLUMNAR INDEX (`id`,`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
*/

Kapan menggunakan pengaturan ini

Gunakan `allow_implicit_imci_alter_comment = OFF` saat:

Komentar tabel harus tetap konsisten di seluruh penyewa atau instans. Misalnya, dalam lingkungan SaaS, tabel penyewa besar (dengan IMCI) dan tabel penyewa kecil (tanpa IMCI) akan memiliki output SHOW CREATE TABLE yang identik, sehingga menyederhanakan validasi dengan alat ekosistem.

Perhatikan batasan berikut:

Replikasi log biner: Saat mereplikasi melalui log biner, database sekunder tidak dapat membuat ulang IMCI karena komentar terkait IMCI tidak ada dalam binlog. Untuk membuat IMCI di database sekunder, konfigurasikan pengaturan parameter allow_implicit_imci_alter_comment dan parameter terkait yang sama di database primer dan sekunder.
IMCI kustom: IMCI kustom tidak dapat dikonfigurasi melalui pernyataan DDL ini. Untuk menyesuaikan IMCI, lihat Gunakan atribut diperluas IMCI untuk menyesuaikan IMCI.