How to Use and Manage Statement Outlines - PolarDB - Centro de documentación de Alibaba Cloud

Cuando los planes de ejecución cambian de forma inesperada —por el crecimiento de los datos, la desviación de las estadísticas o las actualizaciones de versión—, normalmente es necesario agregar optimizer hints directamente en el SQL. Si no puede modificar el código de la aplicación, Statement Outline le permite inyectar esos hints sin tocar el SQL. Esta función fija el plan de ejecución de una instrucción específica: compara un digest de SQL normalizado y reescribe la consulta con el hint configurado antes de que el optimizador la procese.

PolarDB proporciona el paquete DBMS_OUTLN, que incluye procedimientos almacenados para agregar, previsualizar, inspeccionar y eliminar outlines.

Después de agregar un outline, compruebe de inmediato que funciona correctamente. Ejecute CALL dbms_outln.preview_outline('<schema>', '<query>') ; si el resultado no está vacío, se confirma que el outline coincidió. También puede ejecutar EXPLAIN sobre la consulta objetivo y revisar si la columna Extra muestra Using outline <ID> .

Requisitos previos

Antes de empezar, asegúrese de contar con lo siguiente:

Un clúster de PolarDB for MySQL que ejecute una de estas versiones:
- PolarDB for MySQL 5.6 con la versión menor 5.6.1.0.36 o posterior
- PolarDB for MySQL 5.7 con la versión menor 5.7.1.0.2 o posterior
- PolarDB for MySQL 8.0.1 con la versión menor 8.0.1.1.1 o posterior
- PolarDB for MySQL 8.0.2

Para consultar la versión de su clúster, consulte View the version number.

PolarDB for MySQL 5.6 no admite optimizer hints. En los clústeres con la versión 5.6, utilice add_index_outline (un index hint).

Cómo funciona

Statement Outline intercepta una instrucción SQL coincidente y la reescribe con el hint configurado antes de que el optimizador la procese. La coincidencia se basa en un digest normalizado del texto SQL, que de manera opcional se limita a una base de datos específica:

Si Schema_name tiene un valor asignado, tanto el nombre de la base de datos como el digest de SQL deben coincidir con la regla del outline.
Si Schema_name está vacío, solo debe coincidir el digest de SQL.

Todos los hints se almacenan de forma persistente en la tabla del sistema mysql.outline y se cargan en memoria durante el inicio.

Elija un tipo de outline

	Outline de optimizer hint	Outline de index hint
Mecanismo	Inyecta hints `/+ ... /` dentro de la consulta	Agrega `USE INDEX`, `FORCE INDEX` o `IGNORE INDEX` a una referencia de tabla
Controla	El orden de unión, el algoritmo de unión, el comportamiento del bloque de consulta y las variables de sesión	El índice que utiliza el optimizador para una tabla específica
Compatibilidad con MySQL 5.6	No	Sí
Procedimiento	`add_optimizer_outline`	`add_index_outline`

Inicio rápido

A continuación se muestran las operaciones de outline más comunes. Todos los ejemplos utilizan la sintaxis CALL dbms_outln.<procedure>().

Fije una consulta con un optimizer hint

Fuerce un índice específico mediante un optimizer hint:

CALL dbms_outln.add_optimizer_outline(
  'test',                          -- Schema_name
  '/*+ INDEX(t1 i_a) */',          -- Hint string
  'SELECT test.t1.a AS a FROM test.t1'  -- Original SQL
);

Compruebe que el outline coincidió:

CALL dbms_outln.preview_outline('test', 'SELECT test.t1.a AS a FROM test.t1');

Fije una consulta con un index hint

Utilice add_index_outline para aplicar un hint USE INDEX. Este método es compatible con los clústeres de MySQL 5.6:

CALL dbms_outln.add_index_outline(
  'outline_db',   -- Schema_name
  '',             -- Digest (leave blank; the system calculates it from Query)
  1,              -- Position: the 1st table in the SQL
  'USE INDEX',    -- Type
  'ind_1',        -- Index name(s)
  '',             -- Scope (blank = all query types)
  "SELECT * FROM t1 WHERE t1.col1 = 1 AND t1.col2 = 'xpchild'"  -- Original SQL
);

Fije el orden de unión

Obligue a que it1 e it2 se unan primero. El optimizador se encarga de las tablas restantes:

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

Establezca una variable de sesión para una sola instrucción

Aplique hints SET_VAR para anular una variable de sesión durante la ejecución de una única instrucción:

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

Dirija el tráfico a nodos de solo lectura de almacenamiento por filas o columnstore

En los clústeres con nodos de solo lectura de In-Memory Columnar Index (IMCI), fuerce el enrutamiento de instrucciones con SET_VAR:

-- Force execution on columnstore (IMCI) read-only nodes
CALL dbms_outln.add_optimizer_outline('test', '/*+ SET_VAR(cost_threshold_for_imci=0) */', 'SELECT test.t1.a AS a FROM test.t1');

-- Force execution on row-store read-only nodes
CALL dbms_outln.add_optimizer_outline('test', '/*+ SET_VAR(use_imci_engine=OFF) */', 'SELECT test.t1.a AS a FROM test.t1');

Gestione los outlines

Agregue un outline de optimizer hint

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

Parámetro	Descripción
`Schema_name`	El nombre de la base de datos. Déjelo vacío para que la coincidencia se base únicamente en el digest.
`Hint`	La cadena completa del optimizer hint, por ejemplo, `/+ MAX_EXECUTION_TIME(1000) /`.
`Query`	La instrucción SQL original que desea fijar.

Cuando Query contiene cadenas entre comillas dobles, encierre todo el valor de Query entre comillas dobles y use comillas simples en el interior, o viceversa. Un outline creado con comillas simples en Query coincide con el SQL sin importar si lo ejecuta con comillas simples o dobles.

Ejemplo: fije un límite de tiempo de ejecución en una consulta:

SQL original:

SELECT * FROM t1 WHERE name = "Tom";

Normalice las comillas para el outline:

SELECT * FROM t1 WHERE name = 'Tom';

Agregue el outline:

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

Agregue un outline de index hint

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

Parámetro	Descripción
`Schema_name`	El nombre de la base de datos.
`Digest`	Una cadena hash de 64 bytes que se obtiene al calcular el hash de `Digest_text`. Déjela vacía para que el sistema la calcule a partir de `Query`. Consulte STATEMENT_DIGEST().
`Position`	La posición de la tabla a la que aplica el hint, con base 1. Para la N-ésima tabla del texto SQL, establezca `Position` en N.
`Type`	El tipo de index hint: `USE INDEX`, `FORCE INDEX` o `IGNORE INDEX`.
`Hint`	Una lista de nombres de índices separados por comas, por ejemplo, `ind_1,ind_2`.
`Scope`	Restringe el hint a un tipo de consulta: `FOR JOIN`, `FOR ORDER BY` o `FOR GROUP BY`. Déjelo vacío para aplicarlo a todos los tipos de consulta.
`Query`	La instrucción SQL original que desea fijar.

Ejemplo:

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

Previsualice un outline

preview_outline muestra qué outlines coinciden con una consulta determinada sin ejecutarla. Utilícelo para validar un outline antes de implementarlo en producción.

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

Ejemplo:

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

Salida:

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

Consulte los outlines activos

show_outline enumera todos los outlines cargados actualmente en memoria, junto con los conteos de aciertos y desbordamientos.

CALL dbms_outln.show_outline();

Salida:

+------+------------+------------------------------------------------------------------+-----------+-------+------+-------------------------------------------------------+------+----------+-------------------------------------------------------------------------------------+
| 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` = ? |
+------+------------+------------------------------------------------------------------+-----------+-------+------+-------------------------------------------------------+------+----------+-------------------------------------------------------------------------------------+

HIT: el número de veces que este outline coincidió y se aplicó.
OVERFLOW: el número de veces que el outline no encontró el bloque de consulta o la tabla objetivo en la instrucción.

Elimine un outline

CALL dbms_outln.del_outline(<Id>);

Pase el valor de ID obtenido en show_outline. El outline se elimina de inmediato tanto de la memoria como de la tabla del sistema mysql.outline.

Ejemplo:

CALL dbms_outln.del_outline(32);

Si el ID no existe, el sistema registra una advertencia en lugar de un error:

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

Compruebe que se aplicó un outline

Después de agregar un outline, confirme que funciona correctamente con cualquiera de estos métodos.

Método 1: preview_outline (recomendado)

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

Un conjunto de resultados no vacío indica que el outline coincidió.

Método 2: EXPLAIN

Ejecute EXPLAIN sobre la consulta objetivo. Si se aplicó el outline, la columna Extra incluye Using outline <ID>.

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; Using outline 1 |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+------------------------------+

El texto Using outline <ID> en la columna Extra aparece únicamente en:

PolarDB for MySQL 8.0.1 con la versión menor 8.0.1.0.34 o posterior

PolarDB for MySQL 8.0.2 con la versión menor 8.0.2.2.27 o posterior

Para ver el SQL reescrito, ejecute SHOW WARNINGS después de EXPLAIN:

SHOW WARNINGS;
+-------+------+----------------------------------------------------------+
| Level | Code | Message                                                  |
+-------+------+----------------------------------------------------------+
| Note  | 1003 | /* select#1 */ SELECT `outline_db`.`t1`.`id` AS `id`,... USE INDEX (`ind_1`) WHERE ... |
+-------+------+----------------------------------------------------------+

Sharding Outline

En escenarios de fragmentación de tablas, las tablas físicas siguen un patrón de sufijo numérico: t_001, t_002, ..., t_999. Un Statement Outline estándar coincide solo con el nombre exacto de la tabla, por lo que requiere una regla independiente por cada tabla. En una configuración de fragmentación grande, esto resulta difícil de gestionar.

Sharding Outline resuelve este problema al tratar los números finales como un patrón comodín. Reconoce t_1, t_2 y t_100 como el único patrón t_?, de modo que una sola regla cubre todas las tablas coincidentes.

	Outline estándar	Sharding Outline
Coincidencia de nombre de tabla	Coincidencia exacta, por ejemplo, `t_1`	Patrón comodín, por ejemplo, `t_?`
Alcance	Una tabla específica	Todas las tablas fragmentadas con un patrón de nomenclatura consistente
Reglas necesarias	Una por cada tabla fragmentada	Una regla para todas las tablas coincidentes
Mantenimiento	Alto: creación, actualización y eliminación por lotes	Bajo: una sola regla gestiona todas las tablas coincidentes

Versiones compatibles:

PolarDB for MySQL 8.0.1 con la versión menor 8.0.1.1.54 o posterior
PolarDB for MySQL 8.0.2 con la versión menor 8.0.2.2.33 o posterior

Habilite Sharding Outline

En la consola de PolarDB, vaya a Parameter Settings y establezca loose_outline_templated_digest_for_sharding_table en ON. Para obtener más información, consulte Set parameters.
Llame a add_optimizer_outline_sharding y pase cualquier nombre de tabla fragmentada como Query. El procedimiento convierte automáticamente los números finales en el patrón t_?.

CALL dbms_outln.add_optimizer_outline_sharding(
  'test',                            -- Schema_name
  '',                                -- Digest (leave blank)
  1,                                 -- Position
  '/*+ MAX_EXECUTION_TIME(1000) */', -- Hint string
  "SELECT t_1.c_1 FROM t_1"         -- Any sharded table name works: t_1, t_2, t_100
);

Tras la ejecución, la regla se aplica a todas las tablas que coinciden con t_?, incluidas t_1, t_2 y t_100.

Parámetros

Configure el comportamiento de Statement Outline en la consola de PolarDB, dentro de Parameter Settings. Consulte Set parameters.

Parámetro	Alcance	Descripción
`loose_opt_outline_enabled`	Global	Habilita o deshabilita Statement Outline. `ON` (predeterminado) u `OFF`.
`loose_outline_templated_digest_for_sharding_table`	Session	Habilita o deshabilita Sharding Outline. `ON` (predeterminado) u `OFF`. Requiere PolarDB for MySQL 8.0.1 con la versión menor 8.0.1.1.54 o posterior, o bien 8.0.2 con la versión menor 8.0.2.2.33 o posterior.

Apéndice: tabla del sistema de Statement Outline

PolarDB almacena todas las reglas de outline en mysql.outline. Esta tabla se crea automáticamente durante el inicio.

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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Statement outline'

Campo	Descripción
`Id`	El ID único del outline.
`Schema_name`	El nombre de la base de datos. Si está vacío, la coincidencia se basa únicamente en el digest.
`Digest`	Cadena hash de 64 bytes que se obtiene al calcular el hash de `Digest_text`. Consulte STATEMENT_DIGEST().
`Digest_text`	La forma normalizada de la instrucción SQL que se usa para calcular el digest.
`Type`	`OPTIMIZER` para optimizer hints. `USE INDEX`, `FORCE INDEX` o `IGNORE INDEX` para index hints.
`Scope`	El alcance del index hint: `FOR JOIN`, `FOR ORDER BY` o `FOR GROUP BY`. Una cadena vacía aplica a todos los tipos de consulta. Se usa solo para index hints.
`State`	Indica si la regla está activa. `Y` (predeterminado) o `N`.
`Position`	Para optimizer hints: la posición del bloque de consulta al que apunta el hint, con base 1. Para index hints: la posición de la tabla en el texto SQL, con base 1.
`Hint`	Para optimizer hints: la cadena completa del hint, por ejemplo, `/+ MAX_EXECUTION_TIME(1000) /`. Para index hints: una lista de nombres de índices separados por comas, por ejemplo, `ind_1,ind_2`.