В MySQL есть очень полезная функциональность — кеш запросов. Кеш запросов доступен в MySQL начиная с версии 4.0.
Многие СУБД имеют подобную функциональность, но в отличие от MySQL они кешируют планы выполнения запросов, тогда как MySQL кеширует результаты запросов.
Дальше о том, как работает кеш запросов, как его настраивать и оптимально использовать.
Как работает кеш запросов.
Кеш запросов можно представлять себе как хеш, ключами которого являются запросы, а значениями — результаты запросов.
Если использование кеша запросов включено, то при получении запроса MySQL определяет, равны ли первые три символа запроса «SEL». Если да, то MySQL смотрит, есть ли в кеше запросов запись с ключом, равным запросу. Отсюда следуют два важных правила:
- MySQL выполняет побайтовое сравнение, поэтому запросы, имеющие отличие хотя бы в одном символе (например, SELECT * FROM table и select * from table) будут рассматриваться как два разных запроса. Поэтому необходимо писать запросы в едином стиле;
- В MySQL до версии 5.0 запросы, в начале которых есть пробел или написан комментарий никогда не будут браться из кеша.
Кроме результатов, MySQL хранит в кеше список таблиц, выборка из которых закеширована. Если в любой из таблиц, выборка из которой есть в кеше, проиcходят изменения (вставка или изменение строк), то MySQL удаляет из кеша такие выборки. Такой подход ускоряет работу MySQL, но может быть неэффективным для систем с большим количеством запросов на изменение таблиц.
Оптимизация для подобных таблиц — вынести часто изменяющиеся данные в отдельную таблицу. Например, есть таблица статей, в которой также хранятся и количество комментариев к ней. Посколько количество комментариев изменяется часто, а сама статья практически не меняется имеет смысл вынести количество комментариев в отдельную таблицу, позволяя при этом избегать частой инвалидации кеша статей.
Использование кеша запросов полностью прозрачно для клиента. Клиент не знает о том, выполнил ли MySQL запрос, или вернул его из кеша.
MySQL кеширует результаты только SELECT запросов. MySQL не кеширует запросы, результаты которых могут измениться. Например, запросы в которых используются функции, относящиеся к текущему времени (NOW(), CURDATE() и др.), к текущему соединению (CURRENT_USER(), CONNECTION_ID() и др.) и другие. Полный список таких функций можно найти в мануале. Кроме этого, MySQL не кеширует запросы, в которых есть использование пользовательских функций, хранимых процедур, выборки из баз mysql или INFORMATION_SCHEMA, выборки из таблиц, для которых определены привилегии для столбцов.
Оптимизация для запросов, использующих функции текущего времени (NOW(), CURDATE() и др.) — замена таких функций на строку с датой. Например: запрос
SELECT * FROM table WHERE create_date > NOW() — INTERVAL 1 DAY
, который не будет кешироваться можно заменить на запрос, который закешируется:
SELECT * FROM table WHERE create_date > ‘2009-10-14’ — INTERVAL 1 DAY
Отдельно обрабатывается кеширование выборок от InnoDB таблиц. MySQL удаляет результаты выборок из кеша при любом изменении таблицы внутри транзакции (хотя мог бы не удалять до тех пор, пока транзакция не зафиксирована). Кроме этого, все выборки из этой таблицы не будут кешироваться до тех пор, пока транзакция не зафиксирована.
Использование кеша запросов.
Посмотреть статистику использования кеша запросов можно выполнив команду:
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 — количество запросов, которые находятся в кеше.
За использование кеша запросов отвечают следующие конфигурационные переменные:
- query_cache_type = (ON, DEMAND, OFF) — определяет включено ли кеширование или нет(ON, OFF). При использовании DEMAND кешироваться будут только запросы, в которых есть директива SQL_CACHE;
- query_cache_size — размер кеша запросов. query_cache_size = 0 отключает использование кеша;
- query_cache_limit — размер максимальной выборки, хранимой в кеше;
- query_cache_min_res_unit — минимальный размер блока, хранимого в кеше;
- query_cache_wlock_invalidate — определяет будут ли данные браться из кеша, если таблица, к которым они относятся заблокирована на чтение.
При запуске MySQL выделяет в памяти блок, размером в query_cache_size. При выполнении запроса, как только получены первые строки результата сервер начинает кешировать их: он выделяет в кеше блок памяти, равный query_cache_min_res_unit, записывает в него результат выборки. Если не вся выборка поместилась в блок, то сервер выделяет следующий блок и так далее. После того, как весь результат записан в кеш, MySQL выделяет свободную часть последнего блока в отдельный блок и помечает его как свободный. Если вокруг такого блока есть свободный блок, то MySQL объединяет их в один.
В момент начала записи MySQL не знает о размере получившейся выборки. Если записанный в кеш размер выборки больше, чем query_cache_limit, то запись прекращается и занятое место освобождается (поэтому, если вы знаете наперед, что результат выборки будет большим, рекомендуется выполнять его с директивой SQL_NO_CACHE). В случае, если MySQL кеширует несколько запросов параллельно, блоки, выделяемые для разных запросов, могут чередоваться. Кроме этого, после того, как запрос удален из кеша, освободившееся место может быть недостаточным для записи новых запросов. Это приводит к фрагментации кеша. Для дефрагментации кеша можно выполнить команду FLUSH QUERY CACHE. (FLUSH QUERY CACHE переносит все запросы, хранящиеся в кеше в его начало и помечает оставшуюся память как один свободный блок). Кроме этого уменьшить фрагментацию кеша можно правильным подбором параметра query_cache_min_res_unit.Если значение query_cache_min_res_unit небольшое, то фрагментация будет уменьшаться, однако, MySQL будет вынужден создавать больше блоков в кеше. Если значение велико, то фрагментация будет большой.
Значение query_cache_min_res_unit должно быть равно среднему размеру кешируемого значения. Его примерное значение можно вычислить по формуле query_cache_min_res_unit = (query_cache_size – Qcache_free_memory) / Qcache_queries_in_cache. Однако для сайтов, размер выборки которых сильно меняется, рекомендуется использовать query_cache_type = DEMAND и явное указание на то, что запрос должен быть закеширован директивой SQL_CACHE. Кроме этого, необходимо ограничить запись в кеш больших выборок заданием переменной query_cache_limit или директивой SQL_NO_CACHE.
Определить то, насколько фрагментирован кеш, можно по значению переменной Qcache_free_blocks. Для идеального нефрагментированного кеша значение равно единице, в худшем случае — Qcache_total_blocks / 2. Так же можно определить, что ваш кеш запросов сильно фрагментируется, если значение Qcache_lowmem_prunes постоянно возрастает при том, что значение Qcache_free_memory далеко от нуля.
Оценить эффективность использования кеша можно по формуле Qcache_hits / (Qcache_hits + Com_select). О том, какое значение является достаточным для вашего сайта решать вам. Если для запросов хранимых в кеше требуется большое время, то эффективность даже в 10% может быть полезной. Однако если эффективность использования низкая и увеличить ее не удается, то возможно, что характер нагрузки вашей системы такой, что кеш запросов вовсе не эффективен для вас. В таких случаях бывает более полезным вообще отключить кеширование запросов и использовать кеширование на стороне клиентов.
Читайте оригинал статьи на MySQL Consulting.
