Включаем кэширование запросов на MySQL-сервере

MySQL-сервер содержит встроенный механизм кэширования запросов, который не включен по умолчанию. Кэширование запросов позволяет повысить производительность приложений, не погружаясь в структуру самих запросов.

После включения кэш работает автоматически. При каждом запросе типа SELECT результат выполнения заносится в кэш, если результат не больше значения query_cache_limit (с версии MySQL 8.0 параметр устарел).

Содержание:

• Особенности работы кэша MySQL-сервера;
• Включение механизма кэширования MySQL-сервера;
• Текущее состояние кэша MySQL-сервера;
• Кэширование медленных/тяжелых запросов.

Особенности работы кэша MySQL-сервера.

Запросы типа UPDATE, INSERT, DELETE, REPLACE к таблице - удаляют весь кэш этой таблицы, созданный запросами SELECT. Например, в таблицах, где при просмотре статьи обновляется счетчик количества просмотров, типа UPDATE table_name SET viewcount = viewcount+1 WHERE id=... запросы SELECT кэшироваться не будут. То есть запрос UPDATE будет постоянно удалять весь кэш к таблице table_name.

Кэширование запросов имеет следующие особенности:

• Различие запросов определяется буквально. Например SELECT * FROM ... и select * FROM ... (разный регистр инструкции SELECT) будут для кэша двумя разными запросами.
• В кэш всегда попадает результат выполнения запроса целиком, результаты выполнения подзапросов не кэшируются.
• Кэш работает одинаково для запросов к таблицам с различными механизмами хранения. MySQL также кэширует запросы SELECT к представлениям VIEW.

Запросы, которые вообще не подлежит кэшированию:

• Запросы, содержащие одну из недетерминированных функций: NOW(), CURTIME(), SLEEP(), RAND(), LAST_INSERT_ID() и т.д.
• Запросы, использующие функции или хранимые процедуры, определенные пользователем.
• Запросы, использующие значения пользовательских или локальных переменных MySQL.
• Запросы, обращающиеся к таблицам самой MySQL: mysql, INFORMATION_SCHEMA или performance_schema.
• Запросы, обращающиеся к таблицам, разбитым на партиции.
• Запросы типа:
  • SELECT ... FOR UPDATE,
  • SELECT ... IN SHARE MODE,
  • SELECT ... INTO OUTFILE,
  • SELECT ... INTO DUMPFILE,
  • SELECT * FROM ... WHERE autoincrement_col IS NULL.

Включение механизма кэширования MySQL-сервера.

Чтобы посмотреть параметры кэширования по умолчанию MySQL-сервера, воспользуемся терминалом:

mysql [(none)]> SHOW variables like 'query_cache%';
--- +------------------------------+---------+
--- | Variable_name                | Value   |
--- +------------------------------+---------+
--- | query_cache_limit            | 1048576 |
--- | query_cache_min_res_unit     | 4096    |
--- | query_cache_size             | 1048576 |
--- | query_cache_strip_comments   | OFF     |
--- | query_cache_type             | OFF     |
--- | query_cache_wlock_invalidate | OFF     |
--- +------------------------------+---------+
--- 6 rows in set (0.001 sec)

Где параметры:

• query_cache_size - максимальный объем памяти (в байтах), выделяемый под кэширование запросов. Значение 0 - отключает механизм кэширования.
• query_cache_type - определяет включено ли кэширование (1) или нет (0). При использовании (2) кэшироваться будут только запросы, в которых есть директива SQL_CACHE (об этой директиве в конце);
• query_cache_wlock_invalidate - определяет будут ли данные браться из кэша, если таблица, к которым они относятся заблокирована на чтение.
• query_cache_limit - размер максимальной выборки (в байтах), который может быть помещен в кэш (с версии MySQL 8.0 параметр устарел и вычисляется автоматически на основе статистики запросов).

Чтобы включить кэш запросов и выделить под него 10 мегабайт памяти можно выполнить запрос с правами суперпользователя:

mysql [(none)]> SET global query_cache_size=10*1024*1024;
mysql [(none)]> SET global query_cache_limit=100*1024; 
mysql [(none)]> SET global query_cache_type=1

В момент начала записи MySQL не знает о размере выборки. Если записанный в кэш размер выборки больше, чем query_cache_limit, то запись прекращается и занятое место освобождается. Следовательно, если вы знаете наперед, что результат выборки будет большим чем query_cache_limit, то рекомендуется выполнять его с директивой SELECT SQL_NO_CACHE ... FROM ....

Для дефрагментации кэша можно выполнить команду FLUSH QUERY CACHE. Эта инструкция переносит все запросы, хранящиеся в кэше в его начало и помечает оставшуюся память как один свободный блок).

После перезагрузки MySQL-сервера установленный параметры примут значение по умолчанию и кэширование прекратиться. Что бы включить кэширование на постоянной основе, необходимо добавить вышеуказанные параметры в файл конфигурации MySQL-сервера, в секцию [mysqld]:

[mysqld]
query_cache_type = 1
query_cache_size = 10M
query_cache_limit = 100К

Текущее состояние кэша MySQL-сервера.

Посмотреть состояние кэша можно с помощью запроса:

mysql [(none)]> SHOW GLOBAL STATUS LIKE 'Qcache%';

Будет выведена таблица с параметрами:

• Qcache_free_blocks - показывает сколько свободных блоков есть в кэше;
• Qcache_total_blocks - количество занятых блоков;
• Qcache_free_memory - сколько свободной памяти осталось в кэше;
• Qcache_hits - количество запросов, результаты которых были взяты из кэша;
• Qcache_inserts - количество запросов, которые были добавлены в кеш;
• Qcache_lowmem_prunes - количество запросов, которые были удалены из кэша из-за нехватки памяти;
• Qcache_not_cached - количество запросов, которые не были записаны в кеш из-за использования функций работы со временем и т.п.;
• Qcache_queries_in_cache - количество запросов, которые находятся в кэше.

Мерой эффективности кэша может служить отношение Qcache_hits / (Qcache_inserts + Qcache_not_cached).

Кэширование медленных/тяжелых запросов (MySQL-кэш по требованию).

Определить медленные запросы можно, по средствам включения логов медленных/тяжелых запросов (slow_log).

После того как медленные запросы к БД MySQL определены и оптимизация этих запросов не помогает, то их можно принудительно закэшировать на стороне MySQL-сервера, применив дополнительную инструкцию SQL_CACHE, например:

SELECT SQL_CACHE id, title, author FROM news WHERE date = `2023-05-09`;

Перед этим необходимо включить механизм кэширования MySQL-сервера в режим query_cache_type=2 или query_cache_type=1. Режим query_cache_type=2 будет кэшировать запросы только с дополнительной инструкцией SQL_CACHE.

Внимание! Запросы с принудительным кэшированием должны придерживаться правил, описанных в начале материала "Особенности работы кэша MySQL-сервера", что бы их кэш не аннулировался.