PolarDB:Automatic IMCI-based query acceleration (AutoIndex)

Descripción general

La tecnología de índices de almacenamiento en columnas (IMCI) de PolarDB for MySQL está diseñada para consultas complejas sobre grandes volúmenes de datos en escenarios OLAP. Con estos índices, PolarDB for MySQL integra el procesamiento transaccional y el análisis de datos en tiempo real como una solución unificada de base de datos HTAP (procesamiento híbrido transaccional y analítico).

De forma tradicional, para acelerar las tablas involucradas en consultas lentas se requiere usar manualmente CREATE TABLE o ALTER TABLE y agregar COLUMNAR=1 al campo COMMENT de la tabla. Así se crea un índice de almacenamiento en columnas. Este enfoque manual resulta ineficiente cuando existen muchas plantillas SQL con lógica compleja.

La aceleración automática de consultas basada en IMCI (AutoIndex) agrega índices de almacenamiento en columnas a las tablas objetivo a partir de las consultas lentas. De este modo, acelera las consultas sin intervención manual.

Contexto técnico

AutoIndex es una técnica que optimiza el rendimiento de SQL mediante la creación automática de índices. Los índices de almacenamiento en filas cuentan con amplia investigación e implementación comercial, por ejemplo, en Oracle y SQL Server. Estos sistemas analizan los índices secundarios que podrían requerirse según los predicados, las cláusulas ORDER BY o las uniones (JOIN). Después recalculan el costo de la consulta asumiendo la creación de dicho índice y generan aquellos que reducen significativamente ese costo.

En el ámbito de los índices de almacenamiento en columnas, sistemas como Redshift, Snowflake, Databricks y Heatwave han adoptado AutoIndex. Los métodos de automatización más comunes incluyen:

• Carga y descarga automáticas: crea índices de almacenamiento en columnas de forma automática y acelera las consultas lentas según su ejecución. También elimina los índices poco utilizados o que consumen demasiados recursos para reducir el consumo de almacenamiento.

• Codificación automática: selecciona automáticamente el algoritmo de codificación óptimo según la distribución de datos de cada columna para comprimir aún más el almacenamiento.

• Selección automática de Distribute Key: equilibra la distribución de datos en escenarios MPP, reduce el sesgo de datos y optimiza la redistribución (shuffling) en operaciones GROUP BY y JOIN distribuidas.

• Selección automática de Sort Key: utiliza predicados, columnas de unión o columnas de ordenamiento frecuentes para acelerar el filtrado o la clasificación de consultas.

En IMCI, crear un índice de almacenamiento en columnas de forma automática tiene un costo menor y mayor tolerancia a fallos que crear uno de filas. Esto se debe a lo siguiente:

• Menor impacto en las escrituras: un exceso de índices de almacenamiento en filas afecta significativamente el rendimiento de escritura en el nodo principal. En cambio, IMCI no materializa los datos de columnas en dicho nodo. Al agregar un índice de almacenamiento en columnas, las operaciones de escritura solo necesitan sincronizarse al nodo de solo lectura de índice de columnas mediante Redo, lo cual casi no impacta el rendimiento de escritura.

• Mayor tasa de compresión: los índices de almacenamiento en columnas suelen alcanzar una tasa de compresión de 3x a 5x, por lo que la sobrecarga de almacenamiento de los nuevos índices es relativamente baja.

• Impacto limitado en las operaciones del negocio: las estructuras de datos eficientes y sin bloqueos recopilan información de SQL Trace, mientras que Nonblock DDL (DDL sin bloqueo) reduce las interrupciones en las operaciones comerciales.

Funcionamiento

SELECT * FROM information_schema.imci_autoindex

SELECT *
FROM information_schema.imci_autoindex autoindex, information_schema.sql_sharing share
WHERE autoindex.sql_id = share.sql_id
  AND share.type = 'SQL'

Resultados de las pruebas

El siguiente ejemplo se basa en el conjunto de pruebas TPC-H de 100 GB.

• Configuración del entorno: ejecute las 22 consultas de prueba de TPC-H una vez por minuto. Habilite HTAP Optimization (Transactional/Analytical Processing Splitting) para el endpoint del clúster y establezca el grado de paralelismo del almacenamiento en filas en 8, principalmente para acortar el tiempo de la prueba.

• Resultados de la prueba: la siguiente tabla muestra que el rendimiento de las consultas mejora considerablemente en las rondas posteriores. Durante la última parte de la primera ronda, aproximadamente a partir de la duodécima consulta, AutoIndex ya había creado índices de almacenamiento en columnas para la mayoría de las tablas y el optimizador enruto automáticamente esas consultas al nodo de solo lectura de índice de columnas.

Beneficios

Los índices de almacenamiento en columnas ofrecen diversos beneficios, con mejoras significativas en compatibilidad, rendimiento y costos:

• Compatibilidad total con MySQL: admite todos los tipos de datos de MySQL y es completamente compatible con el protocolo de MySQL.

• Excelente rendimiento HTAP: por lo general, mejora el rendimiento en escenarios analíticos entre uno y dos órdenes de magnitud.

• Almacenamiento híbrido de filas y columnas con menor costo: garantiza la consistencia transaccional mediante almacenamiento híbrido. Además, los índices de almacenamiento en columnas ofrecen mejor rendimiento a menor costo en ciertos escenarios de consulta.

• Aceleración automática sencilla: permite habilitar la aceleración automática del rendimiento con un solo clic, sin configuraciones ni ajustes complejos.

• Reducción de la carga operativa: crea automáticamente índices de almacenamiento en columnas para las consultas lentas, lo que disminuye el trabajo manual de optimización.

• Reducción de costos de TI: optimiza únicamente las consultas lentas en lugar de crear índices de almacenamiento en columnas para todas las bases de datos y tablas. Esto ahorra recursos de memoria y almacenamiento.

Versiones compatibles

• Los clústeres de **Enterprise Edition** deben cumplir con las siguientes condiciones:

  • Series: Cluster Edition.

  • Versión del motor de base de datos:

    • MySQL 8.0.1, y la versión secundaria del motor debe ser 8.0.1.1.45.2 o posterior.

    • MySQL 8.0.2, y la versión secundaria del motor debe ser 8.0.2.2.27 o posterior.

• Los clústeres de **Standard Edition** deben cumplir con las siguientes condiciones:

  • CPU architecture: x86.

  • Versión del motor de base de datos: MySQL 8.0.1, y la versión secundaria del motor debe ser 8.0.1.1.45.2 o posterior.

Notas de uso

• Los clústeres Multi-master Cluster (Limitless) EditionSeries y Serverless no admiten la aceleración automática de consultas basada en IMCI.

• Después de habilitar la aceleración automática de consultas basada en IMCI:

  • Los cargos por los nodos de solo lectura de índice de columnas agregados siguen las reglas de billing de los nodos de cómputo normales.

  • El sistema crea automáticamente índices de almacenamiento en columnas a partir de sus sentencias SQL lentas (los datos de estos índices ocupan espacio de almacenamiento).

  • El sistema utiliza la función SQL Trace para registrar el historial de ejecución de las sentencias SQL lentas. En la mayoría de los casos, esto consume no más del 3 % del rendimiento del nodo de base de datos.

• La aceleración automática de consultas basada en IMCI usa internamente Nonblock DDL para agregar índices de almacenamiento en columnas. Nonblock DDL permite que las nuevas transacciones accedan a la tabla objetivo incluso cuando no se puede obtener un bloqueo MDL-X, lo que mantiene estable todo el sistema empresarial. Para obtener más información, consulte Nonblocking DDL statements.

Habilitar la aceleración automática de consultas basada en IMCI

1. Inicie sesión en la consola de PolarDB. En el panel de navegación izquierdo, haga clic en Clusters. Seleccione la Region donde se despliega el clúster y luego haga clic en el ID del clúster para ir a la página de detalles.

2. En la página Basic Information, busque el campo Automatic IMCI-based Query Acceleration y haga clic en Enable.

3. Los pasos siguientes varían según si el clúster actual cuenta con un nodo de solo lectura de índice de columnas:

   • Si el clúster actual ya tiene un nodo de solo lectura de índice de columnas, en el cuadro de diálogo Enable Automatic IMCI-based Query Acceleration, haga clic en OK para habilitar la aceleración automática de consultas basada en IMCI.

   • Si el clúster actual no tiene un nodo de solo lectura de índice de columnas, en el cuadro de diálogo Enable Automatic IMCI-based Query Acceleration, haga clic en OK. El sistema lo redirigirá a la página para agregar un nodo de solo lectura de índice de columnas.

     Nota

     • Después de hacer clic en OK, puede Add a read-only IMCI node de inmediato o hacerlo manualmente (Add a read-only IMCI node) más adelante.

     • Una vez habilitada la aceleración automática de consultas basada en IMCI, el clúster actual debe contener como mínimo un nodo de solo lectura de índice de columnas. De lo contrario, aunque Automatic IMCI-based Query Acceleration esté configurado en Enable, el servicio de aceleración no estará disponible.

     • Si habilita la aceleración automática de consultas basada en IMCI pero no ha agregado un nodo de solo lectura de índice de columnas, el sistema usará la función SQL Trace para registrar el historial de ejecución de las sentencias SQL lentas, pero no creará índices de almacenamiento en columnas. Por lo tanto, no se proporcionará el servicio de aceleración.

Deshabilitar la aceleración automática de consultas basada en IMCI

1. Inicie sesión en la consola de PolarDB. En el panel de navegación izquierdo, haga clic en Clusters. Seleccione la Region donde se despliega el clúster y luego haga clic en el ID del clúster para ir a la página de detalles.

2. En la página Basic Information, busque el campo Automatic IMCI-based Query Acceleration y haga clic en Close.

3. En el cuadro de diálogo Disable Automatic IMCI-based Query Acceleration, haga clic en OK para deshabilitar la aceleración automática de consultas basada en IMCI.