Optimiere Datenbankabfragen für verbesserte Leistung

Einführung

In diesem Projekt lernst du, wie du Datenbankabfragen optimierst, um die Leistung im LabEx-System zu verbessern. Das LabEx-System ist eine Plattform, die Benutzerstudienakten, Kurse und andere verwandte Daten verwaltet. Das Projekt konzentriert sich auf die Optimierung von drei häufig verwendeten Abfragen im System.

👀 Vorschau

🎯 Aufgaben

In diesem Projekt wirst du lernen:

Wie du einen zusammengesetzten Index auf den Feldern user und study_time der Tabelle usercourse hinzufügen, um die Abfrageleistung zu verbessern.
Wie du die erste Abfrage optimierst, um die Lernzeit eines bestimmten Benutzers für einen bestimmten Kurs zu finden.
Wie du die zweite Abfrage optimierst, um die Lernakten der Kurse eines Benutzers nach Lernzeit zu sortieren.
Wie du die dritte Abfrage optimierst, um alle Kurse abzurufen, die ein Benutzer studiert hat.

🏆 Errungenschaften

Nach Abschluss dieses Projekts wirst du in der Lage sein:

Die Wichtigkeit von Indexierung bei der Datenbankoptimierung zu verstehen.
Zusammengesetzte Indexe zu implementieren, um die Leistung komplexer Abfragen zu verbessern.
Abfrageausführungspläne zu analysieren, um Leistungsschwachstellen zu identifizieren.
Datenbankabfragen mithilfe geeigneter Indexe zu optimieren.
Dein Wissen anzuwenden, um die Leistung von Echtzeit-Datenbankanwendungen zu verbessern.

Füge einen zusammengesetzten Index hinzu

In diesem Schritt lernst du, wie du einen zusammengesetzten Index auf den Feldern user und study_time der Tabelle usercourse hinzufügen.

Starte den MySQL-Dienst.
```
sudo /etc/init.d/mysql start
```
Melde dich am MySQL-Terminal an.
```
mysql -uroot
```
Importiere die Daten aus dem bereitgestellten Skript initDatabase.sql in die MySQL-Datenbank:

SOURCE /home/labex/project/initDatabase.sql

Erstelle die Datei addIndex.sql im Verzeichnis /home/labex/project.
Füge in der Datei addIndex.sql die folgende SQL-Anweisung hinzu, um den zusammengesetzten Index zu erstellen:
```
ALTER TABLE labex.usercourse ADD INDEX ix_user_study_time (user, study_time);
```
Diese Anweisung erstellt einen zusammengesetzten Index namens ix_user_study_time auf den Feldern user und study_time der Tabelle usercourse.
Speichere die Datei addIndex.sql.
Im MySQL-Terminal führe den folgenden Befehl aus, um das SQL-Skript auszuführen:
```
SOURCE /home/labex/project/addIndex.sql
```
Dadurch wird der zusammengesetzte Index in der Datenbank erstellt.

Verifiziere die Indexerstellung, indem du die folgende SQL-Anweisung ausführst:

SHOW INDEX FROM usercourse;

Die Ausgabe sollte den neuen zusammengesetzten Index anzeigen.

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 Zeilen in set (0,001 sec)

Optimieren Sie Abfrage 1 – Finden Sie die Lernzeit von Benutzer A für Kurs B

In diesem Schritt lernst du, wie du die erste Abfrage optimierst, um die Lernzeit von Benutzer A für Kurs B zu finden.

Öffne das MySQL-Terminal.
Erläutere die ursprüngliche Abfrage:
```
EXPLAIN SELECT study_time
FROM usercourse
WHERE user = 'A' AND course = 'B';
```
Dies zeigt den Ausführungsplan der ursprünglichen Abfrage, mit dem du potenzielle Leistungsschwachstellen identifizieren kannst.
Ändere die Abfrage, um den zusammengesetzten Index zu verwenden, der im vorherigen Schritt erstellt wurde:
```
SELECT study_time
FROM usercourse
WHERE user = 'A' AND course = 'B';
```
Der zusammengesetzte Index auf user und study_time hilft der Datenbank, die relevanten Zeilen effizient zu finden und den study_time-Wert abzurufen.

Erläutere die geänderte Abfrage:

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

Die Ausgabe sollte zeigen, dass die Datenbank den Index ix_user_study_time verwendet, um die Abfrage auszuführen, was zu einer besseren Leistung führen sollte.

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 Zeile 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 Zeilen 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 Zeile in set (0,000 sec)

Optimieren Sie Abfrage 2 – Sortieren Sie die Lernakten von Benutzer A's Kursen nach Lernzeit

In diesem Schritt lernst du, wie du die zweite Abfrage optimierst, um die Lernakten von Benutzer A's Kursen nach Lernzeit zu sortieren.

Öffne das MySQL-Terminal.
Erläutere die ursprüngliche Abfrage:
```
EXPLAIN SELECT *
FROM usercourse
WHERE user = 'A'
ORDER BY study_time;
```
Dies zeigt den Ausführungsplan der ursprünglichen Abfrage, mit dem du potenzielle Leistungsschwachstellen identifizieren kannst.
Ändere die Abfrage, um den zusammengesetzten Index zu verwenden, der im ersten Schritt erstellt wurde:
```
SELECT *
FROM usercourse
WHERE user = 'A'
ORDER BY study_time;
```
Der zusammengesetzte Index auf user und study_time hilft der Datenbank, die relevanten Zeilen effizient zu finden und sie nach study_time zu sortieren.

Erläutere die geänderte Abfrage:

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

Die Ausgabe sollte zeigen, dass die Datenbank den Index ix_user_study_time verwendet, um die Abfrage auszuführen, was zu einer besseren Leistung führen sollte.

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 Zeile 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 Zeilen 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 Zeile in set (0,000 sec)

Optimieren Sie Abfrage 3 – Abfragen aller Kurse, die Benutzer A studiert hat

In diesem Schritt lernst du, wie du die dritte Abfrage optimierst, um alle Kurse abzurufen, die Benutzer A studiert hat.

Öffne das MySQL-Terminal.
Erläutere die ursprüngliche Abfrage:
```
EXPLAIN SELECT course
FROM usercourse
WHERE user = 'A';
```
Dies zeigt den Ausführungsplan der ursprünglichen Abfrage, mit dem du potenzielle Leistungsschwachstellen identifizieren kannst.
Ändere die Abfrage, um den zusammengesetzten Index zu verwenden, der im ersten Schritt erstellt wurde:
```
SELECT DISTINCT course
FROM usercourse
WHERE user = 'A';
```
Der zusammengesetzte Index auf user und study_time hilft der Datenbank, die relevanten Zeilen effizient zu finden und die eindeutigen Kurswerte abzurufen.

Erläutere die geänderte Abfrage:

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

Die Ausgabe sollte zeigen, dass die Datenbank den Index ix_user_study_time verwendet, um die Abfrage auszuführen, was zu einer besseren Leistung führen sollte.

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 Zeile in set (0,000 sec)

MariaDB [labex]> SELECT DISTINCT course
    -> FROM usercourse
    -> WHERE user = 'A';
+--------+
| course |
+--------+
| B      |
+--------+
1 Zeile 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 Zeile in set (0,000 sec)

Indem du diese Schritte folgst, hast du gelernt, wie du einen zusammengesetzten Index hinzufügen und die drei Abfragen im LabEx-System optimieren, um deren Leistung zu verbessern.