Tool zur Optimierung der MySQL-Datenbankleistung – Schlüsselwort erklären

Einführung in das EXPLAIN-Tool

Mit dem Schlüsselwort EXPLAIN kann die Ausführung von SQL-Anweisungen durch den Optimierer simuliert und die Leistungsengpässe von Abfrageanweisungen oder -strukturen analysiert werden. Durch das Hinzufügen des Schlüsselworts „explaination“ vor der SELECT-Anweisung setzt MySQL eine Markierung für die Abfrage und die Ausführung der Abfrage gibt Informationen zum Ausführungsplan zurück, anstatt SQL auszuführen.

Beispiel für eine Erklärungsanalyse

-- actor建表语句：CREATE TABLE `actor` (  `id` int(11) NOT NULL,  `name` varchar(45) DEFAULT NULL,  `update_time` 
datetime DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

-- film建表语句：CREATE TABLE `film` (  `id` int(11) NOT NULL,  `name` varchar(10) NOT NULL,
PRIMARY KEY (`id`),  KEY `idx_name` (`name`))ENGINE=InnoDB DEFAULT CHARSET=utf8

-- film_actor建表语句：CREATE TABLE `film_actor` (  `id` int(11) NOT NULL,  `film_id` int(11) NOT NULL,  `actor_id` 
int(11) NOT NULL,  `remark` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`),KEY `idx_film_actor_id` (`film_id`,`actor_id`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Ausführungserklärung:

explain select * from actor;

Wenn die Select-Anweisung das Ausführungsergebnis zurückgibt, Das Hinzufügen von EXPLAIN vor der SELECT-Anweisung gibt die Ausführungs-SQL dieser Abfrageanweisung zurück.

Zwei Varianten von EXPLAIN

1. EXPLAIN Extended

bietet einige zusätzliche Abfrageoptimierungen auf der Grundlage von EXPLAIN-Informationen . Verwenden Sie dann den Befehl „show warnings“, um die optimierte Abfrageanweisung abzurufen und zu sehen, was der Optimierer optimiert hat. Es gibt auch eine gefilterte Spalte, bei der es sich um einen halben Verhältniswert rows*filtered/100 handelt, der die Anzahl der Zeilen schätzen kann, die mit der vorherigen Tabelle in der Erklärung verbunden werden (die vorherige Tabelle bedeutet, dass der ID-Wert in der Erklärung ist). kleiner als der ID-Wert der aktuellen Tabellenoberfläche).

explain EXTENDED select * from actor where id = 1;

2. EXPLAIN-Partitionen

hat ein weiteres Partitionsfeld als EXPLAIN. Wenn die Abfrage auf einer Partitionstabelle basiert, Es werden die Partitionen angezeigt, auf die die Abfrage zugreift.

Spalte in Explain

ID-Spalte

Die Nummer der ID-Spalte ist die Seriennummer der Auswahl Es gibt mehrere Selects. Es gibt mehrere IDs, und die Reihenfolge der IDs erhöht sich mit der Reihenfolge, in der Select angezeigt wird.
Je größer die ID, desto höher die Ausführungspriorität. Wenn die ID gleich ist, wird sie von oben nach unten ausgeführt. Wenn die ID NULL ist, wird sie zuletzt ausgeführt.

explain select (select 1 from actor where id = 1) from (select * from filmwhere id = 1) der;

Spalte Typ auswählen

Typ auswählen gibt an, ob es sich bei der entsprechenden Zeile um eine einfache oder komplexe Abfrage handelt.
einfach: einfache Abfrage. Die Abfrage enthält keine Unterabfragen und Unions.

explain select * from film where id=1

primary: Die äußerste Auswahl in einer komplexen Abfrage
subquery: Die in der Auswahl enthaltene Unterabfrage (nicht in der from-Klausel)
derived: Unterabfrage in der from-Klausel enthalten. MySQL speichert die Ergebnisse in einer temporären Tabelle, auch abgeleitete Tabelle

explain select (select 1 from actor where id = 1) from (select * from filmwhere id = 1) der;

union: Sellct nach dem Union-Schlüsselwort genannt.

EXPLAIN select 1 union all select 1;

Tabellenspalte

这一列表示explain的一行正在访问哪个表。
当from子句中有子查询时，table列是格式，表示当前查询依赖id=N的查询，于是先执行id=N的查询。
当有union时，UNION RESULT的table列的值为，1和2表示参与union的select行id。

type列

这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行对应的大概范围。
依次从最优到最差的分别为：system>const>eq_ref>ref>range>index>All
一般来说，得保证查询达到range级别，最好达到ref。
NULL：MySQL能够在优化阶段分解查询语句，在执行阶段用不着在访问表或索引。例如：在索引列中选取最小值，可以单独查找索引来完成，不需在执行时访问表

EXPLAIN select min(id) from film;

const、system：mysql能对查询的某部分进行优化并将其转换成一个常量（可看成是show warnings的结果）。用于primay key或unique key的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速读较快。system 是const的特例，表中只有一行元素匹配时为system。

EXPLAIN select * from (select * from film where id= 1) as tmp;

eq_ref：primay key或 unique key索引的所有部分被连接使用，最多只会返回一条符合条件的记录。这可能是const之外最好的联接类型，简单的select查询不会出现这种type。

EXPLAIN select * from (select * from film where id= 1) as tmp;

ref：相比eq_ref，不适用唯一索引，而是使用普通索引或者唯一索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。
简单select查询，name是普通索引（非主键索引或唯一索引）

EXPLAIN select * from film where name='film1';

关联表查询，idx_film_actor_id是film_id和actor_id的联合索引，这里使用到了film_actor的左边前缀film_id部分。

EXPLAIN select film_id from film LEFT JOIN film_actor on film.id = film_actor.film_id;

range：范围扫描通常出现在in(), between,>,=等操作中。使用一个索引来检索给定范围的行。

EXPLAIN select * from actor WHERE id >1;

index：扫描全表索引，通常比All快一些

EXPLAIN select * from film;

all：即全表扫描，意味着MySQL需要从头到尾去查找所需要的行。这种情况下需要增加索引来进行优化。

explain select * from actor;

possible_keys列

这一列显示select可能会使用哪些查询来查找。
explain时可能会出现possible_keys有列，而key显示为NULL的情况，这种情况是因为表中的数据不多，MySQL认为索引对此查询帮助不大，选择了全表扫描。
如果该列为NULL，则没有相关的索引。这种情况下，可以通过检查where子句看是否可以创造一个适当的索引来提高查询性能，然后用explain查看效果。

EXPLAIN SELECT * from film_actor where film_id =1;

key列

这一列显示MySQL实际采用哪个索引对该表的访问。
如果没有使用索引，则改列为NULL。如果想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用force index、 ignore index。

key_len列

这一列显示了mysql在索引里使用的字节数，通过这个值可以估算出具体使用了索引中的哪些列。

EXPLAIN SELECT * from film_actor where film_id =1;

film_actor的联合索引idx_film_actor_id由film_id和actor_id两个id列组成，并且每个int是4字节。通过结果中的key_len=4可推断出查询使用了第一个列：film_id列来执行索引查找。

ken_len计算规则如下：

字符串
char(n)：n字节长度
varchar(n)：n字节存储字符串长度，如果是utf-8， 则长度是3n+2

数值类型
tinyint：1字节
smallint：2字节
int：4字节
bigint：8字节

时间类型
date：3字节
timestamp：4字节
datetime：8字节

如果字段允许为NULL，需要1字节记录是否为NULL
索引最大长度是768字节，当字符串过长时，MySQL会做一个类似做前缀索引的处理，将前半部分的字符串提取出来做索引。

ref列

这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有： const(常量)，字段名等。一般是查询条件或关联条件中等号右边的值，如果是常量那么ref列是const，非常量的话ref列就是字段名。

EXPLAIN SELECT * from film_actor where film_id =1;

row列

这一列是mysql估计要读取并检测的行数，注意这个不是结果集的行数。

Extra列

这一列是额外信息。

Using index：使用覆盖索引（结果集的字段是索引，即select后的film_id）

explain select film_id from film_actor where film_id=1;

Using index condition：查询的列不完全被索引覆盖，where条件中是一个前导的范围

explain select * from film_actor where film_id > 1;

Using where：使用where语句来处理结果，查询的列未被索引覆盖

explain select * from actor where name ='a'

Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般要进行优化，首先要想到是索引优化。

explain select DISTINCT name from actor;

actor.name没有索引，此时创建了临时表来处理distinct。

explain select DISTINCT name from film;

file.name建立了普通索引，此时查询时Extra是Using index，没有用到临时表。

Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的。

explain select * from actor order by name;

actor.name未创建索引，会浏览acotr整个表，保存排序关键字name和对应id，然后排序name并检索行记录。

explain select * from film order by name;

film.name建立了idx_name索引，此时查询时extra是Using index。

select tables optimized away：使用某些聚合函数（比如：max、min）来访问存在索引的某个字段

explain select min(id) from film ;

Interessierte Schüler können die chinesische PHP-Website besuchen, um weitere verwandte Inhalte zu erfahren: MySQL-Video-Tutorial

Das obige ist der detaillierte Inhalt vonTool zur Optimierung der MySQL-Datenbankleistung – Schlüsselwort erklären. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!