SQLinfo.ru - Все о MySQL

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 22.03.2015 18:56:45

Александр Трофимов
Завсегдатай
Откуда: Юрмала
Зарегистрирован: 19.09.2011
Сообщений: 95

Избавиться от Order By или как-то по-другому оптимизировать запрос

Здравия желаю, товарищи!

Давно я здесь не бывал. +) Но возник вопрос.

Есть две таблицы:

CREATE TABLE tyres (
  size int(10) UNSIGNED NOT NULL DEFAULT 0,
  front_tyre_id bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  rear_tyre_id bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  timestamp timestamp NULL DEFAULT NULL,
  price int(10) UNSIGNED NOT NULL DEFAULT 0,
  INDEX IDX_tyres (size, price, front_tyre_id, rear_tyre_id, timestamp)
)
ENGINE = INNODB
CHARACTER SET utf8
COLLATE utf8_general_ci


CREATE TABLE wheels (
  size int(10) UNSIGNED NOT NULL DEFAULT 0,
  front_wheel_id bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  rear_wheel_id bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  timestamp timestamp NULL DEFAULT NULL,
  price int(11) UNSIGNED NOT NULL DEFAULT 0,
  INDEX IDX_wheels (size, price, front_wheel_id, rear_wheel_id, timestamp),
)
ENGINE = INNODB
CHARACTER SET utf8
COLLATE utf8_general_ci


Есть ли какая-то возможность оптимизировать запрос путем избавления от Order By? Или любым другим путем. +)

SELECT
  (w.price+t.price) as price,
  w.front_wheel_id,
  w.rear_wheel_id,
  t.front_tyre_id,
  t.rear_tyre_id,
  LEAST(w.timestamp,t.timestamp) as `timestamp`
FROM
  tyres t
  JOIN wheels w ON (t.size=w.size)
ORDER BY
  price,
  w.front_wheel_id,
  w.rear_wheel_id,
  t.front_tyre_id,
  t.rear_tyre_id
LIMIT
  21

Неактивен

 

#2 23.03.2015 11:04:43

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Избавиться от Order By или как-то по-другому оптимизировать запрос

Вы сортируете по вычисляемому полю, так что тут по любому будет filesort всего результата. Можно лишь вложенными запросами в части from заранее отсечь лишнее, т.е. вместо
   tyres t
использовать
(select from tyres order by price limit X) t

Аналогично и для wheels

X подбирается с запасом, вдумчиво глядя на данные smile

Неактивен

 

#3 23.03.2015 11:26:29

Александр Трофимов
Завсегдатай
Откуда: Юрмала
Зарегистрирован: 19.09.2011
Сообщений: 95

Re: Избавиться от Order By или как-то по-другому оптимизировать запрос

+))) Спасибо за ответ. К сожалению сортировать по цене без связи по размеру не имеет смысла.
Это таблицы (в реальной жизни TEMPORARY) уже лимитированы по максимуму, в них остались только те данные, которые гарантируют корректное отображение 21 первых самых дешевых комплекта (колеса+диски). Общая таблица после соединения в среднем имеет 1-1,5 млн записей, бывает и 4-5 млн.

Может есть какая-то возможность отдельным запросом узнать данные, которые помогут ограничить количество записей еще больше?

Я рассматривал запрос типа

SELECT
    t.size,
    MIN(w.price+t.price) as minprice,
    MAX(w.price+t.price) as maxprice
FROM
    tyres t
    JOIN wheels w ON (t.size=w.size)
GROUP BY
    t.size


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

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

Неактивен

 

#4 23.03.2015 11:41:33

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Избавиться от Order By или как-то по-другому оптимизировать запрос

Тогда

select   (w1.price+t1.price) as price, ... from
(select from tyres order by price limit X) t1 JOIN wheels w1 ON (t1.size=w1.size)
UNION ALL
select   (w2.price+t2.price) as price, ... from
(select from wheels order by price limit X) w2 JOIN types t2 ON (t2.size=w2.size)
ORDER BY price LIMIT 21

Неактивен

 

#5 23.03.2015 11:48:49

Александр Трофимов
Завсегдатай
Откуда: Юрмала
Зарегистрирован: 19.09.2011
Сообщений: 95

Re: Избавиться от Order By или как-то по-другому оптимизировать запрос

Мысль примерно понимаю. Но как определить X ума пока не приложу. +))

Неактивен

 

#6 23.03.2015 12:03:27

Александр Трофимов
Завсегдатай
Откуда: Юрмала
Зарегистрирован: 19.09.2011
Сообщений: 95

Re: Избавиться от Order By или как-то по-другому оптимизировать запрос

Убрал UNION ALL, чтобы отбирались только уникальные строки. Добавил сортировку по ID товаров.

Странно, но когда я выставляю X = 21, все работает. И даже при разделении на страницы работает. X = 41, LIMIT 20,21 — результаты у обоих запросов одинаковы. +))

Теперь надо понять, где могут быть подводные камни. И надо просто осознать, почему все-таки работает.

Неактивен

 

#7 23.03.2015 12:19:36

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Избавиться от Order By или как-то по-другому оптимизировать запрос

Имхо, если сортировку по front и rear тоже использовать в подзапросах, то X=21.

Неактивен

 

#8 23.03.2015 12:40:37

Александр Трофимов
Завсегдатай
Откуда: Юрмала
Зарегистрирован: 19.09.2011
Сообщений: 95

Re: Избавиться от Order By или как-то по-другому оптимизировать запрос

Все работает. +)) Только не со временными таблицами.
Создание таблиц дубликатов занимает то время, которое потом экономится на новом объединяющем запросе.
Попа. +)))

Неактивен

 

#9 23.03.2015 12:46:44

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Избавиться от Order By или как-то по-другому оптимизировать запрос

Работает потому, что

X первых самых дешевых комплекта (колеса+диски)
это
X самых дешевых колес + связанные с ними диски
+
X самых дешевых дисков + связанные с ними колеса
сортируем по цене комплекта и LIMIT X

Имхо, подводных камней нет. И при разбивке на странице тоже будет работать корректно.
ALL действительно не нужен, это я лопухнулся smile

Неактивен

 

#10 23.03.2015 12:51:19

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Избавиться от Order By или как-то по-другому оптимизировать запрос

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

Неактивен

 

#11 23.03.2015 12:52:03

Александр Трофимов
Завсегдатай
Откуда: Юрмала
Зарегистрирован: 19.09.2011
Сообщений: 95

Re: Избавиться от Order By или как-то по-другому оптимизировать запрос

Вася, когда все так красиво, это кажется нереальным. +))) Поэтому чисто психологически хочется найти косяк. Спасибо за помощь.

Остался вопрос, который меня мучает уже продолжительное время: Подзапрос в скобках отрабатывает быстрее, чем отдельный запрос на создание временной таблицы?

Неактивен

 

#12 23.03.2015 12:55:39

Александр Трофимов
Завсегдатай
Откуда: Юрмала
Зарегистрирован: 19.09.2011
Сообщений: 95

Re: Избавиться от Order By или как-то по-другому оптимизировать запрос

Я говорил о том, что таблицы tyres и wheels в реальной жизни TEMPORARY, поэтому MySQL не может их использовать дважды в одном запросе (Can't reopen). Я пытаюсь решить этот вопрос созданием двух временных таблиц из tyres2 =  select from tyres order by price limit X и wheels2 = select from wheels order by price limit X.

Про сортировку («front и rear тоже использовать в подзапросах») я выше не написал, что так и сделал. +))

Неактивен

 

#13 23.03.2015 13:33:48

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Избавиться от Order By или как-то по-другому оптимизировать запрос

Александр Трофимов написал:

Остался вопрос, который меня мучает уже продолжительное время: Подзапрос в скобках отрабатывает быстрее, чем отдельный запрос на создание временной таблицы?

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

Александр Трофимов написал:

Я говорил о том, что таблицы tyres и wheels в реальной жизни TEMPORARY, поэтому MySQL не может их использовать дважды в одном запросе (Can't reopen). Я пытаюсь решить этот вопрос созданием двух временных таблиц из tyres2 =  select from tyres order by price limit X и wheels2 = select from wheels order by price limit X.

Да, иначе не получиться, а есть возможность в исходных TEMPORARY tyres сделать подходящий индекс для сортировки?

Неактивен

 

#14 23.03.2015 13:42:41

Александр Трофимов
Завсегдатай
Откуда: Юрмала
Зарегистрирован: 19.09.2011
Сообщений: 95

Re: Избавиться от Order By или как-то по-другому оптимизировать запрос

vasya написал:

Да, иначе не получиться, а есть возможность в исходных TEMPORARY tyres сделать подходящий индекс для сортировки?

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

vasya написал:

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

Я попробовал. У меня MySQL на тестах показал увеличение скорости на треть минимум. Сейчас попробую переписать скрипт так, чтобы все временные таблицы создавались в подзапросах одного большого запроса и поглядим. +)

Неактивен

 

#15 23.03.2015 13:45:39

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Избавиться от Order By или как-то по-другому оптимизировать запрос

для
tyres2 =  select from tyres order by price, front_tyre_id, rear_tyre_id limit X
нужен
INDEX (price, front_tyre_id, rear_tyre_id)

Неактивен

 

#16 23.03.2015 13:49:48

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Избавиться от Order By или как-то по-другому оптимизировать запрос

Александр Трофимов написал:

Я попробовал. У меня MySQL на тестах показал увеличение скорости на треть минимум. Сейчас попробую переписать скрипт так, чтобы все временные таблицы создавались в подзапросах одного большого запроса и поглядим. +)

А какая у вас версия MySQL?

Неактивен

 

#17 23.03.2015 14:06:04

Александр Трофимов
Завсегдатай
Откуда: Юрмала
Зарегистрирован: 19.09.2011
Сообщений: 95

Re: Избавиться от Order By или как-то по-другому оптимизировать запрос

vasya написал:

А какая у вас версия MySQL?

5.5.33 MariaDB
Я вложил все запросы в один большой. Разница в скорости генерации страницы 1.36782 sec (все вложенные) против 2.46771 sec (временные таблицы). При том, что я второй запрос запускал после первого (вдруг там чего попало в кеш).

Неактивен

 

#18 23.03.2015 15:11:52

Александр Трофимов
Завсегдатай
Откуда: Юрмала
Зарегистрирован: 19.09.2011
Сообщений: 95

Re: Избавиться от Order By или как-то по-другому оптимизировать запрос

И так результаты опытов. Время генерации страниц по три на каждый вариант.

Как было: 2.22763 sec | 1.17455 sec | 1.85758 sec
Один общий запрос: 0.69147 sec | 1.00500 sec | 0.98592 sec
Две временные таблицы: 0.84656 sec | 0.93798 sec | 1.02652 sec

Повторные генерации страниц много быстрее, наверняка из-за кеша.

И так, было явно плохо. +)) Один общий запрос в среднем вроде бы чуть быстрее работает, чем две временные таблицы. Допускаю, что из-за дополнительных ресурсов на отправку и оформление запросов. Так как скрипт работает на php, думаю, каждый запрос тратит время на общение php с mysql. Надо будет посмотреть в сторону множественных запросов из php.

Неактивен

 

#19 23.03.2015 15:45:06

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Избавиться от Order By или как-то по-другому оптимизировать запрос

В вашем случае подзапрос быстрее, ещё и потому, что MariaDB 5.5 умеет их хорошо готовить smile

Неактивен

 

#20 25.03.2015 01:00:29

Александр Трофимов
Завсегдатай
Откуда: Юрмала
Зарегистрирован: 19.09.2011
Сообщений: 95

Re: Избавиться от Order By или как-то по-другому оптимизировать запрос

Нет слов, одни слюни! +))) Я скрестил общий запрос с идеей временных таблиц (Один общий запрос + Две временные таблицы), т.е. получил один большой общий запрос с повторяющимися подзапросами. И скорость повысилась еще в двое. Выставлю здесь запрос, может кто увидит еще возможность улучшения в плане построения запроса. +)))

ЗЫ. Здесь видимо есть ограничение на длину сообщения. Мой запрос не прошел. +)) Кому интересно, см. в приложении.

Отредактированно Александр Трофимов (25.03.2015 01:04:10)


Прикрепленные файлы:
Attachment Icon Query.sql, Размер: 30,935 байт, Скачано: 1,417

Неактивен

 

#21 25.03.2015 11:56:20

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Избавиться от Order By или как-то по-другому оптимизировать запрос

Возможно какие-то из повторяющихся подзапросов кэшируются. Можно выполнить
explain extended select ...
show warnings;
и посмотреть есть ли там куски со служебными словами <cache>, <temporary table>

Про сам запрос ничего не подскажу, так как его без пол литры не разобрать smile

Неактивен

 

#22 21.11.2015 21:52:00

XJIOP
Участник
Зарегистрирован: 28.02.2009
Сообщений: 22

Re: Избавиться от Order By или как-то по-другому оптимизировать запрос

я так понимаю для ORDER BY (column1+column2) DESC нельзя назначить индекс.
как же тогда оптимизировать такую сортировку?

Неактивен

 

#23 22.11.2015 13:49:30

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Избавиться от Order By или как-то по-другому оптимизировать запрос

хорошего способа нет. доп поле или пытаться уменьшить размер выборки (как по строкам, так и по полям), чтобы filesort был по небольшому объему.

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson