Кэширование запросов в MySQL

17 августа 2008, 21:02

Автор: Григорий Рубцов [rgbeast]

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

Встроенный механизм кэширования запросов в MySQL.

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

mysql> show variables like 'query_cache%';

+------------------------------+---------+

| 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 будут для кэша двумя разными запросами.
В кэш всегда попадает результат выполнения запроса целиком, результаты выполнения подзапросов не кэшируются.
Кэш работает одинаково для запросов к таблицам с различными механизмами хранения.
Ряд запросов не подлежит кэшированию:
- Запросы, содержащие одну из недетерминированных функций: NOW(), SLEEP(), RAND(), CURTIME(), LAST_INSERT_ID() и.др.
- Запросы, использующие функции или хранимые процедуры, определенные пользователем.
- Запросы, использующие значения локальных переменных.
- Запросы, обращающиеся к базам данных mysql или INFORMATION_SCHEMA.
- Запросы типа SELECT ... FOR UPDATE, SELECT ... IN SHARE MODE, SELECT ... INTO OUTFILE, SELECT ... INTO DUMPFILE, SELECT * FROM ... WHERE autoincrement_col IS NULL.
- Запросы, использующие временные таблицы.
- Запросы, не обращающиеся к таблицам.
- Запросы, которые генерируют предупреждения (warnings).
- В случае, если пользователь имеет права не на всю таблицу, а только на определенные колонки таблицы. Это исключение — следствие того, что кэш запросов один для всех пользователей, а права доступа средствами кэша проверяются лишь на уровне таблиц.

В качестве примера запроса, который может быть кэширован, приведем запрос, формирующий список обсуждений на главной странице webew.ru:

SELECT t.*, (SELECT body FROM entities WHERE id=t.lastid) body

                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

Запрос выполняет сортировку статей и тем по дате последнего комментария. Обращения к главной странице сайта происходят чаще, чем добавление комментариев или статей, поэтому кэш достаточно эффективен.

Текущее состояние кэша

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

mysql> SHOW GLOBAL STATUS LIKE 'Qcache%';

+-------------------------+----------+

| 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. Например:

SELECT SQL_NO_CACHE id,title,author FROM news where message like '%швабра%';

Альтернативные способы кэширования

Несмотря на то, что встроенный кэш запросов представляет мощный и ясный механизм, в ряде случаев он неприменим. Опишем наиболее распространенные:

Запрос использует пользовательские функции, но тем не менее является детерменистическим.
Оптимизация работы подзапросов потребовала разбить запрос на два с использованием временных таблиц.
Таблица часто обновляется, аннулируя кэш, но не обязательно делать актуальную выборку.
В таблице часто обновляются поля, не использованные в запросе (что все равно аннулирует кэш запросов к данной таблице).

Последняя причина достаточно распространена в рунете и обычно является ошибкой проектирования. Например, многие реализуют простой счетчик просмотров статьи (каюсь, и я так делал, но сейчас использую Бревно) в виде запроса UPDATE articles SET viewcount=viewcount+1 WHERE id=542. Данный запрос при каждом просмотре статьи обновляет счетчик и вместе с тем удаляет все кэшированные запросы к таблице articles. В ряде случаев, данная ошибка приводит к потере производительности в десятки раз. Для эффективного использования кэша следует выносить часто обновляемые поля в отдельную таблицу.

Предположим, таблица часто обноваляется, но нам достаточно обновлять информацию на главной раз в минуту. Мы приведем простой способ ручного кэширования запроса с помощью MEMORY-таблицы. Если результат, хранящийся в MEMORY-таблице старше одной минуты, будем выполнять запрос и заносить результат в MEMORY-таблицу.

Первое, проверим существует ли таблица с кэшированным результатом не старше одной минуты:

SELECT (create_time > now() - INTERVAL 1 MINUTE) 
fresh FROM cache_mainpage_query LIMIT 1;

Анализируем значение fresh в полученной строке. Если fresh=0 или произошла ошибка, значит запрос следует выполнить и внести в MEMORY-таблицу:

DROP TABLE IF EXISTS 'cache_mainpage_query';

CREATE TABLE cache_mainpage_query ENGINE=MEMORY

        SELECT now() AS create_time, ... FROM ... WHERE ... ORDER BY ... LIMIT ...;

Затем, как в случае c=0, так и в случае c=1, выполняем запрос и возвращаем результат клиенту:

SELECT * FROM cache_mainpage_query ORDER BY ...;

Замечание. В рассмотренном примере время создания таблицы заносится в каждую строчку временной таблицы (колонка create_time). Это неэффективно по занимаемому месту в памяти, поэтому если объем памяти станет узким местом, следует перенести время кэширования в отдельную таблицу.

Если правило сортировки сложное (например, требует подключения дополнительных таблиц или вычислений над полями), то сортировка кэшированного запроса займет время. Чтобы этого избежать, пронумеруем строки при выполнении основного запроса:

SET @c:=0;

CREATE TABLE cache_mainpage_query ENGINE=MEMORY

        SELECT now() AS create_time, @c:=@c+1 AS ord, ... FROM ... WHERE ... ORDER BY ... LIMIT ...;

Выборка в этом случае упрощается:

SELECT * FROM cache_mainpage_query ORDER BY ord;

С MEMORY-таблицами связано одно ограничение: в таких таблицах нельзя хранить поля типа BLOB/TEXT. Если Вам требуется хранить поля такого типа, можно либо создать MEMORY-таблицу явно с полями типа varchar() длиной до 65535 символов, либо использовать таблицы типа MyISAM.

Мы рассмотрели далеко не все варианты альтернативного кэширования, например, за кадром оказалось кэширование с помощью memcached и кэширование готовых кусков HTML. Напишите в комментариях, какие темы из области оптимизации производительности MySQL вам интересны.

Статья написана по материалам онлайн-курса «Оптимизация производительности MySQL».

© Все права на данную статью принадлежат порталу webew.ru. Перепечатка в интернет-изданиях разрешается только с указанием автора и прямой ссылки на оригинальную статью. Перепечатка в печатных изданиях допускается только с разрешения редакции.

Материал взят с сайта: http://webew.ru/articles/1041.webew

Главная > Программы > База данных > MySQL