data / sql

Tri et pagination (ORDER BY, LIMIT)

Leçon 31 exercice

Explication

Ce que vous allez apprendre

Trier des résultats avec ORDER BY, sur une ou plusieurs colonnes
Comprendre pourquoi l'ordre des lignes n'est jamais garanti sans ORDER BY
Découper des résultats en pages avec LIMIT et OFFSET
Repérer pourquoi OFFSET devient lent sur de grandes tables
Mettre en place une pagination par curseur (keyset pagination) performante

Dans quel contexte ?

Une équipe développe le flux d'actualités d'une application mobile qui interroge une table publications de 3 millions de lignes triées par date. Au tout début, la pagination classique avec OFFSET fonctionne bien. Mais quand des utilisateurs font défiler jusqu'à la page 5 000, les temps de réponse explosent : c'est exactement le problème que la pagination par curseur, vue en fin de leçon, permet d'éviter.

D'abord, une croyance fausse à corriger

Beaucoup de débutants pensent que les lignes d'une table sortent toujours "dans l'ordre", par exemple dans l'ordre où elles ont été insérées. En réalité, une base de données ne garantit AUCUN ordre par défaut : le moteur renvoie les lignes dans l'ordre le plus pratique pour lui à cet instant précis.

La conséquence directe

Cet ordre "pratique pour le moteur" peut changer d'une exécution à l'autre, même sans aucune modification des données. ORDER BY est donc la seule façon fiable d'obtenir un ordre prévisible et reproductible.

Une fois le tri acquis, un nouveau problème apparaît

Imagine une table de plusieurs millions de lignes triées par prix. Afficher tout ce résultat d'un coup sur une page web n'a aucun sens : il faut le découper en petits paquets, comme les pages d'un livre.

LIMIT et OFFSET répondent à ce besoin

LIMIT dit "combien de lignes je veux voir", OFFSET dit "combien j'en saute avant de commencer à compter". C'est exactement le mécanisme derrière les boutons "page suivante" d'un site e-commerce.

Technique	Clause	Performance sur grosse table	Cas d'usage
Pagination classique	`LIMIT n OFFSET m`	Se dégrade avec `m` grand	Petites tables, pages proches du début
Pagination par curseur	`WHERE id > dernier_id LIMIT n`	Constante, quel que soit `m`	Fils d'actualité, flux infinis
Top-N standard	`FETCH FIRST n ROWS ONLY`	Bonne (équivalent LIMIT)	SGBD Oracle/PostgreSQL

Mais attention, un piège apparaît en grandissant

OFFSET 100000 ne saute pas magiquement au bon endroit : la base doit quand même parcourir et compter les 100 000 premières lignes avant de les ignorer. Plus on avance dans les pages, plus l'opération devient lente.

Piège fréquent

Une API qui expose ?page=5000&taille=20 sur une table de plusieurs millions de lignes peut voir son temps de réponse passer de quelques millisecondes à plusieurs secondes, simplement parce que OFFSET 99980 oblige la base à lire et écarter 99 980 lignes à chaque requête.

La solution professionnelle : la pagination par curseur

Au lieu de compter des lignes à sauter, on retient simplement le dernier identifiant vu sur la page précédente, et on demande "tout ce qui vient après cet identifiant". Cette technique, appelée keyset pagination, reste rapide même à la page 10 000.

Bonne pratique

Dès qu'une liste peut dépasser quelques milliers de lignes et être parcourue par pagination (flux, historique, export), préfère la pagination par curseur (WHERE id > :dernier_id ORDER BY id LIMIT 20) associée à un index sur la colonne de tri.

Et ensuite ?

Un tri bien défini est indispensable pour donner du sens à toutes les analyses qui suivent — classements, top N par catégorie, moyennes mobiles — qui seront vues dans les leçons sur les agrégations et les fonctions de fenêtrage.

Commandes & code

Tri et pagination

-- Tri simple
SELECT nom, prix FROM produits ORDER BY prix;          -- croissant par défaut (ASC)
SELECT nom, prix FROM produits ORDER BY prix DESC;      -- décroissant

-- Tri multi-colonnes : le premier critère prime, le second départage les égalités
SELECT nom, categorie, prix
FROM produits
ORDER BY categorie ASC, prix DESC;

-- Tri par position (à éviter en prod, fragile si les colonnes changent)
SELECT nom, prix FROM produits ORDER BY 2 DESC;

-- Tri par expression calculée
SELECT nom, prix, stock, (prix * stock) AS valeur_stock
FROM produits
ORDER BY valeur_stock DESC;

-- LIMIT : ne garder que N lignes
SELECT nom, prix FROM produits ORDER BY prix DESC LIMIT 5;

-- OFFSET : sauter les N premières lignes -> pagination
SELECT nom, prix
FROM produits
ORDER BY id
LIMIT 20 OFFSET 40;  -- page 3 avec 20 résultats par page (0, 20, 40, ...)

-- Fonction utilitaire de pagination générique
-- page = 1, 2, 3... ; taille_page = nombre de lignes par page
-- OFFSET = (page - 1) * taille_page
SELECT * FROM produits ORDER BY id LIMIT 10 OFFSET (3 - 1) * 10;

-- Pagination par curseur (keyset pagination) : bien plus performante sur grosses tables
-- On mémorise le dernier id vu au lieu de recompter les lignes à sauter
SELECT id, nom, prix
FROM produits
WHERE id > 1053          -- dernier id de la page précédente
ORDER BY id
LIMIT 20;

-- Top-N par groupe avec FETCH (standard SQL, PostgreSQL/Oracle)
SELECT nom, prix
FROM produits
ORDER BY prix DESC
FETCH FIRST 3 ROWS ONLY;

-- Gérer les NULL dans le tri
SELECT nom, remise FROM produits ORDER BY remise NULLS LAST;   -- PostgreSQL
SELECT nom, remise FROM produits ORDER BY remise IS NULL, remise; -- portable

Résumé

ORDER BY s'applique après le filtrage, avant LIMIT.
La pagination par OFFSET devient lente sur de grandes tables : préférer la pagination par curseur (keyset) en production.
LIMIT/OFFSET ne sont pas standard SQL (Oracle/SQL Server utilisent FETCH/TOP).
Toujours associer ORDER BY à LIMIT : sans tri, l'ordre des lignes n'est pas garanti.

Exercices pratiques

1 disponible

Mission : sauver le fil d'actualité qui s'effondre à la page 5000

Objectif : Diagnostiquer la lenteur d'une pagination par OFFSET et la remplacer par une pagination par curseur performante.

Contexte

Le fil d'actualité d'une application mobile interroge une table publications de 3 millions de lignes triées par date. La pagination classique LIMIT 20 OFFSET m fonctionne bien en début de flux, mais dès que des utilisateurs font défiler jusqu'à la page 5000, le temps de réponse de l'API passe de quelques millisecondes à plusieurs secondes. Tu dois expliquer la cause et livrer une version corrigée.

Résoudre l’exercice →