Кэширование запросов в MySQL
Кэширование запросов позволяет повысить производительность приложений, не погружаясь в структуру самих запросов. Мы рассмотрим встроенный в MySQL механизм кэширования запросов и альтернативный подход для случаев, когда встроенный механизм неприменим.
Встроенный механизм кэширования запросов в MySQL.
MySQL содержит встроенный механизм кэширования запросов, который, однако не включен по умолчанию. Вот такие параметры выставленны по умолчанию в MySQL 5.0:
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+---------+
Чтобы включить кэш запросов и выделить под него 32 мегабайта памяти можно выполнить запрос set @@global.query_cache_size=32*1024*1024; с правами суперпользователя, а чтобы сделать эту настройку постоянной, следует добавить в my.cnf в подраздел [mysqld] строчку query_cache_size=32M. Второй полезный параметр - query_cache_limit задает максимальный объем результата выполнения запроса, который может быть помещен в кэш
После включения кэш работает автоматически:
- При каждом запросе типа SELECT вычисляет хэш-сумму строки запроса и ищет ее в кэше. Если находит - возвращает рузельтат из кэша, если нет - выполняет запрос, а результат заносит в кэш (если результат не больше значения query_cache_limit).
- При каждом запросе типа UPDATE, REPLACE, INSERT, DELETE, TRUNCATE или ALTER, удаляет из кэша все запросы, использующие таблицу, подвергшуюся обновлению.
Отметим следующие особенности работы кэша:
- Различие запросов определяется буквально, сравнение чувствительно к реестру. Поэтому SELECT * FROM news и select * FROM news будут для кэша двумя разными запросами.
- В кэш всегда попадает результат выполнения запроса целиком, результаты выполнения подзапросов не кэшируются.
- Кэш работает одинаково для запросов к таблицам с различными механизмами хранения. MySQL также кэширует запросы SELECT к представлениям (VIEW).
- Ряд запросов не подлежит кэшированию:
- Запросы, содержащие одну из недетерминированных функций: NOW(), SLEEP(), RAND(), CURTIME(), LAST_INSERT_ID() и.др.
- Запросы, использующие функции или хранимые процедуры, определенные пользователем.
- Запросы, использующие значения пользовательских или локальных переменных.
- Запросы, обращающиеся к базам данных mysql, INFORMATION_SCHEMA или performance_schema.
- Запросы, обращающиеся к таблицам, разбитым на партиции (начиная с MySQL версий 5.1.63, 5.5.23, 5.6.5).
- Запросы типа SELECT ... FOR UPDATE, SELECT ... IN SHARE MODE, SELECT ... INTO OUTFILE, SELECT ... INTO DUMPFILE, SELECT * FROM ... WHERE autoincrement_col IS NULL.
- Запросы, использующие временные таблицы.
- Запросы, не обращающиеся к таблицам.
- Запросы, которые генерируют предупреждения (warnings).
- В случае, если пользователь имеет права не на всю таблицу, а только на определенные колонки таблицы. Это исключение — следствие того, что кэш запросов один для всех пользователей, а права доступа средствами кэша проверяются лишь на уровне таблиц.
В качестве примера запроса, который может быть кэширован, приведем запрос, формирующий список обсуждений на главной странице webew.ru:
FROM
(SELECT e2.type,e2.id,e2.title,e2.active,
count(*)-IF(e2.type=3,1,0) cnt, MAX(e.id) lastid, MAX(e.created) lastcreated
FROM entities e
LEFT JOIN entities e2
ON e2.id=IF(e.foreparent,e.foreparent,e.id)
WHERE e.active = 1 AND e2.active = 1 AND e.type=3
GROUP BY e2.id
ORDER BY MAX(e.created) DESC LIMIT 10) t
ORDER BY lastcreated DESC
Запрос выполняет сортировку статей и тем по дате последнего комментария. Обращения к главной странице сайта происходят чаще, чем добавление комментариев или статей, поэтому кэш достаточно эффективен.
Замечание: если системная переменная query_cache_wlock_invalidate принимает значение OFF, то блокировка таблицы для записи не сбрасывает кэшированные запросы с участием данной таблицы. В этом случае через query cache будет возможно получение данных из заблокированной таблицы.
Текущее состояние кэша
Посмотреть состояние кэша можно с помощью запроса:
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 973 |
| Qcache_free_memory | 14282000 |
| Qcache_hits | 3293750 |
| Qcache_inserts | 252819 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 66645 |
| Qcache_queries_in_cache | 1342 |
| Qcache_total_blocks | 3709 |
+-------------------------+----------+
8 rows in set (0.00 sec)
Здесь:
- Qcache_free_memory - объем свободной памяти, отведенной под кэш.
- Qcache_hits - количество запросов, отработанных из кэша.
- Qcache_inserts - количество вставок запросов в кэш.
- Qcache_lowmem_prunes - количество высвобождений памяти из-за наполненности кэша.
- Qcache_not_cached - количество запросов, не подлежащих кэшированию.
- Qcache_queries_in_cache - количество запросов, находящихся в кэше в настоящее время.
Мерой эффективности кэша может служить отношение Qcache_hits / (Qcache_inserts + Qcache_not_cached).
Как избежать использования кэша?
В ряде случаев, желательно не использовать кэш. Наиболее распространены две ситуации:
- Вы делаете запросы, которые точно не повторятся и не хотите нагружать кэш.
- Вы занимаетесь оптимизацией запроса и хотите измерять время его выполнения.
Для выполнения запроса без использования кэша применяется директива SQL_NO_CACHE, которую помещают сразу после оператора SELECT. Например:
Замечание. Если сервер исполняет большое количество быстрых запросов, кэш может оказывать негативное влияние на производительность. Так как кэш обрабытывает запросы только последовательно, связанная с ним часть исполнения запроса будет использовать только одно ядро процессора. Если кэш приводит к снижению производительности, его нужно отключить в конфигурационном файле.
Альтернативные способы кэширования
Несмотря на то, что встроенный кэш запросов представляет мощный и ясный механизм, в ряде случаев он неприменим. Опишем наиболее распространенные:
- Запрос использует пользовательские функции, но тем не менее является детерменистическим.
- Оптимизация работы подзапросов потребовала разбить запрос на два с использованием временных таблиц.
- Таблица часто обновляется, аннулируя кэш, но не обязательно делать актуальную выборку.
- В таблице часто обновляются поля, не использованные в запросе (что все равно аннулирует кэш запросов к данной таблице).
Последняя причина достаточно распространена в рунете и обычно является ошибкой проектирования. Например, многие реализуют простой счетчик просмотров статьи (каюсь, и я так делал, но сейчас использую Бревно) в виде запроса UPDATE articles SET viewcount=viewcount+1 WHERE id=542. Данный запрос при каждом просмотре статьи обновляет счетчик и вместе с тем удаляет все кэшированные запросы к таблице articles. В ряде случаев, данная ошибка приводит к потере производительности в десятки раз. Для эффективного использования кэша следует выносить часто обновляемые поля в отдельную таблицу.
Предположим, таблица часто обноваляется, но нам достаточно обновлять информацию на главной раз в минуту. Мы приведем простой способ ручного кэширования запроса с помощью MEMORY-таблицы. Если результат, хранящийся в MEMORY-таблице старше одной минуты, будем выполнять запрос и заносить результат в MEMORY-таблицу.
Первое, проверим существует ли таблица с кэшированным результатом не старше одной минуты:
Анализируем значение fresh в полученной строке. Если fresh=0 или произошла ошибка, значит запрос следует выполнить и внести в MEMORY-таблицу:
CREATE TABLE cache_mainpage_query ENGINE=MEMORY
SELECT now() AS create_time, ... FROM ... WHERE ... ORDER BY ... LIMIT ...;
Затем, как в случае c=0, так и в случае c=1, выполняем запрос и возвращаем результат клиенту:
Замечание. В рассмотренном примере время создания таблицы заносится в каждую строчку временной таблицы (колонка create_time). Это неэффективно по занимаемому месту в памяти, поэтому если объем памяти станет узким местом, следует перенести время кэширования в отдельную таблицу.
Если правило сортировки сложное (например, требует подключения дополнительных таблиц или вычислений над полями), то сортировка кэшированного запроса займет время. Чтобы этого избежать, пронумеруем строки при выполнении основного запроса:
CREATE TABLE cache_mainpage_query ENGINE=MEMORY
SELECT now() AS create_time, @c:=@c+1 AS ord, ... FROM ... WHERE ... ORDER BY ... LIMIT ...;
Выборка в этом случае упрощается:
С MEMORY-таблицами связано одно ограничение: в таких таблицах нельзя хранить поля типа BLOB/TEXT. Если Вам требуется хранить поля такого типа, можно либо создать MEMORY-таблицу явно с полями типа varchar() длиной до 65535 символов, либо использовать таблицы типа MyISAM.
Мы рассмотрели далеко не все варианты альтернативного кэширования, например, за кадром оказалось кэширование с помощью memcached и кэширование готовых кусков HTML. Напишите в комментариях, какие темы из области оптимизации производительности MySQL вам интересны.
Статья написана по материалам онлайн-курса «Оптимизация производительности MySQL».
© Все права на данную статью принадлежат порталу webew.ru. Перепечатка в интернет-изданиях разрешается только с указанием автора и прямой ссылки на оригинальную статью. Перепечатка в печатных изданиях допускается только с разрешения редакции.
