Комментарии 41
Вы, вероятно, подумали, что мы предлагаем вложенный запрос. Ни в коем случае! Подразумевается IN (1,2,3,4...). Вложенные запросы в MySQL даже не рассматриваем.
Ничего странного. Вложенный запрос приводит к вызову вложенного запроса для каждой строки внешнего. А не вычисляется один раз перед выполнением внешнего.
Это истинно только для correlated subqueries. Нормальный оптимизатор СУБД не коррелированные запросы выполняет единожды (не уверен, как с этим в последних версиях mysql)
Эм, и какие у вас будут references к the outer query?

Внутренний запрос вообще никак от внешнего не зависит, и именно этим вы и пользуетесь, разделяя задачу на 2 запроса
MySQL смотрит referrence к таблице, а не к значению. Таким образом вложенный запрос вида select * from messages where messageid in (select messageid from messages where user1=....limit) не будет оптимизирован как надо.
Вы заставили меня сомневаться. Решил проверить и вот, что я получил
There is an error in select query This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Сижу думаю как можно проверить.
set @off = 10;
set @lim = 5;
select * from messages where messageid in (
    select messageid from (select messageid, (@n:=@n+1) as n
    from messages, (select @n:=0) a) x 
    where n > @off and n <= @off + @lim
);

Тока никому не показывайте…
О боже, это напоминает мне MSSQL мантры… Как же всё печально.
Первый же пример по вашей ссылке делает акцент именно на полях, а не таблицах
Скорее нужно ссылаться на dev.mysql.com/doc/refman/5.5/en/subquery-restrictions.html

Который объясняет, что mysql сам переписывать не коррелированные запросы — в коррелированные. Т.е. проблема, что вы описали, существует («не будет оптимизирован как надо.»), но причины у неё — иные (запрос хороший, а оптимизатор тупит)
Большое спасибо за ссылку. Как-то раньше не попадалась эта страница мне на глаза.
Вложенные запросы тожно нужно уметь делать:
select * from messages m inner join (select messageid from messages where user1=1 and user2=2 limit) m2 on m.messageid = m2.messageid
>> Пользователей в нашей системе всего 10, таким образом, у каждой пары собеседников будет в среднем по миллиону сообщений.

А разве среди 10 собеседников не 45 уникальных пар?
Во первых, история — сущность личная для каждого пользователя и он может её удалять её на своё усмотрение. Так что получаем как минимум 90.
Во вторых пользователь может писать сам себе. (Оставшиеся 10)

А в третьих я упоминал, что это — иллюстрация проблемы, а не реальная структура. Скрипт просто наполняет таблицу случайными данными. Получается 100 пар.
О, прикольно.
А почему возможность удаления сделали именно дублированием, а не флажками? Потому что места на винте много, а выбирать без дополнительного флажка быстрее? (хотя спорно, но всё таки)
К пониманию этого быстро приходишь когда начинаешь делать шардирование по пользователям. У каждого пользователя в шарде должна быть его собственная копия данных. Пользователь должен работать только со своим шардом.
Но сообщений друг другу они могут понаписать миллионы при должном старании)
Если у вас во втором случае 30 млню апдейтов работают так долго, может вам проще вообще пересоздавать таблицу со статистикой с нуля, чем обновлять большое число записей в существующей?
В таблице есть данные, которые уже отсутствуют в основной системе. Это и удалённые пользователи и параметры вида «первый раз воспользовался мобильным приложением».
Расскажите, пожалуйста, а как вы организовали сортировку 30 миллионов значений? Спасибо.
ts является leftmost в ключе key(f1, f2, ts) и запросе типа
WHERE f1=const AND f2=const ORDER BY ts
Так что mysql отлично берёт и сортирует выборку, используя индекс
Но я понял, что сортировка происходит не в базе, а сортируется содержимое файлов.
Интересует, какие алгоритмы надо использовать, чтобы решить проблемы затрат памяти (30 миллионов сериализованных строк надо как-то обрабатывать ведь), хотя, возможно, я не так понимаю процесс решения.
Основная идея такая — разбить на примерно одинаковые части. Отсортировать каждую из них в памяти (в несколько потоков). А затем осуществить слияние данных.

Это можно реализовать множеством способов. В данном случае мы воспользовались тем, что значения ключа сортировки равномерно распределены по множеству возможных значений. Потому мы разделили множество возможных значений на 400 диапазонов. Разбили данные на 400 одинаковых частей. Получили 400 файлов с данными. Затем поочерёдно перебираем эти файлы, сортируем в памяти и вставляем данные в таблицу.
Так вы потом эти 400 файлов сливаете? Или у вас 400 независимо отсортированных файлов?
Давайте на примере
Диапазон значений 1-10000.

Разбиваю на 10 диапазонов 1-1000, 1001-2000,...,9001-10000.

Беру исходные данные и раскладываю по 10 файлам.
в первом файле 66,444,33,2,432
во втором файле 1039,1984,1433

Затем беру первый файл. Сортирую его в памяти и вставляю в БД
2,33,66,432,444
Беру второй файл. Сортирую его в памяти и вставляю в БД
1039,1433,1984

Так нагляднее?
Ясно, понятно. Еще чисто утилитарный вопрос — чем сортировали параллельно? Я, к примеру, нагуглил готовые реализации многопоточного quicksort'а, но может, есть что лучше для этих целей.
В данной задаче многопоточность не используется, ибо в памяти сортировка работает и без того быстро. Но думаю, что скорее всего использовали бы PHP. У нас уже есть подходящие классы для реализации изолированной многопоточности в фреймворке.
Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.