Помогите с запросом к большой базе MySQL

[yaski]

В базе имеются две таблицы
passport с полями
FAM, NAM, OT, BIRTH, PASSPORT...
и таблица snils с полями
FAM, NAM, OT, BIRTH, SNILS...
Нужно сделать выборку из обоих таблиц всех записей, где совпадают фамилия имя отчество и дата рождения. Проблема состоит в том, что в базе snils полтора миллиона записей и в таблице passport 120 тысяч записей. Поэтому выборка даже десяти записей занимает продолжительное время. Например такой запрос

SELECT passport.*, snils.* FROM passport LEFT join snils ON passport.FAM=snils.FAM AND passport.NAM=snils.NAM AND passport.OT=snils.OT AND passport.BIRTH=snils.BIRTH LIMIT 0 , 10

исполняется 42 секунды, а такой

SELECT * FROM passport,snils WHERE passport.FAM=snils.FAM AND passport.NAM=snils.NAM AND passport.OT=snils.OT AND passport.BIRTH=snils.BIRTH LIMIT 0 , 10

22 секунды.
Но это очень много и полтора миллиона записей будут выбираться 2 месяца.

Помогите оптимизировать запрос!

 

[Горбушка]

Тут время определяется не лимитом... Уберёшь лимит, время до 2 месяцев не увеличится... У тебя идёт фуллскан таблиц, отсюда и время.

У тебя 99%, что нет индексов на ФИО и дату.. Добавь индексы - тогда не будет фуллскана таблицы...
Второй вариант: Передай эту функцию в ПО... Сделать 10 запросов в БД СНИЛС - это не 40 секунд...

И наконец верный вариант:
Сделай третью таблицу связей, потрать пару дней на её наполнение тяжёлым запросом и проблема будет решена пожизненно... Далее наполняешь таблицу либо через ПО, либо по триггеру...

 

[yaski]

Тут время определяется не лимитом... Уберёшь лимит, время до 2 месяцев не увеличится... У тебя идёт фуллскан таблиц, отсюда и время.

У тебя 99%, что нет индексов на ФИО и дату.. Добавь индексы - тогда не будет фуллскана таблицы...
Второй вариант: Передай эту функцию в ПО... Сделать 10 запросов в БД СНИЛС - это не 40 секунд...

И наконец верный вариант:
Сделай третью таблицу связей, потрать пару дней на её наполнение тяжёлым запросом и проблема будет решена пожизненно... Далее наполняешь таблицу либо через ПО, либо по триггеру...

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

 

[Горбушка]

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

Я так понимаю, объём в Гб не такой большой...

1) Делаем рам-диск на необходимый этим двум таблицам место
2) Переносим хранение БД в раму
3) Делем полную выборку из меньшей таблицы
4) По каждой строке через тот же PHP ищем строки в большей
5) Сохраняем результат в виде 3-ей таблицы или любом удобном виде

Объём БД сходу предсказать не берусь, но не думаю, что там сильно больше пары Гб... Машину с 8 Гб найти не проблема... 4 Гб под РАМ-диск, 4 Гб оставляем для ОС...

Думаю, за пару суток результирующая таблица будет готова... Если не быстрее

 

[latteo]

То что есть индексы не значит, что они работают ....
Explain запросов покажи. И show create table `...` для обоих таблиц.

LEFT join очень часто работает быстрее джойна, странно что у вас наоборот.
Если поля OT, BIRTH имеют тип unixtime или дата попробуйте поставить их в начале иногда это работает быстрее чем по полям varchar.

 

[Black Hat]

фамилия имя отчество и дата рождения

БД не может использовать сразу несколько индексов, только один. Поэтому надо построить составной индекс. Причем по принципу "первые столбцы - самые селективные".
Допустим, фамилий 10 тыс, имен 1 тыс, отчеств 1 тыс, дат рождений (365*50лет примерно 20 тыс). Тогда порядок полей такой:
дата рождения, фамилия, имя, отчество
ALTER TABLE `passport` ADD INDEX `idx_search` (`BIRTH`, `FAM`, `NAM`, `OT`);
То же самое - для второй таблицы.
Думаю, принцип понятен. Надо более точную статистику, если имен и отчеств действительно не так много, то чего больше - фамилий или дат рождений. Чего больше - ставим первым.
Второе - важно чтобы у столбов по которым шло соединение был одинаковый тип (иначе будут преобразования, а это лишнее время). Поэтому SHOW CREATE TABLE `passport` в студию

 

[Роман Дмитриев]

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

 

[Black Hat]

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

Прирост есть но не серьезный Для просмотра ссылки Войди или Зарегистрируйся. По крайней мере для меня, серьезно - это многие разы, потому как есть просто дофига запросов которые можно ускорить в 50+ раз. Дублировать - плохая практика, так как в один прекрасный момент может быть рассогласованность данных. Денормализация это плохо, если не оправдано, а оправданий тут я не вижу.

 

[kto-to]

А какой движек MySQL используется? MyISAM? там чуть по разному джоины работают.
Также можно попробовать обновится на MariaDB например - там еще навернули работу джоинов с индексами.

 

[Роман Дмитриев]

Прирост есть но не серьезный Для просмотра ссылки Войди или Зарегистрируйся. По крайней мере для меня, серьезно - это многие разы, потому как есть просто дофига запросов которые можно ускорить в 50+ раз. Дублировать - плохая практика, так как в один прекрасный момент может быть рассогласованность данных. Денормализация это плохо, если не оправдано, а оправданий тут я не вижу.

Это зависит от того с какими специалистами и в какой сфере приходится работать. Я работаю в IT-сфере, где зачастую всё достаточно хорошо спроектировано, но есть разные мелочи. И именно на этих мелочах (как выше) удаётся получить 60-70% прироста производительности.