ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Оптимизация запроса с большим объемом данных
27 сообщений из 27, показаны все 2 страниц
  все  
Оптимизация запроса с большим объемом данных
    #39088414
sanich
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sanich
Участник
Добрый день.

Прошу помощи у профессионалов по ускорению запроса для Mysql
Есть таблица email_subscribe с набором значений:

Код: sql
1.
2.
3.
4.
5.
6.
id      ekey
1       ключ1
2       ключ1
3       ключ2
4       ключ1
5       ключ1



Необходимо получить дубликаты по полю "ekey", которые в дальнейшем можно удалить

Сделал 2 запроса, они выводят, список нужных дублей по полю "ekey", которые можно потом удалить...

Код: sql
1.
2.
3.
SELECT t.* FROM email_subscribe t  
LEFT JOIN (SELECT id FROM email_subscribe GROUP BY ekey) as t2 ON t.id=t2.id  
WHERE t2.id IS NULL



Код: sql
1.
2.
select * from email_subscribe 
where id not in (SELECT id FROM email_subscribe GROUP BY ekey)



Результат двух запросов правильный и идентичный:
Код: sql
1.
2.
3.
4.
id      ekey
2       ключ1
4       ключ1
5       ключ1




Проблема в том, что при больших объемах данных (если в таблице email_subscribe будет 700 000 записей) эти запросы очень долго выполняются, не могу дождаться завершения запроса на локальном хосте при ~160 000 строк

Индексы по нужным полям таблицы email_subscribe поставил


Привожу explain запроса:
Код: sql
1.
2.
explain select * from email_subscribe
where id not in (SELECT id FROM email_subscribe GROUP BY ekey)




Привожу explain запроса:
Код: sql
1.
2.
3.
explain SELECT t.* FROM email_subscribe t
LEFT JOIN (SELECT id FROM email_subscribe GROUP BY ekey) as t2 ON t.id=t2.id  
WHERE t2.id IS NULL




В чем причина длительного выполнения обоих запросов?
...
Рейтинг: 0 / 0
27.10.2015, 20:05:17
| Ответить | Цитировать | Написать  
Оптимизация запроса с большим объемом данных
    #39088417
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
sanich,

Каков общий уровень дубликатов в поле ekey?
Если 99% значений уникальны, то это одно. А если каждое из значений повторяется сотни-тысячи раз, то это другое.
...
Рейтинг: 0 / 0
27.10.2015, 20:10:37
| Ответить | Цитировать | Написать  
Оптимизация запроса с большим объемом данных
    #39088420
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
Не очень понял ваши эксперименты с группировкой.
Почему не сделать проще:
Код: sql
1.
2.
3.
SELECT MIN(id), ekey
FROM email_subscribe
GROUP BY ekey

?
...
Рейтинг: 0 / 0
27.10.2015, 20:13:17
| Ответить | Цитировать | Написать  
Оптимизация запроса с большим объемом данных
    #39088427
Фотография Lumix
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Lumix
Участник
Код: sql
1.
2.
3.
4.
5.
select * from t 
    natural join 
        (select id from t 
            group by ekey   
            having count(*) > 1) x
...
Рейтинг: 0 / 0
27.10.2015, 20:41:58
| Ответить | Цитировать | Написать  
Оптимизация запроса с большим объемом данных
    #39088431
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
sanichНеобходимо получить дубликаты по полю "ekey", которые в дальнейшем можно удалить
Давайте всё-таки без допущений. Либо мы их удаляем (тогда нафига их получать), либо получаем (тогда к чему разговор за удаление).
...
Рейтинг: 0 / 0
27.10.2015, 20:56:07
| Ответить | Цитировать | Написать  
Оптимизация запроса с большим объемом данных
    #39088659
DBConstructor
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
DBConstructor
Участник
sanich, хочется задать уточняющий вопрос: Зачем эти дубли туда добавлять? Bad design?
...
Рейтинг: 0 / 0
28.10.2015, 09:50:03
| Ответить | Цитировать | Написать  
Оптимизация запроса с большим объемом данных
    #39090192
sanich
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sanich
Участник
DBConstructorsanich, хочется задать уточняющий вопрос: Зачем эти дубли туда добавлять? Bad design?

недочеты работы программистов
...
Рейтинг: 0 / 0
29.10.2015, 15:26:01
| Ответить | Цитировать | Написать  
Оптимизация запроса с большим объемом данных
    #39090197
sanich
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sanich
Участник
AkinasanichНеобходимо получить дубликаты по полю "ekey", которые в дальнейшем можно удалить
Давайте всё-таки без допущений. Либо мы их удаляем (тогда нафига их получать), либо получаем (тогда к чему разговор за удаление).

Надо получить, для того чтобы в дальнейшем обработать эти записи, есть другая таблица где похожая ситуация с дублями и там нужно просто удалить дубликаты
...
Рейтинг: 0 / 0
29.10.2015, 15:27:21
| Ответить | Цитировать | Написать  
Оптимизация запроса с большим объемом данных
    #39090233
sanich
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sanich
Участник
miksoftНе очень понял ваши эксперименты с группировкой.
Почему не сделать проще:
Код: sql
1.
2.
3.
SELECT MIN(id), ekey
FROM email_subscribe
GROUP BY ekey

?

Этот запрос выводит список записей в которых уникально поле ekey, а надо не уникальные записи а вытатащить все дубликаты по полю ekey...
...
Рейтинг: 0 / 0
29.10.2015, 15:56:29
| Ответить | Цитировать | Написать  
Оптимизация запроса с большим объемом данных
    #39090248
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
sanichmiksoftНе очень понял ваши эксперименты с группировкой.
Почему не сделать проще:
Код: sql
1.
2.
3.
SELECT MIN(id), ekey
FROM email_subscribe
GROUP BY ekey


?

Этот запрос выводит список записей в которых уникально поле ekey, а надо не уникальные записи а вытатащить все дубликаты по полю ekey...Вообще-то нет. Этот запрос выводит записи со всеми значениями поля ekey и с одним значением поля id. Т.е. по сути это то, что осталось бы после удаления дубликатов.

Чтобу получить значения поля ekey, которые имеют дубликаты в таблице, то нужен такой запрос:
Код: sql
1.
2.
3.
4.
SELECT ekey
FROM email_subscribe
GROUP BY ekey
HAVING count(*)>1
...
Рейтинг: 0 / 0
29.10.2015, 16:06:24
| Ответить | Цитировать | Написать  
Оптимизация запроса с большим объемом данных
    #39090377
Фотография javajdbc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
javajdbc
Участник
sanichДобрый день.

Прошу помощи у профессионалов по ускорению запроса для Mysql
Есть таблица email_subscribe с набором значений:

Код: sql
1.
2.
3.
4.
5.
6.
id      ekey
1       ключ1
2       ключ1
3       ключ2
4       ключ1
5       ключ1




Необходимо получить дубликаты по полю "ekey", которые в дальнейшем можно удалить

Сделал 2 запроса, они выводят, список нужных дублей по полю "ekey", которые можно потом удалить...

Код: sql
1.
2.
3.
SELECT t.* FROM email_subscribe t  
LEFT JOIN (SELECT id FROM email_subscribe GROUP BY ekey) as t2 ON t.id=t2.id  
WHERE t2.id IS NULL




Код: sql
1.
2.
select * from email_subscribe 
where id not in (SELECT id FROM email_subscribe GROUP BY ekey)




Результат двух запросов правильный и идентичный:
Код: sql
1.
2.
3.
4.
id      ekey
2       ключ1
4       ключ1
5       ключ1





оба запроса неверные и случайно идентичные
...
Рейтинг: 0 / 0
29.10.2015, 17:37:15
| Ответить | Цитировать | Написать  
Оптимизация запроса с большим объемом данных
    #39090388
Фотография javajdbc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
javajdbc
Участник
sanich,

еше раз уточните задачу. если , допустим имеется две
записис одинаковым ЕКЕУ. вам нужно:
1. выбрать обе
2. выбрать последнюю (первую) по какомунибудь аттрибуту
3. выбрать любую

точно также уточните про удаление. Надо оставить:
1. ОДНУ последнюю (первую) по ИД или таймстампу или по какомунибудь статусу
2. ОДНУ любую из нескольких
...
Рейтинг: 0 / 0
29.10.2015, 17:42:27
| Ответить | Цитировать | Написать  
Оптимизация запроса с большим объемом данных
    #39090519
sanich
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sanich
Участник
javajdbcsanich,

еше раз уточните задачу. если , допустим имеется две
записис одинаковым ЕКЕУ. вам нужно:
1. выбрать обе
2. выбрать последнюю (первую) по какомунибудь аттрибуту
3. выбрать любую

точно также уточните про удаление. Надо оставить:
1. ОДНУ последнюю (первую) по ИД или таймстампу или по какомунибудь статусу
2. ОДНУ любую из нескольких

Отвечаю:

еше раз уточните задачу. если , допустим имеется две
записис одинаковым ЕКЕУ. вам нужно:
1. выбрать обе
2. выбрать последнюю (первую) по какомунибудь аттрибуту
3. выбрать любую

точно также уточните про удаление. Надо оставить:
1. ОДНУ последнюю (первую) по ИД или таймстампу или по какомунибудь статусу
2. ОДНУ любую из нескольких


Постараюсь на пальцах, чтобы исключить недопонимание, есть таблица со значеними:
Код: sql
1.
2.
3.
4.
5.
6.
id      ekey
1       ключ1
2       ключ1
3       ключ2
4       ключ1
5       ключ1



Мне нужно выбрать только те строки, в которые повторяются по полю ekey, причем 1 дубликат не брать в расчет, т.е. мне надо получить:
Код: sql
1.
2.
3.
4.
id      ekey
2       ключ1
4       ключ1
5       ключ1



Запрос вида:

Код: sql
1.
2.
select * from email_subscribe 
where id not in (SELECT id FROM email_subscribe GROUP BY ekey)



Выводит, как раз то что мне надо, я не знаю почему javajdbc считает его неправильным, но этот запрос на большом объеме данных не выполняется.

Далее, miksoft привели запрос, он выполняется быстро:

Код: sql
1.
2.
3.
4.
SELECT ekey
FROM email_subscribe
GROUP BY ekey
HAVING count(*)>1



Но он выводит результат неверный:

Код: sql
1.
2.
id      ekey
1       ключ1


а должно быть:

Код: sql
1.
2.
3.
4.
id      ekey
2       ключ1
4       ключ1
5       ключ1
...
Рейтинг: 0 / 0
29.10.2015, 20:13:38
| Ответить | Цитировать | Написать  
Оптимизация запроса с большим объемом данных
    #39090532
Фотография Lumix
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Lumix
Участник
Код: sql
1.
2.
3.
4.
5.
select * from t 
    natural join 
        (select id from t 
            group by ekey   
            having count(*) > 1) x
...
Рейтинг: 0 / 0
29.10.2015, 20:32:31
| Ответить | Цитировать | Написать  
Оптимизация запроса с большим объемом данных
    #39090548
sanich
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sanich
Участник
Lumix
Код: sql
1.
2.
3.
4.
5.
select * from t 
    natural join 
        (select id from t 
            group by ekey   
            having count(*) > 1) x



результат:
Код: sql
1.
2.
id      ekey
1       ключ1



а должно быть:
Код: sql
1.
2.
3.
4.
id      ekey
2       ключ1
4       ключ1
5       ключ1
...
Рейтинг: 0 / 0
29.10.2015, 21:02:20
| Ответить | Цитировать | Написать  
Оптимизация запроса с большим объемом данных
    #39090571
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
Ну тогда, кажется, так:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
SELECT t1.id, t1.ekey
FROM
    email_subscribe t1
  LEFT JOIN 
    (SELECT min(id) min_id, ekey
    FROM email_subscribe
    GROUP BY ekey
    HAVING count(*)>1) t2
  ON t1.id=t2.min_id AND t1.ekey=t2.ekey
WHERE t2.min_id IS NULL

или так:
Код: sql
1.
2.
3.
SELECT t1.id, t1.ekey
FROM email_subscribe t1
WHERE EXISTS (SELECT NULL FROM email_subscribe t2 WHERE t1.id>t2.id AND t1.ekey=t2.ekey)
...
Рейтинг: 0 / 0
29.10.2015, 21:34:16
| Ответить | Цитировать | Написать  
Оптимизация запроса с большим объемом данных
    #39090576
Фотография Lumix
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Lumix
Участник
miksoft, я что-то не так всекаю или оба два запроса у него вроде как в самом исходном посте и прописаны...
...
Рейтинг: 0 / 0
29.10.2015, 21:47:24
| Ответить | Цитировать | Написать  
Оптимизация запроса с большим объемом данных
    #39090585
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
Lumixmiksoft, я что-то не так всекаю или оба два запроса у него вроде как в самом исходном посте и прописаны...Да, мой первый похож на первый запрос в исходном посте, только более корректно написан.
Это мы уже круг сделали и вернулись примерно туда же, где и были :)

Для быстрого выполнения моих вариантов нужен индекс (ekey, id).

Теоретически, самый быстрый вариант можно написать на переменных. Тогда можно будет обойтись одним сканированием индекса.
...
Рейтинг: 0 / 0
29.10.2015, 21:54:05
| Ответить | Цитировать | Написать  
Оптимизация запроса с большим объемом данных
    #39090595
Фотография Lumix
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Lumix
Участник
sanich,

вы поставили вопрос: как ускорить запрос.
На всякий случай сообщаю, как бы мы сделали в подобном случае, если бы стояла задача не ускорить запрос, а "*****, ну сделайте ЧТО-НИБУДЬ, чтобы было все быстро ну ваще"))))))

Шаг 1. Добавляем флаг.
Код: sql
1.
2.
alter table email_subsribe add column isMain bool not null;
alter table email_subscript add key _isDup (isMain);



Шаг 2. Маркируем его.
Код: sql
1.
2.
3.
4.
5.
update email_subscript a set isMain = 1 
    join (select * from email_subscript 
        group by ekey 
        having count(*) = 1
    ) b on a.id = b.id && a.isMain = 0



Шаг 3. Достаем дубли
Код: sql
1.
select * from email_subscript where isMain = 0;
...
Рейтинг: 0 / 0
29.10.2015, 22:22:14
| Ответить | Цитировать | Написать  
Оптимизация запроса с большим объемом данных
    #39090632
Фотография javajdbc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
javajdbc
Участник
sanichВыводит, как раз то что мне надо, я не знаю почему javajdbc считает его неправильным



признаю что был не прав --- уж очень редко
задача стоит в оставление любой
из повторяюшихся записей. Обычно всеже ставят условие
первый/последний.

Кстате, под любой можно принять "сохранить первую",
тогда так:

Код: sql
1.
2.
3.
4.
5.
select distinct t1.id
from t t1
left join t t2 
on t1.ekey = t2.ekey and t1.id > t2.id
where t2.id is null
...
Рейтинг: 0 / 0
29.10.2015, 23:45:40
| Ответить | Цитировать | Написать  
Оптимизация запроса с большим объемом данных
    #39090644
Фотография Lumix
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Lumix
Участник
javajdbc
Код: sql
1.
2.
3.
4.
5.
select distinct t1.id
from t t1
left join t t2 
on t1.ekey = t2.ekey and t1.id > t2.id
where t2.id is null



javajdbc, чисто ради интереса: а для чего в этом запросе distinct? это такой трюк для левых джоинов, да???
...
Рейтинг: 0 / 0
30.10.2015, 00:03:43
| Ответить | Цитировать | Написать  
Оптимизация запроса с большим объемом данных
    #39090645
Фотография javajdbc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
javajdbc
Участник
Lumixjavajdbc
Код: sql
1.
2.
3.
4.
5.
select distinct t1.id
from t t1
left join t t2 
on t1.ekey = t2.ekey and t1.id > t2.id
where t2.id is null




javajdbc, чисто ради интереса: а для чего в этом запросе distinct? это такой трюк для левых джоинов, да???


если на один ЕКЕУ имеется 3 ид id1, id2, id3 то последний выйдет 2 раза
по условию id3 > id1, id3>id2
...
Рейтинг: 0 / 0
30.10.2015, 00:10:52
| Ответить | Цитировать | Написать  
Оптимизация запроса с большим объемом данных
    #39090650
Фотография Lumix
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Lumix
Участник
javajdbcесли на один ЕКЕУ имеется 3 ид id1, id2, id3 то последний выйдет 2 раза
по условию id3 > id1, id3>id2

получается, что с помощью условий мы что ли как-то можем влиять на дисинкт и "загнать" его так, чтобы он отдавал либо максимум, либо минимум?? я просто всегда думал, что дистинкт всегда берет первое попавшееся, то есть как бы его выбор это всегда not-determined
...
Рейтинг: 0 / 0
30.10.2015, 00:31:06
| Ответить | Цитировать | Написать  
Оптимизация запроса с большим объемом данных
    #39090651
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
Lumixjavajdbcесли на один ЕКЕУ имеется 3 ид id1, id2, id3 то последний выйдет 2 раза
по условию id3 > id1, id3>id2

получается, что с помощью условий мы что ли как-то можем влиять на дисинкт и "загнать" его так, чтобы он отдавал либо максимум, либо минимум?? я просто всегда думал, что дистинкт всегда берет первое попавшееся, то есть как бы его выбор это всегда not-determinedЭто вы с GROUP BY путаете. А DISTINCT всегда детерминистичен.
...
Рейтинг: 0 / 0
30.10.2015, 00:38:18
| Ответить | Цитировать | Написать  
Оптимизация запроса с большим объемом данных
    #39090680
tanglir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tanglir
Участник
Lumixполучается, что с помощью условий мы что ли как-то можем влиять на дисинкт и "загнать" его так, чтобы он отдавал либо максимум, либо минимум??Нет, distinct просто уберёт повторы id-ов для тех ekey, у которых больше двух записей. Никакими условиями на него "повлиять" не получится.

miksoftЭто вы с GROUP BY путаете. А DISTINCT всегда детерминистичен. А вот тут уже я не понял. Вроде бы distinct on() order by ... в мускле ещё нет и не предвидится. Или о чём вообще речь?
...
Рейтинг: 0 / 0
30.10.2015, 06:01:06
| Ответить | Цитировать | Написать  
Оптимизация запроса с большим объемом данных
    #39090805
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
tanglirmiksoftЭто вы с GROUP BY путаете. А DISTINCT всегда детерминистичен. А вот тут уже я не понял. Вроде бы distinct on() order by ... в мускле ещё нет и не предвидится. Или о чём вообще речь?Именно о том, что "Никакими условиями на него "повлиять" не получится".

Т.е., если на входе такой набор:
Код: sql
1.
2.
3.
4.
5.
6.
id      ekey
1       ключ1
2       ключ1
3       ключ2
4       ключ1
5       ключ1

То на выходе SELECT DISTINCT id, ekey будет этот же самый набор. С дублями в поле ekey ничего не произойдет, они как были, так и останутся.
А на выходе SELECT DISTINCT ekey будет такой набор:
Код: sql
1.
2.
3.
ekey
ключ1
ключ2

без всякой зависимости от того, что там было в поле id.
...
Рейтинг: 0 / 0
30.10.2015, 10:04:14
| Ответить | Цитировать | Написать  
Оптимизация запроса с большим объемом данных
    #39096724
sanich
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sanich
Участник
miksoftНу тогда, кажется, так:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
SELECT t1.id, t1.ekey
FROM
    email_subscribe t1
  LEFT JOIN 
    (SELECT min(id) min_id, ekey
    FROM email_subscribe
    GROUP BY ekey
    HAVING count(*)>1) t2
  ON t1.id=t2.min_id AND t1.ekey=t2.ekey
WHERE t2.min_id IS NULL

или так:
Код: sql
1.
2.
3.
SELECT t1.id, t1.ekey
FROM email_subscribe t1
WHERE EXISTS (SELECT NULL FROM email_subscribe t2 WHERE t1.id>t2.id AND t1.ekey=t2.ekey)



Второй запрос

Код: sql
1.
2.
3.
SELECT t1.id, t1.ekey
FROM email_subscribe t1
WHERE EXISTS (SELECT NULL FROM email_subscribe t2 WHERE t1.id>t2.id AND t1.ekey=t2.ekey)



оказался правильным и быстро выполняется, задача решена, спасибо всем и в частности miksoft!!!!
...
Рейтинг: 0 / 0
06.11.2015, 15:19:42
| Ответить | Цитировать | Написать  
27 сообщений из 27, показаны все 2 страниц
  все  
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Оптимизация запроса с большим объемом данных
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/search_topic.php?author=Galaxy&author_mode=last_posts&do_search=1]:
 0ms
get settings:
 5ms
get forum list:
 12ms
get settings:
 6ms
get forum list:
 8ms
get settings:
 8ms
get forum list:
 14ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 58ms
get topic data:
 10ms
get forum data:
 2ms
get page messages:
 45ms
get tp. blocked users:
 1ms
others: 1091ms
total:  1268ms
созд. / загр.: 1268ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]