Инструментарий для оптимизации запросов

Команда EXPLAIN query - основной инструмент диагностики производительности запроса. Используемая этой командой терминология может сбить с толку, основные моменты:

• Using index - используется покрывающий индекс
• Using where - применяется WHERE, используется ли при этом индекс - смотри в possible_keys и key
• Using temporary - временная таблица в памяти или на диске
• Using filesort - сортировка не может быть выполнена по индексу, файл тут не при чем

В случае filesort может использоваться файл, если данные сортировки не помещаются в соответствующий буфер в памяти.

Информация об общем состоянии сервера может быть получена с помощью различных команд SHOW:

список выполняющихся запросов:

SHOW [FULL] PROCESSLIST

значения системных переменных:

SHOW VARIABLES;
SHOW VARIABLES LIKE 'sql_%';

информация о таблицах:

SHOW TABLE STATUS;
SHOW TABLE STATUS FROM database LIKE 'meta%';
SHOW TABLE STATUS FROM database WHERE Engine='InnoDB';
SHOW CREATE TABLE tablename;

С помощью запросов к information_schema можно быстро получить много полезной информации.

Например, следующий запрос выводит информацию о количестве записей в каждой таблицы базы:

SELECT   TABLE_NAME, TABLE_ROWS
FROM     INFORMATION_SCHEMA.TABLES
WHERE    TABLE_SCHEMA='database'
ORDER BY TABLE_ROWS  DESC;

Еще один пример - вывод информации о месте, занимаемом каждым объектом базы:

SELECT TABLE_SCHEMA, ENGINE, COUNT(*) AS count_tables,
       SUM(DATA_LENGTH+INDEX_LENGTH) AS size,
       SUM(INDEX_LENGTH) AS index_size
FROM   INFORMATION_SCHEMA.TABLES
WHERE  TABLE_SCHEMA NOT IN ('mysql', 'INFORMATION_SCHEMA')
       AND ENGINE IS NOT NULL
GROUP  BY TABLE_SCHEMA, ENGINE;

До версии 5.1 включение лога медленных запросов было возможно только из конфигов сервера с обязательным его перезапуском.

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

SET GLOBAL log_slow_queries = 'ON';
SET GLOBAL long_query_time = 2;
SET GLOBAL log_output = 'TABLE';
SET GLOBAL log_quereis_not_using_indexes = 'ON';
 
SELECT start_time, query_time, rows_sent, rows_examined, sql_test
  FROM mysql.slow_log
  WHERE db = 'database';

С помощью команды SHOW STATUS можно получить различную статистику текущего соединения:

FLASH STATUS;
SELECT SQL_NO_CACHE film_actor.actor_id, ...
SHOW SESSION STATUS LIKE 'Select%';
SHOW SESSION STATUS LIKE 'Handler%';
SHOW SESSION STATUS LIKE 'Sort%';
SHOW SESSION STATUS LIKE 'Created%';
FLASH STATUS;

С помощью встроенного профайлера можно получать информацию о времени выполнения всех стадий обработки отдельного запроса:

SET profiling = 1;
SELECT ...
SHOW PROFILES;
SHOW PROFILE;
SHOW PROFILE FOR QUERY 1;
SHOW PROFILE CPU FOR QUERY 1;

В серьезного анализа следует использовать набор программ Percona Toolkit:

• поиск дублирующихся индексов
• визуализация планов выполнения
• поиск объектов базы данных из командной строки (по аналогии с grep)
• анализ логов, диагностика проблем