Артефакт оптимизации производительности MySQL Объяснить анализ использования

MySQL

Введение

MySQL предоставляет команду EXPLAIN, которая можетSELECTВыписка анализируется и выводитсяSELECTДетали выполнения для целевой оптимизации разработчиками.
Использовать команду EXPLAIN очень просто, просто добавьте объяснение перед оператором SELECT, например:

EXPLAIN SELECT * from user_info WHERE  id < 300;

Подготовить

Чтобы облегчить демонстрацию использования EXPLAIN, сначала нам нужно создать две тестовые таблицы и добавить соответствующие данные:

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 выходной формат

Вывод команды EXPLAIN примерно следующий:

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)

Значение каждого столбца следующее:

  • id: идентификатор запроса SELECT. Каждому SELECT автоматически присваивается уникальный идентификатор.

  • select_type: тип запроса SELECT.

  • таблица: какая таблица запрашивается

  • разделы: соответствующие разделы

  • тип: тип соединения

  • возможных_ключей: индексы, которые можно использовать в этом запросе.

  • ключ: точный индекс, используемый в этом запросе.

  • ref: какое поле или константу использовать с ключом

  • rows: показывает, сколько строк было просмотрено этим запросом.Это оценка.

  • filtered: указывает процент данных, отфильтрованных по этому условию запроса.

  • дополнительно: дополнительная информация

Далее, давайте сосредоточимся на некоторых из наиболее важных полей.

select_type

select_typeУказывает тип запроса, и его общие значения:

  • SIMPLE означает, что этот запрос не содержит запроса UNION или подзапроса.

  • PRIMARY, указывающий, что этот запрос является самым внешним запросом

  • UNION, указывающий, что этот запрос является вторым или последующим запросом к UNION.

  • ЗАВИСИМОЕ ОБЪЕДИНЕНИЕ, второй или последующий оператор запроса в ОБЪЕДИНЕНИИ, в зависимости от внешнего запроса

  • UNION RESULT, результат UNION

  • ПОДЗАПРОС, первый SELECT в подзапросе

  • ЗАВИСИМЫЙ ПОДЗАПРОС: первый SELECT в подзапросе, который зависит от внешнего запроса, то есть подзапрос зависит от результата внешнего запроса.

Наиболее распространенная категория запросов должна бытьSIMPLEНапример, если в нашем запросе нет ни подзапросов, ни запросов UNION, то обычно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)

Если бы мы использовали запрос UNION, вывод EXPLAIN выглядел бы так:

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)

table

Указывает таблицу или производную таблицу, задействованную в запросе.

type

typeПоле более важно, оно обеспечивает важную основу для оценки эффективности запроса.typeполе, мы считаем, что этот запрос全表扫描все еще索引扫描Ждать.

тип общий тип

Обычно используемые значения для типа:

  • system: В таблице только одни данные.Этот тип особенныйconstтип.

  • const: Эквивалентное сканирование запроса по первичному ключу или уникальному индексу возвращает не более одной строки данных.Константный запрос очень быстрый, потому что его нужно прочитать только один раз.
    Например, в приведенном ниже запросе используется индекс первичного ключа, поэтомуtypeэтоconstТип.

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)

  • eq_ref: этот тип обычно появляется в запросах на соединение нескольких таблиц, указывая, что для каждого результата предыдущей таблицы может быть сопоставлена ​​только одна строка результатов из последней таблицы.И операция сравнения запроса обычно=, эффективность запросов выше, например:

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)

  • ref: этот тип обычно появляется в запросах на соединение нескольких таблиц, нацеленных на индексы с неуникальными или не первичными ключами или использующих最左前缀Запрос индекса правил.
    Например, в следующем примере используетсяrefТип запроса:

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)

  • range: указывает, что используется запрос диапазона индекса, и некоторые записи данных в таблице получены через диапазон поля индекса.Этот тип обычно появляется в =, , >, >=, , МЕЖДУ, В() в работе.
    когдаtypeдаrange, затем EXPLAIN выводитrefполе равно NULL иkey_lenЭто поле с самым длинным индексом, используемым в этом запросе.

Например, следующий пример представляет собой запрос диапазона:

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)

  • index: представляет полное сканирование индекса, аналогичное типу ALL, за исключением того, что тип ALL представляет собой полное сканирование таблицы, а тип индекса сканирует только все индексы без сканирования данных.
    indexТип обычно возникает, когда: запрашиваемые данные могут быть получены непосредственно в дереве индекса без сканирования данных В этом случае в поле Дополнительно будет отображатьсяUsing index.

Например:

mysql> EXPLAIN SELECT name FROM  user_info \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_info
   partitions: NULL
         type: index
possible_keys: NULL
          key: name_index
      key_len: 152
          ref: NULL
         rows: 10
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

В приведенном выше примере поле имени, которое мы запрашиваем, является индексом, поэтому мы можем получить данные непосредственно из индекса, чтобы удовлетворить требования запроса, не запрашивая данные в таблице.Таким образом, в этом случае значение типа равноindex, а значение Extra равноUsing index.

  • ALL: указывает на полное сканирование таблицы. Этот тип запроса является одним из наименее эффективных запросов. Вообще говоря, наш запрос не должен иметь тип запроса ALL, потому что такой запрос содержит большой объем данных. Производительность — это огромная катастрофа. . Если запрос является запросом типа ALL, его обычно можно избежать, добавив индекс к соответствующему полю. Ниже приведен пример полного сканирования таблицы. Вы можете видеть, что во время полного сканирования таблицы, возможные_ключи и ключ Поля все имеют значение NULL, указывая на то, что индекс не используется, а строки очень велики, поэтому эффективность всего запроса очень низкая.

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)

Сравнение производительности типов

Вообще говоря, отношения производительности разных типов выглядят следующим образом:
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): 3, если кодировка utf8n + 2 байта; 4, если кодировка utf8mb4n + 2 байта.

  • Числовой тип:

    • TINYINT: 1 байт

    • МАЛЕНЬКИЙ INT: 2 байта

    • СРЕДНИЙ: 3 байта

    • INT: 4 байта

    • БОЛЬШОЙ: 8 байт

  • тип времени

    • ДАТА: 3 байта

    • TIMESTAMP: 4 байта

    • ДАТАВРЕМЯ: 8 байт

  • Атрибуты поля: Атрибут NULL занимает один байт.Если поле НЕ NULL, такого атрибута нет.

Возьмем два простых каштана:

mysql> EXPLAIN SELECT * FROM order_info WHERE user_id < 3 AND product_name = 'p1' AND productor = 'WHH' \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 занимает 1 байт, то есть всего 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

Строки также являются важным полем. Оптимизатор запросов MySQL оценивает количество строк данных, которые SQL должен просмотреть и прочитать, чтобы найти результирующий набор на основе статистической информации. Это значение является очень интуитивным индикатором эффективности SQL. В принципе , чем меньше строк, тем лучше.

Extra

Много дополнительной информации в EXplain будет отображаться в поле «Дополнительно», общие из них следующие:

  • Using filesort
    когда Экстра имеетUsing filesort, это означает, что MySQL нуждается в дополнительных операциях сортировки, и эффект сортировки не может быть достигнут за счет порядка индекса.Using filesort, рекомендуется его оптимизировать и удалить, так как такой запрос потребляет много ресурсов ЦП.

Например следующий пример:

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)

  • Использование индекса «покрывающее сканирование индекса» означает, что запрос может найти необходимые данные в дереве индекса без сканирования файла данных таблицы, что часто означает хорошую производительность.

  • При использовании временного запроса используется временная таблица, что обычно происходит при сортировке, группировке и объединении нескольких таблиц.Эффективность запроса невысока, поэтому рекомендуется оптимизация.

Категории