Здравствуйте, Konstantin, Вы писали:
K> А если делать выборки уже ближе к концу таблицы, то просто караул как долго. Вот типа такой запрос: K>
SELECT * FROM `table` LIMIT 799000 , 1000
K> (...Query took 149.6909 sec)
Чудес не бывает — в данном случае сервер выбирает 799000 + 1000 строк и берет в выборке последнюю 1000. Я бы на месте сервера за такое обиделся.
K> Подскажите, как ускорить это дело ? Вроде же должно быть примерно с такой же скоростью ? K> может подкрутить настройки или еще что...
Данная "проблема" называется пэйджингом страниц. В простейших случаях может решаться как-то так.
> ну да: > > * проблема MySQL решается убиранием MySQL. > * проблема в использовании БД решается убиранием БД.
Ирония неуместна. Сколько уже копий сломано, а всё изза
того, что не очень опытные программисты не могут понять
простую вещь: пейджинг никому не нужен. Или не могут
объяснить это своему тупому мэнеджеру проекта.
Здравствуйте, Konstantin, Вы писали:
K>А если делать выборки уже ближе к концу таблицы, то просто караул как долго. Вот типа такой запрос:
У таблицы нет такого понятия, как конец.
K>
SELECT * FROM `table` LIMIT 799000 , 1000
K>(...Query took 149.6909 sec)
K>Подскажите, как ускорить это дело ? Вроде же должно быть примерно с такой же скоростью ? K>может подкрутить настройки или еще что...
Видимо дело в том что записи таблицы очень большие.
Во-первых, обратите внимание на row format вашей таблицы. Если у вас row format = fixed, то возможно стоит попробовать изменить на dynamic.
Во-вторых, возможно стоит попробовать вертикальное партицирование таблицы, т.к. 6Гб на 800000 записей в общем случае — это много.
Konstantin wrote:
> Ну а как без пейджинга решить вопрос постраничного листания ?
А никак не надо решать вопрос постраничного листания.
Постраничное листание -- это бесполезная трата машинного
времени и сил листающего человека.
Если ему что=то нужно, ему нужно дать возможность сформировтаь
набор критериев для выборки и соответственно делать выборку.
Anton Batenev wrote:
> Расскажи это, например, команде гугля, которая сделала постраничный > вывод результатов поиска. Ну или команде RSDN, которая сделала пэйджинг > в списках сообщений — эти ответят на чистом русском
RSDN — отвратительный сайт. С навороченным и дурацким интерфейсом.
Если бы не NNTP, меня бы тут вообще не было бы.
Про google уже написал.
Здравствуйте, MasterZiv, Вы писали:
MZ>wildwind wrote:
>> Пробовал?
MZ>Да как бы пробовал. Успешно в общем.
>> Они, понимаешь ли, ленятся "сформировтаь набор критериев", их пейджинг >> вполне устраивает. Темные, что тут говорить.
MZ>Ну, тут очень хорош простой метод: если другого метода получить данные MZ>не будет, им ПРИДЁТСЯ формулировать критерии. Или придумывать свои новые, MZ>что ещё лучше.
Методы твои понятны. Напомнило:
Пользователи не приняли систему. Всех пришлось уничтожить.
Большая таблица (6Гб) в которой примерно 800 000 записей. Все поля имеют индекс + ключевое поле id (autoincrement).
Таблица типа MyISAM. Движок: MySQL 5.1.
Если сделать запрос такого плана:
SELECT * FROM `table` LIMIT 0 , 1000
То запрос выполняется практически моментально.
(Query took 0.096 sec)
А если делать выборки уже ближе к концу таблицы, то просто караул как долго. Вот типа такой запрос:
SELECT * FROM `table` LIMIT 799000 , 1000
(...Query took 149.6909 sec)
Подскажите, как ускорить это дело ? Вроде же должно быть примерно с такой же скоростью ?
может подкрутить настройки или еще что...
Здравствуйте, Lloyd, Вы писали:
L>Здравствуйте, Konstantin, Вы писали:
K>>А если делать выборки уже ближе к концу таблицы, то просто караул как долго. Вот типа такой запрос:
L>У таблицы нет такого понятия, как конец.
Я понимаю... Просто так условно сказал.
K>>
SELECT * FROM `table` LIMIT 799000 , 1000
K>>(...Query took 149.6909 sec)
K>>Подскажите, как ускорить это дело ? Вроде же должно быть примерно с такой же скоростью ? K>>может подкрутить настройки или еще что...
L>Попробуйте вставить ORDER BY, авось полегчает.
SELECT * FROM `table` ORDER BY id ASC LIMIT 700000 , 10
Здравствуйте, MozgC, Вы писали:
MC>Видимо дело в том что записи таблицы очень большие.
Ну не очень большие вроде как. Правда есть два поля longtext...
MC>Во-первых, обратите внимание на row format вашей таблицы. Если у вас row format = fixed, то возможно стоит попробовать изменить на dynamic.
Да вроде все то же самое:
StatementsValue
Format dynamic
Collation utf8_general_ci
Rows 775,929
Row length ø 6,973
Row size ø 7,263 B
MC>Во-вторых, возможно стоит попробовать вертикальное партицирование таблицы, т.к. 6Гб на 800000 записей в общем случае — это много.
Здравствуйте, Konstantin, Вы писали:
MC>>Во-вторых, возможно стоит попробовать вертикальное партицирование таблицы, т.к. 6Гб на 800000 записей в общем случае — это много. K>А это как ?
Перенести часть столбцов в отдельную таблицу. Если поля longtext используются редко, то их как раз и вынести в отдельную таблицу.
K>(...Query took 149.6909 sec)
K>Подскажите, как ускорить это дело ? Вроде же должно быть примерно с такой же скоростью ? K>может подкрутить настройки или еще что...
А это действительно реальный запрос, который выполняется приложением? Разработчики так "интересно" организовали пейджинг?
On 04/03/2010 04:13 PM, wildwind wrote: > K> > > SELECT * FROM `table` LIMIT 799000 , 1000 > > > K>(...Query took 149.6909 sec) > > K>Подскажите, как ускорить это дело ? Вроде же должно быть примерно с > такой же скоростью ? > K>может подкрутить настройки или еще что... > > А это действительно реальный запрос, который выполняется приложением? > Разработчики так "интересно" организовали пейджинг?
Я в мускулом давно не пользовался, но в Postgre все время пользуюсь
LIMIT 20 OFFSET 400 — или что-то в этом роде. У меня правда
веб-приложение, и таблицы в пределах 100 тысяч записей (чаще в пределах
1000, некоторые по 20-30 тысяч записей). Проблем с производительностью
не наблюдал.
Здравствуйте, Other Sam, Вы писали:
OS>Я в мускулом давно не пользовался, но в Postgre все время пользуюсь OS>LIMIT 20 OFFSET 400 — или что-то в этом роде. У меня правда OS>веб-приложение, и таблицы в пределах 100 тысяч записей (чаще в пределах OS>1000, некоторые по 20-30 тысяч записей). Проблем с производительностью OS>не наблюдал.
Таблицы в пределах 100000 записей (если там не по несколько килобайт строки) — это относительно маленькие таблицы, поэтому там можно даже в лоб написать SELECT * FROM table LIMIT XXXXX, YYYY (как я понял вы так и писали), и оно может отработать в пределах сотен милисекунд. Хотя такой запрос не нужно делать, т.к. будут прочитано XXXXXX + YYYY строк таблицы. В предыдущем посте я показал как надо написать запрос. Ну и, повторюсь, возможно стоит рассмотреть возможность вертикального партицирования — когда таблица занимает уже гигабайты — это может быть актуально.
Konstantin wrote: > SELECT * FROM `table` LIMIT 799000 , 1000 > (...Query took 149.6909 sec) > > Подскажите, как ускорить это дело ? Вроде же должно быть примерно с > такой же скоростью ?
Да никак не ускорить. Мало того, что запрос бессмысленный (он выдаёт
произвольные, случайные записи), он ещё и ВСЕ ТВОИ 800 000 записей
из таблицы обрабатывает. В "начале таблицы" быстро, потому что начинает
читать подряд все записи, и успокаивается на первых 1000. В "конце таблицы"
медленно, потому что долго-долго читает впустую почти все 800 000 записей,
а потом выдаёт одну тыщу из них.
Виной тому, безусловно, не очень продвинутый оптимизатор MySQL : я бы на
месте их выдавал бы всегда на такие запросы первые 1000 записей.
Пользователю всё равно, а серваку приятно. Но запрос тоже дурацкий,
а на все дурацкие запросы в оптимизаторе закладок не наделаешь.
MozgC wrote:
> Видимо дело в том что записи таблицы очень большие. > Во-первых, обратите внимание на row format вашей таблицы. Если у вас row > format = fixed, то возможно стоит попробовать изменить на dynamic. > Во-вторых, возможно стоит попробовать вертикальное партицирование > таблицы, т.к. 6Гб на 800000 записей в общем случае — это много.
Этот запрос ничего не спасёт, он всегда будет медленным.
Надо писать умные запросы.
"Проблема педжинга" решается только убиранием этого самого пейджинга
из приложения. Нет пейджинга -- нет проблем. Есть пейджинг -- есть проблемы.
Более она никак не решается.
> OS>Я в мускулом давно не пользовался, но в Postgre все время пользуюсь > OS>LIMIT 20 OFFSET 400 — или что-то в этом роде. У меня правда > OS>веб-приложение, и таблицы в пределах 100 тысяч записей (чаще в пределах > OS>1000, некоторые по 20-30 тысяч записей). Проблем с производительностью > OS>не наблюдал. > > Таблицы в пределах 100000 записей (если там не по несколько килобайт > строки) — это относительно маленькие таблицы, поэтому там можно даже в > лоб написать SELECT * FROM table LIMIT XXXXX, YYYY (как я понял вы так и > писали), и оно может отработать в пределах сотен милисекунд. Хотя такой > запрос не нужно делать, т.к. будут прочитано XXXXXX + YYYY строк > таблицы. В предыдущем посте я показал как надо написать запрос. Ну и, > повторюсь, возможно стоит рассмотреть возможность вертикального > партицирования — когда таблица занимает уже гигабайты — это может быть > актуально. > ------------------------------------------------------------------------
Мне сейчас недосуг проверять, но почему-то я уверен, что если условия
WHERE и ORDER BY попадают под исполнение по индексу, то чтений из
таблицы должно быть только то количество какое указано в LIMIT, а OFFSET
будет пропущен.
Здравствуйте, MasterZiv, Вы писали:
>> Попробуйте вставить ORDER BY, авось полегчает.
MZ>С какого ? Не полегчает. Ещё и хуже будет: надо будет и индекс, и данные чиатать.
Если сначала прочитать индекс, то будет ясно, с каких конкретно страниц читать данные, тем самым может уменьшиться объем данных, кот. необходимо поднять с диска.
Здравствуйте, Konstantin, Вы писали:
K>А если делать выборки уже ближе к концу таблицы, то просто караул как долго. Вот типа такой запрос: K>
SELECT * FROM `table` LIMIT 799000 , 1000
K>(...Query took 149.6909 sec) K>Подскажите, как ускорить это дело ? Вроде же должно быть примерно с такой же скоростью ?
В конце таблицы с такой же скоростью как и в начале такой пейджинг работать не должен и не будет, ведь он в общем случае должен обработать все строки — от первой (в порядке сортировки) строки таблицы и до последней строки отображаемой страницы пейджинга.
Тем не менее во многих случаях такой запрос пейджинга "в лоб" можно ускорить специально для доступа к страницам "в конце" таблицы немного переписав его.
Запрос разбиваем на две части. Первая — простой пейджинг как и раньше, но мы выбираем не все поля (select * ...), а только первичный ключ (select ID ...). Во второй части — уже отобранный после пейджинга список ID (их будет столько, сколько записей мы отображаем на одной странице, т.е. где-то десятки-сотни) мы джойним с основной таблицей и уже из нее выбираем полные строки со всеми нужными полями. Join здесь скорее всего должен быть типа nested loops с внутренним циклом по индексу первичного ключа основной таблицы.
Схематично запрос выглядит так:
select * from table // здесь получаем полные строки
inner join
(
select ID from table // получаем только первичный ключ
where .... // все фильтры также указываем во внутреннем запросе
order by ... // пейджинг без сортировки - некорректен
limit ... // здесь же пейджинг
) IDs
on table.ID = IDs.ID // nested loops
Здравствуйте, Lloyd, Вы писали:
L>Здравствуйте, MasterZiv, Вы писали:
>>> Попробуйте вставить ORDER BY, авось полегчает.
MZ>>С какого ? Не полегчает. Ещё и хуже будет: надо будет и индекс, и данные чиатать.
L>Если сначала прочитать индекс, то будет ясно, с каких конкретно страниц читать данные, тем самым может уменьшиться объем данных, кот. необходимо поднять с диска.
Здравствуйте, MozgC, Вы писали:
MC>Здравствуйте, Konstantin, Вы писали:
MC>>>Во-вторых, возможно стоит попробовать вертикальное партицирование таблицы, т.к. 6Гб на 800000 записей в общем случае — это много. K>>А это как ?
MC>Перенести часть столбцов в отдельную таблицу. Если поля longtext используются редко, то их как раз и вынести в отдельную таблицу.
К сожалению могу только одно поле вынести. По другому будет полнотекстовый поиск. Но это хоть что-то...
Здравствуйте, MasterZiv, Вы писали:
MZ>Anton Batenev wrote:
>> Данная "проблема" называется пэйджингом страниц. В простейших случаях >> может решаться как-то так <http://habrahabr.ru/blogs/mysql/44608/>.
MZ>"Проблема педжинга" решается только убиранием этого самого пейджинга MZ>из приложения. Нет пейджинга -- нет проблем. Есть пейджинг -- есть проблемы. MZ>Более она никак не решается.
Ну а как без пейджинга решить вопрос постраничного листания ?
Здравствуйте, MasterZiv, Вы писали:
MZ>Мало того, что запрос бессмысленный (он выдаёт MZ>произвольные, случайные записи),
Теоретически это так, практически далеко не всегда. Особенно при использовании MyISAM.
MZ>он ещё и ВСЕ ТВОИ 800 000 записей MZ>из таблицы обрабатывает. В "начале таблицы" быстро, потому что начинает MZ>читать подряд все записи, и успокаивается на первых 1000. В "конце таблицы" MZ>медленно, потому что долго-долго читает впустую почти все 800 000 записей, MZ>а потом выдаёт одну тыщу из них.
В случае MyISAM и fixed row format "он" знает, какие записи нужно прочитать и откуда. Возможно, что на это и был рассчитан такой пейджинг изначально, а потом добавились поля longtext, из-за чего сменился формат записи и доступ стал неэффективным. А может просто база не была рассчитана на такой объем данных.
Здравствуйте, MasterZiv, Вы писали:
MZ>Konstantin wrote: >> SELECT * FROM `table` LIMIT 799000 , 1000 >> (...Query took 149.6909 sec) >> >> Подскажите, как ускорить это дело ? Вроде же должно быть примерно с >> такой же скоростью ?
MZ>Да никак не ускорить. Мало того, что запрос бессмысленный (он выдаёт MZ>произвольные, случайные записи), он ещё и ВСЕ ТВОИ 800 000 записей MZ>из таблицы обрабатывает. В "начале таблицы" быстро, потому что начинает MZ>читать подряд все записи, и успокаивается на первых 1000. В "конце таблицы" MZ>медленно, потому что долго-долго читает впустую почти все 800 000 записей, MZ>а потом выдаёт одну тыщу из них.
MZ>Виной тому, безусловно, не очень продвинутый оптимизатор MySQL : я бы на MZ>месте их выдавал бы всегда на такие запросы первые 1000 записей. MZ>Пользователю всё равно, а серваку приятно. Но запрос тоже дурацкий, MZ>а на все дурацкие запросы в оптимизаторе закладок не наделаешь.
Ну оказалось, что ускорить можно с помощью WHERE id > 700000. И это весьма радует. Так что...
[skip] MZ>>а потом выдаёт одну тыщу из них.
W>В случае MyISAM и fixed row format "он" знает, какие записи нужно прочитать и откуда. Возможно, что на это и был рассчитан такой пейджинг изначально, а потом добавились поля longtext, из-за чего сменился формат записи и доступ стал неэффективным. А может просто база не была рассчитана на такой объем данных.
Ну просто я сейчас пытаюсь максимально ускорить работу базы, чтобы когда пойдет реальная работа, ничего не переделывать. И ищу вот такие узкие места.
Здравствуйте, Konstantin, Вы писали:
K>Ну просто я сейчас пытаюсь максимально ускорить работу базы, чтобы когда пойдет реальная работа, ничего не переделывать. И ищу вот такие узкие места.
А, так вы и есть разработчик? Тогда настоятельно советую изучить статью, указанную Anton Batenev.
Konstantin wrote:
> SELECT * FROM `table` WHERE ID > 799000 ORDER BY ID LIMIT 1000
> MC>Так будет правильнее и должно в несколько раз ускорить выполнение > запроса. Подразумевается что ID — первичный ключ. > > (1,000 total, Query took 0.0805 sec) > > Гениально! Все гениальное просто как оказалось
Просто, да не просто.
Тебе нужно априори знать ID, от которого можно плясать, и гаратнированно
будет та страница, которая тебе нужна. Ты это знаешь ?
Я так понял, что нет.
> L>Если сначала прочитать индекс, то будет ясно, с каких конкретно > страниц читать данные, тем самым может уменьшиться объем данных, кот. > необходимо поднять с диска.
Индекс надо будет прочитать ВЕСЬ. Данные пото -- да, не все.
> А как заставить MySQL это сделать ?
Ну особенно заставлять не нужно, ORDER BY это сделает.
Other Sam wrote:
> Мне сейчас недосуг проверять, но почему-то я уверен, что если условия > WHERE и ORDER BY попадают под исполнение по индексу, то чтений из > таблицы должно быть только то количество какое указано в LIMIT, а OFFSET > будет пропущен.
Конечно.
Только у нас до сих пор одна проблема: нет WHERE.
Здравствуйте, MasterZiv, Вы писали:
MZ>того, что не очень опытные программисты не могут понять MZ>простую вещь: пейджинг никому не нужен. Или не могут MZ>объяснить это своему тупому мэнеджеру проекта.
Да менеджеру-то еще можно, а вот как тупым пользователям объяснить? Пробовал?
Они, понимаешь ли, ленятся "сформировтаь набор критериев", их пейджинг вполне устраивает. Темные, что тут говорить.
Здравствуйте, MasterZiv, Вы писали:
MZ>Konstantin wrote:
>> Ну оказалось, что ускорить можно с помощью WHERE id > 700000. И это >> весьма радует. Так что...
MZ>Ты знаешь априори этот id > 700000 ? вместо 700000 для другой страницы MZ>что ставить будешь ?
Как ни странно — максимальный прочитанный ID (при листании на 1 страницу вправо) или минимальный прочитанный ID (при листании на 1 страницу влево).
Здравствуйте, MasterZiv, Вы писали:
MZ> не очень опытные программисты не могут понять MZ> простую вещь: пейджинг никому не нужен.
Расскажи это, например, команде гугля, которая сделала постраничный вывод результатов поиска. Ну или команде RSDN, которая сделала пэйджинг в списках сообщений — эти ответят на чистом русском
Здравствуйте, MasterZiv, Вы писали:
>> (1,000 total, Query took 0.0805 sec) >> >> Гениально! Все гениальное просто как оказалось
MZ>Просто, да не просто.
MZ>Тебе нужно априори знать ID, от которого можно плясать, и гаратнированно MZ>будет та страница, которая тебе нужна. Ты это знаешь ? MZ>Я так понял, что нет.
Удаления будут нечастыми и можно построить значения стартовых id для каждой страницы. Причем даже с помощью хранимой процедуры прямо на сервере.
Здравствуйте, MasterZiv, Вы писали:
MZ>Konstantin wrote:
>> Ну а как без пейджинга решить вопрос постраничного листания ?
MZ>А никак не надо решать вопрос постраничного листания. MZ>Постраничное листание -- это бесполезная трата машинного MZ>времени и сил листающего человека. MZ>Если ему что=то нужно, ему нужно дать возможность сформировтаь MZ>набор критериев для выборки и соответственно делать выборку.
MZ>А листания никому не нужны.
Здравствуйте, wildwind, Вы писали:
W>Здравствуйте, Konstantin, Вы писали:
K>>Ну просто я сейчас пытаюсь максимально ускорить работу базы, чтобы когда пойдет реальная работа, ничего не переделывать. И ищу вот такие узкие места.
W>А, так вы и есть разработчик? Тогда настоятельно советую изучить статью, указанную Anton Batenev.
Здравствуйте, MozgC, Вы писали:
MC>Здравствуйте, MasterZiv, Вы писали:
MZ>>Konstantin wrote:
>>> Ну оказалось, что ускорить можно с помощью WHERE id > 700000. И это >>> весьма радует. Так что...
MZ>>Ты знаешь априори этот id > 700000 ? вместо 700000 для другой страницы MZ>>что ставить будешь ?
MC>Как ни странно — максимальный прочитанный ID (при листании на 1 страницу вправо) или минимальный прочитанный ID (при листании на 1 страницу влево).
Плюс, можно построить список этих стартовых id. При удалении перестраивать. При добавлении дописывать.
Konstantin wrote:
> Удаления будут нечастыми и можно построить значения стартовых id для > каждой страницы. Причем даже с помощью хранимой процедуры прямо на сервере.
Тогда твоё счастье. Тогда ты можешь пойти и дальне -- сделать достаточно
статичный список страниц с диапазоном или перечнем ID-ов на каждой странице.
Да как бы пробовал. Успешно в общем.
> Они, понимаешь ли, ленятся "сформировтаь набор критериев", их пейджинг > вполне устраивает. Темные, что тут говорить.
Ну, тут очень хорош простой метод: если другого метода получить данные
не будет, им ПРИДЁТСЯ формулировать критерии. Или придумывать свои новые,
что ещё лучше.
Здравствуйте, MasterZiv, Вы писали:
MZ>Konstantin wrote:
>> Удаления будут нечастыми и можно построить значения стартовых id для >> каждой страницы. Причем даже с помощью хранимой процедуры прямо на сервере.
MZ>Тогда твоё счастье. Тогда ты можешь пойти и дальне -- сделать достаточно MZ>статичный список страниц с диапазоном или перечнем ID-ов на каждой странице.
Хммм... А что если сделать отдельную таблицу, которая будет только ID содержать ? Там row format будет fixed и по идее позиционирование будет моментальным.
Konstantin wrote:
> Хммм... А что если сделать отдельную таблицу, которая будет только ID > содержать ? Там row format будет fixed и по идее позиционирование будет > моментальным.
Я не знаю, что такое fixed row format и кто его придумал.
Есть индексы, с ними надо работать. А fixed row format -ы это хачки
какие-то MYISAM-овские.
