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

Тема в разделе "Базы данных", создана пользователем yaski, 22 сен 2015.

Статус темы:
Закрыта.
Модераторы: latteo
  1. yaski

    yaski

    Регистр.:
    21 фев 2010
    Сообщения:
    500
    Симпатии:
    301
    В базе имеются две таблицы
    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. Горбушка

    Горбушка Ищу её...

    Регистр.:
    2 май 2008
    Сообщения:
    3.199
    Симпатии:
    2.223
    Тут время определяется не лимитом... Уберёшь лимит, время до 2 месяцев не увеличится... У тебя идёт фуллскан таблиц, отсюда и время.

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

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

    yaski

    Регистр.:
    21 фев 2010
    Сообщения:
    500
    Симпатии:
    301
    индексы проставлены и все равно, как я понимаю, идет полное сканирование обеих таблиц
    по второму варианту не совсем понял
    по третьему варианту выборка нужна всего-лишь на один раз, поэтому тут нужен один грамотный запрос, который я хз как правильно составить, не очень в этом силен.
    Помогите!!!
     
  4. Горбушка

    Горбушка Ищу её...

    Регистр.:
    2 май 2008
    Сообщения:
    3.199
    Симпатии:
    2.223
    Если нужно однократно и есть машина, которая может полежать пару суток под нагрузкой, выполняя работу...

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

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

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

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

    latteo Эффективное использование PHP, MySQL

    Moderator
    Регистр.:
    28 фев 2008
    Сообщения:
    1.546
    Симпатии:
    1.418
    То что есть индексы не значит, что они работают ....
    Explain запросов покажи. И show create table `...` для обоих таблиц.

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

    Black Hat Постоялец

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

    Роман Дмитриев Создатель

    Регистр.:
    16 авг 2015
    Сообщения:
    14
    Симпатии:
    0
    Если преобразуете поле даты рождения в числовое (или продублируете его ещё в одном поле и будете использовать для сравнения), то получите ощутимый прирост в скорости.
     
  8. Black Hat

    Black Hat Постоялец

    Регистр.:
    15 май 2015
    Сообщения:
    121
    Симпатии:
    81
    Прирост есть но не серьезный тыц. По крайней мере для меня, серьезно - это многие разы, потому как есть просто дофига запросов которые можно ускорить в 50+ раз. Дублировать - плохая практика, так как в один прекрасный момент может быть рассогласованность данных. Денормализация это плохо, если не оправдано, а оправданий тут я не вижу.
     
    Горбушка и Renny нравится это.
  9. kto-to

    kto-to Создатель

    Регистр.:
    19 май 2012
    Сообщения:
    15
    Симпатии:
    2
    А какой движек MySQL используется? MyISAM? там чуть по разному джоины работают.
    Также можно попробовать обновится на MariaDB например - там еще навернули работу джоинов с индексами.
     
  10. Роман Дмитриев

    Роман Дмитриев Создатель

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