Cara Menggunakan dan Mengelola Statement Outline - PolarDB

Dalam lingkungan produksi, rencana eksekusi Pernyataan SQL sering berubah sehingga menyebabkan ketidakstabilan database. PolarDB menstabilkan rencana eksekusi MySQL menggunakan petunjuk pengoptimal (optimizer hints) dan petunjuk indeks (index hints). Metode ini disebut Statement Outline. PolarDB juga menyediakan paket DBMS_OUTLN untuk membantu Anda menerapkan Statement Outline secara cepat. Topik ini menjelaskan cara menggunakan dan mengelola Statement Outline.

Versi yang Didukung

Kluster PolarDB Anda harus menjalankan salah satu versi berikut:

PolarDB for MySQL 5.6 dengan versi minor 5.6.1.0.36 atau lebih baru.
PolarDB for MySQL 5.7 dengan versi minor 5.7.1.0.2 atau lebih baru.
PolarDB for MySQL 8.0.1 dengan versi minor 8.0.1.1.1 atau lebih baru.
PolarDB for MySQL 8.0.2.

Anda dapat memeriksa versi kluster Anda dengan melihat nomor versi.

Quick Start

Menentukan Indeks Menggunakan Optimizer Hint

Sintaks

Catatan

Versi 5.6 tidak mendukung hint. Untuk menentukan indeks pada versi ini, gunakan Index Outline sebagai gantinya.

/*+ INDEX(table_name idx) */

Contoh

CALL dbms_outln.add_optimizer_outline('test', '/*+ INDEX(t1 i_a) */', 'SELECT test.t1.a AS a FROM test.t1');

Menentukan Indeks Menggunakan Index Hint

Sintaks

Catatan

Gunakan USE atau FORCE INDEX untuk menentukan indeks. Metode ini berfungsi di versi 5.6 yang tidak mendukung hint.

CALL dbms_outln.add_index_outline('<Schema_name>','<Digest>',<Position>,'<Type>','<Hint>','<Scope>','<Query>');

Deskripsi Parameter

Parameter Position menentukan tabel yang dipengaruhi oleh outline. Parameter ini menggunakan pengindeksan berbasis 1 dan sesuai dengan urutan tabel dalam teks SQL. Nilai Position adalah nomor urut tabel tempat parameter Hint diterapkan.
Untuk parameter Digest, string kosong biasanya berfungsi. Untuk detail lebih lanjut, lihat add_index_outline.

Contoh

CALL dbms_outln.add_index_outline('outline_db', '', 1, 'USE INDEX', 'ind_1', '',
                                  "SELECT * FROM t1 WHERE t1.col1 =1 AND t1.col2 ='xpchild'");

Menentukan Urutan Join

Sintaks

Catatan

Petunjuk ini memaksa urutan join tertentu. Anda tidak perlu mencantumkan semua tabel. Tabel yang tercantum akan di-join terlebih dahulu. Pengoptimal memilih urutan untuk tabel yang tersisa.

/*+ JOIN_PREFIX(t1, t2, ...) */

Contoh

CALL dbms_outln.add_optimizer_outline('outline_db', '/*+ JOIN_PREFIX(it1, it2) */',
                                      'SELECT it3.id3, it2.i2, it1.id2
                                       FROM t3 it3, t1 it1, t2 it2
                                       WHERE it3.i3 = it1.id1
                                       AND it2.id2 = it1.id2
                                       GROUP BY it3.id3, it1.id2
                                       ) t, t2 ot
                                       WHERE ot.id2 = t.id2');

Mengatur Variabel untuk Satu Pernyataan

Sintaks

Catatan

Atur nilai variabel yang hanya berlaku untuk pernyataan ini.

/*+ SET_VAR(<var_name>=<var_value>) */

Contoh

CALL dbms_outln.add_optimizer_outline('test', '/*+ SET_VAR(max_execution_time=1) */',
                                      'SELECT * FROM t1 ');

Menentukan Secara Manual Eksekusi Row Store atau Column Store

Pada kluster dengan node read-only yang mendukung indeks columnstore, gunakan Outline untuk memaksa pernyataan dijalankan pada node read-only row-store atau node read-only columnstore-index.

Format hint:

# Force columnstore usage
/*+ SET_VAR(cost_threshold_for_imci=0) */

# Force row-store usage
/*+ SET_VAR(use_imci_engine=OFF) */

Outline:

# Force execution on columnstore-index read-only nodes
CALL dbms_outln.add_optimizer_outline('test', '/*+ SET_VAR(cost_threshold_for_imci=0) */', 'SELECT test.t1.a AS a FROM test.t1');

# Force execution on row-store read-only nodes
CALL dbms_outln.add_optimizer_outline('test', '/*+ SET_VAR(use_imci_engine=OFF) */', 'SELECT test.t1.a AS a FROM test.t1');

Ikhtisar Fitur

Statement Outline mendukung semua jenis hint dalam MySQL 8.0 resmi. Ini terbagi menjadi dua kategori:

Optimizer Hints
Termasuk optimizer hints tingkat tabel, tingkat indeks, dan urutan join. Untuk detailnya, lihat Optimizer Hints.
Catatan
PolarDB for MySQL 5.6 tidak mendukung optimizer hints.
Index Hints
Diklasifikasikan berdasarkan tipe dan cakupan. Untuk detailnya, lihat Index Hints.

Deskripsi Parameter

Login ke Konsol PolarDB. Pada halaman Pengaturan Parameter, gunakan Set Parameters untuk mengaktifkan atau menonaktifkan Statement Outline atau Sharding Outline.

Parameter

Level

Deskripsi

loose_opt_outline_enabled

Global

Mengontrol apakah Statement Outline diaktifkan. Nilai yang valid:

ON (default): Mengaktifkan Statement Outline.
OFF: Menonaktifkan Statement Outline.

loose_outline_templated_digest_for_sharding_table

Session

Fitur Table Sharding Outline. Nilai yang valid:

ON (default)
OFF

Catatan

Parameter ini hanya berlaku untuk versi berikut:

PolarDB for MySQL 8.0.1 dengan versi minor 8.0.1.1.54 atau lebih baru.
PolarDB for MySQL 8.0.2 dengan versi minor 8.0.2.2.33 atau lebih baru.

Mengelola Statement Outlines

Catatan

Jika Schema_name tidak kosong, Schema_name dari Pernyataan SQL Anda dan nilai Digest dari pernyataan tersebut harus sesuai dengan Schema_name dan nilai Digest dalam aturan Statement Outline agar Statement Outline tersebut berlaku.
Jika Schema_name kosong, hanya Digest dari Pernyataan SQL Anda yang harus sesuai dengan Digest dalam aturan Statement Outline.

Untuk memudahkan pengelolaan Statement Outline, PolarDB mendefinisikan lima prosedur tersimpan lokal dalam DBMS_OUTLN, seperti dijelaskan di bawah ini:

add_optimizer_outline: Menambahkan optimizer hints.
add_index_outline: Menambahkan index hints.
preview_outline: Melihat pratinjau pernyataan mana yang sesuai dengan Statement Outline. Gunakan ini untuk memvalidasi outline secara manual.
show_outline: Melihat apakah Statement Outline aktif di memori.
del_outline: Menghapus Statement Outline dari memori dan dari tabel sistem.

add_optimizer_outline

Sintaks

dbms_outln.add_optimizer_outline('<Schema_name>','<Hint>','<query>');

Parameter

Parameter	Deskripsi
Schema_name	Nama database.
Hint	Pada Optimizer Hint, Hint adalah string hint lengkap, seperti */+MAX_EXECUTION_TIME(1000) /*.
Query	Pernyataan SQL asli yang akan ditambahkan Statement Outline-nya.

Catatan

PolarDB for MySQL versi 5.6 tidak mendukung add_optimizer_outline.
Ketika pernyataan Query memerlukan tanda kutip, sertakan bagian yang membutuhkannya dalam tanda kutip tunggal di dalam pernyataan Query, dan sertakan seluruh pernyataan Query dalam tanda kutip ganda.
Ketika mengeksekusi Statement Outline yang dibuat dengan tanda kutip tunggal dalam pernyataan Query, outline tersebut berhasil dicocokkan terlepas dari penggunaan tanda kutip tunggal atau ganda.

Contoh

Pernyataan Query asli:

SELECT * FROM t1 WHERE name="Tom";

Pernyataan Query yang dimodifikasi:

SELECT * FROM t1 WHERE name='Tom';

Pernyataan Query untuk menambahkan Outline:

CALL dbms_outln.add_optimizer_outline("", "/*+ max_execution_time(1000) */", "SELECT * FROM t1 WHERE name='Tom'");

add_index_outline

Sintaks

dbms_outln.add_index_outline('<Schema_name>','<Digest>',<Position>,'<Type>','<Hint>','<Scope>','<Query>');

Catatan

Query adalah pernyataan SQL asli yang akan ditambahkan Statement Outline-nya.
Pilih salah satu antara Digest atau Query. Jika Anda memilih Query, DBMS_OUTLN akan menghitung Digest dan Digest_text.

Parameter

Parameter	Deskripsi
Schema_name	Nama database.
Digest	String hash 64-byte yang diperoleh dengan melakukan hashing pada Digest_text. Untuk informasi lebih lanjut, lihat STATEMENT_DIGEST().
Position	Position menunjukkan posisi tabel, dimulai dari 1. Jika hint berlaku untuk tabel ke-N, Position bernilai N.
Type	Pada Index Hint, tipe hint dapat berupa USE INDEX, FORCE INDEX, atau IGNORE INDEX.
Hint	Pada Index Hint, Hint adalah daftar nama indeks, seperti ind_1,ind_2.
Scope	Mencakup tiga tipe berikut: FOR GROUP BY FOR ORDER BY FOR JOIN Catatan String kosong menunjukkan semua tipe Index Hint.
Query	Pernyataan SQL asli yang akan ditambahkan Statement Outline-nya.

Contoh

CALL dbms_outln.add_index_outline('outline_db', '', 1, 'USE INDEX', 'ind_1', '', "SELECT * FROM t1 WHERE t1.col1 =1 AND t1.col2 ='xpchild'");

preview_outline

Sintaks

dbms_outln.preview_outline('<Schema_name>','<Query>');

Contoh

mysql> CALL dbms_outln.preview_outline('outline_db', "SELECT * FROM t1 WHERE t1.col1 =1 AND t1.col2 ='xpchild'");
+------------+------------------------------------------------------------------+------------+------------+-------+---------------------+
| SCHEMA     | DIGEST                                                           | BLOCK_TYPE | BLOCK_NAME | BLOCK | HINT                |
+------------+------------------------------------------------------------------+------------+------------+-------+---------------------+
| outline_db | b4369611be7ab2d27c85897632576a04bc08f50b928a1d735b62d0a140628c4c | TABLE      | t1         |     1 | USE INDEX (`ind_1`) |
+------------+------------------------------------------------------------------+------------+------------+-------+---------------------+
1 row in set (0.01 sec)

show_outline

Sintaks

dbms_outln.show_outline();

Contoh

CALL dbms_outln.show_outline();
+------+------------+------------------------------------------------------------------+-----------+-------+------+-------------------------------------------------------+------+----------+-------------------------------------------------------------------------------------+
| ID   | SCHEMA     | DIGEST                                                           | TYPE      | SCOPE | POS  | HINT                                                  | HIT  | OVERFLOW | DIGEST_TEXT                                                                         |
+------+------------+------------------------------------------------------------------+-----------+-------+------+-------------------------------------------------------+------+----------+-------------------------------------------------------------------------------------+
|   33 | outline_db | 36bebc61fce7e32b93926aec3fdd790dad5d895107e2d8d3848d1c60b74bcde6 | OPTIMIZER |       |    1 | /*+ SET_VAR(foreign_key_checks=OFF) */                |    1 |        0 | SELECT * FROM `t1` WHERE `id` = ?                                                   |
|   32 | outline_db | 36bebc61fce7e32b93926aec3fdd790dad5d895107e2d8d3848d1c60b74bcde6 | OPTIMIZER |       |    1 | /*+ MAX_EXECUTION_TIME(1000) */                       |    2 |        0 | SELECT * FROM `t1` WHERE `id` = ?                                                   |
|   34 | outline_db | d4dcef634a4a664518e5fb8a21c6ce9b79fccb44b773e86431eb67840975b649 | OPTIMIZER |       |    1 | /*+ BNL(t1,t2) */                                     |    1 |        0 | SELECT `t1` . `id` , `t2` . `id` FROM `t1` , `t2`                                   |
|   35 | outline_db | 5a726a609b6fbfb76bb8f9d2a24af913a2b9d07f015f2ee1f6f2d12dfad72e6f | OPTIMIZER |       |    2 |  /*+ QB_NAME(subq1) */                                |    2 |        0 | SELECT * FROM `t1` WHERE `t1` . `col1` IN ( SELECT `col1` FROM `t2` )               |
|   36 | outline_db | 5a726a609b6fbfb76bb8f9d2a24af913a2b9d07f015f2ee1f6f2d12dfad72e6f | OPTIMIZER |       |    1 | /*+ SEMIJOIN(@subq1 MATERIALIZATION, DUPSWEEDOUT) */  |    2 |        0 | SELECT * FROM `t1` WHERE `t1` . `col1` IN ( SELECT `col1` FROM `t2` )               |
|   30 | outline_db | b4369611be7ab2d27c85897632576a04bc08f50b928a1d735b62d0a140628c4c | USE INDEX |       |    1 | ind_1                                                 |    3 |        0 | SELECT * FROM `t1` WHERE `t1` . `col1` = ? AND `t1` . `col2` = ?                    |
|   31 | outline_db | 33c71541754093f78a1f2108795cfb45f8b15ec5d6bff76884f4461fb7f33419 | USE INDEX |       |    2 | ind_2                                                 |    1 |        0 | SELECT * FROM `t1` , `t2` WHERE `t1` . `col1` = `t2` . `col1` AND `t2` . `col2` = ? |
+------+------------+------------------------------------------------------------------+-----------+-------+------+-------------------------------------------------------+------+----------+-------------------------------------------------------------------------------------+
7 rows in set (0.00 sec)

Di sini, HIT menunjukkan jumlah kali Statement Outline dicocokkan, dan OVERFLOW menunjukkan jumlah kali Statement Outline tidak menemukan blok kueri atau tabel yang sesuai.

del_outline

Sintaks

dbms_outln.del_outline(<Id>);

Contoh

CALL dbms_outln.del_outline(32);

Catatan

Jika aturan yang akan dihapus tidak ada, sistem akan memberikan peringatan. Gunakan SHOW WARNINGS; untuk melihat isi peringatan tersebut.

CALL dbms_outln.del_outline(1000);
Query OK, 0 rows affected, 2 warnings (0.00 sec)

SHOW WARNINGS;
+---------+------+----------------------------------------------+
| Level   | Code | Message                                      |
+---------+------+----------------------------------------------+
| Warning | 7521 | Statement outline 1000 is not found in table |
| Warning | 7521 | Statement outline 1000 is not found in cache |
+---------+------+----------------------------------------------+
2 rows in set (0.00 sec)

Sharding Outline

Dalam skenario sharding tabel, Anda sering membuat banyak tabel fisik yang memiliki struktur sama tetapi akhiran numerik berbeda pada nama tabelnya, seperti t_001, t_002, ..., dan t_999. Statement Outline tradisional memerlukan aturan terpisah untuk setiap nama tabel spesifik. Hal ini menghasilkan jumlah aturan yang sangat besar, biaya maintenance tinggi, dan sering terjadi kelalaian.

Sharding Outline secara otomatis membuat templat dari angka berurutan di akhir nama tabel dan kolom. Misalnya, sistem mengenali t_1, t_2, dan t_100 sebagai pola tunggal t_?. Hal ini memungkinkan satu aturan Outline untuk mencocokkan semua tabel sharded yang mengikuti pola penamaan tersebut. Konfigurasikan aturan sekali untuk menerapkannya ke semua tabel yang sesuai.

Dimensi perbandingan	Standard Outline	Table Sharding Outline
Pencocokan nama tabel	Cocok secara ketat dengan nama tabel lengkap, seperti `t_1`	Otomatis membuat templat angka di akhir, seperti `t_?`
Cakupan	Satu tabel spesifik	Banyak tabel sharded dengan pola penamaan konsisten
Jumlah aturan	Diperlukan aturan terpisah untuk setiap tabel sharded	Satu aturan mencakup semua tabel sharded yang sesuai
Kompleksitas maintenance	Tinggi: Memerlukan pembuatan, pembaruan, dan penghapusan secara batch	Rendah: Manajemen terpadu untuk pembuatan, pembaruan, dan penghapusan aturan menjamin konsistensi kuat

Cakupan

Kluster PolarDB Anda harus merupakan salah satu versi berikut:

PolarDB for MySQL 8.0.1 dengan versi minor 8.0.1.1.54 atau lebih baru.
PolarDB for MySQL 8.0.2 dengan versi minor 8.0.2.2.33 atau lebih baru.

Penggunaan

Login ke Konsol PolarDB. Pada halaman Pengaturan Parameter, atur parameter loose_outline_templated_digest_for_sharding_table untuk mengaktifkan fitur Sharding Outline.

Panggil prosedur tersimpan khusus add_optimizer_outline_sharding. Struktur dan semantik parameternya identik dengan add_optimizer_outline. Satu-satunya perbedaan adalah kemampuan tambahannya untuk membuat templat sharding tabel:

CALL dbms_outln.add_optimizer_outline_sharding(
  'test',                          -- Schema_name
  '',                              -- Digest (Biarkan kosong agar sistem menghitungnya)
  1,                               -- Position (Posisi berbasis 1 dari tabel. Untuk hint pada tabel ke-N, atur Position ke N.)
  '/*+ MAX_EXECUTION_TIME(1000) */', -- String hint
  "SELECT t_1.c_1 FROM t_1"        -- SQL asli (Dapat berisi nama tabel sharded apa pun, seperti t_1, t_2, atau t_100)
);

Setelah dieksekusi, aturan tersebut berlaku untuk semua tabel yang sesuai dengan pola t_?, seperti t_1, t_2, dan t_100.

Contoh Demo

Gunakan salah satu metode berikut untuk memvalidasi efek Statement Outline.

Lihat pratinjau outline menggunakan preview_outline.

Perintah SQL-nya adalah sebagai berikut:

CALL dbms_outln.preview_outline('outline_db', "SELECT * FROM t1 WHERE t1.col1 =1 AND t1.col2 ='xpchild'");

Hasilnya adalah sebagai berikut:

+------------+------------------------------------------------------------------+------------+------------+-------+---------------------+
| SCHEMA     | DIGEST                                                           | BLOCK_TYPE | BLOCK_NAME | BLOCK | HINT                |
+------------+------------------------------------------------------------------+------------+------------+-------+---------------------+
| outline_db | b4369611be7ab2d27c85897632576a04bc08f50b928a1d735b62d0a140628c4c | TABLE      | t1         |     1 | USE INDEX (`ind_1`) |
+------------+------------------------------------------------------------------+------------+------------+-------+---------------------+
1 row in set (0.01 sec)

Anda dapat menggunakan EXPLAIN untuk melihat rencana eksekusi.

Catatan

Nilai pada kolom Extra hanya muncul pada versi berikut:

PolarDB for MySQL 8.0.1 dengan versi minor 8.0.1.0.34 atau lebih baru.
PolarDB for MySQL versi 8.0.1 dengan versi minor 8.0.2.2.27 atau yang lebih baru.

Perintah SQL-nya adalah sebagai berikut:

EXPLAIN SELECT * FROM t1 WHERE t1.col1 =1 AND t1.col2 ='xpchild';

Hasilnya adalah sebagai berikut:

+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra                        |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+------------------------------+
|  1 | SIMPLE      | t1    | NULL       | ref  | ind_1         | ind_1 | 5       | const |    1 |   100.00 | Using where; Using outline 1 |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+------------------------------+
1 row in set, 1 warning (0.00 sec)

Perintah SQL-nya adalah sebagai berikut:

SHOW warnings;

Hasilnya adalah sebagai berikut:

+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                 |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ SELECT `outline_db`.`t1`.`id` AS `id`,`outline_db`.`t1`.`col1` AS `col1`,`outline_db`.`t1`.`col2` AS `col2` FROM `outline_db`.`t1` USE INDEX (`ind_1`) WHERE ((`outline_db`.`t1`.`col1` = 1) AND (`outline_db`.`t1`.`col2` = 'xpchild')) |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Lampiran: Tabel Statement Outline

PolarDB menyertakan tabel sistem bernama outline untuk menyimpan Hint. Sistem secara otomatis membuat tabel ini saat startup; pembuatan manual tidak diperlukan. Pernyataan pembuatan untuk tabel sistem ini adalah sebagai berikut:

CREATE TABLE `mysql`.`outline` (
  `Id` bigint(20) NOT NULL AUTO_INCREMENT,
  `Schema_name` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `Digest` varchar(64) COLLATE utf8_bin NOT NULL,
  `Digest_text` longtext COLLATE utf8_bin,
  `Type` enum('IGNORE INDEX','USE INDEX','FORCE INDEX','OPTIMIZER') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `Scope` enum('','FOR JOIN','FOR ORDER BY','FOR GROUP BY') CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '',
  `State` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'Y',
  `Position` bigint(20) NOT NULL,
  `Hint` text COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`Id`)
) /*!50100 TABLESPACE `mysql` */ ENGINE=InnoDB
DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0 COMMENT='Statement outline'

Deskripsi parameter adalah sebagai berikut:

Parameter	Deskripsi
Id	ID Outline.
Schema_name	Nama database.
Digest	String hash 64-byte yang diperoleh dengan melakukan perhitungan hash pada Digest_text. Untuk informasi lebih lanjut, lihat STATEMENT_DIGEST().
Digest_text	Fitur dari pernyataan SQL.
Type	Untuk Optimizer Hints, tipe Hint adalah OPTIMIZER. Untuk Index Hints, tipe Hint adalah USE INDEX, FORCE INDEX, atau IGNORE INDEX.
Scope	Parameter ini hanya diperlukan untuk Index Hints. Termasuk tiga tipe berikut: FOR GROUP BY FOR ORDER BY FOR JOIN Catatan String kosong menunjukkan semua tipe Index Hints.
State	Apakah aturan ini diaktifkan. Nilai yang valid: N Y (Default)
Position	Untuk Optimizer Hints, Position menunjukkan Query Block. Semua Optimizer Hints harus diterapkan pada Query Block. Position dimulai dari 1. Jika Hint berlaku untuk kata kunci ke-N dalam pernyataan, Position bernilai N. Untuk Index Hints, Position menunjukkan posisi tabel. Dimulai dari 1 juga. Jika Hint berlaku untuk tabel ke-N, Position bernilai N.
Hint	Untuk Optimizer Hints, Hint merepresentasikan string Hint lengkap, seperti `/+ MAX_EXECUTION_TIME(1000) /`. Untuk Index Hints, Hint merepresentasikan daftar nama indeks, seperti `ind_1,ind_2`.