MySQL-Leistungsoptimierung – Erläutern Sie die Einführung in die Verwendung

---

Einführung

MySQL bietet einen EXPLAIN-Befehl, der die SELECT-Anweisung analysieren und die detaillierten Informationen der SELECT-Ausführung ausgeben kann, damit Entwickler sie optimieren können.
Der EXPLAIN-Befehl ist Sehr einfach zu verwenden. Fügen Sie einfach Explain vor der SELECT-Anweisung hinzu, zum Beispiel:

EXPLAIN SELECT * from user_info WHERE  id < 300;

Vorbereitung

Um die Demonstration der Verwendung von EXPLAIN zu erleichtern, müssen wir zunächst zwei erstellen Eine Tabelle zum Testen und Hinzufügen entsprechender Daten:

CREATE TABLE `user_info` (
  `id`   BIGINT(20)  NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL DEFAULT '',
  `age`  INT(11)              DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name_index` (`name`)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8

INSERT INTO user_info (name, age) VALUES ('xys', 20);
INSERT INTO user_info (name, age) VALUES ('a', 21);
INSERT INTO user_info (name, age) VALUES ('b', 23);
INSERT INTO user_info (name, age) VALUES ('c', 50);
INSERT INTO user_info (name, age) VALUES ('d', 15);
INSERT INTO user_info (name, age) VALUES ('e', 20);
INSERT INTO user_info (name, age) VALUES ('f', 21);
INSERT INTO user_info (name, age) VALUES ('g', 23);
INSERT INTO user_info (name, age) VALUES ('h', 50);
INSERT INTO user_info (name, age) VALUES ('i', 15);

CREATE TABLE `order_info` (
  `id`           BIGINT(20)  NOT NULL AUTO_INCREMENT,
  `user_id`      BIGINT(20)           DEFAULT NULL,
  `product_name` VARCHAR(50) NOT NULL DEFAULT '',
  `productor`    VARCHAR(30)          DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_product_detail_index` (`user_id`, `product_name`, `productor`)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8

INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p1', 'WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p2', 'WL');
INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p1', 'DX');
INSERT INTO order_info (user_id, product_name, productor) VALUES (2, 'p1', 'WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (2, 'p5', 'WL');
INSERT INTO order_info (user_id, product_name, productor) VALUES (3, 'p3', 'MA');
INSERT INTO order_info (user_id, product_name, productor) VALUES (4, 'p1', 'WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (6, 'p1', 'WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (9, 'p8', 'TE');

EXPLAIN-Ausgabeformat

Der Ausgabeinhalt des EXPLAIN-Befehls sieht ungefähr wie folgt aus:

mysql> explain select * from user_info where id = 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_info
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

Die Bedeutung jeder Spalte ist wie folgt:

• id: Bezeichner der SELECT-Abfrage. Jedem SELECT wird automatisch ein eindeutiger Bezeichner zugewiesen.

• select_type: Der Typ der SELECT-Abfrage.

• Tabelle: Welche Tabelle abgefragt wird

• Partitionen: Passende Partitionen

• Typ: Join-Typ

• possible_keys: mögliche Indizes, die in dieser Abfrage verwendet werden

• Schlüssel: genaue Werte in diesem Abfrage-Index verwendet.

• Ref: Welches Feld oder welche Konstante mit Schlüssel verwendet wird

• Zeilen: Zeigt an, wie viele Zeilen von dieser Abfrage gescannt wurden eine Schätzung.

• gefiltert: Gibt den Prozentsatz der durch diese Abfragebedingung gefilterten Daten an

• extra: Zusätzliche Informationen

Als nächstes werfen wir einen Blick auf die wichtigeren Felder.

select_type

select_type stellt den Typ der Abfrage dar und wird häufig verwendet. Die Werte sind:

• SIMPLE, was bedeutet, dass diese Abfrage keine UNION-Abfrage oder Unterabfrage enthält.

• PRIMARY, was bedeutet, dass diese Abfrage die äußerste Abfrage ist

• UNION, was angibt, dass diese Abfrage die zweite oder nachfolgende Abfrage von UNION ist.

• DEPENDENT UNION, die zweite oder nachfolgende Abfrage von UNION. Die Abfrageanweisung hängt davon ab die externe Abfrage

• UNION RESULT, das Ergebnis von UNION

• SUBQUERY, das erste SELECT in der Unterabfrage

• ABHÄNGIGE UNTERABFRAGE: Das erste SELECT in der Unterabfrage hängt von der äußeren Abfrage ab. Das heißt, die Unterabfrage hängt vom Ergebnis der äußeren Abfrage ab.

Der häufigste Abfragetyp sollte

sein. Wenn unsere Abfrage beispielsweise keine Unterabfrage und keine UNION-Abfrage hat, ist sie normalerweise vom Typ SIMPLE, zum Beispiel: SIMPLE

mysql> explain select * from user_info where id = 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_info
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

Wenn wir die UNION-Abfrage verwenden , die Ergebnisausgabe von EXPLAIN ähnelt der folgenden:

mysql> EXPLAIN (SELECT * FROM user_info  WHERE id IN (1, 2, 3))
    -> UNION
    -> (SELECT * FROM user_info WHERE id IN (3, 4, 5));
+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+
| id | select_type  | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra           |
+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+
|  1 | PRIMARY      | user_info  | NULL       | range | PRIMARY       | PRIMARY | 8       | NULL |    3 |   100.00 | Using where     |
|  2 | UNION        | user_info  | NULL       | range | PRIMARY       | PRIMARY | 8       | NULL |    3 |   100.00 | Using where     |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL   | NULL          | NULL    | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+
3 rows in set, 1 warning (0.00 sec)

Tabelle

stellt die Tabelle oder abgeleitete Tabelle dar, die an der Abfrage beteiligt ist

Typ

ist wichtiger, es bietet eine wichtige Grundlage für die Beurteilung, ob die Abfrage effizient ist. Mithilfe des Felds

beurteilen wir, ob die Abfrage type oder type usw. ist. 全表扫描索引扫描Typ allgemeiner Typ

Typ häufig verwendete Werte sind:

System: Es gibt nur ein Datenelement in der Tabelle. Dieser Typ ist ein besonderer
• Typ.

  const

const: Äquivalenter Abfragescan für Primärschlüssel oder eindeutigen Index, die höchstens eine Datenzeile zurückgibt, da sie nur einmal liest >Zum Beispiel verwendet die folgende Abfrage den Primärschlüssel Index, daher ist
• vom Typ

  
  typeconst

  eq_ref: Dieser Typ erscheint normalerweise in Join-Abfragen von mehreren Tabellen geben an, dass für jedes Ergebnis der vorherigen Tabelle nur eine Ergebniszeile in der letzteren Tabelle übereinstimmen kann. Die Vergleichsoperation der Abfrage ist normalerweise

  mysql> explain select * from user_info where id = 2\G
  *************************** 1. row ***************************
          id: 1
  select_type: SIMPLE
       table: user_info
  partitions: NULL
        type: const
  possible_keys: PRIMARY
         key: PRIMARY
     key_len: 8
         ref: const
        rows: 1
    filtered: 100.00
       Extra: NULL
  1 row in set, 1 warning (0.00 sec)

  , was eine höhere Abfrageeffizienz aufweist 🎜>

• ref: Dieser Typ erscheint normalerweise in Multi-Table-Join-Abfragen, für nicht eindeutige oder nicht primäre Schlüsselindizes oder Abfragen, die =-Regelindizes

  verwenden. Im folgenden Beispiel wird ein Abfragetyp

  mysql> EXPLAIN SELECT * FROM user_info, order_info WHERE user_info.id = order_info.user_id\G
  *************************** 1. row ***************************
          id: 1
  select_type: SIMPLE
       table: order_info
  partitions: NULL
        type: index
  possible_keys: user_product_detail_index
         key: user_product_detail_index
     key_len: 314
         ref: NULL
        rows: 9
    filtered: 100.00
       Extra: Using where; Using index
  *************************** 2. row ***************************
          id: 1
  select_type: SIMPLE
       table: user_info
  partitions: NULL
        type: eq_ref
  possible_keys: PRIMARY
         key: PRIMARY
     key_len: 8
         ref: test.order_info.user_id
        rows: 1
    filtered: 100.00
       Extra: NULL
  2 rows in set, 1 warning (0.00 sec)

  verwendet:

• 最左前缀
  Bereich: Gibt die Verwendung einer Indexbereichsabfrage an, um einige Datensätze in der Tabelle über den Index abzurufen Feldbereich. Dieser Typ erscheint normalerweise in den Operationen =, a8093152e673feb7aba1828c43532094, >, >=, 48e2311e8bb2f94e3e6a9e3627d15361, BETWEEN, IN().refWenn

  ist

  mysql> EXPLAIN SELECT * FROM user_info, order_info WHERE user_info.id = order_info.user_id AND order_info.user_id = 5\G
  *************************** 1. row ***************************
          id: 1
  select_type: SIMPLE
       table: user_info
  partitions: NULL
        type: const
  possible_keys: PRIMARY
         key: PRIMARY
     key_len: 8
         ref: const
        rows: 1
    filtered: 100.00
       Extra: NULL
  *************************** 2. row ***************************
          id: 1
  select_type: SIMPLE
       table: order_info
  partitions: NULL
        type: ref
  possible_keys: user_product_detail_index
         key: user_product_detail_index
     key_len: 9
         ref: const
        rows: 1
    filtered: 100.00
       Extra: Using index
  2 rows in set, 1 warning (0.01 sec)

  , dann ist das von EXPLAIN ausgegebene Feld
NULL und
• Das Feld ist der längste der in dieser Abfrage verwendeten Indizes.

  Das folgende Beispiel ist beispielsweise eine Bereichsabfrage:
  typerangerefkey_lenIndex: bedeutet, dass der vollständige Index-Scan (vollständiger Index-Scan) dem ALL-Typ ähnelt, mit der Ausnahme, dass der ALL-Typ ein vollständiger Tabellenscan ist, während der Indextyp nur scannt Alle Indizes ohne Scannen der Daten.
  Der Indextyp wird normalerweise angezeigt, wenn: die abzufragenden Daten direkt im Indexbaum abgerufen werden können, ohne die Daten zu scannen. In diesem Fall wird das Feld Extra angezeigt >.

  mysql> EXPLAIN SELECT *
   ->         FROM user_info
   ->         WHERE id BETWEEN 2 AND 8 \G
  *************************** 1. row ***************************
          id: 1
  select_type: SIMPLE
       table: user_info
  partitions: NULL
        type: range
  possible_keys: PRIMARY
         key: PRIMARY
     key_len: 8
         ref: NULL
        rows: 7
    filtered: 100.00
       Extra: Using where
  1 row in set, 1 warning (0.00 sec)

• Zum Beispiel:

  
  Using indexIm obigen Beispiel ist das von uns abgefragte Namensfeld zufällig ein Index, sodass wir die Abfrageanforderungen erfüllen können Erhalten Sie die Daten direkt aus dem Index, ohne die Daten in der Tabelle abzufragen. Geben Sie daher in diesem Fall den Wert von ist

  ein, und der Wert von Extra ist
.

• ALL: 表示全表扫描, 这个类型的查询是性能最差的查询之一. 通常来说, 我们的查询不应该出现 ALL 类型的查询, 因为这样的查询在数据量大的情况下, 对数据库的性能是巨大的灾难. 如一个查询是 ALL 类型查询, 那么一般来说可以对相应的字段添加索引来避免.
  下面是一个全表扫描的例子, 可以看到, 在全表扫描时, possible_keys 和 key 字段都是 NULL, 表示没有使用到索引, 并且 rows 十分巨大, 因此整个查询效率是十分低下的.

  mysql> EXPLAIN SELECT age FROM  user_info WHERE age = 20 \G
  *************************** 1. row ***************************
          id: 1
  select_type: SIMPLE
       table: user_info
  partitions: NULL
        type: ALL
  possible_keys: NULL
         key: NULL
     key_len: NULL
         ref: NULL
        rows: 10
    filtered: 10.00
       Extra: Using where
  1 row in set, 1 warning (0.00 sec)

  type 类型的性能比较

  通常来说, 不同的 type 类型的性能关系如下:
  ALL < index < range ~ index_merge < ref < eq_ref < const < system
ALL 类型因为是全表扫描, 因此在相同的查询条件下, 它是速度最慢的.
  而 index 类型的查询虽然不是全表扫描, 但是它扫描了所有的索引, 因此比 ALL 类型的稍快.
  后面的几种类型都是利用了索引来查询数据, 因此可以过滤部分或大部分数据, 因此查询效率就比较高了.

possible_keys

possible_keys 表示 MySQL 在查询时, 能够使用到的索引. 注意, 即使有些索引在 possible_keys 中出现, 但是并不表示此索引会真正地被 MySQL 使用到. MySQL 在查询时具体使用了哪些索引, 由 key 字段决定.

key

此字段是 MySQL 在当前查询时所真正使用到的索引.

key_len

表示查询优化器使用了索引的字节数. 这个字段可以评估组合索引是否完全被使用, 或只有最左部分字段被使用到.
key_len 的计算规则如下:

• 字符串

• char(n): n 字节长度

• varchar(n): 如果是 utf8 编码, 则是 3 n + 2字节; 如果是 utf8mb4 编码, 则是 4 n + 2 字节.

• 数值类型:

• TINYINT: 1字节

• SMALLINT: 2字节

• MEDIUMINT: 3字节

• INT: 4字节

• BIGINT: 8字节

• 时间类型

• DATE: 3字节

• TIMESTAMP: 4字节

• DATETIME: 8字节

• 字段属性: NULL 属性 占用一个字节. 如果一个字段是 NOT NULL 的, 则没有此属性.

我们来举两个简单的栗子:

mysql> EXPLAIN SELECT * FROM order_info WHERE user_id < 3 AND product_name = &#39;p1&#39; AND productor = &#39;WHH&#39; \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_info
   partitions: NULL
         type: range
possible_keys: user_product_detail_index
          key: user_product_detail_index
      key_len: 9
          ref: NULL
         rows: 5
     filtered: 11.11
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

上面的例子是从表 order_info 中查询指定的内容, 而我们从此表的建表语句中可以知道, 表 order_info 有一个联合索引:

KEY `user_product_detail_index` (`user_id`, `product_name`, `productor`)

不过此查询语句 WHERE user_id < 3 AND product_name = 'p1' AND productor = 'WHH' 中, 因为先进行 user_id 的范围查询, 而根据 最左前缀匹配 原则, 当遇到范围查询时, 就停止索引的匹配, 因此实际上我们使用到的索引的字段只有 user_id, 因此在 EXPLAIN    中, 显示的 key_len 为 9. 因为 user_id 字段是 BIGINT, 占用 8 字节, 而 NULL 属性占用一个字节, 因此总共是 9 个字节. 若我们将user_id 字段改为 BIGINT(20)  NOT NULL DEFAULT '0', 则 key_length 应该是8.

上面因为 最左前缀匹配 原则, 我们的查询仅仅使用到了联合索引的 user_id 字段, 因此效率不算高.

接下来我们来看一下下一个例子:

mysql> EXPLAIN SELECT * FROM order_info WHERE user_id = 1 AND product_name = 'p1' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_info
   partitions: NULL
         type: ref
possible_keys: user_product_detail_index
          key: user_product_detail_index
      key_len: 161
          ref: const,const
         rows: 2
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

这次的查询中, 我们没有使用到范围查询, key_len 的值为 161. 为什么呢? 因为我们的查询条件 WHERE user_id = 1 AND product_name = 'p1' 中, 仅仅使用到了联合索引中的前两个字段, 因此 keyLen(user_id) + keyLen(product_name) = 9 + 50 * 3 + 2 = 161

rows

rows 也是一个重要的字段. MySQL 查询优化器根据统计信息, 估算 SQL 要查找到结果集需要扫描读取的数据行数.
这个值非常直观显示 SQL 的效率好坏, 原则上 rows 越少越好.

Extra

EXplain 中的很多额外的信息会在 Extra 字段显示, 常见的有以下几种内容:

• Using filesort
  当 Extra 中有 Using filesort 时, 表示 MySQL 需额外的排序操作, 不能通过索引顺序达到排序效果. 一般有 Using filesort, 都建议优化去掉, 因为这样的查询 CPU 资源消耗大.
  例如下面的例子:

  mysql> EXPLAIN SELECT * FROM order_info ORDER BY product_name \G
  *************************** 1. row ***************************
          id: 1
  select_type: SIMPLE
       table: order_info
  partitions: NULL
        type: index
  possible_keys: NULL
         key: user_product_detail_index
     key_len: 253
         ref: NULL
        rows: 9
    filtered: 100.00
       Extra: Using index; Using filesort
  1 row in set, 1 warning (0.00 sec)

我们的索引是

KEY `user_product_detail_index` (`user_id`, `product_name`, `productor`)

但是上面的查询中根据 product_name 来排序, 因此不能使用索引进行优化, 进而会产生 Using filesort.
如果我们将排序依据改为 ORDER BY user_id, product_name, 那么就不会出现 Using filesort 了. 例如:

mysql> EXPLAIN SELECT * FROM order_info ORDER BY user_id, product_name \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_info
   partitions: NULL
         type: index
possible_keys: NULL
          key: user_product_detail_index
      key_len: 253
          ref: NULL
         rows: 9
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

• Using index
  "覆盖索引扫描", 表示查询在索引树中就可查找所需数据, 不用扫描表数据文件, 往往说明性能不错

• Using temporary
  查询有使用临时表, 一般出现于排序, 分组和多表 join 的情况, 查询效率不高, 建议优化.

【相关推荐】

1. 免费mysql在线视频教程

2. MySQL最新手册教程

3. 布尔教育燕十八mysql入门视频教程

Das obige ist der detaillierte Inhalt vonMySQL-Leistungsoptimierung – Erläutern Sie die Einführung in die Verwendung. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!