O sistema grava fisicamente os dados de um índice columnstore na ordem da chave primária e, para linhas atualizadas, na ordem de anexação. Por isso, os dados geralmente não estão ordenados. Sem uma ordenação intencional, o pruner do In-Memory Column Index (IMCI) precisa ler mais blocos de dados de coluna do que o necessário para atender a uma consulta. As chaves de ordenação permitem controlar a ordem física dos blocos de dados de coluna para que o pruner ignore blocos irrelevantes e reduza a E/S.
Como funciona
Os dados do índice columnstore são organizados em grupos de 64.000 linhas cada. Dentro de cada grupo, as colunas são empacotadas em blocos de dados de coluna. Cada bloco armazena os valores mínimo e máximo de seus dados como metadados (um índice aproximado). Ao executar uma consulta, o pruner do IMCI classifica todos os blocos de dados de coluna em três categorias com base no predicado da consulta e nesses metadados:
Relevante — leitura obrigatória
Possivelmente relevante — lido como candidato
Irrelevante — totalmente ignorado
A ordem física dos blocos de dados de coluna determina a eficácia dessa poda. Para uma consulta como SELECT * FROM t WHERE c >= 8, um conjunto não ordenado de blocos exige o carregamento de todos eles. Já um conjunto ordenado permite que o pruner ignore qualquer bloco cujo valor máximo seja menor que 8.
Quando usar chaves de ordenação
As chaves de ordenação são mais eficazes quando:
A tabela é grande (centenas de gigabytes ou mais).
As consultas são seletivas, ou seja, filtram um pequeno subconjunto de linhas em vez de varrer a tabela inteira.
Uma grande porcentagem das consultas utiliza as mesmas colunas de filtro.
O uso de chaves de ordenação é menos vantajoso quando:
As consultas varrem a maior parte da tabela sem filtros seletivos.
O throughput de escrita é mais importante que o desempenho de leitura. A ordenação incremental desacelera durante cargas altas de escrita para liberar recursos para essas operações.
O tempo de build é restrito. A ordenação aumenta significativamente o tempo de criação do índice (consulte Referência de desempenho).
Pré-requisitos
Antes de começar, verifique se você possui:
-
Um cluster PolarDB for MySQL Enterprise Edition em uma das seguintes versões:
Ordenação de dados ao criar um novo índice columnstore:
Versão
Revisão mínima
PolarDB for MySQL 8.0.1
8.0.1.1.32
PolarDB for MySQL 8.0.2
8.0.2.2.12
Ordenação incremental:
Versão
Revisão mínima
PolarDB for MySQL 8.0.1
8.0.1.1.39.1
PolarDB for MySQL 8.0.2
8.0.2.2.20.1
Para verificar a versão do seu cluster, consulte Consultar o número da versão.
Limitações
Não é possível usar colunas BLOB, JSON e GEOMETRY como chaves de ordenação.
A ordenação incremental não suporta colunas de inteiro sem sinal ou Decimal como chaves de ordenação.
A ordenação incremental mantém a ordem com base apenas na primeira coluna da chave de ordenação. O sistema não mantém colunas adicionais da chave de forma incremental.
Durante cargas altas de escrita, a ordenação incremental desacelera para liberar recursos para as operações de escrita.
Escolha colunas de chave de ordenação
A escolha correta das colunas determina o benefício obtido com a poda de blocos. Siga estas diretrizes:
Priorize colunas usadas em filtros de intervalo ou igualdade. Estas obtêm o maior benefício da poda por mínimo-máximo. Para consultas de intervalo de datas (por exemplo,
WHERE order_date BETWEEN ... AND ...), a coluna de data é uma forte candidata.Prefira colunas de menor cardinalidade como chave principal. O pruner ignora blocos com mais eficácia quando a coluna principal da chave de ordenação tem cardinalidade relativamente baixa. Uma coluna com cardinalidade muito alta (por exemplo, um ID de usuário único) gera blocos com intervalos mínimo-máximo amplos, o que reduz a eficácia da exclusão.
Adicione colunas de predicado de junção como chaves secundárias se houver capacidade restante.
Limite o número de colunas na chave de ordenação. Colunas adicionais aumentam o tempo de build com benefícios decrescentes de poda.
Configure chaves de ordenação
Etapa 1: Ative a ordenação de dados
Defina o parâmetro imci_enable_pack_order_key como ON.
Este parâmetro é ON por padrão. Se você não o alterou, a ordenação já está ativa para índices columnstore recém-criados.
Para diferenças de nomenclatura de parâmetros entre o console do PolarDB e uma sessão de banco de dados, consulte Parâmetros.
Etapa 2: Adicione chaves de ordenação à tabela
Execute a seguinte instrução para especificar as colunas de chave de ordenação:
ALTER TABLE table_name COMMENT 'columnar=1 order_key=column_name[,column_name]';|
Parâmetro |
Descrição |
|
|
O nome da tabela. |
|
|
A coluna a ser usada como chave de ordenação. Separe várias colunas por vírgulas. |
Exemplo — ordene a tabela lineitem pela data de recebimento e modo de envio:
ALTER TABLE lineitem COMMENT='columnar=1 order_key=l_receiptdate,l_shipmode';Etapa 3: Monitore o progresso do build
Consulte INFORMATION_SCHEMA.IMCI_ASYNC_DDL_STATS para acompanhar o progresso do build:
SELECT * FROM INFORMATION_SCHEMA.IMCI_ASYNC_DDL_STATS;Para descrições das colunas, consulte Visualizar velocidade de execução de DDL e progresso de build para IMCIs.
Parâmetros
Para ativar ou desativar a ordenação e configurar o paralelismo, defina os seguintes parâmetros no seu cluster.
Os nomes dos parâmetros diferem entre o console do PolarDB e uma sessão de banco de dados.
PolarDB console: Use nomes de parâmetros com o prefixoloose_(por exemplo,loose_imci_enable_pack_order_key). O console adiciona esse prefixo para compatibilidade com arquivos de configuração do MySQL.
Sessão de banco de dados (cliente SQL ou linha de comando): Remova o prefixoloose_e use o nome base do parâmetro (por exemplo,imci_enable_pack_order_key).
| Parâmetro | Descrição | Padrão |
|---|---|---|
loose_imci_enable_pack_order_key | Controla a ordenação de dados ao criar um novo índice columnstore. Defina como ON para ativar ou OFF para desativar. | ON |
loose_imci_enable_pack_order_key_changed_rebuild | Controla a reconstrução da tabela quando a ordem de classificação mudar. Defina como ON para exigir a reconstrução ou OFF para ignorá-la. | OFF |
loose_imci_parallel_build_threads_per_table | Número de threads usadas para criar o índice columnstore para uma única tabela. Valores válidos: 1–128. | 8 |
Implementação da ordenação
Ordenação ao criar um novo índice columnstore
O processo de ordenação espelha o algoritmo de ordenação DDL (Data Definition Language) para índices secundários. Há suporte para modos single-threaded e multi-threaded:
Single-threaded: Merge sort bidirecional padrão.
Multi-threaded: Merge sort externo k-way com loser tree, com ordenação por amostragem opcional.
O processo é executado em quatro passagens:
Percorra os dados na ordem da chave primária, grave linhas completas em arquivos de dados e adicione colunas de chave de ordenação ao buffer de ordenação. Cada thread grava em seu próprio arquivo de dados.
Quando o buffer de ordenação estiver cheio, ordene seu conteúdo pela combinação da chave de ordenação e descarregue-o em arquivos de mesclagem.
Faça o merge-sort dos arquivos de mesclagem em pares, grave a saída ordenada em arquivos temporários e substitua os arquivos de mesclagem pelos arquivos temporários.
Repita a passagem 3 até que todos os arquivos de mesclagem estejam ordenados. Em seguida, leia cada registro dos arquivos de mesclagem, recupere a linha completa dos arquivos de dados usando o deslocamento e anexe-a ao índice columnstore.
Ordenação incremental
A ordenação incremental é progressiva: ela melhora a ordem dos blocos de dados ao longo do tempo, mas não garante uma ordenação completa. O processo:
Agrupe todos os blocos de dados em pares, selecionando grupos com alta sobreposição em seus intervalos de timestamp.
Faça o merge-sort de cada par para produzir dois novos blocos de dados ordenados.
Repita até que todos os blocos de dados estejam ordenados.
A ordenação incremental mantém a ordem com base apenas na primeira coluna da chave de ordenação.
Referência de desempenho
Tempo de build vs. tempo de consulta (TPC-H de 100 GB, tabela lineitem, 16 threads)
A ordenação aumenta o tempo de build, mas reduz significativamente o tempo de consulta.
|
Conjunto de dados |
Tempo de build |
Tempo de consulta (TPC-H Q12) |
|
Não ordenado |
6 minutos |
7,47 s |
|
Ordenado |
35 minutos |
1,25 s |
Condições de teste: cache LRU = 10 GB, memória do executor = 10 GB. Consulta TPC-H Q12:
SELECT
l_shipmode,
SUM(CASE
WHEN o_orderpriority = '1-URGENT' OR o_orderpriority = '2-HIGH'
THEN 1
ELSE 0
END) AS high_line_count,
SUM(CASE
WHEN o_orderpriority <> '1-URGENT' AND o_orderpriority <> '2-HIGH'
THEN 1
ELSE 0
END) AS low_line_count
FROM
orders,
lineitem
WHERE
o_orderkey = l_orderkey
AND l_shipmode in ('MAIL', 'SHIP')
AND l_commitdate < l_receiptdate
AND l_shipdate < l_commitdate
AND l_receiptdate >= date '1994-01-01'
AND l_receiptdate < date '1994-01-01' + interval '1' year
GROUP BY
l_shipmode
ORDER BY
l_shipmode;Chaves de ordenação combinadas com particionamento (TPC-H de 1 TB, 32 núcleos, 256 GB de memória)
Os resultados a seguir comparam índices columnstore não ordenados com índices columnstore com partições e chaves de ordenação. Definições de tabela utilizadas:
CREATE TABLE region ( r_regionkey BIGINT NOT NULL,
r_name CHAR(25) NOT NULL,
r_comment VARCHAR(152)) COMMENT 'COLUMNAR=1';
CREATE TABLE nation ( n_nationkey BIGINT NOT NULL,
n_name CHAR(25) NOT NULL,
n_regionkey BIGINT NOT NULL,
n_comment VARCHAR(152)) COMMENT 'COLUMNAR=1';
CREATE TABLE part ( p_partkey BIGINT NOT NULL,
p_name VARCHAR(55) NOT NULL,
p_mfgr CHAR(25) NOT NULL,
p_brand CHAR(10) NOT NULL,
p_type VARCHAR(25) NOT NULL,
p_size BIGINT NOT NULL,
p_container CHAR(10) NOT NULL,
p_retailprice DECIMAL(15,2) NOT NULL,
p_comment VARCHAR(23) NOT NULL) COMMENT 'COLUMNAR=1';
CREATE TABLE supplier ( s_suppkey BIGINT NOT NULL,
s_name CHAR(25) NOT NULL,
s_address VARCHAR(40) NOT NULL,
s_nationkey BIGINT NOT NULL,
s_phone CHAR(15) NOT NULL,
s_acctbal DECIMAL(15,2) NOT NULL,
s_comment VARCHAR(101) NOT NULL) COMMENT 'COLUMNAR=1';
CREATE TABLE partsupp ( ps_partkey BIGINT NOT NULL,
ps_suppkey BIGINT NOT NULL,
ps_availqty BIGINT NOT NULL,
ps_supplycost DECIMAL(15,2) NOT NULL,
ps_comment VARCHAR(199) NOT NULL) COMMENT 'COLUMNAR=1';
CREATE TABLE customer ( c_custkey BIGINT NOT NULL,
c_name VARCHAR(25) NOT NULL,
c_address VARCHAR(40) NOT NULL,
c_nationkey BIGINT NOT NULL,
c_phone CHAR(15) NOT NULL,
c_acctbal DECIMAL(15,2) NOT NULL,
c_mktsegment CHAR(10) NOT NULL,
c_comment VARCHAR(117) NOT NULL) COMMENT 'COLUMNAR=1';
CREATE TABLE orders ( o_orderkey BIGINT NOT NULL,
o_custkey BIGINT NOT NULL,
o_orderstatus CHAR(1) NOT NULL,
o_totalprice DECIMAL(15,2) NOT NULL,
o_orderdate DATE NOT NULL,
o_orderpriority CHAR(15) NOT NULL,
o_clerk CHAR(15) NOT NULL,
o_shippriority BIGINT NOT NULL,
o_comment VARCHAR(79) NOT NULL) COMMENT 'COLUMNAR=1'
PARTITION BY RANGE (year(`o_orderdate`))
(PARTITION p0 VALUES LESS THAN (1992) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (1993) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (1994) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (1995) ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN (1996) ENGINE = InnoDB,
PARTITION p5 VALUES LESS THAN (1997) ENGINE = InnoDB,
PARTITION p6 VALUES LESS THAN (1998) ENGINE = InnoDB,
PARTITION p7 VALUES LESS THAN (1999) ENGINE = InnoDB);
CREATE TABLE lineitem ( l_orderkey BIGINT NOT NULL,
l_partkey BIGINT NOT NULL,
l_suppkey BIGINT NOT NULL,
l_linenumber BIGINT NOT NULL,
l_quantity DECIMAL(15,2) NOT NULL,
l_extendedprice DECIMAL(15,2) NOT NULL,
l_discount DECIMAL(15,2) NOT NULL,
l_tax DECIMAL(15,2) NOT NULL,
l_returnflag CHAR(1) NOT NULL,
l_linestatus CHAR(1) NOT NULL,
l_shipdate DATE NOT NULL,
l_commitdate DATE NOT NULL,
l_receiptdate DATE NOT NULL,
l_shipinstruct CHAR(25) NOT NULL,
l_shipmode CHAR(10) NOT NULL,
l_comment VARCHAR(44) NOT NULL) COMMENT 'COLUMNAR=1'
PARTITION BY RANGE (year(`l_shipdate`))
(PARTITION p0 VALUES LESS THAN (1992) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (1993) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (1994) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (1995) ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN (1996) ENGINE = InnoDB,
PARTITION p5 VALUES LESS THAN (1997) ENGINE = InnoDB,
PARTITION p6 VALUES LESS THAN (1998) ENGINE = InnoDB,
PARTITION p7 VALUES LESS THAN (1999) ENGINE = InnoDB);Após importar os dados, aplique as chaves de ordenação usando a sintaxe ALTER TABLE acima:
ALTER TABLE customer COMMENT='COLUMNAR=1 order_key=c_mktsegment';
ALTER TABLE nation COMMENT='COLUMNAR=1 order_key=n_name';
ALTER TABLE part COMMENT='COLUMNAR=1 order_key=p_brand,p_container,p_type';
ALTER TABLE region COMMENT='COLUMNAR=1 order_key=r_name';
ALTER TABLE orders COMMENT='COLUMNAR=1 order_key=o_orderkey,o_custkey,o_orderdate';
ALTER TABLE lineitem COMMENT='COLUMNAR=1 order_key=l_orderkey,l_linenumber,l_receiptdate,l_shipdate,l_partkey';Resultados de tempo de consulta (segundos):
|
Consulta |
Não ordenado |
Ordenado com partições e chaves de ordenação |
|
Q3 |
71.951 |
36.566 |
|
Q4 |
46.679 |
32.015 |
|
Q6 |
34.652 |
4,4 |
|
Q7 |
74.749 |
34.166 |
|
Q12 |
86.742 |
28.586 |
|
Q14 |
50.248 |
12,56 |
|
Q15 |
79,22 |
21.113 |
|
Q20 |
51.746 |
10.178 |
|
Q21 |
216.942 |
148.459 |
Diferenças em relação à ordenação DDL de índice secundário
A ordenação de índice columnstore difere da ordenação DDL de índice secundário de duas maneiras:
Flexibilidade da chave de ordenação: O DDL de índice secundário usa colunas de índice como chaves de ordenação. A ordenação de índice columnstore permite especificar qualquer combinação de colunas como chaves de ordenação, independentemente da definição do índice.
Escopo dos dados: Após a ordenação do índice columnstore, é necessário ler os dados completos da linha. O DDL de índice secundário armazena apenas a parte do índice (por exemplo, apenas o prefixo de uma coluna VARCHAR).