Utilice Hybrid Plan para acelerar las consultas de tablas anchas - PolarDB

Las consultas de tablas anchas que filtran por unas pocas columnas pero recuperan todas las columnas generan un problema de amplificación de lectura. Un índice columnar procesa el filtrado de forma eficiente. Sin embargo, recuperar cada columna proyectada a través de él es costoso. Un índice de almacenamiento de filas evita esa amplificación. No obstante, pierde velocidad de filtrado columnar. Hybrid Plan combina ambos índices en una sola ejecución de consulta para obtener lo mejor de cada uno.

Cómo funciona

Hybrid Plan divide la ejecución en dos etapas:

El optimador utiliza el In-Memory Column Index (IMCI) para filtrar filas y recuperar sus claves principales.
El sistema utiliza esas claves principales para buscar los datos completos de la fila en el índice principal de InnoDB.

Esto elimina la amplificación de lectura: IMCI se encarga del barrido columnar rápido y InnoDB devuelve solo las filas coincidentes completas.

Aplicabilidad

Edición de clúster: PolarDB for MySQL Enterprise Edition
Versiones compatibles:
- PolarDB for MySQL 8.0.1, revisión 8.0.1.1.37 o posterior
- PolarDB for MySQL 8.0.2, revisión 8.0.2.2.23.1 o posterior

Casos de uso

Consultas de tablas anchas que devuelven 15 columnas o más.

Limitaciones

Las tablas que proporcionan columnas de salida para la consulta deben tener una clave principal definida explícitamente.

Nota

Si Hybrid Plan no puede acelerar una sentencia SQL lenta debido a limitaciones como el tipo de tabla, contact us para obtener ayuda.

Parámetros

Hybrid Plan tiene dos parámetros de control. Actúan en distintos niveles y siguen un orden de prioridad claro:

imci_enable_hybrid_plan — control global de la función. Cuando se establece en ON, el optimizador evalúa cada consulta. Además, aplica automáticamente Hybrid Plan cuando estima una ventaja en el costo.
imci_optimizer_switch — una sugerencia por consulta. Cuando se establece force_hybrid_index_search=ON en una sugerencia, fuerza Hybrid Plan para esa sentencia. Esto ocurre sin importar la estimación de costos del optimizador.

Prioridad: una sugerencia por consulta (force_hybrid_index_search=ON) anula la decisión automática del optimizador. El optimizador solo decide cuando no hay ninguna sugerencia y imci_enable_hybrid_plan está en ON.

Parámetro	Predeterminado	Descripción
`imci_enable_hybrid_plan`	ON	Habilite Hybrid Plan globalmente. Establezca el valor en OFF para que se deshabilite la función.
`imci_optimizer_switch`	`force_hybrid_index_search=OFF`	Anulación por consulta. Establezca `force_hybrid_index_search=ON` en una sugerencia para forzar Hybrid Plan para una sentencia específica.

Habilite y compruebe Hybrid Plan

Esta sección usa SELECT * FROM t1; como ejemplo.

Paso 1: Conéctese a su clúster

Conéctese al clúster de PolarDB. Para obtener más información, consulte Connect to a database cluster.

Paso 2: Habilite la función

Establezca imci_enable_hybrid_plan en ON. Luego, el optimizador aplica automáticamente Hybrid Plan a las consultas de tablas anchas elegibles.

SET imci_enable_hybrid_plan=ON;

Paso 3: Fuerce una consulta específica para que use Hybrid Plan (opcional)

Si el optimizador no selecciona Hybrid Plan para una consulta, agregue una sugerencia para anular la decisión. Por ejemplo, esto ocurre cuando su estimación de costos favorece otro plan:

SELECT /*+ SET_VAR(imci_optimizer_switch='force_hybrid_index_search=ON') */ * FROM t1;

Paso 4: Compruebe que Hybrid Plan está activo

Ejecute EXPLAIN en la consulta y confirme que Hybrid Plan está en uso. Cuando está activo, el plan de ejecución incluye el operador HybridIndexSearch.

EXPLAIN SELECT /*+ SET_VAR(imci_optimizer_switch='force_hybrid_index_search=ON') */ * FROM t1;

Salida esperada:

+----+-------------------------+------+---------------------------------------------------------------+
| ID | Operator                | Name | Extra Info                                                    |
+----+-------------------------+------+---------------------------------------------------------------+
|  1 | Select Statement        |      | IMCI Execution Plan (max_dop = 32, max_query_mem = unlimited) |
|  2 | └─HybridIndexSearch     |      | Used table: t1                                                |
|  3 |   └─Compute Scalar      |      |                                                               |
|  4 |     └─Table Scan        | t1   |                                                               |
+----+-------------------------+------+---------------------------------------------------------------+
4 rows in set (0.02 sec)

El operador HybridIndexSearch de la fila 2 confirma que Hybrid Plan está activo. La fila 1 muestra el contexto de ejecución de IMCI. La fila 4 confirma el barrido de tabla de InnoDB para la recuperación completa de la fila.

Prueba de rendimiento

La prueba que se presenta a continuación usa el conjunto de datos OnTime de ClickHouse. La tabla OnTime tiene 109 columnas, por lo que es una tabla ancha representativa. El esquema de la tabla se modificó para incluir una clave principal explícita. El esquema modificado se muestra a continuación:

CREATE TABLE `ontime` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Year` year(4) DEFAULT NULL,
  `Quarter` tinyint(4) DEFAULT NULL,
  `Month` tinyint(4) DEFAULT NULL,
  `DayofMonth` tinyint(4) DEFAULT NULL,
  `DayOfWeek` tinyint(4) DEFAULT NULL,
  `FlightDate` date DEFAULT NULL,
  `UniqueCarrier` char(7) DEFAULT NULL,
  `AirlineID` int(11) DEFAULT NULL,
  `Carrier` char(2) DEFAULT NULL,
  `TailNum` varchar(50) DEFAULT NULL,
  `FlightNum` varchar(10) DEFAULT NULL,
  `OriginAirportID` int(11) DEFAULT NULL,
  `OriginAirportSeqID` int(11) DEFAULT NULL,
  `OriginCityMarketID` int(11) DEFAULT NULL,
  `Origin` char(5) DEFAULT NULL,
  `OriginCityName` varchar(100) DEFAULT NULL,
  `OriginState` char(2) DEFAULT NULL,
  `OriginStateFips` varchar(10) DEFAULT NULL,
  `OriginStateName` varchar(100) DEFAULT NULL,
  `OriginWac` int(11) DEFAULT NULL,
  `DestAirportID` int(11) DEFAULT NULL,
  `DestAirportSeqID` int(11) DEFAULT NULL,
  `DestCityMarketID` int(11) DEFAULT NULL,
  `Dest` char(5) DEFAULT NULL,
  `DestCityName` varchar(100) DEFAULT NULL,
  `DestState` char(2) DEFAULT NULL,
  `DestStateFips` varchar(10) DEFAULT NULL,
  `DestStateName` varchar(100) DEFAULT NULL,
  `DestWac` int(11) DEFAULT NULL,
  `CRSDepTime` int(11) DEFAULT NULL,
  `DepTime` int(11) DEFAULT NULL,
  `DepDelay` int(11) DEFAULT NULL,
  `DepDelayMinutes` int(11) DEFAULT NULL,
  `DepDel15` int(11) DEFAULT NULL,
  `DepartureDelayGroups` int(11) DEFAULT NULL,
  `DepTimeBlk` varchar(20) DEFAULT NULL,
  `TaxiOut` int(11) DEFAULT NULL,
  `WheelsOff` int(11) DEFAULT NULL,
  `WheelsOn` int(11) DEFAULT NULL,
  `TaxiIn` int(11) DEFAULT NULL,
  `CRSArrTime` int(11) DEFAULT NULL,
  `ArrTime` int(11) DEFAULT NULL,
  `ArrDelay` int(11) DEFAULT NULL,
  `ArrDelayMinutes` int(11) DEFAULT NULL,
  `ArrDel15` int(11) DEFAULT NULL,
  `ArrivalDelayGroups` int(11) DEFAULT NULL,
  `ArrTimeBlk` varchar(20) DEFAULT NULL,
  `Cancelled` tinyint(4) DEFAULT NULL,
  `CancellationCode` char(1) DEFAULT NULL,
  `Diverted` tinyint(4) DEFAULT NULL,
  `CRSElapsedTime` int(11) DEFAULT NULL,
  `ActualElapsedTime` int(11) DEFAULT NULL,
  `AirTime` int(11) DEFAULT NULL,
  `Flights` int(11) DEFAULT NULL,
  `Distance` int(11) DEFAULT NULL,
  `DistanceGroup` tinyint(4) DEFAULT NULL,
  `CarrierDelay` int(11) DEFAULT NULL,
  `WeatherDelay` int(11) DEFAULT NULL,
  `NASDelay` int(11) DEFAULT NULL,
  `SecurityDelay` int(11) DEFAULT NULL,
  `LateAircraftDelay` int(11) DEFAULT NULL,
  `FirstDepTime` varchar(10) DEFAULT NULL,
  `TotalAddGTime` varchar(10) DEFAULT NULL,
  `LongestAddGTime` varchar(10) DEFAULT NULL,
  `DivAirportLandings` varchar(10) DEFAULT NULL,
  `DivReachedDest` varchar(10) DEFAULT NULL,
  `DivActualElapsedTime` varchar(10) DEFAULT NULL,
  `DivArrDelay` varchar(10) DEFAULT NULL,
  `DivDistance` varchar(10) DEFAULT NULL,
  `Div1Airport` varchar(10) DEFAULT NULL,
  `Div1AirportID` int(11) DEFAULT NULL,
  `Div1AirportSeqID` int(11) DEFAULT NULL,
  `Div1WheelsOn` varchar(10) DEFAULT NULL,
  `Div1TotalGTime` varchar(10) DEFAULT NULL,
  `Div1LongestGTime` varchar(10) DEFAULT NULL,
  `Div1WheelsOff` varchar(10) DEFAULT NULL,
  `Div1TailNum` varchar(10) DEFAULT NULL,
  `Div2Airport` varchar(10) DEFAULT NULL,
  `Div2AirportID` int(11) DEFAULT NULL,
  `Div2AirportSeqID` int(11) DEFAULT NULL,
  `Div2WheelsOn` varchar(10) DEFAULT NULL,
  `Div2TotalGTime` varchar(10) DEFAULT NULL,
  `Div2LongestGTime` varchar(10) DEFAULT NULL,
  `Div2WheelsOff` varchar(10) DEFAULT NULL,
  `Div2TailNum` varchar(10) DEFAULT NULL,
  `Div3Airport` varchar(10) DEFAULT NULL,
  `Div3AirportID` int(11) DEFAULT NULL,
  `Div3AirportSeqID` int(11) DEFAULT NULL,
  `Div3WheelsOn` varchar(10) DEFAULT NULL,
  `Div3TotalGTime` varchar(10) DEFAULT NULL,
  `Div3LongestGTime` varchar(10) DEFAULT NULL,
  `Div3WheelsOff` varchar(10) DEFAULT NULL,
  `Div3TailNum` varchar(10) DEFAULT NULL,
  `Div4Airport` varchar(10) DEFAULT NULL,
  `Div4AirportID` int(11) DEFAULT NULL,
  `Div4AirportSeqID` int(11) DEFAULT NULL,
  `Div4WheelsOn` varchar(10) DEFAULT NULL,
  `Div4TotalGTime` varchar(10) DEFAULT NULL,
  `Div4LongestGTime` varchar(10) DEFAULT NULL,
  `Div4WheelsOff` varchar(10) DEFAULT NULL,
  `Div4TailNum` varchar(10) DEFAULT NULL,
  `Div5Airport` varchar(10) DEFAULT NULL,
  `Div5AirportID` int(11) DEFAULT NULL,
  `Div5AirportSeqID` int(11) DEFAULT NULL,
  `Div5WheelsOn` varchar(10) DEFAULT NULL,
  `Div5TotalGTime` varchar(10) DEFAULT NULL,
  `Div5LongestGTime` varchar(10) DEFAULT NULL,
  `Div5WheelsOff` varchar(10) DEFAULT NULL,
  `Div5TailNum` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=58592318 DEFAULT CHARSET=latin1 COMMENT='columnar=1'

Consulta de prueba:

SELECT * FROM ontime ORDER BY ArrTime LIMIT 1000;

Todas las consultas se ejecutaron desde un inicio en frío.

Estrategia de ejecución	Tiempo de consulta
Solo almacenamiento de filas	232.48 segundos
Solo índice columnar (IMCI)	2.56 segundos
Hybrid Plan	0.33 segundos

Para esta carga de trabajo, Hybrid Plan es 7.8 veces más rápido que usar solo el índice columnar. También es 704 veces más rápido que usar solo el almacenamiento de filas.