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

Question

[vadikrudnov]

База MySQL, в ней содержится 5млн. записей, каким методом можно моментально/быстро найти нужную запись с данными?
Делаю так:

Model::where('model', 989)->where('number_mod', '00003454354743643')->orderBy('id', 'desc')->first();

Но такой запрос занимает 7-8 секунд, хотелось бы снизить до меньше секунды.
Запрос подобного рода, сокращает ответ до 4 секунд, но все равно это много:

DB::table('models')->select('model','number_mod','created_at')
  ->where('model', 989)
  ->where('number_mod', '00003454354743643')
  ->orderBy('id', 'desc')->first();

Comments

[Дмитрий]

explain?

[vadikrudnov]

Дмитрий, не понимаю что это дает:

0 => {#335 ▼
    +"id": 1
    +"select_type": "SIMPLE"
    +"table": "models"
    +"type": "index"
    +"possible_keys": null
    +"key": "PRIMARY"
    +"key_len": "4"
    +"ref": null
    +"rows": 6000000
    +"Extra": "Using where"

[pLavrenov]

Учи мат часть, а не оспаривай ответы, уже дали 2 ответа на вопрос. 5млн записаей это не много, время запросов не должно быть даже больше секунды. Гуглить "Индексы в MySQL"

[Дмитрий]

vadikrudnov, а вы можете нормально опубликовать ответ на explain. И это точно explain того запроса что вы привели?

[vadikrudnov]

pLavrenov, Спасибо за разъяснения!

[vadikrudnov]

Дмитрий, да, тот самый ответ, запрос делал с ->explain()->dd()

[Дмитрий]

vadikrudnov, а структура таблицы какая?

[vadikrudnov]

Дмитрий, вот такая

[Дмитрий]

vadikrudnov, а. вы сортируете по id. ну вообщем вам нужен композитный индекс по полям model, number_mod вперед ставим поле с большей селективностью.

[vadikrudnov]

Дмитрий, вы имеете ввиду составной индекс? Тогда number_mod + model

[Дмитрий]

vadikrudnov, да. и после сделайте explain еще раз

[vadikrudnov]

Дмитрий, Спасибо большое!

array:1 [▼
  0 => {#335 ▼
    +"id": 1
    +"select_type": "SIMPLE"
    +"table": "models"
    +"type": "ref"
    +"possible_keys": "FINDDUPLICATE"
    +"key": "FINDDUPLICATE"
    +"key_len": "14"
    +"ref": "const,const"
    +"rows": 1
    +"Extra": "Using where"
  }
]

Да, теперь поиск идеален!

[Дмитрий]

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

и вообще включите slow log и мониторьте запросы

[vadikrudnov]

Дмитрий, про индексы читал, у меня поиск идет исключительно по этим полям, поэтому в другие пихать не собираюсь.

Answer 1

[Dmitry Bay]

Сделайте сперва запрос в SQL.
Поймите слабые места.
Добавьте индексы.
Сделайте снова запрос в SQL.

У вас
where('model', 989)->where('number_mod', '00003454354743643') - оба параметра перебираются перебором, лучше иметь индекс или с fk.
Для 5 млн записей вы можете сделать индексы, я думаю и все будет летать.

Answer 2

[alexalexes]

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

Создаете индексы в базе данных по полям, которые используются в условиях запросов, и тогда будет скорость.
PS: Конечно, лучше иметь представление, как ORM создает текст запроса SQL, а потом обращается с ним к СУБД. И как анализировать этот запрос SQL, иначе работа с ORM превращается в практику работы с магией.

Comments

[vadikrudnov]

Про какие индексы Вы говорите? У таблицы есть индекс ID

[alexalexes]

www.mysql.ru/docs/man/MySQL_indexes.html
https://habr.com/ru/post/556440/

Answer 3

[rPman]

Для запросов, включающих в себя и фильтрацию where и сортировку/групировку, нужны составные индексы (обычно порядок полей в составном индексе рекомендуется сначала поля, которые в условии where, потом те что в group by/order by)

Опиши модель данных, без конкретики, но важен смысл любых чисел и условий в твоем запросе, в частности меня смущает твое условие first обычно это показывает что что то не так в самом способе хранения и использования данных

p.s. иногда, когда запросов на чтение сильно больше запросов на запись, логичнее заполнять тригерами искомые данные в кастомной табличке-кеше

например в твоем случае последняя добавленная запись может быть скопирована на insert/update в дополнительную таблицу (где вообще индексов нет), тогда брать ее от туда будет максимально просто и быстро

Answer 4

[Антон]

Можно попробовать так:
1. Получить итоговый запрос который выдает ORM, наверняка у ORM есть опция для этого
2. Выполнить EXPLAIN для этого запроса, на БД с кол-вом и качеством данных близких к реальному
3. Проанализировать EXPLAIN из п.2, таки образом понять, какие индексы пытается использовать MySQL
4. Посмотреть какие индексы есть на таблицах, в MySQL - `SHOW INDEX FROM ...`
5. Подумать, нельзя ли изменить запрос, чтобы он использовал индексы, можно все тем же EXPLAIN

Если этот запрос достаточно частый, тогда возможно есть смысл добавить индексы

Индексы нужно добавлять только по тем полям которые точно используются в запросе, во первых серверу меньше читать с диска индексы, а во вторых, так работают составные индексы, обратите внимание на раздел про составные индексы в статье "Индексы MySQL https://highload.today/indeksy-v-mysql/'
Кстати хорошая статья, рекомендую