Bild von NicoElNino / Shutterstock
SQL-Statements mit MySQL erfassen
Der vorherige Artikel Capturing running SQL statements behandelt einen wichtigen Baustein der Performance-Analyse von Anwendungen, die auf einer relationalen Datenbank (RDBMS) basieren. Um ein SQL-Statement in MySQL fundiert analysieren zu können, sind darüber hinaus weitere Meta-Informationen nötig, die den passenden Kontext liefern. Dazu zählen die genutzten Tabellenstrukturen, Statistiken zu Daten und Struktur sowie die Zeitabläufe der einzelnen Schritte bei Vorbereitung und Ausführung des SQL-Statements. Diese Informationen werden zu unterschiedlichen Zeitpunkten erfasst – vor, während und nach der Ausführung.
Zusätzliche Informationen zum Statement erfassen
In MySQL helfen die folgenden ergänzenden Informationen dabei, SQL-Statements Ihrer Anwendung zu validieren, zu bewerten und zu tunen. Sie lassen sich mit den Standard-Tools von MySQL erheben.
- Zurückgegebene Zeilen (nach der Ausführung)
- Ausführungszeit (nach der Ausführung)
- Query Execution Plan (QEP) (vor der Ausführung)
- Tabellenstruktur (vor der Ausführung)
- Index-Kardinalität (vor der Ausführung)
- Kosten der Query-Ausführung (vor der Ausführung)
- Bewertung durch den Query-Optimizer (vor der Ausführung)
- Statuswerte (vor, während und nach der Ausführung)
- Query-Profiling (nach der Ausführung)
- Processlist (während der Ausführung)
Sofern nicht anders angegeben, kommt in den folgenden Beispielen das Kommandozeilen-Tool mysql zum Einsatz.
HINWEIS: Die hier gezeigten SQL-Beispiele sind sowohl mit MySQL 5.7 als auch mit MySQL 8.0 kompatibel. Da die Community-Edition von MySQL 5.7 das End-of-Life (EOL) erreicht hat, beziehen sich alle Beispielausgaben und Verweise auf die aktuelle Version MySQL 8.0. Einzelne Ausgaben in diesem Artikel können von denen unter MySQL 5.7 abweichen.
1\. Zurückgegebene Zeilen
Zurückgegebene Zeilen mit dem MySQL-Client
Mit dem mysql-Client führen Sie ein SQL-Statement aus; die Anzahl der zurückgegebenen Zeilen erscheint im Anschluss an das Ergebnis in einer Statuszeile. Diese muss allerdings zusammen mit dem übrigen Output geparst werden.
mysql> SELECT ...
10 rows in set ...
Zurückgegebene Zeilen über das Slow Query Log
Das Slow Query Log enthält für jedes geloggte SQL-Statement die zurückgegebenen Zeilen im Wert `Rows_sent`.
# Query_time: 0.473775 Lock_time: 0.000751 Rows_sent: 10 Rows_examined: 152960
Der Artikel Capturing running SQL statements zeigt im Detail, wie Sie das MySQL Slow Query Log konfigurieren, nutzen und auswerten.
Zurückgegebene Zeilen über das performance/sys-Schema
Informationen zu rows_sent erhalten Sie auch aus den Schemas PERFORMANCE_SCHEMA und sys, allerdings sind die Werte dort meist aggregiert. Im folgenden Beispiel zeigt exec_count=1, dass rows_sent exakt für eine Ausführung steht.
mysql> SELECT * FROM sys.x$statement_analysis WHERE query LIKE '%airport%'\G
query: SELECT `a` . `country_code` , `c` . `name` , COUNT ( * ) AS `cnt` FROM `airport` `a` INNER JOIN `country` `c` USING ( `country_code` ) GROUP BY `country_code` , NAME ORDER BY ? DESC LIMIT ?
db: airport
full_scan: *
exec_count: 1
err_count: 0
warn_count: 0
total_latency: 477392952000
max_latency: 477392952000
avg_latency: 477392952000
lock_latency: 221000000
rows_sent: 10
rows_sent_avg: 10
rows_examined: 152960
rows_examined_avg: 152960
rows_affected: 0
rows_affected_avg: 0
tmp_tables: 1
tmp_disk_tables: 0
rows_sorted: 10
sort_merge_passes: 0
digest: 616aa31e4f99b22a194c91ae9f1cfbfb95d6957621b55393b4a01603d8e1fd47
first_seen: 2024-01-18 19:23:42.595635
2\. Ausführungszeit der Query
Ausführungszeit über den MySQL-Client
Ähnlich wie die Anzahl der zurückgegebenen Zeilen gibt der mysql-Client auch die Gesamtausführungszeit aus – mit einer Granularität von 10 Millisekunden.
mysql> SELECT ...
10 rows in set (0.28 sec)
Ausführungszeit über Profiling
Mit Profiling erhalten Sie eine hochpräzise Gesamtausführungszeit eines SQL-Statements.
mysql > SET @@profiling=1;
mysql > SELECT ...
mysql > SET @@profiling=0;
mysql > SHOW PROFILES;
mysql > SELECT SUM(DURATION) FROM INFORMATION_SCHEMA.PROFILING WHERE query_id=1;
HINWEIS: Wenn Sie weitere SELECT-Statements ausführen, müssen Sie die query_id ggf. anpassen, damit sie zu der Query passt, deren Ausführungszeit Sie ermitteln möchten.
mysql > SHOW PROFILES;
+----------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| 1| 0.23497150 | SELECT a.country_code, c.name, COUNT(*) AS cnt
FROM airport a
INNER JOIN country c USING (country_code)
GROUP BY country_code, name ORDER BY 3 DESC LIMIT 10 |
+----------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
mysql> SELECT SUM(DURATION) FROM INFORMATION_SCHEMA.PROFILING WHERE query_id=9;
+----------------+
| SUM(DURATION) |
+----------------+
| 0.2349715 |
+----------------+
Der Einsatz von Profiling wird in einem späteren Abschnitt ausführlicher beschrieben.
Ausführungszeit über das Slow Query Log
Wird eine Query in das Slow Query Log geschrieben, erhalten Sie über den Wert Query_time eine hochpräzise Ausführungszeit des SQL-Statements.
# Query_time: 0.473775 Lock_time: 0.000751 Rows_sent: 10 Rows_examined: 152960
3\. Query Execution Plan (QEP)
Das EXPLAIN-Statement liefert den QEP – mit wertvollen Informationen darüber, wie MySQL das SQL-Statement ausführen wird. Der QEP zeigt die Ausführungsschritte in der vorgesehenen Reihenfolge und liefert ergänzende Angaben wie ausgewertete Indizes, berücksichtigte Partitionen, verwendete Join-Typen, Zeilenschätzungen sowie weitere Details in der Spalte Extra.
mysql> EXPLAIN SELECT ...
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------------+-------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------------+-------+----------+---------------------------------+
| 1 | SIMPLE | a | NULL | ALL | country_code | NULL | NULL | NULL | 86733 | 100.00 | Using temporary; Using filesort |
| 1 | SIMPLE | c | NULL | eq_ref | PRIMARY | PRIMARY | 8 | airport.a.country_code | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------------+-------+----------+---------------------------------+
2 rows in set, 1 warning (0.04 sec)
Das EXPLAIN-Statement erzeugt immer genau eine Warnung – nämlich das für die interne Ausführung umgeschriebene Statement. Treten mehr Warnungen auf, sollten Sie diese prüfen, bevor Sie weitermachen.
> SHOW WARNINGS;
Note (Code 1003): /* select#1 */ select `airport`.`a`.`country_code` AS `country_code`,`airport`.`c`.`name` AS `name`,count(0) AS `cnt` from `airport`.`airport` `a` join `airport`.`country` `c` where (`airport`.`c`.`country_code` = `airport`.`a`.`country_code`) group by `airport`.`a`.`country_code` order by count(0) desc limit 10
Neben dem klassischen Tabellenformat (FORMAT=TRADITIONAL) gibt es auch das neuere JSON-Format, das zusätzlich cost_info-Daten liefert.
mysql> EXPLAIN FORMAT=JSON SELECT ...
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "105264.60"
},
"ordering_operation": {
"using_filesort": true,
"grouping_operation": {
"using_temporary_table": true,
"using_filesort": false,
"nested_loop": [\
{\
"table": {\
"table_name": "a",\
"access_type": "ALL",\
"possible_keys": [\
"country_code"\
],\
"rows_examined_per_scan": 86733,\
"rows_produced_per_join": 86733,\
"filtered": "100.00",\
"cost_info": {\
"read_cost": "1185.00",\
"eval_cost": "8673.30",\
"prefix_cost": "9858.30",\
"data_read_per_join": "256M"\
},\
"used_columns": [\
"airport_id",\
"name",\
"country_code"\
]\
}\
},\
{\
"table": {\
"table_name": "c",\
"access_type": "eq_ref",\
"possible_keys": [\
"PRIMARY"\
],\
"key": "PRIMARY",\
"used_key_parts": [\
"country_code"\
],\
"key_length": "8",\
"ref": [\
"airport.a.country_code"\
],\
"rows_examined_per_scan": 1,\
"rows_produced_per_join": 86733,\
"filtered": "100.00",\
"cost_info": {\
"read_cost": "86733.00",\
"eval_cost": "8673.30",\
"prefix_cost": "105264.60",\
"data_read_per_join": "160M"\
},\
"used_columns": [\
"country_code",\
"name"\
]\
}\
}\
]
}
}
}
}
Mehr dazu finden Sie im MySQL 8.0 Reference Manual.
Ein späteres Beispiel zur Option EXPLAIN ANALYZE zeigt zudem das Layout FORMAT=TREE.
4\. Tabellenstruktur
Tabellenstruktur über den mysql-Client (1)
Für jede Tabelle in einem SQL-Statement sollten Sie deren Spalten und Constraints kennen. Nutzen Sie dazu die Syntax SHOW CREATE TABLE.
mysql> SHOW CREATE TABLE airport\G
*************************** 1. row ***************************
Table: airport
Create Table: CREATE TABLE `airport` (
`airport_id` int unsigned NOT NULL,
`ident` varchar(7) NOT NULL,
`type` varchar(20) NOT NULL,
`name` varchar(100) NOT NULL,
`latitude` float NOT NULL,
`longitude` float NOT NULL,
`elevation_ft` smallint DEFAULT NULL,
`continent_code` char(2) NOT NULL,
`country_code` char(2) NOT NULL,
`region_code` char(7) NOT NULL,
`municipality` varchar(60) NOT NULL,
`scheduled_service` tinyint(1) NOT NULL,
`gps_code` char(4) DEFAULT NULL,
`iata_code` char(3) DEFAULT NULL,
`local_code` varchar(7) DEFAULT NULL,
`home_url` varchar(128) DEFAULT NULL,
`wikipedia_url` varchar(128) DEFAULT NULL,
`keywords` varchar(300) DEFAULT NULL,
PRIMARY KEY (`airport_id`),
KEY `name` (`name`),
KEY `country_code` (`country_code`),
KEY `region_code` (`region_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.03 sec)
HINWEIS: Der Vorteil dieser Syntax: Sie können die Tabelle direkt aus der Ausgabe heraus neu anlegen.
Mehr dazu finden Sie im MySQL 8.0 Reference Manual.
Tabellenstruktur über den mysql-Client (2)
Mit dem SQL-Statement DESC lässt sich eine Tabelle ebenfalls beschreiben. Der Nachteil: Aus dieser Ausgabe können Sie die Tabelle nicht neu erstellen, und auch Index-Spalten und ‑Positionen lassen sich daraus nicht zuverlässig ableiten.
mysql> DESC airport;
+-------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+---------+-------+
| airport_id | int unsigned | NO | PRI | NULL | |
| ident | varchar(7) | NO | | NULL | |
| type | varchar(20) | NO | | NULL | |
| name | varchar(100) | NO | MUL | NULL | |
| latitude | float | NO | | NULL | |
| longitude | float | NO | | NULL | |
| elevation_ft | smallint | YES | | NULL | |
| continent_code | char(2) | NO | | NULL | |
| country_code | char(2) | NO | MUL | NULL | |
| region_code | char(7) | NO | MUL | NULL | |
| municipality | varchar(60) | NO | | NULL | |
| scheduled_service | tinyint(1) | NO | | NULL | |
| gps_code | char(4) | YES | | NULL | |
| iata_code | char(3) | YES | | NULL | |
| local_code | varchar(7) | YES | | NULL | |
| home_url | varchar(128) | YES | | NULL | |
| wikipedia_url | varchar(128) | YES | | NULL | |
| keywords | varchar(300) | YES | | NULL | |
+-------------------+--------------+------+-----+---------+-------+
18 rows in set (0.00 sec)
Mehr dazu finden Sie im MySQL 8.0 Reference Manual.
Tabellenstruktur über den mysqldump-Client
Oft ist es praktisch, die Definition aller Tabellen eines Schemas auf einmal abzurufen – oder gezielt nur die einer einzelnen Tabelle.
$ mysqldump --no-data --compact --set-gtid-purged=OFF \
-h${INSTANCE_ENDPOINT} -u${USER} -p${DBA_PASSWD} \
${DB_NAME} airport
Das Ergebnis ist mit der Ausgabe von SHOW CREATE TABLE identisch. Zusätzliche Kommentarzeilen rund um die Tabellenstruktur können Sie ignorieren.
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `airport` (
`airport_id` int unsigned NOT NULL,
`ident` varchar(7) NOT NULL,
`type` varchar(20) NOT NULL,
`name` varchar(100) NOT NULL,
`latitude` float NOT NULL,
`longitude` float NOT NULL,
`elevation_ft` smallint DEFAULT NULL,
`continent_code` char(2) NOT NULL,
`country_code` char(2) NOT NULL,
`region_code` char(7) NOT NULL,
`municipality` varchar(60) NOT NULL,
`scheduled_service` tinyint(1) NOT NULL,
`gps_code` char(4) DEFAULT NULL,
`iata_code` char(3) DEFAULT NULL,
`local_code` varchar(7) DEFAULT NULL,
`home_url` varchar(128) DEFAULT NULL,
`wikipedia_url` varchar(128) DEFAULT NULL,
`keywords` varchar(300) DEFAULT NULL,
PRIMARY KEY (`airport_id`),
KEY `name` (`name`),
KEY `country_code` (`country_code`),
KEY `region_code` (`region_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
Mehr dazu finden Sie im MySQL 8.0 Reference Manual.
Tabellenstruktur über das Information Schema
Eine ähnliche Ausgabe wie bei DESC lässt sich auch über die INFORMATION_SCHEMA-Tabellen erzeugen. Das folgende Beispiel ist nicht vollständig, da andere Optionen praxisgerechter sind. Ein vollständiges SQL-Statement müsste zusätzlich verschiedene Datentyp-Formate, Nullable-Eigenschaften, Zeichensätze, Kollationen und sämtliche Constraints abbilden.
> SELECT column_name, data_type, character_maximum_length, numeric_precision,
numeric_scale, datetime_precision, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = 'airport'
AND table_name = 'airport'
ORDER BY ordinal_position;
+-------------------+-----------+--------------------------+-------------------+---------------+--------------------+-------------+----------------+
| COLUMN_NAME | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | DATETIME_PRECISION | IS_NULLABLE | COLUMN_DEFAULT |
+-------------------+-----------+--------------------------+-------------------+---------------+--------------------+-------------+----------------+
| airport_id | int | NULL | 10 | 0 | NULL | NO | NULL |
| ident | varchar | 7 | NULL | NULL | NULL | NO | NULL |
| type | varchar | 20 | NULL | NULL | NULL | NO | NULL |
| name | varchar | 100 | NULL | NULL | NULL | NO | NULL |
| latitude | float | NULL | 12 | NULL | NULL | NO | NULL |
| longitude | float | NULL | 12 | NULL | NULL | NO | NULL |
| elevation_ft | smallint | NULL | 5 | 0 | NULL | YES | NULL |
| continent_code | char | 2 | NULL | NULL | NULL | NO | NULL |
| country_code | char | 2 | NULL | NULL | NULL | NO | NULL |
| region_code | char | 7 | NULL | NULL | NULL | NO | NULL |
| municipality | varchar | 60 | NULL | NULL | NULL | NO | NULL |
| scheduled_service | tinyint | NULL | 3 | 0 | NULL | NO | NULL |
| gps_code | char | 4 | NULL | NULL | NULL | YES | NULL |
| iata_code | char | 3 | NULL | NULL | NULL | YES | NULL |
| local_code | varchar | 7 | NULL | NULL | NULL | YES | NULL |
| home_url | varchar | 128 | NULL | NULL | NULL | YES | NULL |
| wikipedia_url | varchar | 128 | NULL | NULL | NULL | YES | NULL |
| keywords | varchar | 300 | NULL | NULL | NULL | YES | NULL |
+-------------------+-----------+--------------------------+-------------------+---------------+--------------------+-------------+----------------+
5\. Index-Kardinalität
Die Tabellenstruktur zeigt zwar die definierten Indizes, doch der QEP wird zusätzlich durch Statistiken zu den indizierten Spalten beeinflusst. Eine gute Annäherung liefert ein Blick auf die Kardinalität jeder Spalte eines Index per SHOW INDEXES.
> SHOW INDEXES FROM airport;
+---------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| airport | 0 | PRIMARY | 1 | airport_id | A | 86733 | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 1 | name | 1 | name | A | 70534 | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 1 | country_code | 1 | country_code | A | 214 | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 1 | region_code | 1 | region_code | A | 3334 | NULL | NULL | | BTREE | | | YES | NULL |
+---------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.01 sec)
HINWEIS: Je höher der Wert in der Spalte "Cardinality", desto besser für ein SELECT-Statement, das gezielt einzelne Zeilen treffen soll.
Mit dem Schlüsselwort EXTENDED erhalten Sie zusätzliche Informationen, die für Endanwender allerdings kaum praxisrelevant sind.
> SHOW EXTENDED INDEXES FROM airport;
+---------+------------+--------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+--------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| airport | 0 | PRIMARY | 1 | airport_id | A | 86733 | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 0 | PRIMARY | 2 | DB_TRX_ID | A | NULL | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 0 | PRIMARY | 3 | DB_ROLL_PTR | A | NULL | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 0 | PRIMARY | 4 | ident | A | NULL | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 0 | PRIMARY | 5 | type | A | NULL | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 0 | PRIMARY | 6 | name | A | NULL | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 0 | PRIMARY | 7 | latitude | A | NULL | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 0 | PRIMARY | 8 | longitude | A | NULL | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 0 | PRIMARY | 9 | elevation_ft | A | NULL | NULL | NULL | YES | BTREE | | | YES | NULL |
| airport | 0 | PRIMARY | 10 | continent_code | A | NULL | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 0 | PRIMARY | 11 | country_code | A | NULL | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 0 | PRIMARY | 12 | region_code | A | NULL | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 0 | PRIMARY | 13 | municipality | A | NULL | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 0 | PRIMARY | 14 | scheduled_service | A | NULL | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 0 | PRIMARY | 15 | gps_code | A | NULL | NULL | NULL | YES | BTREE | | | YES | NULL |
| airport | 0 | PRIMARY | 16 | iata_code | A | NULL | NULL | NULL | YES | BTREE | | | YES | NULL |
| airport | 0 | PRIMARY | 17 | local_code | A | NULL | NULL | NULL | YES | BTREE | | | YES | NULL |
| airport | 0 | PRIMARY | 18 | home_url | A | NULL | NULL | NULL | YES | BTREE | | | YES | NULL |
| airport | 0 | PRIMARY | 19 | wikipedia_url | A | NULL | NULL | NULL | YES | BTREE | | | YES | NULL |
| airport | 0 | PRIMARY | 20 | keywords | A | NULL | NULL | NULL | YES | BTREE | | | YES | NULL |
| airport | 1 | name | 1 | name | A | 70534 | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 1 | name | 2 | airport_id | A | NULL | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 1 | country_code | 1 | country_code | A | 214 | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 1 | country_code | 2 | airport_id | A | NULL | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 1 | region_code | 1 | region_code | A | 3334 | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 1 | region_code | 2 | airport_id | A | NULL | NULL | NULL | | BTREE | | | YES | NULL |
+---------+------------+--------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
26 rows in set (0.00 sec)
Mehr dazu finden Sie im MySQL 8.0 Reference Manual.
6\. Kosten der Query-Ausführung
Der Befehl EXPLAIN ANALYZE liefert detaillierte Kosteninformationen für jeden Schritt der Query-Ausführung. Damit identifizieren Sie gezielt die Schritte, die in puncto Ausführungszeit und verarbeiteten Zeilen besonders teuer sind.
mysql > EXPLAIN ANALYZE SELECT ...
-> Limit: 10 row(s) (actual time=350.043..350.045 rows=10 loops=1)
-> Sort: cnt DESC, limit input to 10 row(s) per chunk (actual time=350.043..350.043 rows=10 loops=1)
-> Table scan on <temporary> (actual time=0.003..0.049 rows=245 loops=1)
-> Aggregate using temporary table (actual time=349.935..349.994 rows=245 loops=1)
-> Nested loop inner join (cost=112.60 rows=248) (actual time=0.083..257.689 rows=76475 loops=1)
-> Table scan on c (cost=25.80 rows=248) (actual time=0.051..0.225 rows=248 loops=1)
-> Index lookup on a using country_code (country_code=c.country_code) (cost=0.25 rows=1) (actual time=0.043..0.993 rows=308 loops=248)
Mehr dazu finden Sie im MySQL 8.0 Reference Manual.
7\. Bewertung durch den Query-Optimizer
Bevor MySQL eine SQL-Query ausführt, wird sie zunächst geparst. Anschließend prüft MySQL, ob der ausführende Benutzer die nötigen Berechtigungen für die betroffenen Tabellen und Spalten besitzt. Ist Query-Caching aktiv, wird zusätzlich nach einem passenden Treffer gesucht – im Erfolgsfall werden die Ergebnisse direkt zurückgeliefert. Danach ermittelt der Query-Optimizer den optimalen Ausführungspfad: Er bewertet verschiedene Varianten und berücksichtigt die Fähigkeiten der Storage-Engine. Mit dem Optimizer Trace erfassen Sie detailliert, welche Schritte der Optimizer geprüft hat und auf welchen Statistiken seine kostenbasierte Entscheidung beruht.
HINWEIS: Diese Informationen sind hochgradig technisch und sollten ausschließlich für spezialisierte Auswertungen durch erfahrene Fachkräfte erhoben werden.
> SET SESSION optimizer_trace="enabled=on";
> SELECT ...
> SELECT TRACE FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
{
"steps": [\
{\
"join_preparation": {\
...\
},\
{\
"join_optimization": {\
"select#": 1,\
"steps": [\
...\
},\
{\
"rows_estimation": [\
{\
"table": "`airport` `a`",\
"table_scan": {\
"rows": 86733,\
"cost": 296.25\
}\
},\
{\
"table": "`country` `c`",\
"table_scan": {\
"rows": 248,\
"cost": 1\
}\
}\
]\
},\
...\
},\
{\
"join_execution": {\
"select#": 1,\
"steps": [\
{\
"temp_table_aggregate": {\
"select#": 1,\
"steps": [\
{\
"creating_tmp_table": {\
"tmp_table_info": {\
"in_plan_at_position": 2,\
"columns": 3,\
"row_length": 218,\
"key_length": 210,\
"unique_constraint": false,\
"makes_grouped_rows": true,\
"cannot_insert_duplicates": false,\
"location": "TempTable"\
}\
}\
}\
]\
}\
},\
{\
"sorting_table": "<temporary>",\
"filesort_information": [\
{\
"direction": "desc",\
"expression": "`cnt`"\
}\
],\
"filesort_priority_queue_optimization": {\
"limit": 10,\
"chosen": true\
},\
"filesort_execution": [\
],\
"filesort_summary": {\
"memory_available": 262144,\
"key_size": 8,\
"row_size": 226,\
"max_rows_per_buffer": 11,\
"num_rows_estimate": 18446744073709551615,\
"num_rows_found": 245,\
"num_initial_chunks_spilled_to_disk": 0,\
"peak_memory_used": 2574,\
"sort_algorithm": "std::sort",\
"unpacked_addon_fields": "using_priority_queue",\
"sort_mode": "<fixed_sort_key, additional_fields>"\
}\
}\
]\
}\
}\
]\
}\
```\
HINWEIS: Die obige JSON-Ausgabe ist gekürzt, da sie mehrere Seiten umfasst. Eine vollständige Version finden Sie [hier](https://gist.github.com/ronaldbradford/14f3e253342da7c7ce64756976b690f7).\
Mehr dazu finden Sie im [MySQL 8.0 Reference Manual](https://dev.mysql.com/doc/refman/8.0/en/information-schema-optimizer-trace-table.html).\
8\. MySQL-Statuswerte\
MySQL stellt intern über 300 Metriken bereit, die sich auf GLOBAL- oder SESSION-Ebene auswerten lassen. Mit der passenden Statusvariablen lassen sich interne Vorgänge während der Query in den verschiedenen Ausführungsphasen prüfen – etwa Indexnutzung, Where-Qualifikation, Sortierung und Gruppierung. Im folgenden einfachen Beispiel, in dem eine interne Temp-Tabelle verwendet wurde, ist die Information besonders relevant, dass diese auf die Festplatte geschrieben wurde – ein Hinweis darauf, ob sich die Ausführungszeit über verschiedene Stellschrauben senken lässt.
HINWEIS: Diese Informationen sind hochgradig technisch und sollten nur durch erfahrene Fachkräfte erhoben werden, die genau wissen, welche Metriken relevant sind und welche nicht. Schon das Erfassen selbst beeinflusst manche Metriken.\
mysql> SHOW SESSION STATUS LIKE 'Created_tmp%tables';\
+-------------------------+-------+\
| Variable_name | Value |\
+-------------------------+-------+\
| Created_tmp_disk_tables | 0 |\
| Created_tmp_tables | 4 |\
+-------------------------+-------+\
mysql> SELECT ...\
mysql> SHOW SESSION STATUS LIKE 'Created_tmp%tables';\
+-------------------------+-------+\
| Variable_name | Value |\
+-------------------------+-------+\
| Created_tmp_disk_tables | 1 |\
| Created_tmp_tables | 5 |\
+-------------------------+-------+\
```\
Mehr dazu finden Sie im [MySQL 8.0 Reference Manual](https://dev.mysql.com/doc/refman/8.0/en/show-status.html).\
9\. Query-Profiling\
Wenn Sie Profiling für eine Query aktivieren, lassen sich die einzelnen internen Schritte nachvollziehen und mit dem MySQL-Quellcode abgleichen. In der folgenden Ausgabe entfällt der Großteil der Ausführungszeit auf einen einzelnen Schritt – Zeile 14 mit 0,234060 Sekunden. Die meisten Queries folgen einer ähnlichen Verteilung; bei komplexeren Queries kommen weitere Schritte hinzu.
HINWEIS: Diese Informationen sind hochgradig technisch und sollten ausschließlich für spezialisierte Auswertungen durch erfahrene Fachkräfte erhoben werden. Die Analyse selbst wird hier nicht weiter ausgeführt.\
mysql > SET @@profiling=1;\
mysql > SELECT ...\
mysql > SET @@profiling=0;\
mysql > SHOW PROFILES;\
mysql> SELECT * FROM INFORMATION_SCHEMA.PROFILING WHERE query_id=1;\
```\
```\
+----------+-----+--------------------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+--------------------------------+----------------------+-------------+\
| QUERY_ID | SEQ | STATE | DURATION | CPU_USER | CPU_SYSTEM | CONTEXT_VOLUNTARY | CONTEXT_INVOLUNTARY | BLOCK_OPS_IN | BLOCK_OPS_OUT | MESSAGES_SENT | MESSAGES_RECEIVED | PAGE_FAULTS_MAJOR | PAGE_FAULTS_MINOR | SWAPS | SOURCE_FUNCTION | SOURCE_FILE | SOURCE_LINE |\
+----------+-----+--------------------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+--------------------------------+----------------------+-------------+\
| 9 | 2 | starting | 0.000125 | 0.000130 | 0.000021 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL |\
| 9 | 3 | Executing hook on transaction | 0.000010 | 0.000009 | 0.000002 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | launch_hook_trans_begin | rpl_handler.cc | 1376 |\
| 9 | 4 | starting | 0.000011 | 0.000010 | 0.000001 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | launch_hook_trans_begin | rpl_handler.cc | 1378 |\
| 9 | 5 | checking permissions | 0.000005 | 0.000004 | 0.000001 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | check_access | sql_authorization.cc | 2303 |\
| 9 | 6 | checking permissions | 0.000005 | 0.000004 | 0.000001 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | check_access | sql_authorization.cc | 2303 |\
| 9 | 7 | Opening tables | 0.000180 | 0.000156 | 0.000025 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | open_tables | sql_base.cc | 5803 |\
| 9 | 8 | init | 0.000008 | 0.000006 | 0.000001 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | execute | sql_select.cc | 570 |\
| 9 | 9 | System lock | 0.000009 | 0.000007 | 0.000001 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_lock_tables | lock.cc | 331 |\
| 9 | 10 | optimizing | 0.000012 | 0.000011 | 0.000002 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 344 |\
| 9 | 11 | statistics | 0.000027 | 0.000023 | 0.000003 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 663 |\
| 9 | 12 | preparing | 0.000017 | 0.000014 | 0.000003 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 747 |\
| 9 | 13 | Creating tmp table | 0.000047 | 0.000041 | 0.000006 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | create_intermediate_table | sql_executor.cc | 195 |\
| 9 | 14 | executing | 0.234060 | 0.202675 | 0.000000 | 65 | 3 | 0 | 0 | 0 | 0 | 0 | 1222 | 0 | ExecuteIteratorQuery | sql_union.cc | 1130 |\
| 9 | 15 | end | 0.000022 | 0.000020 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | execute | sql_select.cc | 603 |\
| 9 | 16 | query end | 0.000009 | 0.000009 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_execute_command_internal | sql_parse.cc | 6372 |\
| 9 | 17 | waiting for handler commit | 0.000231 | 0.000231 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ha_commit_trans | handler.cc | 1707 |\
| 9 | 18 | closing tables | 0.000018 | 0.000021 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | mysql_execute_command_internal | sql_parse.cc | 6450 |\
| 9 | 19 | freeing items | 0.000018 | 0.000015 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | finish_dispatch_sql_command | sql_parse.cc | 6971 |\
| 9 | 20 | logging slow query | 0.000150 | 0.000150 | 0.000000 | 0 | 0 | 0 | 8 | 0 | 0 | 0 | 4 | 0 | log_slow_do | log.cc | 1784 |\
| 9 | 21 | cleaning up | 0.000010 | 0.000010 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | finish_dispatch_command | sql_parse.cc | 3409 |\
+----------+-----+--------------------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+--------------------------------+----------------------+-------------+\
20 rows in set, 1 warning (0.00 sec)\
```\
Mehr dazu finden Sie im [MySQL 8.0 Reference Manual](https://dev.mysql.com/doc/refman/8.0/en/performance-schema-query-profiling.html).\
10\. Processlist\
Während der Ausführung eines SQL-Statements liefert die processlist wertvolle Hinweise – insbesondere zu State und Time einer länger laufenden Query. Sie zeigt außerdem, wie sich das Statement auf andere parallele Statements auswirkt, etwa durch Locking. Wie Sie die processlist über verschiedene Wege erfassen, lesen Sie ausführlich unter Capturing running SQL statements.\
Hinweis zur Genauigkeit der Werte\
Viele der hier vorgestellten Analyse-Tools liefern unter unterschiedlichen Bedingungen abweichende Ergebnisse. Manche Werte sollten bei jeder Ausführung exakt gleich bleiben, etwa die Anzahl der zurückgegebenen Zeilen oder die Tabellenstruktur.
Andere Ausgaben sollten bei wiederholten Ausführungen weitgehend konsistent bleiben – der QEP enthält etwa dieselbe Anzahl an Schritten, einzelne Spalten können aber unterschiedliche Werte aufweisen. Ein QEP kann sich abhängig von verwendeten Konstanten im SQL-Statement, einer anderen MySQL-Version oder Änderungen an der MySQL-Konfiguration ändern.
Andere Werte sind selten identisch: Hochpräzise Ausführungszeit, Query-Kosten oder einzelne Profiling-Schritte sollten gemittelt werden; ein passender Perzentilwert hilft dabei, vergleichbare Ausführungen zu identifizieren.\
Fazit\
Es gibt zahlreiche native Wege, mit denen sich über die MySQL-Client-Tools begleitende Informationen zur Ausführung von SQL-Statements erfassen lassen. Für die Analyse und Optimierung von SQL-Statements sind diese Informationen unverzichtbar.
Dieser Artikel knüpft an Capturing running SQL statements an, der die verschiedenen Techniken zum Erfassen vollständiger SQL-Statements im Detail beschreibt.\
Anhang\
Verwendete Beispieldaten\
Für diesen Artikel kommt der Airport-Datensatz zum Einsatz, der unter https://github.com/ronaldbradford/data/tree/main/mysql-data/airport verfügbar ist.\
Verwendetes Beispiel-SQL\
Für jede Erfassungsmethode wurde das folgende SQL-Statement verwendet.\
SELECT a.country_code, c.name, COUNT(*) AS cnt\
FROM airport a\
INNER JOIN country c USING (country_code)\
GROUP BY country_code, name ORDER BY 3 DESC LIMIT 10;\
```\
### Dieses SQL-Statement liefert ein Ergebnis wie das folgende:\
```\
+--------------+----------------+-------+\
| country_code | name | cnt |\
+--------------+----------------+-------+\
| US | United States | 30581 |\
| BR | Brazil | 6849 |\
| JP | Japan | 3430 |\
| CA | Canada | 3075 |\
| AU | Australia | 2576 |\
| MX | Mexico | 2288 |\
| RU | Russia | 1556 |\
| KR | South Korea | 1400 |\
| GB | United Kingdom | 1398 |\
| DE | Germany | 1042 |\
+--------------+----------------+-------+\
10 rows in set (0.47 sec)\
```\
HINWEIS: Die tatsächliche Anzahl der Flughäfen sowie die Reihenfolge der Länder können je nach Zeitpunkt des Datenabrufs variieren.\
Hinweis zu Backquotes (\`)\
In vielen Beispielen sehen Sie das Backquote-Zeichen (`) rund um Objektnamen. Es stammt aus der Möglichkeit, reservierte Wörter zu verwenden – diese müssen in Backquotes eingeschlossen werden. Die Tools setzen diese zusätzliche Syntax allerdings nicht nur dort, wo sie nötig wäre, sondern bei allen Tabellen- und Spaltenobjekten – das erschwert die Lesbarkeit.\