ApsaraDB RDS:Outline pernyataan

AliSQL menyertakan tabel sistem bawaan bernama outline untuk menyimpan hint. Tabel ini dibuat secara otomatis saat startup. Pernyataan CREATE TABLE berikut disediakan sebagai referensi:

​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'​

AliSQL menyediakan enam prosedur tersimpan berikut dalam DBMS_OUTLN untuk mengelola outline pernyataan:

• add_optimizer_outline

  Menambahkan petunjuk pengoptimal. Gunakan perintah berikut:

  dbms_outln.add_optimizer_outline('<Schema_name>','<Digest>','<query_block>','<hint>','<query>');

  Catatan

  Anda dapat menentukan Digest atau Query (Pernyataan SQL asli). Jika Anda memberikan Query, DBMS_OUTLN akan menghitung 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 petunjuk indeks. Gunakan perintah berikut:

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

  Catatan

  Anda dapat menentukan Digest atau Query (Pernyataan SQL asli). Jika Anda memberikan Query, DBMS_OUTLN akan menghitung 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

  Menampilkan pratinjau outline yang sesuai dengan suatu kueri. Ini berguna untuk validasi manual. Gunakan perintah berikut:

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

  Catatan

  Nilai parameter max_digest_length dapat memengaruhi akurasi pencocokan outline pernyataan. Jika suatu outline pernyataan tidak dicocokkan dengan benar, 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 saat ini dimuat di memori, beserta statistik hit-nya. Gunakan perintah berikut:

  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 kolom HIT dan OVERFLOW.

  Parameter	Deskripsi
  HIT	Jumlah kali outline pernyataan ini dicocokkan.
  OVERFLOW	Jumlah kali outline pernyataan ini gagal menemukan blok kueri atau tabel yang sesuai.

• del_outline

  Menghapus outline pernyataan dari memori dan tabel outline. Gunakan perintah berikut:

  dbms_outln.del_outline(<Id>);

  Contoh:

  ​mysql> call dbms_outln.del_outline(32);

  Catatan

  Jika outline yang ingin Anda hapus tidak ada, sistem akan mengembalikan peringatan. Anda dapat menjalankan show warnings; untuk melihat detailnya.

  ​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 langsung memodifikasi tabel outline, Anda harus menjalankan perintah ini agar perubahan diterapkan. Gunakan perintah berikut:

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

Anda dapat menggunakan salah satu dari dua metode berikut untuk memverifikasi bahwa outline pernyataan aktif:

• Gunakan preview_outline untuk memeriksa kecocokan.

  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)

• Gunakan EXPLAIN untuk melihat rencana eksekusi.

  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)