Производительность запросов профиля в База данных Azure для MySQL — гибкий сервер с помощью EXPLAIN

  • Статья

ОБЛАСТЬ ПРИМЕНЕНИЯ: Отдельный сервер Базы данных Azure MySQL Гибкий сервер Базы данных Azure MySQL

Важно!

База данных Azure для MySQL один сервер находится на пути выхода на пенсию. Настоятельно рекомендуется выполнить обновление до База данных Azure для MySQL гибкого сервера. Дополнительные сведения о миграции на гибкий сервер База данных Azure для MySQL см. в статье "Что происходит с одним сервером База данных Azure для MySQL?"

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

mysql> EXPLAIN SELECT * FROM tb1 WHERE id=100\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 995789
     filtered: 10.00
        Extra: Using where

В этом примере значение ключа равно NULL, что означает, что База данных Azure для MySQL гибкий сервер не может найти индексы, оптимизированные для запроса. В результате выполняется полная проверка таблицы. Давайте оптимизируем этот запрос, добавив индекс в столбец ID, а затем снова запустим инструкцию EXPLAIN.

mysql> ALTER TABLE tb1 ADD KEY (id);
mysql> EXPLAIN SELECT * FROM tb1 WHERE id=100\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb1
   partitions: NULL
         type: ref
possible_keys: id
          key: id
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL

Теперь выходные данные показывают, что База данных Azure для MySQL гибкий сервер использует индекс для ограничения количества строк до 1, что значительно сокращает время поиска.

Индекс покрытия

Индекс покрытия состоит из всех столбцов запроса в индексе для сокращения извлечения значений из таблиц данных. Это показано в следующей инструкции GROUP BY и в ее выходных данных.

mysql> EXPLAIN SELECT MAX(c1), c2 FROM tb1 WHERE c2 LIKE '%100' GROUP BY c1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 995789
     filtered: 11.11
        Extra: Using where; Using temporary; Using filesort

В выходных данных показано, что База данных Azure для MySQL гибкий сервер не использует индексы, так как правильные индексы недоступны. Выходные данные также показывают использование временных данных; Использование filesort, указывающее, что гибкий сервер База данных Azure для MySQL создает временную таблицу для удовлетворения предложения GROUP BY.

Создание индекса только в столбце c2 не отличается, и База данных Azure для MySQL гибкий сервер по-прежнему должен создать временную таблицу:

mysql> ALTER TABLE tb1 ADD KEY (c2);
mysql> EXPLAIN SELECT MAX(c1), c2 FROM tb1 WHERE c2 LIKE '%100' GROUP BY c1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 995789
     filtered: 11.11
        Extra: Using where; Using temporary; Using filesort

В этом случае можно создать охватываемый индекс по обоим столбцам c1 и c2, добавив при этом значение столбца c2 непосредственно в индекс для исключения дальнейшего поиска данных.

mysql> ALTER TABLE tb1 ADD KEY covered(c1,c2);
mysql> EXPLAIN SELECT MAX(c1), c2 FROM tb1 WHERE c2 LIKE '%100' GROUP BY c1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb1
   partitions: NULL
         type: index
possible_keys: covered
          key: covered
      key_len: 108
          ref: NULL
         rows: 995789
     filtered: 11.11
        Extra: Using where; Using index

Как показано в выходных данных, приведенных выше, База данных Azure для MySQL гибкий сервер теперь использует покрытый индекс и избегает создания временной таблицы.

Составной индекс

Составной индекс состоит из значений нескольких столбцов и может считаться массивом строк, отсортированных путем объединения значений индексированных столбцов. Этот метод может быть полезен в инструкции GROUP BY.

mysql> EXPLAIN SELECT c1, c2 from tb1 WHERE c2 LIKE '%100' ORDER BY c1 DESC LIMIT 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 995789
     filtered: 11.11
        Extra: Using where; Using filesort

База данных Azure для MySQL гибкий сервер выполняет операцию сортировки файлов, которая довольно медленна, особенно если она должна сортировать много строк. Для оптимизации этого запроса создайте составной индекс по обоим столбцам, которые сортируются.

mysql> ALTER TABLE tb1 ADD KEY my_sort2 (c1, c2);
mysql> EXPLAIN SELECT c1, c2 from tb1 WHERE c2 LIKE '%100' ORDER BY c1 DESC LIMIT 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb1
   partitions: NULL
         type: index
possible_keys: NULL
          key: my_sort2
      key_len: 108
          ref: NULL
         rows: 10
     filtered: 11.11
        Extra: Using where; Using index

Выходные данные инструкции EXPLAIN теперь показывают, что База данных Azure для MySQL гибкий сервер использует объединенный индекс, чтобы избежать дополнительной сортировки по мере сортировки индекса.

Заключение

Вы можете значительно повысить производительность, используя EXPLAIN вместе с различными типами индексов. Наличие индекса в таблице не обязательно означает, что База данных Azure для MySQL гибкий сервер может использовать его для запросов. Следует всегда проверять свои предположения с помощью инструкции EXPLAIN и оптимизировать запросы с помощью индексов.

Следующие шаги

  • Чтобы найти ответы на самые важные вопросы либо опубликовать новый вопрос или ответ, посетите Stack Overflow.