AnalyticDB:Conception du schéma de table

Sélectionner un type de table

Sélectionner une clé de distribution

Pour importer des données incrémentielles, spécifiez une clé de distribution et une clé de partition lors de la création d'une table standard. Cela permet la synchronisation des données incrémentielles. Utilisez la clause DISTRIBUTED BY HASH(column_name,...) lors de la création de la table pour définir la clé de distribution. La table est alors fragmentée selon les valeurs de hachage du champ column_name. Pour plus d'informations, consultez CREATE TABLE.

• Syntaxe

  DISTRIBUTED BY HASH(column_name,...)

• Notes d'utilisation

  • Choisissez comme clé de distribution des champs dont les valeurs sont uniformément réparties, tels que les ID de transaction, les ID d'appareil, les ID utilisateur ou les colonnes à incrémentation automatique.

    Remarque

    Évitez les champs de type DATE, TIME ou TIMESTAMP comme clé de distribution. Ils peuvent provoquer une distorsion des données (data skew) lors des écritures et dégrader les performances. La plupart des requêtes se limitent à une plage temporelle, comme le dernier jour ou le dernier mois. Dans ce cas, les données interrogées peuvent résider sur un seul nœud, empêchant l'utilisation des capacités de traitement de tous les nœuds de la base de données distribuée. Nous vous recommandons d'utiliser les champs de type DATE ou TIME comme clés de sous-partition. Pour plus d'informations, consultez Sélectionner une clé de partition.

  • Pour réduire les brassages de données (shuffles), choisissez comme clé de distribution les champs utilisés pour les jointures. Par exemple, pour interroger les commandes historiques par client, sélectionnez le champ customer_id comme clé de distribution.

  • Choisissez comme clé de distribution les champs qui apparaissent fréquemment dans les conditions de requête. Cela permet l'élimination des partitions (partition pruning) basée sur la clé de distribution.

  • Chaque table ne possède qu'une seule clé de distribution, qui peut contenir un ou plusieurs champs. Sélectionnez le moins de champs possible pour rendre la clé de distribution plus polyvalente face à diverses requêtes complexes.

  • Si vous ne spécifiez pas de clé de distribution lors de la création d'une table, le système procède comme suit :

    • Si la table possède une clé primaire, AnalyticDB for MySQL utilise la clé primaire comme clé de distribution par défaut.

    • Si la table ne possède pas de clé primaire, AnalyticDB for MySQL ajoute un champ __adb_auto_id__ et l'utilise comme clé primaire et clé de distribution.

Sélectionner une clé de partition

Si un shard contient une grande quantité de données après la définition d'une clé de distribution, vous pouvez le sous-partitionner à l'aide d'une clé de partition. Vous pouvez également inclure des conditions de filtrage sur les champs de sous-partition dans la clause WHERE des requêtes pour déclencher l'élimination des partitions. Cela réduit considérablement la quantité de données à analyser et améliore les performances d'accès. Utilisez la clause PARTITION BY lors de la création de la table pour définir les sous-partitions. Les données sont ensuite divisées comme spécifié. Pour plus d'informations, consultez CREATE TABLE.

• Syntaxe

  • Partitionnez la table en utilisant la valeur du champ column_name. La syntaxe est la suivante :

    PARTITION BY VALUE(column_name)

  • Partitionnez la table en utilisant la valeur du champ column_name convertie au format de date %Y%m%d, par exemple 20210101. La syntaxe est la suivante :

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

  • Partitionnez la table en utilisant la valeur du champ column_name convertie au format de date %Y%m, par exemple 202101. La syntaxe est la suivante :

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

  • Partitionnez la table en utilisant la valeur du champ column_name convertie au format de date %Y, par exemple 2021. La syntaxe est la suivante :

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

• Notes d'utilisation

  • Le choix des sous-partitions est crucial lorsque la table contient un grand volume de données. L'absence de sous-partitions ou une division incorrecte peut gravement affecter les performances du cluster AnalyticDB for MySQL. Pour diagnostiquer la pertinence des champs de partition, consultez Distribution field reasonability diagnostics.

  • Le partitionnement est actuellement pris en charge uniquement par année, mois, jour ou valeur d'origine. Une granularité trop grande ou trop petite affecte les performances des requêtes et des écritures, et peut même compromettre la stabilité du cluster AnalyticDB for MySQL.

  • Maintenez les sous-partitions dans un état statique autant que possible. Évitez les mises à jour fréquentes des sous-partitions. Par exemple, si plusieurs sous-partitions historiques sont fréquemment mises à jour quotidiennement, vérifiez la pertinence du champ de sous-partition choisi.

  • Utilisez le mot-clé LIFECYCLE N pour gérer le cycle de vie de la table. Les partitions sont triées et celles qui dépassent N sont filtrées.

    Important

    Le nombre maximal de partitions par table est limité. Par conséquent, les données d'une table partitionnée ne peuvent pas être conservées indéfiniment. Pour plus d'informations sur les limites de partition, consultez Limits.

    Si une erreur indique que le nombre de partitions dépasse la limite supérieure et que cette limite ne peut pas être ajustée via la configuration, augmentez la granularité des sous-partitions (par exemple, passez d'un partitionnement quotidien à mensuel) ou optimisez la conception de la clé de partition pour réduire le nombre total de partitions.

Sélectionner une clé primaire

La clé primaire sert d'identifiant unique pour chaque enregistrement. Utilisez la clause PRIMARY KEY lors de la création d'une table pour définir une clé primaire. Pour plus d'informations, consultez CREATE TABLE.

• Syntaxe

  PRIMARY KEY (column_name,...)

• Notes d'utilisation

  • Seules les tables dotées d'une clé primaire prennent en charge les opérations de mise à jour des données, telles que DELETE et UPDATE.

  • La clé primaire d'une table AnalyticDB for MySQL peut être constituée d'un champ unique ou d'une combinaison de plusieurs champs. Pour de meilleures performances, utilisez des champs numériques comme clé primaire et limitez le nombre de champs.

  • La clé primaire doit contenir la clé de distribution et la clé de partition. Placez la clé de distribution et la clé de partition au début d'une clé primaire composite. La clé de distribution détermine la répartition des données sur les shards, tandis que la clé de partition divise les données par plage de valeurs au sein d'un shard. Lorsque la clé primaire contient les deux, l'optimiseur de requête peut utiliser l'index de clé primaire pour localiser les données dans le shard et la partition correspondants, garantissant ainsi les performances des requêtes.

Sélectionner une clé d'index clusterisé

Exemple

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

FAQ

• Q : Après avoir créé des sous-partitions, comment afficher toutes les sous-partitions d'une table et leurs statistiques ?

  R : Exécutez l'instruction SQL suivante pour afficher toutes les sous-partitions de la table et leurs statistiques :

  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;

  Important

  Les partitions des données incrémentielles pour lesquelles la compaction n'a pas été déclenchée ne sont pas affichées. Pour afficher une liste en temps réel de toutes les sous-partitions, exécutez l'instruction select distinct $partition_column from $db.$table;.

• Q : Quels facteurs influencent le nombre de shards ? Puis-je modifier moi-même le nombre de shards ?

  R : Le nombre de shards est automatiquement calculé en fonction des spécifications initiales du cluster lors de sa création. Vous ne pouvez pas modifier le nombre de shards.

• Q : La modification des spécifications du cluster affecte-t-elle le nombre de shards ?

  R : Les mises à niveau ou rétrogradations du cluster n'affectent pas le nombre de shards.

• Q : AnalyticDB for MySQL prend-il en charge la modification de la clé de distribution ou de la clé de partition ?

  R : Non. Pour modifier la clé de distribution ou la clé de partition, consultez ALTER TABLE.

• Q : Quelles exigences de cohérence les tables d'un même groupe de tables doivent-elles respecter ?

  R : Dans AnalyticDB for MySQL, toutes les tables d'un même groupe de tables doivent avoir le même nombre de partitions de hachage primaires, de partitions de liste secondaires et de réplicas. Sinon, les tables ne peuvent pas être ajoutées au même groupe de tables.