How columnstore index statistics optimize queries - PolarDB - Centro de documentación de Alibaba Cloud

El pruner del índice columnstore omite los bloques de datos que no pueden contener filas coincidentes antes de iniciar un escaneo. Esto reduce las operaciones de E/S y mejora el rendimiento de las consultas en tablas grandes. Este tema describe los cuatro tipos de pruner, cuándo usar cada uno, cómo configurarlos y cómo comprobar que funcionan correctamente.

Cómo funcionan los pruner

Los datos del índice columnstore se almacenan en bloques de una sola columna, con una granularidad predeterminada de 64,000 filas por bloque. Todos los paquetes de columnas (column packs) de un conjunto de filas forman un grupo de filas. Un escaneo recorre cada bloque de datos de una columna específica, a menos que un pruner demuestre que el bloque no contiene filas coincidentes. En ese caso, el bloque se omite por completo.

Calcule la cantidad estimada de bloques de datos de una tabla con esta fórmula:

Number of data blocks = total table rows / 64,000

En tablas demasiado grandes para caber en memoria, omitir incluso una fracción de los bloques produce una reducción notable en el tiempo de escaneo.

Tipos de pruner

Se admiten cuatro tipos de pruner. Los índices minmax para tipos numéricos (INT, DECIMAL, DATETIME) se construyen automáticamente cuando usted crea un índice columnstore. En PolarDB MySQL 8.0.1.1.35 o posterior, o bien 8.0.2.2.16 o posterior, los pruner para columnas de cadena también se construyen de forma automática. En versiones anteriores, los pruner de cadena requieren una configuración explícita.

Pruner	Método de filtrado	Más indicado para	No recomendado para
Filtro Bloom	Arreglo de bits que verifica la pertenencia de un elemento	Condiciones de igualdad (`=`) y `IN` en columnas de cadena con alta cardinalidad	Tipos numéricos; columnas JSON, BLOB, TEXT
Índice minmax	Almacena los valores mínimo y máximo por bloque; omite los bloques fuera del rango del filtro	Filtros de rango e igualdad en columnas ordenadas o de fecha	Columnas JSON, GEOMETRY
Filtro Bloom de token	Divide las cadenas en caracteres no alfanuméricos (por ejemplo, `"I am IMCI"` → `I	am	IMCI`)	Consultas `LIKE` sobre texto en lenguaje natural	Tipos numéricos
Filtro Bloom de n-gramas	Divide las cadenas en subcadenas de longitud fija (por ejemplo, `"storage"` con N=3 → `sto	tor	ora	rag	age`)	Búsquedas de subcadenas con `LIKE "%string%"`	Tipos numéricos; consultas donde la cadena de búsqueda es más corta que N

Los índices minmax en tipos numéricos se construyen automáticamente cuando usted crea un índice columnstore. Estas columnas no necesitan configuración adicional.

Cuándo usar pruner

Utilice un pruner cuando:

Los valores de filtro estén dispersos entre los bloques de datos, de modo que la mayoría de los bloques no contenga filas coincidentes.
La columna tenga una alta localidad de datos, es decir, valores similares agrupados según el orden de inserción. Esto es común en columnas de fecha o identificadores ordenados.
Ejecute búsquedas LIKE "%substring%" en columnas de cadena grandes.

Evite usar un pruner cuando:

Los valores coincidentes se distribuyan uniformemente en todos los bloques. El pruner evaluará cada bloque sin poder omitir ninguno, lo que añade sobrecarga sin aportar ventajas.
Aplique un filtro Bloom a una columna cuyos valores distintos representen el 3 % o menos del total de filas y los datos estén distribuidos uniformemente. La eficacia depende de la localidad, por lo que el rendimiento podría ser peor que sin el pruner.
El tipo de columna no sea compatible (consulte la tabla en Tipos de pruner).

Importante

Agregar un pruner a una columna incorrecta puede afectar negativamente el rendimiento en lugar de mejorarlo. Un filtro Bloom no puede omitir ningún bloque si el valor objetivo aparece en todos ellos. Pruebe con datos realistas antes de habilitar pruner en tablas de producción.

Uso de memoria

Habilitar pruner en columnas de cadena aumenta el consumo de memoria. Use estas fórmulas para estimar el uso antes de habilitarlos en tablas grandes.

Filtro Bloom, filtro Bloom de token o filtro Bloom de n-gramas

Cuando la cantidad de valores distintos supera el 3 % del total de filas:

Memory = 1.2 × (number of columns with bloom filter) × (total table rows) bytes

Cuando la cantidad de valores distintos es igual o inferior al 3 % del total de filas:

Memory = 1.2 × (number of columns with bloom filter) × (number of distinct values) bytes

Índice minmax

Memory = 2 × (columns with minmax index) × (total rows / block size) × prefix length × character encoding length

Por ejemplo, una tabla con 2 mil millones de filas, índices minmax en 10 columnas, una longitud de prefijo de 20 caracteres, un tamaño de bloque de 64,000 y codificación utf8mb4 (4 bytes por carácter) utiliza aproximadamente 46 MB de memoria.

Notas de uso

Las cadenas no deben contener \0 (por ejemplo, 'polar\0db') para que se construya un pruner en esa columna.
Los atributos COMMENT a nivel de columna tienen prioridad sobre los atributos COMMENT a nivel de tabla.
Para agregar o quitar un pruner en una tabla existente, elimine el índice columnstore y vuelva a crearlo. Actualice el atributo COMMENT antes de reconstruirlo.
En PolarDB MySQL versión 8.0.1.1.35 o posterior, o bien 8.0.2.2.16 o posterior, los pruner para columnas de cadena se construyen automáticamente cuando usted crea un índice columnstore. Los filtros Bloom usan una caché Least Recently Used (LRU) para gestionar la memoria. Si actualiza desde una versión anterior, reconstruya el índice columnstore para habilitar el pruner en las columnas de cadena.
En PolarDB MySQL versión 8.0.1.1.34 o anterior, o bien 8.0.2.2.15 o anterior, los pruner permanecen en memoria de forma permanente y no se construyen automáticamente para columnas de cadena.
En PolarDB MySQL versión 8.0.1.1.32 o anterior, o bien 8.0.2.2.13 o anterior, no se construyen pruner para bloques de datos que contienen valores NULL. No se admite el filtrado con IS NULL ni con IS NOT NULL.
Antes de agregar nuevos pruner, compruebe los tipos de pruner existentes en cada columna. Consulte Check pruner status on table columns.

Construir un pruner al crear una tabla

Los atributos del pruner se establecen en el campo COMMENT de una instrucción CREATE TABLE. Los atributos a nivel de tabla se aplican a todas las columnas compatibles, mientras que los atributos a nivel de columna se aplican únicamente a la columna especificada.

PRUNER_BLOOM , PRUNER_MINMAX y PREFIX_LEN requieren PolarDB MySQL 8.0.1.1.32 o posterior, o bien 8.0.2.2.13 o posterior.

Filtro Bloom

Construya un filtro Bloom para todas las columnas compatibles:

CREATE TABLE t1 (
  id       INT PRIMARY KEY,
  str_col1 CHAR(10),
  str_col2 VARCHAR(10)
) ENGINE=InnoDB COMMENT "COLUMNAR=1 PRUNER_BLOOM=1";

Construya un filtro Bloom solo para una columna específica:

CREATE TABLE t1 (
  id       INT PRIMARY KEY,
  str_col1 CHAR(10) COMMENT "PRUNER_BLOOM=1",
  str_col2 VARCHAR(10)
) ENGINE=InnoDB COMMENT "COLUMNAR=1";

Índice minmax

En columnas de cadena largas, el índice compara un prefijo de la cadena con los valores mínimo y máximo almacenados para reducir el uso de memoria. La longitud predeterminada del prefijo es de 20 caracteres y el máximo es de 255 caracteres. El conteo se basa en la cantidad de caracteres, no en la longitud en bytes. Por ejemplo, los primeros 5 caracteres de "Alibaba Cloud PolarDB" son "Aliba".

Construya índices minmax para todas las columnas de cadena:

CREATE TABLE t1 (
  id       INT PRIMARY KEY,
  str_col1 CHAR(10),
  str_col2 VARCHAR(10)
) ENGINE=InnoDB COMMENT "COLUMNAR=1 PRUNER_MINMAX=1 PREFIX_LEN=30";

Construya índices minmax con distintas longitudes de prefijo por columna:

CREATE TABLE t1 (
  id       INT PRIMARY KEY,
  str_col1 CHAR(10)    COMMENT "PRUNER_MINMAX=1 PREFIX_LEN=30",
  str_col2 VARCHAR(10) COMMENT "PRUNER_MINMAX=1 PREFIX_LEN=10"
) ENGINE=InnoDB COMMENT "COLUMNAR=1";

Filtro Bloom de prefijo

Requiere PolarDB MySQL 8.0.1.1.42 o posterior, o bien 8.0.2.2.25 o posterior.

El filtro Bloom de prefijo se construye sobre prefijos de cadena para reducir la sobrecarga de rendimiento y almacenamiento que generan las cadenas largas.

Construya un filtro Bloom de prefijo para todas las columnas compatibles:

CREATE TABLE t1 (
  id       INT PRIMARY KEY,
  str_col1 CHAR(10),
  str_col2 VARCHAR(10)
) ENGINE=InnoDB COMMENT "COLUMNAR=1 PRUNER_PREFIX_BLOOM=1";

Construya un filtro Bloom de prefijo para una columna específica:

CREATE TABLE t1 (
  id       INT PRIMARY KEY,
  str_col1 CHAR(10) COMMENT "PRUNER_PREFIX_BLOOM=1",
  str_col2 VARCHAR(10)
) ENGINE=InnoDB COMMENT "COLUMNAR=1";

Filtro Bloom de token

Requiere PolarDB MySQL 8.0.1.1.39 o posterior, o bien 8.0.2.2.20 o posterior.

Construya un filtro Bloom de token para todas las columnas compatibles:

CREATE TABLE t1 (
  id       INT PRIMARY KEY,
  str_col1 CHAR(10),
  str_col2 VARCHAR(10)
) ENGINE=InnoDB COMMENT "COLUMNAR=1 PRUNER_TOKEN_BLOOM=1";

Construya un filtro Bloom de token para una columna específica:

CREATE TABLE t1 (
  id       INT PRIMARY KEY,
  str_col1 CHAR(10) COMMENT "PRUNER_TOKEN_BLOOM=1",
  str_col2 VARCHAR(10)
) ENGINE=InnoDB COMMENT "COLUMNAR=1";

Filtro Bloom de n-gramas

Requiere PolarDB MySQL 8.0.1.1.39 o posterior, o bien 8.0.2.2.20 o posterior. PRUNER_NGRAM_BLOOM=N establece la longitud de la subcadena; N debe ser 2 o mayor. Establezca N con un valor menor o igual a la longitud de la cadena de búsqueda en LIKE "%string%" . Si la cadena de búsqueda es más corta que N, no se puede usar el filtro Bloom de n-gramas.

Construya un filtro Bloom de n-gramas para todas las columnas compatibles (N=2):

CREATE TABLE t1 (
  id       INT PRIMARY KEY,
  str_col1 CHAR(10),
  str_col2 VARCHAR(10)
) ENGINE=InnoDB COMMENT "COLUMNAR=1 PRUNER_NGRAM_BLOOM=2";

Construya un filtro Bloom de n-gramas para una columna específica:

CREATE TABLE t1 (
  id       INT PRIMARY KEY,
  str_col1 CHAR(10) COMMENT "PRUNER_NGRAM_BLOOM=3",
  str_col2 VARCHAR(10)
) ENGINE=InnoDB COMMENT "COLUMNAR=1";

Agregar o quitar un pruner en una tabla existente

Para modificar un pruner en una tabla existente, actualice el atributo COMMENT y reconstruya el índice columnstore eliminándolo y volviéndolo a crear.

A continuación se muestran ejemplos con PRUNER_MINMAX para ilustrar los pasos. El mismo patrón aplica para otros tipos de pruner.

Agregar un pruner

Agregue índices minmax a todas las columnas de cadena de la tabla t1:

Suponga que la estructura original de la tabla es la siguiente:

CREATE TABLE `t1` (
  `id`       int(11) NOT NULL,
  `str_col1` char(10) DEFAULT NULL,
  `str_col2` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  COLUMNAR INDEX (`id`,`str_col1`,`str_col2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='COLUMNAR=1'

Elimine el índice columnstore:
```
ALTER TABLE t1 COMMENT = "COLUMNAR=0";
```

Agregue PRUNER_MINMAX y reconstruya el índice:

ALTER TABLE t1 COMMENT = "COLUMNAR=1 PRUNER_MINMAX=1";

Compruebe la estructura actualizada de la tabla:

SHOW CREATE TABLE t1 FULL \G

Resultado esperado:

*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `str_col1` char(10) DEFAULT NULL,
  `str_col2` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  COLUMNAR INDEX (`id`,`str_col1`,`str_col2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='COLUMNAR=1 PRUNER_MINMAX=1'

Ahora el atributo PRUNER_MINMAX está establecido en t1.

Agregue un índice minmax a una columna específica (str_col1) en la tabla t1:

Agregue PRUNER_MINMAX al comentario de la columna:

ALTER TABLE t1 MODIFY COLUMN str_col1 CHAR(10) COMMENT 'PRUNER_MINMAX=1';

Reconstruya el índice columnstore:

ALTER TABLE t1 COMMENT = "COLUMNAR=0";
ALTER TABLE t1 COMMENT = "COLUMNAR=1";

Compruebe la estructura actualizada de la tabla:

SHOW CREATE TABLE t1 FULL \G

Resultado esperado:

*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `str_col1` char(10) DEFAULT NULL COMMENT 'PRUNER_MINMAX=1',
  `str_col2` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  COLUMNAR INDEX (`id`,`str_col1`,`str_col2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='COLUMNAR=1'

La columna str_col1 ahora tiene el atributo PRUNER_MINMAX.

Quitar un pruner

Quite el atributo PRUNER_MINMAX a nivel de tabla:

Suponga que t1 tiene PRUNER_MINMAX=1 en el comentario de la tabla.

Elimine el índice columnstore:
```
ALTER TABLE t1 COMMENT = "COLUMNAR=0";
```

Establezca PRUNER_MINMAX=0 y reconstruya el índice:

ALTER TABLE t1 COMMENT = "COLUMNAR=1 PRUNER_MINMAX=0";

Compruebe la estructura actualizada de la tabla:

SHOW CREATE TABLE t1 FULL \G

Resultado esperado:

*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `str_col1` char(10) DEFAULT NULL,
  `str_col2` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  COLUMNAR INDEX (`id`,`str_col1`,`str_col2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='COLUMNAR=1'

El atributo PRUNER_MINMAX se quitó de t1.

Quite el atributo PRUNER_MINMAX a nivel de columna de str_col1:

Suponga que str_col1 tiene COMMENT 'PRUNER_MINMAX=1'.

Establezca PRUNER_MINMAX=0 en la columna:

ALTER TABLE t1 MODIFY COLUMN str_col1 CHAR(10) COMMENT 'PRUNER_MINMAX=0';

Reconstruya el índice columnstore:

ALTER TABLE t1 COMMENT = "COLUMNAR=0";
ALTER TABLE t1 COMMENT = "COLUMNAR=1";

Compruebe la estructura actualizada de la tabla:

SHOW CREATE TABLE t1 FULL \G

Resultado esperado:

*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `str_col1` char(10) DEFAULT NULL,
  `str_col2` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  COLUMNAR INDEX (`id`,`str_col1`,`str_col2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='COLUMNAR=1'

El atributo PRUNER_MINMAX se quitó de str_col1.

Comprobar el estado de los pruner en las columnas de la tabla

PolarDB MySQL 8.0.1.1.42 o posterior, o bien 8.0.2.2.25 o posterior

Consulte information_schema.imci_secondary_indexes. El campo INDEX_TYPE enumera los tipos de pruner activos para cada columna de cadena.

SELECT * FROM information_schema.imci_secondary_indexes \G

Ejemplo de resultado:

*************************** 1. row ***************************
         TABLE_ID: 1111
      SCHEMA_NAME: test_imci
       TABLE_NAME: lineitem
      COLUMN_NAME: l_returnflag
       INDEX_TYPE: MinMax,PrefixBloom   -- pruner types active on this column
  MINMAX_RE_PACKS: 0                   -- blocks skipped by minmax pruner (cumulative)
  MINMAX_AC_PACKS: 0                   -- blocks fully accepted by minmax (no per-row predicate needed)
MINMAX_TEST_PACKS: 0                   -- total minmax evaluations (cumulative)
   BLOOM_RE_PACKS: 0                   -- blocks skipped by bloom filter (cumulative)
 BLOOM_TEST_PACKS: 0                   -- total bloom filter evaluations (cumulative)

Versiones anteriores

Consulte information_schema.imci_secondary_indexes. Los campos STR_BLOOM_PRUNER y STR_MINMAX_PRUNER indican el estado del pruner: 1 significa que el pruner está construido.

SELECT * FROM information_schema.imci_secondary_indexes
WHERE schema_name='test_tmp' AND table_name='t1' \G

Ejemplo de resultado:

*************************** 1. row ***************************
         TABLE_ID: 1091
      SCHEMA_NAME: test_tmp
       TABLE_NAME: t1
      COLUMN_NAME: str_col1
 STR_BLOOM_PRUNER: 1   -- bloom filter built for str_col1
STR_MINMAX_PRUNER: 1   -- minmax built for str_col1
    SINDEX_SWITCH: 0
*************************** 2. row ***************************
         TABLE_ID: 1091
      SCHEMA_NAME: test_tmp
       TABLE_NAME: t1
      COLUMN_NAME: str_col2
 STR_BLOOM_PRUNER: 1   -- bloom filter built for str_col2
STR_MINMAX_PRUNER: 1   -- minmax built for str_col2
    SINDEX_SWITCH: 0

Comprobar la eficacia del pruner

Comprobación mediante SHOW STATUS

Ejecute SHOW STATUS LIKE 'imci_pruner%' antes y después de una consulta para medir cuántos bloques de datos omitió el pruner. Los valores son acumulativos para la sesión actual; en consultas de varias tablas, los resultados se suman entre todas las tablas.

Variable de estado	Descripción
`imci_pruner_accepted`	Bloques que satisfacen completamente la condición del filtro. Estos bloques omiten el filtrado por fila, aunque la materialización aún podría acceder a ellos.
`imci_pruner_rejected`	Bloques que no cumplen en absoluto la condición del filtro. No se realizan operaciones de E/S para estos bloques.

Total de bloques omitidos = imci_pruner_accepted + imci_pruner_rejected.

Example

A continuación se muestra cómo comprobar que el pruner es eficaz para SELECT COUNT(1) FROM t1 WHERE str_col1='polardb' en una tabla con 10 bloques de datos, donde las filas coincidentes se concentran en un solo bloque.

Esquema de la tabla:

CREATE TABLE `t1` (
  `id`       int(11) NOT NULL,
  `str_col1` char(10) DEFAULT NULL,
  `str_col2` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  COLUMNAR INDEX (`id`,`str_col1`,`str_col2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='COLUMNAR=1 PRUNER=1'

Compruebe el estado inicial del pruner:

SHOW STATUS LIKE 'imci_pruner%';

Resultado:

+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| imci_pruner_accepted | 0     |
| imci_pruner_rejected | 0     |
+----------------------+-------+

Ejecute la consulta:

SELECT COUNT(1) FROM t1 WHERE str_col1='polardb';

Resultado:

+----------+
| count(1) |
+----------+
|        1 |
+----------+

Vuelva a comprobar el estado del pruner:

SHOW STATUS LIKE 'imci_pruner%';

Resultado:

+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| imci_pruner_accepted | 0     |
| imci_pruner_rejected | 9     |
+----------------------+-------+

El valor de imci_pruner_rejected aumentó en 9, lo que confirma que el pruner omitió 9 de los 10 bloques.

Comprobación mediante imci_sql_profiling

Requiere PolarDB MySQL 8.0.1.1.5 o posterior, o bien 8.0.2.2.29 o posterior.

information_schema.imci_sql_profiling muestra el filtrado de bloques a nivel de tabla para una consulta específica, vinculado directamente al plan de ejecución de dicha consulta. A diferencia de SHOW STATUS, cuyo alcance es la sesión y acumula valores, esta vista ofrece resultados por consulta sin necesidad de calcular diferencias manualmente.

Habilite el perfilado de consultas:
```
SET imci_analyze_query = ON;
```
Ejecute la consulta:
```
SELECT COUNT(*) FROM t1 WHERE v1 > 100;
```

Consulte la tabla de perfilado:

SELECT `Operator`, `Extra Info` FROM INFORMATION_SCHEMA.IMCI_SQL_PROFILING;

Ejemplo de resultado:

+----+--------------------------------------------------+-----------------------------------------------------------------------------------------------------------+
| ID | Operator                                         | Extra Info                                                                                                |
+----+--------------------------------------------------+-----------------------------------------------------------------------------------------------------------+
|  1 | Select Statement                                 | IMCI Execution Plan (max_dop = 32, real_dop = 32, max_query_mem = unlimited, real_query_mem = unlimited)  |
|  2 | └─Aggregation                                    |                                                                                                           |
|  3 |   └─Table Scan  Cond: (v1 > 100), Pruner Counter: [AC: 8, RE: 1, PA: 2] |                                                                                               |
+----+--------------------------------------------------+-----------------------------------------------------------------------------------------------------------+

El valor Pruner Counter en la fila Table Scan detalla el filtrado de bloques para esta consulta:

Contador	Valor	Significado
`AC`	8	Bloques aceptados (todas las filas coinciden; se omite el filtrado por fila)
`RE`	1	Bloques rechazados (ninguna fila coincide; no hay operaciones de E/S)
`PA`	2	Bloques con coincidencias parciales (requieren filtrado adicional por fila)

Prueba de rendimiento

La siguiente prueba se ejecutó en una tabla con 120 millones de filas (aproximadamente 1,800 bloques de datos) sobre un clúster de 2 núcleos y 4 GB. La columna de cadena col contenía 80 millones de valores distintos. La consulta de prueba fue:

SELECT COUNT(1) FROM t1 WHERE col = 'xxx'

Configuración	Tiempo de consulta
Con filtro Bloom	0.15 s
Sin filtro Bloom	18.6 s

Con el filtro Bloom habilitado, el pruner descartó la mayoría de los 1,800 bloques. Solo se escanearon los bloques que podían contener el valor objetivo, lo que redujo el tiempo de consulta de 18.6 s a 0.15 s.