Форум PHP программистов > Постраничная навигация

[ Поиск ] - [ Пользователи ] - [ Календарь ]

Полная Версия: Постраничная навигация

kirik

16.09.2009 - 06:25

Собственно такая штука.. таблица около 50к записей (будет больше). Запрос вида

SQL

SELECT SQL_NO_CACHE `post_id` FROM `links_posts` ORDER BY `post_id` DESC LIMIT 0, 10

отрабатывается моментом (за 0.0003 сек.), а вот запрос

SQL

SELECT SQL_NO_CACHE `post_id` FROM `links_posts` ORDER BY `post_id` DESC LIMIT 50000, 10

отрабатывается уже 0.0170 сек.
(поле `post_id` праймари кей)
Отсюда вопрос, чего можно поделать с такими шутками.. 500-600 юзеров онлайн реально дают ~~просраться~~ too many connections на немного более сложных запросах..
Читал статейку (и комменты к ней естесственно), но все описанные варианты не совсем подходят.. Может подскажете чего..?

Спасибо!

Спустя 4 часа, 38 минут, 26 секунд (16.09.2009 - 10:04) glock18 написал(а):

Почему описанные варианты не подходят?
вариант с union all мне кажется достаточно туманным, и не гарантирующим точной выборки, но наверняка его можно довести до ума... на крайняк есть и вариант предложенный автором статьи. он вполне рабочий должен быть.

Или какие-то особые проблемы с этим есть?

Спустя 22 минуты, 21 секунда (16.09.2009 - 10:26) Nikitian написал(а):

Вроде читал эту статью и где-то в комментах проскакивало делать сортировку в обратном направлении, если требуются данные из большей половины.
Хотя я всё же рекомендую кешировать такие запросы, чтобы нагрузка не была роковой для машинки. Ну появится следующая страница не сейчас, А через 5 минут - велика потеря, зато сервер будет работать куда шустрее.

Спустя 25 минут, 50 секунд (16.09.2009 - 10:52) sergeiss написал(а):

Цитата (kirik @ 16.09.2009 - 06:25)

но все описанные варианты не совсем подходят

А чем не подходит вот это?

Код

Рассмотрим все на простом примере: пусть записи выдаются отсортированные по id. В этом случае нам нужно вместе со ссылкой передать id записи, на которой мы остановились. А остановимся мы на записи с id=10. То есть, в параметрах ссылки на следующую страницу нам нужно передать 10. Соответственно, для второй страницы запрос будет выглядеть так:

SELECT SQL_NO_CACHE id FROM items WHERE id>10 ORDER BY id LIMIT 10;

По-моему, "оно", то, что надо.

Спустя 31 минута, 4 секунды (16.09.2009 - 11:23) Nikitian написал(а):

sergeiss
Есть минус: если во время навигации появляются новые страницы, то они в этой навигации учтены не будут вообще, пока не зайти на первую страницу без идентификатором последней записи.
На примере: вы смотрите комменты, где первый коммент находится на последней странице. Соответственно сортировка по дате по убыванию. Вы перешли на следующую страницу, передав каким-то вторым параметром последнюю в сортировке дату. - всё, теперь более новые комменты не увидите, пока не сбросится этот идентификатор, т.к. он будет опираться только на первую сортировку и скакать от неё (т.е. для каждой страницы идентификатор будет другой, но новее ему не стать).

Спустя 5 минут, 46 секунд (16.09.2009 - 11:29) FatCat написал(а):

Цитата (kirik @ 16.09.2009 - 06:25)

чего можно поделать с такими шутками.

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

SQL

SELECT * FROM ibf_posts ORDER BY pid ASC WHERE topic_id = 12345 LIMIT 150, 15

выполняется вдесятеро дольше чем

SQL

SELECT * FROM ibf_posts ORDER BY pid ASC WHERE topic_id = 12345 LIMIT 0, 15

Решение нашлось в физическом структурировании данных.
Структурируем:

SQL

SELECT * INTO OUTFILE 'ibf_posts.sql' FROM ibf_posts ORDER BY topic_id;
TRUNKATE ibf_posts;
LOAD DATA INFILE "ibf_posts.sql" INTO TABLE ibf_posts;

И вот уже два первых запроса выполняются с одинаковой скоростью.

Спустя 21 минута, 19 секунд (16.09.2009 - 11:50) glock18 написал(а):

SQL

SELECT * INTO OUTFILE 'ibf_posts.sql' FROM ibf_posts ORDER BY topic_id;
TRUNKATE ibf_posts;
LOAD DATA INFILE "ibf_posts.sql" INTO TABLE ibf_posts;

скромный вопрос, а optimize table не дал бы тот же эффект?

Спустя 14 минут, 38 секунд (16.09.2009 - 12:05) sergeiss написал(а):

Nikitian - мне кажется, что это не проблема. Потому что при навигации где-то в начале задержки будут небольшие и можно просто "тупо" использовать лимит безо всяких "выкрутасов". А при навигации где-то далеко от начала это несущественно.

Я вот тут немного "поигрался" в Постгре... На ноуте, не очень скоростном. Взял реальную таблицу, в которой 6388929 записей. Каждая - поле "дата" и 7 целых полей. Сортировка в запросе - по дате и 2-м другим полям. Все эти поля находятся в индексе.

Выбирал по 5 записей, с разными смещениями:
0 - 10-30 мс
10000 - 40-60 мс
100000 - 150-300 мс
600000 - почти 1,5 минуты (!)

Дальнейшее исследование показало, что выборка 5 записей со смещением от 0 до 463374 происходит за время не более 700 мс, а со смещением 463375 записей и более - 80-95 секунд.
Вот последнее мне вообще непонятно - что за граница такая?

Спустя 25 минут, 15 секунд (16.09.2009 - 12:30) Nikitian написал(а):

sergeiss
Видимо в буфер не влезает и используется диск. explain что говорит?

Спустя 4 минуты, 44 секунды (16.09.2009 - 12:35) glock18 написал(а):

Цитата

более - 80-95 секунд.

Цитата

Видимо в буфер не влезает и используется диск. explain что говорит?

видимо не просто диск используется, а еще и перманентные поминки по оперативной памяти успевают сыграть.

PS: однозначно надо explain смотреть.

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

Стоит только запросом начать выбирать только id, а уже по ним всю информацию из таблицы - все начинает летать.

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

Спустя 24 минуты, 32 секунды (16.09.2009 - 12:59) sergeiss написал(а):

Цитата (Nikitian @ 16.09.2009 - 13:30)

explain что говорит?

Если бы я до конца понимал, что он говорит

Вот запрос такой

SQL

select * from handovers order by date_, lac, cellid
limit 5 offset 463375

А вот, что говорит explain

Код

offset 463374
"Limit (cost=1364547.00..1364561.73 rows=5 width=36) (actual time=1860.456..1860.474 rows=5 loops=1)"
" -> Index Scan using date_handovers on handovers (cost=0.00..18814162.89 rows=6388929 width=36) (actual time=0.183..1222.032 rows=463379 loops=1)"
"Total runtime: 1860.576 ms"

offset 463375
"Limit (cost=1364563.87..1364563.89 rows=5 width=36) (actual time=106573.340..106573.358 rows=5 loops=1)"
" -> Sort (cost=1363405.44..1379377.76 rows=6388929 width=36) (actual time=104816.103..105962.603 rows=463380 loops=1)"
" Sort Key: date_, lac, cellid"
" Sort Method: external merge Disk: 324632kB"
" -> Seq Scan on handovers (cost=0.00..117131.29 rows=6388929 width=36) (actual time=0.025..10274.524 rows=6388929 loops=1)"
"Total runtime: 106601.495 ms"

Да, при бОльшем смещении выделяется что-то дополнительно. Скорее всего, на жестком диске. И проводится анализ, похоже, всех данных без исключения, всех 6 с лишним миллионов записей

Спустя 2 часа, 59 минут, 45 секунд (16.09.2009 - 15:59) FatCat написал(а):

Цитата (glock18 @ 16.09.2009 - 12:50)

а optimize table не дал бы тот же эффект?

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

Обращение к БД - это в том числе и чтение из файла головкой винчестера по кластерам. Если данные разбросаны по разным кластерам, дополнительное время расходуется на считывание информации, потому и задержки.

Спустя 9 минут, 46 секунд (16.09.2009 - 16:09) waldicom написал(а):

Цитата (FatCat @ 16.09.2009 - 14:59)

Цитата (glock18 @ 16.09.2009 - 12:50)

а optimize table не дал бы тот же эффект?

SQL

SELECT * INTO OUTFILE 'ibf_posts.sql' FROM ibf_posts ORDER BY topic_id;
TRUNKATE ibf_posts;
LOAD DATA INFILE "ibf_posts.sql" INTO TABLE ibf_posts;

Пардон, если скажу глупость, но такая конструкция не гарантирует того, что все данные встанут по порядку... Как на винте место будет, так операционная система их и распределит.
Или не так?

Спустя 16 минут, 7 секунд (16.09.2009 - 16:25) Nikitian написал(а):

Цитата (FatCat @ 16.09.2009 - 12:59)

Цитата (glock18 @ 16.09.2009 - 12:50)

а optimize table не дал бы тот же эффект?

Хм, а мануал говорит иначе:

Цитата

Команда OPTIMIZE TABLE должна использоваться после удаления большей части таблицы или если в таблице было внесено много изменений в строки переменной длины (таблицы, в которых есть столбцы VARCHAR, BLOB или TEXT). Удаленные записи поддерживаются при помощи связного списка, и последующие операции INSERT повторно используют позиции старых записей. Чтобы перераспределить неиспользуемое пространство и дефрагментировать файл данных, можно воспользоваться командой OPTIMIZE TABLE.

Спустя 2 часа, 8 минут, 29 секунд (16.09.2009 - 18:33) FatCat написал(а):

Цитата (waldicom @ 16.09.2009 - 17:09)

Как на винте место будет, так операционная система их и распределит.

Да, конечно, фрагментация файлов вполне возможна. Но это такая мелочь в сравнении с фрагментацией данных внутри файла...
В форуме строки добавляются в порядке написания сообщений в разные топики, а считываются группой для одного топика. Если топик большой и в него писались сообщения весь срок жизни таблицы - сообщения будут разбросаны по всей таблице сообщений; и программе придется пробегать по кластерам практически всего файла; время, сопоставимое с временем чтения всего файла.

Цитата (Nikitian @ 16.09.2009 - 17:25)

Хм, а мануал говорит иначе:

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

Фокус с физической структуризацией файла мы придумали и отладили еще в стареньком FoxPro 2.0 с базами данных медицинской статистики на сотни тысяч строк. Машины были тогда "двушки" с процессорами 20, максимум 40, и билд отчета с неструктурированной базы занимал десятки минут; структурирование файла базы позволяло билдить отчеты за единицы минут...
В фоксе это все проще делалось: копированием таблицы по критерию сортировки. Была несортированная таблица, куда постоянно вносились новые записи, а для генерации отчетов были сделаны батники, запускавшие копирование таблицы с нужной сортировкой: для бухгалтерии свои сортировки, для медстатистики свои, для фармстатистики третьи...

Спустя 3 часа, 32 минуты, 15 секунд (16.09.2009 - 22:06) kirik написал(а):

Цитата (glock18 @ 16.09.2009 - 02:04)

Почему описанные варианты не подходят?

Тот вариант что описан в статье не подходит по причине непростой навигации, тоесть во-первых у юзера есть выбор страниц (не только вперед-назад), во-вторых он может выбрать сколько постов ему выводить на странице, и в-третьих, пожалуй самое непростое при таком алгоритме: юзер может сам указать в поле ввода, на какую страницу он хочет перейти.
А в комментах уж очень какие-то хлипкие варианты, мне показалось..

Цитата (Nikitian @ 16.09.2009 - 02:26)

Хотя я всё же рекомендую кешировать такие запросы, чтобы нагрузка не была роковой для машинки.

Тут тоже не все так просто.. Слишком много изменяемых параметров запроса, чтобы все кэшировать: сортировка может проводиться по одному полю из 10, количество выводимых строк может варьироваться (выше описал).. все варианты не закэшируешь..

Цитата (glock18 @ 16.09.2009 - 04:35)

Стоит только запросом начать выбирать только id, а уже по ним всю информацию из таблицы - все начинает летать.

Угу.. как-то так..

Цитата (FatCat @ 16.09.2009 - 03:29)

Решение нашлось в физическом структурировании данных.

Хм.. ну это нужно, насколько я понимаю, когда происходят большие удаления, или изменения в таблицах.. А когда например данные добавляются, но не изменяются (допустим какой-нибудь лог), это не спасет?

Решение лежит на поверхности, нужно просто придумать че-нить универсально-гениальное..

Спустя 3 часа, 27 минут, 45 секунд (17.09.2009 - 01:33) FatCat написал(а):

Цитата (kirik @ 16.09.2009 - 23:06)

А когда например данные добавляются, но не изменяются (допустим какой-нибудь лог), это не спасет?

Посмотри физическую структуру данных: селект без сортировок. Если данные вводились и не удалялись, скорее физическая структура будет идентична сортировке по дате.
Если для вывода нужна сортировка по другому параметру - для ускорения работы имеет смысл физически пересортировать данные.
Я привел пример для форумной таблицы постов, где мы меняем сортировку по дате на сортировку по айдишнику родительского объекта: топика.

Быстрый ответ:

Здесь расположена полная версия этой страницы.