Introduction
Des volumes élevés de clics et de conversions sollicitent rapidement les bases de données relationnelles : des tableaux qui grossissent, des requêtes plus longues et un calcul des commissions retardé. Pour les exploitants d'Affilentra qui préfèrent le contrôle auto‑hébergé et des licences permanentes, des stratégies MySQL évolutives sont indispensables. Cet article décrit des modèles pragmatiques — partitionnement, sharding temporel, tables chaudes, pipelines d'archivage et sharding par marchand — en se concentrant sur l'intégrité des données et un calcul des commissions traçable.
Principes de conception avant mise en œuvre
- Séparer les rôles : l'opérateur logiciel, les marchands et les partenaires conservent des responsabilités claires ; l'accès aux données et la souveraineté des données du marchand doivent être reflétés.
- Préserver l'intégrité du tracking : les événements de suivi doivent rester idempotents et intangibles ; les archives ne doivent pas influencer les processus de facturation.
- Chaud vs. froid : maintenir les données récentes performantes et archiver les anciennes de manière peu consommatrice d'espace.
- Requêtes uniformes : les requêtes pour le calcul des commissions doivent être déterministes et reproductibles — même après archivage.
Partitionnement : basé sur le temps et par marchand
Le partitionnement réduit les E/S et améliore le partition pruning lorsque les requêtes utilisent la clé de partition. Deux variantes éprouvées :
Partitionnement temporel (RANGE)
Bon choix pour les logs de clics et de conversions : partitions par mois ou par semaine. Avantage : archivage simple de segments temporels entiers.
Exemple pratique (conceptuel) :
CREATE TABLE clicks (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
merchant_id INT NOT NULL,
partner_id INT NULL,
click_uuid CHAR(36) NOT NULL,
created_at DATETIME NOT NULL,
payload JSON,
UNIQUE KEY ux_click_uuid (click_uuid)
)
PARTITION BY RANGE (TO_DAYS(created_at)) (
PARTITION p_2024_01 VALUES LESS THAN (TO_DAYS('2024-02-01')),
PARTITION p_2024_02 VALUES LESS THAN (TO_DAYS('2024-03-01'))
);
Important : les requêtes doivent contenir WHERE created_at BETWEEN ... ou WHERE created_at >= ... afin que MySQL utilise le partition pruning.
Partitionnement par marchand ou LIST
Pour quelques marchands très volumineux, le partitionnement LIST par merchant_id peut être judicieux, ou une combinaison (sous‑partitionnement). Avantage : E/S isolées et chaudes par marchand.
Sharding : distribution horizontale
Le partitionnement aide au sein d'une base de données. En cas de charges extrêmes, un sharding horizontal devient nécessaire : plusieurs instances MySQL, pilotées par une couche de routage dans Affilentra (ou un proxy).
- Choix de clé : merchant_id comme clé de shard permet l'isolation par commerçant ; en alternative Hash(partner_id) pour répartir la charge.
- Topologie : petits marchands dans un shard commun, marchands à fort volume dans leurs propres shards (base de données mono‑locataire ou schéma dédié).
- Métadonnées centralisées : conserver les profils partenaires, règles de programme et logs de facturation au centre, afin de garantir la cohérence des calculs.
Concept Affilentra : la séparation des rôles permet l'isolation par marchand sans modifier la logique métier — idéal pour une migration progressive vers le sharding.
Tables chaudes & architecture nearline
Règle pratique : seules les N derniers jours (par ex. 7–30 jours, selon l'activité) restent dans les tables chaudes ; les données plus anciennes sont déplacées vers un archive compact et optimisé pour les requêtes.
- Table chaude : même structure que la table en production, optimisée pour inserts rapides et lectures fréquentes.
- Table froide/Archive : compressée, éventuellement avec une autre engine de stockage ou sur du matériel à latence plus élevée.
- Échange de partition : Dans MySQL vous pouvez « échanger » des partitions (EXCHANGE PARTITION) ou les déplacer rapidement avec RENAME/ALTER — minimise les interruptions.
Pipelines d'archivage : déplacer de manière sûre et traçable
Flux d'archivage en étapes :
- Placer un marqueur : Marquez les clics/conversions encore pertinents pour la facturation (par ex. status = 'pending_settlement').
- Snapshot avant déplacement : Avant de déplacer les données, écrivez un snapshot de settlement dans une table séparée, afin que les calculs restent reproductibles.
- Déplacer par lots : Déplacez des partitions entières ou par lots avec des IDs cohérents ; utilisez des étapes atomiques (par ex. RENAME + INSERT INTO ... SELECT) dans la même BD si possible.
- Vérification & réconciliation : Comparez les compteurs (nombre de lignes, checksums) avant et après le déplacement ; les erreurs doivent disposer d'une procédure de rollback.
Outils : pt‑archiver (Percona) ou jobs ETL sur mesure. L'important est un comportement déterministe et la journalisation de chaque modification, pour que les factures soient vérifiables.
Règles d'intégrité pour le calcul des commissions
- Événements idempotents : Empêcher le double comptage des clics et des conversions en utilisant un click_uuid unique.
- Statut de settlement : Utilisez des champs de statut (pending, validated, paid). Archivez uniquement les enregistrements avec settled = true ou créez un instantané de facturation immuable.
- Transactions : Effectuez les validations et le calcul des commissions dans des transactions contrôlées ou des workflows distribués.
- Journal d'audit : Chaque paiement devrait référencer les Click‑IDs et les snapshots de conversion utilisés ; cela facilite la réconciliation après archivage.
Stratégies par marchand
Pour les gros marchands, l'isolation est recommandée :
- Schéma/BD propre : Hébergez les marchands à fort volume dans des bases de données séparées ; cela réduit les conflits de réplication et de sauvegarde.
- Partitionnement différencié : À l'intérieur du schéma du marchand, partitionnez davantage par temps ; ainsi, les archivages mensuels spécifiques au marchand sont possibles.
- Quota et SLA par marchand : Surveillez l'I/O, les verrous et les temps de requête par marchand ; les décisions de mise à l'échelle doivent être orientées coût et état.
Affilentra prend en charge des droits basés sur les rôles et rend l'isolation par marchand praticable : les marchands conservent le contrôle sur les produits et les supports publicitaires, tandis que l'opérateur garde le contrôle de l'infrastructure.
Exploitation & Monitoring
- Profilage des requêtes : Identifier les requêtes lentes et ajuster la stratégie d'index/partition.
- Hygiène des partitions : Ajout et suppression automatisés de partitions temporelles pour éviter un nombre excessive de partitions.
- Sauvegardes et tests de restauration : Vérifiez les scénarios de restauration pour les données chaudes et d'archive séparément.
- Métriques : Surveillez le taux d'insertion, la latence de lecture, les attentes de verrou, le taux de partition pruning et le débit d'archivage.
Résumé : plan de mise en œuvre étape par étape
- Analyse : identifier les requêtes principales et les points chauds (par merchant, partner, time).
- Partitionner : commencer par le temps, auditer les clauses WHERE pour que le pruning soit effectif.
- Stockage hot/gradé : table hot pour les données actuelles, archive pour les données froides ; utiliser Partition Exchange.
- Isolation par marchand : pour les marchands à fort volume, créer des bases de données ou shards séparés.
- Pipeline d'archivage & snapshots : créer des snapshots de settlement, déplacement en batch avec réconciliation.
- Monitoring & itération : surveiller les métriques et ajuster la stratégie.
Avantage pratique pour les opérateurs Affilentra
Ces schémas permettent aux exploitants de conserver la souveraineté des données dans un environnement self‑hosted et d'éviter la perte de suivi ou des calculs de commission erronés. L'Architektur Affilentra avec rôles séparés facilite l'isolation par marchand et autorise des migrations contrôlées vers des topologies partitionnées ou sharded — sans sacrifier la logique métier.
Agir maintenant : Pour une installation évolutive et des conseils sur les stratégies de partitionnement et de sharding, choisissez votre point d'entrée : Choisir l'entrée.
Prochaine étape
Essayer Affilentra soi‑même
Plateforme unifiée qui réunit de manière transparente la mise à disposition des produits, le tracking, la validation des conversions, le calcul des commissions et le paiement selon des rôles clairement séparés ; option d'installation self‑hosted avec licence perpétuelle sans participation au chiffre d'affaires ; les marchands conservent le contrôle sur les produits, les conditions et les supports publicitaires ; les partenaires reçoivent des liens de tracking personnels et un historique de paiement traçable.