Почему LIMIT 1 может ускорить запрос в Yii2 + SQL Server на больших таблицах

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

В этой заметке рассмотрим реальный кейс оптимизации запроса в Yii2, где добавление LIMIT 1 неожиданно резко ускорило выборку.

Есть таблица транзакций: transaction_log. Размер таблицы — несколько миллионов записей.

Структура упрощённо выглядит так:

CREATE TABLE
transaction_log ( id INT IDENTITY PRIMARY KEY,
operation_type CHAR(1) NOT NULL,
amount NUMERIC(15,2) NOT NULL,
balance NUMERIC(15,2) NOT NULL,
transaction_date DATETIME NOT NULL,
organization_id INT NOT NULL,
description VARCHAR(500) NOT NULL
);

Также существует индекс:

CREATE INDEX idx_org_date_id ON transaction_log (organization_id, transaction_date, id);

Задача получить последнюю транзакцию организации.

В Yii2 это выглядело так:

return TransactionLog::find()
->where(['organization_id' => $orgId])
->orderBy([ 'transaction_date' => SORT_DESC, 'id' => SORT_DESC ])
->one();

Проблема заключалась в том, что у некоторых организаций в таблице накопилось более 2 миллионов записей. Запрос начал выполняться очень долго, иногда приводя к таймауту страницы.

Сгенерированный SQL выглядел так:

SELECT * FROM transaction_log WHERE organization_id = 123 ORDER BY transaction_date DESC, id DESC

Обратите внимание — ограничения на количество строк нет. Хотя в PHP используется one(), на уровне SQL серверу всё равно приходится:

1. Найти все строки организации

2. Отсортировать их

3. Вернуть результат

Для миллионов строк это может быть очень дорого.

Было добавлено явное ограничение:

return TransactionLog::find()
->where(['organization_id' => $orgId])
->orderBy([ 'transaction_date' => SORT_DESC, 'id' => SORT_DESC ])
->limit(1)
->one();

После этого Yii2 сгенерировал другой запрос:

SELECT * FROM transaction_log WHERE organization_id = 123 ORDER BY transaction_date DESC, id DESC OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY

Запрос стал выполняться значительно быстрее.

Почему это работает?

В SQL Server оптимизатор начинает применять так называемый Row Goal Optimization. Это означает: Оптимизатор понимает, что нужна только одна строка, и старается найти её максимально быстро.

В результате:

• уменьшается объём читаемых данных

• может использоваться более эффективный план выполнения

• сортировка большого объёма данных может быть частично или полностью устранена

Почему one() не помог

Метод one() в Yii2:

• ограничивает количество возвращаемых результатов на уровне ActiveRecord

• но не всегда заставляет SQL генератор добавить TOP 1 или FETCH NEXT 1 В результате SQL Server не знает, что нужно только одна строка.

Когда это особенно важно? Эта оптимизация полезна, если:

• таблица очень большая

• у одного ключа может быть много записей

• используется ORDER BY

• требуется только одна запись

При использовании Yii2 с SQL Server для выборки одной записи из больших таблиц лучше писать:

 ->limit(1)->one() 

Иногда небольшое изменение в ORM-запросе может существенно повлиять на план выполнения SQL. Если запрос выбирает одну строку из потенциально большого набора, явное использование LIMIT 1 может значительно ускорить выполнение.