Как оптимизировать SQL запрос рандомной выборки?

Question

[NRO]

Вот что есть: SELECT * FROM dump WHERE text LIKE 'some text' ORDER BY RAND() LIMIT 10

Проблема такова: в таблице на данный момент 600к записей, запрос выполняется в среднем 2-4 секунды, а таблица пополняется, и в перспективе будет раз в 40 больше (что по-логике вещей пропорционально увеличит время запроса и ресурсы на вычисление). В итоге сервак прикурит. Перефразирую задачу: мне нужно выбрать 10 рандомных записей соответствующих паттерну самым оптимальным способом. Помогите пожалуйста.

На хабре вычитал следующее (пример был без LIKE): В процессе выполнения этого запроса MySQL записывает во временную таблицу все (!!!) строки исходной таблицы, с одним новым полем, в которое записываются результаты функции RAND () — т.е. набор произвольных значений. Затем эта временная таблица сортируется filesort по добавленному полю с произвольными значениями и далее выбираются первые 10 записей.

Answer 1

[Дмитрий Энтелис]

У Вас есть 2 задачи: задача найти вхождения строки, и задача выбрать из результатов случайные 10.
Это 2 разные задачи :)

Первую задачу логично решать используя инструменты предназначенные для поиска, например Sphinx. sphinxsearch.com
Он быстро вернет Вам id подходящих записей.
Из них средставами PHP выбираем сколько надо случайных, далее запрос в SQL select ... where id in (1,2,3,4...)

В Вашем случае это будет наиболее производительное и масштабируемое решение.

PS Мне не очень понятна сама идея вывода 10 случайных записей соответствующих паттерну. Не лучше ли выводить 10 самых релевантных? :) Или у вас слишком общий паттерн и о релевантности речи не идет? Уточните этот момент пожалуйста.

Comments

[NRO]

У меня есть база вида вопрос-ответ (грубо говоря). Мне нужно найти 10 схожих вопросов (по тексту, соответственно). Рандом нужен для предотвращения однотипности ответов.

Спасибо за Sphinx, сейчас посмотрю что это такое.

[whats]

зачем куда то выносить логику если ее можно сделать в базе ? Все работает быстрее чем в предложенном вами варианте со сфинксом и пхп.

[Дмитрий Энтелис]

@whats: LIKE не будет работать быстрее чем предложенный мной вариант со сфинксом. Если вам кажется что он быстрее - отключите query cache в sql.
До кучи бесплатным бонусом сфинкс умеет делать морфологический поиск.
Вообще за реализацию поиска через LIKE надо убивать. Медленно и в муках.
Это настолько очевидно что я даже не хочу это аргументировать.

[whats]

@DmitriyEntelis: я не рассматриваю LIKE в данном ключе вообще никак, вы советуете автору изменить бизнес логику размазав ее по 3 уровням. Если не брать LIKE во внимание можно все сделать на постгресе например в его среде. Поверьте будет не хуже сфинкса! Только это все происходить будет в 1 месте вместо ваших трех. Если брать Mysql то у него так же есть полнотекстовый поиск, который в различных ветках этой базы отличается по производительности. Опять же, заменить базу и сделать все что нужно в ней.

А сфинкс надо еще настроить и проиндексировать. Ну а с РТ индексами мало кто умеет работать правильно.

[Дмитрий Энтелис]

@whats: не очень понимаю о чем вы - бизнес логика находится в php. Сложностей в настройке сфинкса на таких объемах нет.
Ну а заменить базу - это imho слишком суровое решение для одной мелкой задачи.

[whats]

@DmitriyEntelis: Если нам нужно вытащить 20 случайных записей из базы то логично :) что логика этого процесса должна находиться в базе. Когда все это можно сделать только в ней. Если человек настроит нормально полнотекстовый поиск в своей базе то менять ничего не нужно.

То есть на больших объемах настройка сфинкса чем то будет отличаться ? Хотелось бы узнать чем.

[Дмитрий Энтелис]

@whats: мне очень интересно, как вы планируете в чистом mysql выбирать случайные записи.
Прочитал ваш ответ - ок, вы молодец, написали запрос который проставил всем row_number.
Дальше что с ним делать?
Качать все данные в php? Это жесть, там могут быть сотни тысяч значений.
Генерить id из php и писать запрос вида select * from (ваш запрос) where id in (1,2,3) - во первых вам придется делать отдельный запрос для нахождения максимального вашего id, а во вторых это будет нереально медленно в случае большого выбранного случайного id

[whats]

@DmitriyEntelis: Не очень хочу с вами спорить. У нас большое различие в подходе и в применимых знаниях. Вам что бы узнать максимальный ид подзапроса нужно возвращать данные или делать 2 запрос, мне только обратиться к переменной что была в запросе ниже. Оверхед ? Да минимальный самый. Сделайте бенчмарк инкремента. На 100кк записей 1.4 секунды на моей конфигурации. Если кому то нужно будет с таким объемом данных что то сделать... Я не знаю кому это может придти в голову.

Как на чистом SQL, а не mysql выбрать данные ? Автор написал реализацию с LIKE RAND(). Я ее предложил заменить на полнотекстовый поиск с подзапросом, что можно в принципе сделать и без него, данные он в предикате сокращает в численности, а далее к ним уже добавляется функция рандома. Так же ниже есть ссылка на такие же рандомные выборки. Вот так примерно я и планировал.

Конкретный запрос вот такой
SELECT * FROM table WHERE MATCH() AGAINST() AND ROUND(RAND()) =1 ORDER BY RAND() LIMIT 10

[Дмитрий Энтелис]

@whats: запрос который вы привели, рассчитает RAND() для каждой строки удовлетворяющей MATCH.
По условиям автора - это не приемлимо, т.к в выборку попадает большая часть базы.

Смысл кода ROUND(RAND()) =1 от меня вообще ускользает - смысл выкидывать ~половину значений?

[whats]

@DmitriyEntelis: опечатка, в моем запросе это не нужно ORDER BY RAND()
Поэтому запрос что привел я рандомит по ROUND(RAND()) =1, это как пример, вы можете написать любую функцию вычисления случайного числа и потом сравнивать с каким нибудь другим случайным числом.
Вы конкретно спросили - мне очень интересно, как вы планируете в чистом mysql выбирать случайные записи.
я вам конкретно ответил с примером. На чистом sql ? ДА! Вы теперь вернулись к условию автора, хорошо. Начинаем с начала. Ваше кстати решение так же возвращает ваши сотни тысяч данных в пхп. Мое же работает только в базе и возвращает 10 значений.

но так как вы упорно не видите о чем я вам пишу давайте разберем ваше решение.
1)Первое что нужно найти автору это все совпадения с его запросом.(Вернулись сотни тысяч ИД из сфинкса)
2)Вы эти сотни тысяч засовываете в пхп скрипт и выбираете случайные 10
3)Вы передаете эти значения в запрос к базе данных

Не вижу чем ваше решение отличается от решения автора ? Все тоже самое. Только добавился еще сфинкс. Все тоже самое можно сделать в одной базе, запросы выше. Вы теперь это понимаете ?

Если мы говорим о действительном рандоме всех записей то ORDER BY RAND() тут не играет собственно никакой роли, на таблице в 1кк записей поиск по LIKE у меня занял 2.3 секунды и вернулось более 200 записей, с RAND() поиск занял 2.35.
Предложенный мной вариант вернет рандомные записи в первой части данных, что полностью решает поставленную задачу автора. Без Сфинкса !!!

[Дмитрий Энтелис]

"на таблице в 1кк записей поиск по LIKE у меня занял 2.3 секунды и вернулось более 200 записей"
И Вы говорите что не нужен сфинкс? Ну ну )

Answer 2

[Радость моя]

www.warpconduit.net/2011/03/23/selecting-a-random-...

вот тут 3 примера шустрых rand() выборок

Comments

[NRO]

Спасибо, сейчас почитаю

Answer 3

[whats]

На комментарий kazmiruk
в таком случае вам подойдет подзапрос в котором вы выбираете все LIKE записи.

SELECT * FROM (SELECT * FROM table WHERE column LIKE ?) sub

Но так как mysql инвалид во всем, то далее вам необходимо применить имитацию ROW_NUMBER()

SELECT * FROM (SELECT t.*, @row_number := @row_number + 1 AS rank FROM table t,(SELECT @row_number := 0) r WHERE t.column LIKE ?) sub

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

Далее вы в основном запросе можете делать ваш запрос что был в примере с RAND(). Но вы учтите что LIKE это медленная функция поиска, а в случае с %text% она тормозит очень сильно. Опять же повторюсь, mysql это дерьмовая база для всего кроме обычного чтения по индексу и записи. В ней этого лучше не делать. Тот же postgres со своим полнотекстовым поиском сделает все на уровне сфинкса, в некоторых случаях даже быстрее.

Answer 4

[ugodrus]

Если количество результатов по условию достаточно велико, то соответственно вычисляется много случайных чисел для этих полей. И LIKE я лично не использую никогда - очень тупой метод.. а когда с большими объёмами работает - вообще мрак. Попробуйте переделать под REGEX и если набор выбранных данных всегда достаточно большой - лучше переделать по другому. Советую заглянуть сюда.