La page qui mettait dix secondes
Un index est une liste triée qui aide une base de données à trouver des lignes vite, et EXPLAIN montre si une requête s'en sert. Sans index, une grande table devient lente.
Un service client ouvre les billets d'un client. Le premier jour, la page s'affiche aussitôt. Un an plus tard, il y a des millions de billets et la même page met dix secondes.
Personne n'a changé la requête. La table a grossi, et la base lit maintenant tous les billets pour trouver les vôtres.
À la fin de cette leçon
Remarque
Je peux accélérer une requête avec un index et lire EXPLAIN pour le vérifier.
Mots-clés
| Mot | Ce que ça veut dire | Exemple |
|---|---|---|
| Index | une liste triée gardée à côté d'une table, pour trouver des lignes sans les lire toutes | |
| Parcours complet | la lecture de toutes les lignes d'une table | type ALL dans EXPLAIN |
| EXPLAIN | une commande qui montre comment la base compte exécuter une requête, sans l'exécuter | |
| Plan d'exécution | les étapes que la base choisit pour répondre à une requête | |
| Estimation | le calcul approché de la base, pas un compte exact |
Les cellules que les étudiants remplissent restent vides dans cet aperçu.
Une table assez grande pour être lente
La boutique du laboratoire n'a que quelques dizaines d'articles : chaque requête est instantanée. Pour voir un index à l'œuvre, il faut plus de lignes.
Ces Runs créent une table billets de 5000 lignes dans votre propre base. La boutique n'est pas touchée.
Relancez-les quand vous voulez pour repartir de zéro.
Accepter le certificat du serveur
printf '[client]\nssl-verify-server-cert=0\n' > ~/.my.cnfAffichée ici, non exécutée. Créez un compte gratuit pour appuyer sur Exécuter et la saisir dans le terminal de votre propre labo.
Un nom court pour le client
alias bd='mariadb -h db -u student -pstudent -t -e'Affichée ici, non exécutée. Créez un compte gratuit pour appuyer sur Exécuter et la saisir dans le terminal de votre propre labo.
Créer votre base
bd "CREATE DATABASE IF NOT EXISTS student_copie"Affichée ici, non exécutée. Créez un compte gratuit pour appuyer sur Exécuter et la saisir dans le terminal de votre propre labo.
Supprimer l'ancienne table
bd "DROP TABLE IF EXISTS student_copie.billets"Affichée ici, non exécutée. Créez un compte gratuit pour appuyer sur Exécuter et la saisir dans le terminal de votre propre labo.
Créer la table
bd "CREATE TABLE student_copie.billets (id INT PRIMARY KEY, client_id INT NOT NULL, statut VARCHAR(10) NOT NULL, ouvert_le DATE NOT NULL)"Affichée ici, non exécutée. Créez un compte gratuit pour appuyer sur Exécuter et la saisir dans le terminal de votre propre labo.
La remplir de 5000 billets
bd "SET SESSION cte_max_recursion_depth = 10000; INSERT INTO student_copie.billets WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 5000) SELECT i, 1 + (i * 37) % 800, IF(i % 5 = 0, 'ouvert', 'ferme'), DATE_ADD('2024-01-01', INTERVAL i % 365 DAY) FROM n"Affichée ici, non exécutée. Créez un compte gratuit pour appuyer sur Exécuter et la saisir dans le terminal de votre propre labo.
Les compter
bd "SELECT COUNT(*) AS lignes_dans_table FROM student_copie.billets"Affichée ici, non exécutée. Créez un compte gratuit pour appuyer sur Exécuter et la saisir dans le terminal de votre propre labo.
EXPLAIN montre le plan
Écrivez le mot EXPLAIN devant une requête. La base ne l'exécute pas. Elle montre comment elle le ferait.
Trois colonnes comptent. type dit comment la table est lue. key nomme l'index utilisé, ou NULL s'il n'y en a pas. rows est son estimation du nombre de lignes à regarder.
Interrogez-la sur les billets d'un seul client.
Le plan pour un client, sans index
bd "EXPLAIN SELECT * FROM student_copie.billets WHERE client_id = 42"Affichée ici, non exécutée. Créez un compte gratuit pour appuyer sur Exécuter et la saisir dans le terminal de votre propre labo.
Exemple : lire le plan
| Colonne | Ce que vous avez vu | Ce que cela veut dire |
|---|---|---|
| type | ALL | un parcours complet : toutes les lignes sont lues |
| key | NULL | aucun index n'est utilisé |
| rows | environ 5000 | la base compte lire toute la table |
| Extra | Using where | elle lit chaque ligne, puis teste client_id |
Les cellules que les étudiants remplissent restent vides dans cet aperçu.
Un index est un raccourci trié
L'index d'un livre liste des mots dans l'ordre, avec le numéro de page. Vous ne lisez pas tout le livre pour trouver un mot.
Un index de base de données fait pareil. Il garde les valeurs de client_id dans l'ordre, chacune avec la place de sa ligne.
CREATE INDEX en crée un. Donnez-lui un nom, puis la table et la colonne.
Créer un index sur client_id
bd "CREATE INDEX idx_client ON student_copie.billets (client_id)"Affichée ici, non exécutée. Créez un compte gratuit pour appuyer sur Exécuter et la saisir dans le terminal de votre propre labo.
Demander le plan de nouveau
bd "EXPLAIN SELECT * FROM student_copie.billets WHERE client_id = 42"Affichée ici, non exécutée. Créez un compte gratuit pour appuyer sur Exécuter et la saisir dans le terminal de votre propre labo.
De 5000 lignes à environ 6
Maintenant type vaut ref, key vaut idx_client et rows vaut 6. La base va droit aux billets de ce client.
La requête n'a pas changé. Seul le plan a changé.
Une clé primaire a déjà un index. Une clé étrangère aussi : MySQL en crée un pour vous.
Voir les index de la table orders
mariadb -h db -u student -pstudent shop -t -e "SHOW INDEX FROM orders"Affichée ici, non exécutée. Créez un compte gratuit pour appuyer sur Exécuter et la saisir dans le terminal de votre propre labo.
Un index a un prix
Un index est une seconde copie d'une colonne, gardée dans l'ordre. Il prend de la place.
Chaque INSERT, UPDATE et DELETE doit aussi changer l'index. Chaque index de plus ralentit donc un peu l'écriture.
Ajoutez un index pour les questions posées souvent, pas pour toutes les colonnes.
Voir la place que prend un index
bd "ANALYZE TABLE student_copie.billets; SELECT data_length, index_length FROM information_schema.tables WHERE table_schema = 'student_copie' AND table_name = 'billets'"Affichée ici, non exécutée. Créez un compte gratuit pour appuyer sur Exécuter et la saisir dans le terminal de votre propre labo.
Une erreur fréquente
Attention
Beaucoup pensent qu'un index est toujours utilisé dès qu'il existe. Ce n'est pas vrai. Si la requête cache la colonne dans une fonction, la base ne peut pas se servir de la liste triée. Regardez le plan, ne supposez pas.
Indexer la date d'ouverture
bd "CREATE INDEX idx_ouverture ON student_copie.billets (ouvert_le)"Affichée ici, non exécutée. Créez un compte gratuit pour appuyer sur Exécuter et la saisir dans le terminal de votre propre labo.
Le plan avec une fonction sur la colonne
bd "EXPLAIN SELECT * FROM student_copie.billets WHERE YEAR(ouvert_le) = 2024"Affichée ici, non exécutée. Créez un compte gratuit pour appuyer sur Exécuter et la saisir dans le terminal de votre propre labo.
Le plan avec un intervalle sur la colonne
bd "EXPLAIN SELECT * FROM student_copie.billets WHERE ouvert_le >= '2024-03-01' AND ouvert_le < '2024-03-08'"Affichée ici, non exécutée. Créez un compte gratuit pour appuyer sur Exécuter et la saisir dans le terminal de votre propre labo.