Один запрос: 4 мс с литералами и 3 секунды из Laravel. Как плейсхолдеры в IN выключают loose index scan в MySQL

Наша панель для управления серверами хранит метрики в таблице server_metrics. В октябре в ней было 2,26 млн строк, и на проде страницы со списком серверов открывались по несколько секунд. Виноватым оказался один запрос. С подставленными значениями он выполнялся за 4–8 мс, а с биндингами, как его отправляет приложение, за 2,3–3,3 секунды.

Локально и в тестах этого видно не было. Тесты гоняются на SQLite, а в локальной базе мало данных.

Откуда запрос

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

public function latestMetric()
{
    return $this->hasOne(ServerMetric::class)->latestOfMany();
}

На странице со списком серверы грузятся вместе с ней через with('latestMetric'). Для такой жадной загрузки (eager loading) Laravel строит подзапрос с MAX и GROUP BY server_id, а id всех серверов со страницы уходят в where server_id in (...). На проде latestOfMany мерили и по id, и по created_at. Оба варианта стоили от 0,5 до 2,3 секунды на каждую загрузку.

Если убрать обвязку Laravel, остается вот что:

select max(created_at), server_id
from server_metrics
where server_id in (?, ?, ?)
group by server_id

На таблице есть индекс (server_id, created_at). Для такого запроса MySQL умеет делать loose index scan. Для каждого server_id он берет из индекса одну крайнюю запись и остальные метрики сервера не читает. В EXPLAIN это видно по Using index for group-by

Где теряется loose index scan

С подставленными значениями все так и работает. Приложение отправляет запрос по‑другому. В Laravel эмуляция prepared statements по умолчанию выключена, это видно в Illuminate/Database/Connectors/Connector.php:

protected $options = [
    PDO::ATTR_CASE => PDO::CASE_NATURAL,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_ORACLE_NULLS => PDO::NULL_NATURAL,
    PDO::ATTR_STRINGIFY_FETCHES => false,
    PDO::ATTR_EMULATE_PREPARES => false,
];

PDO отправляет на сервер текст запроса с вопросительными знаками, а значения передает отдельно. С вопросительными знаками внутри IN оптимизатор loose index scan уже не выбирает.

Это видно прямо в консоли MySQL. Для проверки поднят стенд, MySQL 8.0.46 в Docker и таблица на 2,25 млн строк, 50 серверов по 45 000 метрик. Сначала значения через пользовательские переменные, потом тот же запрос через PREPARE с плейсхолдерами:

SELECT id INTO @a FROM servers ORDER BY id LIMIT 0, 1;
SELECT id INTO @b FROM servers ORDER BY id LIMIT 1, 1;

EXPLAIN SELECT MAX(created_at), server_id FROM server_metrics
WHERE server_id IN (@a, @b) GROUP BY server_id;
-- type: range, rows: 399, Extra: Using where; Using index for group-by

PREPARE s FROM 'EXPLAIN SELECT MAX(created_at), server_id FROM server_metrics
WHERE server_id IN (?, ?) GROUP BY server_id';
EXECUTE s USING @a, @b;
-- type: range, rows: 181170, Extra: Using where; Using index

Во втором плане Using index for group-by пропал, и MySQL проходит все метрики обоих серверов подряд.

Сам PREPARE тут ни при чем. Если подготовить через него запрос, где значения вписаны в текст, loose index scan остается. Не мешает и один плейсхолдер, IN (?) с одним значением и server_id = ? тоже получают Using index for group-by. План ломается, когда в IN два значения и больше. При 40 и 50 значениях MySQL уже проходит индекс целиком, в EXPLAIN это type: index и 2,24 млн строк.

По времени на стенде выходит 2 мс со значениями в тексте и 1,6 секунды с плейсхолдерами. Разница в 800 раз. С включенной эмуляцией PDO сам вписывает значения в текст, и запрос снова идет за 2 мс. На MySQL 9.2 картина такая же.

Похожий случай в баг‑трекере MySQL есть, это баг #115227. Там запрос через prepared statement вместо range scan шел по ref, читал 123 тысячи строк и выполнялся 270 мс вместо 0,26. Его исправили в MySQL 9.1, а план с плейсхолдерами в IN на 9.2 все тот же. Отчета именно про этот случай я не нашел.

Почему с целыми id этого не видно

Если ключи у моделей целые, Laravel обходит проблему сам. Как подставлять id в жадную загрузку, решает метод в Illuminate/Database/Eloquent/Relations/Relation.php:

protected function whereInMethod(Model $model, $key)
{
    return $model->getKeyName() === last(explode('.', $key))
        && in_array($model->getKeyType(), ['int', 'integer'])
            ? 'whereIntegerInRaw'
            : 'whereIn';
}

Целые ключи вписываются прямо в текст запроса через whereIntegerInRaw, строковые уходят биндингами. У серверов в панели ключи UUID, поэтому запрос и попал под плейсхолдеры.

На том же стенде два одинаковых отношения hasOne()->latestOfMany('created_at')У одного родитель с UUID, у другого с целым id. Жадная загрузка 50 серверов заняла 1 643 мс с 50 биндингами против 7 мс без единого биндинга

Под удар попадают приложения на MySQL, где у моделей UUID или ULID, а на большой таблице стоит latestOfMany, oldestOfMany или ofMany. То же касается любого своего запроса с whereIn на несколько значений, groupBy и max или min

Как проверить у себя

Проще всего взять свой запрос с GROUP BY и сравнить два EXPLAIN, как выше. Значения сначала через SET @a, потом тот же текст через PREPARE и EXECUTE ... USING. Если Using index for group-by есть только в первом, приложение работает по второму плану.

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

CREATE TABLE servers (id CHAR(36) PRIMARY KEY);
CREATE TABLE server_metrics (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  server_id CHAR(36) NOT NULL,
  cpu DECIMAL(5,2) NOT NULL,
  created_at TIMESTAMP NOT NULL,
  KEY server_metrics_server_id_created_at_index (server_id, created_at)
);

INSERT INTO servers
SELECT UUID() FROM (WITH RECURSIVE n AS (SELECT 1 i UNION ALL SELECT i + 1 FROM n WHERE i < 50) SELECT i FROM n) t;

SET SESSION cte_max_recursion_depth = 100000;
INSERT INTO server_metrics (server_id, cpu, created_at)
SELECT s.id, RAND() * 100, NOW() - INTERVAL n.i MINUTE
FROM servers s
CROSS JOIN (WITH RECURSIVE n AS (SELECT 1 i UNION ALL SELECT i + 1 FROM n WHERE i < 45000) SELECT i FROM n) n;

ANALYZE TABLE server_metrics;

Что сделали

Последний id метрики теперь лежит у самого сервера, в servers.latest_metric_id. Его обновляет хук created у метрики, а latestMetric() стал обычным belongsTo:

// ServerMetric
protected static function booted(): void
{
    static::created(function (ServerMetric $metric): void {
        Server::whereKey($metric->server_id)->toBase()->update(['latest_metric_id' => $metric->id]);
    });
}

// Server
public function latestMetric(): BelongsTo
{
    return $this->belongsTo(ServerMetric::class, 'latest_metric_id');
}

toBase() здесь нужен, чтобы на каждую метрику не срабатывали журнал изменений, обсерверы и updated_at у сервера. Выборка по первичному ключу с биндингами на проде идет за 1–3 мс. Список серверов после выкатки открывается за 0,23 секунды вместо 1,7

Можно было включить ATTR_EMULATE_PREPARES, с ней запрос снова идет за 2 мс. Тогда до эмуляции не додумались. Это выяснилось уже на стенде для статьи, а указатель к тому времени работал на проде.

Как замер уронил прод на 13 минут

Чтобы увидеть разницу на проде, запрос гоняли через PDO с биндингами. Один такой пробный запрос по server_metrics отменили, но отменилась только задача, которая его отправила. В MySQL он проработал еще 93 минуты. В processlist он висел с command = Execute, потому что пришел как prepared statement. Искали его среди command = 'Query'

В ту же ночь выкатывалась миграция с latest_metric_id, то есть ALTER TABLE servers. Пробный запрос читал другую таблицу, но у server_metrics внешний ключ на servers. В документации MySQL про metadata locks так и написано, при изменении родительской таблицы блокировка берется и на дочернюю. ALTER встал ждать, за ним в очередь встали все запросы к servers, и с 00:36 до 00:49 панель не отвечала, пока поток с пробным запросом не сняли через KILL

После этого пробные SELECT на проде идут только с хинтом

/*+ MAX_EXECUTION_TIME(5000) */

через 5 секунд MySQL обрывает их сам. А после отмены processlist проверяется по времени, без фильтра по типу команды:

SELECT id, command, time, LEFT(info, 80) AS query
FROM information_schema.processlist
WHERE command NOT IN ('Sleep', 'Daemon') AND time > 20;

И перед деплоем с ALTER по таблице, на которую ссылаются внешние ключи, лучше посмотреть, нет ли долгих запросов к дочерним таблицам.

А у вас прод ронял какой‑нибудь чудо‑запрос? Расскажите в комментариях, как это было и чем кончилось.