Кстати, в MySQL 8 тоже есть поддержка оконных функций вот буквальная копия запроса Sail на движке MySQL: https://www.db-fiddle.com/f/uoAksmjeeeGctq9F4wEK6X/2 Мне он кажется избыточным.
Результаты сравнения: http://pesow.com/F/test-speed-2020-01-03.7z Я где-то ошибся в запросах? Дамп, если кто захочет потестить на PG: http://pesow.com/F/tProducts-2020-01-02.7z
Можно ещё выкинуть WHERE color IN() и слово LEFT — ничего не изменится. В версиии для MySQL можно CASE заменить на FIELD(), для больших списков значений, как у Чушкина, оно выглядит лучше. Хотя что-то мне подсказывает, что в реале если список длинный, то он скорее всего в своей таблице и тогда нужен новый JOIN и простой ORDER BY без функций.
@Chushkin так бы и сказал, что борешся с проблемой падения скорости LIMIT m,n --- Добавлено --- а то заходишь откуда-то сбоку
Ну это очевидно, что LIMIT нужен чтобы выборка выполнилась за разумное время ))) Из твоей сводки по скорости я вижу, что при LIMIT 0,100 время нормальное. надо сделать так, чтобы время не росло для других вариантов, так? --- Добавлено --- Время обработки ВСЕХ строк будет большим, но тебя вроде это не должно парить.
Короче, тебе нужен способ преобразовать LIMIT m,n в WHERE x>=y ORDER BY x LIMIT n. Специфично для конкретной задачи. Ты тут таблицу Products упростил до одних только ID, это приводит нас к вырожденному случаю, когда она вообщше накуй не нужна. Достаточно одной только пайвот таблицы, там те же productID. Короче, Чушкин, ты как будто и не хочешь получить подсказку по делу. Проблема XY налицо. --- Добавлено --- Если у тебя будут 100 productID, неважно каким способом добытые, ты можешь выполнить запрос SELECT products.* FROM products JOIN похуйчто WHERE products.id IN(...) ORDER BY похуйчто и это будет достаточно быстро и не будет проблемы LIMIT m,n
Код (Text): SELECT DISTINCT ON (tPC."productID") tPC."productID", tPC."colorID" FROM "tProductsColors" tPC WHERE tPC."productID" IN (SELECT "productID" from "tProducts" limit 100 OFFSET 100000) ORDER BY tPC."productID", array_position(ARRAY [68,73,45,35,5,58,40,7,98,59,31,1,89,39,17,88,51,52,69,54, 99,91,27,78,81,12,56,34,94,28,36,90,57,24,85,15,92,4,67,72, 95,32,64,11,30,80,8,96,100,10,48,37,16,41,76,3,2,79,33,43, 74,75,55,20,26,19,47,49,42,62,70,66,50,71,29,22,82,14,9,84, 23,44,53,87,21,63,46,61,18,77,38,86,6,93,13,65,97,83,25,60], tPC."colorID") Собственно, идея в том, что ранжирование (как самое тяжелое) проводится уже после того, как мы получили нужные ID продуктов. Даже если добавить join к запросу, то это опять же не даст просадки в скорости, потому что цепляться будет вновь конечный результат.
Код (Text): WITH ranks AS (SELECT ARRAY[68,73,45,35,5,58,40,7,98,59,31,1,89,39,17,88,51,52,69,54, 99,91,27,78,81,12,56,34,94,28,36,90,57,24,85,15,92,4,67,72, 95,32,64,11,30,80,8,96,100,10,48,37,16,41,76,3,2,79,33,43, 74,75,55,20,26,19,47,49,42,62,70,66,50,71,29,22,82,14,9,84, 23,44,53,87,21,63,46,61,18,77,38,86,6,93,13,65,97,83,25,60] AS rank), products AS (SELECT "productID" from "tProducts" limit 100 OFFSET 100000) SELECT DISTINCT ON (tPC."productID") tPC."productID", tPC."colorID" FROM "tProductsColors" tPC, products, ranks WHERE tPC."productID" IN (products."productID") ORDER BY tPC."productID", array_position(ranks.rank, tPC."colorID") вот так красивше и логичнее )
Если создать "таблицу отсортированных colorID", то можно пользоваться вот таким Код (Text): SELECT * FROM tProductsColors JOIN seq ON tProductsColors.colorID = seq.id ORDER BY seq.id LIMIT 1000000,100 Время выборки очень даже приемлемое. Я подозреваю, что приоритеты пользовательского цвета уже хранятся в таблице. Так что много переделывать не придется, ну типа ON tProductsColors.colorID = seq.id AND seq.user_id = ? Надо позаботиться об индексах. В вот EXPLAIN EXTENDED: Код (Text): id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE seq index id id 4 100 100.00 Using index 1 SIMPLE tProductsColors ref colorID colorID 4 tmp.seq.id 1232 100.00 Using index Данное решение не зависит от диалекта SQL и установленных расширений
Главное избежать преждевременных "общих" join`ов. Пока планировщик манипулирует ограниченным набором записей - всё будет нормально, как только он решит что настало время сделать цикл внутри цикла и собрать данные в кучу - память и процессор закончатся. Каким способом этого добиться - не особо важно. А ещё можно вспомнить про такую крутую штуку как materalized view: её можно использовать как быструю замену тяжелых запросов, где не критична актуальность. Другими словами это запрос, сохраненный в виде таблицы, на которую можно ещё и своих индексов навесить, а обновлять с определенной периодичностью и в фоне. Часто бывает, что на самом деле данные не нужны прям вот сейчас и сразу и лаг даже в полчаса ни кому не помешает. В результате запрос, который в обычное время выполнялся долго, станет отдаваться практически мгновенно и без нагрузки по памяти / цп. И партицирование. Короче, много больших табличек - это весело, потому что вся сухая теория идет лесом и приходится искать оригинальные способы не убить сервер )
Ты всё ещё не понял смысл запроса с приоритетом. Основной смысл, из примера, "один productID - один colorID". А не "один productID - много colorID". Или в общем случае, - суть запроса с приоритетом в том, что у сущности будет одно значение, а не куча списком. Я не знаю вариантов без использования подзапросов тем или иным способом. Вот у @romach рабочий запрос. К сожалению, в MySQL нет DISTINCT ON, поэтому напрямую потестить/сравнить не смогу. И всё же, это агрегатная функция. И я не уверен, что использование агрегатных запросов в подобных задачах даст лучший результат, чем вариант 1 (из теста). Можно, конечно, сделать аналогичный вариант с GROUP BY, но всё же это будет немного другой запрос. --- Добавлено --- Вообще, суть проблемы в том, что на каждую сущность будет обработано N записей. Т.е. не важно, 100 productID, 1000 или миллион и как они получены, всё равно на каждый productID будет порядка 100 чтений из таблицы tProductsColors (по тесту; фактически, обработано записей). И это проблема. В идеале: хотелось бы иметь не линейную зависимость от числа записей, а логарифмическую, как поиск по индексу, например. Накрайняк, пусть будет линейная зависимость от числа данных, но запрос, при одинаковом результате, должен выполняться раз в 100 быстрее, чем вариант 1 (из теста). Конечно, если это разовый запрос, то пофиг, сколько запрос выполняется 0.01 секунду или 0.001. А вот если это API ... то согласитесь, есть разница, - 100 запросов/секунду или 1000 з/с. Отсюда поиск оптимальности и эффективности. --- Добавлено --- Кстати, это и есть твой "самый быстрый вариант из всех, немного костыльный"?
@Chushkin до сих пор я не давал законченного рецепта, только приёмы. Думал ты можешь сам скомпилировать всё в кучку Короче, я сравниваю твой запрос со своим, оптимизированным с помощью таблицы приоритетных цветов. Беру большие значения limit offset потому что только на них проявляются тормоза. Получается такая картина: твой пример Код (SQL): SELECT SQL_NO_CACHE p.productID, pc.colorID FROM tProducts p LEFT JOIN tProductsColors pc ON pc.productID = p.productID AND colorID = (SELECT colorID FROM tProductsColors WHERE productID = p.productID AND colorID IN ( 68,73,45,35,5,58,40,7,98,59,31,1,89,39,17,88,51,52,69,54, 99,91,27,78,81,12,56,34,94,28,36,90,57,24,85,15,92,4,67,72, 95,32,64,11,30,80,8,96,100,10,48,37,16,41,76,3,2,79,33,43, 74,75,55,20,26,19,47,49,42,62,70,66,50,71,29,22,82,14,9,84, 23,44,53,87,21,63,46,61,18,77,38,86,6,93,13,65,97,83,25,60) ORDER BY FIELD(colorID, 68,73,45,35,5,58,40,7,98,59,31,1,89,39,17,88,51,52,69,54, 99,91,27,78,81,12,56,34,94,28,36,90,57,24,85,15,92,4,67,72, 95,32,64,11,30,80,8,96,100,10,48,37,16,41,76,3,2,79,33,43, 74,75,55,20,26,19,47,49,42,62,70,66,50,71,29,22,82,14,9,84, 23,44,53,87,21,63,46,61,18,77,38,86,6,93,13,65,97,83,25,60) LIMIT 1) LIMIT 100000,100 21.378s против моего. Код (SQL): SELECT SQL_NO_CACHE p.productID, ( SELECT x.colorID FROM tProductsColors x JOIN seq AS y ON y.id = x.colorID WHERE x.productID = p.productID ORDER BY y.seq_no LIMIT 1 ) AS colorID FROM tProducts AS p ORDER BY p.productID LIMIT 100000, 100 0.047s 21.378s vs. 0.047s !!! а результат они выдают один и тот же. Так что не говори, что я чего-то не понял. --- Добавлено --- Ещё раз повторю, что возможно создавать таблицу seq и не надо, а надо только проверить индексы у какой-то существующей таблицы параметров. Откуда-то взялись твои 100 значений внутри FIELD() ? Но чисто для возможных проверок, напишу как я её создавал: Код (SQL): DROP TABLE IF EXISTS `seq`; CREATE TABLE `seq` ( `seq_no` INT(11) NOT NULL AUTO_INCREMENT, `id` INT(11) NOT NULL, PRIMARY KEY (`seq_no`), UNIQUE KEY `id` (`id`), KEY `seq_no` (`seq_no`) ); INSERT INTO seq(id) VALUES -- значения скопированы из твоего примера с FIELD() (68),(73),(45),(35),(5),(58),(40),(7),(98),(59),(31),(1),(89),(39),(17),(88),(51),(52),(69),(54), (99),(91),(27),(78),(81),(12),(56),(34),(94),(28),(36),(90),(57),(24),(85),(15),(92),(4),(67),(72), (95),(32),(64),(11),(30),(80),(8),(96),(100),(10),(48),(37),(16),(41),(76),(3),(2),(79),(33),(43), (74),(75),(55),(20),(26),(19),(47),(49),(42),(62),(70),(66),(50),(71),(29),(22),(82),(14),(9),(84), (23),(44),(53),(87),(21),(63),(46),(61),(18),(77),(38),(86),(6),(93),(13),(65),(97),(83),(25),(60)
Суть ускорения в том, чтобы отсечь мусор как можно раньше, как я уже писал. А потом можно чего-то сортировать, джойнить и т.п. Фраза SELECT это некий аналог View в парадигме MVC. Он работает с уже добытой записью, поэтому перенос под-запроса из FROM-WHERE в SELECT меняет план выполнения запроса. Ниже даю пример без таблицы приоритетов. Он работает примерно с той же скоростью, что и предыдущий мой запрос. Разница с примером от Чушкина в том, что под-запрос будет выполнен только на уже отфильтрованных записях, а не на всех 100100 ! Код (SQL): SELECT SQL_NO_CACHE p.productID, ( SELECT colorID FROM tProductsColors WHERE productID = p.productID ORDER BY FIELD(colorID, 68,73,45,35,5,58,40,7,98,59,31,1,89,39,17,88,51,52,69,54, 99,91,27,78,81,12,56,34,94,28,36,90,57,24,85,15,92,4,67,72, 95,32,64,11,30,80,8,96,100,10,48,37,16,41,76,3,2,79,33,43, 74,75,55,20,26,19,47,49,42,62,70,66,50,71,29,22,82,14,9,84, 23,44,53,87,21,63,46,61,18,77,38,86,6,93,13,65,97,83,25,60) LIMIT 1 ) AS colorID FROM tProducts p LIMIT 100000,100 100 айдишников некрасиво выглядят. Но почему бы и нет. План запроса: Код (Text): id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY p index PRIMARY 4 500175 100.00 Using index 2 DEPENDENT SUBQUERY tProductsColors ref PRIMARY,productID productID 4 tmp.p.productID 54 100.00 Using where; Using index; Using filesort Файлсорт на 100 записях. Не отлично, но и не ужасно Я по прежнему считаю, что твоя постановка вопроса странная, @Chushkin . Ты спрашиваешь как применить pgsql, а не как выполнить запрос быстро. Проблема XY .
Код (Text): Unique (cost=5342.23..5372.72 rows=6099 width=12) (actual time=90.773..91.300 rows=100 loops=1) CTE ranks -> Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=1) CTE products -> Limit (cost=4327.80..4329.24 rows=100 width=4) (actual time=82.722..82.765 rows=100 loops=1) -> Seq Scan on "tProducts" (cost=0.00..7213.00 rows=500000 width=4) (actual time=0.014..65.284 rows=300100 loops=1) -> Sort (cost=1012.97..1028.22 rows=6099 width=12) (actual time=90.772..90.998 rows=2468 loops=1) Sort Key: tpc."productID", (array_position(ranks.rank, tpc."colorID")) Sort Method: quicksort Memory: 212kB -> Nested Loop (cost=0.43..629.52 rows=6099 width=12) (actual time=84.488..89.372 rows=2468 loops=1) -> Nested Loop (cost=0.00..3.02 rows=100 width=36) (actual time=82.735..82.857 rows=100 loops=1) -> CTE Scan on ranks (cost=0.00..0.02 rows=1 width=32) (actual time=0.004..0.005 rows=1 loops=1) -> CTE Scan on products (cost=0.00..2.00 rows=100 width=4) (actual time=82.728..82.824 rows=100 loops=1) -> Index Only Scan using tproductscolors_productid_colorid_uindex on "tProductsColors" tpc (cost=0.43..5.50 rows=61 width=8) (actual time=0.021..0.029 rows=25 loops=100) Index Cond: ("productID" = products."productID") Heap Fetches: 0 Planning time: 0.295 ms Execution time: 91.406 ms LIMIT 100 OFFSET 300000 Для сравнения, твой запрос на тестовой таблице в mysql выполнился у меня раза в два-три медленнее, потому что на dev-машине все кэши и прочие оптимизации выкручены в минимум. На всякий случай: Код (Text): select p."productID", pc."colorID" from "tProducts" p left join "tProductsColors" pc on pc."productID" = p."productID" and "colorID" = ( select "colorID" from "tProductsColors" where "productID" = p."productID" and "colorID" in ( 68, 73, 45, 35, 5, 58, 40, 7, 98, 59, 31, 1, 89, 39, 17, 88, 51, 52, 69, 54, 99, 91, 27, 78, 81, 12, 56, 34, 94, 28, 36, 90, 57, 24, 85, 15, 92, 4, 67, 72, 95, 32, 64, 11, 30, 80, 8, 96, 100, 10, 48, 37, 16, 41, 76, 3, 2, 79, 33, 43, 74, 75, 55, 20, 26, 19, 47, 49, 42, 62, 70, 66, 50, 71, 29, 22, 82, 14, 9, 84, 23, 44, 53, 87, 21, 63, 46, 61, 18, 77, 38, 86, 6, 93, 13, 65, 97, 83, 25, 60) order by array_position(ARRAY [ 68,73,45,35,5,58,40,7,98,59,31,1,89,39,17,88,51,52,69,54, 99,91,27,78,81,12,56,34,94,28,36,90,57,24,85,15,92,4,67,72, 95,32,64,11,30,80,8,96,100,10,48,37,16,41,76,3,2,79,33,43, 74,75,55,20,26,19,47,49,42,62,70,66,50,71,29,22,82,14,9,84, 23,44,53,87,21,63,46,61,18,77,38,86,6,93,13,65,97,83,25,60], "colorID") limit 1) offset 10000 limit 100; я сравнивал с этим, это тот же твой запрос, только в pg-синтаксисе. --- Добавлено --- p.s. в mysql до сих пор так и не сделали нормальный explain как в pg?