Большая таблица (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)
Подскажите, как ускорить это дело ? Вроде же должно быть примерно с такой же скоростью ?
может подкрутить настройки или еще что...
Здравствуйте, 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 записей в общем случае — это много.
Здравствуйте, 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 используются редко, то их как раз и вынести в отдельную таблицу.
Здравствуйте, Konstantin, Вы писали:
K> А если делать выборки уже ближе к концу таблицы, то просто караул как долго. Вот типа такой запрос: K>
SELECT * FROM `table` LIMIT 799000 , 1000
K> (...Query took 149.6909 sec)
Чудес не бывает — в данном случае сервер выбирает 799000 + 1000 строк и берет в выборке последнюю 1000. Я бы на месте сервера за такое обиделся.
K> Подскажите, как ускорить это дело ? Вроде же должно быть примерно с такой же скоростью ? K> может подкрутить настройки или еще что...
Данная "проблема" называется пэйджингом страниц. В простейших случаях может решаться как-то так.
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>Если сначала прочитать индекс, то будет ясно, с каких конкретно страниц читать данные, тем самым может уменьшиться объем данных, кот. необходимо поднять с диска.
