Introduction
Un magasin en pleine croissance a besoin de deux vues utiles des commandes : les plus anciennes commandes prêtes d’un magasin et l’historique des commandes les plus récentes d’un client. La première page contient sept commandes. Les identifiants de commande distincts doivent rester distincts même lorsque les horodatages et les prix sont identiques ; une commande promotionnelle à zéro centime peut tout de même être prête au retrait.
Vous enregistrerez deux requêtes MySQL réutilisables et un script d’index, appliquerez des index utiles et maintiendrez les deux premières pages dans une limite commune de deux index secondaires et de 64 demandes natives de lignes par page dans le diagnostic. L’instantané fictif d’origine contient plus de 96 000 commandes. Cette unité optimise les premières pages ; l’unité indépendante suivante enseigne la pagination complète par curseur.
Vous devez savoir naviguer dans un terminal, vous connecter à MySQL et utiliser SELECT/WHERE. Les paramètres, les plages temporelles semi-ouvertes, le tri, les index et les observations des plans réels sont expliqués avant utilisation. Le serveur MySQL privé, les données d’origine et Terminal sont prêts ; aucun développement Python ni d’interface web n’est requis.
Travaillez dans /home/labex/project/order-desk. Créez ou modifiez queue.sql, history.sql et indexes.sql, et gérez au plus deux index secondaires. Conservez les commandes, colonnes, clé primaire, identités, états, horodatages applicatifs exacts, centimes, notes et outils exécutables d’origine. Ne supprimez ni ne réécrivez les lignes sources, ne changez pas les réglages de diagnostic, n’ajoutez pas d’indications à l’optimiseur et n’utilisez pas de cache externe. Les noms d’index et le SQL équivalent peuvent différer. Les libellés et sorties de l’environnement d’exécution restent en anglais.
Les connexions de diagnostic préparées désactivent l’index condition pushdown, une optimisation qui peut masquer le filtrage dans le moteur de stockage. Cela facilite l’interprétation des comparaisons de demandes natives de lignes. Ce réglage s’applique uniquement à ces connexions ; l’exercice ne mesure pas les E/S disque physiques et ne promet pas de facteur d’accélération en production. Les millisecondes et une chaîne exacte de plan ne sont pas des critères d’acceptation.
Examiner la charge et optimiser la file de retrait
Dans cette étape, vous examinerez des commandes valides avant de choisir un index utile. Une première page plus rapide doit toujours contenir les mêmes données de commande requises.
Commencez dans Terminal, à l’invite du shell ordinaire. cd entre dans l’espace de travail ; ./ exécute un programme du répertoire courant. db ouvre le client MySQL préparé pour lire les commandes et gérer les index du cours. Un here-document envoie les lignes comprises entre le marqueur SQL entre guillemets et son SQL final à ce client. Le marqueur final est seul sur sa ligne. Les instructions SQL se terminent par des points-virgules et cette connexion client se ferme à la fin du bloc. Si un chemin ou un nom de commande est mal saisi, utilisez ls pour examiner les véritables noms et réessayez la commande corrigée ; gardez les données d’origine intactes.
Examinez les commandes prêtes du magasin 3 le 10 mai. La comparaison >= inclut l’ouverture ; < exclut le minuit suivant. ORDER BY placed_at,order_id trie par heure puis identifiant, dans l’ordre croissant. L’identifiant départage les horodatages égaux. total_cents stocke des centimes entiers et placed_at est un DATETIME(6) : un horodatage applicatif conservant six chiffres de microsecondes. Ces horodatages fournis ne nécessitent pas de conversion de fuseau horaire.
cd /home/labex/project/order-desk
./db <<'SQL'
SELECT order_id,shop_id,customer_id,state,placed_at,total_cents
FROM orders
WHERE shop_id=3 AND state='ready'
AND placed_at>='2025-05-10' AND placed_at<'2025-05-11'
ORDER BY placed_at,order_id;
SQL
Il y a dix commandes admissibles : neuf identifiants distincts partagent 09:15 et la commande 900021 se situe à 23:59:59.999999. La commande 900004 coûte zéro et reste prête. La plupart des commandes à heure identique coûtent 1250 centimes, mais l’égalité de prix n’est pas une identité. Le minuit suivant exact appartient au jour suivant. Conservez ces faits lorsque vous changez les chemins d’accès.
Un index est une structure ordonnée supplémentaire qui aide MySQL à localiser les lignes. Il coûte du stockage et de la maintenance lors des écritures ; ajouter tous les index possibles n’est donc pas l’objectif. Un index composite ordonne les entrées selon plusieurs colonnes. La clé primaire identifie déjà order_id ; ce poste autorise au plus deux index secondaires supplémentaires pour les deux charges de travail réunies.
Un plan décrit comment MySQL atteint un résultat. EXPLAIN ordinaire comprend des estimations. EXPLAIN ANALYZE exécute réellement le SELECT et fournit des observations des itérateurs, telles que actual ... rows=... loops=.... FORMAT=TREE affiche les étapes d’exécution, appelées itérateurs, sous forme de texte parent/enfant indenté. LIMIT 7 demande au plus sept lignes triées. Cet itérateur supérieur peut renvoyer sept lignes tandis qu’un itérateur inférieur de parcours de table lit un ensemble bien plus grand. Les durées écoulées varient et ne sont pas notées. DATE(placed_at) ci-dessous extrait la date calendaire ; cette requête de référence particulière sélectionne donc toujours le bon jour unique.
./db <<'SQL'
EXPLAIN ANALYZE FORMAT=TREE
SELECT order_id,shop_id,customer_id,state,placed_at,total_cents
FROM orders
WHERE shop_id=3 AND state='ready' AND DATE(placed_at)='2025-05-10'
ORDER BY placed_at,order_id
LIMIT 7;
SQL
Lisez l’itérateur inférieur d’accès, pas seulement les sept lignes du sommet. Sans index adapté, il parcourt environ 96 000 commandes. La connexion de diagnostic garde index condition pushdown désactivé pour que le filtrage reste visible ; conservez ce réglage préparé de comparaison. Une petite page renvoyée ne suffit pas à établir une faible quantité de travail de la base.
Enregistrez une requête capable de traiter différentes demandes de magasin/état et des plages temporelles semi-ouvertes quelconques. Préfixer un nom par @ en fait une variable de session MySQL native. Le poste fournit @group_id, @state, @from_at et @until_at pour chaque demande dans la connexion de cette requête. Une variable définie dans une connexion db manuelle ne persiste pas dans une autre. Gardez les instructions SET hors du fichier de requête enregistré.
Les deux interfaces de requête renvoient six colonnes dans cet ordre : order_id, group_id, actor_id, state, event_at, amount_cents. AS donne à une colonne sélectionnée le nom d’interface : le magasin est le groupe et le client est l’acteur. Conservez les véritables valeurs plutôt que de les remplacer par des récapitulatifs. LIMIT 7 est le contrat de page à sept commandes du poste ; le réduire masquerait des commandes au lieu d’optimiser la page. ASC rend l’ordre croissant explicite.
cat > queue.sql écrit le texte complet suivant dans ce fichier en remplaçant tout contenu précédent. Le marqueur de here-document entre guillemets empêche l’expansion du shell. Collez tout le bloc avec le marqueur final seul :
cat > queue.sql <<'SQL'
SELECT order_id,shop_id AS group_id,customer_id AS actor_id,
state,placed_at AS event_at,total_cents AS amount_cents
FROM orders
WHERE shop_id=@group_id AND state=@state
AND placed_at>=@from_at AND placed_at<@until_at
ORDER BY placed_at ASC,order_id ASC
LIMIT 7;
SQL
./desk page queue
page exécute votre SQL enregistré sans le modifier et affiche ses véritables lignes. La première page contient les identifiants 900001 à 900007, y compris la commande à zéro centime. La valeur native handler_requests compte les demandes à la frontière serveur/moteur de stockage dans cette connexion de diagnostic ; ce n’est pas un nombre d’E/S physiques. Elle reste élevée avant l’indexation. Le curseur affiché pour la demande suivante provient uniquement de la véritable dernière ligne renvoyée ; cette requête de première page ne l’utilise pas encore. Les entrées de curseur affichées comme null signifient qu’il n’y a pas de ligne précédente pour une demande de première page.
Un préfixe d’accès utile fixe d’abord le magasin et l’état, puis suit la plage temporelle demandée et l’ordre (time,ID). Les deux colonnes d’égalité peuvent échanger leurs positions pour ces requêtes ; leur ordre textuel dans WHERE ne dicte pas l’ordre de l’index. Un index sur le temps seul peut encore devoir parcourir des magasins ou états sans rapport pour d’autres demandes. Enregistrez le candidat choisi dans indexes.sql afin qu’il puisse aussi être appliqué à une nouvelle copie.
cat > indexes.sql <<'SQL'
CREATE INDEX queue_time ON orders(shop_id,state,placed_at,order_id);
SQL
./db < indexes.sql
< fournit un fichier comme entrée SQL du client. Cela crée une structure d’accès, pas de nouvelles commandes. Si vous relancez plus tard le même CREATE INDEX, MySQL signale un nom de clé en double, car cet index existe déjà. Examinez les index existants avant de décider de conserver ou remplacer un candidat créé pour le cours ; ne supprimez pas les commandes sources et n’attendez pas de ce script qu’il soit une migration idempotente.
Ici, -e exécute la chaîne SQL complète entre guillemets puis quitte. L’action plan du poste exécute EXPLAIN ANALYZE sur la véritable requête enregistrée. --from et --until sélectionnent une autre ouverture et fin exclusive pour cette demande ; les valeurs ne comportant qu’une date signifient minuit.
./db -e "SHOW INDEX FROM orders;"
./desk page queue
./desk plan queue
./desk page queue --from 2025-01-05 --until 2025-01-12
Le chemin d’accès devrait maintenant utiliser une plage indexée étroite, tandis que la page d’origine du 10 mai contient toujours les mêmes sept identifiants de commande et champs exacts. Vérifiez les deux demandes par rapport à la limite de 64 demandes. SHOW INDEX prouve l’existence ; le plan réel, les lignes renvoyées et le nombre de demandes établissent le comportement utile. Les noms exacts d’index, coûts et millisecondes peuvent varier.
Si une commande ou un nom de fichier est mal saisi, lisez l’erreur, utilisez ls pour examiner l’espace de travail et réessayez le nom corrigé. Order desk error signifie une erreur de fichier, de SQL ou de connexion ; ce n’est pas un résultat vide réussi. Corrigez le SQL enregistré ou l’index candidat, puis relancez la véritable demande. Lancez la vérification de l’étape après avoir confirmé à la fois l’exactitude et le travail observé.
Partager la limite d’index avec l’historique client
Dans cette étape, vous prendrez en charge une seconde demande métier sans perdre la file de retrait ni dépasser deux index secondaires.
Restez dans Terminal et dans /home/labex/project/order-desk. Conservez les tables d’origine et queue.sql. La vue d’historique sélectionne un client, quels que soient l’état de retrait et le magasin, et montre d’abord les commandes les plus récentes. DESC inverse le tri ; utilisez-le pour l’horodatage comme pour l’identifiant afin que les commandes à même heure aient un ordre déterministe du plus récent au plus ancien. Le paramètre natif @actor_id identifie le client. Ne le remplacez pas par un client fixe et n’ajoutez pas la restriction d’état de la file.
Écrivez la requête complète de première page d’historique. Elle utilise la même interface à six colonnes et la même plage semi-ouverte que la requête de retrait :
cat > history.sql <<'SQL'
SELECT order_id,shop_id AS group_id,customer_id AS actor_id,
state,placed_at AS event_at,total_cents AS amount_cents
FROM orders
WHERE customer_id=@actor_id
AND placed_at>=@from_at AND placed_at<@until_at
ORDER BY placed_at DESC,order_id DESC
LIMIT 7;
SQL
./desk page history
./desk plan history
L’historique du 10 mai commence par la commande 900021 à la dernière microseconde, suivie des identifiants à heure égale dans l’ordre décroissant. Leurs véritables centimes et états restent visibles. Cette première page correcte peut encore demander trop de travail : l’index de file existant commence par magasin/état, que l’historique ne contraint pas.
Un second préfixe utile commence par le client puis le temps/l’identifiant. Un index croissant peut être parcouru à l’envers pour l’ordre décroissant de l’historique ; le plan observé montre ce qui se passe réellement. Des colonnes couvrantes supplémentaires ne sont pas requises dans cette tâche. Gardez au plus deux index secondaires et conservez un accès utile pour les deux charges.
cat >> indexes.sql ajoute la nouvelle instruction au fichier source existant ; > le remplacerait. Après l’ajout, exécutez uniquement la nouvelle instruction CREATE ci-dessous, car l’index de file existe déjà dans cette VM. Sur une nouvelle base, le fichier enregistré à deux instructions constitue la source complète des index.
cat >> indexes.sql <<'SQL'
CREATE INDEX actor_time ON orders(customer_id,placed_at,order_id);
SQL
./db -e "CREATE INDEX actor_time ON orders(customer_id,placed_at,order_id);"
./db -e "SHOW INDEX FROM orders;"
./desk page history
./desk plan history
Vérifiez un historique client plus large, une autre identité, une plage de dates vide et la file d’origine. --actor change l’identifiant client demandé ; il ne change pas les données sources. Une requête valide peut ne renvoyer aucune ligne, ce qui diffère d’une erreur d’exécution.
./desk page history --from 2025-01-01 --until 2025-05-01
./desk page history --actor 78
./desk page history --from 2027-01-01 --until 2027-01-02
./desk page queue
Les deux charges doivent conserver des premières pages correctes dans la limite du diagnostic. La demande vide renvoie réellement une liste de lignes vide, pas des commandes inventées. Le indexes.sql enregistré et les définitions actives sont vérifiés indépendamment : un bon fichier ne prouve pas qu’il a été appliqué et un index actif fonctionnel ne remplace pas la source enregistrée.
Si vous changez de candidat, examinez SHOW INDEX, supprimez uniquement l’index créé pour le cours que vous remplacez, mettez à jour indexes.sql et appliquez la nouvelle définition. Ne supprimez jamais la clé primaire d’origine, les colonnes ou les lignes. L’erreur de nom en double d’un CREATE répété invite à examiner la définition actuelle, pas à réinitialiser la VM.
L’objectif final de cette unité est deux recherches utiles de première page. Sept lignes renvoyées ne constituent pas une file plus longue complète. L’unité indépendante suivante commence avec ces requêtes de première page et index déjà préparés, puis enseigne les conditions de curseur qui conservent chaque commande sur toutes les pages. Lancez la vérification finale avec les deux requêtes enregistrées, le script complet d’index et au plus deux index secondaires effectifs.
Résumé
Vous avez examiné les véritables données des commandes et les observations réelles d’exécution, enregistré des requêtes réutilisables de retrait et d’historique client avec des bornes semi-ouvertes, et pris en charge les deux premières pages avec un ensemble limité d’index utiles. Les identités, horodatages, centimes et notes d’origine restent intacts. Vous avez distingué les demandes natives de lignes du diagnostic des nombres de lignes renvoyées, des estimations et des E/S physiques. La livraison complète sur plusieurs pages est l’objectif de l’unité suivante.
