Оптимизация MySQL сервера. Часть 5. Полезные запросы для MySQL

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

По мере нахождения полезных запросов я буду дополнять материал публикации.

Эта публикация сугубо мой конспект прочитанной литературы. Информация может быть не очень структурирована и удобочитаема. Материал публикации предоставляется без каких-либо гарантий со стороны автора.

Список первоисточников

1. O’Reilly High Performance MySQL 3rd Edition.
2. O’Reilly High Performance MySQL 4rd Edition.

Примеры запросов

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

Вывод запросов, которые не используют индекс

Запрос:

SELECT THREAD_ID, SQL_TEXT, ROWS_SENT, ROWS_EXAMINED, CREATED_TMP_TABLES,
NO_INDEX_USED, NO_GOOD_INDEX_USED
FROM performance_schema.events_statements_history_long
WHERE NO_INDEX_USED > 0 OR NO_GOOD_INDEX_USED > 0;

Вывод запросов, которые создают временные таблицы

Запрос:

SELECT THREAD_ID, SQL_TEXT, ROWS_SENT, ROWS_EXAMINED, CREATED_TMP_TABLES,
CREATED_TMP_DISK_TABLES
FROM performance_schema.events_statements_history_long
WHERE CREATED_TMP_TABLES > 0 OR CREATED_TMP_DISK_TABLES > 0;

Запросы, которые выполняются более 5 секунд

Запрос:

SELECT THREAD_ID, SQL_TEXT, ROWS_SENT, ROWS_EXAMINED, CREATED_TMP_TABLES,
CREATED_TMP_DISK_TABLES
FROM performance_schema.events_statements_history_long
WHERE TIMER_WAIT > 5000000000;

Потенциально проблемные запросы:

Запрос:

SELECT THREAD_ID, SQL_TEXT, ROWS_SENT, ROWS_EXAMINED, CREATED_TMP_TABLES,
CREATED_TMP_DISK_TABLES
FROM performance_schema.events_statements_history_long
WHERE ROWS_EXAMINED > ROWS_SENT
OR ROWS_EXAMINED > ROWS_AFFECTED
OR ERRORS > 0
OR CREATED_TMP_DISK_TABLES > 0
OR CREATED_TMP_TABLES > 0
OR SELECT_FULL_JOIN > 0
OR SELECT_FULL_RANGE_JOIN > 0
OR SELECT_RANGE > 0
OR SELECT_RANGE_CHECK > 0
OR SELECT_SCAN > 0
OR SORT_MERGE_PASSES > 0
OR SORT_RANGE > 0
OR SORT_ROWS > 0
OR SORT_SCAN > 0
OR NO_INDEX_USED > 0
OR NO_GOOD_INDEX_USED > 0;

Пожалуй, один из немногих запросов, по которому необходимы дополнительные пояснения. Краткая сводая информация по выводимим колонкам представлена в таблице ниже:

Колонка	Описание	Важность
CREATED_TMP_DISK_TABLES	Количество создаваемых запросом временных таблиц на диске. Вариантов решения два: либо оптимизировать запрос, либо увеличивать размер временных таблиц в памяти (параметры tmp_table_size и max_heap_table_size)	Высокая
CREATED_TMP_TABLES	Количество создаваемых временных таблиц в оперативной памяти. Это не плохо само по себе. До тех пор, пока не превышен размер в параметрах tmp_table_size и max_heap_table_size. После этого начнется выгрузка на диск. И это уже проблема.	Средняя
SELECT_FULL_JOIN	Полное сканирование таблицы при операциях JOIN говорит о том, что не хватает каких-то индексов. Нужно выяснять – каких именно.	Высокая
SELECT_FULL_RANGE_JOIN	Этот счетки для запроса увеличивается в том случае, если используется сканирование диапазона в операциях JOIN.	Средняя
SELECT_RANGE	Операция JOIN использует сканирование диапазова в первой таблице. Обычно это не проблема.	Низкая
SELECT_RANGE_CHECK	Счетчик увеличивается, если операция JOIN выполняется без использования индексов. Это очень плохой знак.	Высокая
SELECT_SCAN	Если операция JOIN использует полное сканирование первой таблицы. Это не плохо, если размер таблицы небольшой.	Средняя
SORT_MERGE_PASSES	Количество операций слияния, которые были выполнены при операции сортировки. Если запрос с использование операции сортировки выполняется медленно, то стоит увеличить параметр sort_buffer_size	Низкая
SORT_RANGE	При выполнении операции сортировки использовался диапазон	Низкая
SORT_ROWS	Количество строк в запросе на сортировки. Сравнивается с количество возвращаемых строк. Если количество сортируемых строк больше, чем количество возвращаемых строок, то нужно оптимизировать запрос	Средняя
SORT_SCAN	Счетчик увеличивается, если операция сортировки была выполнена с использованием сканирования таблицы.	Высокая
NO_INDEX_USED	При выполнении запроса не был использовал ни один индекс	Высокая (если таблица большая)
NO_GOOD_INDEX_USED	При выполнении запроса использовался не оптимальный индекс. Необходимо провести ревизию имеющихся индексов	Высокая

Запросы, которые используют полное сканирование таблицы

Запрос:

SELECT query, total_latency, no_index_used_count, rows_sent,
rows_examined
FROM sys.statements_with_full_table_scans
WHERE db='employees' AND
query NOT LIKE '%performance_schema%'\G

Запросы к sys schema

Запрос, которые выводит все проблемные или завершившиеся с ошибками запросы (обращение к sys.statements_with_errors_or_warnings):

SELECT query
FROM sys.statements_with_errors_or_warnings
WHERE db='employees' AND
query NOT LIKE '%performance_schema%'\G

Привести пример таблицы со страницы 57 новой книги от 2021 года.

Вы так же можете обращаться к другим представлениям sys:

Представление	Описание
statement_analysis	Представление с агрегированной статистикой запросов, упорядоченное времени выполнения.
statements_with_errors_or_warnings	Все запросы, которые завершились с ошибками или предупреждениями
statements_with_full_table_scans	Все запросы, которые использовали полное сканирование таблицы
statements_with_runtimes_in_95th_percentile	Все запросы, время выполнения которые было больше, чем время выполнения 95% всех запросов. Короче говоря – список кандидатов на оптимизацию.
statements_with_sorting	Список запросов, которые использовали операцию(и) сортировки
statements_with_temp_tables	Перечень запросов, которые использовали временные таблицы

Полный перечень запросов к sys приведен в разделе документации по MySQL.

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

Запрос

SELECT EVENT_NAME, COUNT(EVENT_NAME)
FROM events_statements_history_long
GROUP BY EVENT_NAME;

Статистика распределения времени выполнения запросов по их типам

Запрос:

SELECT EVENT_NAME, COUNT(EVENT_NAME),
SUM(LOCK_TIME/1000000) AS latency_ms
FROM events_statements_history
GROUP BY EVENT_NAME ORDER BY latency_ms DESC;

Структуры InnoDB, которые используют больше всего памяти

Запрос:

SELECT EVENT_NAME,
CURRENT_NUMBER_OF_BYTES_USED/1024/1024 AS CURRENT_MB,
HIGH_NUMBER_OF_BYTES_USED/1024/1024 AS HIGH_MB
FROM performance_schema.memory_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'memory/innodb/%'
ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC LIMIT 10;

Неиспользуемые индексы

Запрос:

SELECT * FROM sys.schema_unused_indexes;

Ссылки на другие статьи этого цикла

Часть 1. Тестирование производительности MySQL.

Часть 2. Профилирование нагрузки сервера MySQL.

Часть 3. Базовая конфигурация и оптимизация настроек MySQL.

Часть 4. Признаки проблем с производительностью MySQL.

Часть 6. Нюансы настройки репликации MySQL.

Часть 7. Резервное копирование MySQL.