Отправить заявку

MySQL: Оптимизируем Query Cache

Query Cache кеширует результаты запроса. Это значит, что кешированный запрос не выполняется вовсе. Более того, кеширование отрабатывает даже до разбора самого запроса. А это значит, что запрос должен быть точно таким же как в кеше, вплоть до байта.

За настройку кэширования отвечают системные переменные начинающиеся с 'query_cache_'

mysql>SHOW VARIABLES LIKE 'query_cache_%';
+------------------------------+-----------+
| Variable_name                | Value     
+------------------------------+-----------+
| query_cache_limit            | 1048576   
| query_cache_min_res_unit     | 1024      
| query_cache_size             | 256000000 
| query_cache_type             | ON        
| query_cache_wlock_invalidate | OFF       
+------------------------------+-----------+
7 rows in SET (0.00 sec)

Во-первых, убедимся, что кэширование включено. Переменная query_cache_type должна иметь значение ON(1) или DEMAND(2) и query_cache_size быть отличной от нуля.

При 'query_cache_type = ON' кэшируются все запросы, кроме содержащих хинт SQL_NO_CACHE и некоторых исключений.
При 'query_cache_type = DEMAND' кэшируются только запросы начинающиеся с SELECT SQL_CACHE.

query_cache_min_res_unit минимальный размер выделяемого блока памяти для хранения результатов кэшированного запроса. MySQL не хранит кэш в одном большом куске памяти, вместо этого по требованию выделяются блоки с минимальным размером query_cache_min_res_unit(=4KB по умолчанию). Последний такой блок обрезается до размера данных, а оставшаяся память освобождается.
И если у вас много небольших запросов в кэше, то это может привести к фрагментации памяти из-за большого количества свободных блоков. А это, в свою очередь, вызывает удаление кэшированных записей из-за недостатка памяти. В таком случае имеет смысл уменьшить значение query_cache_min_res_unit. Если большинство ваших запросов порождают большой результат, то увеличение этого параметра может повысить производительность.

Для мониторинга query cache используется

SHOW STATUS

Query Cache используется не всегда и не для всех типов запросов. Если размер результата запроса превышает query_cache_limit(=1MB по умолчанию), то он не кэшируется. MySQL до версии 4.1.1 не поддерживает query cache в транзакциях.

  • Запросы с временными таблицами или вовсе не использующие таблицы;
  • Запросы генерирующие предупреждения(warnings);
  • Запросы которые являются подзапросами внешнего запроса;
  • Запросы внутри хранимых процедур и функций;
  • Запросы с SQL_NO_CACHE
  • Подготовленные запросы (Prepared statements);
  • Запросы в которых используются функции:
    BENCHMARK(), CONNECTION_ID(), CONVERT_TZ(), CURDATE(), CURRENT_DATE(), CURRENT_TIME(), CURRENT_TIMESTAMP(), CURTIME(), DATABASE(), ENCRYPT() с одним аргументом, FOUND_ROWS(), GET_LOCK(), LAST_INSERT_ID(), LOAD_FILE(), MASTER_POS_WAIT(), NOW(), RAND(), RELEASE_LOCK(), SLEEP(), SYSDATE(), UNIX_TIMESTAMP() без аргументов, USER(), UUID();
  • Запросы использующие хранимые функции, пользовательские переменные или ссылающиеся на таблицы в системных базах mysql или INFORMATION_SCHEMA;
  • Запросы имеющие следующие формы:
    SELECT ... IN SHARE MODE
    SELECT ... FOR UPDATE
    SELECT ... INTO OUTFILE ...
    SELECT ... INTO DUMPFILE ...
    SELECT * FROM ... WHERE autoincrement_col IS NULL
Другие публикации