AnalyticDB:Design de schema de tabela

Selecione um tipo de tabela

Selecione uma chave de distribuição

Para importar dados incrementais, especifique uma chave de distribuição e uma chave de partição ao criar uma tabela padrão. Isso permite a sincronização incremental de dados. Durante a criação da tabela, use a cláusula DISTRIBUTED BY HASH(column_name,...) para definir a chave de distribuição. A tabela será fragmentada com base nos valores de hash do campo column_name. Para mais informações, consulte CREATE TABLE.

• Sintaxe

  DISTRIBUTED BY HASH(column_name,...)

• Notas de uso

  • Escolha campos com valores uniformemente distribuídos como chave de distribuição, como IDs de transação, IDs de dispositivo, IDs de usuário ou colunas de incremento automático.

    Nota

    Não selecione campos dos tipos DATE, TIME ou TIMESTAMP como chave de distribuição. Esses campos podem causar distorção de dados (data skew) durante gravações e degradar o desempenho de escrita. Como a maioria das consultas se restringe a um intervalo de tempo específico, como o último dia ou mês, os dados consultados podem residir em apenas um único nó. Isso impede o aproveitamento da capacidade de processamento de todos os nós do banco de dados distribuído. Recomendamos usar campos do tipo DATE ou TIME como chaves de subpartição. Para mais informações, consulte Selecione uma chave de partição.

  • Para minimizar a movimentação de dados (data shuffles), prefira campos usados em junções de tabelas como chave de distribuição. Por exemplo, para consultar pedidos históricos por cliente, selecione o campo customer_id como chave de distribuição.

  • Prefira campos que aparecem frequentemente nas condições de consulta como chave de distribuição. Isso possibilita a poda de partições (partition pruning) com base na chave de distribuição.

  • Cada tabela admite apenas uma chave de distribuição, que pode conter um ou mais campos. Selecione o menor número possível de campos para tornar a chave de distribuição mais versátil para diversas consultas complexas.

  • Se você não especificar uma chave de distribuição durante a criação da tabela, o sistema adotará o seguinte comportamento:

    • Se a tabela possuir uma chave primária, o AnalyticDB for MySQL a usará como chave de distribuição padrão.

    • Se a tabela não tiver uma chave primária, o AnalyticDB for MySQL adicionará um campo __adb_auto_id__ e o usará como chave primária e chave de distribuição.

Selecione uma chave de partição

Caso um único shard contenha um grande volume de dados após a definição da chave de distribuição, subdivida esse shard usando uma chave de partição. Além disso, incluir condições de filtro para campos de subpartição na cláusula WHERE das instruções de consulta aciona a poda de partições. Isso reduz significativamente a quantidade de dados a serem verificados e melhora o desempenho de acesso. Ao criar uma tabela, use a cláusula PARTITION BY para definir as subpartições. Os dados serão divididos conforme especificado. Para mais informações, consulte CREATE TABLE.

• Sintaxe

  • Particione a tabela usando o valor do campo column_name. A sintaxe é a seguinte:

    PARTITION BY VALUE(column_name)

  • Particione a tabela usando o valor do campo column_name convertido para o formato de data %Y%m%d, como 20210101. A sintaxe é a seguinte:

    PARTITION BY VALUE{(DATE_FORMAT(column_name, '%Y%m%d'))|(FROM_UNIXTIME(column_name, '%Y%m%d'))}

  • Particione a tabela usando o valor do campo column_name convertido para o formato de data %Y%m, como 202101. A sintaxe é a seguinte:

    PARTITION BY VALUE{(DATE_FORMAT(column_name, '%Y%m'))|(FROM_UNIXTIME(column_name, '%Y%m'))}

  • Particione a tabela usando o valor do campo column_name convertido para o formato de data %Y, como 2021. A sintaxe é a seguinte:

    PARTITION BY VALUE{(DATE_FORMAT(column_name, '%Y'))|(FROM_UNIXTIME(column_name, '%Y'))}

• Notas de uso

  • Em tabelas com grande volume de dados, a escolha das subpartições é fundamental. A ausência de subpartições ou uma divisão inadequada pode comprometer gravemente o desempenho do cluster AnalyticDB for MySQL. Para saber como diagnosticar a adequação dos campos de partição, consulte Diagnóstico de adequação de campos de distribuição.

  • Atualmente, o particionamento é suportado apenas por ano, mês, dia ou valor original. Uma granularidade de partição excessivamente grande ou pequena prejudica o desempenho de consultas e gravações, podendo inclusive afetar a estabilidade do cluster AnalyticDB for MySQL.

  • Mantenha as subpartições em estado estático sempre que possível. Não recomendamos atualizações frequentes nas subpartições. Caso seu cenário envolva atualizações diárias recorrentes em múltiplas subpartições históricas, avalie se o campo de subpartição escolhido é apropriado.

  • Use a palavra-chave LIFECYCLE N para gerenciar o ciclo de vida da tabela. As partições são ordenadas e aquelas que excedem N são filtradas.

    Importante

    Existe um limite máximo de partições suportadas por tabela. Portanto, os dados em uma tabela particionada não podem ser retidos permanentemente. Para mais informações sobre os limites de partição, consulte Limites.

    Se ocorrer um erro indicando que o número de partições ultrapassou o limite superior e esse limite não puder ser ajustado via configuração, aumente a granularidade da subpartição (por exemplo, altere de particionamento diário para mensal) ou otimize o design da chave de partição para reduzir o número total de partições.

Selecione uma chave primária

A chave primária funciona como identificador exclusivo de cada registro. Ao criar uma tabela, use a cláusula PRIMARY KEY para defini-la. Para mais informações, consulte CREATE TABLE.

• Sintaxe

  PRIMARY KEY (column_name,...)

• Notas de uso

  • Somente tabelas com chave primária suportam operações de atualização de dados, como DELETE e UPDATE.

  • A chave primária de uma tabela do AnalyticDB for MySQL pode ser composta por um único campo ou pela combinação de vários campos. Para obter melhor desempenho, recomendamos o uso de campos numéricos como chave primária, mantendo o menor número possível de campos.

  • A chave primária deve obrigatoriamente conter a chave de distribuição e a chave de partição. Recomendamos posicioná-las no início de uma chave primária composta. A chave de distribuição determina como os dados são distribuídos entre os shards, enquanto a chave de partição divide os dados por faixa de valores dentro de um shard. Quando a chave primária contém ambas, o otimizador de consultas consegue usar o índice de chave primária para localizar dados no shard e na partição correspondentes, garantindo assim o desempenho da consulta.

Selecione uma chave de índice clusterizado

Exemplo

CREATE TABLE customer (
customer_id bigint NOT NULL COMMENT 'Customer ID',
customer_name varchar NOT NULL COMMENT 'Customer name',
phone_num bigint NOT NULL COMMENT 'Phone number',
city_name varchar NOT NULL COMMENT 'City',
sex int NOT NULL COMMENT 'Gender',
id_number varchar NOT NULL COMMENT 'ID card number',
home_address varchar NOT NULL COMMENT 'Home address',
office_address varchar NOT NULL COMMENT 'Office address',
age int NOT NULL COMMENT 'Age',
login_time timestamp NOT NULL COMMENT 'Logon time',
PRIMARY KEY (login_time, customer_id, phone_num)
 )
DISTRIBUTED BY HASH(customer_id)
PARTITION BY VALUE(DATE_FORMAT(login_time, '%Y%m%d')) LIFECYCLE 30
COMMENT 'Customer information table';

Perguntas frequentes

• P: Após criar subpartições, como visualizo todas as subpartições de uma tabela e suas estatísticas?

  R: Execute a seguinte instrução SQL para visualizar todas as subpartições da tabela e suas respectivas estatísticas:

  SELECT partition_id, -- Partition name
            row_count, -- Total number of rows in the partition
            local_data_size, -- Size of the local storage occupied by the partition
            index_size, -- Index size of the partition
            pk_size, -- Size of the primary key index of the partition
            remote_data_size -- Size of the remote storage occupied by the partition
  FROM information_schema.kepler_partitions
  WHERE schema_name = '$DB'
   AND table_name ='$TABLE' 
   AND partition_id > 0;

  Importante

  Partições em dados incrementais que ainda não passaram por compactação não são exibidas. Para obter uma lista em tempo real de todas as subpartições, execute a instrução select distinct $partition_column from $db.$table;.

• P: Quais fatores influenciam o número de shards? É possível alterar esse número manualmente?

  R: O número de shards é calculado automaticamente com base nas especificações iniciais do cluster no momento da criação. Não é possível alterar o número de shards.

• P: A alteração das especificações do cluster afeta o número de shards?

  R: Upgrades ou downgrades do cluster não alteram o número de shards.

• P: O AnalyticDB for MySQL permite alterar a chave de distribuição ou a chave de partição?

  R: Não. Para alterar a chave de distribuição ou a chave de partição, consulte ALTER TABLE.

• P: Quais requisitos de consistência as tabelas do mesmo grupo de tabelas devem atender?

  R: No AnalyticDB for MySQL, todas as tabelas pertencentes ao mesmo grupo devem ter o mesmo número de partições hash primárias, partições de lista secundárias e réplicas. Caso contrário, não será possível adicioná-las ao mesmo grupo de tabelas.