Pull to refresh

Comments 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 сразу поднимает проблемму педжинейшн, как буд-то других задач и проблем связанных с мускулом больше нет…
Ну заодно можно было привести пример запроса, так как то не красиво получилось, но и за описание спасибо.

По поводу радости о публикациях в данном блоге, думаю будет много довольных людей если вы предложите темы для обсуждения
ну, я уже предлагал в своих статьях… по 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
Ваш запрос пролетает мимо querycache в отличии от первого запроса.
и почему это он пролетает? ничего подобного, с чего Вы решили?

С директивой 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 ALL

UNION это алиас для UNION DESTINCT, т.е. что UNION, что UNION ALL обе конструкции используют временные таблицы, только для UNION эта временная таблица имеет еще и UNIQUE KEY, из-за чего она медленные работает на вставках.

поэтому используйте UNION ALL и в последующих запросах исключайте с помощью фильтров записи, которые попали в предыдущие. и будет вам счастье.

Можете просмтореть мои статьи я там об этом писал.

Или можем обсудить и посмотреть на ваши данные и ваши запросы, если хотите :-)
предлагаю переместить эту дискуссию в ЛС, а потом сюда отписаться о результатах:)
ПС. ориентировочно — в понедельник
Да, без проблем :-)
стучите — поговорим
думаю автор предполагал, что мы сделаем индекс и по price, height, id
тогда никаких тормозов с этим запросом. и работать будет все же быстрее union all ;)

но всему этому обратная сторона медали… 100к записей рандомных у меня весят около 5 мб, а индексы в сумме 10 мб. да и вставка записей будет с каждым разом медленнее и медленне… потому… ваш вариант имхо предпочтительей.
Вы правы, предполагается, что индекс по price, height, id есть. Но даже если Вы перепишете запрос через UNION ALL, то он все-равно понадобится, если Вы захотите отсортировать записи по height, price и при этом ни height, ни price не являются уникальным полем.

Что касается размера индексов и времени вставки… Тут все сильно зависит от проекта. Обычно все же вставки происходят намного реже. Вы вставляете статью 1 раз, а прочитать ее могут, скажем 1000 человек. Даже если селект будет делаться не все 1000 раз благодаря кэшу, то выигрыш все-равно очевиден. А по поводу того, что с каждым разом вставки будут все медленнее — очень спорный вопрос…
позвольте уточнить, что запрос с UNION правомерен только в случае первых N записей а если же я делаю limit 1000,20 то результаты будут совсем разные!
так что он не верен… Или я что то не догоняю?
UFO just landed and posted this here
UFO just landed and posted this here
Вы, что!

Мы же про мускул говорим! А в мускуле вложенные запросы почти всегда зло.
Такие

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)
UFO just landed and posted this here
Я не склонен полагать… Я в этом абсолютно уверен.

ха ха, нет 3.23 :-))
UFO just landed and posted this here
Если Ваши данные не закэшированы в памяти, то будет сильный тормоз из-за того, что mysql будет бежать с самой первой записи до 1000000
UFO just landed and posted this here
Заметьте, я не говорл, что это хороший вариант, я просто сказал, что тот запрос плох.

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

вариант SELECT list, of, fields, MAX(id)

where id > $Id

LIMIT 11

когда нужно 10

Вполне имеет право на жизнь, я сам использовал такой способ.
Вы знаете, все-же этот способ не очень хорош тем, что скорость его выполнения зависит от того, на которой странице Вы находитесь. Если Вы сделаете WHERE id>1000000 ORDER BY id LIMIT 10, то этот запрос по скорости не будет зависеть от того, какое число будет в id>XXX.
UFO just landed and posted this here
100 млн мускль не потянет. да и зачем ему столько скармливать. эффективнее в таком случае разбивать на таблички по 0,5—1 млн.
ладно, не буду спорить.
мой случай: базу статистики с уймой полей, когда на 4млн полей база занимает уже гектар, мускулю стаёт плохо искать. добавлять ещё хуже.
На самом деле, как я уже писал, все зависит от данных и частоты обновлений.

Мы как-то кешировали первые 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'
UFO just landed and posted this here
все тот же 5.0.32, который использовался при написании статьи
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… и подходит не столько сайтам сколько приложениям
Именно. Я упомянул в самом начале, что используя предложенную технологию можно и не отказываться от стандартного меню навигации — все зависит от Вашей фантазии и умения работы с кэшем:)
Замечательно! Уяснил для себя одну неочевидную вещь.
Интересно будет ли эта проблема решена когда либо на стороне сервера.
Чтобы лимит работал так как должен и не приходилось бы шаманить.
А как он должен? В чем он сейчас работает не правильно?
Тем что умные мальчики прочитавшие толстенькие книжечки не знают что ЗАСАДА
помню начинал один сайт, работал он хорошо и быстренько, а через пару лет началось…

Эх, прям как в жизни — мечты и ожидания разбились о быт :)
Ну и все таки, что неправильного в поведении limit?
При увеличении офсета начинает тормозить.
А вообще интересно — почему во многих SQL диалектах такого оператора как LIMIT просто нет…
Умненьким мальчикам стоит подумать, ЧТО происходит при использовании offset.
Если бы это было бы сразу понятно об этом не создавали бы топики.
Без ковыряния статистики так сразу и не поймёшь что за грабля.
UFO just landed and posted this here
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 — не будем

Я так понимаю никто лимиты по НЕ ключам не делает. Осталось эту вот «офсетную» информацию в индексе хранить.

UFO just landed and posted this here
Я так понимаю никто лимиты по НЕ ключам не делает

Не будьте так категоричны.

Осталось эту вот «офсетную» информацию в индексе хранить.

Вы действительно не представляете что такое индексы.
Если хранить порядковый номера ключа в индексе, при в ставке в значения с минимальным ключом необходимо перебабашить все узлы индекса, а это непозволительная роскошь.
почему во многих SQL диалектах такого оператора как LIMIT просто нет

Именно limit нет, но есть FIRST SKIP (кажется это по стандарту), вроде бы в MS SQL есть TOP (могу ошибаться)
Очень интересно, как для меня больше это можно применить к MS SQL так как там блага(или для кого зла) LIKE нет, а TOP указывает только на количество строк которое нужно выбрать.
отлично! вот за это «WHERE id>10 ORDER BY id LIMIT 10;» огромное спасибо
А что делать в случае, если пользователь хочет перейти на последнюю страницу?
UFO just landed and posted this here
Мне больше интересно что нужно делать когда пользователь хочет перейти на страницу в середине.

Про последнюю страницу есть в статье:
SELECT id FROM items ORDER BY id DESC LIMIT 10;
Ну да, на любую страницу, которая не является следующей… ведь в этом случае ID не получится высчитать…
UFO just landed and posted this here
Вы не совсем правильно поняли…
С одной стороны, надо было бы получить количество записей, и взять остаток от деления на количество записей на странице. То есть, если у нас получилось 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 не раскрыта ;)
эм… совсем без уникального поля не обойтись… А пример с сортировкой по неуникальному полю есть в статье, но при этом уникальное поле тоже используется:)
упс… прочитал по диагонали. Ваш вариант уйдет в filesort на сортировке.
неа… Специально все запросы через EXPLAIN прогонял. Вы про какой запрос конкретно?
Отличный топик. Беру на вооружение. Надо будет посмотреть, какие запросы выполняет Zend_Paginator.

Кстати, если все запросы и результаты оформить тегами <code> и <pre>, будет просто великолепно.
Не могу, к сожалению, проплюсовать — пост отменный! Спасибо! На некоторые вещи я не обращал внимания.
«перемножить значения rows.»? может суммировать? или я не внимателен?
Ну вот наконец-то эту проблему разжевали. а то я когда--то думал, что отсутствие в других БД LIMIT offset, limit — это недостаток. счас понимаю, что это способ заставить разработчика писать правильно)
а как решать данную проблему если реально необходима навигация типа
..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'у не в мускле? И как они работают?
хорошая статья… для огромных проектов-- очень полезно… а в реале… в разделе обычных сайтов редко бывает более 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

Спасибо за статью, вот сегодня на нее напоролся, очень помогла.
Only those users with full accounts are able to leave comments. Log in, please.

Articles