Stratégies de pagination pour les ensembles de données complexes (lents)

Question

Quelles sont certaines des stratégies utilisées pour la pagination des ensembles de données qui impliquent des requêtes complexes ? count(*) prend environ 1,5 seconde donc nous ne voulons pas interroger la base de données pour chaque visualisation de page. Actuellement, environ 45 000 lignes sont renvoyées par cette requête.

Voici certaines des approches que j'ai envisagées :

• Mettre en cache le nombre de lignes et le mettre à jour toutes les X minutes
• Limitez (et décalez) les lignes comptées à 41 (par exemple) et affichez le sélecteur de page comme "1 2 3 4 ..."; puis recalculez si quelqu'un va réellement à la page 4 et affichez "... 3 4 5 6 7 ..."
• Obtenez le nombre de lignes une fois et stockez-le dans la session de l'utilisateur
• Supprimez le sélecteur de page et proposez uniquement un lien "Page suivante"

Answer 1

Ma suggestion est de demander à MySQL une ligne de plus que nécessaire dans chaque requête, et de décider en fonction du nombre de lignes dans le jeu de résultats s'il faut afficher le lien de page suivante ou non.

Answer 2

J'ai dû concevoir quelques stratégies de pagination en utilisant PHP et MySQL pour un site qui génère plus d'un million de pages vues par jour. J'ai suivi la stratégie en plusieurs étapes :

Indexation multi-colonne. J'aurais dû faire cela en premier lieu avant d'essayer une vue matérialisée.

Génération d'une vue matérialisée. J'ai créé une tâche cron qui faisait une dénormalisation commune des tables de documents que j'utilisais. Je ferais un SELECT ... INTO OUTFILE ... puis créer la nouvelle table et la remplacer :

SELECT ... INTO OUTFILE '/tmp/ondeck.txt' FROM mytable ...;
CREATE TABLE ondeck_mytable LIKE mytable;
LOAD DATA INFILE '/tmp/ondeck.txt' INTO TABLE ondeck_mytable...;
DROP TABLE IF EXISTS dugout_mytable;
RENAME TABLE atbat_mytable TO dugout_mytable, ondeck_mytable TO atbat_mytable;

Cela a permis de réduire le temps de verrouillage sur l'écriture de mytable au minimum et les requêtes de pagination pouvaient être effectuées sur la vue matérialisée atbat. J'ai simplifié ce qui précède en omettant la manipulation réelle, qui est sans importance.

Memcache. J'ai ensuite créé un wrapper autour de ma connexion à la base de données pour mettre en cache ces résultats paginés dans memcache. Cela a grandement amélioré les performances. Cependant, ce n'était toujours pas suffisant.

Génération par lots. J'ai écrit un démon PHP et extrait la logique de pagination. Il détectait les changements dans mytable et régénérait périodiquement depuis le premier enregistrement modifié jusqu'au plus récent tous les pages dans le système de fichiers du serveur web. Avec un peu de mod_rewrite, je pouvais vérifier si la page existait sur le disque et la servir. Cela m'a également permis de tirer pleinement parti du reverse proxying en laissant Apache détecter les en-têtes If-Modified-Since et répondre avec des codes de réponse 304. (Évidemment, j'ai supprimé toute option permettant aux utilisateurs de sélectionner le nombre de résultats par page, une fonctionnalité sans importance.)

Mise à jour : *RE `count():** Lors de l'utilisation de tables MyISAM,COUNT` n'a pas posé de problème lorsque j'ai pu réduire la contention lecture-écriture sur la table. Si j'utilisais InnoDB, je créerais un déclencheur qui mettrait à jour une table adjacente avec le nombre de lignes. Ce déclencheur ajouterait simplement +1 ou -1 en fonction des instructions INSERT ou DELETE.

RE sélecteurs de page (molettes). Lorsque je suis passé à un cache de requêtes agressif, les requêtes de molettes étaient également mises en cache, et lors de la génération par lots des pages, j'utilisais des tables temporaires - donc calculer la molette était sans problème. Beaucoup de calculs de molettes ont été simplifiés car il s'agissait d'un schéma de fichiers prévisible qui ne nécessitait en réalité que le numéro de page le plus élevé. Le numéro de page le plus bas était toujours 1.

Molette en fenêtre. L'exemple que vous donnez ci-dessus pour une molette en fenêtre (<< 4 [5] 6 >>) devrait être assez facile à réaliser sans aucune requête tant que vous connaissez votre nombre maximal de pages.

Answer 3

MySQL dispose d'un mécanisme spécifique pour calculer un nombre approximatif d'un ensemble de résultats sans la clause LIMIT: FOUND_ROWS().

Answer 4

MySQL est assez bon pour optimiser les requêtes LIMIT.

Cela signifie qu'il choisit le buffer de jointure approprié, le buffer de tri de fichiers, etc., juste assez pour satisfaire la clause LIMIT.

Remarquez également qu'avec 45k lignes, vous n'avez probablement pas besoin d'un décompte exact. Des décomptes approximatifs peuvent être déterminés en utilisant des requêtes séparées sur les champs indexés. Par exemple, cette requête :

SÉLECTIONNER  COUNT(*)
DEPUIS    ma_table
OÙ   col1 = :mavaleur
        ET col2 = :autrevaleur

peut être approximée par celle-ci :

SÉLECTIONNER  COUNT(*) *
        (
        SÉLECTIONNER  COUNT(*)
        DEPUIS    ma_table
        ) / 1000
DEPUIS    (
        SÉLECTIONNER  1
        DEPUIS    ma_table
        OÙ   col1 = :mavaleur
                ET col2 = :autrevaleur
        LIMIT 1000
        )

, ce qui est beaucoup plus efficace dans MyISAM.

Si vous donnez un exemple de votre requête complexe, je pourrais probablement dire quelque chose de plus précis sur la façon d'améliorer sa pagination.

Answer 5

Je ne suis pas du tout un expert de MySQL, mais peut-être abandonner le COUNT(*) et continuer avec COUNT(id) ?