10.2.1.20 Function Call Optimization — MySQL 9.2

10.2.1.20 Оптимизация вызовов функций

Функции MySQL помечаются внутри как детерминированные или недетерминированные. Функция является недетерминированной, если при фиксированных значениях её аргументов она может возвращать разные результаты при разных вызовах. Примеры недетерминированных функций: RAND(), UUID().

Если функция помечена как недетерминированная, ссылка на неё в WHERE-оператор оценивается для каждой строки (при выборке из одной таблицы) или комбинации строк (при выборке из соединения нескольких таблиц).

MySQL также определяет, когда следует вычислять функции, на основе типов аргументов, являются ли аргументы столбцами таблицы или константными значениями. Детерминированная функция, принимающая столбец таблицы в качестве аргумента, должна быть вычислена всякий раз, когда значение этого столбца изменяется.

Недетерминированные функции могут повлиять на производительность запросов. Например, некоторые оптимизации могут быть недоступны или может потребоваться больше блокировок. В следующем обсуждении используется RAND(), но это также применимо и к другим недетерминированным функциям.

Предположим, что таблица t имеет такое определение:

CREATE TABLE t (id INT NOT NULL PRIMARY KEY, col_a VARCHAR(100));

Рассмотрим эти два запроса:

SELECT * FROM t WHERE id = POW(1,2);
SELECT * FROM t WHERE id = FLOOR(1 + RAND() * 49);

Оба запроса, по-видимому, используют поиск по первичному ключу из-за сравнения с первичным ключом, но это верно только для первого из них:

Первый запрос всегда возвращает не более одной строки, потому что POW() с константными аргументами является константным значением и используется для поиска по индексу.
Второй запрос содержит выражение, использующее недетерминированную функцию RAND(), которая не является константой в запросе, но фактически имеет новое значение для каждой строки таблицы t. Следовательно, запрос читает каждую строку таблицы, оценивает предикат для каждой строки и выводит все строки, для которых первичный ключ соответствует случайному значению. Это может быть ноль, одна или несколько строк, в зависимости от значений столбца id и значений в последовательности RAND().

Влияние недетерминированности не ограничивается операторами SELECT. Этот оператор UPDATE использует недетерминированную функцию для выбора строк, которые нужно изменить:

UPDATE t SET col_a = some_expr WHERE id = FLOOR(1 + RAND() * 49);

Предполагается, что цель состоит в том, чтобы обновить не более одной строки, для которой первичный ключ соответствует выражению. Однако это может быть ноль, одна или несколько строк, в зависимости от значений столбца id и значений в последовательности RAND().

Описанное поведение имеет последствия для производительности и репликации:

Поскольку недетерминированная функция не производит постоянное значение, оптимизатор не может использовать стратегии, которые в противном случае могли бы быть применимы, например, поиск по индексу. Результатом может стать сканирование таблицы.
InnoDB может перейти к блокировке диапазона ключей, а не к блокировке одной строки для одной совпадающей строки.
Обновления, которые не выполняются детерминированно, небезопасны для репликации.

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

Переместите выражение, содержащее недетерминированную функцию, в отдельный оператор, сохранив значение в переменной. В исходном операторе замените выражение ссылкой на переменную, которую оптимизатор может рассматривать как константное значение:
```
SET @keyval = FLOOR(1 + RAND() * 49);
UPDATE t SET col_a = some_expr WHERE id = @keyval;
```
Присвойте случайное значение переменной в таблице с выводом. Этот метод приводит к тому, что переменная получает значение один раз до её использования в сравнении в WHERE-оператор:
```
UPDATE /*+ NO_MERGE(dt) */ t, (SELECT FLOOR(1 + RAND() * 49) AS r) AS dt
SET col_a = some_expr WHERE id = dt.r;
```

Как упоминалось ранее, недетерминированное выражение в WHERE-операторе может препятствовать оптимизациям и привести к сканированию таблицы. Однако частичная оптимизация WHERE-оператора может быть возможна, если другие выражения являются детерминированными. Например:

SELECT * FROM t WHERE partial_key=5 AND some_column=RAND();

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