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

в мускуле rowid нету?

такс не rowid а rownum

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

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

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

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

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

Экие программисты затейники бывают.

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

Не катит. Там вариант с копированием таблицы. Копировать 5Гб и 2 миллиона записей - накладно :)

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

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

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

> Вариант с полем page - НАМНОГО эффективнее.

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

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

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

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

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

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

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

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

Сейчас - 

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)

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

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

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

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

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

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

И вообще пишут что лимит быстро работает и так http://www.mysql.ru/docs/man/LIMIT_optimisation.html однакто что-то тут не так.

Всё, нашёл в первом приближении :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

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

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

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

Там про индексы пишут, хотя прозреваю что у тебя они есть.

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

...

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

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

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

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

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

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)

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

> Вышло 0.22сек.

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

CREATE INDEX i1 ON posts(topic_id,id)

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

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

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

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

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

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

>Мне интересно эксперимент поставить на 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

Жду предложение от тебя типа: 100К топиков в каждом из которых от 0 до 20К постов, в среднем 10К постов, в посте в среднем ... байт.

Да, еще как-то надо записи в таблицу добавлять не по-порядку.

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

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

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

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

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

>Жду предложение от тебя типа

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

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

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

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

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

>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 закеширует.

>может можно сделать 50К топиков, в каждом из которых ровно по 5К мессаг?

Ну, где-то близко будет.

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

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

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

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

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

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

>Тут еще от оперативки сильно зависит, т.к. время сика у винта около
>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 сообщений в теме. У меня даже ругань начиналась автоматическая,
типа «тема слишком большая, открывайте новую».

>А может ты напишешь?

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

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

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

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

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

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

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

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

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

Вот, на 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`;

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

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

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

Фичи:

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

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

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

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

Т.е. "не тестировал свой скрипт", а не "не тестировал производительность мускула".

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

http://top.mail.ru/visits?id=1574967&period=0&date=2009-07-18

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