Optimiser les requêtes de base de données pour améliorer les performances

Introduction

Dans ce projet, vous allez apprendre à optimiser les requêtes de base de données pour améliorer les performances dans le système LabEx. Le système LabEx est une plateforme qui gère les enregistrements d'études d'utilisateurs, les cours et d'autres données connexes. Le projet se concentre sur l'optimisation de trois requêtes fréquemment utilisées dans le système.

👀 Aperçu

🎯 Tâches

Dans ce projet, vous allez apprendre :

Comment ajouter un index composé sur les champs user et study_time de la table usercourse pour améliorer les performances de requête.
Comment optimiser la première requête pour trouver le temps d'étude d'un utilisateur spécifique pour un cours spécifique.
Comment optimiser la deuxième requête pour trier les enregistrements d'études des cours d'un utilisateur par temps d'étude.
Comment optimiser la troisième requête pour récupérer tous les cours que l'utilisateur a étudiés.

🏆 Réalisations

Après avoir terminé ce projet, vous serez capable de :

Comprendre l'importance de l'indexation dans l'optimisation de la base de données.
Implémenter des index composés pour améliorer les performances des requêtes complexes.
Analyser les plans d'exécution de requête pour identifier les goulots d'étranglement de performances.
Optimiser les requêtes de base de données en utilisant des index appropriés.
Appliquer vos connaissances pour améliorer les performances des applications de base de données du monde réel.

Ajouter un index composé

Dans cette étape, vous allez apprendre à ajouter un index composé sur les champs user et study_time de la table usercourse.

Démarrez le service MySQL.
```
sudo /etc/init.d/mysql start
```
Connectez-vous au terminal MySQL.
```
mysql -uroot
```
Importez les données à partir du script initDatabase.sql fourni dans la base de données MySQL :

SOURCE /home/labex/project/initDatabase.sql

Créez le fichier addIndex.sql dans le répertoire /home/labex/project.
Dans le fichier addIndex.sql, ajoutez l'instruction SQL suivante pour créer l'index composé :
```
ALTER TABLE labex.usercourse ADD INDEX ix_user_study_time (user, study_time);
```
Cette instruction crée un index composé nommé ix_user_study_time sur les champs user et study_time de la table usercourse.
Enregistrez le fichier addIndex.sql.
Dans le terminal MySQL, exécutez la commande suivante pour exécuter le script SQL :
```
SOURCE /home/labex/project/addIndex.sql
```
Cela créera l'index composé dans la base de données.

Vérifiez la création de l'index en exécutant l'instruction SQL suivante :

SHOW INDEX FROM usercourse;

La sortie devrait montrer le nouvel index composé.

MariaDB [labex]> SHOW INDEX FROM usercourse;
+------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table      | Non_unique | Key_name           | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| usercourse |          0 | PRIMARY            |            1 | ID          | A         |          10 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| usercourse |          1 | ix_user_study_time |            1 | user        | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| usercourse |          1 | ix_user_study_time |            2 | study_time  | A         |          10 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
+------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
3 rows in set (0.001 sec)

Optimiser la requête 1 – Trouver le temps d'étude de l'utilisateur A pour le cours B

Dans cette étape, vous allez apprendre à optimiser la première requête pour trouver le temps d'étude de l'utilisateur A pour le cours B.

Ouvrez le terminal MySQL.
Analysez la requête d'origine :
```
EXPLAIN SELECT study_time
FROM usercourse
WHERE user = 'A' AND course = 'B';
```
Cela montrera le plan d'exécution de la requête d'origine, que vous pouvez utiliser pour identifier les éventuels goulots d'étranglement de performances.
Modifiez la requête pour utiliser l'index composé créé dans l'étape précédente :
```
SELECT study_time
FROM usercourse
WHERE user = 'A' AND course = 'B';
```
L'index composé sur user et study_time aidera la base de données à localiser efficacement la ou les lignes pertinentes et à récupérer la valeur de study_time.

Analysez la requête modifiée :

EXPLAIN SELECT study_time
FROM usercourse
WHERE user = 'A' AND course = 'B';

La sortie devrait montrer que la base de données utilise l'index ix_user_study_time pour exécuter la requête, ce qui devrait entraîner de meilleures performances.

MariaDB [labex]> EXPLAIN SELECT study_time
 -> FROM usercourse
 -> WHERE user = 'A' AND course = 'B';
+------+-------------+------------+------+--------------------+------+---------+------+------+-------------+
| id   | select_type | table      | type | possible_keys      | key  | key_len | ref  | rows | Extra       |
+------+-------------+------------+------+--------------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | usercourse | ALL  | ix_user_study_time | NULL | NULL    | NULL | 10   | Using where |
+------+-------------+------------+------+--------------------+------+---------+------+------+-------------+
1 row in set (0.000 sec)

MariaDB [labex]> SELECT study_time
    -> FROM usercourse
    -> WHERE user = 'A' AND course = 'B';
+------------+
| study_time |
+------------+
| 50         |
| 130        |
| 60         |
| 70         |
| 80         |
| 90         |
| 100        |
| 110        |
| 120        |
| 50         |
+------------+
10 rows in set (0.000 sec)

MariaDB [labex]> EXPLAIN SELECT study_time
    -> FROM usercourse
    -> WHERE user = 'A' AND course = 'B';
+------+-------------+------------+------+--------------------+------+---------+------+------+-------------+
| id   | select_type | table      | type | possible_keys      | key  | key_len | ref  | rows | Extra       |
+------+-------------+------------+------+--------------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | usercourse | ALL  | ix_user_study_time | NULL | NULL    | NULL | 10   | Using where |
+------+-------------+------------+------+--------------------+------+---------+------+------+-------------+
1 row in set (0.000 sec)

Optimiser la requête 2 – Trier les enregistrements d'études des cours de l'utilisateur A par temps d'étude

Dans cette étape, vous allez apprendre à optimiser la deuxième requête pour trier les enregistrements d'études des cours de l'utilisateur A par temps d'étude.

Ouvrez le terminal MySQL.
Analysez la requête d'origine :
```
EXPLAIN SELECT *
FROM usercourse
WHERE user = 'A'
ORDER BY study_time;
```
Cela montrera le plan d'exécution de la requête d'origine, que vous pouvez utiliser pour identifier les éventuels goulots d'étranglement de performances.
Modifiez la requête pour utiliser l'index composé créé dans la première étape :
```
SELECT *
FROM usercourse
WHERE user = 'A'
ORDER BY study_time;
```
L'index composé sur user et study_time aidera la base de données à localiser efficacement les lignes pertinentes et à les trier par study_time.

Analysez la requête modifiée :

EXPLAIN SELECT *
FROM usercourse
WHERE user = 'A'
ORDER BY study_time;

La sortie devrait montrer que la base de données utilise l'index ix_user_study_time pour exécuter la requête, ce qui devrait entraîner de meilleures performances.

MariaDB [labex]> EXPLAIN SELECT *
 -> FROM usercourse
 -> WHERE user = 'A'
 -> ORDER BY study_time;
+------+-------------+------------+------+--------------------+------+---------+------+------+-----------------------------+
| id   | select_type | table      | type | possible_keys      | key  | key_len | ref  | rows | Extra                       |
+------+-------------+------------+------+--------------------+------+---------+------+------+-----------------------------+
|    1 | SIMPLE      | usercourse | ALL  | ix_user_study_time | NULL | NULL    | NULL | 10   | Using where; Using filesort |
+------+-------------+------------+------+--------------------+------+---------+------+------+-----------------------------+
1 row in set (0.000 sec)

MariaDB [labex]> SELECT *
    -> FROM usercourse
    -> WHERE user = 'A'
    -> ORDER BY study_time;
+----+------+--------+------------+
| ID | user | course | study_time |
+----+------+--------+------------+
| 6  | A    | B      | 100        |
| 7  | A    | B      | 110        |
| 8  | A    | B      | 120        |
| 10 | A    | B      | 130        |
| 1  | A    | B      | 50         |
| 9  | A    | B      | 50         |
| 2  | A    | B      | 60         |
| 3  | A    | B      | 70         |
| 4  | A    | B      | 80         |
| 5  | A    | B      | 90         |
+----+------+--------+------------+
10 rows in set (0.000 sec)

MariaDB [labex]> EXPLAIN SELECT *
    -> FROM usercourse
    -> WHERE user = 'A'
    -> ORDER BY study_time;
+------+-------------+------------+------+--------------------+------+---------+------+------+-----------------------------+
| id   | select_type | table      | type | possible_keys      | key  | key_len | ref  | rows | Extra                       |
+------+-------------+------------+------+--------------------+------+---------+------+------+-----------------------------+
|    1 | SIMPLE      | usercourse | ALL  | ix_user_study_time | NULL | NULL    | NULL | 10   | Using where; Using filesort |
+------+-------------+------------+------+--------------------+------+---------+------+------+-----------------------------+
1 row in set (0.000 sec)

Optimiser la requête 3 – Interroger tous les cours que l'utilisateur A a étudiés

Dans cette étape, vous allez apprendre à optimiser la troisième requête pour récupérer tous les cours que l'utilisateur A a étudiés.

Ouvrez le terminal MySQL.
Analysez la requête d'origine :
```
EXPLAIN SELECT course
FROM usercourse
WHERE user = 'A';
```
Cela montrera le plan d'exécution de la requête d'origine, que vous pouvez utiliser pour identifier les éventuels goulots d'étranglement de performances.
Modifiez la requête pour utiliser l'index composé créé dans la première étape :
```
SELECT DISTINCT course
FROM usercourse
WHERE user = 'A';
```
L'index composé sur user et study_time aidera la base de données à localiser efficacement les lignes pertinentes et à récupérer les valeurs distinctes de cours.

Analysez la requête modifiée :

EXPLAIN SELECT DISTINCT course
FROM usercourse
WHERE user = 'A';

La sortie devrait montrer que la base de données utilise l'index ix_user_study_time pour exécuter la requête, ce qui devrait entraîner de meilleures performances.

MariaDB [labex]> EXPLAIN SELECT course
 -> FROM usercourse
 -> WHERE user = 'A';
+------+-------------+------------+------+--------------------+------+---------+------+------+-------------+
| id   | select_type | table      | type | possible_keys      | key  | key_len | ref  | rows | Extra       |
+------+-------------+------------+------+--------------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | usercourse | ALL  | ix_user_study_time | NULL | NULL    | NULL | 10   | Using where |
+------+-------------+------------+------+--------------------+------+---------+------+------+-------------+
1 row in set (0.000 sec)

MariaDB [labex]> SELECT DISTINCT course
    -> FROM usercourse
    -> WHERE user = 'A';
+--------+
| course |
+--------+
| B      |
+--------+
1 row in set (0.000 sec)

MariaDB [labex]> EXPLAIN SELECT DISTINCT course
    -> FROM usercourse
    -> WHERE user = 'A';
+------+-------------+------------+------+--------------------+------+---------+------+------+------------------------------+
| id   | select_type | table      | type | possible_keys      | key  | key_len | ref  | rows | Extra                        |
+------+-------------+------------+------+--------------------+------+---------+------+------+------------------------------+
|    1 | SIMPLE      | usercourse | ALL  | ix_user_study_time | NULL | NULL    | NULL | 10   | Using where; Using temporary |
+------+-------------+------------+------+--------------------+------+---------+------+------+------------------------------+
1 row in set (0.000 sec)

En suivant ces étapes, vous avez appris à ajouter un index composé et à optimiser les trois requêtes dans le système LabEx pour améliorer leurs performances.

✨ Vérifier la solution et pratiquer

Sommaire

Félicitations ! Vous avez terminé ce projet. Vous pouvez pratiquer plus de laboratoires dans LabEx pour améliorer vos compétences.

La requête est si lente

Introduction

👀 Aperçu

🎯 Tâches

🏆 Réalisations

Une expérience immersive pour apprendre le code.

Ajouter un index composé

Optimiser la requête 1 – Trouver le temps d'étude de l'utilisateur A pour le cours B

Apprenez en écrivant du vrai code.

Optimiser la requête 2 – Trier les enregistrements d'études des cours de l'utilisateur A par temps d'étude

Optimiser la requête 3 – Interroger tous les cours que l'utilisateur A a étudiés

Interagissez avec le code en temps réel.

Sommaire