MySQL: EXPLAIN, SHOW PROCESSLIST, Query-Optimierung

Kategorie: Datenbank · Channel: #klassiker

SHOW PROCESSLIST – der erste Blick

# Was läuft gerade in MySQL?
mysql -e "SHOW PROCESSLIST\G"
mysql -e "SHOW FULL PROCESSLIST\G"   # vollständige Query-Texte

# Nur aktive Queries (kein Sleep):
mysql -e "SELECT id, user, host, db, command, time, state,
          LEFT(info, 100) as query
          FROM information_schema.processlist
          WHERE command != 'Sleep'
          ORDER BY time DESC;"

PROCESSLIST Felder verstehen

Kritische States

Waiting for lock          → Lock-Contention, anderer Query blockiert
Waiting for table lock    → ALTER TABLE oder LOCK TABLE läuft
Copying to tmp table      → GROUP BY/ORDER BY ohne Index = langsam
Sorting result            → filesort, kein passender Index
Sending data              → große Ergebnismengen
Locked                    → MyISAM-Lock (InnoDB: "Waiting for lock")

EXPLAIN – Query-Plan lesen

EXPLAIN zeigt wie MySQL einen Query intern ausführt.
Es verändert nichts – nur Analyse.

# EXPLAIN für SELECT
mysql -e "EXPLAIN SELECT * FROM orders
          WHERE status = 'pending'
          AND created_at > '2026-01-01'\G"

# EXPLAIN ANALYZE (MySQL 8.0+) – führt Query aus, zeigt echte Zeiten
mysql -e "EXPLAIN ANALYZE SELECT * FROM orders
          WHERE status = 'pending'\G"

# Für UPDATE/DELETE auch möglich:
mysql -e "EXPLAIN UPDATE orders SET status='done'
          WHERE created_at < '2020-01-01'\G"

EXPLAIN Ausgabe – die kritischen Felder

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders
   partitions: NULL
         type: ALL          ← KRITISCH: Full Table Scan!
possible_keys: NULL         ← kein Index verfügbar
          key: NULL         ← kein Index genutzt
      key_len: NULL
          ref: NULL
         rows: 2847291      ← scannt 2,8 Mio Zeilen!
     filtered: 11.11
        Extra: Using where  ← Filter nach dem Scan

type – das wichtigste Feld

system    → Tabelle hat genau 1 Zeile (ideal)
const     → Primary Key oder Unique Key Lookup (sehr gut)
eq_ref    → JOIN mit Unique/PK (gut)
ref       → Index-Lookup, mehrere mögliche Zeilen (gut)
range     → Index-Bereichssuche (WHERE id > 100) (ok)
index     → Full Index Scan (besser als ALL, aber noch langsam)
ALL       → Full Table Scan ← PROBLEM! Index fehlt oder wird nicht genutzt

Faustregel: type = ALL mit rows > 1000 → Index prüfen!

Extra – weitere Hinweise

Using index          → Index Covering Scan (gut! keine Tabellenzugriffe)
Using where          → Filter nach dem Scan (ok)
Using filesort       → Sortierung ohne Index (langsam bei großen Tabellen)
Using temporary      → Temporäre Tabelle (GROUP BY/ORDER BY ohne Index)
Using index condition → Index Condition Pushdown (gut, MySQL 5.6+)
NULL                 → nichts besonderes

Index fehlt – erkennen und beheben

# EXPLAIN zeigt type: ALL und key: NULL
# → Index anlegen

# Einfacher Index:
ALTER TABLE orders ADD INDEX idx_status (status);

# Zusammengesetzter Index (für mehrere WHERE-Felder):
ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);
# Reihenfolge wichtig: Gleichheits-Felder zuerst, dann Range-Felder!

# Index prüfen:
SHOW INDEX FROM orders;

# EXPLAIN nochmal – jetzt sollte type: ref oder range kommen:
mysql -e "EXPLAIN SELECT * FROM orders
          WHERE status = 'pending'
          AND created_at > '2026-01-01'\G"
# type: range, key: idx_status_created, rows: 42  ← viel besser!

# Online Index (ohne Tabellen-Lock, große Tabellen):
ALTER TABLE orders
  ADD INDEX idx_status_created (status, created_at),
  ALGORITHM=INPLACE, LOCK=NONE;

Slow Query Log

# Status prüfen:
mysql -e "SHOW VARIABLES LIKE 'slow_query%';"
mysql -e "SHOW VARIABLES LIKE 'long_query_time';"

# Live aktivieren (kein Restart):
mysql -e "SET GLOBAL slow_query_log = 'ON';"
mysql -e "SET GLOBAL long_query_time = 1;"
mysql -e "SET GLOBAL log_queries_not_using_indexes = 'ON';"
mysql -e "SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';"

# Dauerhaft in /etc/mysql/mysql.conf.d/mysqld.cnf:
# slow_query_log = 1
# slow_query_log_file = /var/log/mysql/slow.log
# long_query_time = 1
# log_queries_not_using_indexes = 1

# Auswerten:
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
# -s t = nach Gesamtzeit sortieren
# -t 10 = Top 10

Lock-Probleme diagnostizieren

# Wer hält einen Lock?
mysql -e "SELECT * FROM information_schema.innodb_trx\G"

# Wer wartet auf wen?
mysql -e "SELECT * FROM information_schema.innodb_lock_waits\G"

# InnoDB Status (detailliert):
mysql -e "SHOW ENGINE INNODB STATUS\G" | grep -A 30 "LATEST DETECTED DEADLOCK"
mysql -e "SHOW ENGINE INNODB STATUS\G" | grep -A 20 "TRANSACTIONS"

# Blockierende Query killen:
mysql -e "KILL QUERY 142;"   # nur Query abbrechen, Verbindung bleibt
mysql -e "KILL 142;"         # gesamte Verbindung trennen

Distro-Unterschiede: MySQL Pfade und Service

# Ubuntu/Debian:
systemctl status mysql
journalctl -u mysql

# RHEL/Rocky:
systemctl status mysqld
journalctl -u mysqld

# FreeBSD:
service mysql-server status
tail -f /var/log/mysql/mysqld.err

Schnellreferenz: MySQL Diagnose – Einzeiler

# Prozesse
mysql -e "SHOW FULL PROCESSLIST\G" | grep -A 5 "time: [3-9][0-9]"
mysql -e "SELECT id,time,state,LEFT(info,80) FROM information_schema.processlist WHERE command!='Sleep' ORDER BY time DESC;"

# EXPLAIN
mysql -e "EXPLAIN SELECT * FROM tabelle WHERE feld = 'wert'\G"

# Locks
mysql -e "SHOW ENGINE INNODB STATUS\G" | grep -A 10 "LOCK"
mysql -e "SELECT * FROM information_schema.innodb_trx\G"

# Slow Log
tail -50 /var/log/mysql/slow.log
mysqldumpslow -s t -t 5 /var/log/mysql/slow.log

# Index fehlt?
mysql -e "EXPLAIN SELECT ...\G" | grep "type: ALL"

# Verbindungen
mysql -e "SHOW STATUS LIKE 'Threads_connected';"
mysql -e "SHOW VARIABLES LIKE 'max_connections';"

# Prozess killen
mysql -e "KILL 142;"