Statement outline syntax, parameters, and examples - ApsaraDB RDS

Dans un environnement de production, le plan d'exécution d'une instruction SQL peut changer fréquemment, entraînant une instabilité de la base de données. Alibaba Cloud utilise des indications pour l'optimiseur et des indications d'index — une méthode appelée « statement outline » — afin de stabiliser les plans d'exécution MySQL. Utilisez des commandes SQL et la boîte à outils DBMS_OUTLN pour appliquer rapidement des plans de requête.

Prérequis

L'instance RDS exécute l'une des versions MySQL suivantes :

MySQL 8.4
MySQL 8.0
MySQL 5.7

Fonctionnement

Le plan de requête prend en charge tous les types d'indications disponibles dans les versions officielles MySQL 8.4, 8,0 et 5,7. Ces indications se répartissent dans les deux catégories suivantes :

Indication pour l'optimiseur

Selon leur portée et l'objet cible, les indications pour l'optimiseur incluent des indications au niveau global, au niveau table ou index, ainsi que des indications sur l'ordre de jointure. Pour plus d'informations, consultez la documentation MySQL.
Indication d'index

Les indications d'index sont classées par type et par portée. Pour plus d'informations, consultez la documentation MySQL.

Table du plan de requête

AliSQL inclut une table système intégrée nommée outline pour stocker les indications. Cette table est créée automatiquement au démarrage. L'instruction CREATE TABLE suivante est fournie à titre de référence :

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'

Le tableau suivant décrit les colonnes.

Paramètre	Description
Id	ID du plan de requête.
Schema_name	Nom de la base de données.
Digest	Chaîne de hachage de 64 octets calculée à partir de Digest_text.
Digest_text	Résumé de l'instruction SQL.
Type	Pour une indication destinée à l'optimiseur, la valeur est `OPTIMIZER`. Pour une indication d'index, la valeur peut être `USE INDEX`, `FORCE INDEX` ou `IGNORE INDEX`.
Scope	Requis uniquement pour les indications d'index. Valeurs valides : FOR GROUP BY FOR ORDER BY FOR JOIN Une chaîne vide indique que l'indication d'index s'applique à tous les types.
State	Indique si le plan de requête est activé.
Position	Pour une indication destinée à l'optimiseur, Position spécifie le bloc de requête. Étant donné que toutes les indications pour l'optimiseur doivent s'appliquer à un bloc de requête, Position commence à 1. Si une indication s'applique au Nième bloc de requête d'une instruction, Position est N. Pour une indication d'index, Position spécifie la position d'une table. Elle commence également à 1. Si une indication s'applique à la Nième table d'une instruction, Position est N.
Hint	Pour une indication destinée à l'optimiseur, Hint correspond à la chaîne complète de l'indication, par exemple `/+ MAX_EXECUTION_TIME(1000) /`. Pour une indication d'index, Hint est une liste de noms d'index séparés par des virgules, par exemple `ind_1,ind_2`.

Gestion des plans de requête

AliSQL fournit les six procédures stockées suivantes dans DBMS_OUTLN pour gérer les plans de requête :

add_optimizer_outline

Ajoute une indication pour l'optimiseur. Utilisez la commande suivante :

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

Remarque

Spécifiez soit Digest, soit Query (l'instruction SQL d'origine). Si vous fournissez Query, DBMS_OUTLN calcule Digest et Digest_text.

Exemple :

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

add_index_outline

Ajoute une indication d'index. Utilisez la commande suivante :

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

Remarque

Spécifiez soit Digest, soit Query (l'instruction SQL d'origine). Si vous fournissez Query, DBMS_OUTLN calcule Digest et Digest_text.

Exemple :

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

Prévisualise les plans correspondant à une requête. Cette fonction est utile pour la validation manuelle. Utilisez la commande suivante :

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

Remarque

La valeur du paramètre max_digest_length peut affecter la précision de la mise en correspondance des plans de requête. Si un plan de requête n'est pas correctement mis en correspondance, augmentez la valeur de ce paramètre.

Exemple :

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

Affiche les plans de requête actuellement chargés en mémoire, ainsi que leurs statistiques d'utilisation. Utilisez la commande suivante :

dbms_outln.show_outline();

Exemple :

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)

Le tableau suivant décrit les colonnes HIT et OVERFLOW.

Paramètre

Description

HIT

Nombre de fois où ce plan de requête a été mis en correspondance.

OVERFLOW

Nombre de fois où ce plan de requête n'a pas trouvé le bloc de requête ou la table correspondante.

del_outline

Supprime un plan de requête de la mémoire et de la table outline. Utilisez la commande suivante :

dbms_outln.del_outline(<Id>);

Exemple :

mysql> call dbms_outln.del_outline(32);

Remarque

Si le plan que vous tentez de supprimer n'existe pas, le système renvoie un avertissement. Exécutez show warnings; pour afficher les détails.

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

Si vous modifiez directement la table outline, exécutez cette commande pour appliquer les modifications. Utilisez la commande suivante :

dbms_outln.flush_outline();

Exemple :

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)

Vérification des plans de requête

Utilisez l'une des deux méthodes suivantes pour vérifier qu'un plan de requête est actif :

Utilisez preview_outline pour vérifier la correspondance.

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)

Utilisez EXPLAIN pour afficher le plan d'exécution.

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)