Rin
*
Кэширование функций и подзапросов в MySQL (эмуляция)
Как известно, в настоящий момент MySQL не кэширует результат работы подзапросов и функций, честно отрабатывая их при каждом запросе. Большинство коммерческих БД это делают. И правильно -- это даёт прирост производительности.
Я попробовал сделать эксперимент с кэшированием функций в MySQL-5.0.51.
Мне нужно было увеличить скорость выполнения некоторого SELECT-запроса с коррелированным подзапросом. Причём набор входных данных, исходящий от запроса к подзапросу часто повторялся, а сам подзапрос возвращал скалярное значение. Это и послужило поводом к оптимизации через кэширование. В итоге мне удалось увеличить скорость работы запроса в 2 раза. Ниже описываю алгоритм и привожу примеры кода на PHP и SQL.
Ключевые шаги выполнения PHP-скрипта:
1) Создаём кэш-таблицу в памяти БД, если она ещё не создана.
2) Удаляем из таблицы устаревшие данные (сборщик мусора).
3) Выполняем SELECT-запрос. В том месте, где д.б. подзапрос, пишем имя функции с параметрами. Сам подзапрос помещаем в эту функцию.
Ключевые шаги выполнения SQL-функции:
1) Внутри функции делаем быстрый запрос к кэш-таблице, используя id соединения и хэш значений параметров функции.
2) Если данные есть, возвращаем результат, если нет, то:
3) Выполняем основной запрос (бывший подзапрос).
4) Сохраняем результат в кэш-таблицу, если его еще там нет.
PHP код (интерфейс к БД -- класс MysqlRapid):
Пример SQL кода основного запроса:
Пример SQL кода функции (CMS_PARTS_MANAGER_STAT.sql):
Нужно отметить, что здесь кэширование происходит в рамках соединения, а не в рамках запроса, как в настоящем (встроенном) кэшировании. Если нужно ограничиться только запросом, придётся удалить кэш для текущего соединения сразу после выполнения запроса. Еще одно (несущественное?) ограничение -- кэширование только скалярных значений.
Как известно, в настоящий момент MySQL не кэширует результат работы подзапросов и функций, честно отрабатывая их при каждом запросе. Большинство коммерческих БД это делают. И правильно -- это даёт прирост производительности.
Я попробовал сделать эксперимент с кэшированием функций в MySQL-5.0.51.
Мне нужно было увеличить скорость выполнения некоторого SELECT-запроса с коррелированным подзапросом. Причём набор входных данных, исходящий от запроса к подзапросу часто повторялся, а сам подзапрос возвращал скалярное значение. Это и послужило поводом к оптимизации через кэширование. В итоге мне удалось увеличить скорость работы запроса в 2 раза. Ниже описываю алгоритм и привожу примеры кода на PHP и SQL.
Ключевые шаги выполнения PHP-скрипта:
1) Создаём кэш-таблицу в памяти БД, если она ещё не создана.
2) Удаляем из таблицы устаревшие данные (сборщик мусора).
3) Выполняем SELECT-запрос. В том месте, где д.б. подзапрос, пишем имя функции с параметрами. Сам подзапрос помещаем в эту функцию.
Ключевые шаги выполнения SQL-функции:
1) Внутри функции делаем быстрый запрос к кэш-таблице, используя id соединения и хэш значений параметров функции.
2) Если данные есть, возвращаем результат, если нет, то:
3) Выполняем основной запрос (бывший подзапрос).
4) Сохраняем результат в кэш-таблицу, если его еще там нет.
PHP код (интерфейс к БД -- класс MysqlRapid):
PHP:
$cms->db->query('CREATE TABLE IF NOT EXISTS _func_cache
(
params_sha1 BINARY(20) NOT NULL,
result VARCHAR(1024) NOT NULL,
connection_id INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (params_sha1, connection_id)
) ENGINE=MEMORY');
#сборщик мусора для кэша SQL функций: удаляем кэш для закрытых (уже отработавших) процессов
$connection_ids = $cms->db->selectCol('SHOW PROCESSLIST');
$cms->db->query('DELETE FROM _func_cache
WHERE connection_id NOT IN(' . $cms->db->quoteArray($connection_ids) . ')');
Код:
SELECT d.*,
CMS_PARTS_MANAGER_STAT(d.parts_manager_id) AS parts_manager_stat
FROM table1 AS d
WHERE ...
Код:
DELIMITER //
DROP FUNCTION IF EXISTS CMS_PARTS_MANAGER_STAT //
CREATE FUNCTION CMS_PARTS_MANAGER_STAT(user_id INT) RETURNS VARCHAR(255)
BEGIN
DECLARE params_sha1 BINARY(20);
DECLARE result VARCHAR(255);
IF user_id IS NULL THEN RETURN NULL;
END IF;
SET params_sha1 := UNHEX(SHA1(CONCAT_WS('|', user_id)));
-- быстрый запрос к кэш-таблице
SELECT fc.result
INTO result
FROM _func_cache AS fc
WHERE fc.params_sha1 = params_sha1 AND connection_id = CONNECTION_ID();
IF result IS NOT NULL THEN RETURN result;
END IF;
-- бывший подзапрос
SELECT CONCAT(COUNT(*),
' / ',
SUM(request.parts_reply_count)
)
INTO result
FROM request
WHERE request.parts_manager_id = user_id
AND request.parts_status IN ('emailed', 'phoned', 'inaccessible')
AND request.status = 'public';
IF result IS NULL THEN RETURN result;
END IF;
INSERT IGNORE INTO _func_cache
SET params_sha1 = params_sha1,
result = result,
connection_id = CONNECTION_ID();
RETURN result;
END;
//
DELIMITER ;
