Gunakan dan kelola outline pernyataan - PolarDB

Dalam lingkungan produksi, rencana eksekusi untuk pernyataan SQL sering berubah, yang dapat menyebabkan ketidakstabilan database. PolarDB menyediakan fitur outline pernyataan, memungkinkan penggunaan optimizer hints atau index hints untuk menstabilkan rencana eksekusi SQL. PolarDB juga menyediakan toolkit DBMS_OUTLN untuk mengelola outline pernyataan secara efisien. Topik ini menjelaskan cara menggunakan dan mengelola outline pernyataan.

Versi yang Didukung

Kluster PolarDB Anda harus menjalankan salah satu versi mesin database berikut:

PolarDB untuk MySQL 5.6 dengan versi revisi 5.6.1.0.36 atau lebih baru.
PolarDB untuk MySQL 5.7 dengan versi revisi 5.7.1.0.2 atau lebih baru.
PolarDB untuk MySQL 8.0.1 dengan versi revisi 8.0.1.1.1 atau lebih baru.
PolarDB untuk MySQL 8.0.2.

Untuk informasi tentang cara memeriksa versi mesin database kluster PolarDB, lihat Periksa versi mesin.

Hints

Fitur outline pernyataan mendukung jenis hints berikut yang disediakan oleh MySQL 8.0:

Optimizer Hints
Optimizer hints diklasifikasikan menjadi optimizer hints tingkat tabel, indeks, dan urutan join berdasarkan ruang lingkup dan objek hint yang berlaku. Untuk informasi lebih lanjut, lihat Optimizer Hints.
Catatan
Optimizer hints tidak tersedia untuk kluster PolarDB untuk MySQL 5.6.
Index Hints
Index hints diklasifikasikan berdasarkan tipe dan ruang lingkup. Untuk informasi lebih lanjut, lihat Index Hints.

Parameter

Anda dapat masuk ke Konsol PolarDB untuk mengaktifkan atau menonaktifkan fitur outline pernyataan dengan mengonfigurasi parameter opt_outline_enabled. Untuk informasi tentang cara mengonfigurasi parameter, lihat Konfigurasikan parameter kluster dan node.

Parameter

Tingkat

Deskripsi

loose_opt_outline_enabled

Global

Menentukan apakah akan mengaktifkan fitur outline pernyataan. Nilai valid:

ON (default)
OFF

Tabel Outline

PolarDB menggunakan tabel sistem bawaan bernama outline untuk menyimpan hints. Sistem secara otomatis membuat tabel outline saat startup. Pernyataan berikut digunakan untuk membuat tabel outline:

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'

Tabel berikut menjelaskan parameter dalam pernyataan sebelumnya.

Parameter	Deskripsi
Id	ID setiap outline pernyataan.
Schema_name	Skema database di mana pernyataan SQL dieksekusi.
Digest	Nilai hash digest pernyataan 64-byte yang dihitung berdasarkan nilai parameter Digest_text. Untuk informasi lebih lanjut, lihat STATEMENT_DIGEST().
Digest_text	Digest 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	Konteks tempat hint berlaku. Parameter ini hanya diperlukan untuk index hints. Nilai valid: FOR GROUP BY FOR ORDER BY FOR JOIN Catatan String kosong menentukan bahwa hint berlaku untuk semua konteks.
State	Menentukan apakah akan mengaktifkan hint. Nilai valid: N Y (default)
Position	Untuk optimizer hints, parameter Position menentukan nomor blok kueri tempat optimizer hints berlaku. Nilai parameter Position adalah pengenal numerik yang dimulai dari 1. Untuk index hints, parameter Position menentukan nomor tabel tempat index hints berlaku. Nilai parameter Position adalah pengenal numerik yang dimulai dari 1.
Hint	Untuk optimizer hints, parameter Hint menentukan string hint. Contoh: `/+ MAX_EXECUTION_TIME(1000) /`. Untuk index hints, parameter Hint menentukan daftar nama indeks. Contoh: `ind_1,ind_2`.

Kelola outline pernyataan

Catatan

Outline pernyataan dengan parameter Schema_name yang tidak kosong hanya berlaku jika nilai parameter Schema_name dan Digest dari pernyataan SQL cocok dengan nilai parameter Schema_name dan Digest dalam outline pernyataan.
Outline pernyataan dengan parameter Schema_name kosong hanya berlaku jika nilai parameter Digest dari pernyataan SQL cocok dengan nilai parameter Digest dalam outline pernyataan.

Untuk mengelola outline pernyataan secara efisien, PolarDB menyediakan serangkaian prosedur yang telah ditentukan sebelumnya dalam toolkit DBMS_OUTLN.

add_optimizer_outline: Menambahkan optimizer hints.
add_index_outline: Menambahkan index hints.
preview_outline: Memeriksa apakah pernyataan SQL cocok dengan outline yang ada. Ini berguna untuk validasi manual.
show_outline: Menampilkan outline pernyataan yang cocok dan digunakan dalam memori.
del_outline: Menghapus outline pernyataan dari memori dan tabel outline.

add_optimizer_outline

Syntax

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

Parameters

Parameter	Deskripsi
Schema_name	Skema database di mana pernyataan SQL dieksekusi.
Hint	String hint, seperti */+ MAX_EXECUTION_TIME(1000) /*.
Query	Pernyataan SQL asli untuk mana Anda ingin menggunakan outline pernyataan.

Catatan

Prosedur add_optimizer_outline tidak didukung dalam kluster PolarDB untuk MySQL 5.6.

Jika bagian dalam pernyataan Query memerlukan kutipan, gunakan tanda kutip tunggal (') untuk mengapit bagian dalam pernyataan Query dan gunakan tanda kutip ganda (") untuk mengapit pernyataan Query.
Tidak peduli apakah pernyataan Query menggunakan tanda kutip tunggal (') atau tanda kutip ganda ("), outline pernyataan berlaku untuk kueri hanya jika digest dari kueri cocok dengan outline pernyataan.

Examples

Pernyataan kueri asli:

SELECT * FROM t1 WHERE name="Tom";

Pernyataan kueri yang dimodifikasi:

SELECT * FROM t1 WHERE name='Tom';

Outline pernyataan digunakan untuk pernyataan kueri:

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

add_index_outline

Syntax

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

Catatan

Parameter Query menentukan pernyataan SQL asli untuk mana Anda ingin menggunakan outline pernyataan.
Anda dapat menentukan parameter Digest atau Query dalam pernyataan. Jika Anda menentukan pernyataan kueri, DBMS_OUTLN menghitung nilai parameter Digest dan Digest_text.

Parameters

Parameter	Deskripsi
Schema_name	Skema database di mana pernyataan SQL dieksekusi.
Digest	Nilai hash digest pernyataan 64-byte yang dihitung berdasarkan parameter Digest_text. Untuk informasi lebih lanjut, lihat STATEMENT_DIGEST().
Position	Nomor tabel tempat index hints berlaku. Nilai parameter Position adalah pengenal numerik yang dimulai dari 1.
Type	Tipe hint. Nilai valid: USE INDEX, FORCE INDEX, atau IGNORE INDEX.
Hint	Daftar nama indeks, seperti ind_1,ind_2.
Scope	Konteks tempat hint berlaku. Nilai valid: FOR GROUP BY FOR ORDER BY FOR JOIN Catatan String kosong menentukan bahwa hint berlaku untuk semua konteks.
Query	Pernyataan SQL asli untuk mana Anda ingin menggunakan outline pernyataan.

Examples

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

Syntax

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

Examples

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

Syntax

dbms_outln.show_outline();

Examples

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)

HIT menunjukkan jumlah hit dalam memori dari outline pernyataan. OVERFLOW menunjukkan jumlah kali blok kueri atau tabel gagal ditemukan.

del_outline

Syntax

dbms_outln.del_outline(<Id>);

Examples

CALL dbms_outln.del_outline(32);

Catatan

Jika outline pernyataan yang ingin Anda hapus tidak ada, kesalahan akan dikembalikan. Anda dapat menjalankan pernyataan SHOW WARNINGS; untuk melihat pesan kesalahan.

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)

Skenario

Gunakan optimizer hint untuk menentukan indeks

Syntax

Catatan

PolarDB for MySQL 5.6 tidak mendukung hints. Anda dapat menentukan indeks dengan menggunakan outline indeks.

/*+ INDEX(table_name idx) */

Examples

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

Tentukan indeks menggunakan index hints

Syntax

Catatan

Dalam versi yang tidak mendukung hints seperti PolarDB for MySQL 5.6, Anda dapat menentukan indeks dengan menggunakan klausa USE atau FORCE INDEX.

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

Parameter Position menentukan tabel tempat outline pernyataan berlaku. Nilai awal parameter ini adalah 1. Parameter Hint berlaku untuk tabel yang ditentukan oleh parameter Position.

Examples

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

Tentukan urutan untuk operasi JOIN

Syntax

Catatan

Anda dapat menggunakan hint untuk memaksa urutan operasi JOIN dalam kueri. Anda tidak perlu menyertakan semua tabel dalam hint. Tabel yang ditentukan akan digabungkan terlebih dahulu. Optimizer secara otomatis menentukan urutan tabel yang tersisa.

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

Examples

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');

Konfigurasikan variabel untuk pernyataan SQL

Syntax

Catatan

Anda dapat menentukan nilai variabel yang hanya berlaku pada pernyataan SQL tertentu.

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

Examples

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

Pengujian

Anda dapat memeriksa apakah outline pernyataan berlaku dengan menggunakan salah satu metode berikut:

Jalankan pernyataan preview_outline. Untuk informasi lebih lanjut, lihat preview_outline.

Contoh pernyataan SQL:

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

Contoh hasil:

+------------+------------------------------------------------------------------+------------+------------+-------+---------------------+
| 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)

Jalankan pernyataan EXPLAIN.

Catatan

Hasil di kolom Extra hanya ditampilkan dalam versi berikut:

PolarDB untuk MySQL 8.0.1 dengan versi revisi 8.0.1.0.34 atau lebih baru.
PolarDB untuk MySQL 8.0.1 dengan versi revisi 8.0.2.2.27 atau lebih baru.

Contoh pernyataan SQL:

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

Contoh hasil:

+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+------------------------------+
| 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)

Contoh pernyataan SQL:

SHOW warnings;

Contoh hasil:

+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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)