AnalyticDB:Cache de pagination (optimisation des performances pour les requêtes paginées profondes)

Configurer une base de données de cache

Avant d'utiliser la fonctionnalité de cache de pagination pour mettre en cache les résultats de requête, nous vous recommandons de spécifier une base de données destinée à stocker les tables de cache temporaires utilisées pour les requêtes paginées. Si aucune base de données n'est spécifiée, les tables de cache temporaires seront stockées dans la base de données interne connectée. Lorsque vous activez la fonctionnalité de cache de pagination, une table de cache temporaire est créée automatiquement.

Par exemple, vous pouvez désigner la base de données paging_cache comme base de données de cache. D'autres bases de données peuvent également être spécifiées.

SET ADB_CONFIG PAGING_CACHE_SCHEMA=paging_cache;

Activer la fonctionnalité de cache de pagination pour les requêtes paginées

Si plusieurs requêtes paginées partagent le même modèle SQL, ajoutez un hint (indicateur) aux instructions SQL pour améliorer les performances. Lors de la première initiation d'une requête paginée contenant un hint, le système crée une table de cache temporaire afin de stocker les résultats de la requête paginée. Lorsque vous lancez des requêtes paginées ultérieures utilisant le même modèle SQL, incluez le même hint dans les instructions SQL. Le système lira alors les données depuis la table de cache temporaire, sans avoir besoin d'accéder de nouveau à la base de données.

Interroger les informations relatives aux tables de cache

Vous pouvez consulter les informations concernant toutes les tables de cache des requêtes paginées au sein du cluster actuel, telles que l'identifiant de pagination, la taille du cache et l'état du cache.

SELECT * FROM INFORMATION_SCHEMA.KEPLER_PAGING_CACHE_STATUS_MERGED;

Spécifier le nombre maximal de tables de cache

Vous pouvez spécifier le nombre maximal de tables de cache au sein du cluster actuel. La valeur par défaut est 32.

SET ADB_CONFIG PAGING_CACHE_MAX_TABLE_COUNT=32;

Si vous tentez de créer une table de cache alors que le nombre total de tables de cache dépasse la limite, une erreur se produit. Exemple de message d'erreur :

Paging cache count exceeds the limit. Please clean up unused caches or increase the related parameter using SET ADB_CONFIG PAGING_CACHE_MAX_TABLE_COUNT=xxx.

Effacez les données de cache devenues inutiles ou augmentez le nombre maximal de tables de cache, en suivant les indications du message d'erreur.

Spécifier une durée de validité pour une table de cache

Vous pouvez spécifier une durée de validité pour une table de cache, exprimée en secondes. Une fois cette période écoulée, la table de cache devient invalide. Lorsque vous lancez des requêtes paginées ultérieures partageant le même modèle SQL, le système accède à la base de données et met à jour la table de cache. Généralement, il est recommandé de spécifier une durée de validité pour une table de cache dans les scénarios de contrôle de concurrence des rapports.

Par exemple, utilisez le hint /*paging_cache_enabled=true, paging_cache_validity_interval=300*/ pour rendre une table de cache valide pendant 300 secondes après sa création. Exemple d'instruction :

/*paging_cache_enabled=true, paging_cache_validity_interval=300*/ SELECT * FROM t_order ORDER BY id LIMIT 0, 100;

Effacer les données de cache des requêtes paginées

Lorsque vous utilisez la fonctionnalité de cache de pagination pour mettre en cache les résultats des requêtes paginées, les données sont stockées dans l'espace de stockage chaud d'AnalyticDB for MySQL. Si certaines données mises en cache ne sont plus nécessaires, effacez-les afin d'augmenter l'espace de stockage disponible.

Effacement manuel

• Données de cache des requêtes paginées spécifiées par un modèle SQL

  Utilisez le hint /*paging_cache_enabled=true, invalidate_paging_cache=true*/ pour effacer les données de cache des requêtes paginées spécifiées par un modèle SQL.

  Exemple d'instruction :

  /*paging_cache_enabled=true,invalidate_paging_cache=true*/ SELECT * FROM t_order ORDER BY id LIMIT 0, 100;

• Données de cache des requêtes paginées spécifiées par le paramètre paging_id**

  Effacez les données de cache des requêtes paginées spécifiées par le paramètre paging_id.

  Exemple d'instruction :

  CLEAN_PAGING_CACHE paging123;

  Remarque

  Pour savoir comment obtenir la valeur du paramètre paging_id, consultez la section « Interroger les informations relatives aux tables de cache » de cette rubrique.

Effacement automatique

Vous pouvez spécifier un délai d'expiration du cache afin d'effacer les données de cache des requêtes paginées qui n'ont pas été consultées dans l'intervalle de temps défini. La valeur par défaut est 3 600 secondes. Par défaut, les données de cache non consultées pendant 1 heure sont automatiquement effacées.

SET ADB_CONFIG PAGING_CACHE_EXPIRATION_TIME=3600;

Alternative : Pagination par jeu de clés (Keyset pagination)

Si votre scénario métier prend en charge la navigation basée sur un curseur (par exemple, naviguer vers la page suivante ou précédente sans sauter vers une page arbitraire), vous pouvez utiliser la pagination par jeu de clés comme alternative à la pagination traditionnelle basée sur OFFSET. Au lieu d'utiliser OFFSET pour ignorer des lignes, la pagination par jeu de clés emploie une clause WHERE pour filtrer les lignes en fonction de la dernière valeur récupérée. Cela évite la surcharge liée au tri global et au brassage de données inter-nœuds pour les pages profondes.

Par exemple, supposons que la colonne id soit la clé primaire et que la dernière valeur id de la page actuelle soit 1000. Utilisez l'instruction SQL suivante pour interroger la page suivante :

-- Traditional deep pagination (slow)
SELECT * FROM t_order ORDER BY id LIMIT 1000, 100;

-- Keyset pagination (fast, recommended)
SELECT * FROM t_order WHERE id > 1000 ORDER BY id LIMIT 100;

La méthode de pagination par jeu de clés récupère uniquement le lot de lignes suivant directement depuis l'index, sans tri ni saut d'un grand nombre de lignes. Cela élimine la dégradation des performances causée par la pagination profonde. Les performances de la requête restent constantes, quelle que soit la profondeur de la pagination.

Erreurs courantes et dépannage

Échec de la préparation du cache de pagination, cache indisponible

Message d'erreur :

Paging cache prepare failed, and cache is not available. Please use /*paging_cache_enabled=true,invalidate_paging_cache=true*/ to clean the unavailable cache or set a specific pagingId with /*paging_id=xxx*/ to gen a new cache. Note that the old and new cache data may be inconsistent.

Cause : Lorsque vous utilisez la fonctionnalité de cache de pagination pour interroger des données, des exceptions peuvent survenir, telles qu'un redémarrage de nœud ou une mise à l'échelle. Si une requête paginée atteint une table de cache dont la création a échoué, le serveur n'effectue pas d'accès à la base de données ni de recréation automatique de la table de cache. Le serveur renvoie une erreur.

Solution : Pour garantir la cohérence des données, procédez comme suit : Dans les scénarios d'exportation de données, nous vous recommandons d'effacer les données exportées ainsi que les données de cache indisponibles, puis de relancer une requête paginée pour recréer une table de cache. Dans d'autres scénarios, nous vous conseillons d'effacer les données de cache indisponibles ou de spécifier une nouvelle valeur pour le paramètre paging_id, puis de relancer une requête paginée afin de recréer une table de cache.

-- General paged query without using the paging cache feature
SELECT * FROM lineitem ORDER BY l_orderkey,l_linenumber LIMIT 0,100000;

-- Paged query by using the paging cache feature (ORDER BY eliminated in the data export scenario)
/*paging_cache_enabled=true*/ SELECT * FROM lineitem LIMIT 0,100000;