PolarDB:LIMIT OFFSET pushdown

Fonctionnement

Dans MySQL standard, la clause LIMIT est évaluée au niveau de la couche SQL. Le moteur de stockage lui envoie toutes les lignes candidates ; la couche SQL ignore ensuite les lignes en fonction de la valeur OFFSET et renvoie le jeu de résultats final. Pour les requêtes paginées avec des valeurs OFFSET élevées, le moteur de stockage doit transmettre un grand volume de lignes aussitôt rejetées. Ce coût augmente linéairement avec le numéro de page.

Lorsque le LIMIT OFFSET pushdown est activé, PolarDB filtre les lignes au niveau du moteur de stockage avant qu'elles n'atteignent la couche SQL. Ce mécanisme s'applique principalement dans deux cas :

• Absence de conditions WHERE au niveau SQL : Lorsque les prédicats sont entièrement délégués via la fonctionnalité de pushdown de prédicats detach_range_condition, aucun filtrage ne subsiste dans la couche SQL. Le LIMIT OFFSET peut alors être délégué directement.

• Accès par index secondaire avec recherches dans la table : Si une requête utilise un index secondaire tout en nécessitant des colonnes de la table primaire, le LIMIT OFFSET pushdown évite les recherches inutiles dans la table au niveau du moteur de stockage. Cela permet de ne pas récupérer des lignes qui seraient de toute façon écartées.

Prérequis

Avant d'activer cette fonctionnalité, assurez-vous que votre cluster PolarDB for MySQL respecte les versions suivantes :

• Version 8.0, révision 8.0.1.1.16 ou ultérieure

• Version 8.0, révision 8.0.2.2.0 ou ultérieure

Pour vérifier la version de votre cluster, consultez Interroger la version du moteur.

Limites

Le LIMIT OFFSET pushdown ne s'applique que si la valeur OFFSET dépasse 512. Pour des valeurs plus faibles, le nombre de lignes à ignorer reste limité ; la surcharge liée à la coordination du pushdown excède alors le gain par rapport au chemin d'exécution standard.

Pour lever cette restriction, définissez le paramètre ignore_polar_optimizer_rule sur ON. Pour plus d'informations, reportez-vous à Spécifier les paramètres du cluster et des nœuds.

Activer ou désactiver le LIMIT OFFSET pushdown

Le LIMIT OFFSET pushdown est activé par défaut. Utilisez le paramètre loose_optimizer_switch pour modifier son état. Pour savoir comment modifier les paramètres, consultez Spécifier les paramètres du cluster et des nœuds.

Vérifier l'activation du pushdown

Exécutez la commande EXPLAIN sur votre requête et examinez le champ Extra. Si le LIMIT OFFSET pushdown est actif, la sortie contient la mention Using limit-offset pushdown. L'absence de cette chaîne indique que la requête ne remplit pas les conditions requises. Consultez la section Cas où le pushdown ne s'applique pas pour connaître les raisons les plus fréquentes.

Les exemples suivants s'appuient sur le schéma TPC-H.

EXPLAIN
SELECT *
FROM lineitem
LIMIT 10000000, 10\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: lineitem
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 59440464
     filtered: 100.00
        Extra: Using limit-offset pushdown

EXPLAIN SELECT * FROM lineitem WHERE l_orderkey > 10 AND l_orderkey < 60000000 LIMIT 10000000, 10\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: lineitem
   partitions: NULL
         type: range
possible_keys: PRIMARY,i_l_orderkey,i_l_orderkey_quantity
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 29720232
     filtered: 100.00
        Extra: Using limit-offset pushdown

EXPLAIN SELECT * FROM lineitem WHERE l_partkey > 10 AND l_partkey < 200000 LIMIT 5000000, 10\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: lineitem
   partitions: NULL
         type: range
possible_keys: i_l_partkey,i_l_suppkey_partkey
          key: i_l_suppkey_partkey
      key_len: 5
          ref: NULL
         rows: 11123302
     filtered: 100.00
        Extra: Using limit-offset pushdown

EXPLAIN SELECT * FROM lineitem WHERE l_partkey > 10 AND l_partkey < 200000 ORDER BY l_partkey LIMIT 5000000, 10\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: lineitem
   partitions: NULL
         type: range
possible_keys: i_l_partkey,i_l_suppkey_partkey
          key: i_l_suppkey_partkey
      key_len: 5
          ref: NULL
         rows: 11123302
     filtered: 100.00
        Extra: Using limit-offset pushdown

Améliorations des performances

La figure ci-dessous illustre les gains de temps de requête obtenus avec un jeu de données TPC-H (facteur d'échelle 10). Elle compare les requêtes Q1, Q2 et Q3 avec et sans activation du LIMIT OFFSET pushdown.