Análisis del rendimiento de consultas basadas en IMCI - PolarDB - Centro de documentación de Alibaba Cloud

PolarDB for MySQL recopila estadísticas de ejecución por operador para las consultas que procesa el motor In-Memory Column Index (IMCI). Estas estadísticas —cantidad real de filas, costos reales y tiempo transcurrido por operador— se almacenan junto con el plan EXPLAIN. Utilícelas para identificar qué operadores consumen más tiempo y medir el impacto de las optimizaciones.

Requisitos previos

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

Un clúster de PolarDB for MySQL que ejecute la versión 8.0.1.1.42 o posterior. Para comprobar su versión, consulte Query an engine version.

Habilitar el perfilado

Establezca el parámetro de sesión imci_analyze_query

en ON para habilitar el perfilado. Una vez que se ejecuta una consulta, los datos de perfilado correspondientes quedan disponibles en

information_schema.imci_sql_profiling.

Parámetro	Nivel	Valor predeterminado	Descripción
`imci_analyze_query`	Sesión	OFF	Captura las estadísticas de ejecución por operador de la consulta IMCI más reciente. Establézcalo en ON para habilitarlo.

imci_sql_profiling almacena los datos de la última instrucción SQL ejecutada mediante IMCI. Consulte la tabla inmediatamente después de que finalice la consulta objetivo.

Funcionamiento

Cuando imci_analyze_query está en ON, PolarDB registra las siguientes métricas para cada operador del plan de ejecución IMCI:

Columna	Descripción
`ID`	Identificador del operador, coincide con la salida de EXPLAIN
`Operator`	Tipo de operador (por ejemplo, Hash Join, Table Scan)
`Name`	Nombre de la tabla, si aplica
`A-Rows`	Cantidad real de filas que produjo el operador
`A-Cost`	Costo real
`Execution Time(s)`	Tiempo transcurrido invertido en este operador
`Extra Info`	Condiciones de unión, claves de agrupación y uso de recursos (CPU/memoria)

Interpretación de la salida de perfilado

Utilice los siguientes patrones para diagnosticar problemas de rendimiento:

Señal	Causa probable	Acción
El valor de `Execution Time(s)` de un operador representa una proporción alta del tiempo total de la consulta	Operador con cuello de botella	Concentre la optimización en este operador
`A-Rows` es mucho mayor que `E-Rows` del plan EXPLAIN	Error en la estimación de filas; podría indicar una unión explosiva (las filas de salida superan por mucho a las de entrada)	Revise las condiciones de unión y compruebe si faltan filtros o estadísticas
La columna `Extra Info` de la fila 1 (`Select Statement`) muestra un valor de `real_query_mem` cercano o superior a `max_query_mem`	Presión de memoria	Reduzca el paralelismo o procese los datos en lotes más pequeños

Para ordenar los operadores por tiempo de ejecución y localizar directamente el cuello de botella, ejecute lo siguiente:

/*ROUTE_TO_LAST_USED*/SELECT ID, Operator, Name, `A-Rows`, `Execution Time(s)`
FROM information_schema.imci_sql_profiling
ORDER BY `Execution Time(s)` DESC;

Ejemplos

Ejemplo de consulta sencilla

Este ejemplo perfila una consulta de unión y agregación sobre un conjunto de datos TPC-H (Transaction Processing Performance Council Benchmark H).

SELECT l_shipmode, COUNT(*) FROM lineitem, orders WHERE l_orderkey = o_orderkey GROUP BY l_shipmode;

Paso 1: Revise el plan de ejecución.

EXPLAIN SELECT l_shipmode, COUNT(*) FROM lineitem, orders WHERE l_orderkey = o_orderkey GROUP BY l_shipmode;

Salida de ejemplo:

+----+------------------------+----------+--------+----------+---------------------------------------------------------------+
| ID | Operator               | Name     | E-Rows | E-Cost   | Extra Info                                                    |
+----+------------------------+----------+--------+----------+---------------------------------------------------------------+
|  1 | Select Statement       |          |        |          | IMCI Execution Plan (max_dop = 1, max_query_mem = unlimited)  |
|  2 | └─Hash Groupby         |          | 6      | 51218.50 | Group Key: lineitem.l_shipmode                                |
|  3 |   └─Compute Scalar     |          | 1869   | 50191.00 |                                                               |
|  4 |     └─Hash Join        |          | 1869   | 31000.00 | Join Cond: lineitem.l_orderkey = orders.o_orderkey            |
|  5 |       ├─Table Scan     | lineitem | 2000   | 0.00     |                                                               |
|  6 |       └─Table Scan     | orders   | 2000   | 0.00     |                                                               |
+----+------------------------+----------+--------+----------+---------------------------------------------------------------+

Paso 2: Habilite el perfilado y ejecute la consulta.

SET imci_analyze_query = ON;

SELECT l_shipmode, COUNT(*) FROM lineitem, orders WHERE l_orderkey = o_orderkey GROUP BY l_shipmode;

Salida de ejemplo:

+------------+----------+
| l_shipmode | COUNT(*) |
+------------+----------+
| REG AIR    |      283 |
| SHIP       |      269 |
| FOB        |      299 |
| RAIL       |      289 |
| TRUCK      |      314 |
| MAIL       |      274 |
| AIR        |      272 |
+------------+----------+
7 rows in set (0.05 sec)

Paso 3: Obtenga los datos de perfilado.

Use la sugerencia /*ROUTE_TO_LAST_USED*/ para dirigir esta consulta al mismo nodo donde se ejecutó la consulta IMCI. Sin ella, PolarProxy podría enrutarla a otro nodo y no devolver resultados.

/*ROUTE_TO_LAST_USED*/SELECT * FROM information_schema.imci_sql_profiling;

Salida de ejemplo:

+----+------------------------+----------+--------+----------+-------------------+--------------------------------------------------------------------------------------------------------+
| ID | Operator               | Name     | A-Rows | A-Cost   | Execution Time(s) | Extra Info                                                                                             |
+----+------------------------+----------+--------+----------+-------------------+--------------------------------------------------------------------------------------------------------+
|  1 | Select Statement       |          |      0 | 52609.51 |                 0 | IMCI Execution Plan (max_dop = 1, real_dop = 1, max_query_mem = unlimited, real_query_mem = unlimited) |
|  2 | └─Hash Groupby         |          |      7 |  52609.5 |             0.002 | Group Key: lineitem.l_shipmode                                                                         |
|  3 |   └─Compute Scalar     |          |   2000 |    51501 |                 0 |                                                                                                        |
|  4 |     └─Hash Join        |          |   2000 |    31000 |             0.007 | Join Cond: lineitem.l_orderkey = orders.o_orderkey                                                     |
|  5 |       ├─Table Scan     | lineitem |   2000 |        0 |             0.001 |                                                                                                        |
|  6 |       └─Table Scan     | orders   |   2000 |        0 |                 0 |                                                                                                        |
+----+------------------------+----------+--------+----------+-------------------+--------------------------------------------------------------------------------------------------------+

La columna Extra Info muestra la condición de unión del operador Hash Join y la clave de agrupación del operador Hash Groupby. En la primera fila, esta misma columna presenta el uso real de CPU y memoria (real_dop, real_query_mem) junto con los valores estimados (max_dop, max_query_mem), lo cual le permite compararlos para detectar presión sobre los recursos.

Paso 4: (Opcional) Agregue las métricas de perfilado.

Ejecute consultas de agregación directamente sobre imci_sql_profiling. Por ejemplo, para obtener el tiempo total de ejecución:

/*ROUTE_TO_LAST_USED*/SELECT SUM(`Execution Time(s)`) AS TOTAL_TIME FROM information_schema.imci_sql_profiling;

Salida de ejemplo:

+----------------------+
| TOTAL_TIME           |
+----------------------+
| 0.010000000000000002 |
+----------------------+
1 row in set (0.00 sec)

La duración total de ejecución de la instrucción SQL es de 10 ms.

Ejemplo de consulta compleja

Este ejemplo muestra cómo usar los datos de perfilado para identificar un cuello de botella, aplicar una optimización y comprobar la mejora. La consulta se ejecuta contra un conjunto de datos TPC-H SF100 (factor de escala 100).

SELECT
    c_name,
    sum(l_quantity)
FROM
    customer,
    orders,
    lineitem
WHERE
    o_orderkey IN (
        SELECT
            l_orderkey
        FROM
            lineitem
        WHERE
            l_partkey > 18000000
    )
    AND c_custkey = o_custkey
    AND o_orderkey = l_orderkey
GROUP BY
    c_name
ORDER BY
    c_name
LIMIT 10;

Diagnóstico del cuello de botella

Paso 1: Revise el plan de ejecución estimado.

EXPLAIN SELECT
    c_name,
    sum(l_quantity)
FROM
    customer,
    orders,
    lineitem
WHERE
    o_orderkey IN (
        SELECT
            l_orderkey
        FROM
            lineitem
        WHERE
            l_partkey > 18000000
    )
    AND c_custkey = o_custkey
    AND o_orderkey = l_orderkey
GROUP BY
    c_name
ORDER BY
    c_name
LIMIT 10;

Salida de ejemplo:

+----+----------------------------------+----------+-----------+------------+---------------------------------------------------------------+
| ID | Operator                         | Name     | E-Rows    | E-Cost     | Extra Info                                                    |
+----+----------------------------------+----------+-----------+------------+---------------------------------------------------------------+
|  1 | Select Statement                 |          |           |            | IMCI Execution Plan (max_dop = 32, max_query_mem = unlimited) |
|  2 | └─Limit                          |          | 10        | 7935739.96 | Offset=0 Limit=10                                             |
|  3 |   └─Sort                         |          | 10        | 7935739.96 | Sort Key: c_name ASC                                          |
|  4 |     └─Hash Groupby               |          | 1503700   | 7933273.99 | Group Key: customer.C_NAME                                    |
|  5 |       └─Hash Right Semi Join     |          | 54010545  | 7865930.26 | Join Cond: lineitem.L_ORDERKEY = orders.O_ORDERKEY            |
|  6 |         ├─Table Scan             | lineitem | 59785766  | 24001.52   | Cond: (L_PARTKEY > 18000000)                                  |
|  7 |         └─Hash Join              |          | 538776190 | 5488090.33 | Join Cond: orders.O_ORDERKEY = lineitem.L_ORDERKEY            |
|  8 |           ├─Hash Join            |          | 181006430 | 668535.99  | Join Cond: customer.C_CUSTKEY = orders.O_CUSTKEY              |
|  9 |           │ ├─Table Scan         | customer | 15000000  | 600.00     |                                                               |
| 10 |           │ └─Table Scan         | orders   | 150000000 | 6000.00    |                                                               |
| 11 |           └─Table Scan           | lineitem | 600037902 | 24001.52   |                                                               |
+----+----------------------------------+----------+-----------+------------+---------------------------------------------------------------+

El Hash Join con ID 7 tiene un costo estimado de 5,488,090, aproximadamente el 70 % del costo total de la consulta (7,935,739). Consume una gran cantidad de recursos de CPU, lo que indica un conjunto de resultados intermedio muy grande. Habilite el perfilado para confirmar que este es el cuello de botella real.

Paso 2: Habilite el perfilado y ejecute la consulta.

SET imci_analyze_query = ON;

SELECT
    c_name,
    sum(l_quantity)
FROM
    customer,
    orders,
    lineitem
WHERE
    o_orderkey IN (
        SELECT
            l_orderkey
        FROM
            lineitem
        WHERE
            l_partkey > 18000000
    )
    AND c_custkey = o_custkey
    AND o_orderkey = l_orderkey
GROUP BY
    c_name
ORDER BY
    c_name
LIMIT 10;

Salida de ejemplo:

+--------------------+-----------------+
| c_name             | sum(l_quantity) |
+--------------------+-----------------+
| Customer#000000001 |          172.00 |
| Customer#000000002 |          663.00 |
| Customer#000000004 |          174.00 |
| Customer#000000005 |          488.00 |
| Customer#000000007 |         1135.00 |
| Customer#000000008 |          440.00 |
| Customer#000000010 |          625.00 |
| Customer#000000011 |          143.00 |
| Customer#000000013 |         1032.00 |
| Customer#000000014 |          564.00 |
+--------------------+-----------------+
10 rows in set (21.37 sec)

Paso 3: Obtenga los datos de perfilado.

/*ROUTE_TO_LAST_USED*/SELECT * FROM information_schema.imci_sql_profiling;

Salida de ejemplo:

+----+----------------------------------+----------+-----------+------------+-------------------+----------------------------------------------------+
| ID | Operator                         | Name     | A-Rows    | A-Cost     | Execution Time(s) | Extra Info                                         |
+----+----------------------------------+----------+-----------+------------+-------------------+----------------------------------------------------+
|  1 | Select Statement                 |          |         0 | 8336856.67 |                 0 |                                                    |
|  2 | └─Limit                          |          |        10 | 8336856.67 |             0.002 | Offset=0 Limit=10                                  |
|  3 |   └─Sort                         |          |         0 | 8336856.67 |             2.275 | Sort Key: c_name ASC                               |
|  4 |     └─Hash Groupby               |          |   9813586 | 8320763.22 |           160.083 | Group Key: customer.C_NAME                         |
|  5 |       └─Hash Right Semi Join     |          | 239598134 | 7994854.23 |            98.174 | Join Cond: lineitem.L_ORDERKEY = orders.O_ORDERKEY |
|  6 |         ├─Table Scan             | lineitem |  60013756 |   24001.52 |              3.28 | Cond: (L_PARTKEY > 18000000)                       |
|  7 |         └─Hash Join              |          | 600037902 | 5156677.35 |           301.503 | Join Cond: orders.O_ORDERKEY = lineitem.L_ORDERKEY |
|  8 |           ├─Hash Join            |          | 150000000 |  629777.96 |            97.201 | Join Cond: customer.C_CUSTKEY = orders.O_CUSTKEY   |
|  9 |           │ ├─Table Scan         | customer |  15000000 |        600 |             3.321 |                                                    |
| 10 |           │ └─Table Scan         | orders   | 150000000 |       6000 |             0.241 |                                                    |
| 11 |           └─Table Scan           | lineitem | 600037902 |   24001.52 |             0.661 |                                                    |
+----+----------------------------------+----------+-----------+------------+-------------------+----------------------------------------------------+

El Hash Join con ID 7 tardó 301.503 s, casi la mitad del tiempo total de la consulta. Sus entradas son la tabla lineitem (600 millones de filas, ID 11) y el resultado del Hash Join con ID 8 (150 millones de filas). Unir dos conjuntos tan grandes obliga al motor a construir una tabla hash enorme, lo que domina el tiempo de ejecución.

El plan de ejecución también revela que el filtro o_orderkey IN (...) se evalúa como un Hash Right Semi Join (ID 5) después de que termina la unión costosa del ID 7. Si el optimizador convierte esta subconsulta IN en una semiunión y la empuja antes del ID 7, filtrará las filas de orders de forma temprana y reducirá el conjunto de resultados intermedio que alimenta al ID 7.

Aplicación de la optimización

Paso 4: Habilite el empuje de semiuniones basado en costos.

SET imci_optimizer_switch = 'semijoin_pushdown=on';

Paso 5: Compruebe el nuevo plan de ejecución.

EXPLAIN SELECT
    c_name,
    sum(l_quantity)
FROM
    customer,
    orders,
    lineitem
WHERE
    o_orderkey IN (
        SELECT
            l_orderkey
        FROM
            lineitem
        WHERE
            l_partkey > 18000000
    )
    AND c_custkey = o_custkey
    AND o_orderkey = l_orderkey
GROUP BY
    c_name
ORDER BY
    c_name
LIMIT 10;

Salida de ejemplo:

+----+----------------------------------------+----------+-----------+------------+---------------------------------------------------------------+
| ID | Operator                               | Name     | E-Rows    | E-Cost     | Extra Info                                                    |
+----+----------------------------------------+----------+-----------+------------+---------------------------------------------------------------+
|  1 | Select Statement                       |          |           |            | IMCI Execution Plan (max_dop = 32, max_query_mem = unlimited) |
|  2 | └─Limit                                |          | 10        | 2800433.74 | Offset=0 Limit=10                                             |
|  3 |   └─Sort                               |          | 10        | 2800433.74 | Sort Key: c_name ASC                                          |
|  4 |     └─Hash Groupby                     |          | 14567321  | 2776544.58 | Group Key: customer.C_NAME                                    |
|  5 |       └─Hash Join                      |          | 57918330  | 2631846.75 | Join Cond: orders.O_ORDERKEY = lineitem.L_ORDERKEY            |
|  6 |         ├─Hash Join                    |          | 14567321  | 1014041.92 | Join Cond: orders.O_CUSTKEY = customer.C_CUSTKEY              |
|  7 |         │ ├─Hash Right Semi Join       |          | 12071937  | 906226.67  | Join Cond: lineitem.L_ORDERKEY = orders.O_ORDERKEY            |
|  8 |         │ │ ├─Table Scan               | lineitem | 59785766  | 24001.52   | Cond: (L_PARTKEY > 18000000)                                  |
|  9 |         │ │ └─Table Scan               | orders   | 150000000 | 6000.00    |                                                               |
| 10 |         │ └─Table Scan                 | customer | 15000000  | 600.00     |                                                               |
| 11 |         └─Table Scan                   | lineitem | 600037902 | 24001.52   |                                                               |
+----+----------------------------------------+----------+-----------+------------+---------------------------------------------------------------+

Ahora la semiunión se empujó al ID 7, lo que filtra las filas de orders antes de la unión costosa. El costo total estimado bajó de 7,935,739 a 2,800,433.

Comprobación de la mejora

Paso 6: Vuelva a ejecutar la consulta y perfílela.

SELECT
    c_name,
    sum(l_quantity)
FROM
    customer,
    orders,
    lineitem
WHERE
    o_orderkey IN (
        SELECT
            l_orderkey
        FROM
            lineitem
        WHERE
            l_partkey > 18000000
    )
    AND c_custkey = o_custkey
    AND o_orderkey = l_orderkey
GROUP BY
    c_name
ORDER BY
    c_name
LIMIT 10;

Salida de ejemplo:

+--------------------+-----------------+
| c_name             | sum(l_quantity) |
+--------------------+-----------------+
| Customer#000000001 |          172.00 |
| Customer#000000002 |          663.00 |
| Customer#000000004 |          174.00 |
| Customer#000000005 |          488.00 |
| Customer#000000007 |         1135.00 |
| Customer#000000008 |          440.00 |
| Customer#000000010 |          625.00 |
| Customer#000000011 |          143.00 |
| Customer#000000013 |         1032.00 |
| Customer#000000014 |          564.00 |
+--------------------+-----------------+
10 rows in set (13.74 sec)

El tiempo de ejecución pasó de 21.37 s a 13.74 s, una reducción aproximada del 40 %.

Paso 7: Confirme el resultado con los datos de perfilado.

/*ROUTE_TO_LAST_USED*/SELECT * FROM information_schema.imci_sql_profiling;

Salida de ejemplo:

+----+----------------------------------------+----------+-----------+------------+-------------------+----------------------------------------------------+
| ID | Operator                               | Name     | A-Rows    | A-Cost     | Execution Time(s) | Extra Info                                         |
+----+----------------------------------------+----------+-----------+------------+-------------------+----------------------------------------------------+
|  1 | Select Statement                       |          |         0 | 4318488.35 |                 0 |                                                    |
|  2 | └─Limit                                |          |        10 | 4318488.35 |             0.002 | Offset=0 Limit=10                                  |
|  3 |   └─Sort                               |          |         0 | 4318488.34 |             3.076 | Sort Key: c_name ASC                               |
|  4 |     └─Hash Groupby                     |          |   9813586 | 4302394.89 |           163.149 | Group Key: customer.C_NAME                         |
|  5 |       └─Hash Join                      |          | 239598134 | 3976485.91 |           151.253 | Join Cond: orders.O_ORDERKEY = lineitem.L_ORDERKEY |
|  6 |         ├─Hash Join                    |          |  49393149 | 1321335.54 |            55.392 | Join Cond: orders.O_CUSTKEY = customer.C_CUSTKEY   |
|  7 |         │ ├─Hash Right Semi Join       |          |  49393149 |  954805.16 |            52.552 | Join Cond: lineitem.L_ORDERKEY = orders.O_ORDERKEY |
|  8 |         │ │ ├─Table Scan               | lineitem |  60013756 |   24001.52 |             2.791 | Cond: (L_PARTKEY > 18000000)                       |
|  9 |         │ │ └─Table Scan               | orders   | 150000000 |       6000 |             0.152 |                                                    |
| 10 |         │ └─Table Scan                 | customer |  15000000 |        600 |             0.028 |                                                    |
| 11 |         └─Table Scan                   | lineitem | 600037902 |   24001.52 |             0.642 |                                                    |
+----+----------------------------------------+----------+-----------+------------+-------------------+----------------------------------------------------+

El cuello de botella anterior (Hash Join con ID 7, 301.503 s) desapareció. Ahora, el empuje de la semiunión en el ID 7 filtra orders hasta unos 49 millones de filas antes de la unión del ID 5, lo que reduce el tamaño de entrada de la unión y el tiempo total de ejecución.