Благодаря вот этому посту обнаружил такую вещь. limit не работает с индексами, и offset, похоже, просто перебирает значения. Если в таблице много записей, то получаются тормоза. location - таблица на ~миллион записей. После рестарта мускуля (тобишь - сброса его кешей), вот такие запросы отрабатываются ~20 секунд: Код (Text): select * from location limit 850000, 50 select * from location order by id limit 850000, 50 Собственно, wtf? Размеры строк в таблице фиксированного размера. Физические позиции строк растут вместе со значением кластерного индекса. Следовательно, select без order by теоретически должен давать константное позиционирование, а с order by [индектированное поле] - log2 (n) поцизионирование. А тут, видимо, оно линейное. Как дальше жить?
Вы в этом уверены? Записи в файле должны лежать чаще всего в порядке их инсертов (если не было удалений). А натуральный порядок выдачи SELECT * по возрастанию первичного ключа, если я не ошибаюсь. То есть, даже если из-за автоинкремента порядок записей в файле и возрастание ключа вроде бы должны совпадать, тем не менее, вы даете индексу совершенно чуждую ему задачу: "найди мне такую запись, перед которой в порядке возрастания первичного ключа имеется 850000 записей" Вы заставляете механизм индексирования заниматься полным обходом внутренних структур индекса для подсчета количества записей, тогда как его родная задача - быстро найти запись по значению.
Dmitry_Milk http://ru.wikipedia.org/wiki/Индекс_(базы_данных): http://dev.mysql.com/doc/refman/5.0/en/innodb-index-types.html:
Да да. Вот что вы понимаете под этими словами? тут ведь не сказано, что в таком порядке располагаются записи в файле. Тут сказано, что SELECT * FROM TABLE обязательно выдаст записи в таком порядке. Постраничный вывод требуется? Или какая-то другая задача? надо отойти подальше и посмотреть по-другому.
Dmitry_Milk http://www.ovaistariq.net/521/understanding-innodb-clustered-indexes/ А что еще может подразумеваться под "physical order", если не физическое расположение в файле?
Dmitry_Milk Он самый. select ... from ... limit page_number * items_per_page, items_per_page # page_number = over9000
Dmitry_Milk Ну и еще раз посмотрим сюда: http://dev.mysql.com/doc/refman/5.6/en/innodb-table-and-index.html Опять же, "physically in insertion order". Как это понимать, кроме как последовательность в файле?
Хм, с этим поспорить сложнее. Тем не менее, поддержание физического упорядочивания записей в файле - нонсенс. Представьте себя на месте разработчиков СУБД. Представьте, что при наличии первичного ключа требуется вставить в таблицу такую запись, что ключ требует ее положения в самом начале. Что, переписывать весь файл? В какой файловой системе вам известна операция "вставить последовательность байтов в файл с раздвиганием"? По поводу страниц, варианты: а). уговорить заказчика на то, что ради производительности можно пожертвовать требованием строгого количества записей на странице, что количество записей на странице может изменяться после удаления. Тогда завести дополнительное поле page и сделать по нему дополнительный неуникальный индекс. Естественно, заполнять поле сразу при вставке записи, в любом случае select max(page) from item;select count(*) from item where page=N отработают быстро, если есть индекс. Кстати, хороший плюс от этого варианта для SEO - содержимое большинства страниц будет оставаться неизменным. Доастаточно весомый аргумент, если заказчика волнует важность SEO. Еще один недостаток, помимо нестрого количества - размер страницы придется выбрать заранее. б). Отказаться от автоинкремента ID (но оставив первичным ключом) и поддерживать сплошную нумерацию, чтоб ID строго совпадал с порядковым номером. То есть, если вставка в конец - то insert into item(ID,поля...) select max(id)+1,значения-константы... from item Если удаление - то delete from item where id=N update item set id=id-1 where id>N Недостаток - долго работает удаление Ну и с вставкой в середину еще надо придумать что-то, если таковая требуется. Просто так не получится, из-за уникальности id.
Dmitry_Milk 1. В энторнетах читал, что такая проблема есть - инсерт в центр ведет к перезаписи фрагманта таблицы. И в этом, имхо, все правильно (см п.3) 2. У мускуля есть опция raw disk, работа с диском напрямую, минуя ФС (правда хз, поможет ли это в данном случае). 3. Заглядывать в значение PK - идеологически неверно Он должен быть черным ящиком с единственно операцией проверки на равенство, т.е. = / != / in / not in. В энторнетах, опять же, советуют, что если вам захотелось заглядывать в значение PK, то лучше ввести второе вспомогательное поле с UNIQUE индексом, и заглядывать уже в него. Поэтому PK должен быть int autoincrement, и должен рассматриваться как черный ящик - можно только спросить "ты - не ты". PK нужен для идентификации объекта, и обеспечении целостности отношений. Завязывать логику на его внутреннее устройство неправильно, и очень черевато. Не выйдет, т.к. я на уговоры не поддаюсь Тогда уж см. п.3 - дополнительное поле. А то этот способ соснет при наличии FK на эти PK. [UPD]: конечно, можно сделать каскадное обновление на update, но это никому не нужный онанизм.
Вот ведь человек, сам себе гемор создает. Нет чтоб пойти на компромисс со своими желаниями (вполне ли обоснованными?), облегчив себе участь Насчет дополнительного поля -действительно завести тогда дополнительное поле, order_num, повесить на него уникальный индекс и работать по страницам с ним. FK из других мест на order_num создавать не надо. Если доступны триггера - то вроде все должно гладко получаться.
Dmitry_Milk Так...) Пообщался с разработчиком MySQL Задача в лоб нерешаема. Пагинация мульярдов данных в любом случае даст o(n), вместо o(1). Нужно какое-то хитрое решение. Написали в google groups, ждем кто что насоветует
Вот тебе вариант: Если тебе нужен быстрый пейджинг только для сортировки по ПК, то ты можешь опираться на ПК при выборке. Т.е. страницу задает смещение и ПК последнего элемента предыдущей страницы. Тогда получить следующую страницу можно эффективным запросом select * from list where id > $last_id limit 20. Что делать с рандомным доступом к страницам? Можно либо на них забить как на редкое явление, либо сделать кеш с парами page_num -> last_id. Разумееется, при обновлении этого списка кеш придется сбрасывать, но операций чтения, как правило, намного больше, чем операций записи. Можно сделать "продвинутую технику" - при добавлении нового элемента в список не сбрасывать кеш, а сохранять поправки (например, теперь надо выбирать не 20, а 21 элемент и первый из них игнорировать). Еще один вариант - строить индексы для пагинации самостоятельно в памяти - обрабатывать добавления-удаления-изменения списка и хранить в памяти такую структуру, по которой можно извлечь набор ПК элементов для нужной страницы и сортировки. Готовые решения уже наверняка есть, если и не для ПХП, то для явы - однозначно.
http://www.jvanetsky.ru/data/text/t8/konservatoria/ Стало крылатой фразой со смыслом - "возможно, есть какая-то более общая/глубокая вещь, являющаяся источником проблем по разным направлениям, и надо попробовать поискать и попытаться изменить общую причину". Я имел в виду - зачем именно возникла необходимость в "пагинации мульярдов данных"? ПРичем в такой пагинации, когда конкретные записи не будут закреплены за конкретными страницами, а будут плавать со страницы на страницу при удалениях вставках. Такая пагинация на огромном количестве данных не годится ни для быстрого поиска, ни для последовательного просмотра какого-либо участка этого огромного массива данных. Скорее всего именно поэтому для такой задачи (на огромном количестве данных) до сих пор и не придумано нормальной реализации, потому что задача надуманная. Скажем, в MSSQL тоже только TOP() с количеством, но не со смещением. Может быть нужна не пагинация, а что-то другое? Или не такая пагинация?
Может это поможет http://habrahabr.ru/company/badoo/blog/135966/ Но секунда на 10млн меня бы не устроила, я в свое время решал отказом от mysql.
