Sintaxe, parâmetros e exemplos de statement outline - ApsaraDB RDS: Central de Documentação do Alibaba Cloud

Em ambientes de produção, o plano de execução de uma instrução SQL pode mudar com frequência, causando instabilidade no banco de dados. A Alibaba Cloud utiliza optimizer hints e index hints — um método chamado statement outline — para estabilizar os planos de execução do MySQL. Utilize comandos SQL e o toolkit DBMS_OUTLN para aplicar statement outlines rapidamente.

Pré-requisitos

A instância RDS deve executar uma das seguintes versões do MySQL:

MySQL 8.4
MySQL 8.0
MySQL 5.7

Funcionamento

O statement outline suporta todos os tipos de hints disponíveis nas versões oficiais 8.4, 8.0 e 5.7 do MySQL. Esses hints dividem-se em duas categorias:

Optimizer hint

Classificados por escopo e objeto alvo, os optimizer hints incluem hints de nível global, hints de nível de tabela ou índice e hints de ordem de junção. Para mais informações, consulte a documentação do MySQL.
Index hint

Os index hints são classificados por tipo e escopo. Para mais detalhes, veja a documentação do MySQL.

Tabela de statement outline

O AliSQL inclui uma tabela de sistema integrada chamada outline para armazenar hints. Essa tabela é criada automaticamente na inicialização. A instrução CREATE TABLE abaixo serve como referência:

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'

A tabela a seguir descreve as colunas.

Parâmetro	Descrição
Id	ID do outline.
Schema_name	Nome do banco de dados.
Digest	String de hash de 64 bytes calculada a partir de Digest_text.
Digest_text	Digest da instrução SQL.
Type	Para optimizer hint, o valor é `OPTIMIZER`. Para index hint, o valor pode ser `USE INDEX`, `FORCE INDEX` ou `IGNORE INDEX`.
Scope	Obrigatório apenas para index hints. Valores válidos: FOR GROUP BY FOR ORDER BY FOR JOIN Uma string vazia indica que o index hint se aplica a todos os tipos.
State	Indica se o statement outline está ativado.
Position	Para optimizer hints, Position especifica o bloco de consulta. Como todos os optimizer hints devem ser aplicados a um bloco de consulta, Position começa em 1. Se um hint se aplicar ao enésimo bloco de consulta em uma instrução, Position será N. Para index hints, Position especifica a posição de uma tabela, também começando em 1. Se um hint se aplicar à enésima tabela em uma instrução, Position será N.
Hint	Para optimizer hints, Hint é a string completa do hint, como `/+ MAX_EXECUTION_TIME(1000) /`. Para index hints, Hint é uma lista de nomes de índices separados por vírgula, como `ind_1,ind_2`.

Gerenciamento de statement outlines

O AliSQL fornece seis stored procedures no DBMS_OUTLN para gerenciar statement outlines:

add_optimizer_outline

Adiciona um optimizer hint. Execute o seguinte comando:

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

Nota

Especifique Digest ou Query (a instrução SQL original). Se você fornecer Query, o DBMS_OUTLN calculará Digest e Digest_text.

Exemplo:

mysql> call dbms_outln.add_optimizer_outline("outline_db", '', 1, '/*+ MAX_EXECUTION_TIME(1000) */',
                                      "select * from t1 where id = 1");

add_index_outline

Adiciona um index hint. Execute o seguinte comando:

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

Nota

Especifique Digest ou Query (a instrução SQL original). Se você fornecer Query, o DBMS_OUTLN calculará Digest e Digest_text.

Exemplo:

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

Visualiza os outlines correspondentes a uma consulta, útil para validação manual. Execute o seguinte comando:

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

Nota

O valor do parâmetro max_digest_length pode afetar a precisão da correspondência do statement outline. Caso a correspondência não ocorra corretamente, aumente o valor desse parâmetro.

Exemplo:

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

Exibe os statement outlines carregados atualmente na memória, juntamente com suas estatísticas de acerto. Execute o seguinte comando:

dbms_outln.show_outline();

Exemplo:

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)

A tabela a seguir descreve as colunas HIT e OVERFLOW.

Parâmetro	Descrição
HIT	Número de vezes que este statement outline foi correspondido.
OVERFLOW	Quantidade de falhas deste statement outline ao localizar o bloco de consulta ou tabela correspondente.

del_outline

Exclui um statement outline da memória e da tabela outline. Execute o seguinte comando:

dbms_outln.del_outline(<Id>);

Exemplo:

mysql> call dbms_outln.del_outline(32);

Nota

Se o outline que você tentar excluir não existir, o sistema retornará um aviso. Execute show warnings; para visualizar os detalhes.

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

Ao modificar diretamente a tabela outline, execute este comando para aplicar as alterações. Use o seguinte comando:

dbms_outln.flush_outline();

Exemplo:

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)

Verificação de statement outlines

Utilize um dos dois métodos abaixo para verificar se um statement outline está ativo:

Use preview_outline para verificar a correspondência.

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)

Utilize EXPLAIN para visualizar o plano de execução.

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)