Производительность запросов профиля в База данных Azure для MySQL — гибкий сервер с помощью EXPLAIN
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 и оптимизировать запросы с помощью индексов.