Melhores práticas de design de tabelas do SelectDB - ApsaraDB for SelectDB: Central de Documentação do Alibaba Cloud

Principais propriedades de tabela

Escolha as propriedades de tabela do SelectDB adequadas à sua carga de trabalho no SelectDB.

Propriedade da tabela	Obrigatório	Descrição	Referências
Modelo de dados	Sim	O modelo Unique impõe unicidade da chave primária para atualizações flexíveis e eficientes. O modelo Duplicate anexa todas as linhas para análise detalhada de alto desempenho. O modelo Aggregate pré-agrega colunas de valor para análises resumidas.	Modelos de dados
Bucketing	Sim	Distribui dados entre os nós do cluster para processamento paralelo de grandes conjuntos de dados.	Bucketing (Tablets) Visão geral do particionamento de dados
Partição	Não	Divide uma tabela em sub-tabelas por campos como tempo ou região. Permite o pruning de partições para acelerar consultas.	Partições (Partition) Visão geral do particionamento de dados
Índice	Não	Acelera consultas filtrando ou localizando dados.	Índices

Modelos de dados

Cada modelo de dados atende a diferentes cenários de análise. Modelos de dados.

Conceitos básicos

No SelectDB, os dados são organizados em tabelas. Cada tabela possui linhas (registros) e colunas (campos).

As colunas dividem-se em dois tipos principais:

Colunas de chave: Colunas especificadas por UNIQUE KEY, AGGREGATE KEY ou DUPLICATE KEY em uma instrução CREATE TABLE.
Colunas de valor: Todas as colunas que não são de chave.

Guia de seleção de modelo

O SelectDB oferece três modelos de dados: Unique, Duplicate e Aggregate.

Importante

O modelo de dados é definido na criação da tabela e não pode ser modificado posteriormente.
Se nenhum modelo for especificado durante a criação da tabela, o modelo Duplicate será usado por padrão. As três primeiras colunas serão selecionadas automaticamente como colunas de chave.
Nos modelos Unique, Duplicate e Aggregate, os dados são ordenados e armazenados pelas colunas de chave.

Tipo de modelo	Características	Cenários	Desvantagens
Unique	Cada linha possui uma chave única. Chaves duplicadas sobrescrevem as colunas de valor anteriores com os valores mais recentes.	Unicidade de chave primária ou atualizações eficientes: pedidos de e-commerce, perfis de usuários.	Visualizações materializadas síncronas suportam apenas reordenação de colunas, não agregação.
Duplicate	Permite valores de chave duplicados. Linhas com chaves idênticas são armazenadas juntas.	Alto throughput de escrita e consulta. Retém todos os registros brutos: análises de logs e faturamento.	Não suporta atualizações em dados históricos.
Aggregate	Cada linha possui uma chave única. Chaves duplicadas acionam a pré-agregação das colunas de valor conforme o método definido na criação da tabela.	Semelhante ao modelo Cube tradicional de data warehouse. A pré-agregação melhora o desempenho de consultas: tráfego de sites, relatórios personalizados.	Não oferece suporte ideal para consultas `count(*)`. O método de agregação para colunas de valor é fixo.

Início rápido com modelos

Modelo Unique

O modelo Unique mantém apenas as colunas de valor mais recentes para chaves duplicadas. Existem duas implementações: Merge on Read (MOR) e Merge on Write (MOW).

Recomenda-se o MOW devido à sua maturidade e desempenho em consultas. A alternativa é Merge on Read (MOR).

Observações

Ao criar uma tabela com o modelo Unique usando MOW:

Use UNIQUE KEY para especificar os campos da chave primária única.
Adicione a propriedade para ativar o MOW na seção PROPERTIES.
```
"enable_unique_key_merge_on_write" = "true"
```

Exemplo

Este comando cria a tabela orders com o modelo Unique, uma chave primária composta (order_id, order_time) e MOW ativado.

CREATE TABLE IF NOT EXISTS orders
(
    `order_id` LARGEINT NOT NULL COMMENT "Order ID",
    `order_time` DATETIME NOT NULL COMMENT "Order time",
    `customer_id` LARGEINT NOT NULL COMMENT "User ID",
    `total_amount` DOUBLE COMMENT "Total order amount",
    `status` VARCHAR(20) COMMENT "Order status",
    `payment_method` VARCHAR(20) COMMENT "Payment method",
    `shipping_method` VARCHAR(20) COMMENT "Shipping method",
    `customer_city` VARCHAR(20) COMMENT "User's city",
    `customer_address` VARCHAR(500) COMMENT "User's address"
)
UNIQUE KEY(`order_id`, `order_time`)
PARTITION BY RANGE(`order_time`) ()
DISTRIBUTED BY HASH(`order_id`)
PROPERTIES (
    "enable_unique_key_merge_on_write" = "true",
    "dynamic_partition.enable" = "true",
    "dynamic_partition.time_unit" = "DAY",
    "dynamic_partition.start" = "-7",
    "dynamic_partition.end" = "3",
    "dynamic_partition.prefix" = "p",
    "dynamic_partition.create_history_partition" = "true",
    "dynamic_partition.buckets" = "16"
);

Modelo Duplicate

O modelo Duplicate armazena linhas com chaves idênticas juntas, sem pré-agregação ou restrições de unicidade.

Para registrar e analisar dados de log ordenados por tempo, tipo e código de erro, utilize o modelo Duplicate. O exemplo abaixo cria uma tabela de log com o modelo Duplicate, ordenada por log_time, log_type e error_code.

CREATE TABLE IF NOT EXISTS log
(
    `log_time` DATETIME NOT NULL COMMENT "Log time",
    `log_type` INT NOT NULL COMMENT "Log type",
    `error_code` INT COMMENT "Error code",
    `error_msg` VARCHAR(1024) COMMENT "Error details",
    `op_id` BIGINT COMMENT "Owner ID",
    `op_time` DATETIME COMMENT "Processing time"
)
DUPLICATE KEY(`log_time`, `log_type`, `error_code`)
PARTITION BY RANGE(`log_time`) ()
DISTRIBUTED BY HASH(`log_type`)
PROPERTIES (
    "dynamic_partition.enable" = "true",
    "dynamic_partition.time_unit" = "DAY",
    "dynamic_partition.start" = "-7",
    "dynamic_partition.end" = "3",
    "dynamic_partition.prefix" = "p",
    "dynamic_partition.create_history_partition" = "true",
    "dynamic_partition.buckets" = "16"
);

Modelo Aggregate

Observações

No modelo Aggregate, chaves duplicadas acionam a pré-agregação das colunas de valor. Ao criar uma tabela com este modelo:

Utilize AGGREGATE KEY para definir as colunas de chave. Linhas com os mesmos valores nessas colunas serão agregadas.

Especifique o método de agregação para as colunas de valor. Os seguintes métodos são suportados:

Tipo de agregação	Descrição
`SUM`	Calcula a soma entre as linhas. Aplicável a valores numéricos.
`MIN`	Retém o valor mínimo. Aplicável a valores numéricos.
`MAX`	Retém o valor máximo. Aplicável a valores numéricos.
`REPLACE`	Substitui o valor anterior pelo novo valor importado. Para linhas com as mesmas colunas de chave, os valores são substituídos na ordem de importação.
`REPLACE_IF_NOT_NULL`	Igual ao `REPLACE`, mas ignora valores nulos. Defina `null` (não uma string vazia) como padrão da coluna; caso contrário, strings vazias serão sobrescritas.
`HLL_UNION`	Agrega colunas do tipo HyperLogLog (HLL) usando o algoritmo HLL.
`BITMAP_UNION`	Agrega colunas BITMAP usando agregação de união.

Exemplo

Para rastrear o comportamento do usuário — última visita, custo total, tempo máximo e mínimo de permanência — use o modelo Aggregate. O exemplo abaixo cria a tabela user_behavior. Quando múltiplos registros compartilham os mesmos valores nas colunas de chave (ID do usuário, data, cidade, idade e gênero), as colunas de valor são pré-agregadas:

Última visita do usuário: Utiliza-se o valor máximo do campo last_visit_date.
Consumo total do usuário: Valor total agregado a partir de múltiplos registros de dados.
Tempo máximo de permanência: Utiliza-se o valor máximo do campo max_dwell_time.
Tempo mínimo de permanência: Utiliza-se o valor mínimo do campo min_dwell_time.

CREATE TABLE IF NOT EXISTS user_behavior
(
    `user_id` LARGEINT NOT NULL COMMENT "User ID",
    `date` DATE NOT NULL COMMENT "Date and time of data write",
    `city` VARCHAR(20) COMMENT "User's city",
    `age` SMALLINT COMMENT "User's age",
    `sex` TINYINT COMMENT "User's gender",
    `last_visit_date` DATETIME REPLACE DEFAULT "1970-01-01 00:00:00" COMMENT "User's last visit time",
    `cost` BIGINT SUM DEFAULT "0" COMMENT "User's total cost",
    `max_dwell_time` INT MAX DEFAULT "0" COMMENT "User's maximum dwell time",
    `min_dwell_time` INT MIN DEFAULT "99999" COMMENT "User's minimum dwell time"
)
AGGREGATE KEY(`user_id`, `date`, `city`, `age`, `sex`)
PARTITION BY RANGE(`date`) ()
DISTRIBUTED BY HASH(`user_id`)
PROPERTIES (
    "dynamic_partition.enable" = "true",
    "dynamic_partition.time_unit" = "DAY",
    "dynamic_partition.start" = "-7",
    "dynamic_partition.end" = "3",
    "dynamic_partition.prefix" = "p",
    "dynamic_partition.create_history_partition" = "true",
    "dynamic_partition.buckets" = "16"
);

Visão geral do particionamento de dados

O SelectDB utiliza particionamento de dados em duas camadas: partições (lógicas, menor unidade de gerenciamento) e tablets (físicas, menor unidade operacional para distribuição e movimentação).

Relação entre partições e tablets

Um tablet pertence a uma única partição. Uma partição pode conter vários tablets.
Com partições, os dados são primeiro divididos pelas regras de partição e depois subdivididos pelas regras de bucketing dentro de cada partição. Sem partições, as regras de bucketing aplicam-se diretamente a toda a tabela.
Durante a escrita, os dados entram primeiro na partição correspondente e depois são distribuídos para os tablets conforme as regras de bucketing. O bucketing subdivide os dados particionados para garantir distribuição uniforme e melhor eficiência nas consultas.

Partições (Partition)

No SelectDB, o particionamento divide os dados da tabela em partes independentes com base em regras definidas pelo usuário, melhorando a eficiência das consultas e simplificando o gerenciamento. Particionamento | Particionamento dinâmico.

Guia de seleção de particionamento

O SelectDB suporta particionamento Range e List, além de particionamento dinâmico para gerenciamento automatizado.

Método de particionamento

Tipos de coluna suportados

Método para especificar informações da partição

Cenários

Range

Tipos de coluna: DATE, DATETIME, TINYINT, SMALLINT, INT, BIGINT, LARGEINT

Suporta quatro sintaxes:

VALUES [...）: Define um intervalo fechado à esquerda e aberto à direita para a partição.
VALUES LESS THAN (...): Define apenas o limite superior da partição. O limite inferior é determinado pelo limite superior da partição anterior.
BATCH RANGE: Cria múltiplas partições Range para tipos numéricos e de tempo em lote. Define um intervalo fechado à esquerda e aberto à direita para as partições e estabelece um tamanho de passo.
MULTI RANGE: Cria múltiplas partições Range em lote. Define um intervalo fechado à esquerda e aberto à direita para as partições.

Ideal para gerenciar a divisão de dados por intervalos. Um cenário típico é o particionamento por tempo.

List

Tipos de coluna: BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, LARGEINT, DATE, DATETIME, CHAR, VARCHAR

Suporta o uso de VALUES IN (...) para especificar os valores enumerados que cada partição contém.

Indicado para gerenciamento de dados baseado em categorias existentes ou características fixas. A coluna de chave de partição geralmente é um valor enumerado, como particionar dados pela região do usuário.

Observações

As tabelas do SelectDB podem ser particionadas ou não. Essa definição ocorre na criação da tabela e não pode ser alterada. Tabelas particionadas permitem adicionar ou excluir partições posteriormente; tabelas não particionadas não permitem.
As colunas de chave de partição devem ser colunas de chave. É possível especificar uma ou mais colunas.
Independentemente do tipo de dado da coluna de chave de partição, coloque o valor da partição entre aspas duplas ("").
Teoricamente, não há limite superior para o número de partições.
Ao criar partições, garanta que os intervalos de valores não se sobreponham.

Uso de partições

Particionamento Range

O particionamento Range divide e gerencia dados com base no intervalo de um campo de partição. É o método mais comum, sendo frequentemente utilizado para particionar dados por tempo. Isso facilita o gerenciamento e otimiza consultas em grandes volumes de dados de séries temporais.

O objetivo final do particionamento e do bucketing é dividir os dados de forma adequada. Os principais critérios para uma regra de particionamento eficiente são:

O volume de dados de cada tablet deve ficar entre 1 GB e 10 GB.
Defina a granularidade da partição conforme suas necessidades de gerenciamento de dados. Por exemplo, em um cenário de logs onde é necessário excluir dados históricos diariamente, o particionamento por dia é apropriado.

Para dados de log com consultas por intervalo de tempo e retenção diária, particione por dia usando log_time:

CREATE TABLE IF NOT EXISTS log
(
 `log_time` DATETIME NOT NULL COMMENT "Log time",
 `log_type` INT NOT NULL COMMENT "Log type",
 `error_code` INT COMMENT "Error code",
 `error_msg` VARCHAR(1024) COMMENT "Error details",
 `op_id` BIGINT COMMENT "Owner ID",
 `op_time` DATETIME COMMENT "Processing time"
)
DUPLICATE KEY(`log_time`, `log_type`, `error_code`)
PARTITION BY RANGE(`log_time`)
(
 PARTITION `p20240201` VALUES [("2024-02-01"), ("2024-02-02")),
 PARTITION `p20240202` VALUES [("2024-02-02"), ("2024-02-03")),
 PARTITION `p20240203` VALUES [("2024-02-03"), ("2024-02-04"))
)
DISTRIBUTED BY HASH(`log_type`);

Visualize as informações da partição:

SHOW partitions FROM log;

p20240201: [("2024-02-01"), ("2024-02-02"))
p20240202: [("2024-02-02"), ("2024-02-03"))
p20240203: [("2024-02-03"), ("2024-02-04"))

Esta consulta acessa apenas a partição p20240202: [("2024-02-02"), ("2024-02-03")), ignorando as demais:

SELECT * FROM orders WHERE order_time = '2024-02-02';

Particionamento List

O particionamento List agrupa dados por valores enumerados. O sistema realiza pruning das partições não correspondentes durante as consultas para melhorar o desempenho.

Escolha colunas de partição usadas frequentemente nas consultas. Distribua os dados uniformemente entre as partições para evitar skew.

Em um cenário de e-commerce com grande volume de pedidos e consultas frequentes por cidade, particione por customer_city. Suponha que os dados estejam distribuídos por região:

Pequim, Xangai e Hong Kong (China): 6 GB
Nova York e São Francisco: 5 GB
Tóquio: 5 GB

Neste caso, você pode particionar os dados conforme mostrado no exemplo a seguir.

CREATE TABLE IF NOT EXISTS orders
(
    `order_id` LARGEINT NOT NULL COMMENT "Order ID",
    `order_time` DATETIME NOT NULL COMMENT "Order time",
    `customer_city` VARCHAR(20) COMMENT "User's city",
    `customer_id` LARGEINT NOT NULL COMMENT "User ID",
    `total_amount` DOUBLE COMMENT "Total order amount",
    `status` VARCHAR(20) COMMENT "Order status",
    `payment_method` VARCHAR(20) COMMENT "Payment method",
    `shipping_method` VARCHAR(20) COMMENT "Shipping method",
    `customer_address` VARCHAR(500) COMMENT "User's address"
)
UNIQUE KEY(`order_id`, `order_time`, `customer_city`)
PARTITION BY LIST(`customer_city`)
(
    PARTITION `p_cn` VALUES IN ("Beijing", "Shanghai", "Hong Kong"),
    PARTITION `p_usa` VALUES IN ("New York", "San Francisco"),
    PARTITION `p_jp` VALUES IN ("Tokyo")
)
DISTRIBUTED BY HASH(`order_id`) BUCKETS 16
PROPERTIES (
    "enable_unique_key_merge_on_write" = "true"
);

Visualize as informações da partição:

SHOW partitions FROM orders;

p_cn: ("Beijing", "Shanghai", "Hong Kong")
p_usa: ("New York", "San Francisco")
p_jp: ("Tokyo")

Esta consulta acessa apenas a partição p_jp: ("Tokyo"), ignorando as demais:

SELECT * FROM orders WHERE customer_city = 'Tokyo';

Uso de particionamento dinâmico

O gerenciamento manual de partições torna-se trabalhoso à medida que as tabelas crescem. O SelectDB suporta regras de particionamento dinâmico para automação desse gerenciamento.

Para uma tabela de pedidos de e-commerce onde você filtra por intervalo de tempo e arquiva pedidos antigos, especifique order_time como chave de partição e configure o particionamento dinâmico em PROPERTIES — por exemplo, partições diárias (dynamic_partition.time_unit), retenção de 180 dias (dynamic_partition.start) e previsão de 3 dias (dynamic_partition.end).

Importante

Na instrução abaixo, os parênteses () ao final de PARTITION BY RANGE( não são um erro de sintaxe. Se você desejar usar particionamento dinâmico, esses parênteses são obrigatórios pela sintaxe.

CREATE TABLE IF NOT EXISTS orders
(
    `order_id` LARGEINT NOT NULL COMMENT "Order ID",
    `order_time` DATETIME NOT NULL COMMENT "Order time",
    `customer_id` LARGEINT NOT NULL COMMENT "User ID",
    `total_amount` DOUBLE COMMENT "Total order amount",
    `status` VARCHAR(20) COMMENT "Order status",
    `payment_method` VARCHAR(20) COMMENT "Payment method",
    `shipping_method` VARCHAR(20) COMMENT "Shipping method",
    `customer_city` VARCHAR(20) COMMENT "User's city",
    `customer_address` VARCHAR(500) COMMENT "User's address"
)
UNIQUE KEY(`order_id`, `order_time`)
PARTITION BY RANGE(`order_time`) ()
DISTRIBUTED BY HASH(`order_id`)
PROPERTIES (
    "enable_unique_key_merge_on_write" = "true",
    "dynamic_partition.enable" = "true",
    "dynamic_partition.time_unit" = "DAY",
    "dynamic_partition.start" = "-180",
    "dynamic_partition.end" = "3",
    "dynamic_partition.prefix" = "p",
    "dynamic_partition.create_history_partition" = "true",
    "dynamic_partition.buckets" = "16"
);

Para tabelas com muitas partições, utilize Particionamento dinâmico para gerenciamento automatizado.

Bucketing (Tablet)

No SelectDB, os dados são divididos em tablets com base no valor hash de uma coluna especificada. Os tablets são distribuídos pelos nós do cluster para processamento paralelo. Configure o bucketing com DISTRIBUTED BY HASH(. Bucketing.

Observações

Com partições, a cláusula DISTRIBUTED... define a divisão de dados dentro de cada partição. Sem partições, ela se aplica a toda a tabela.
É possível especificar múltiplas colunas de bucketing.

Para os modelos Aggregate e Unique, as colunas de bucketing devem ser colunas de chave. Para o modelo Duplicate, não há restrição quanto às colunas de bucketing.

Escolha colunas de alta cardinalidade como colunas de bucketing para distribuir os dados uniformemente e evitar data skew.
Teoricamente, não há limite superior para o número de tablets.

Teoricamente, não há limite superior ou inferior para o volume de dados de um único tablet, mas recomenda-se um intervalo de 1 GB a 10 GB.

Se o volume de dados de um único tablet for muito pequeno, isso pode gerar um excesso de tablets, aumentando a pressão sobre o gerenciamento de metadados.

Se o volume de dados de um único tablet for muito grande, isso dificulta a migração de réplicas e a utilização total do cluster distribuído. Também aumenta o custo de novas tentativas em operações falhas, como alterações de esquema ou criação de índices, pois a granularidade dessas tentativas está no nível do tablet.

Guia de seleção de coluna de bucketing

A escolha da coluna de bucketing impacta o desempenho e a concorrência das consultas. Se houver conflito de requisitos, priorize seu padrão principal de consulta.

Princípio de seleção	Efeito
Priorize a distribuição uniforme de dados escolhendo colunas de alta cardinalidade ou uma combinação de colunas.	Distribuição equilibrada entre os nós. Para varreduras amplas, isso utiliza totalmente os recursos distribuídos.
Escolha colunas frequentemente usadas em condições de filtro de consulta para acelerar as consultas através de data pruning.	Linhas com os mesmos valores na coluna de bucketing são agrupadas. Consultas pontuais que usam a coluna de bucketing realizam pruning rapidamente, melhorando a concorrência. Nota Consultas pontuais recuperam uma pequena quantidade de dados usando condições específicas, como filtros de chave primária ou colunas de alta cardinalidade.

Exemplo

Em um cenário de e-commerce, a maioria das consultas filtra por pedido, enquanto algumas realizam análises em toda a tabela. Escolha a coluna de alta cardinalidade order_id como coluna de bucketing para garantir distribuição uniforme e agrupar dados por pedido:

CREATE TABLE IF NOT EXISTS orders
(
    `order_id` LARGEINT NOT NULL COMMENT "Order ID",
    `order_time` DATETIME NOT NULL COMMENT "Order time",
    `customer_id` LARGEINT NOT NULL COMMENT "User ID",
    `total_amount` DOUBLE COMMENT "Total order amount",
    `status` VARCHAR(20) COMMENT "Order status",
    `payment_method` VARCHAR(20) COMMENT "Payment method",
    `shipping_method` VARCHAR(20) COMMENT "Shipping method",
    `customer_city` VARCHAR(20) COMMENT "User's city",
    `customer_address` VARCHAR(500) COMMENT "User's address"
)
UNIQUE KEY(`order_id`, `order_time`)
PARTITION BY RANGE(`order_time`) ()
DISTRIBUTED BY HASH(`order_id`)
PROPERTIES (
    "enable_unique_key_merge_on_write" = "true",
    "dynamic_partition.enable" = "true",
    "dynamic_partition.time_unit" = "DAY",
    "dynamic_partition.start" = "-7",
    "dynamic_partition.end" = "3",
    "dynamic_partition.prefix" = "p",
    "dynamic_partition.create_history_partition" = "true",
    "dynamic_partition.buckets" = "16"
);

Índices

Índices adequados melhoram o desempenho das consultas, mas consomem armazenamento adicional e reduzem o throughput de escrita. Aceleração de índice.

Guia de design

Especifique as colunas de filtro mais frequentes como colunas de chave para o índice de prefixo automático. Uma tabela pode ter apenas um índice de prefixo, portanto, aplique-o ao padrão de filtro mais comum.
Para outras necessidades de filtragem, use índices invertidos. Eles suportam múltiplas combinações de condições. Para igualdade de strings e consultas LIKE, considere índices BloomFilter ou NGram BloomFilter.

Guia de seleção de índice

No SelectDB, os índices podem ser integrados (criados automaticamente) ou personalizados (criados manualmente na criação da tabela ou posteriormente).

Método de criação	Tipo de índice	Tipos de consulta suportados	Tipos de consulta não suportados	Vantagens	Desvantagens
Integrado	Índice de prefixo	Consultas de igualdade e desigualdade Consultas de intervalo	Consultas LIKE MATCH (palavra-chave, frase)	Baixo uso de espaço, totalmente armazenável em cache na memória. Localiza blocos de dados rapidamente.	Uma tabela pode ter apenas um índice de prefixo.
Personalizado	Índice invertido (recomendado)	Consultas de igualdade, desigualdade e intervalo para tipos string, numérico e data/hora MATCH para strings (palavra-chave, frase) Busca full-text para tipos de texto	Nenhum	Amplo suporte a tipos de consulta. Crie sob demanda na criação da tabela ou posteriormente.	Maior sobrecarga de armazenamento.
	Índice BloomFilter	Consulta de igualdade	Consulta de diferença (not-equal-to) Consultas de intervalo Consultas LIKE MATCH (palavra-chave, frase)	A criação do índice consome poucos recursos de computação e armazenamento.	Suporta poucos tipos de consulta. Apenas consultas de igualdade.
	Índice NGram BloomFilter	Consultas LIKE	Consultas de igualdade e desigualdade Consultas de intervalo MATCH (palavra-chave, frase)	Melhora a velocidade de consultas LIKE. A criação do índice consome poucos recursos de computação e armazenamento.	Acelera apenas consultas LIKE.

Início rápido com índices

Índice invertido

Os índices invertidos do SelectDB suportam busca full-text em campos de texto e consultas de igualdade ou intervalo em outros campos. Índices invertidos.

Criar um índice ao criar uma tabela

Para acelerar consultas por ID de usuário e palavras-chave de endereço, crie índices invertidos em customer_id e customer_address:

CREATE TABLE IF NOT EXISTS orders
(
    `order_id` LARGEINT NOT NULL COMMENT "Order ID",
    `order_time` DATETIME NOT NULL COMMENT "Order time",
    `customer_id` LARGEINT NOT NULL COMMENT "User ID",
    `total_amount` DOUBLE COMMENT "Total order amount",
    `status` VARCHAR(20) COMMENT "Order status",
    `payment_method` VARCHAR(20) COMMENT "Payment method",
    `shipping_method` VARCHAR(20) COMMENT "Shipping method",
    `customer_city` VARCHAR(20) COMMENT "User's city",
    `customer_address` VARCHAR(500) COMMENT "User's address",
    INDEX idx_customer_id (`customer_id`) USING INVERTED,
    INDEX idx_customer_address (`customer_address`) USING INVERTED PROPERTIES("parser" = "chinese")
)
UNIQUE KEY(`order_id`, `order_time`)
PARTITION BY RANGE(`order_time`) ()
DISTRIBUTED BY HASH(`order_id`)
PROPERTIES (
    "enable_unique_key_merge_on_write" = "true",
    "dynamic_partition.enable" = "true",
    "dynamic_partition.time_unit" = "DAY",
    "dynamic_partition.start" = "-7",
    "dynamic_partition.end" = "3",
    "dynamic_partition.prefix" = "p",
    "dynamic_partition.create_history_partition" = "true",
    "dynamic_partition.buckets" = "16"
);

Criar um índice em uma tabela existente

Para adicionar um índice invertido em customer_id a uma tabela existente:

ALTER TABLE orders ADD INDEX idx_customer_id (`customer_id`) USING INVERTED;

Índice de prefixo

Um índice de prefixo é construído sobre uma ou mais colunas de chave de prefixo e depende da ordenação subjacente dos dados por essas colunas de chave. Trata-se essencialmente de uma busca binária baseada na natureza ordenada dos dados. Índices de prefixo são índices integrados que o SelectDB cria automaticamente após a criação da tabela.

Nenhuma sintaxe especial define um índice de prefixo. O sistema seleciona campos cobertos pelos primeiros 36 bytes das colunas de chave. Uma coluna VARCHAR trunca o índice de prefixo; colunas de chave subsequentes são excluídas.

A ordem das colunas de chave determina o índice de prefixo. Ordene as colunas de chave seguindo estes princípios:

Posicione colunas de chave de alta cardinalidade, frequentemente usadas para filtragem, antes de outros campos. Por exemplo, em no cenário de log na seção do modelo Duplicate, o tempo de log log_time é posicionado antes do código de erro error_code.
Posicione colunas de chave para filtros de igualdade antes de colunas de chave para filtros de intervalo. Por exemplo, em no cenário de e-commerce na seção de Índice invertido, o tempo order_time geralmente é filtrado por intervalo e é posicionado após o ID do pedido order_id.
Posicione campos de tipos regulares antes de campos do tipo VARCHAR. Por exemplo, coloque colunas de chave do tipo INT antes de colunas de chave do tipo VARCHAR.

Exemplo

Em no cenário de e-commerce na seção de Índice invertido, o índice de prefixo para a tabela de informações de pedidos é order_id+order_time. Quando a condição de consulta é um prefixo do índice de prefixo (ou seja, a condição inclui order_id, ou ambos order_id e order_time), a velocidade da consulta aumenta significativamente. Conforme mostrado nos dois exemplos a seguir, a consulta no Exemplo 1 é muito mais rápida que a consulta no Exemplo 2.

Exemplo 1

SELECT * FROM orders WHERE order_id = 1829239 and order_time = '2024-02-01';

Exemplo 2

SELECT * FROM orders WHERE order_time = '2024-02-01';

Próximas etapas

Com esses fundamentos, você pode projetar tabelas do SelectDB para sua carga de trabalho. Explore migração de dados, consultas em fontes externas e atualizações de versão em O que fazer a seguir.