Полезный код | developer.donnoval.ru

MySQL: query_cache_size в MySQL и MariaDB: Query Cache и современные альтернативы

query_cache_size управляет размером встроенного кеша результатов SQL-запросов. Однако в современных версиях MySQL этот механизм больше не существует: Query Cache был объявлен устаревшим в MySQL 5.7.20 и полностью удалён из MySQL 8.0.

MariaDB сохранила Query Cache, но он отключён по умолчанию и подходит только для ограниченного числа сценариев. Для большинства современных проектов надёжнее сначала оптимизировать SQL-запросы и индексы, а повторно используемые данные кешировать на уровне приложения — например, в Redis.

Что решаем

Разбираемся, почему параметры query_cache_size, query_cache_type и query_cache_limit больше не работают в MySQL 8+, как проверить текущий сервер и чем заменить старый Query Cache.

Query Cache обычно не подходит, если:

  • используется MySQL 8.0 или новее;
  • в таблицах часто выполняются INSERT, UPDATE и DELETE;
  • текст похожих запросов постоянно различается;
  • результаты зависят от пользователя, прав доступа, языка или региона;
  • приложению требуется точное управление TTL и инвалидацией;
  • нужно кешировать данные сразу для нескольких серверов приложений.

Различия MySQL и MariaDB

  • MySQL 5.7.20: Query Cache объявлен устаревшим.
  • MySQL 8.0 и новее: Query Cache удалён, переменные query_cache_* отсутствуют.
  • MariaDB: Query Cache сохраняется, но обычно неактивен по умолчанию.

Конфигурация с query_cache_size не является переносимой между MySQL и MariaDB. Сначала необходимо определить, какой именно сервер установлен.

Проверка версии и наличия Query Cache

SELECT
	VERSION() AS Version,
	@@version_comment AS Distribution;

SHOW VARIABLES LIKE 'have_query_cache';

SHOW VARIABLES LIKE 'query_cache%';

В MySQL 8.0 и новее запрос SHOW VARIABLES LIKE 'query_cache%' не вернёт соответствующих переменных.

В MariaDB можно увидеть параметры:

  • query_cache_type — режим работы;
  • query_cache_size — общий объём кеша;
  • query_cache_limit — максимальный размер одного результата;
  • query_cache_min_res_unit — минимальный размер блока.

MariaDB: включение Query Cache

Включать Query Cache стоит только после замеров на реальной нагрузке. Для начала безопаснее использовать режим DEMAND: в кеш попадут только запросы, явно отмеченные как SQL_CACHE.

Пример конфигурации /etc/my.cnf.d/server.cnf или другого файла, подключённого из my.cnf:

[mysqld]

# Кешировать только запросы с SELECT SQL_CACHE
query_cache_type=DEMAND

# Начальный размер для тестирования
query_cache_size=16M

# Не сохранять слишком большие результаты
query_cache_limit=1M

После изменения конфигурации:

sudo systemctl restart mariadb

sudo systemctl status mariadb --no-pager

Запрос, который можно кешировать при режиме DEMAND:

SELECT SQL_CACHE
	id,
	title,
	published_at
FROM articles
WHERE status = 'published'
ORDER BY published_at DESC
LIMIT 20;

Размер 16M приведён как стартовое значение для теста, а не как универсальная настройка. Увеличение кеша не гарантирует ускорения. Слишком большой Query Cache может усилить конкуренцию за блокировки и замедлить сервер.

Проверка эффективности Query Cache

SHOW GLOBAL STATUS
WHERE Variable_name IN (
	'Qcache_hits',
	'Qcache_inserts',
	'Qcache_not_cached',
	'Qcache_lowmem_prunes',
	'Qcache_queries_in_cache',
	'Qcache_free_memory',
	'Qcache_free_blocks',
	'Qcache_total_blocks'
);
  • Qcache_hits — сколько результатов было получено из кеша;
  • Qcache_inserts — сколько результатов добавлено;
  • Qcache_not_cached — сколько запросов не удалось кешировать;
  • Qcache_lowmem_prunes — сколько записей удалено из-за нехватки памяти;
  • Qcache_free_blocks — количество свободных блоков, косвенно указывающее на фрагментацию.

Если число вставок и удалений из-за нехватки памяти быстро растёт, а повторных попаданий мало, Query Cache не приносит пользы.

Очистка и дефрагментация в MariaDB

-- Дефрагментирует кеш, но не удаляет результаты
FLUSH QUERY CACHE;

-- Полностью очищает результаты
RESET QUERY CACHE;

FLUSH QUERY CACHE не очищает кеш полностью. В старой версии страницы эта команда могла восприниматься как команда удаления кеша.

Сначала оптимизируйте сам SQL-запрос

Кеш не исправляет плохие индексы, чтение лишних строк, запросы N+1 и неудачную структуру таблиц. Перед внедрением Redis или ProxySQL проверьте план выполнения.

EXPLAIN
SELECT
	id,
	title,
	published_at
FROM articles
WHERE status = 'published'
ORDER BY published_at DESC
LIMIT 20;

Для такого запроса может подойти составной индекс:

CREATE INDEX idx_articles_status_published_at
	ON articles(status, published_at);

После создания индекса снова выполните EXPLAIN и сравните:

  • используемый индекс в поле key;
  • предполагаемое число прочитанных строк;
  • наличие Using filesort;
  • наличие полного сканирования таблицы;
  • фактическое время ответа приложения.

Поиск медленных запросов

Для временной диагностики можно включить журнал медленных запросов:

[mysqld]

slow_query_log=ON
long_query_time=0.5

Проверка:

SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'slow_query_log_file';
SHOW GLOBAL STATUS LIKE 'Slow_queries';

Slow Query Log может содержать значения из SQL-запросов. Ограничьте доступ к файлу, настройте ротацию и не храните журнал дольше необходимого.

Redis и кеш приложения

Для MySQL 8+ наиболее управляемый вариант — паттерн cache-aside:

  • приложение сначала проверяет Redis;
  • при промахе выполняет запрос к MySQL или MariaDB;
  • сохраняет результат в Redis с ограниченным TTL;
  • после изменения данных удаляет соответствующий ключ;
  • при недоступности Redis продолжает работать напрямую с базой данных.

Пример cache-aside на PHP 8 и ext-redis

<?php

declare(strict_types=1);

final class ProductRepository
{
	public function __construct(
		private PDO $pdo,
		private Redis $redis
	) {
		$this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
		$this->pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
	}

	public function getProduct(int $productId): ?array
	{
		if ($productId <= 0) {
			throw new InvalidArgumentException('Некорректный идентификатор товара');
		}

		$cacheKey = 'product:v1:' . $productId;

		try {
			$cachedValue = $this->redis->get($cacheKey);

			if ($cachedValue !== false) {
				if ($cachedValue === '__NULL__') {
					return null;
				}

				$decodedValue = json_decode(
					$cachedValue,
					true,
					512,
					JSON_THROW_ON_ERROR
				);

				return is_array($decodedValue) ? $decodedValue : null;
			}
		} catch (RedisException | JsonException $exception) {
			error_log('Redis cache read failed: ' . $exception->getMessage());
		}

		$statement = $this->pdo->prepare(
			'SELECT id, name, price, updated_at
			FROM products
			WHERE id = :productId
			LIMIT 1'
		);

		$statement->bindValue(':productId', $productId, PDO::PARAM_INT);
		$statement->execute();

		$product = $statement->fetch();

		try {
			if ($product === false) {
				$this->redis->setEx($cacheKey, 30, '__NULL__');

				return null;
			}

			$this->redis->setEx(
				$cacheKey,
				300,
				json_encode(
					$product,
					JSON_THROW_ON_ERROR | JSON_UNESCAPED_UNICODE
				)
			);
		} catch (RedisException | JsonException $exception) {
			error_log('Redis cache write failed: ' . $exception->getMessage());
		}

		return $product;
	}

	public function invalidateProduct(int $productId): void
	{
		if ($productId <= 0) {
			throw new InvalidArgumentException('Некорректный идентификатор товара');
		}

		try {
			$this->redis->del('product:v1:' . $productId);
		} catch (RedisException $exception) {
			error_log('Redis cache invalidation failed: ' . $exception->getMessage());
		}
	}
}

Подключение

<?php

$pdo = new PDO(
	'mysql:host=127.0.0.1;dbname=app;charset=utf8mb4',
	'app_user',
	'secret',
	[
		PDO::ATTR_EMULATE_PREPARES => false,
		PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
	]
);

$redis = new Redis();
$redis->connect('127.0.0.1', 6379, 1.5);

$repository = new ProductRepository($pdo, $redis);

$product = $repository->getProduct(123);

var_dump($product);

После успешного изменения товара ключ нужно удалить:

<?php

$pdo->beginTransaction();

try {
	$statement = $pdo->prepare(
		'UPDATE products
		SET price = :price
		WHERE id = :productId'
	);

	$statement->execute([
		':price' => 1990,
		':productId' => 123,
	]);

	$pdo->commit();

	$repository->invalidateProduct(123);
} catch (Throwable $exception) {
	if ($pdo->inTransaction()) {
		$pdo->rollBack();
	}

	throw $exception;
}

Сначала фиксируется изменение в базе данных, затем удаляется ключ Redis. Следующее чтение загрузит актуальное значение и заново заполнит кеш.

ProxySQL Query Cache

ProxySQL располагается между приложением и MySQL/MariaDB и может кешировать результаты подходящих запросов без изменения кода приложения.

Пример правила с TTL 5 секунд:

INSERT INTO mysql_query_rules (
	rule_id,
	active,
	username,
	schemaname,
	match_digest,
	cache_ttl,
	apply
)
VALUES (
	100,
	1,
	'report_reader',
	'app',
	'^SELECT .* FROM public_report',
	5000,
	1
);

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

cache_ttl указывается в миллисекундах.

Ограничения ProxySQL Query Cache:

  • результаты не инвалидируются автоматически при изменении таблиц;
  • устаревшие данные сохраняются до окончания TTL;
  • правило должно быть максимально узким;
  • нельзя бездумно кешировать все запросы, начинающиеся с SELECT;
  • Query Cache ProxySQL несовместим с некоторыми сценариями Prepared Statements;
  • персональные и конфиденциальные результаты требуют отдельной изоляции.

Проверка статистики выполняется в административном интерфейсе ProxySQL:

SELECT *
FROM stats_mysql_query_cache;

ProxySQL подходит для короткоживущего кеширования публичных отчётов, справочников и других предсказуемых запросов, где допустима небольшая задержка обновления данных.

Какой вариант выбрать

  • MySQL 8.0 и новее: оптимизация SQL, индексы и Redis либо другой кеш приложения.
  • MariaDB с редкими изменениями данных: Query Cache можно проверить в режиме DEMAND.
  • Приложение нельзя изменять: можно рассмотреть ProxySQL с коротким TTL и узкими правилами.
  • Один PHP-сервер и небольшие объёмы: для локального кеша может подойти APCu.
  • Готовая HTML-страница: эффективнее использовать FastCGI Cache или другой full-page cache, а не кешировать отдельный SQL-запрос.

Краевые случаи

  • Cache stampede: после одновременного истечения TTL множество процессов может обратиться к БД. Для популярных ключей используют блокировку или предварительное обновление.
  • Устаревшие данные: TTL ограничивает срок жизни значения, но не заменяет явную инвалидацию.
  • Отрицательный кеш: отсутствующие записи можно кешировать на короткий срок, чтобы снизить нагрузку от повторных запросов.
  • Версионирование: добавляйте версию в ключ, например product:v2:123, если формат данных изменился.
  • Мультитенантность: включайте идентификатор клиента в ключ кеша.
  • Локализация: язык, валюта, регион и права доступа должны быть частью ключа.
  • Случайные запросы: запросы с NOW(), RAND() и постоянно меняющимися условиями обычно плохо подходят для кеширования.

Безопасность

  • Кеширование не защищает от SQL-инъекций — используйте подготовленные выражения PDO.
  • Не сохраняйте в кеше пароли, токены, секретные ключи и полные платёжные данные.
  • Не используйте общий ключ для данных разных пользователей или организаций.
  • Не открывайте Redis-порт 6379 в интернет.
  • Для удалённого Redis используйте ACL, защищённую сеть и TLS.
  • Ограничьте доступ к административному интерфейсу ProxySQL.
  • Не помещайте пароль базы данных непосредственно в публичный исходный код.

Производительность

Оценивайте кеширование по измеримым показателям:

  • P50, P95 и P99 времени ответа;
  • количество запросов к базе на один HTTP-запрос;
  • доля попаданий и промахов кеша;
  • нагрузка CPU и диска базы данных;
  • объём памяти Redis или Query Cache;
  • частота инвалидации;
  • доля устаревших ответов, если они допустимы.

Высокий hit rate сам по себе не гарантирует пользу. Если кешируются дешёвые запросы, стоимость сериализации, сети и управления ключами может оказаться выше стоимости обращения к базе данных.

Тест и проверка

Проверка Redis:

redis-cli PING

redis-cli INFO stats

redis-cli INFO memory

Ожидаемый ответ на первую команду:

PONG

Проверка конкретного ключа:

redis-cli GET product:v1:123

redis-cli TTL product:v1:123

Порядок нагрузочной проверки:

  1. измерить запрос без кеша;
  2. выполнить первый запрос с пустым кешем;
  3. повторить тот же запрос и убедиться, что он читается из кеша;
  4. изменить запись в базе;
  5. удалить ключ;
  6. повторить запрос и проверить обновлённые данные;
  7. временно остановить Redis и убедиться, что приложение продолжает читать данные из БД.

Совместимость

  • MySQL 5.7: Query Cache существует, но устарел.
  • MySQL 8.0 и новее: Query Cache отсутствует.
  • MariaDB: Query Cache доступен, если сервер не собран без его поддержки.
  • PHP: пример рассчитан на PHP 8.0+.
  • Redis: используется расширение ext-redis.
  • ProxySQL: синтаксис и ограничения следует сверять с установленной версией.

Альтернативы

  • Redis: общий кеш для нескольких PHP-FPM-серверов.
  • APCu: быстрый локальный кеш внутри одного сервера.
  • ProxySQL: кеширование на уровне прокси без изменения приложения.
  • FastCGI Cache: кеширование целых HTML-ответов nginx.
  • Read Replica: распределение тяжёлых операций чтения.
  • Сводные таблицы: предварительный расчёт тяжёлых отчётов.
  • Оптимизация SQL: индексы, устранение N+1, уменьшение выборки и корректная пагинация.

Документация

up: Просмотров: 2.8k
Материал помог? Оценка помогает находить устаревшие и спорные решения.
Не сработало Есть нюансы Полезно
Оценки до обновления материала
  • Полезно1

Комментарии

2

Нашли ошибку или хотите дополнить материал?

Войти и написать
Профессор кислых щей
# тип кэширования запросов
 query_cache_type=1

Какие ещё типы запросов есть?
developer
Да, но:
Кэш запросов устарел, начиная с MySQL 5.7.20, и удален в MySQL 8.0.
Telegram