Accueil › SQL & bases de données › Trier, limiter, paginer
Trier, limiter, paginer
Interroger avec SELECTdebutant
Un résultat n'a d'ordre que si vous le demandez.
SELECT reference, total, creee_le
FROM commande
ORDER BY creee_le DESC, id DESC -- second critère : départage les ex æquo
LIMIT 20;
Trois règles évitent les bugs les plus coûteux :
- Toujours un critère de départage unique. Trier sur une date seule laisse les ex æquo dans un ordre arbitraire ; en pagination, cela produit des lignes vues deux fois et d'autres jamais. Terminez le tri par une colonne unique, typiquement la clé primaire.
- Décider où vont les
NULL. PostgreSQL les place en dernier enASC, MySQL en premier. Si l'emplacement compte, écrivezNULLS LAST. OFFSETcoûte cher en profondeur.LIMIT 20 OFFSET 100000fabrique 100 020 lignes pour en renvoyer 20. Au-delà de quelques pages, passez à la pagination par curseur.
-- Page suivante, après la dernière ligne affichée
SELECT reference, total, creee_le
FROM commande
WHERE (creee_le, id) < (:dernier_creee_le, :dernier_id)
ORDER BY creee_le DESC, id DESC
LIMIT 20;
Cette forme compare des tuples : le couple est comparé colonne par colonne, exactement comme le tri. Le coût reste le même à la page 2 et à la page 2000.
Les termes à connaître
- ORDER BY
La seule façon de garantir l'ordre du résultat. Sans elle, l'ordre dépend du plan d'exécution et peut changer d'une requête à l'autre.
- LIMIT / OFFSET
LIMIT nborne le nombre de lignes renvoyées,OFFSET ken saute k. La forme normalisée estFETCH FIRST n ROWS ONLY;LIMITest accepté par PostgreSQL et MySQL.- Tri instable
Un tri dont les ex æquo ne sont pas départagés. Deux exécutions peuvent alors les renvoyer dans un ordre différent — d'où des doublons ou des oublis en pagination.
- NULLS FIRST / NULLS LAST
Position des valeurs nulles dans le tri. En PostgreSQL, les
NULLsont considérés comme les plus grandes valeurs : derniers enASC, premiers enDESC. MySQL fait l'inverse.- Pagination par décalage
LIMIT 20 OFFSET 4000: simple, mais la base doit produire puis jeter les 4000 premières lignes. Le coût croît avec le numéro de page.- Pagination par curseur (keyset)
Reprendre après la dernière ligne vue :
WHERE (creee_le, id) < (:date, :id) ORDER BY creee_le DESC, id DESC LIMIT 20. Coût constant quelle que soit la profondeur.
Cette page présente la partie essentielle de la leçon. Les exemples commentés, les pièges courants et les trois questions de quiz sont dans l'application.