loading..
Русский    English
16:28
листать

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

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

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

  1. SELECT COUNT(*)N, T1.*
  2. FROM TestTable T1
  3.    JOIN TestTable T2 ON T1.order_id >= T2.order_id
  4. GROUP BY T1.order_id;
Что на 10000 записей в таблице TestTable выдаёт:

Duration / Fetch

16.084 sec / 0.016 sec

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

  1. SELECT @N:=@N+1 N, T1.*
  2. FROM TestTable T1, (SELECT @N := 0)M
  3. ORDER BY T1.order_id;
Выдаёт:

Duration / Fetch

0.016 sec / 0.015 sec

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

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

Вывести по 2 первые строки из таблицы TestTable для каждого значения group_id, отсортированных по order_id.

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

  1. SELECT group_id, order_id, value
  2. FROM(
  3.   SELECT *, ROW_NUMBER()OVER(PARTITION BY group_id ORDER BY order_id) RowNum
  4.   FROM TestTable
  5. )T
  6. WHERE RowNum <= 2;

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

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

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

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

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

  1. SELECT DISTINCT group_id FROM TestTable;

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

  1. SELECT * FROM TestTable WHERE group_id=1 ORDER BY order_id LIMIT 2
  2. UNION ALL
  3. SELECT * FROM TestTable WHERE group_id=2 ORDER BY order_id LIMIT 2
  4. UNION ALL
  5. SELECT * FROM TestTable WHERE group_id=20 ORDER BY order_id LIMIT 2;

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


Bookmark and Share
Тэги:
ALL AND AUTO_INCREMENT AVG battles CASE CAST CHAR CHARINDEX CHECK classes COALESCE CONSTRAINT Convert COUNT CROSS APPLY CTE DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DELETE DISTINCT DML EXCEPT EXISTS EXTRACT FOREIGN KEY FROM FULL JOIN GROUP BY Guadalcanal HAVING IDENTITY IN INFORMATION_SCHEMA INNER JOIN insert INTERSECT IS NOT NULL IS NULL ISNULL laptop LEFT LEFT OUTER JOIN LEN maker Больше тэгов
Учебник обновлялся
месяц назад
©SQL-EX,2008 [Развитие] [Связь] [О проекте] [Ссылки] [Team]
Перепечатка материалов сайта возможна только с разрешения автора.