Научит вас анализу оптимизации запросов MySQL

MySQL
Научит вас анализу оптимизации запросов MySQL

предисловие

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

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

И просто добавьте объяснение перед оператором запроса:

EXPLAIN SELECT * FROM customer WHERE id < 100;

Подготовить

Во-первых, вам нужно создать две тестовые таблицы и данные:

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

INSERT INTO customer (name, age) VALUES ('a', 1);
INSERT INTO customer (name, age) VALUES ('b', 2);
INSERT INTO customer (name, age) VALUES ('c', 3);
INSERT INTO customer (name, age) VALUES ('d', 4);
INSERT INTO customer (name, age) VALUES ('e', 5);
INSERT INTO customer (name, age) VALUES ('f', 6);
INSERT INTO customer (name, age) VALUES ('g', 7);
INSERT INTO customer (name, age) VALUES ('h', 8);
INSERT INTO customer (name, age) VALUES ('i', 9);
CREATE TABLE `orders` (
  `id`           BIGINT(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id`      BIGINT(20) unsigned NOT NULL DEFAULT 0,  `product_name` VARCHAR(50) NOT NULL DEFAULT '',
  `productor`    VARCHAR(30) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `user_product_detail_index` (`user_id`, `product_name`, `productor`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4

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

EXPLAIN выходной формат

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

mysql> explain select * from customer where id = 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer
   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

  • SIMPLE — простой запрос на выборку, не содержащий подзапросов или UNION.

  • PRIMARY — если запрос содержит какие-либо сложные подзапросы, самый внешний запрос помечается как основной.

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

  • DEPENDENT UNION — второй или последующий оператор запроса в UNION, в зависимости от внешнего запроса

  • UNION RESULT - выберите результат, чтобы получить результаты из таблицы UNION.

  • DERIVED - Подзапросы, содержащиеся в списке from, помечаются как производные. MySQL будет рекурсивно выполнять эти подзапросы, помещая результаты во временную таблицу.

  • SUBQUERY — содержит подзапрос в списке select или where

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

Наиболее распространенная категория запросов должна бытьSIMPLEНапример, если в нашем запросе нет ни подзапросов, ни запросов UNION, то обычноSIMPLEтипа, например:

mysql> explain select * from customer where id = 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer
   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 customer  WHERE id IN (1, 2, 3))    
    -> UNION
    -> (SELECT * FROM customer WHERE id IN (3, 4, 5));
+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+
| id | select_type  | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra           |
+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+
|  1 | PRIMARY      | customer  | NULL       | range | PRIMARY       | PRIMARY | 8       | NULL |    3 |   100.00 | Using where     |
|  2 | UNION        | customer  | 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 customer where id = 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer
   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 customer, order_info WHERE customer.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: customer
   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 customer, order_info WHERE customer.id = order_info.user_id AND order_info.user_id = 5\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer
   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 customer WHERE id BETWEEN 2 AND 8 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer
   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  customer \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer
   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  customer WHERE age = 20 \G*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer
   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.
Последние типы используют индексы для запроса данных, поэтому часть или большую часть данных можно отфильтровать, поэтому эффективность запросов относительно высока.

Для программистов, если запрос гарантированно достигнет хотя быrangeуровне или в лучшем случаеrefЭто отличный и ответственный программист.

  • ALL: (полное сканирование таблицы) Полное сканирование таблицы, несомненно, является наихудшим.Если количество данных составляет миллионы, полное сканирование таблицы будет очень медленным.
  • index: (полное сканирование индекса) Полное сканирование файла индекса намного лучше всех, ведь поиск данных из индексного дерева выполняется быстрее, чем поиск данных из полной таблицы.
  • range: Получить только заданный диапазон строк, используя индекс для сопоставления строк. Диапазон сужается и, конечно, быстрее, чем полное сканирование таблицы и полное сканирование файлов индекса. Операторы SQL обычно имеют между, в, >,
  • ref: Сканирование неуникального индекса, которое по сути является доступом к индексу, возвращает все строки, соответствующие одному значению. Например, при запросе всех коллег, принадлежащих к группе исследований и разработок в компании, совпадающие результаты представляют собой несколько, но не уникальных значений.
  • eq_ref: Уникальное сканирование индекса, для каждого ключа индекса есть одна соответствующая ему запись в таблице. Например, если вы запрашиваете генерального директора компании, результатом сопоставления может быть только одна запись.
  • const: указывает, что его можно найти по индексу один раз, а const используется для сравнения первичного ключа или уникального индекса. Поскольку сопоставляется только одна строка данных, MySQL может быстро преобразовать запрос в константу, если первичный ключ помещен в список where.
  • system: В таблице есть только одна запись (равная системной таблице), представляющая собой специальный столбец типа const, который обычно не отображается, просто поймите


possible_keys

possible_keysУказывает индексы, которые MySQL может использовать при выполнении запросов. Обратите внимание, что даже некоторые индексыpossible_keysпоявляется в , но это не означает, что этот индекс будет фактически использоваться MySQL.Какие индексы используются MySQL при запросе, определяетсяkeyполевое решение.

key

Это поле является индексом, фактически используемым MySQL в текущем запросе.

key_len

Указывает количество байтов индекса, используемого оптимизатором запросов. Это поле можно использовать для оценки того, используется ли составной индекс полностью или используется только крайнее левое поле.
Правила расчета key_len следующие:

  • нить

    • char(n): длина n байтов

    • varchar(n): 3, если кодировка utf8

      n + 2 байта; 4, если кодировка utf8mb4
      n + 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 занимает один байт, поэтому общее количество байтов равно 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)

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

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


Категории