Gunakan pernyataan outline untuk menstabilkan rencana eksekusi SQL - ApsaraDB RDS

Dalam lingkungan produksi, rencana eksekusi untuk Pernyataan SQL sering berubah, menyebabkan ketidakstabilan database. AliSQL menyediakan fitur outline pernyataan yang memungkinkan Anda menggunakan optimizer hints atau index hints untuk menstabilkan rencana eksekusi Pernyataan SQL. AliSQL juga menyediakan toolkit DBMS_OUTLN untuk pengelolaan outline pernyataan secara efisien.

Prasyarat

Instansi RDS harus menjalankan salah satu versi mesin utama berikut:

MySQL 8.0
MySQL 5.7

Deskripsi Fitur

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

Optimizer hints
Optimizer hints diklasifikasikan menjadi optimizer hints tingkat global, tabel, indeks, dan urutan join berdasarkan ruang lingkup dan objek hint yang berlaku. Untuk informasi lebih lanjut, lihat Optimizer Hints.
Index hints
Index hints diklasifikasikan berdasarkan jenis dan ruang lingkup. Untuk informasi lebih lanjut, lihat Index Hints.

Tabel Outline

AliSQL 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='Outline Pernyataan'

Tabel berikut menjelaskan parameter dalam pernyataan tersebut.

Parameter	Deskripsi
Id	ID dari setiap outline pernyataan.
Schema_name	Nama database.
Digest	String hash 64-byte yang diperoleh dari parameter Digest_text.
Digest_text	Ringkasan 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 ke mana hint berlaku. Parameter ini hanya diperlukan untuk index hints. Nilai valid: FOR GROUP BY FOR ORDER BY FOR JOIN String kosong menentukan bahwa hint berlaku untuk semua konteks.
State	Menentukan apakah akan mengaktifkan hint.
Position	Untuk optimizer hints, parameter Position adalah posisi kata kunci dalam blok query karena semua optimizer hints diterapkan pada blok query. Parameter Position menentukan nomor urut kata kunci tempat hints diterapkan. Nilai parameter Position dimulai dari 1. Untuk index hints, parameter Position adalah posisi tabel. Parameter Position menentukan nomor urut tabel tempat hints diterapkan. Nilainya dimulai dari 1.
Hint	Untuk optimizer hints, parameter Hint menentukan string hint terintegrasi. Contoh: `/+ MAX_EXECUTION_TIME(1000) /`. Untuk index hints, parameter Hint menentukan daftar nama indeks. Contoh: `ind_1,ind_2`.

Kelola Outline Pernyataan

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

add_optimizer_outline
Menambahkan optimizer hint. Contoh perintah:
```
dbms_outln.add_optimizer_outline('<Schema_name>','<Digest>','<query_block>','<hint>','<query>');
```
Catatan
Anda dapat mengonfigurasi salah satu parameter Digest atau query dalam pernyataan. Jika Anda memasukkan pernyataan query untuk parameter query, DBMS_OUTLN menghitung nilai parameter Digest dan Digest_text.
Contoh:
```
mysql> call dbms_outln.add_optimizer_outline("outline_db", '', 1, '/*+ MAX_EXECUTION_TIME(1000) */',
                                      "select * from t1 where id = 1");
```

add_index_outline

Menambahkan index hint. Contoh perintah:

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

Catatan

Anda dapat mengonfigurasi salah satu parameter Digest atau query dalam pernyataan. Jika Anda memasukkan pernyataan query untuk parameter Query, DBMS_OUTLN menghitung nilai parameter Digest dan Digest_text.

Contoh:

mysql> 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

Memeriksa apakah pernyataan SQL cocok dengan outline yang ada. Ini berguna untuk validasi manual. Contoh perintah:

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

Catatan

Nilai parameter max_digest_length dapat memengaruhi pencocokan antara pernyataan SQL dan outline. Jika pernyataan SQL cocok dengan outline yang salah, tingkatkan nilai parameter ini.

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.00 sec)

show_outline

Menampilkan outline pernyataan yang terkena di memori. Contoh perintah:

dbms_outln.show_outline();

Contoh:

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

Tabel berikut menjelaskan parameter HIT dan OVERFLOW.

Parameter	Deskripsi
HIT	Jumlah kali outline pernyataan menemukan blok query tujuan atau tabel.
OVERFLOW	Jumlah kali outline pernyataan tidak menemukan blok query tujuan atau tabel.

del_outline

Menghapus outline pernyataan dari memori atau tabel. Contoh perintah:

dbms_outln.del_outline(<Id>);

Contoh:

mysql> call dbms_outln.del_outline(32);

Catatan

Jika outline yang ingin Anda hapus tidak ada, sistem melaporkan kesalahan. Anda dapat mengeksekusi pernyataan SHOW WARNINGS; untuk melihat pesan kesalahan.

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

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

flush_outline

Jika Anda memodifikasi outline pernyataan dalam tabel outline, Anda harus mengeksekusi pernyataan berikut agar outline pernyataan baru berlaku. Contoh perintah:

dbms_outln.flush_outline();

Contoh:

mysql> update mysql.outline set Position = 1 where Id = 18;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> call dbms_outln.flush_outline(); 
Query OK, 0 rows affected (0.01 sec)

Pengujian Fitur

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

Eksekusi pernyataan preview_outline.

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)

Eksekusi pernyataan EXPLAIN.

mysql> explain select * from t1 where t1.col1 =1 and t1.col2 ='xpchild';
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| 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 |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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)