LINUX.ORG.RU

MySQL: а можно ли одним запросом?...


0

0

Есть таблица, данные из которой надо выводить с разбивкой по страницам. Данных МНОГО. LIMIT N,M тормозит ужасно. Поэтому в таблице давно есть поле `page`, где и хранится нужное число.

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

Подумалось тут - а, может, я торможу.

И можно как-то извратиться одним запросом

SET `page` = <some> WHERE ... ORDER BY ...

?

Пусть он хоть 2-3 секунды для 5000 записей думает, такие задержки перетерпеть можно, сейчас до 10 секунд уходит, что уже выглядит как тормоза при пересчётах...

★★★★★

Ответ на: Re: MySQL: а можно ли одним запросом?... от wfrr

Re: MySQL: а можно ли одним запросом?...

такс не rowid а rownum

и таки яндкусувание подсказало

http://kurapov.name/article/2092 =>

SET @rank=0;
SELECT @rank:=@rank+1 AS rank, id FROM menu;

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

wfrr ★★☆ ()

Re: MySQL: а можно ли одним запросом?...

если данных в таблице реально МНОГО, как вариант оптимизации могу предложить разбивку одной большой таблицы на несколько более мелких. например table_page0, table_page1, etc.

isden ★★★★★ ()
Ответ на: Re: MySQL: а можно ли одним запросом?... от isden

Re: MySQL: а можно ли одним запросом?...

>если данных в таблице реально МНОГО, как вариант оптимизации могу предложить разбивку одной большой таблицы на несколько более мелких.

Так и было в течении нескольких лет :) В итоге оказалось чрезмерно много накладных расходов на циклы работы с подтаблицами. Вариант с полем page - НАМНОГО эффективнее.

KRoN73 ★★★★★ ()
Ответ на: Re: MySQL: а можно ли одним запросом?... от wfrr

Re: MySQL: а можно ли одним запросом?...

>Что то я совсем SQL забыл, где это приведенном мной запросе копируется таблица?

Мне не SELECT нужен, а UPDATE :)

По твоей ссылке автор решает эту же задачу, но с копированием в промежуточную таблицу.

KRoN73 ★★★★★ ()
Ответ на: Re: MySQL: а можно ли одним запросом?... от isden

Re: MySQL: а можно ли одним запросом?...

>т.е. ВСЕ данные из этой кучи реально используются?

Был, кстати, да, ещё вариант с архивом не использующихся данных и
копированием туда-сюда данных по запросу или по истечению времени
хранения без обращений :)

Тоже неэффективно оказалось. Реально редкие данные лежат без подъёма
больше пары недель.

>а сколько записей, если не секрет? )

Сейчас - 

mysql> select count(*) from posts;
+----------+
| count(*) |
+----------+
|  1865237 | 
+----------+
1 row in set (0.00 sec)

В день добавляется в среднем:

mysql> select count(*)/30 from posts where posted > unix_timestamp()-86400*30;
+-------------+
| count(*)/30 |
+-------------+
|   1046.3000 | 
+-------------+
1 row in set (0.35 sec)

KRoN73 ★★★★★ ()
Ответ на: Re: MySQL: а можно ли одним запросом?... от KRoN73

Re: MySQL: а можно ли одним запросом?...

Не не я решаю другую задачу:

>Есть таблица, данные из которой надо выводить с разбивкой по страницам


У меня для разбивки постранично вообще не используется никаких дополнительных полей в таблице, делается вложенный запрос где внутренний селект выбирает все и номер записи, а внешний селект выбирает по номеру записи заданный диапазон , напр, от 100 до 200 записи. Конечно, возможно мускуль таки вещи не оптимизирует, потому там так низя.

wfrr ★★☆ ()
Ответ на: Re: MySQL: а можно ли одним запросом?... от wfrr

Re: MySQL: а можно ли одним запросом?...

>Не не я решаю другую задачу

Твоё решение не годится, когда в MySQL нужно взять последние 20 записей из 5000-го массива, взятого из двухмиллионной базы :) Тормоза достигают секунд.

KRoN73 ★★★★★ ()
Ответ на: Re: MySQL: а можно ли одним запросом?... от KRoN73

Re: MySQL: а можно ли одним запросом?...

Ммм, фигня этот ваш мускуль. Оракл это дело оптимизирует, а запрос там страшен. Надо на пострегресе завести тестовую базу и проверить там такое.

wfrr ★★☆ ()

Re: MySQL: а можно ли одним запросом?...

Всё, нашёл в первом приближении :D

set @rownum=0; UPDATE posts t, (SELECT @rownum:=@rownum+1 rownum, posts.* FROM posts WHERE posts.topic_id = 52776 ORDER BY posts.`order`, posts.id) tmp SET t.page = floor((tmp.rownum-1)/25)+1 WHERE (t.id = tmp.id);

Выполняется за секунду. Выигрышь колоссальный :D

KRoN73 ★★★★★ ()
Ответ на: Re: MySQL: а можно ли одним запросом?... от wfrr

Re: MySQL: а можно ли одним запросом?...

>И вообще пишут что лимит быстро работает

Понятие «быстро» - относительное :) Для кого-то выборка из миллионной базы за секунды - это быстро. Но когда на сайте несколько сот запросов в секунду идут, то секунда на запрос - это уже очень медленно :)

KRoN73 ★★★★★ ()
Ответ на: Re: MySQL: а можно ли одним запросом?... от KRoN73

Re: MySQL: а можно ли одним запросом?...

Заразо. Сейчас буду ломать голову, как совместить прямые изменения в БД с объектами, кешированными в memcached :)

KRoN73 ★★★★★ ()
Ответ на: Re: MySQL: а можно ли одним запросом?... от wfrr

Re: MySQL: а можно ли одним запросом?...

Есть :) Без индксов он бы минутами, если не часами сортировал бы на таких объёмах :)

...

Красота получилась. Там, где раньше пересчёт темы и связанные с ней операции (типа, переноса сообщений из темы в тему) было долгой и утомительной операцией на десяток-другой секунд, сейчас всё выполняется почти мгновенно :)

KRoN73 ★★★★★ ()

Re: MySQL: а можно ли одним запросом?...

> Данных МНОГО. LIMIT N,M тормозит ужасно.

А можно конкретные цифры? LIMIT N,M при правильно построенных индексах и должном к-ве оперативы вообще не должен тормозить.

www_linux_org_ru ★★★★★ ()
Ответ на: Re: MySQL: а можно ли одним запросом?... от www_linux_org_ru

Re: MySQL: а можно ли одним запросом?...

>А можно конкретные цифры?

Сейчас проверил на

select id from posts where topic_id = 52776 ORDER BY id LIMIT 4000, 25;

Вышло 0.22сек.

Для сравнения:

select id from posts where topic_id = 52776 and page=160 ORDER BY id;
...
25 rows in set (0.00 sec)

Думаю, вполне наглядно :)

KRoN73 ★★★★★ ()
Ответ на: Re: MySQL: а можно ли одним запросом?... от www_linux_org_ru

Re: MySQL: а можно ли одним запросом?...

>Значит индексов нет правильных.

Без правильных индексов такой запрос многими часами бы делался. Мне в 3-й раз повторить, что число записей - миллионы. Число записей в выборке - тысячи? :)

0.22 сек. без индекса - это когда сотня-другая записей :D

KRoN73 ★★★★★ ()
Ответ на: Re: MySQL: а можно ли одним запросом?... от KRoN73

Re: MySQL: а можно ли одним запросом?...

> Без правильных индексов такой запрос многими часами бы делался. Мне в 3-й раз повторить, что число записей - миллионы. Число записей в выборке - тысячи? :)

Мне интересно эксперимент поставить на 10М записях. Какого размера таблица (в ГБ) или каков средний размер записи?

www_linux_org_ru ★★★★★ ()
Ответ на: Re: MySQL: а можно ли одним запросом?... от www_linux_org_ru

Re: MySQL: а можно ли одним запросом?...

>Мне интересно эксперимент поставить на 10М записях. Какого размера таблица (в ГБ) или каков средний размер записи?

# ls -lh /home2/mysql/punbb/posts.*
-rw-rw---- 1 mysql mysql  14K Июл  9 03:01 /home2/mysql/punbb/posts.frm
-rw-rw---- 1 mysql mysql 3,9G Июл 18 03:14 /home2/mysql/punbb/posts.MYD
-rw-rw---- 1 mysql mysql 340M Июл 18 03:14 /home2/mysql/punbb/posts.MYI

KRoN73 ★★★★★ ()
Ответ на: Re: MySQL: а можно ли одним запросом?... от www_linux_org_ru

Re: MySQL: а можно ли одним запросом?...

>Я полагаю, что у меня твой первый запрос тоже сделается за 0.00 сек.

В домашних условиях сложно воспроизвести ситуацию реальной загрузки БД. У меня бывало за сутки mysql отрабатывал до 70+ млн. запросов.

Сейчас, после всех оптимизаций и переезда на статику - 12 миллионов в сутки. 500 тыс. в час.

Из их 12 тыс. в час - UPDATE, 10 тыс. INSERT и 4,5тыс. REPLACE :)

А, да, ещё 3.6тыс. DELETE :)

KRoN73 ★★★★★ ()
Ответ на: Re: MySQL: а можно ли одним запросом?... от KRoN73

Re: MySQL: а можно ли одним запросом?...

500 тыс. в час это 150 запросов в секунду.

Если я смогу сделать 50 запросов в секунду таких, как твой первый запрос, занявший 0.22 секунды, это будет сравнимо?

www_linux_org_ru ★★★★★ ()
Ответ на: Re: MySQL: а можно ли одним запросом?... от KRoN73

Re: MySQL: а можно ли одним запросом?...

> Какое? Не понял :)

Я бы хотел базу поточнее смоделировать. Но лень напрягаться... может можно сделать 50К топиков, в каждом из которых ровно по 5К мессаг?

www_linux_org_ru ★★★★★ ()
Ответ на: Re: MySQL: а можно ли одним запросом?... от www_linux_org_ru

Re: MySQL: а можно ли одним запросом?...

>500 тыс. в час это 150 запросов в секунду.

Не так всё просто. Я потому и пишу про число запросов в сутки :)

Ночью загрузка падает по сравнению с дневной в 10 раз.

Если на глаз график загрузки усреднить, то получится что средне пиковая загрузка раза в полтора-два выше средней. Т.е. что-то под 200-300 запросов в секунду.

...

А, вот же, у меня munin статистику ведёт. Врёт, собака, сам mysql число запросов другое выдаёт (12 млн. в сутки - это из его переменных), но позволяет тоже среднюю и пиковую нагрузки соотнести:
http://admin.airbase.ru/munin/airbase/airbase/mysql_queries.html

Выходит (если брать за неделю данные), что пиковая нагрузка выше средней в 3 раза. Значит пиковая загрузка сейчас на сервере бывает где-то под 400 запросов в секунду.

>Если я смогу сделать 50 запросов в секунду таких, как твой первый запрос, занявший 0.22 секунды


SELECT'ов для топиков достаточно с десяток в секунду выдавать, наверное. У меня же не вся нагрузка висела именно на форумных топиках. С другой стороны - сервер загружен не только топиками :)

Короче, сложно оценить.

>это будет сравнимо?


Ну, грубо оценить разницу в скорости, конечно, потянет.

Только запросы надо случайные делать, а то mysql закеширует.

KRoN73 ★★★★★ ()
Ответ на: Re: MySQL: а можно ли одним запросом?... от KRoN73

Re: MySQL: а можно ли одним запросом?...

> 200-300 запросов в секунду.

Тут еще от оперативки сильно зависит, т.к. время сика у винта около 10 мс, и выдать столько на одних сиках нельзя.

Мой вариан 50К топиков по 5К записей дает 250М записей => 16 байт в записи, что как-то нелепо. Т.е. придется распределение сделать неравномерным. Например, максимум 5К, минимум 0, в среднем ???

www_linux_org_ru ★★★★★ ()
Ответ на: Re: MySQL: а можно ли одним запросом?... от www_linux_org_ru

Re: MySQL: а можно ли одним запросом?...

Надеюсь, я завтра не поленюсь и запощу сюда скрипт, генерирующий инсерты. Это типа "наш ответ TPC", в дополнение к твоей коллекции тестов языков программирования :-) А может ты напишешь?

www_linux_org_ru ★★★★★ ()
Ответ на: Re: MySQL: а можно ли одним запросом?... от KRoN73

Re: MySQL: а можно ли одним запросом?...

И еще нужен скрипт для моделирования нагрузки, т.е. селектов, тоже не по-порядку.

И хотелось бы учесть, что топики заполняются не совсем в беспорядке, т.е. например порядка 10% (?) топиков активны...

www_linux_org_ru ★★★★★ ()
Ответ на: Re: MySQL: а можно ли одним запросом?... от www_linux_org_ru

Re: MySQL: а можно ли одним запросом?...

>Тут еще от оперативки сильно зависит, т.к. время сика у винта около
>10 мс, и выдать столько на одних сиках нельзя.

Там 2.5Гб и винты SCSI с сиком, наверное, под 6мс :)

>Мой вариан 50К топиков по 5К записей дает 250М записей

Ну, я уже свои текущие цифры говорил. 1,8млн. записей, 3,8Гб.

Число топиков - 56 тысяч.

Максимальные размеры - под 5000 сообщений. Но это редкость, чаще - под 1000.

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

Обычно народ пасётся в темах по 500-1000 сообщений.

Из обновившихся на неделю:

mysql> select num_replies from topics where last_post > unix_timestamp()-86400*7 order by num_replies desc limit 20;
+-------------+
| num_replies |
+-------------+
|        2504 | 
|        2133 | 
|        1384 | 
|        1359 | 
|        1235 | 
|        1197 | 
|        1184 | 
|        1113 | 
|        1067 | 
|        1040 | 
|         985 | 
|         976 | 
|         949 | 
|         934 | 
|         924 | 
|         920 | 
|         898 | 
|         861 | 
|         856 | 
|         852 | 
+-------------+
20 rows in set (0.00 sec)

Всего за неделю обновилось 510 тем.

Из них более 500 сообщений у 77.

...

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

Сейчас другая проблема актуальна. Когда база такого размера,
то любое изменение структуры или индексов - это полтора часа
перестройки индексов :) Плюс равное текущей базе место на диске,
так как делается копия. А у меня с местом совсем хреново нынче :)

Недавно, чтобы начистить 6Гб (с запасом, не в ноль же забивать)
для модификации базы, на чистку диска часов 6 времени угрохал :)

Да, ещё наводка. Раньше реально скорость начинала снижаться где-то за
500 сообщений в теме. У меня даже ругань начиналась автоматическая,
типа «тема слишком большая, открывайте новую».

KRoN73 ★★★★★ ()
Ответ на: Re: MySQL: а можно ли одним запросом?... от KRoN73

Re: MySQL: а можно ли одним запросом?...

> Тестирование разных БД? Нет, увольте, не мой профиль

ОК, завтра (т.е. сегодня уже) если не поленюсь, запощу скрипты заполнения и тестирования.

www_linux_org_ru ★★★★★ ()
Ответ на: Re: MySQL: а можно ли одним запросом?... от www_linux_org_ru

Re: MySQL: а можно ли одним запросом?...

Есть подозрение, что создание очень не маленького лишнего индекса на загруженной машине (если там не совсем дофига оперативки) к улучшению врятли приведёт.

Кстати индекс лучше UNIQUE, т.к. он действительно уникальный.

Как напишешь скрипты создания таблиц - запости, пожалуйста, тоже интересно посмотреть.

qnikst ★★★★★ ()

Re: MySQL: а можно ли одним запросом?...

KRoN73, если не секрет, что за форум такой?
Не ag.ru случайно?

blackst0ne ★★★★★ ()
Ответ на: Re: MySQL: а можно ли одним запросом?... от blackst0ne

Re: MySQL: а можно ли одним запросом?...

Не... У ag.ru загрузка, думается мне, на порядок выше :D

balancer.ru/forum это. Он же (в смысле - одна из его подветок: кроме общего списка есть тематически подразделы на разных адресах) - forums.airbase.ru

KRoN73 ★★★★★ ()

Re: MySQL: а можно ли одним запросом?...

Вот, на SQL.ru подкинули ещё более короткий вариант:

UPDATE posts AS t SET t.page =FLOOR(((select @rn:= @rn + 1 from (select @rn:= 0) s)-1)/25)+1 WHERE t.topic_id = 52776 ORDER BY t.`order`, t.`id`;

KRoN73 ★★★★★ ()
Ответ на: Re: MySQL: а можно ли одним запросом?... от KRoN73

Re: MySQL: а можно ли одним запросом?...

> balancer.ru/forum это. Он же (в смысле - одна из его подветок: кроме общего списка есть тематически подразделы на разных адресах) - forums.airbase.ru

А сколько там в среднем активных пользователей в сутки/неделю?

blackst0ne ★★★★★ ()
Ответ на: Re: MySQL: а можно ли одним запросом?... от qnikst

Re: MySQL: а можно ли одним запросом?...

Написал первоначальный вариант инсертов (т.е. не поленился все же), но еще не тестировал.

Фичи:

1. задаем дискретное распределение длин топиков

2. задаем максимальное количество мессаг

3. задаем приблизительное количество мессаг в активных топиках

(т.е. эмулируется жизнь настоящего форума во времени)

www_linux_org_ru ★★★★★ ()

Re: MySQL: а можно ли одним запросом?...

А поцчему не взять какой-нить JForum попробовать? Он тоже над MySQL надстроен?

Karapuz ★★★★★ ()
Вы не можете добавлять комментарии в эту тему. Тема перемещена в архив.