Форум Сайтостроение Веб-строительство

Помогите оптимизировать (переписать) SQL запрос

12 3
M
На сайте с 11.01.2006
Offline
153
Magistr
1169

Добрый день! Возникла очень интересная ситуация, прошу помощи в поиске решения.

Есть запрос: 


SELECT table1.email, table1.last_name, table1.first_name, table1.phone1, table1.patronymic,

                table2.id as exist_id

                FROM table3, table1

                LEFT JOIN table2 on (table2.email IN(table1.email, table1.email_1, table1.email_2))

                WHERE

	            table3.group_id IN ("13,14") 

	            and (table3.uuid =table1.puuid)

	            AND 

                (

                    (table1.email IS NOT NULL )

                    or (table1.email_1 IS NOT NULL)

                    or (table1.email_2 IS NOT NULL)

                    or (table1.email_3 IS NOT NULL)

                    or (table1.email_4 IS NOT NULL)

                )

                GROUP BY table1.email

Суть запроса. Есть таблица с контактными данными людей (table1) (назовем их сотрудниками различных компаний). Мне нужно дернуть емейлы всех сотрудников, и проверить есть ли их емейлы в базе получателей моей рассылки (table2).

Вся соль, что у сотрудника может быть несколько емейлов забито в его профиле, поэтому приходится проводить вот такой left join 

LEFT JOIN table2 on (table2.email IN(table1.email, table1.email_1, table1.email_2))

Суть проблемы заключется в том, что при ~ 7000 сотрудников, такого рода запрос выполняется около 35 сек на довольно таки не слабом сервере.

Методом проб и исключений, что удалось выяснить:

Если в условие left join подставить только 1 поле, к примеру вот так: 

LEFT JOIN table2 on (table2.email IN(table1.email))

выборка выполняется за 0,1 сек

Каждое последующее добавление в условие еще одного поля увеличивает время выполнения до 32-35 сек, и больше не увиличивается, т.е если больше 1 поля в IN условии - получаем запрос в 35 сек.

Если в условие IN передать не название колонок, а обычные строковые данные, запрос выполняется моментально, не зависимо от переданного кол-ва строк. пример моментально выполненного запроса: 

LEFT JOIN table2 on (table2.email IN('table1.email', 'table1.email_1', 'table1.email_2'))

Индексы: на все поля, которые участвуют в условиях выставлены индексы.

Вариант вынесения емейлов сотрудников в отдельную таблицу рассматриваю в последний момент :)

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

Просьба сильно не пинать, не являюсь гуру SQL :)

Буду благодарен за любые советы и подсказки.

PN
На сайте с 22.08.2012
Offline
103
proksey-net
#1

Я рекомендую не засовывать все в один запрос. Несколько E-mail у одного сотрудника делается не тремя колонками в таблице, а через связующую таблицу email_user:


email_user
id
email_id
user_id
Мой совет помог? Не скупись! Bitcoin 1Lseddet1o1B6odgXQHbGaWGwRkt1Db8Ef Ethereum 0x450f1a17461e25194B7F9226cDEe70173F39e1e1
Aisamiery
На сайте с 12.04.2015
Offline
301
Aisamiery
#2
Magistr:

выборка выполняется за 0,1 сек
Каждое последующее добавление в условие еще одного поля увеличивает время выполнения до 32-35 сек, и больше не увиличивается, т.е если больше 1 поля в IN условии - получаем запрос в 35 сек.

Выполните запрос 3 раза, будет 0.3 сек. А так, если я правильно понимаю в IN вы каждый раз обходите таблицу table1, но 7000 записей для базы это фигня какая то, запустите EXPLAIN

Разработка проектов на Symfony, Laravel, 1C-Bitrix, UMI.CMS, OctoberCMS
Какой нужен сервер? Отчет по всем базам MySql постоянно падает
dma84
На сайте с 21.04.2009
Offline
168
dma84
#3

Попробуйте сделать составной индекс на емейлы

Или как вариант:


SELECT

	table1.email,

	table1.last_name,

	table1.first_name,

	table1.phone1,

	table1.patronymic,

	table2.id AS exist_id

FROM

	table1	

LEFT JOIN table2 ON (table2.email = table1.email)

LEFT JOIN table2 ON (table2.email = table1.email_1)

LEFT JOIN table2 ON (table2.email = table1.email_2)

LEFT JOIN table2 ON (table2.email = table1.email_3)

LEFT JOIN table2 ON (table2.email = table1.email_4)

GROUP BY table1.email

В данном случае не нужен составной индекс, будут юзаться отдельные индексы для каждого джоина.

table3 убрал, она ни к селу ни к городу, если, кончено, вы представили полный запрос.

MySql постоянно падает Подскажите по MySQL Как снизить нагрузку скриптов
B
На сайте с 23.05.2001
Offline
195
Basilisk
#4

Magistr

А дамп базы можете сделать?

Интересно попробовать, но с нуля моделировать схему лень. Больше подготовительных работ, чем непосредственно процесса решения :)



---------- Добавлено 21.07.2016 в 16:46 ----------

dma84

Если 35 секунд на 7000 записей - дело не в индексах, ошибка именно в схеме БД.

К примеру, email_1 - email_4 - это уже непонятно, зачем денормализация в этом месте и чем вызвана (но это просто к примеру, потому что про отдельную таблицу автор уже писал, что в последнюю очередь).

Как правильно организовать работу MySQL, GROUP BY - Как грамотно подготовить почву
Aisamiery
На сайте с 12.04.2015
Offline
301
Aisamiery
#5
Basilisk:

Если 35 секунд на 7000 записей - дело не в индексах, ошибка именно в схеме БД.

без EXPLAIN не понять, скорее всего там где то не юзается индекс и mysql из за этого ворочает миллионами строк, собственно по этому так и долго.

B
На сайте с 23.05.2001
Offline
195
Basilisk
#6
Aisamiery:
без EXPLAIN не понять, скорее всего там где то не юзается индекс и mysql из за этого ворочает миллионами строк, собственно по этому так и долго.

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

Если там ничего секретного нет, конечно.

Хотя, данные сотрудников, плюс контакты и все такое, не уверен, что можно. Даже самому сомнительно стало :)

Но 35 секунд даже для нескольких миллионов строк много.

Почему некоторые копирайтеры не Помогите, трафик растет-продаж нет Хостеры, это нормально?
M
На сайте с 11.01.2006
Offline
153
Magistr
#7
Basilisk:
Magistr

А дамп базы можете сделать?
Интересно попробовать, но с нуля моделировать схему лень. Больше подготовительных работ, чем непосредственно процесса решения :)





dma84

Если 35 секунд на 7000 записей - дело не в индексах, ошибка именно в схеме БД.
К примеру, email_1 - email_4 - это уже непонятно, зачем денормализация в этом месте и чем вызвана (но это просто к примеру, потому что про отдельную таблицу автор уже писал, что в последнюю очередь).
Aisamiery:
без EXPLAIN не понять, скорее всего там где то не юзается индекс и mysql из за этого ворочает миллионами строк, собственно по этому так и долго.
Basilisk:
Ну вот поэтому и интересно на схему глянуть - готовую развернуть, чтобы не гадать.
Если там ничего секретного нет, конечно.
Хотя, данные сотрудников, плюс контакты и все такое, не уверен, что можно. Даже самому сомнительно стало :)
Но 35 секунд даже для нескольких миллионов строк много.

К сожаленью, сами понимаете, персональные данные ~7000 сотрудников, увы, не смогу :(

EXPLAIN

https://www.dropbox.com/s/pnqfvct1rxiqaai/2016-07-21_173735.jpg?dl=0

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

При Join с 1 емейлом - выполняется 0.1 сек

При Join с 2-мя емейлами - 33сек

При Join с 3-мя емейлами - 33сек

При Join с 4-мя емейлами - 33сек

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

Вот и пытаюсь понять, почему Join по 1 полю - все отлично, по 2 и больше - затык, причем с одинаковой скоростью выборки.

Ухожу от Majordomo по Секционное хранение статьи в Оптимизация MYSQL DB -
Aisamiery
На сайте с 12.04.2015
Offline
301
Aisamiery
#8
Magistr:

Вот и пытаюсь понять, почему Join по 1 полю - все отлично, по 2 и больше - затык, причем с одинаковой скоростью выборки.

У вас запрос обрабатывает 32 304 386 строк

А добавьте EXPLAIN с одним параметром, который отрабатывается быстро. У вас просто на джоинах не используються индексы, да и тип объединения чуть лучше чем ALL, но очень плохо что последний.

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

MySQL: индексы почти на Проблемы с большими массивами SQL | Много таблиц
B
На сайте с 23.05.2001
Offline
195
Basilisk
#9
Aisamiery:
У вас запрос обрабатывает 32 304 386 строк
А добавьте EXPLAIN с одним параметром, который отрабатывается быстро. У вас просто на джоинах не используються индексы, да и тип объединения чуть лучше чем ALL, но очень плохо что последний.
А вообще предлагаю упрощать запросы, разбивать на подзапросы и менять структуру или вводить избыточность в таблицы.

неплохо, кстати

единственное, что избыточности и так много

M
На сайте с 11.01.2006
Offline
153
Magistr
#10
Aisamiery:
У вас запрос обрабатывает 32 304 386 строк
А добавьте EXPLAIN с одним параметром, который отрабатывается быстро. У вас просто на джоинах не используються индексы, да и тип объединения чуть лучше чем ALL, но очень плохо что последний.
А вообще предлагаю упрощать запросы, разбивать на подзапросы и менять структуру или вводить избыточность в таблицы.

EXPLAIN с 1 параметром:

https://www.dropbox.com/s/vq990lw6pccuqo5/2016-07-21_183144.jpg?dl=0

12 3

Авторизуйтесь или зарегистрируйтесь, чтобы оставить комментарий