Сегодня заказчик столкнулся с проблемой: для работы выбрано несколько файлов, фоновая обработка очень медленная из-за большого объема данных, в браузере отображается таймаут 504. Чтобы убедиться, что проблема заключается в операторе sql, для анализа используются следующие методы:
Запросить записи выполнения SQL
объяснить анализ
Время выполнения оператора MySQL
Три способа открытия будут описаны ниже.
Запросить записи выполнения SQL
Проверьте, включена ли функция журнала
show variables LIKE 'general%';
general_log: включена ли функция ведения журнала, по умолчанию она выключена. general_log_file: путь хранения журнала
В большинстве случаев анализа производительности необходимо использовать эту команду, которую можно использовать для просмотра результатов выполнения операторов SQL, а также для выбора лучших индексов и оптимизации операторов.
грамматика
объяснить + оператор SQL
вывод:
разбор параметров
id
ВЫБЕРИТЕ идентификатор. Это порядковый номер запроса SELECT
select_type
SELECT, который может быть любым из следующих:
SIMPLE: Простой SELECT (без использования UNION или подзапросов)
PRIMARY: самый внешний SELECT
UNION:Второй или последующий оператор SELECT в UNION
DEPENDENT UNION: второй или последующий оператор SELECT в UNION, в зависимости от внешнего запроса.
UNION RESULT:СОЮЗ
результат
SUBQUERY: первый SELECT в подзапросе
DEPENDENT SUBQUERY: первый SELECT в подзапросе, в зависимости от внешнего запроса
DERIVED: SELECT производной таблицы (подзапрос предложения FROM)
table
таблица, на которую ссылается выходная строка
type
Тип соединения. Ниже приведены различные типы соединений, упорядоченные от лучшего к худшему:
system: Таблица имеет только одну строку (= системная таблица). Это частный случай типа соединения const.
const: В таблице есть не более одной соответствующей строки, которая будет прочитана в начале запроса. Так как имеется только одна строка, значения столбца в этой строке могут считаться постоянными остальной частью оптимизатора. const таблицы работают быстро, потому что они читаются только один раз!
eq_ref: Для каждой комбинации строк из предыдущей таблицы прочитать строку из этой таблицы. Это, вероятно, лучший тип соединения, за исключением типов const.
ref: для каждой комбинации строк из предыдущей таблицы все строки с совпадающими значениями индекса будут считаны из этой таблицы.
ref_or_null: этот тип объединения похож на ref, но добавляет, что MySQL может специально искать строки, содержащие значения NULL.
index_merge: этот тип соединения указывает на то, что используется метод оптимизации слияния индексов.
unique_subquery: Этот тип заменяет refs для подзапросов IN формы:
значение IN
(ВЫБЕРИТЕ первичный_ключ ИЗ одиночной_таблицы, ГДЕ некоторые_выражения)
unique_subquery — это функция поиска по индексу, которая может полностью заменить подзапросы и является более эффективной.
index_subquery: этот тип соединения похож на unique_subquery. В подзапросах можно заменить, но только для неуникальных индексов в подзапросах вида:
значение IN (ВЫБРАТЬ key_column FROM single_table WHERE some_expr)
range: Получить только заданный диапазон строк, используя индекс для выбора строк.
index: этот тип соединения аналогичен ALL, за исключением того, что сканируется только дерево индексов. Обычно это быстрее, чем ALL, потому что индексные файлы обычно меньше, чем файлы данных.
ALL: Для каждой комбинации строк из предыдущей таблицы выполните полное сканирование таблицы.
possible_keys
Указывает, какой индекс MySQL может использовать для поиска строк в этой таблице.
key
Показывает ключи (индексы), которые MySQL действительно решил использовать. Если индекс не выбран, ключ равен NULL.
key_len
Отображает длину ключа, которую MySQL решил использовать. Если ключ равен NULL, длина равна NULL.
ref
Показывает, какой столбец или константа используется с ключом для выбора строк из таблицы.
rows
Отображает количество строк, которые, по мнению MySQL, необходимо проверить при выполнении запроса. Умножение данных по нескольким строкам дает оценку количества строк для обработки.
filtered
Показывает оценку в процентах количества строк, отфильтрованных по критериям.
Extra
Этот столбец содержит сведения о запросе, разрешаемом MySQL.
Distinct: MySQL прекращает поиск дополнительных строк для текущей комбинации строк после нахождения первой совпадающей строки.
Not exists: MySQL может оставить запрос
Оптимизация JOIN, найдено 1 совпадение LEFT
После JOINing стандартной строки в таблице больше не проверяются строки для предыдущей комбинации строк.
range checked for each record (index map: #): MySQL не нашел подходящего индекса для использования, но обнаружил, что если известно значение столбца из предыдущей таблицы, возможно, можно использовать частичный индекс.
Using filesort: MySQL требуется дополнительный проход, чтобы выяснить, как извлекать строки в отсортированном порядке.
Using index: получить информацию о столбцах таблицы путем чтения фактической строки, используя только информацию из дерева индексов, без дальнейшего поиска.
Using temporary: Чтобы разрешить запрос, MySQL необходимо создать временную таблицу для хранения результатов.
Using where:ГДЕ
Предложение используется для ограничения того, какая строка соответствует следующей таблице или отправляется клиенту.
Using sort_union(...), Using union(...), Using
intersect(...): эти функции иллюстрируют, как объединять сканирования индекса для типа соединения index_merge.
Using index for group-by: Аналогично методу доступа к таблице с помощью индекса, с помощью
index for group-by означает, что MySQL нашел индекс, который можно использовать для запроса всех столбцов запроса GROUP BY или DISTINCT без дополнительного поиска на жестком диске для доступа к фактической таблице.
Время выполнения оператора MySQL
Операторы show profile и show profiles могут отображать информацию о производительности для выполнения операторов SQL во время текущего сеанса.
Проверьте, включен ли профиль
show variables like '%profil%';
профилирование:OFF По умолчанию эта функция отключена.