ApsaraDB for SelectDB:Étape 3 : Maîtriser les points clés de la conception des bases de données et des tables

Utiliser le modèle Unique

Dans le modèle Unique, si plusieurs lignes partagent la même valeur pour une colonne clé, la dernière ligne écrite remplace les précédentes. Ce modèle propose deux méthodes d'implémentation : Merge on Read (MoR) et Merge on Write (MoW).

Mature et stable, la méthode MoW offre d'excellentes performances de requête. Nous recommandons donc son utilisation avec le modèle Unique. L'exemple suivant illustre la mise en œuvre du modèle Unique via la méthode MoW. Pour plus d'informations sur la méthode MoR, reportez-vous à la section MoR de la rubrique « Modèles de données ».

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"
);

Utiliser le modèle Duplicate

Avec le modèle Duplicate, le système conserve simultanément plusieurs lignes partageant la même valeur pour une colonne clé. Ce modèle ne prend pas en charge la pré-agrégation et n'impose pas de clés primaires uniques.

Par exemple, ce modèle permet d'enregistrer et d'analyser les journaux générés par un système métier, en triant les données par heure, type de journal et code d'erreur. Le code suivant montre l'instruction SQL de création de la table log. Ici, le modèle Duplicate est utilisé et les données sont triées selon les champs log_time, log_type et 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"
);

Utiliser le modèle Aggregate

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"
);

Utiliser le partitionnement par plage

Le partitionnement par plage est la méthode la plus courante pour organiser les données selon des intervalles de champs. Un cas typique consiste à partitionner de grands volumes de séries temporelles par date afin d'en faciliter la gestion et d'optimiser les requêtes.

L'objectif ultime du partitionnement et du découpage en tablets est d'organiser les données de manière rationnelle. Respectez les principes suivants lors de la définition des règles de partitionnement :

• Chaque tablet doit contenir entre 1 Go et 10 Go de données.

• Adaptez la granularité du partitionnement au volume de données à gérer. Par exemple, si vous devez supprimer quotidiennement des journaux historiques, une granularité journalière est appropriée.

L'exemple de code suivant montre comment créer une table partitionnée permettant de filtrer les données par période et de purger les anciens journaux. Ici, le champ log_time sert de clé de partition.

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 ();

Une fois la table créée, exécutez l'instruction SQL suivante pour afficher ses informations de partitionnement :

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"))

Lors de l'exécution de la requête ci-dessous, seule la partition p20240202: [("2024-02-02"), ("2024-02-03")) est sollicitée. Le système ignore les deux autres partitions, ce qui accélère considérablement la récupération des données.

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

Utiliser le partitionnement par liste

Le partitionnement par liste organise les données selon les valeurs d'énumération des colonnes clés de partition. Lors de l'interrogation d'une table ainsi partitionnée, le système peut exclure les partitions non pertinentes grâce aux filtres, améliorant ainsi les performances.

Choisissez les colonnes clés de partition parmi les champs couramment utilisés pour gérer vos données métier. Veillez à répartir uniformément les données entre les partitions pour éviter tout déséquilibre significatif.

Prenons l'exemple d'un site e-commerce traitant un grand volume de commandes. Si vous souhaitez analyser ces commandes par ville de résidence des utilisateurs, spécifiez le champ customer_city comme clé de partition pour simplifier la gestion et les requêtes. Supposons que les données soient réparties géographiquement comme suit :

• Pékin, Shanghai et Hong Kong, Chine : 6 Go

• New York et San Francisco : 5 Go

• Tokyo : 5 Go

Le code suivant illustre la création d'une table partitionnée par liste pour ces données de commande :

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"
);

Après la création de la table, exécutez l'instruction SQL suivante pour vérifier les partitions générées automatiquement (trois au total) :

SHOW partitions FROM orders;

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

Lors de l'exécution de la requête ci-dessous, seule la partition p_jp: ("Tokyo") est ciblée. Les deux autres partitions sont ignorées, ce qui optimise la vitesse d'interrogation.

SELECT * FROM orders WHERE customer_city = 'Tokyo';

Utiliser un index inversé

ApsaraDB for SelectDB prend en charge les index inversés. Ceux-ci permettent d'effectuer des recherches plein texte sur des données de type TEXT, ainsi que des requêtes d'égalité ou par plage sur des champs classiques, facilitant ainsi la récupération rapide d'informations spécifiques au sein de grands volumes de données. Cette section explique comment créer un index inversé. Pour plus de détails, consultez Index inversé.

Créer un index inversé lors de la création de la table

Dans un scénario e-commerce, les recherches de commandes se font souvent par mots-clés tels que l'identifiant utilisateur ou l'adresse. Pour accélérer ces requêtes, créez un index inversé sur les champs customer_id et customer_address. L'exemple suivant illustre la création d'une table intégrant ces index :

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"
);

Ajouter un index inversé à une colonne d'une table existante

Toujours dans un contexte e-commerce, supposons que vous deviez rechercher fréquemment des commandes par identifiant utilisateur, mais que l'index inversé sur le champ customer_id ait été omis lors de la création de la table orders. Exécutez alors l'instruction suivante pour ajouter cet index :

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

Utiliser un index de préfixe

Un index de préfixe repose sur une ou plusieurs colonnes clés des données sous-jacentes, lesquelles sont triées selon ces colonnes. Il fonctionne essentiellement comme une recherche binaire tirant parti de ce tri. S'agissant d'index intégrés, SelectDB les génère automatiquement après la création de la table.

Aucune syntaxe spécifique ne permet de définir explicitement un index de préfixe. Le système sélectionne automatiquement les premiers champs de colonne clé de la table pour constituer cet index, dont la longueur totale ne peut excéder 36 octets. Tout champ de colonne clé situé après un champ de type VARCHAR est exclu de l'index de préfixe.

L'ordre des champs dans la table est donc déterminant, car il définit la composition de l'index de préfixe. Nous vous recommandons vivement de respecter les règles suivantes pour organiser vos colonnes clés :

• Placez en tête les colonnes clés à forte cardinalité fréquemment utilisées comme filtres. Par exemple, dans la section Utiliser le modèle Duplicate, le champ log_time précède le champ error_code.

• Priorisez les colonnes clés utilisées pour des filtres d'égalité avant celles servant aux filtres par plage. Ainsi, dans la section Utiliser un index inversé, le champ order_time (filtre par plage) apparaît après order_id.

• Positionnez les champs de type ordinaire avant ceux de type VARCHAR. Par exemple, placez les colonnes clés de type INT avant celles de type VARCHAR.

Exemples

Reprenons l'exemple de la section Utiliser un index inversé, où la table des commandes bénéficie de l'index de préfixe order_id+order_time. Si la condition de requête inclut order_id seul ou combiné avec order_time, les performances s'améliorent nettement. Ainsi, la requête de l'Exemple 1 sera plus rapide que celle de l'Exemple 2.

Exemple 1

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

Exemple 2

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