Cinq leçons pour paginer les énormes tables de commandes dans une base de données distribuée

La pagination est l'un des modèles de requêtes les plus courants dans les applications en ligne. Pour les petites tables, elle devient rarement un goulot d'étranglement. Mais sur les grandes tables de commandes dans une base de données distribuée, les requêtes de pagination font face à une série de défis : sélection d'index instable, coûteuses recherches de tables, et analyses de partitions gaspillées qui créent des requêtes lentes à longue queue.

Cette analyse s'appuie sur des cas de production réels pour partager cinq idées d'optimisation pour les requêtes paginées sur les grandes tables de commandes dans PolarDB-X, la base de données SQL distribuée d'Alibaba Cloud. Les stratégies sont construites autour d'un schéma de partitionnement à deux niveaux : partitionnement par clé KEY au premier niveau par ID utilisateur, et partitionnement par plage RANGE au second niveau par un champ temporel, une architecture courante dans les plateformes financières, de commerce électronique et SaaS.

Les optimisations couvrent la chaîne complète de l'élagage de partitions, de l'utilisation des index, de la réduction des recherches de tables jusqu'à l'assurance de stabilité. Avant d'entrer dans le vif du sujet, une note : les schémas de tables et les instructions SQL ici ont été anonymisés et ne représentent pas des données métier réelles, bien que les chiffres de performance proviennent de scénarios utilisateurs réels.

L'anatomie d'une requête de pagination lente

Une table de commandes typique dans PolarDB-X est large, 60+ colonnes, avec des index de couverture conçus pour réduire les recherches dans les tables. Le schéma de partitionnement garantit que toutes les données pour un utilisateur donné atterrissent dans la même partition de premier niveau (par ID utilisateur), puis sous-partitions par date (par une colonne de timestamp pt).

Considérons une requête qui récupère les commandes récemment modifiées pour un grand locataire :

SELECT * FROM t_order
WHERE uid = 12345678
  AND pt >= '2024-08-01 00:00:00.000'
  AND update_ts > '2024-11-13 14:20:00.000'
  AND update_ts < '2024-11-13 14:25:00.000'
  AND origin != 33
ORDER BY id DESC
LIMIT 0, 100;

La condition pt >= '2024-08-01' force la requête à analyser quatre sous-partitions : p202408, p202409, p202410, and p202411. Mais le filtre update_ts restreint les résultats aux commandes modifiées dans les dernières minutes, qui sont presque toujours des commandes récentes, ce qui signifie que les lignes réellement qualifiées atterrissent toutes dans la partition p202411. Les trois autres partitions sont analysées pour rien. Sur la partition d'un grand locataire, chaque analyse gaspillée lit essentiellement toutes les données de cette partition, devenant une requête lente à longue queue qui détermine la latence globale.

Insight 1 : élagage de partitions par prédicats de filtre

Lorsqu'une colonne de filtre est corrélée à la clé de partition, ici, les commandes récemment modifiées ont tendance à avoir des valeurs pt récentes, la base de données peut exploiter cette corrélation pour ignorer les sous-partitions inutiles. L'optimiseur de PolarDB-X peut élaguer dynamiquement les partitions en reconnaissant que la plage effective de pt impliquée par le filtre update_ts est beaucoup plus étroite que la plage littérale pt >= '2024-08-01'. Dans le scénario utilisateur réel, cette optimisation a fait passer une requête de 5 secondes à 0,01 seconde.

Insight 2 : élagage de partitions par colonne de tri

Un scénario plus courant est celui où le SQL de l'utilisateur ne contient que uid comme filtre, aucun prédicat temporel. Sans élagage de partitions, la requête doit analyser toutes les sous-partitions sous la partition de premier niveau correspondante. Sous la logique de fusion originale, toutes les instructions SQL physiques des partitions doivent se terminer avant que le tri de fusion puisse commencer, ce qui signifie que la partition la plus lente (souvent une plus ancienne avec des données massives) détermine la latence globale.

PolarDB-X résout cela avec un élagage dynamique basé sur la colonne de tri. Comme la colonne de tri (id ou update_ts) est approximativement monotone dans le temps, les nouvelles partitions ont des IDs ou des timestamps plus élevés, l'optimiseur peut arrêter d'analyser les partitions dès que suffisamment de lignes sont trouvées. Cela a réduit une requête de 3 secondes à 0,05 seconde en production.

Insight 3 : ordre des index pour les conditions OR/IN

La clé d'une pagination rapide sur les bases de données orientées lignes est la terminaison précoce : plutôt que de filtrer d'abord puis de trier pour sélectionner les K premiers, la requête devrait exploiter l'ordre des index pour filtrer et sélectionner les K premiers simultanément. Les prédicats d'égalité le font naturellement, mais les conditions OR brisent l'ordre des index.

La solution consiste à diviser les conditions OR/IN en plusieurs sous-requêtes ordonnées, puis à fusionner :

SELECT * FROM (
    (SELECT * FROM t_swap WHERE asset_a = 'TOKEN26'
     ORDER BY id LIMIT 100)
    UNION
    (SELECT * FROM t_swap WHERE asset_b = 'TOKEN50'
     ORDER BY id LIMIT 100)
) t
ORDER BY id
LIMIT 100;

Chaque branche utilise son propre index pour une terminaison précoce. Dans le scénario utilisateur, cela a transformé une requête de 10 secondes en une de 0,001 seconde.

Insight 4 : réduire les recherches de tables avec la matérialisation tardive

Pour les tables larges (60+ colonnes), SELECT * avec des recherches de tables est extrêmement coûteux. Les index de couverture aident, mais ils consomment un espace de stockage important. Lorsque les index de couverture ne sont pas réalisables, la matérialisation tardive est la réponse : d'abord, récupérer uniquement les clés primaires via un index de couverture (ne récupérant que le nombre de lignes LIMIT), puis effectuer une recherche ponctuelle de clé primaire pour ces quelques lignes.

SELECT t_order0.*
FROM (
    SELECT id, uid, pt
    FROM t_order 
    WHERE uid = 1
      AND channel_id = 0
      AND sub_id IN (0)
      AND id > 123456789
    ORDER BY update_ts DESC
    LIMIT 20
) AS t3
INNER JOIN t_order AS t_order0
  ON t3.id = t_order0.id
  AND t3.uid = t_order0.uid
  AND t3.pt = t_order0.pt
ORDER BY t_order0.update_ts DESC
LIMIT 20;

Cela a réduit une requête de 10 secondes à 0,6 seconde. Au-delà des optimisations au niveau SQL, PolarDB-X optimise également les recherches de tables au niveau du moteur de stockage (Guess Primarykey Pageno et adressage physique) pour réduire l'amplification d'E/S et améliorer les taux de succès du cache.

Insight 5 : sélection d'index déterministe

Pour les grandes tables de commandes, la mémoire n'est jamais suffisante. Si le mauvais index est choisi, de grands volumes de données sont chargés dans le buffer pool, polluant le cache et déclenchant des défaillances en cascade. La sélection d'index de la base de données doit suivre une méthodologie déterministe qui s'aligne avec les conceptions d'index recommandées par l'IA.

La liste de contrôle standard : indexer la colonne de tri (éviter filesort), placer les colonnes WHERE d'égalité avant les colonnes ORDER BY dans les index composites, utiliser des index de couverture lorsque c'est possible, mettre les colonnes à haute sélectivité en premier, éviter les fonctions sur les colonnes indexées, et maintenir une cohérence de type. Lorsque le comportement de la base de données correspond à la conception d'index recommandée, la stabilité est assurée. En production, PolarDB-X a maintenu une sélection d'index stable tandis qu'une base de données distribuée concurrente a montré un comportement instable, menant à un 4K de QPS de pointe (avec plantages) contre 60K (évolutivité linéaire) pour PolarDB-X.

Tableau récapitulatif des améliorations réelles

Les idées principales peuvent être résumées ainsi : élaguer agressivement, exploiter l'ordre des index pour une terminaison précoce, minimiser les recherches de tables avec des index de couverture ou une matérialisation tardive, et garantir une sélection d'index déterministe. Pour les équipes qui gèrent de grandes tables de commandes sur des bases de données distribuées, appliquer ces cinq leçons de manière systématique peut prévenir les pièges de performance les plus courants liés à la pagination.