Хабр Курсы для всех
РЕКЛАМА
Практикум, Хекслет, SkyPro, авторские курсы — собрали всех и попросили скидки. Осталось выбрать!
Хабр доступен 24/7 благодаря поддержке друзей
Комментарии 81
Спасибо. Отлично разжевано.
Я так понял индекс height, id нужен для условия (height=0 AND id>1174)?
Имено. Ну и для ORDER height, id
Индекс то нужен, причем несколько, в том числе составные. И в MySQL 5 они даже будут использоваться
используя index merge алгоритм. Но все же лкчше такие запросы (с такими условиями)
WHERE (price>5) OR (price=5 AND height>0) AND (price=5 AND height=0 AND id>1174)
не писать, а переписывать их на запросы с UNION ALL, хоть и букаф больше, но работают значительно быстрей, притом если у вас есть общий лимит, скажем вывести 10 записей, то просто нужно добавить лимит в каждый из UNION-ов и в итоге, у вас получается датасет максимум из 30 записей (для 3х юнионов) который впоследствии сортируется и из него достаются 10; а не как в приведенном случае огромный датасет, который будет сортироваться используя огромтую темпорари тейбл. Вы можете проверить это с помощью все той же команды SHOW STATUS и посмотреть кол-во записей и чтений.
Ну, я автору, конечно плюс поставил, для меня любая статься в блоге MySQL за радость, только почему-то мне кажется, что каждый автор, который пишет про MySQL сразу поднимает проблемму педжинейшн, как буд-то других задач и проблем связанных с мускулом больше нет…
используя index merge алгоритм. Но все же лкчше такие запросы (с такими условиями)
WHERE (price>5) OR (price=5 AND height>0) AND (price=5 AND height=0 AND id>1174)
не писать, а переписывать их на запросы с UNION ALL, хоть и букаф больше, но работают значительно быстрей, притом если у вас есть общий лимит, скажем вывести 10 записей, то просто нужно добавить лимит в каждый из UNION-ов и в итоге, у вас получается датасет максимум из 30 записей (для 3х юнионов) который впоследствии сортируется и из него достаются 10; а не как в приведенном случае огромный датасет, который будет сортироваться используя огромтую темпорари тейбл. Вы можете проверить это с помощью все той же команды SHOW STATUS и посмотреть кол-во записей и чтений.
Ну, я автору, конечно плюс поставил, для меня любая статься в блоге MySQL за радость, только почему-то мне кажется, что каждый автор, который пишет про MySQL сразу поднимает проблемму педжинейшн, как буд-то других задач и проблем связанных с мускулом больше нет…
Ну заодно можно было привести пример запроса, так как то не красиво получилось, но и за описание спасибо.
По поводу радости о публикациях в данном блоге, думаю будет много довольных людей если вы предложите темы для обсуждения
По поводу радости о публикациях в данном блоге, думаю будет много довольных людей если вы предложите темы для обсуждения
ну, я уже предлагал в своих статьях… по MySQL написанных ранее.
Сейчас небольшой завал на рпботе, но если будет интересно сообществу, то напишу.
Просто немного уже надоело читать про педжинейшины.
Ну а по поводу запроса. Я упоминал у себя в статьях об этой технике, так что там смотрите подробности почему так лучше. ну а запрос, так уж и быть набросаю
Вместо этого запроса
SELECT SQL_NO_CACHE * FROM items WHERE (price>5) OR (price=5 AND height>0) OR (price=5 AND height=0 AND id>1174) ORDER BY price, height, id LIMIT 10;
Предлагается использовать нечто подобное
select
*
FROM
(
SELECT
*
FROM
items
WHERE price>5
LIMIT 10
UNION ALL
SELECT
*
FROM
items
WHERE price=5 AND height=0 AND id>1174
LIMIT 10
UNION ALL
SELECT
*
FROM
items
WHERE price=5 AND height>0
LIMIT 10
) as table
LIMIT 10
Сейчас небольшой завал на рпботе, но если будет интересно сообществу, то напишу.
Просто немного уже надоело читать про педжинейшины.
Ну а по поводу запроса. Я упоминал у себя в статьях об этой технике, так что там смотрите подробности почему так лучше. ну а запрос, так уж и быть набросаю
Вместо этого запроса
SELECT SQL_NO_CACHE * FROM items WHERE (price>5) OR (price=5 AND height>0) OR (price=5 AND height=0 AND id>1174) ORDER BY price, height, id LIMIT 10;
Предлагается использовать нечто подобное
select
*
FROM
(
SELECT
*
FROM
items
WHERE price>5
LIMIT 10
UNION ALL
SELECT
*
FROM
items
WHERE price=5 AND height=0 AND id>1174
LIMIT 10
UNION ALL
SELECT
*
FROM
items
WHERE price=5 AND height>0
LIMIT 10
) as table
LIMIT 10
Ваш запрос пролетает мимо querycache в отличии от первого запроса.
и почему это он пролетает? ничего подобного, с чего Вы решили?
С директивой SQL_NO_CACHE шансов пролететь у первого запроса неймоверно больше :-)))
С директивой SQL_NO_CACHE шансов пролететь у первого запроса неймоверно больше :-)))
Оказывается я до сих пор был в преступном неведении.
Ваша правда — попадает!
Ретируюсь :)
Ваша правда — попадает!
Ретируюсь :)
я, честно-говоря, не могу понять, что Вас сбило с толку и привело к таким мыслям, что может не попасть. В запросе нет недетерминированных ф-ий типа RAND, CURRENT_TIME, NOW() и т.д.
Это обычный запрос, состоящий из нескольких запросов, результаты которых, кстати, закешированы не будут, я имею в виду каждый по отдельности. Но результат общего запроса в кеш попадет
Это обычный запрос, состоящий из нескольких запросов, результаты которых, кстати, закешированы не будут, я имею в виду каждый по отдельности. Но результат общего запроса в кеш попадет
Вы знаете, специально не стал писать здесь про index merge, так как, если честно мне редко удается его добиться… Пробовал даже (x AND y) OR z = (x OR z) AND (y OR z), но говорят, что mysql пока не очень умно с ним работает…
Что касается UNION — проверил конкретно на своих примерах — работает медленнее, поэтому и не стал про это писать:)
Что касается UNION — проверил конкретно на своих примерах — работает медленнее, поэтому и не стал про это писать:)
Да, индекс мердж пока оставляет желать лучшего.
А вы попробуйте не UNION, а UNION ALL
UNION это алиас для UNION DESTINCT, т.е. что UNION, что UNION ALL обе конструкции используют временные таблицы, только для UNION эта временная таблица имеет еще и UNIQUE KEY, из-за чего она медленные работает на вставках.
поэтому используйте UNION ALL и в последующих запросах исключайте с помощью фильтров записи, которые попали в предыдущие. и будет вам счастье.
Можете просмтореть мои статьи я там об этом писал.
Или можем обсудить и посмотреть на ваши данные и ваши запросы, если хотите :-)
А вы попробуйте не UNION, а UNION ALL
UNION это алиас для UNION DESTINCT, т.е. что UNION, что UNION ALL обе конструкции используют временные таблицы, только для UNION эта временная таблица имеет еще и UNIQUE KEY, из-за чего она медленные работает на вставках.
поэтому используйте UNION ALL и в последующих запросах исключайте с помощью фильтров записи, которые попали в предыдущие. и будет вам счастье.
Можете просмтореть мои статьи я там об этом писал.
Или можем обсудить и посмотреть на ваши данные и ваши запросы, если хотите :-)
думаю автор предполагал, что мы сделаем индекс и по price, height, id
тогда никаких тормозов с этим запросом. и работать будет все же быстрее union all ;)
но всему этому обратная сторона медали… 100к записей рандомных у меня весят около 5 мб, а индексы в сумме 10 мб. да и вставка записей будет с каждым разом медленнее и медленне… потому… ваш вариант имхо предпочтительей.
тогда никаких тормозов с этим запросом. и работать будет все же быстрее union all ;)
но всему этому обратная сторона медали… 100к записей рандомных у меня весят около 5 мб, а индексы в сумме 10 мб. да и вставка записей будет с каждым разом медленнее и медленне… потому… ваш вариант имхо предпочтительей.
Вы правы, предполагается, что индекс по price, height, id есть. Но даже если Вы перепишете запрос через UNION ALL, то он все-равно понадобится, если Вы захотите отсортировать записи по height, price и при этом ни height, ни price не являются уникальным полем.
Что касается размера индексов и времени вставки… Тут все сильно зависит от проекта. Обычно все же вставки происходят намного реже. Вы вставляете статью 1 раз, а прочитать ее могут, скажем 1000 человек. Даже если селект будет делаться не все 1000 раз благодаря кэшу, то выигрыш все-равно очевиден. А по поводу того, что с каждым разом вставки будут все медленнее — очень спорный вопрос…
Что касается размера индексов и времени вставки… Тут все сильно зависит от проекта. Обычно все же вставки происходят намного реже. Вы вставляете статью 1 раз, а прочитать ее могут, скажем 1000 человек. Даже если селект будет делаться не все 1000 раз благодаря кэшу, то выигрыш все-равно очевиден. А по поводу того, что с каждым разом вставки будут все медленнее — очень спорный вопрос…
позвольте уточнить, что запрос с UNION правомерен только в случае первых N записей а если же я делаю limit 1000,20 то результаты будут совсем разные!
так что он не верен… Или я что то не догоняю?
так что он не верен… Или я что то не догоняю?
Вы, что!
Мы же про мускул говорим! А в мускуле вложенные запросы почти всегда зло.
Такие
select * from table where id in (select id from table where height>100 LIMIT 100000,10)
всегда зло!
Мускул не выносит вложенные запросы наверх. Поэтому этот страх и ужас select id from table where height>100 LIMIT 100000,10 выполнится многократно.
Насколько я понимаю Вы хотели использоваться covering index таким образом.
Ну и делали бы себе джоин
Select
*
From
table
JOIN
(
select id from table where height>100 LIMIT 100000,10
)as t
USING (id)
Мы же про мускул говорим! А в мускуле вложенные запросы почти всегда зло.
Такие
select * from table where id in (select id from table where height>100 LIMIT 100000,10)
всегда зло!
Мускул не выносит вложенные запросы наверх. Поэтому этот страх и ужас select id from table where height>100 LIMIT 100000,10 выполнится многократно.
Насколько я понимаю Вы хотели использоваться covering index таким образом.
Ну и делали бы себе джоин
Select
*
From
table
JOIN
(
select id from table where height>100 LIMIT 100000,10
)as t
USING (id)
Если Ваши данные не закэшированы в памяти, то будет сильный тормоз из-за того, что mysql будет бежать с самой первой записи до 1000000
Заметьте, я не говорл, что это хороший вариант, я просто сказал, что тот запрос плох.
На самом деле проблема паджинейшн она во многом зависит от того в каком состоянии данные и каккое колличество этих данных вообще, т.е. если дырки в данных и т.д.
Исходя из этого нужно выбирать способ для паджиенйшн.
вариант SELECT list, of, fields, MAX(id)
…
where id > $Id
…
LIMIT 11
когда нужно 10
Вполне имеет право на жизнь, я сам использовал такой способ.
На самом деле проблема паджинейшн она во многом зависит от того в каком состоянии данные и каккое колличество этих данных вообще, т.е. если дырки в данных и т.д.
Исходя из этого нужно выбирать способ для паджиенйшн.
вариант SELECT list, of, fields, MAX(id)
…
where id > $Id
…
LIMIT 11
когда нужно 10
Вполне имеет право на жизнь, я сам использовал такой способ.
Вы знаете, все-же этот способ не очень хорош тем, что скорость его выполнения зависит от того, на которой странице Вы находитесь. Если Вы сделаете WHERE id>1000000 ORDER BY id LIMIT 10, то этот запрос по скорости не будет зависеть от того, какое число будет в id>XXX.
На самом деле, как я уже писал, все зависит от данных и частоты обновлений.
Мы как-то кешировали первые 10 страниц и хранили их айдишникив мемкеше, т.е. для первых 10 страниц с постраничным расположением статей.
Ну а если страниц очень много, то тут и горизонтальный шардинг вполне может вам приголится.
Пути господни неисповедимы :-) Для задач высокой нагрузки любое решение правильно, которое работает.
Мы как-то кешировали первые 10 страниц и хранили их айдишникив мемкеше, т.е. для первых 10 страниц с постраничным расположением статей.
Ну а если страниц очень много, то тут и горизонтальный шардинг вполне может вам приголится.
Пути господни неисповедимы :-) Для задач высокой нагрузки любое решение правильно, которое работает.
mysql> select * from items where id in (select id from items where height>100 LIMIT 90000,10);
ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
select * from table AS a where EXISTS (select 1 from table where id=a.id AND height>100 LIMIT 100000,10)
Потрясающе. Воистину, оптимизировать можно бесконечно.
Автору спасибо. Сам сталкивался с такой задачей — решили используя трюк с выборкой «11 записей вместо 10» что позволяет знать, а есть ли следующая страница.
Кстати помимо этого когда юзер начинает листать мы читаем не «11» а «21» запись, т.е. 2 страницы сразу… ведь если юзер начал листать — то врядли остановится ))… размер «кеша» можно и увеличивать если мы видим что юзер настырный… но это больше в сторону javascrit-а а не mysql… и подходит не столько сайтам сколько приложениям
Кстати помимо этого когда юзер начинает листать мы читаем не «11» а «21» запись, т.е. 2 страницы сразу… ведь если юзер начал листать — то врядли остановится ))… размер «кеша» можно и увеличивать если мы видим что юзер настырный… но это больше в сторону javascrit-а а не mysql… и подходит не столько сайтам сколько приложениям
Замечательно! Уяснил для себя одну неочевидную вещь.
Интересно будет ли эта проблема решена когда либо на стороне сервера.
Чтобы лимит работал так как должен и не приходилось бы шаманить.
Чтобы лимит работал так как должен и не приходилось бы шаманить.
А как он должен? В чем он сейчас работает не правильно?
Тем что умные мальчики прочитавшие толстенькие книжечки не знают что ЗАСАДА
помню начинал один сайт, работал он хорошо и быстренько, а через пару лет началось…
Эх, прям как в жизни — мечты и ожидания разбились о быт :)
помню начинал один сайт, работал он хорошо и быстренько, а через пару лет началось…
Эх, прям как в жизни — мечты и ожидания разбились о быт :)
Ну и все таки, что неправильного в поведении limit?
При увеличении офсета начинает тормозить.
А вообще интересно — почему во многих SQL диалектах такого оператора как LIMIT просто нет…
А вообще интересно — почему во многих SQL диалектах такого оператора как LIMIT просто нет…
Умненьким мальчикам стоит подумать, ЧТО происходит при использовании offset.
SELECT MIN,MAX,COUNT from atable
Берем два запроса
SELECT * FROM atable WHERE 1 LIMIT offset,numrows — будем тормозить и читать лишнее
SELECT * FROM atable WHERE id>(MIN+((MAX-MIN)/COUNT)*offset LIMIT numrows — не будем
Я так понимаю никто лимиты по НЕ ключам не делает. Осталось эту вот «офсетную» информацию в индексе хранить.
Берем два запроса
SELECT * FROM atable WHERE 1 LIMIT offset,numrows — будем тормозить и читать лишнее
SELECT * FROM atable WHERE id>(MIN+((MAX-MIN)/COUNT)*offset LIMIT numrows — не будем
Я так понимаю никто лимиты по НЕ ключам не делает. Осталось эту вот «офсетную» информацию в индексе хранить.
Я так понимаю никто лимиты по НЕ ключам не делает
Не будьте так категоричны.
Осталось эту вот «офсетную» информацию в индексе хранить.
Вы действительно не представляете что такое индексы.
Если хранить порядковый номера ключа в индексе, при в ставке в значения с минимальным ключом необходимо перебабашить все узлы индекса, а это непозволительная роскошь.
почему во многих SQL диалектах такого оператора как LIMIT просто нет
Именно limit нет, но есть FIRST SKIP (кажется это по стандарту), вроде бы в MS SQL есть TOP (могу ошибаться)
Очень интересно, как для меня больше это можно применить к MS SQL так как там блага(или для кого зла) LIKE нет, а TOP указывает только на количество строк которое нужно выбрать.
отлично! вот за это «WHERE id>10 ORDER BY id LIMIT 10;» огромное спасибо
А что делать в случае, если пользователь хочет перейти на последнюю страницу?
Мне больше интересно что нужно делать когда пользователь хочет перейти на страницу в середине.
Про последнюю страницу есть в статье:
SELECT id FROM items ORDER BY id DESC LIMIT 10;
Про последнюю страницу есть в статье:
SELECT id FROM items ORDER BY id DESC LIMIT 10;
Ну да, на любую страницу, которая не является следующей… ведь в этом случае ID не получится высчитать…
Вы не совсем правильно поняли…
С одной стороны, надо было бы получить количество записей, и взять остаток от деления на количество записей на странице. То есть, если у нас получилось 1234567 записей, а выводим мы по 10, то на последней странице должно быть 7 записей начиная с 1234561-й.
Так как COUNT делать медленно, то мы можем просто показать последние 10 записей — никто даже не заметит подвоха:) Делается это через SELECT… ORDER BY id DESC LIMIT 10. В этом случае MySQL просто прочитает первые 10 записей с конца нашей таблицы, что не составит особого труда.
С одной стороны, надо было бы получить количество записей, и взять остаток от деления на количество записей на странице. То есть, если у нас получилось 1234567 записей, а выводим мы по 10, то на последней странице должно быть 7 записей начиная с 1234561-й.
Так как COUNT делать медленно, то мы можем просто показать последние 10 записей — никто даже не заметит подвоха:) Делается это через SELECT… ORDER BY id DESC LIMIT 10. В этом случае MySQL просто прочитает первые 10 записей с конца нашей таблицы, что не составит особого труда.
я не настолько крут, поэтому использую SQL_CALC_FOUND_ROWS
Если у Вас нет проблем с производительностью — то этого вполне достаточно. Единственный момент, лучше все-таки делать два запроса — один, который делать COUNT (и его резальтат кэшировать) + запрос, который собственно выбирает данные (который использует LIMIT… OFFSET). Если вы будете использовать SQL_CALC_FOUND_ROWS в КАЖДОМ запросе, то это будет практически равносильно выполению SELECT без LIMIT.
Спасибо, что написали эту статью.
Помимо того, что честный пейджер медленнее, он еще и не очень удобен из-за того, что у страницы нет постоянной ссылки, т.к. контент постоянно перетекает из страницы в страницу. Поэтому бешенные поисковые роботы начинают усиленно сканировать всю эту радость.
Еще было бы узнать, как автор предлагает бороться со случаями, когда сортировка идет не по уникальному полю. Тема reverse_id не раскрыта ;)
Помимо того, что честный пейджер медленнее, он еще и не очень удобен из-за того, что у страницы нет постоянной ссылки, т.к. контент постоянно перетекает из страницы в страницу. Поэтому бешенные поисковые роботы начинают усиленно сканировать всю эту радость.
Еще было бы узнать, как автор предлагает бороться со случаями, когда сортировка идет не по уникальному полю. Тема reverse_id не раскрыта ;)
Отличный топик. Беру на вооружение. Надо будет посмотреть, какие запросы выполняет Zend_Paginator.
Кстати, если все запросы и результаты оформить тегами <code> и <pre>, будет просто великолепно.
Кстати, если все запросы и результаты оформить тегами <code> и <pre>, будет просто великолепно.
Не могу, к сожалению, проплюсовать — пост отменный! Спасибо! На некоторые вещи я не обращал внимания.
«перемножить значения rows.»? может суммировать? или я не внимателен?
Ну вот наконец-то эту проблему разжевали. а то я когда--то думал, что отсутствие в других БД LIMIT offset, limit — это недостаток. счас понимаю, что это способ заставить разработчика писать правильно)
а как решать данную проблему если реально необходима навигация типа
..5 6 7 8…
для таблиц с большим объемом данных?
..5 6 7 8…
для таблиц с большим объемом данных?
Пожалуй, самый распространенный способ предложен в этом комментарии habrahabr.ru/blogs/mysql/44608/#comment_1121189 и habrahabr.ru/blogs/mysql/44608/#comment_1120939. То есть, первым запросом вы выбираете ТОЛЬКО данные, необходимые для пэйджинга (id и height, например) сразу для 10-ти страниц + 1 (101 запись, например) и кладете все это в кэш. Мотаете так же как обычно, только для каждой циферки в меню подставляете нужные параметры для id и height.
Хмм, а в постгресе лимит нормально работает? В смысле не читает каждую запись, которую надо поскипать?
И какие есть альтернативы limit'у не в мускле? И как они работают?
И какие есть альтернативы limit'у не в мускле? И как они работают?
хорошая статья… для огромных проектов-- очень полезно… а в реале… в разделе обычных сайтов редко бывает более 200-300 страниц… а вот если что-то народного пользования и с огромным количеством документов… :)
Есть вот такая идея.
Таблица новостей:
Вспомогательная таблица:
Раз в несколько минут пересоздаём вспомогательную таблицу:
Страницу с новостями выбираем так:
Если возвращает пустой результат — делаем такую (медленную только в маленькой доле запросов) выборку:
Таблица новостей:
CREATE TABLE `news` (
`id` int(10) unsigned NOT NULL auto_increment,
`dt` int(10) unsigned NOT NULL,
`text` text NOT NULL,
PRIMARY KEY (`id`),
KEY `dt` (`dt`)
);Вспомогательная таблица:
CREATE TABLE `news_order` (
`num` int(10) unsigned NOT NULL auto_increment,
`id` int(11) NOT NULL,
PRIMARY KEY (`num`),
KEY `num_id` (`num`,`id`)
);Раз в несколько минут пересоздаём вспомогательную таблицу:
TRUNCATE `news_order`; INSERT INTO `news_order` SELECT NULL, `id` FROM `news` ORDER BY `id` DESC;
Страницу с новостями выбираем так:
SELECT `news`.`id`, `news`.`dt`, `news`.`text`
FROM (
SELECT `id`
FROM `news_order`
WHERE `num` > $from AND `num` < $till
ORDER BY `num`
) AS `no`
INNER JOIN `news` ON `news`.`id` = `no`.`id`Если возвращает пустой результат — делаем такую (медленную только в маленькой доле запросов) выборку:
SELECT `id`, `dt`, `text` FROM `news` LIMIT $from, $count
Спасибо за статью, вот сегодня на нее напоролся, очень помогла.
Зарегистрируйтесь на Хабре, чтобы оставить комментарий
Постраничная навигация с MySQL при большом количестве записей