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

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

1) Классический способ с самомоединением:

SELECT COUNT(*)N, T1.*
FROM TestTable T1
   JOIN TestTable T2 ON T1.order_id >= T2.order_id
GROUP BY T1.order_id;

Что на 10000 записей в таблице TestTable выдаёт:

Duration / Fetch

16.084 sec / 0.016 sec

2) С использованием переменных:

SELECT @N:=@N+1 N, T1.*
FROM TestTable T1, (SELECT @N := 0)M
ORDER BY T1.order_id;

Выдаёт:

Duration / Fetch

0.016 sec / 0.015 sec

Результат говорит сам за себя. Однако надо понимать, что вычисленные с помощью переменных значения не оптимально использовать в условиях фильтрации. Сортировка и вычисление будут происходить для ВСЕХ строк, несмотря на то, что в итоге нам нужна только малая их часть.

Рассмотрим более подробно на примере такой задачи:

Вот как эта задача решалась бы в СУБД с поддержкой аналитических функций:

SELECT group_id, order_id, value
FROM(
  SELECT *, ROW_NUMBER()OVER(PARTITION BY group_id ORDER BY order_id) RowNum
  FROM TestTable
)T
WHERE RowNum <= 2;

Поскольку СУБД «знает», как работает ROW_NUMBER, оптимизатору незачем нумеровать ВСЕ строки, чтобы выбрать первые две. И всё выполнится быстро (при наличии индекса по group_id, order_id, конечно).

В случае с MySQL решение с подобным алгоритмом будет выглядеть так:

SELECT group_id, order_id, value
FROM(
  SELECT T.*,
    IF(@last_group_id = group_id, @I:=@I+1, @I:=1) RowNum,
    @last_group_id := group_id
  FROM TestTable T,(SELECT @last_group_id:=NULL, @I:=0)I
  ORDER BY group_id, order_id
)T
WHERE RowNum <= 2;

Однако оптимизатор MySQL ничего не знает о том, по каким правилам мы вычисляем поле RowNum. Ему придётся пронумеровать ВСЕ строки, и только потом отобрать нужные.

Теперь представьте, что у нас 1 миллион записей и 20 уникальных значений group_id. Т.е. чтобы выбрать 40 строк, MySQL будет вычислять значение RowNum для миллиона строк! Красивого решения этой задачи одним запросом в MySQL нет. Но можно сначала получить список уникальных значений group_id, например, так:

SELECT DISTINCT group_id FROM TestTable;

Затем средствами любого другого языка программирования сгенерировать запрос вида:

SELECT * FROM TestTable WHERE group_id=1 ORDER BY order_id LIMIT 2
UNION ALL
SELECT * FROM TestTable WHERE group_id=2 ORDER BY order_id LIMIT 2
UNION ALL
…
SELECT * FROM TestTable WHERE group_id=20 ORDER BY order_id LIMIT 2;

20 лёгких запросов отработают намного быстрее, чем вычисление RowNum для миллиона строк.