Um design de esquema de tabela bem elaborado permite o suporte a diversos recursos e melhora significativamente o desempenho, a manutenibilidade e a escalabilidade do sistema de banco de dados. Por isso, o design do esquema de bancos de dados e tabelas é fundamental. Este tópico descreve as propriedades de tabela que exigem atenção durante o design de esquemas no ApsaraDB for SelectDB. Essas informações ajudam você a projetar tabelas adequadamente para atender melhor aos seus requisitos de negócios.
Propriedades importantes da tabela
Ao armazenar dados de negócios no ApsaraDB for SelectDB, é essencial definir as propriedades principais da tabela com base nas necessidades do seu negócio. Isso permite criar um esquema de tabela de alto desempenho e fácil manutenção. A tabela a seguir descreve as propriedades importantes de tabela do ApsaraDB for SelectDB.
Propriedade da tabela | Obrigatória | Descrição | Referências |
Modelo de dados | Sim | Cada modelo de dados atende a diferentes cenários de negócios: o modelo Unique suporta restrições de unicidade na chave primária e é usado para atender a requisitos flexíveis e eficientes de atualização de dados. O modelo Duplicate utiliza o modo de escrita por anexação de dados e é adequado para análise de alto desempenho de dados detalhados. O modelo Aggregate suporta pré-agregação de dados e é indicado para cenários de agregação e estatísticas. | |
Tablet | Sim | Os tablets distribuem dados entre diferentes nós de um cluster, permitindo gerenciar e consultar grandes volumes de dados com os recursos de um sistema distribuído. | |
Partição | Não | O particionamento divide uma tabela original em várias sub-tabelas com base em campos especificados, como tempo e região. Essa prática facilita o gerenciamento e a consulta de dados, além de acelerar as consultas. | |
Índice | Não | Os índices permitem filtrar ou localizar dados rapidamente, o que melhora significativamente o desempenho das consultas. |
Modelos de dados
Selecione o modelo de dados apropriado com base nos requisitos funcionais e de desempenho dos seus cenários de análise. Cada modelo atende a diferentes necessidades de negócios. Esta seção apresenta uma visão geral dos modelos disponíveis para ajudar você a compreender e escolher a opção mais adequada. Para obter mais informações, consulte Modelos de dados.
Conceitos básicos
No ApsaraDB for SelectDB, os dados são organizados e gerenciados na forma de tabelas na camada lógica. Cada tabela consiste em linhas e colunas. Uma linha representa um registro de dados na tabela. Uma coluna descreve um campo dentro dessa linha.
As colunas dividem-se nos seguintes tipos:
Coluna chave: são as colunas modificadas pelas palavras-chave
UNIQUE KEY,AGGREGATE KEYeDUPLICATE KEYna instrução CREATE TABLE.Coluna de valor: todas as demais colunas são consideradas colunas de valor.
Selecione um modelo
O ApsaraDB for SelectDB oferece três tipos de modelos de dados para tabelas: Unique, Duplicate e Aggregate.
O modelo de dados é definido durante a criação da tabela e não pode ser alterado posteriormente.
Caso nenhum modelo seja especificado na criação, o sistema adota o modelo Duplicate por padrão e seleciona automaticamente as três primeiras colunas como colunas chave.
Nos modelos Unique, Duplicate e Aggregate, os dados são ordenados com base nas colunas chave.
Modelo de dados | Característica | Cenário | Limitação |
Unique | O valor de uma coluna chave em cada linha é único. Se múltiplas linhas possuírem o mesmo valor na coluna chave, a linha gravada posteriormente substitui a anterior. | Indicado para cenários que exigem chaves primárias únicas ou atualizações eficientes. Por exemplo, use o modelo Unique em análises de dados como pedidos de e-commerce e atributos de usuários. |
|
Duplicate | O valor de uma coluna chave pode se repetir em várias linhas. O sistema armazena simultaneamente múltiplas linhas com o mesmo valor na coluna chave. | Este modelo apresenta alta eficiência na escrita e consulta de dados, sendo ideal para cenários onde todos os registros originais devem ser preservados. Utilize-o para análise detalhada de dados, como logs e faturas. |
|
Aggregate | O valor de uma coluna chave em cada linha é único. Quando várias linhas compartilham o mesmo valor na coluna chave, as colunas de valor são pré-agregadas conforme o tipo de agregação definido na criação da tabela. | Semelhante ao modelo Cube de data warehouses tradicionais, o modelo Aggregate é recomendado para estatísticas agregadas que melhoram o desempenho via pré-agregação. Aplique este modelo em análises como tráfego de sites e relatórios personalizados. |
|
Utilizar um modelo
Usar o modelo Unique
No modelo Unique, se uma coluna chave tiver o mesmo valor em várias linhas, a linha gravada posteriormente substitui a anterior. Existem dois métodos de implementação: Merge on Read (MoR) e Merge on Write (MoW).
O método MoW é maduro, estável e oferece excelente desempenho de consulta. Portanto, recomendamos o uso do método MoW no modelo Unique. O exemplo abaixo demonstra como implementar o modelo Unique com MoW. Para detalhes sobre o método MoR, consulte a seção MoR no tópico "Modelos de dados".
Observações de uso
Ao optar pelo modelo Unique com o método MoW, observe os seguintes pontos durante a criação da tabela:
Utilize a palavra-chave
UNIQUE KEYpara definir um campo único como chave primária.-
Ative o MoW na seção PROPERTIES.
"enable_unique_key_merge_on_write" = "true"
Exemplo
O código a seguir mostra a instrução SQL para criar a tabela orders. Neste exemplo, a tabela utiliza o modelo Unique, com os campos order_id e order_time como chave primária composta, e o método MoW ativado.
CREATE TABLE IF NOT EXISTS orders
(
`order_id` LARGEINT NOT NULL COMMENT "The order ID.",
`order_time` DATETIME NOT NULL COMMENT "The order time.",
`customer_id` LARGEINT NOT NULL COMMENT "The user ID.",
`total_amount` DOUBLE COMMENT "The total amount of the order.",
`status` VARCHAR(20) COMMENT "The order status.",
`payment_method` VARCHAR(20) COMMENT "The payment method.",
`shipping_method` VARCHAR(20) COMMENT "The shipping method.",
`customer_city` VARCHAR(20) COMMENT "The city in which the user resides.",
`customer_address` VARCHAR(500) COMMENT "The address of the user."
)
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"
);Usar o modelo Duplicate
No modelo Duplicate, o sistema armazena simultaneamente várias linhas com o mesmo valor na coluna chave. Este modelo não suporta pré-agregação nem exige chaves primárias únicas.
Por exemplo, utilize este modelo para registrar e analisar logs gerados por sistemas de negócios, ordenando os dados por horário, tipo de log e código de erro. O código abaixo apresenta a instrução SQL para criar a tabela log. Aqui, a tabela usa o modelo Duplicate e os dados são ordenados pelos campos log_time, log_type e error_code.
CREATE TABLE IF NOT EXISTS log
(
`log_time` DATETIME NOT NULL COMMENT "The time when the log was generated.",
`log_type` INT NOT NULL COMMENT "The type of the log.",
`error_code` INT COMMENT "The error code.",
`error_msg` VARCHAR(1024) COMMENT "The error message.",
`op_id` BIGINT COMMENT "The owner ID.",
`op_time` DATETIME COMMENT "The time when the error was handled."
)
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"
);Usar o modelo Aggregate
Observações de uso
No modelo Aggregate, quando múltiplas linhas possuem o mesmo valor na coluna chave, as colunas de valor são pré-agregadas segundo o tipo definido na criação da tabela. Ao criar uma tabela com este modelo, atente-se aos seguintes itens:
Use a palavra-chave
AGGREGATE KEYpara especificar uma ou mais colunas chave. Linhas com valores idênticos nessas colunas serão agregadas.Defina um tipo de agregação para as colunas de valor. A tabela a seguir descreve os tipos disponíveis.
Exemplo
Suponha que você precise realizar análises estatísticas sobre o comportamento do usuário, registrando: última visita, consumo total, tempo máximo de permanência e tempo mínimo de permanência. O código abaixo ilustra a criação da tabela user_behavior. Neste caso, as colunas de valor são pré-agregadas sempre que as seguintes colunas chave apresentarem o mesmo valor em múltiplas linhas: user_id, date, city, age e sex. A agregação segue estas regras:
Última visita do usuário: utiliza o valor máximo do campo last_visit_date.
Consumo total: calcula a soma dos registros.
Tempo máximo de permanência: utiliza o valor máximo do campo max_dwell_time.
Tempo mínimo de permanência: utiliza o valor mínimo do campo min_dwell_time.
CREATE TABLE IF NOT EXISTS user_behavior
(
`user_id` LARGEINT NOT NULL COMMENT "The user ID.",
`date` DATE NOT NULL COMMENT "The date on which data is written to the table.",
`city` VARCHAR(20) COMMENT "The city in which the user resides.",
`age` SMALLINT COMMENT "The age of the user.",
`sex` TINYINT COMMENT "The gender of the user.",
`last_visit_date` DATETIME REPLACE DEFAULT "1970-01-01 00:00:00" COMMENT "The last time when the user paid a visit.",
`cost` BIGINT SUM DEFAULT "0" COMMENT "The amount of money that the user spends.",
`max_dwell_time` INT MAX DEFAULT "0" COMMENT "The maximum dwell time of the user.",
`min_dwell_time` INT MIN DEFAULT "99999" COMMENT "The minimum dwell time of the user."
)
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"
);Divisão de dados
O ApsaraDB for SelectDB suporta duas camadas de divisão de dados, conforme ilustrado na figura abaixo. Na primeira camada, a tabela é dividida logicamente em partições, que representam a menor unidade de gerenciamento de dados. Na segunda camada, ocorre a divisão física em tablets, que constituem a menor unidade para operações como distribuição e migração de dados.
Relação entre partições e tablets
Um tablet pertence a apenas uma partição, enquanto uma partição contém vários tablets.
Se o particionamento estiver ativado na criação da tabela, ela será dividida em partições conforme as regras definidas e, em seguida, em tablets. Caso contrário, a tabela é dividida diretamente em tablets.
Durante a escrita, os dados vão primeiro para uma partição e depois são distribuídos entre os tablets dessa partição. A criação de tablets subdivide os dados particionados para garantir uma distribuição mais uniforme e melhorar a eficiência das consultas.
Partições
No mecanismo de armazenamento do ApsaraDB for SelectDB, o particionamento organiza os dados dividindo-os em partes independentes com base em regras personalizadas. Essa divisão lógica aumenta a eficiência das consultas e torna o gerenciamento de dados mais flexível. Esta seção resume os conceitos de particionamento para auxiliar na escolha do modo ideal. Para mais detalhes, veja a seção Particionamento no tópico "Particionamento e bucketing" e o tópico Particionamento dinâmico.
Selecione um modo de particionamento
O ApsaraDB for SelectDB oferece dois modos: particionamento por intervalo (range) e por lista (list). Além disso, disponibiliza o recurso de particionamento dinâmico para automatizar o gerenciamento. Cada modo atende a cenários específicos.
Modo de particionamento | Tipo de dados da coluna suportado | Método para especificar informações da partição | Cenário |
Range | DATE, DATETIME, TINYINT, SMALLINT, INT, BIGINT e LARGEINT | Quatro métodos são suportados:
| Ideal para gerenciar faixas de dados. Um cenário típico é o particionamento baseado em tempo. |
List | BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, LARGEINT, DATE, DATETIME, CHAR e VARCHAR |
| Recomendado para gerenciamento baseado em categorias ou características fixas. As colunas chave geralmente possuem valores enumeráveis, como regiões geográficas de usuários. |
Observações de uso
No ApsaraDB for SelectDB, as tabelas classificam-se em particionadas e não particionadas. Decida se deseja ativar o particionamento durante a criação da tabela. Essa propriedade é opcional, mas imutável após a definição. Tabelas particionadas permitem criar ou excluir partições; tabelas não particionadas não oferecem essa possibilidade.
É possível especificar uma ou mais colunas como chaves de partição. Essas colunas devem ser obrigatoriamente colunas chave.
Sempre envolva os valores das chaves de partição entre aspas duplas ("), independentemente do tipo de dado.
Teoricamente, não há limite para o número de partições.
Ao criar partições, garanta que os intervalos não se sobreponham.
Usar particionamento
Usar particionamento por intervalo
O particionamento por intervalo (range) é o método mais comum para gerenciar dados baseados em faixas de valores. Tipicamente, utiliza-se esse método para particionar grandes volumes de séries temporais por tempo, facilitando a gestão e otimizando consultas.
O objetivo final do particionamento e da criação de tablets é dividir os dados racionalmente. Siga estes critérios ao definir regras de particionamento:
Mantenha o volume de dados de cada tablet entre 1 GB e 10 GB.
Defina a granularidade da partição conforme o volume de dados a ser gerenciado. Por exemplo, se a exclusão de logs históricos for diária, uma granularidade de dia é apropriada.
O exemplo abaixo demonstra a criação de uma tabela particionada onde os dados são filtrados por intervalo de tempo e logs antigos são removidos periodicamente. Aqui, o campo log_time atua como chave de partição.
CREATE TABLE IF NOT EXISTS log
(
`log_time` DATETIME NOT NULL COMMENT "The time when the log was generated.",
`log_type` INT NOT NULL COMMENT "The type of the log.",
`error_code` INT COMMENT "The error code.",
`error_msg` VARCHAR(1024) COMMENT "The error message.",
`op_id` BIGINT COMMENT "The owner ID.",
`op_time` DATETIME COMMENT "The time when the error was handled."
)
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`)
PROPERTIES ();Após criar a tabela, execute a seguinte instrução SQL para visualizar as informações de 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"))Ao executar a consulta abaixo, o sistema acessa apenas a partição p20240202: [("2024-02-02"), ("2024-02-03")), ignorando as outras duas. Isso acelera significativamente a recuperação dos dados.
SELECT * FROM orders WHERE order_time = '2024-02-02';Usar particionamento por lista
O particionamento por lista organiza os dados com base em valores enumeráveis das colunas chave. Durante consultas, o sistema elimina partições irrelevantes (pruning) conforme as condições de filtro, melhorando o desempenho.
Escolha colunas chave de partição baseadas em campos frequentemente usados na gestão do negócio. Distribua os dados uniformemente entre as partições para evitar skew (desequilíbrio).
Considere um cenário de e-commerce com grande volume de pedidos, onde análises frequentes ocorrem por cidade do usuário. Para simplificar a gestão e consulta, defina o campo customer_city como chave de partição. Suponha a seguinte distribuição de dados:
Pequim, Xangai e Hong Kong, China: 6 GB
Nova York e São Francisco: 5 GB
Tóquio: 5 GB
O código a seguir cria uma tabela particionada por lista para esses pedidos:
CREATE TABLE IF NOT EXISTS orders
(
`order_id` LARGEINT NOT NULL COMMENT "The order ID.",
`order_time` DATETIME NOT NULL COMMENT "The order time.",
`customer_city` VARCHAR(20) COMMENT "The city in which the user resides.",
`customer_id` LARGEINT NOT NULL COMMENT "The user ID.",
`total_amount` DOUBLE COMMENT "The total amount of the order.",
`status` VARCHAR(20) COMMENT "The order status.",
`payment_method` VARCHAR(20) COMMENT "The payment method.",
`shipping_method` VARCHAR(20) COMMENT "The shipping method.",
`customer_address` VARCHAR(500) COMMENT "The address of the user."
)
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"
);Depois de criada a tabela, execute o SQL abaixo para ver as partições geradas automaticamente:
SHOW partitions FROM orders;p_cn: ("Beijing", "Shanghai", "Hong Kong")
p_usa: ("New York", "San Francisco")
p_jp: ("Tokyo")Na consulta abaixo, apenas a partição p_jp: ("Tokyo") é acessada. O sistema ignora as demais, acelerando a resposta.
SELECT * FROM orders WHERE customer_city = 'Tokyo';Usar particionamento dinâmico
Em ambientes de produção, tabelas podem acumular muitas partições, tornando o gerenciamento manual trabalhoso e custoso. O ApsaraDB for SelectDB permite configurar regras de particionamento dinâmico na criação da tabela para automatizar esse processo.
Por exemplo, em e-commerces, consultas por faixa temporal e arquivamento de pedidos antigos são comuns. Defina o campo order_time como chave de partição e ative o particionamento dinâmico em PROPERTIES. O código abaixo cria uma tabela com particionamento dinâmico. Os parâmetros dynamic_partition.time_unit, dynamic_partition.start e dynamic_partition.end configuram partições diárias, retendo apenas os últimos 180 dias e criando antecipadamente as partições para os próximos três dias.
Os parênteses () ao final da instrução PARTITION BY RANGE('order_time') () não são um erro de sintaxe. Eles são obrigatórios para ativar o particionamento dinâmico.
CREATE TABLE IF NOT EXISTS orders
(
`order_id` LARGEINT NOT NULL COMMENT "The order ID.",
`order_time` DATETIME NOT NULL COMMENT "The order time.",
`customer_id` LARGEINT NOT NULL COMMENT "The user ID.",
`total_amount` DOUBLE COMMENT "The total amount of the order.",
`status` VARCHAR(20) COMMENT "The order status.",
`payment_method` VARCHAR(20) COMMENT "The payment method.",
`shipping_method` VARCHAR(20) COMMENT "The shipping method.",
`customer_city` VARCHAR(20) COMMENT "The city in which the user resides.",
`customer_address` VARCHAR(500) COMMENT "The address of the user."
)
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"
);Se sua tabela tende a ter muitas partições, recomendamos fortemente estudar o particionamento dinâmico. Consulte Particionamento dinâmico para mais detalhes.
Tablets
O mecanismo de armazenamento do ApsaraDB for SelectDB divide os dados em tablets usando o hash de uma coluna específica. Diferentes nós do cluster gerenciam esses tablets, aproveitando a capacidade distribuída para lidar com grandes volumes de dados. Ao criar a tabela, configure os tablets com a cláusula DISTRIBUTED BY HASH('<Tablet key column>') BUCKETS <Number of tablets>. Veja mais na seção Bucketing do tópico "Particionamento e bucketing".
Observações de uso
Com o particionamento ativado, a cláusula DISTRIBUTED... define a divisão dos dados dentro de cada partição. Sem particionamento, ela regula a divisão de todos os dados da tabela.
-
Várias colunas podem servir como chaves de tablet.
Nos modelos Aggregate e Unique, as chaves de tablet devem ser colunas chave. No modelo Duplicate, podem ser colunas chave ou de valor.
Prefira colunas com alta cardinalidade como chaves de tablet para distribuir os dados uniformemente e evitar skew.
-
Não há limite teórico para a quantidade de tablets.
Embora não haja limite teórico de armazenamento por tablet, recomenda-se manter entre 1 GB e 10 GB por unidade.
Tablets muito pequenos aumentam excessivamente a carga de gerenciamento de metadados.
Tablets muito grandes prejudicam a migração de réplicas e impedem o aproveitamento pleno do cluster distribuído. Além disso, elevam o custo de novas tentativas em operações falhas (como alterações de esquema ou criação de índices), que ocorrem por tablet.
Selecione uma coluna chave de tablet
A escolha das colunas chave de tablet impacta diretamente o desempenho e a concorrência das consultas. A tabela abaixo resume as regras de seleção. Se houver múltiplos padrões de consulta, priorize as colunas que atendem aos requisitos principais.
Regra | Benefício |
Priorize colunas de alta cardinalidade ou combinações de colunas para garantir distribuição uniforme | Distribui os dados equitativamente pelos nós do cluster. Maximiza o uso dos recursos distribuídos, melhorando o desempenho em consultas que varrem grandes volumes de dados com pouca filtragem. |
Escolha colunas frequentes em filtros para equilibrar pruning e aceleração de consultas | Agrupa dados com o mesmo valor nas colunas chave. Acelera o pruning e aumenta a concorrência em point queries que usam essas colunas como filtro. Nota Point queries recuperam pequenos conjuntos de dados sob condições específicas, como filtros por chave primária ou colunas de alta cardinalidade. |
Exemplo
Num cenário de e-commerce, a maioria das consultas foca em pedidos específicos, embora análises estatísticas globais também ocorram. Nesse caso, selecione a coluna de alta cardinalidade order_id (presente nas chaves da tabela) como chave de tablet. Isso assegura distribuição uniforme e agrupa dados relevantes para atender aos requisitos de desempenho. O código abaixo ilustra essa configuração:
CREATE TABLE IF NOT EXISTS orders
(
`order_id` LARGEINT NOT NULL COMMENT "The order ID.",
`order_time` DATETIME NOT NULL COMMENT "The order time.",
`customer_id` LARGEINT NOT NULL COMMENT "The user ID.",
`total_amount` DOUBLE COMMENT "The total amount of the order.",
`status` VARCHAR(20) COMMENT "The order status.",
`payment_method` VARCHAR(20) COMMENT "The payment method.",
`shipping_method` VARCHAR(20) COMMENT "The shipping method.",
`customer_city` VARCHAR(20) COMMENT "The city in which the user resides.",
`customer_address` VARCHAR(500) COMMENT "The address of the user."
)
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 são cruciais no design de bancos de dados e podem elevar drasticamente o desempenho das consultas. Contudo, consomem espaço extra e podem reduzir a performance de escrita. Esta seção aborda os índices mais comuns para orientar sua escolha. Consulte Aceleração baseada em índices para detalhes.
Regras para criação de índices
Geralmente, o sistema cria automaticamente um índice de prefixo baseado em uma chave específica, oferecendo filtragem ótima. Como cada tabela admite apenas um índice de prefixo, escolha a chave usada com maior frequência nos filtros.
Para outras necessidades de filtragem acelerada, prefira índices invertidos. Eles abrangem diversos casos e aceitam combinações de colunas. Use índices Bloom Filter leves e NGram Bloom Filter para correspondências exatas e LIKE em strings.
Selecione um índice
No ApsaraDB for SelectDB, as tabelas utilizam índices internos ou personalizados. Os internos são criados automaticamente. Já os personalizados podem ser adicionados durante ou após a criação da tabela, conforme a necessidade.
Método | Tipo de índice | Tipos de consulta suportados | Tipos de consulta não suportados | Vantagem | Desvantagem |
Interno | Índice de prefixo |
|
| Ocupa pouco espaço e cabe inteiramente na memória, permitindo localização rápida de blocos de dados e alta eficiência. | Cada tabela admite apenas um índice de prefixo. |
Personalizado | Índice invertido (recomendado) |
| N/A | Suporta variados tipos de consulta. Permite criar índices durante ou após a criação da tabela, além de excluí-los. | Consome bastante espaço de armazenamento. |
Índice Bloom Filter | Consulta equivalente |
| Baixo consumo de recursos computacionais e de armazenamento. | Restrito a consultas equivalentes. | |
Índice NGram Bloom Filter | Consulta LIKE |
| Acelera consultas LIKE com baixo custo computacional e de armazenamento. | Acelera exclusivamente consultas LIKE. |
Usar índices
Usar índice invertido
O ApsaraDB for SelectDB suporta índices invertidos. Eles permitem buscas full-text em dados TEXT e consultas equivalentes ou por intervalo em campos comuns, recuperando rapidamente informações específicas em grandes volumes. Veja abaixo como criar um índice invertido. Para mais detalhes, consulte Índice invertido.
Criar um índice invertido durante a criação da tabela
Em e-commerces, consultas frequentes por termos como ID e endereço do usuário são comuns. Crie índices invertidos nos campos customer_id e customer_address para acelerar essas buscas. O código abaixo exemplifica essa criação:
CREATE TABLE IF NOT EXISTS orders
(
`order_id` LARGEINT NOT NULL COMMENT "The order ID.",
`order_time` DATETIME NOT NULL COMMENT "The order time.",
`customer_id` LARGEINT NOT NULL COMMENT "The user ID.",
`total_amount` DOUBLE COMMENT "The total amount of the order.",
`status` VARCHAR(20) COMMENT "The order status.",
`payment_method` VARCHAR(20) COMMENT "The payment method.",
`shipping_method` VARCHAR(20) COMMENT "The shipping method.",
`customer_city` VARCHAR(20) COMMENT "The city in which the user resides.",
`customer_address` VARCHAR(500) COMMENT "The address of the user.",
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 invertido em uma coluna de tabela existente
Suponha que consultas por ID do usuário sejam frequentes, mas o índice invertido não foi criado no campo customer_id inicialmente. Execute a instrução abaixo para adicionar o índice:
ALTER TABLE orders ADD INDEX idx_customer_id (`customer_id`) USING INVERTED;Usar índice de prefixo
O índice de prefixo incide sobre uma ou mais colunas chave nos dados subjacentes, já ordenados por essas colunas. Funciona essencialmente como uma busca binária baseada na ordenação. Trata-se de um índice interno, criado automaticamente pelo SelectDB após a criação da tabela.
Não existe sintaxe dedicada para defini-lo. O sistema seleciona automaticamente os primeiros campos de coluna chave, limitando o tamanho total do índice a 36 bytes. Campos posteriores a um tipo VARCHAR não entram no índice de prefixo.
A ordem dos campos na tabela é crucial, pois determina a composição do índice. Recomendamos fortemente seguir estas regras para ordenar as colunas chave:
Posicione primeiro as colunas chave de alta cardinalidade usadas frequentemente em filtros. Por exemplo, na seção Usar o modelo Duplicate, o campo
log_timeantecedeerror_code.Coloque colunas usadas em filtros de equivalência antes daquelas usadas em filtros de intervalo. Na seção Usar índice invertido, o campo order_time (intervalo) vem após order_id.
Priorize campos de tipos ordinários antes de tipos VARCHAR. Por exemplo, coloque colunas INT antes de colunas VARCHAR.
Exemplos
Na seção Usar índice invertido, a tabela de pedidos usa o índice de prefixo order_id+order_time. Consultas contendo order_id (ou ambos os campos) tornam-se muito mais rápidas. O Exemplo 1 é mais veloz que o 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óximos passos
Após concluir as três primeiras etapas deste tutorial, você possui uma compreensão básica do ApsaraDB for SelectDB e consegue projetar tabelas alinhadas aos seus negócios. Agora, explore operações avançadas como migração de dados, consultas em fontes externas e atualização de kernel. Consulte Próximos passos para continuar.