Комментарии 32
А если product.id имеет пропуски? Например, у нас удалены или выключены продукты с id 5000-5015, то мы два раза покажем одно и тоже?
В целом автор описал курсорную пагинацию (Cursor Pagination). Она кстати очень быстро работает. При верном подходе, такие пустоты не проблема.
Из условий, что пагинация должна идти по столбу который уникальный и имеет последовательность(возможность сортировки). Ну те условные id, email, username.
При этих условиях у нас будет возможность передавать параметры следюущая страницы и предыдущие (условно больше и меньше в запросе). Запрос будет эффективным, потому что мы используем только limit, не нужно делать относительно жрущий offset.
Выходит если у нас нужна пагинация при order by полю которое уникальное и можно сортировать и нет необходимости в циферках страниц, то ОЧЕНЬ выгодно использовать курсорную пагинацию. В других случаях усложняет логика запросов, да и выгода в производительности относительно limit + offset падает(а нередко и ухудшается).
В статье этот момент рассмотрен. Просто для следующей страницы строится метка по последней выбранной записи.
Если же мы перешли по карте переходов, например, на 26-12-2021, если это календарь, то показываем страницу и указываем в её конце ссылку на следующую, а ссылку, опять же, формируем по последней записи на странице.
Да, многие, как и вы ошибаются. На самом деле вложенные запросы требуют меньше ресурсов, чем джойн. Так БД умеют кешировать индекс в памяти для вложенных селектов.
Если же разместить вложенный селект в WHERE после других условий, через AND, то до него дело вообще не дойдёт на записях отброшенных предыдущими условиями.
Опять же, через джоин вложенный запрос в статье переписать нельзя, так как в stock несколько записей соответствуют одному product-у. Вам придётся сделать группировку, но при этом-то лимит останется за ней. На сервере памяти, как всегда, не хватит для всей таблицы stock и ни о какой высокой нагрузке речи уже не пойдёт.
В общем рекомендую вам выполнить несколько подобных описанному запросов и с вложенной выборкой и с джойном и посмотреть на расход ресурсов (как памяти, так и времени выполнения).
похоже тогда разработчики mysql тоже ошибаются.
https://dev.mysql.com/doc/refman/5.7/en/rewriting-subqueries.html
А что, если использовать count на stock.product_id и после product_count>0 вместо exists
В указанной вами документации утверждается, что SELECT DISTINCT t1.* FROM t1, t2 WHERE t1.id=t2.id; будет выполнятся быстрее SELECT * FROM t1 WHERE id IN (SELECT id FROM t2); , верно?
CREATE TABLE t1(id int PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB;
CREATE TABLE t2(id int PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB;Заполняем:
$ perl -MDBI -MDDP -e '
$x=DBI->connect("DBI:mysql:database=test;host=127.0.0.1", "root", 123);
$x->do("INSERT INTO t1 VALUES " . join ", ", map "($_)", 1..1000_000)'
$ perl -MDBI -MDDP -e '
$x=DBI->connect("DBI:mysql:database=test;host=127.0.0.1", "root", 123);
$x->do("INSERT INTO t2 VALUES " . join ", ", map "($_)", 1..500_000)'Измеряем:
$ perl -MDBI -e '
$x=DBI->connect("DBI:mysql:database=test;host=127.0.0.1", "root", 123);
use Time::HiRes qw/time/;
$t=Time::HiRes::time();
$x->selectall_arrayref("SELECT * FROM t1 WHERE id IN (SELECT id FROM t2)",
{Slice=>{}}); print Time::HiRes::time()-$t, "\n"'
5.24384808540344
$ perl -MDBI -e '
$x=DBI->connect("DBI:mysql:database=test;host=127.0.0.1", "root", 123);
use Time::HiRes qw/time/; $t=Time::HiRes::time();
$x->selectall_arrayref("SELECT DISTINCT t1.* FROM t1, t2 WHERE t1.id=t2.id",
{Slice=>{}}); print Time::HiRes::time()-$t, "\n"'
8.04534697532654Повторил дважды (вдруг таблицы не прокешировались) - результат тот же.
У меня ноут HP AMD A9-9420 5 ядер 2C+4G 3ГГц 8 ОЗУ c Windows10, 10.3.13-MariaDB (несколько лет не обновлял).
Есть предположения почему вложенный SELECT 5 сек., а JOIN - все 8?
P.S. Замерил скорость через HeidiSQL:
SELECT * FROM t1 WHERE id IN (SELECT id FROM t2) limit 1000000 -> 3,203 сек.
SELECT DISTINCT t1.* FROM t1, t2 WHERE t1.id=t2.id -> 7,078 сек.
SELECT t1.id FROM t1, t2 WHERE t1.id=t2.id -> 4,312 сек.
Если же разместить вложенный селект в WHERE после других условий, через AND, то до него дело вообще не дойдёт на записях отброшенных предыдущими условиями.
в общем случае это разумеется не верно, потому что: sql — декларативный язык программирования, в котором описывается ожидаемый результат, а не способ его получения. т.е. последовательность условий в WHERE не означает их такой же буквальный порядок их исполнения.
Программирование - это практика и у вас будут требовать, чтобы ваши SQL-запросы выполнялись быстро. Вряд ли вашего заказчика удовлетворит ответ: "А в декларативном программировании нет времени". )
(помниться на одном форуме, один программист, пытался использовать некий спецэффект реализации хэш-таблицы в одном из яп/фреймворков, который заключался в сохранении порядка добавляемых сортированных элементов. ему пытались объяснить, что хэш-таблиц это не про сортировку, а он говорил — ну вот же, практика))
CREATE TABLE product (
id int unsigned PRIMARY KEY AUTO_INCREMENT
) ENGINE=InnoDB COMMENT='Товар.';
create table stock(
id int unsigned primary key AUTO_INCREMENT,
product_id int unsigned NOT NULL COMMENT 'Ссылка на товар.'
) ENGINE=InnoDB COMMENT='Склад.'
insert into product(id) values (1), (2), (3), (4), (5);
insert into stock(id, product_id) values (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
set @a:='';
select product.id
from product
WHERE product.id >= 3
AND (
SELECT @a:=concat(@a, ' ', product_id)
FROM stock WHERE product_id=product.id LIMIT 1);
SELECT @a; --> ' 3 4 5'Как видите, вложенный SELECT сработал только для 3 4 и 5, а 1 и 2 были отброшены условием product.id >= 3 и во вложенный SELECT интерпретатор мускула просто не зашёл.
Что до хеша. Хеш - это реализация ассоциативного массива, но так как она очень распространена из-за того что кушает меньше памяти, чем, например, красно-чёрные деревья, то программисты часто называют хешами ассоциативные массивы. Хеши в PHP и Python, сохраняют порядок в котором были добавлены ключи:
hash = {4:1, 5:1, 3:1, 1:1, 2:1}
for i in hash:
print(i)
# -> 4 5 3 1 2В JavaScript порядок не сохраняется, а при переборе ключи хеша выдаются в алфавитном порядке:
hash = {4:1, 5:1, 3:1, 1:1, 2:1}
for(i in hash) console.log(i)
// -> 1 2 3 4 5В Perl-е порядок не сохраняется, а при переборе ключи выдаются в том порядке в котором расположились внутри хеша. То есть при 2-м запуске одного и того же скрипта порядок перебора ключей скорее всего будет разным:
$ perl -e '%hash = map { $_=>1 } 1..5;
print "$k "while ($k, $v) = each %hash'
2 1 4 3 5
$ perl -e '%hash = map { $_=>1 } 1..5;
print "$k "while ($k, $v) = each %hash'
5 1 2 4 3Соответственно если важен порядок ключей хеша при переборе, то пэхапешники и питонисты используют встроенную возможность не задумываясь. )
вот например sql-server: разумеется, от перемены местами условий в WHERE план запроса не меняется. по другому и быть не может.
ещё например в sql-server можно хинтами пытаться указывать тип джойнов, типа LOOP | HASH | MERGE.
а например в Оракле хинтами также можно влиять на план запроса путем указания используемого индекса.
типом джойнов и индекса(!) но не как не перестановкой условий в WHERE))
Действительно, мускул оптимизировал запрос и поменял операнды AND местами:
set @a:='';
select product.id
from product
WHERE
(
SELECT @a:=concat(@a, ' ', product_id)
FROM stock WHERE product_id=product.id LIMIT 1
)
AND product.id >= 3;
SELECT @a; # -> 3 4 5мускул оптимизировал запрос и поменял операнды AND местами
не согласен я с формулировкой. не то, что бы он не оптимизировал и не поменял местами, сам по себе SQL — это декларация, а не буквальная пошаговая инструкция. такого же рода например XSLT: высокоуровневое описание того, чего надо сделать.
да чё там — даже в классических языках программирования, типа C/C++, может вмешаться оптимизатор и итоговый код (ассемблерный) получается несколько иным. (отсюда например сюрпризы в многопоточных процедурах). про СУБД и говорить нечего. план выполнения запроса зависит от: индексов (которые есть в таблицах), статистики и селективности по имеющимся индексам (а она может быть битая/не успетая перестоиться), холодного/горячего старта данного запроса (были ли недавно запросы по таблицам из запроса), ресурсов сервера, нагруженностью СУБД-сервера по другим БД (и по этой, но по другой части). дохрена вообщем от чего зависит)))
Хеш — это реализация ассоциативного массива, но так как она очень распространена из-за того что кушает меньше памяти, чем, например, красно-чёрные деревья...
она очень распространена из-за того что у неё сложность поиска и построения O(1), (хотя памяти действительно кушает меньше чем в т.ч. и красно-чёрные деревья).
а у красно-чёрные деревья сложность O(log(n)).
второе: красно-чёрные деревья строятся путем балансировки, а балансировка осуществляется путем сравнения ключей на «больше/меньше» меж собой, (в отличие от хэш-таблицы, где требуется вычислять свертку ключа в значение и уметь сравнивать ключи на «равно/не_равно»).
поэтому: красно-чёрные деревья — это про сортировку, про поддержание отсортированного порядка элементов, а хэш-таблица — не про сортировку, про почти максимально возможный быстрый доступ (быстрее только прямое обращение по индексу в массив).
её там изначально нет, всё остальное — спецэффекты конретных реализаций))
О(1) это же когда можно за один шаг получить элемент хранилища. То есть у массивов. А в хеше делается crc-сумма ключа, берётся от неё модуль по количеству элементов (индекс) и проверяется: есть ли там этот ключ или другая crc-сумма. Если другая, то алгоритм пробует то же с предыдущей ячейкой и т. д., пока не найдёт или не порадётся незаполненная ячейка. То есть при самом неблагоприятном случае мы можем обойти весь хеш в поисках ключа (О(n)).
Но такой случай маловероятен, поэтому, округляя будет О(1) в среднем... Опять же, если массив с элементами хеша будет заполнен не более чем на 80%.
Ещё есть реализация хеша, когда ячейка занята, то за ней прикрепляется список, чтобы добавлять другие элементы с ключами с одинаковым индексом: index(key)=crc32(key) % length_hash.
Ну это так: к слову. На самом деле в веб-программировании 99% времени уходит на получение данных, поэтому важнее оптимизировать базу и кеши, чем, например, переписывать приложение с языка сверхвысокого уровня (perl, python, php, ruby, groovy или node) на язык высокого (c#, java, julia, d) или среднего уровня (c++, go).
Видимо не "пейджинг", а всё же пагинация...
И пейджинг и пагинация - одно и то же английское слово в разных транскрипциях. В лингвистике, для создания заимствованных слов, и просто, чтобы озвучить иностранное слово, используют транскрипцию - правила преобразования звуков или букв одного языка в звуки или буквы другого. При этом транскрипцию выдумывает какой-то лингвист, а лингвист из другого города - свою.
Вот здесь чуть понятнее написано. А то метки, нумерация и так далее сбивает с пути истинного.
https://use-the-index-luke.com/sql/partial-results/fetch-next-page
Давайте бросим взгляд на эту проблему на примере ...
Сходу тут же на хабре я нашёл сразу две статьи, раскрывающие зачем, почему и как надо вообще решать эту проблему:
Было бы неплохо хотя бы на них сослаться для более полного раскрытия сути проблемы.
$value =~ /^-?\d+(\.\d+)?$/ комментарий к этой строке. Я заметил, что в приведённом списке use нет utf8. Но при этом нет никаких пояснений почему, современные же программисты на Perl, особенно не-американские, (которые пока ещё существуют) практически стандартно к warnings и strict добавляют utf8. Если же его добавить, то класс \d внезапно расширяется, что вряд ли понравится базе данных:
$ perl -Mstrict -Mwarnings -Mutf8 -E '
my $v ="\x{667}\x{666}"; say "yes" if $v=~/^\d+$/
'
yesКогда-то давно я тоже ручками составлял условия для where, но затем таки перешёл на использование модуля SQL::Abstract.
В общем, извиняюсь, но у меня нашлось что прокомментировать.
Вообще, статьи по перлу я приветствую, не зря сам подписан на этот тег.
Приведённые Вами статьи действительно поднимают проблему медленного OFFSET-а, но не раскрывают решения проблемы: в них просто сказано "используйте id" - и всё. У меня же расписано как применить метку начала страницы (закладку) для нескольких полей, используя ORDER BY и WHERE.
Насчёт \d в юникоде - согласен. Подредактировал функцию quote - добавил use bytes;. На самом деле лучше использовать $dbh->quote. Ну а ещё базе может не понравится слишком большое число 1000000000000000000, например. )
SQL::Absctract видел на боевом коде, как и DBIx::Class, сам написал ORM (https://github.com/darviarush/rubin-forms/blob/master/mark/00-orm.markdown), но пришёл к выводу, что использование SQL проще и продуктивнее. А для квотирования параметров использую функцию вида:
my @rows = query "SELECT id FROM product WHERE price=:price", price => 5000;но не раскрывают решения проблемы
Если же Вы не увидели в тех статьях решений, то загляните к ним в комментарии хотя бы. Тема, как видно по статьям, совсем не новая.
SQL::Absctract видел на боевом коде
Так получилось, что не хотя этого, мне тоже довелось написать ORM: https://metacpan.org/pod/DBIx::Struct и таки я придерживаюсь мнения, что условие удобнее и надёжнее не ручками составлять и заботиться о правильном квотинге, а через модуль, на который есть много тестов и проверенного кода. Но, как говорится, YMMV.
Прочёл комментарии. В них приводятся примеры по одному полю. У меня же метод для N-го количества полей. Часто нужно сортировать по нескольким полям, из-за этого усложняются условия в WHERE.
Что же до слоёв абстракций, то каждый слой увеличивает количество точек отказа. То есть чем больше вы будете использовать технологий и библиотек, тем больше багов поймаете. Это связано с тем, что Вам придётся следить за версиями этих библиотек, при переносе на другое оборудование часть библиотек может не установится или работать иначе, разработчикам придётся изучать не только SQL, но и SQL::Abstract. То есть Вы сознательно нарушаете принцип программирования KISS ("Чем проще - тем лучше").
https://metacpan.org/pod/DBIx::Struct - немного читабельней выглядит, чем DBIx. Но по сравнению с синтаксисом rubin-forms ORM - "вырви глаз". Сравните:
my $count = one_row("select count(*) from table",
{ date => {'>=', \"now() - interval '1 hour'"})
->count;и
my $count = $app->model->table
->find(date__ge => \"now() - interval '1 hour'")
->count;Справедливости ради, а не спора о вкусе для, там для каунтов ещё один пример в документации есть:
my $count = one_row([table => "-count"],
{ date => {'>=', \"now() - interval '1 hour'"}});Пример с select приведён как вариант использования для произвольных запросов.
У меня же метод для N-го количества полей.
Было бы неплохо, если б именно на этом и было сконцентрировано внимание стати -- на новаторском подходе, на том, чем это отлично от предыдущих статей на этом ресурсе или на стековерфлоу (ссылки по теме на статьи оттуда я не стал приводить, чтобы не загромождать мой комментарий).
Пейджинг страниц в соцсетях