Como as estatísticas de índice columnstore otimizam consultas - PolarDB: Central de Documentação do Alibaba Cloud

O pruner de índice columnstore ignora blocos de dados que não podem conter linhas correspondentes antes do início da varredura, reduzindo a E/S e melhorando o desempenho de consultas em tabelas grandes. Este tópico descreve os quatro tipos de pruner, quando usar cada um, como configurá-los e como verificar se estão funcionando.

Como os pruners funcionam

Os dados do índice columnstore são armazenados em blocos de dados de coluna única, com granularidade padrão de 64.000 linhas por bloco. Todos os pacotes de colunas de um conjunto de linhas formam um grupo de linhas. A varredura percorre todos os blocos de dados de uma coluna especificada, a menos que um pruner comprove a ausência de linhas correspondentes no bloco — nesse caso, o bloco é totalmente ignorado.

Estime o número de blocos de dados de uma tabela com esta fórmula:

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

Em tabelas grandes demais para caber na memória, ignorar mesmo uma fração dos blocos reduz mensuravelmente o tempo de varredura.

Tipos de pruner

Há suporte a quatro tipos de pruner. Índices minmax para tipos numéricos (INT, DECIMAL, DATETIME) são criados automaticamente ao criar um índice columnstore. No PolarDB MySQL 8.0.1.1.35 ou posterior, ou 8.0.2.2.16 ou posterior, pruners para colunas de string também são criados automaticamente. Em versões anteriores, pruners de string exigem configuração explícita.

Pruner	Como filtra	Ideal para	Não indicado para
Bloom filter	Array de bits que testa a pertinência de elementos	Condições de igualdade (`=`) e `IN` em colunas de string de alta cardinalidade	Tipos numéricos; colunas JSON, BLOB, TEXT
Índice minmax	Armazena valores mínimo/máximo por bloco; ignora blocos fora do intervalo de filtro	Filtros de intervalo e igualdade em colunas ordenadas ou de data	Colunas JSON, GEOMETRY
Token bloom filter	Divide strings em caracteres não alfanuméricos (por exemplo, `"I am IMCI"` → `I	am	IMCI`)	Consultas `LIKE` em texto de linguagem natural	Tipos numéricos
N-gram bloom filter	Divide strings em substrings de comprimento fixo (por exemplo, `"storage"` com N=3 → `sto	tor	ora	rag	age`)	Buscas de substring `LIKE "%string%"`	Tipos numéricos; consultas em que a string de busca é menor que N

Índices minmax em tipos numéricos são criados automaticamente ao criar um índice columnstore. Essas colunas não exigem configuração adicional.

Quando usar pruners

Use um pruner quando:

Os valores de filtro forem esparsos entre os blocos de dados — a maioria dos blocos não contém linhas correspondentes.
A coluna tiver forte localidade de dados — valores semelhantes se agruparem na ordem de inserção (comum em colunas de data ou IDs ordenados).
Executar buscas LIKE "%substring%" em colunas de string grandes.

Evite um pruner quando:

Os valores correspondentes estiverem distribuídos uniformemente por todos os blocos — o pruner avalia cada bloco, mas não consegue ignorar nenhum, adicionando sobrecarga sem benefício.
Aplicar um bloom filter a uma coluna em que valores distintos representam 3% ou menos do total de linhas e os dados estão distribuídos uniformemente — a eficácia depende da localidade, e o desempenho pode ser pior do que sem o pruner.
O tipo da coluna não for suportado (consulte a tabela em Tipos de pruner).

Importante

Adicionar um pruner à coluna errada pode prejudicar o desempenho em vez de ajudar. Um bloom filter não consegue ignorar nenhum bloco se o valor alvo aparecer em todos os blocos. Teste com dados realistas antes de ativar pruners em tabelas de produção.

Uso de memória

Ativar pruners em colunas de string adiciona sobrecarga de memória. Use estas fórmulas para estimar o uso antes de ativar pruners em tabelas grandes.

Bloom filter, token bloom filter ou n-gram bloom filter

Quando a contagem de valores distintos excede 3% do total de linhas:

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

Quando a contagem de valores distintos é de 3% ou menos do total de linhas:

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

Exemplo: Uma tabela com 2 bilhões de linhas, índices minmax em 10 colunas, comprimento de prefixo de 20 caracteres, tamanho de bloco de 64.000 e codificação utf8mb4 (4 bytes por caractere) usa aproximadamente 46 MB de memória.

Notas de uso

Strings não devem conter \0 (por exemplo, 'polar\0db') para que um pruner seja criado nessa coluna.
Atributos COMMENT no nível da coluna têm precedência sobre atributos COMMENT no nível da tabela.
Para adicionar ou remover um pruner em uma tabela existente, exclua o índice columnstore e recrie-o. Atualize o atributo COMMENT antes de reconstruir.
No PolarDB MySQL versão 8.0.1.1.35 ou posterior, ou 8.0.2.2.16 ou posterior, pruners para colunas de string são criados automaticamente ao criar um índice columnstore. Bloom filters usam um cache Least Recently Used (LRU) para gerenciamento de memória. Se atualizar de uma versão mais antiga, reconstrua o índice columnstore para ativar o pruner para colunas de string.
No PolarDB MySQL versão 8.0.1.1.34 ou anterior, ou 8.0.2.2.15 ou anterior, os pruners permanecem na memória permanentemente e não são criados automaticamente para colunas de string.
Para o PolarDB MySQL versão 8.0.1.1.32 ou anterior, ou 8.0.2.2.13 ou anterior, pruners não são criados para blocos de dados que contêm valores NULL. Não há suporte a filtragem com IS NULL ou IS NOT NULL.
Antes de adicionar novos pruners, verifique os tipos de pruner existentes em cada coluna. Consulte Verificar status do pruner nas colunas da tabela.

Criar um pruner ao criar uma tabela

Defina os atributos do pruner no campo COMMENT de uma instrução CREATE TABLE. Atributos no nível da tabela aplicam-se a todas as colunas suportadas; atributos no nível da coluna aplicam-se apenas à coluna especificada.

PRUNER_BLOOM , PRUNER_MINMAX e PREFIX_LEN exigem PolarDB MySQL 8.0.1.1.32 ou posterior, ou 8.0.2.2.13 ou posterior.

Bloom filter

Crie um bloom filter para todas as colunas suportadas:

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

Crie um bloom filter apenas para uma coluna 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

Para colunas de string longas, o índice compara um prefixo da string com os valores mínimo/máximo armazenados para reduzir o uso de memória. O comprimento padrão do prefixo é de 20 caracteres; o máximo é de 255 caracteres. A contagem baseia-se no número de caracteres, não no comprimento em bytes. Por exemplo, os primeiros 5 caracteres de "Alibaba Cloud PolarDB" são "Aliba".

Crie índices minmax para todas as colunas de string:

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";

Crie índices minmax com comprimentos de prefixo diferentes por coluna:

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";

Prefix bloom filter

Exige PolarDB MySQL 8.0.1.1.42 ou posterior, ou 8.0.2.2.25 ou posterior.

O prefix bloom filter baseia-se em prefixos de string para reduzir a sobrecarga de desempenho e armazenamento causada por strings longas.

Crie um prefix bloom filter para todas as colunas suportadas:

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

Crie um prefix bloom filter para uma coluna 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";

Token bloom filter

Exige PolarDB MySQL 8.0.1.1.39 ou posterior, ou 8.0.2.2.20 ou posterior.

Crie um token bloom filter para todas as colunas suportadas:

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

Crie um token bloom filter para uma coluna 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";

N-gram bloom filter

Exige PolarDB MySQL 8.0.1.1.39 ou posterior, ou 8.0.2.2.20 ou posterior. PRUNER_NGRAM_BLOOM=N define o comprimento da substring; N deve ser 2 ou maior. Defina N com um valor menor ou igual ao comprimento da string de busca em LIKE "%string%" . Se a string de busca for menor que N, o n-gram bloom filter não poderá ser usado.

Crie um n-gram bloom filter para todas as colunas suportadas (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";

Crie um n-gram bloom filter para uma coluna 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";

Adicionar ou remover um pruner em uma tabela existente

Para modificar um pruner em uma tabela existente, atualize o atributo COMMENT e reconstrua o índice columnstore excluindo-o e recriando-o.

Os exemplos a seguir usam PRUNER_MINMAX para ilustrar as etapas. O mesmo padrão se aplica a outros tipos de pruner.

Adicionar um pruner

Adicionar índices minmax a todas as colunas de string na tabela t1:

Considere que a estrutura original da tabela seja:

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'

Exclua o índice columnstore:
```
ALTER TABLE t1 COMMENT = "COLUMNAR=0";
```

Adicione PRUNER_MINMAX e reconstrua o índice:

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

Verifique a estrutura atualizada da tabela:

SHOW CREATE TABLE t1 FULL \G

Saída esperada:

*************************** 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'

O atributo PRUNER_MINMAX agora está definido em t1.

Adicionar um índice minmax a uma coluna específica (str_col1) na tabela t1:

Adicione PRUNER_MINMAX ao comentário da coluna:

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

Reconstrua o índice columnstore:

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

Verifique a estrutura atualizada da tabela:

SHOW CREATE TABLE t1 FULL \G

Saída esperada:

*************************** 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'

A coluna str_col1 agora possui o atributo PRUNER_MINMAX.

Remover um pruner

Remover o atributo PRUNER_MINMAX no nível da tabela:

Considere que t1 tenha PRUNER_MINMAX=1 no comentário da tabela.

Exclua o índice columnstore:
```
ALTER TABLE t1 COMMENT = "COLUMNAR=0";
```

Defina PRUNER_MINMAX=0 e reconstrua o índice:

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

Verifique a estrutura atualizada da tabela:

SHOW CREATE TABLE t1 FULL \G

Saída esperada:

*************************** 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'

O atributo PRUNER_MINMAX foi removido de t1.

Remover o atributo PRUNER_MINMAX no nível da coluna de str_col1:

Considere que str_col1 tenha COMMENT 'PRUNER_MINMAX=1'.

Defina PRUNER_MINMAX=0 na coluna:

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

Reconstrua o índice columnstore:

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

Verifique a estrutura atualizada da tabela:

SHOW CREATE TABLE t1 FULL \G

Saída esperada:

*************************** 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'

O atributo PRUNER_MINMAX foi removido de str_col1.

Verificar status do pruner nas colunas da tabela

PolarDB MySQL 8.0.1.1.42 ou posterior, ou 8.0.2.2.25 ou posterior

Consulte information_schema.imci_secondary_indexes. O campo INDEX_TYPE lista os tipos de pruner ativos para cada coluna de string.

SELECT * FROM information_schema.imci_secondary_indexes \G

Exemplo de saída:

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

Versões anteriores

Consulte information_schema.imci_secondary_indexes. Os campos STR_BLOOM_PRUNER e STR_MINMAX_PRUNER indicam o status do pruner: 1 significa que o pruner foi criado.

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

Exemplo de saída:

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

Verificar a eficácia do pruner

Verificar usando SHOW STATUS

Execute SHOW STATUS LIKE 'imci_pruner%' antes e depois de uma consulta para medir quantos blocos de dados o pruner ignorou. Os valores são cumulativos para a sessão atual; para consultas com múltiplas tabelas, os resultados são somados em todas as tabelas.

Variável de status	Descrição
`imci_pruner_accepted`	Blocos que satisfazem totalmente a condição de filtro. Esses blocos ignoram a filtragem linha a linha, embora a materialização ainda possa acessá-los.
`imci_pruner_rejected`	Blocos que falham completamente na condição de filtro. Nenhuma E/S ocorre para esses blocos.

Total de blocos ignorados = imci_pruner_accepted + imci_pruner_rejected.

Example

O exemplo a seguir mostra como verificar se o pruner é eficaz para SELECT COUNT(1) FROM t1 WHERE str_col1='polardb' em uma tabela com 10 blocos de dados, onde as linhas correspondentes estão concentradas em um único bloco.

Esquema da tabela:

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'

Verifique o status inicial do pruner:

SHOW STATUS LIKE 'imci_pruner%';

Saída:

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

Execute a consulta:

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

Saída:

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

Verifique o status do pruner novamente:

SHOW STATUS LIKE 'imci_pruner%';

Saída:

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

imci_pruner_rejected aumentou em 9, confirmando que o pruner ignorou 9 de 10 blocos.

Verificar usando imci_sql_profiling

Exige PolarDB MySQL 8.0.1.1.5 ou posterior, ou 8.0.2.2.29 ou posterior.

information_schema.imci_sql_profiling mostra a filtragem de blocos no nível da tabela para uma consulta específica, vinculada diretamente ao plano de execução dessa consulta. Ao contrário de SHOW STATUS, que tem escopo de sessão e é cumulativo, esta visualização fornece resultados por consulta sem exigir cálculo manual de delta.

Ative o profiling de consulta:
```
SET imci_analyze_query = ON;
```
Execute a consulta:
```
SELECT COUNT(*) FROM t1 WHERE v1 > 100;
```

Verifique a tabela de profiling:

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

Exemplo de saída:

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

O Pruner Counter na linha Table Scan detalha a filtragem de blocos para esta consulta:

Contador	Valor	Significado
`AC`	8	Blocos aceitos (todas as linhas correspondem; ignora filtragem linha a linha)
`RE`	1	Blocos rejeitados (nenhuma linha corresponde; sem E/S)
`PA`	2	Blocos parcialmente correspondidos (exigem filtragem linha a linha adicional)

Benchmark de desempenho

O teste a seguir foi executado em uma tabela com 120 milhões de linhas (aproximadamente 1.800 blocos de dados) em um cluster de 2 núcleos e 4 GB. A coluna de string col tinha 80 milhões de valores distintos. A consulta de teste foi:

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

Configuração	Tempo de consulta
Com bloom filter	0,15 s
Sem bloom filter	18,6 s

Com o bloom filter ativado, o pruner eliminou a maioria dos 1.800 blocos. Apenas os blocos que poderiam conter o valor alvo foram varridos, reduzindo o tempo de consulta de 18,6 s para 0,15 s.