Deep Thought

SQL Exercise - Indexoptimierung

9 min read

-- ============================================================
--  ÜBUNG: Indizes in MySQL verstehen und anwenden
-- ============================================================
 
-- ------------------------------------------------------------
-- 0) Vorbereitung: Datenbank anlegen und verwenden
-- ------------------------------------------------------------
 
DROP DATABASE IF EXISTS index_uebung;
CREATE DATABASE index_uebung;
USE index_uebung;
 
-- Optional: Ausführlichere Ausgabe für Pläne in MySQL 8+:
SET SESSION optimizer_switch = 'use_invisible_indexes=on';
 
-- Für rekursive CTE mit vielen Zeilen:
-- (Standard ist oft 1000; wir erhöhen auf 200000)
SET SESSION cte_max_recursion_depth = 200000;
 
 
 
-- ------------------------------------------------------------
-- 1) Tabelle USERS anlegen (ohne zusätzliche Indizes)
-- ------------------------------------------------------------
 
-- Wir simulieren eine typische User-Tabelle einer Web-Anwendung.
-- Später werden wir häufig nach last_name, email und city suchen.
 
-- AUFGABE:
-- Lies dir die Spalten durch und überlege:
--  - Nach welchen Spalten könnte eine Anwendung typischerweise suchen?
--  - Welche Spalten sind eher nur zur Anzeige gedacht?
--  Notiere deine Vermutung, bevor du weiter machst.
 
DROP TABLE IF EXISTS users;
 
CREATE TABLE users (
    id           INT PRIMARY KEY,
    first_name   VARCHAR(50) NOT NULL,
    last_name    VARCHAR(50) NOT NULL,
    email        VARCHAR(150) NOT NULL,
    city         VARCHAR(100) NOT NULL,
    signup_date  DATE NOT NULL
) ENGINE = InnoDB;
 
 
 
-- ------------------------------------------------------------
-- 2) USER-Daten generieren (ca. 50.000 Zeilen)
-- ------------------------------------------------------------
 
-- Wir verwenden eine rekursive CTE, um viele Zeilen zu erzeugen.
-- HINWEIS: Das Einfügen kann ein paar Sekunden dauern.
 
-- AUFGABE:
-- Führe **nur** diesen INSERT aus, beobachte und dokumentiere
-- wie lange der Befehl ungefähr dauert.
 
INSERT INTO users (id, first_name, last_name, email, city, signup_date)  
WITH RECURSIVE seq AS (  
	SELECT 1 AS n  
	UNION ALL  
	SELECT n + 1 FROM seq WHERE n < 50000  
)  
SELECT  
	n AS id,  
	ELT(1 + (n % 5), 'Anna', 'Ben', 'Clara', 'David', 'Eva') AS first_name,  
	ELT(1 + (n % 5), 'Müller', 'Huber', 'Schmidt', 'Gruber', 'Wagner') AS last_name,  
	CONCAT('user', n, '@example.com') AS email,  
	ELT(1 + (n % 4), 'Wien', 'Linz', 'Graz', 'Salzburg') AS city,  
	DATE('2023-01-01') + INTERVAL (n % 365) DAY AS signup_date  
FROM seq;
 
 
 
-- ------------------------------------------------------------
-- 3) Erste Abfragen auf USERS ohne Indizes
-- ------------------------------------------------------------
 
-- AUFGABE 1:
-- Führe die folgenden SELECTs nacheinander aus.
--  1) Notiere dir jeweils die Ausführungszeit.
--  2) Führe zusätzlich EXPLAIN ANALYZE davor aus und betrachte:
--       - Wie viele Zeilen werden geschätzt/gescannt?
--       - Wird ein Full Table Scan gemacht?
 
-- Beispiel:
-- EXPLAIN ANALYZE
-- SELECT ...
-- ;
 
-- 3) Schreibe zu jeder Abfrage auf, ob du hier einen Index
--    sinnvoll findest und auf welcher(n) Spalte(n).
 
-- 3.1) Suche nach einem häufig vorkommenden Nachnamen
EXPLAIN ANALYZE
SELECT *
FROM users
WHERE last_name = 'Müller';
 
-- 3.2) Suche nach einer seltenen E-Mail (eher selektiv)
EXPLAIN ANALYZE
SELECT *
FROM users
WHERE email = 'user12345@example.com';
 
-- 3.3) Nutzer aus einer bestimmten Stadt
EXPLAIN ANALYZE
SELECT *
FROM users
WHERE city = 'Wien';
 
-- 3.4) Nutzer, die im Mai 2023 beigetreten sind
EXPLAIN ANALYZE
SELECT *
FROM users
WHERE signup_date BETWEEN '2023-05-01' AND '2023-05-31';
 
 
 
-- ------------------------------------------------------------
-- 4) Indizes für USERS anlegen und Effekte beobachten
-- ------------------------------------------------------------
 
-- AUFGABE 2:
-- Erstelle nun gezielt Indizes für typische Suchspalten.
-- Vorschlag (du kannst eigene Ideen ergänzen oder variieren):
--   - Index auf last_name
--   - Index auf email (hier könnte sogar ein UNIQUE Index sinnvoll sein)
--   - Index auf city
--   - Optional: zusammengesetzter Index auf (city, signup_date)
 
-- >>> TRAGE HIER DEINE EIGENEN CREATE INDEX BEFEHLE EIN <<<
 
 
-- AUFGABE 3:
-- Führe die Abfragen aus Abschnitt 3 (3.1–3.4) erneut mit
-- EXPLAIN ANALYZE aus.
--  - Wie haben sich die Ausführungszeiten verändert?
--  - Wie haben sich die geschätzten/gescannten Zeilen verändert?
--  - Siehst du in EXPLAIN, dass der Index jetzt benutzt wird
--    (Spalten "key" / "rows")?
 
-- Schätze anschließend:
--  - Welcher dieser Indizes bringt am meisten?
--  - Gibt es einen Index, der eher wenig bringt?
--    Begründe deine Einschätzung.
 
 
 
-- ------------------------------------------------------------
-- 5) LIKE-Abfragen und Grenzen von Indizes
-- ------------------------------------------------------------
 
-- AUFGABE 4:
-- Betrachte diese zwei Abfragen auf die Spalte email.
 
-- 5.1) Präfix-Suche: (kann einen Index nutzen)
EXPLAIN ANALYZE
SELECT *
FROM users
WHERE email LIKE 'user12%';
 
-- 5.2) Suffix-Suche: (kann den Index in vielen Fällen nicht nutzen)
EXPLAIN ANALYZE
SELECT *
FROM users
WHERE email LIKE '%45@example.com';
 
-- FRAGEN:
--  - Welche der beiden Abfragen ist schneller?
--  - Was zeigt der Query-Plan bzgl. Indexnutzung?
--  - Warum kann MySQL bei einem führenden '%' den Index nicht
--    sinnvoll verwenden?
 
 
 
-- ------------------------------------------------------------
-- 6) LOG_ENTRIES-Tabelle anlegen (für Zeitreihen / Logging)
-- ------------------------------------------------------------
 
-- Wir simulieren eine Log-Tabelle, wie sie z.B. in einem Webshop
-- oder einer API vorkommen könnte. Typische Queries:
--  - Viele Zeilen, aber nur ein bestimmter Zeitraum
--  - Bestimmte Aktionstypen (LOGIN, PURCHASE, …)
--  - Filter auf user_id
 
DROP TABLE IF EXISTS log_entries;
 
CREATE TABLE log_entries (
    id          INT PRIMARY KEY,
    user_id     INT NOT NULL,
    action_type VARCHAR(20) NOT NULL,   -- z.B. 'LOGIN', 'VIEW', 'PURCHASE'
    status      VARCHAR(20) NOT NULL,   -- z.B. 'SUCCESS', 'ERROR'
    created_at  DATETIME NOT NULL,
    details     TEXT
) ENGINE = InnoDB;
 
 
 
-- ------------------------------------------------------------
-- 7) LOG-Daten generieren (ca. 200.000 Zeilen)
-- ------------------------------------------------------------
 
-- AUFGABE 5:
-- Führe den folgenden INSERT aus und beobachte unten die Dauer.
-- Das kann je nach Rechner etwas dauern.
-- Tipp: Vergleiche später, wie sich Insert-Zeiten mit vielen Indizes verhalten.
 
WITH RECURSIVE seq AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 200000
)
INSERT INTO log_entries (id, user_id, action_type, status, created_at, details)
SELECT
    n AS id,
    1 + (n % 10000) AS user_id,  -- 10.000 verschiedene User
    ELT(1 + (n % 4), 'LOGIN', 'LOGOUT', 'VIEW', 'PURCHASE') AS action_type,
    ELT(1 + (n % 3), 'SUCCESS', 'ERROR', 'CANCELLED') AS status,
    TIMESTAMP(
        DATE('2024-01-01') + INTERVAL (n % 365) DAY,
        MAKETIME(n % 24, n % 60, n % 60)
    ) AS created_at,
    CONCAT('Log-Eintrag Nr. ', n, ' für User ', 1 + (n % 10000)) AS details
FROM seq;
 
 
 
-- ------------------------------------------------------------
-- 8) Abfragen auf LOG_ENTRIES ohne Indizes
-- ------------------------------------------------------------
 
-- AUFGABE 6:
-- Führe die folgenden Queries mit EXPLAIN ANALYZE aus.
-- Notiere Zeit + Plan.
 
-- 8.1) Alle erfolgreichen Logins in einem bestimmten Monat
EXPLAIN ANALYZE
SELECT *
FROM log_entries
WHERE action_type = 'LOGIN'
  AND status = 'SUCCESS'
  AND created_at BETWEEN '2024-05-01' AND '2024-05-31';
 
-- 8.2) Letzte 50 Aktionen eines bestimmten Users
EXPLAIN ANALYZE
SELECT *
FROM log_entries
WHERE user_id = 1234
ORDER BY created_at DESC
LIMIT 50;
 
-- 8.3) Anzahl der Purchases pro Tag im Juni
EXPLAIN ANALYZE
SELECT DATE(created_at) AS day,
       COUNT(*) AS purchase_count
FROM log_entries
WHERE action_type = 'PURCHASE'
  AND created_at BETWEEN '2024-06-01' AND '2024-06-30'
GROUP BY DATE(created_at)
ORDER BY day;
 
 
 
-- ------------------------------------------------------------
-- 9) Sinnvolle Indizes für LOG_ENTRIES erstellen
-- ------------------------------------------------------------
 
-- ÜBERLEGUNG:
--  - Welche Spalten kommen in WHERE-Bedingungen vor?
--  - In welcher Reihenfolge erscheinen sie?
--  - Welche Spalten sind sehr selektiv (viele verschiedene Werte)?
--  - Welche sind eher unselektiv (wenige verschiedene Werte)?
 
-- AUFGABE 7:
-- Formuliere für jede der Abfragen 8.1–8.3 einen Vorschlag für
-- einen (oder mehrere) Indizes, die sie schneller machen könnten.
 
-- >>> TRAGE HIER ZUERST DEINE EIGENEN CREATE INDEX BEFEHLE EIN <<<
 
 
 
-- ------------------------------------------------------------
-- 10) Abfragen erneut ausführen (mit Indizes)
-- ------------------------------------------------------------
 
-- AUFGABE 8:
-- Führe die Queries aus Abschnitt 8 (8.1–8.3) erneut mit
-- EXPLAIN ANALYZE aus und vergleiche:
--  - Ausführungszeiten vorher / nachher
--  - Anzahl gescannter Zeilen (Spalte "rows")
--  - verwendeter Index (Spalte "key")
 
-- BEANTWORTE:
--  a) Wie groß ist die Beschleunigung in etwa (Faktor)?
--  b) Welche Abfrage hat am meisten profitiert?
--  c) Bei welcher Abfrage ist der Unterschied eher klein?
--  d) Erkläre, warum das deiner Meinung nach so ist.
 
 
 
-- ------------------------------------------------------------
-- 11) Nachteile von Indizes: Einfügen & Speicherplatz
-- ------------------------------------------------------------
 
-- Indizes beschleunigen SELECTs, haben aber auch Kosten:
--  - Jede INSERT/UPDATE/DELETE-Operation muss auch die Indizes anpassen.
--  - Indizes benötigen zusätzlichen Speicher.
--
-- Das wollen wir kurz praktisch sehen.
 
-- AUFGABE 9:
-- a) Miss die Zeit für das Einfügen von weiteren 50.000 Zeilen
--    in die Tabelle log_entries (mit den bereits existierenden Indizes).
-- b) Lösche danach alle Indizes auf log_entries, füge erneut
--    50.000 Zeilen ein und vergleiche die Zeiten.
--
-- Hinweis: Die genauen Zeiten sind nicht so wichtig, sondern
-- nur der Vergleich "mit Indizes" vs. "ohne Indizes".
 
-- a) Einfügen MIT Indizes
WITH RECURSIVE seq AS (
    SELECT 200001 AS n
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 250000
)
INSERT INTO log_entries (id, user_id, action_type, status, created_at, details)
SELECT
    n AS id,
    1 + (n % 10000) AS user_id,
    ELT(1 + (n % 4), 'LOGIN', 'LOGOUT', 'VIEW', 'PURCHASE') AS action_type,
    ELT(1 + (n % 3), 'SUCCESS', 'ERROR', 'CANCELLED') AS status,
    TIMESTAMP(
        DATE('2024-01-01') + INTERVAL (n % 365) DAY,
        MAKETIME(n % 24, n % 60, n % 60)
    ) AS created_at,
    CONCAT('Zweite Welle Log-Eintrag Nr. ', n) AS details
FROM seq;
 
-- b) Indizes entfernen
DROP INDEX idx_log_entries_action_status_date ON log_entries;
DROP INDEX idx_log_entries_user_date ON log_entries;
DROP INDEX idx_log_entries_action_date ON log_entries;
 
-- c) Nochmals 50.000 Zeilen OHNE Indizes einfügen
WITH RECURSIVE seq AS (
    SELECT 250001 AS n
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 300000
)
INSERT INTO log_entries (id, user_id, action_type, status, created_at, details)
SELECT
    n AS id,
    1 + (n % 10000) AS user_id,
    ELT(1 + (n % 4), 'LOGIN', 'LOGOUT', 'VIEW', 'PURCHASE') AS action_type,
    ELT(1 + (n % 3), 'SUCCESS', 'ERROR', 'CANCELLED') AS status,
    TIMESTAMP(
        DATE('2024-01-01') + INTERVAL (n % 365) DAY,
        MAKETIME(n % 24, n % 60, n % 60)
    ) AS created_at,
    CONCAT('Dritte Welle Log-Eintrag Nr. ', n) AS details
FROM seq;
 
-- FRAGEN:
--  - War das Einfügen ohne Indizes schneller spürbar schneller?
--  - Warum ist das so?
--  - Was bedeutet das für Tabellen, in die sehr häufig geschrieben wird
--    (z.B. Logging, Messdaten, …)?
 
 
 
-- ------------------------------------------------------------
-- 12) Reflexion: Wann sind Indizes sinnvoll?
-- ------------------------------------------------------------
 
-- AUFGABE 10 (Diskussion / schriftliche Reflexion):
-- Beantworte die folgenden Fragen in eigenen Worten:
--
-- 1) Woran kann man erkennen, dass ein Index sinnvoll ist?
--    (Hinweise: SELECT-Häufigkeit, WHERE-Bedingungen, Selektivität)
--
-- 2) Woran erkennt man, dass ein Index vielleicht unnötig ist
--    oder sogar schadet?
--    (Hinweise: kaum verwendete Spalte, sehr wenige verschiedene Werte,
--    viele Schreiboperationen)
--
-- 3) Erkläre anhand eines deiner Beispiele aus dieser Übung,
--    wie ein konkreter Index den Query-Plan und die Ausführungszeit
--    verbessert hat.
--
-- 4) Formuliere eine einfache Faustregel für den praktischen Einsatz:
--    "Wann erstelle ich einen Index, wann eher nicht?"
 
 
-- ENDE DER ÜBUNG
-- Du kannst die Datenbank index_uebung bei Bedarf wieder löschen:
-- DROP DATABASE index_uebung;

Graph View

Backlinks