Cours MariaDB / Améliorer filtres de date, tri et pagination sans changer les résultats
Améliorer une requête consiste à conserver les lignes voulues tout en adaptant les filtres, le tri et la pagination aux chemins d’accès possibles. Définissez clairement les bornes de date et les critères qui départagent les égalités, puis comparez OFFSET et la navigation par dernières valeurs sans supposer un gain de performance avant de l’avoir vérifié.
Vérifier les résultats avant de comparer les coûts
Une requête plus rapide qui oublie une heure à la frontière d’un jour ou une ligne à égalité ne constitue pas une amélioration équivalente. Comparez d’abord les clés primaires retournées et leur ordre. Examinez ensuite le plan, les lignes réellement lues et la latence. Retirer une condition métier pour réduire le coût change la question posée à la base.
Le guide ORDER BY et LIMIT explique le tri et les limites de lecture ; la comparaison des plans avec EXPLAIN aide à distinguer résultats et estimations d’accès.
| Élément | Décision à vérifier | Exemple |
|---|---|---|
| Filtre de date | Début inclus et borne suivante exclue | Une journée entière |
| Tri | Départage déterministe des égalités | ordered_at, order_id |
| Pagination | Navigation attendue et cohérence entre demandes | OFFSET ou dernières valeurs de tri |
| Recherche de texte | Motif réellement demandé | Préfixe et présence dans le texte sont différents |
Préparer les commandes et l’index de tri
Connectez-vous au serveur d’apprentissage avec course_user dans shopcourse. Le compte doit pouvoir créer, remplir, lire, modifier et nettoyer les objets d’exercice et y créer un index. Le guide du client MariaDB et des fichiers UTF-8 décrit la connexion.
Vérifiez le serveur et la base avec SELECT VERSION(), DATABASE();, puis les noms avec SHOW TABLES;. Les tables c41_customers, c41_products, c41_orders et c41_items sont propres à cet exercice. Si elles existent, examinez leurs définitions et leurs lignes avant de décider si elles peuvent être nettoyées. Ne remplacez pas des données à conserver.
Enregistrez le bloc complet de préparation ci-dessous en UTF-8, par exemple dans C:/mariadb-lab/c41-pagination.sql, après avoir créé le dossier. Dans le client MariaDB, lancez SOURCE C:/mariadb-lab/c41-pagination.sql; avec le chemin réel. Le SQL s’exécute dans la connexion au serveur, pas dans l’invite du système.
USE shopcourse;
CREATE TABLE c41_customers (
customer_id INT NOT NULL PRIMARY KEY,
customer_name VARCHAR(50) NOT NULL,
city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c41_products (
product_id INT NOT NULL PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
category VARCHAR(30) NOT NULL,
price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c41_orders (
order_id INT NOT NULL PRIMARY KEY,
customer_id INT NOT NULL,
ordered_at DATETIME NOT NULL,
status VARCHAR(20) NOT NULL,
shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
CONSTRAINT fk_c41_order_customer FOREIGN KEY (customer_id)
REFERENCES c41_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c41_items (
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL CHECK (quantity > 0),
unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
PRIMARY KEY (order_id, product_id),
CONSTRAINT fk_c41_item_order FOREIGN KEY (order_id)
REFERENCES c41_orders(order_id) ON DELETE RESTRICT,
CONSTRAINT fk_c41_item_product FOREIGN KEY (product_id)
REFERENCES c41_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c41_customers VALUES
(1,'김민수','서울'),(2,'이서연','부산'),
(3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c41_products VALUES
(1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
(3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c41_orders
(order_id,customer_id,ordered_at,status,shipping_fee) VALUES
(101,1,'2026-01-10 10:00:00','paid',1000),
(102,1,'2026-01-20 11:00:00','paid',1000),
(103,2,'2026-02-05 12:00:00','paid',1000),
(104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c41_items VALUES
(101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
(103,1,1,3500),(103,3,2,8000),(104,2,1,1500);
Vous obtenez quatre clients, quatre produits, quatre commandes et six détails. Les littéraux coréens sont conservés entre langues pour reproduire les données : les produits 1 à 4 sont un carnet, un stylo, une tasse et une lampe ; les clients 1 et 3 ont la ville Séoul, les clients 2 et 4 Busan et Daejeon. Gardez les chaînes UTF-8 et suivez les identifiants.
Vérifiez que le nom de l’index n’est pas déjà utilisé, puis créez la clé par horaire et identifiant :
CREATE INDEX idx_c41_time ON c41_orders(ordered_at,order_id);
Avec seulement quatre commandes, l’optimiseur peut choisir un parcours complet. Ce jeu de données sert à vérifier les lignes et les frontières, pas à chiffrer le gain sur une grande table. Les résultats suivants incluent les commandes correspondant aux conditions indiquées ; aucun filtre implicite ne retire la commande annulée 104.
Comparer une fonction de date et une plage explicite
Exécutez ces deux requêtes sur la même table :
SELECT order_id FROM c41_orders WHERE DATE(ordered_at)='2026-01-10'
ORDER BY order_id;
SELECT order_id FROM c41_orders
WHERE ordered_at>='2026-01-10' AND ordered_at<'2026-01-11' ORDER BY order_id;
Elles renvoient toutes deux 101. La seconde exprime un intervalle dont le 10 janvier est le début inclus et le 11 janvier le début exclu. Cette forme couvre une journée entière, y compris les heures et les fractions de seconde si la colonne utilise une précision fractionnaire. Elle évite de choisir une dernière heure artificielle comme 23:59:59.
Depuis MariaDB 11.1, certaines comparaisons de DATE ou YEAR sur une colonne temporelle indexée peuvent être réécrites par l’optimiseur pour servir à un accès par plage. Les types, opérateurs et conditions pris en charge sont précisés dans la documentation de l’optimisation DATE et YEAR. N’affirmez donc pas qu’une fonction sur la colonne empêche toujours l’utilisation d’un index.
La plage explicite rend les bornes lisibles entre versions, mais ne prouve pas à elle seule une meilleure vitesse. Appliquez EXPLAIN aux deux SELECT et examinez leurs accès réels possibles. Vérifiez aussi les types : formater une date en texte pour la comparer peut mélanger un besoin d’affichage et un besoin de recherche.
Conserver l’horaire et l’identifiant comme curseur de page
La pagination par clés, aussi appelée keyset pagination, mémorise les dernières valeurs du tri au lieu de compter les lignes à sauter. Dans l’ordre décroissant suivant, les valeurs après le curseur sont plus petites. Pour un horaire identique, l’identifiant unique départage les commandes.
SELECT order_id,ordered_at FROM c41_orders
ORDER BY ordered_at DESC,order_id DESC LIMIT 2;
SELECT order_id,ordered_at FROM c41_orders
WHERE ordered_at<'2026-02-05 12:00:00'
OR(ordered_at='2026-02-05 12:00:00' AND order_id<103)
ORDER BY ordered_at DESC,order_id DESC LIMIT 2;
La première page contient 104, puis 103. Le curseur est donc le couple formé par l’horaire du 5 février 2026 à 12:00:00 et l’identifiant 103. La seconde requête renvoie 102, puis 101. Le OR conserve les horaires antérieurs et, pour un horaire égal, les identifiants plus petits.
Dans une application, reprenez le couple exact de la dernière ligne reçue, gardez les mêmes filtres et les mêmes critères de tri, et transmettez les valeurs comme paramètres de la requête. Si le tri devient croissant, examinez aussi le sens des comparaisons : changer seulement DESC en ASC ne définit pas correctement le même parcours.
Cette navigation peut éviter de grands décalages OFFSET, mais elle ne remplit pas exactement le même besoin qu’un saut direct vers un numéro de page arbitraire. La documentation MariaDB sur la pagination présente la reprise depuis une position. Les insertions, suppressions ou modifications entre deux demandes peuvent encore changer la liste ; un curseur ne garantit pas à lui seul un instantané fixe de tous les résultats.
Testez maintenant la frontière entre deux horaires égaux dans une transaction annulable. Exécutez tout ce bloc dans la même connexion InnoDB :
START TRANSACTION;
UPDATE c41_orders SET ordered_at='2026-02-05 12:00:00' WHERE order_id=104;
SELECT order_id,ordered_at FROM c41_orders
ORDER BY ordered_at DESC,order_id DESC LIMIT 1;
SELECT order_id,ordered_at FROM c41_orders
WHERE ordered_at<'2026-02-05 12:00:00'
OR (ordered_at='2026-02-05 12:00:00' AND order_id<104)
ORDER BY ordered_at DESC,order_id DESC LIMIT 1;
ROLLBACK;
Le premier résultat est 104 et le suivant est 103, tous deux au même horaire. Une condition portant seulement sur un horaire strictement plus petit pourrait sauter 103. ROLLBACK doit rétablir l’horaire initial de 104, le 10 février 2026 à 13:00:00 ; relisez cette ligne pour le vérifier. Ne confondez pas ce changement contrôlé avec une liste totalement stable entre des demandes indépendantes.
Distinguer les colonnes demandées et le motif de recherche
Un SELECT * peut transférer inutilement de gros textes et augmenter le travail de lecture ou la mémoire utilisée. Choisir seulement les colonnes nécessaires ne revient pas à retirer des lignes correspondant à la condition. Vérifiez que le résultat reste suffisant pour l’utilisateur, puis examinez si le plan peut obtenir les valeurs demandées dans un index couvrant.
Avec un index B-tree ordinaire, une recherche de préfixe, comme LIKE 'mot%', et une recherche de présence, comme LIKE '%mot%', n’ont pas les mêmes possibilités d’accès. Remplacer la seconde par la première peut donner l’impression d’accélérer la requête tout en supprimant des résultats attendus. Définissez le motif et la collation, qui gouverne les comparaisons de texte, avant de choisir une autre stratégie de recherche.
Le guide de conception d’index selon la requête aide à relier les conditions, le tri et les colonnes sélectionnées. Ne donnez pas à une règle de préfixe une portée universelle sans tenir compte de la version et de la requête.
Garder les conditions de comparaison et nettoyer les tables
Conservez les versions avant et après de la requête, leurs clés retournées, la version du serveur, les index, le nombre de lignes et leur répartition. Distinguez un cache réchauffé d’une amélioration de structure, et ne forcez pas une clé simplement pour obtenir un plan attendu. Vérifiez aussi les autres tris et modes de pagination qui partagent la requête modifiée.
Avant de recommencer, contrôlez la base, les définitions et les données réelles, puis sauvegardez ce qui doit être conservé. Supprimez uniquement les objets jetables de cet exercice, les enfants avant les tables référencées. DROP TABLE supprime structure et données et n’est pas annulé par un ROLLBACK ordinaire ; gardez ce nettoyage en dehors de l’essai de modification réversible.
DROP TABLE shopcourse.c41_items;
DROP TABLE shopcourse.c41_orders;
DROP TABLE shopcourse.c41_products;
DROP TABLE shopcourse.c41_customers;




