Cara mengambil pernyataan DDL secara batch untuk membuat indeks penyimpanan kolom - PolarDB

Saat Anda perlu membuat indeks kolom dalam memori (IMCI) untuk seluruh layanan atau modul—bukan hanya untuk kolom dalam satu pernyataan SELECT—gunakan alur kerja dbms_imci.columnar_advise_begin(). Alur kerja ini menyimpan rekomendasi dari beberapa kueri ke dalam cache dan menghapus duplikat hasilnya, sehingga menghasilkan kumpulan pernyataan DDL yang bersih mencakup semua tabel dan kolom yang terpengaruh.

Prasyarat

Sebelum memulai, pastikan Anda telah memiliki:

Kluster PolarDB for MySQL 8.0.1 dengan versi revisi 8.0.1.1.30 atau lebih baru
Izin SELECT pada tabel yang ingin Anda analisis

Cara kerja

Alur kerja DDL batch menggunakan empat prosedur tersimpan yang dipanggil secara berurutan:

Panggil dbms_imci.columnar_advise_begin() untuk memulai sesi batch. Panggilan columnar_advise() berikutnya akan menyimpan rekomendasinya di memori alih-alih langsung mengembalikan hasil. Nama tabel dan kolom duplikat secara otomatis dihapus duplikatnya selama proses caching.
Panggil dbms_imci.columnar_advise() sekali per kueri untuk mendaftarkan setiap pernyataan SELECT yang ingin Anda analisis.
Panggil dbms_imci.columnar_advise_show() atau dbms_imci.columnar_advise_show_by_columns() untuk mengambil pernyataan DDL yang telah dihapus duplikatnya.
Panggil dbms_imci.columnar_advise_end() untuk mengakhiri sesi dan mengosongkan cache.

Catatan

Memanggil dbms_imci.columnar_advise_begin() diikuti oleh dbms_imci.columnar_advise_by_columns() setara dengan memanggil dbms_imci.columnar_advise().

Prosedur tersimpan

dbms_imci.columnar_advise_begin()

Memulai sesi pengumpulan DDL batch. Setelah Anda memanggil prosedur ini, dbms_imci.columnar_advise() akan menyimpan rekomendasi di memori alih-alih menampilkannya secara langsung.

dbms_imci.columnar_advise_show()

Mengembalikan satu pernyataan DDL per tabel yang terpengaruh. Nama tabel duplikat tidak disertakan.

Setiap baris dalam hasil berisi satu kolom DDL_STATEMENT dengan pernyataan ALTER TABLE yang mengaktifkan IMCI pada seluruh tabel menggunakan atribut COMMENT='COLUMNAR=1'.

dbms_imci.columnar_advise_show_by_columns()

Mengembalikan satu pernyataan DDL per tabel yang terpengaruh, dengan mencantumkan setiap kolom yang direkomendasikan secara individual. Nama kolom duplikat tidak disertakan.

Setiap baris berisi satu kolom DDL_STATEMENT dengan pernyataan ALTER TABLE ... MODIFY COLUMN yang mengaktifkan IMCI pada setiap kolom yang direkomendasikan menggunakan COMMENT 'COLUMNAR=1'.

Gunakan varian ini ketika Anda membutuhkan granularitas tingkat kolom—misalnya, ketika hanya sebagian kolom dalam suatu tabel yang dirujuk oleh kueri Anda.

dbms_imci.columnar_advise_end()

Mengakhiri sesi batch dan mengosongkan cache. Sebelum memanggil prosedur ini, Anda dapat memanggil prosedur show beberapa kali untuk memeriksa hasilnya. Memanggil prosedur show setelah columnar_advise_end() akan menghasilkan error.

Bahkan jika Anda melewatkan columnar_advise_end(), cache tetap akan dikosongkan secara otomatis saat koneksi ditutup.

Catatan penggunaan

Batas memori cache dikontrol oleh parameter imci_columnar_advise_buffer_size. Nilai default-nya adalah 8 MB, yang cukup untuk ribuan tabel. Untuk menambah batas tersebut, jalankan:
```
SET imci_columnar_advise_buffer_size = 16777216;
```

Contoh: mendapatkan pernyataan DDL secara batch

Contoh berikut menganalisis beberapa kueri SELECT terhadap dua tabel dan mengambil pernyataan DDL IMCI yang direkomendasikan.

Beralih ke database test:
```
USE test;
```

Buat tabel t1 dan t2:

CREATE TABLE t1 (a INT, b INT) ENGINE = InnoDB;
CREATE TABLE t2 (a INT, b INT) ENGINE = InnoDB;

Mulai sesi DDL batch:
```
CALL dbms_imci.columnar_advise_begin();
```

Daftarkan kueri yang akan dianalisis. Contoh berikut memanggil columnar_advise() empat kali dengan kueri yang sama untuk mensimulasikan workload dengan pola kueri berulang:

CALL dbms_imci.columnar_advise('SELECT COUNT(t1.a) FROM t1 INNER JOIN t2 ON t1.a = t2.a GROUP BY t1.b');
CALL dbms_imci.columnar_advise('SELECT COUNT(t1.a) FROM t1 INNER JOIN t2 ON t1.a = t2.a GROUP BY t1.b');
CALL dbms_imci.columnar_advise('SELECT COUNT(t1.a) FROM t1 INNER JOIN t2 ON t1.a = t2.a GROUP BY t1.b');
CALL dbms_imci.columnar_advise('SELECT COUNT(t1.a) FROM t1 INNER JOIN t2 ON t1.a = t2.a GROUP BY t1.b');

Ambil pernyataan DDL. Gunakan salah satu atau kedua prosedur show sebelum mengakhiri sesi.

Berdasarkan tabel — satu pernyataan ALTER TABLE per tabel yang terpengaruh, mengaktifkan IMCI pada semua kolom:

CALL dbms_imci.columnar_advise_show();

Output yang diharapkan:

+-------------------------------------------+
| DDL_STATEMENT                             |
+-------------------------------------------+
| ALTER TABLE test.t1 COMMENT='COLUMNAR=1'; |
| ALTER TABLE test.t2 COMMENT='COLUMNAR=1'; |
+-------------------------------------------+
2 rows in set (0.00 sec)

Berdasarkan kolom — satu pernyataan ALTER TABLE per tabel yang terpengaruh, dengan setiap kolom yang direkomendasikan dicantumkan secara individual:

CALL dbms_imci.columnar_advise_show_by_columns();

Output yang diharapkan:

+-------------------------------------------------------------------------------------------------------------------------------------------+
| DDL_STATEMENT                                                                                                                             |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ALTER TABLE test.t1 MODIFY COLUMN a int(11) DEFAULT NULL COMMENT 'COLUMNAR=1', MODIFY COLUMN b int(11) DEFAULT NULL COMMENT 'COLUMNAR=1'; |
| ALTER TABLE test.t2 MODIFY COLUMN a int(11) DEFAULT NULL COMMENT 'COLUMNAR=1';                                                            |
+-------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Akhiri sesi dan kosongkan cache:

CALL dbms_imci.columnar_advise_end();

Output yang diharapkan:

Query OK, 0 rows affected (0.11 sec)