Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Оптимизация запроса с большим объемом данных / 25 сообщений из 27, страница 1 из 2
  1  все
27.10.2015, 20:05:17
    #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:10:37
    #39088417
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса с большим объемом данных
sanich,

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

?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.10.2015, 20:41:58
    #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:56:07
    #39088431
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса с большим объемом данных
sanichНеобходимо получить дубликаты по полю "ekey", которые в дальнейшем можно удалить
Давайте всё-таки без допущений. Либо мы их удаляем (тогда нафига их получать), либо получаем (тогда к чему разговор за удаление).
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.10.2015, 09:50:03
    #39088659
DBConstructor
DBConstructor
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса с большим объемом данных
sanich, хочется задать уточняющий вопрос: Зачем эти дубли туда добавлять? Bad design?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
29.10.2015, 15:26:01
    #39090192
sanich
sanich
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса с большим объемом данных
DBConstructorsanich, хочется задать уточняющий вопрос: Зачем эти дубли туда добавлять? Bad design?

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

Надо получить, для того чтобы в дальнейшем обработать эти записи, есть другая таблица где похожая ситуация с дублями и там нужно просто удалить дубликаты
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
29.10.2015, 15:56:29
    #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, 16:06:24
    #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, 17:37:15
    #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:42:27
    #39090388
javajdbc
javajdbc
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса с большим объемом данных
sanich,

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

точно также уточните про удаление. Надо оставить:
1. ОДНУ последнюю (первую) по ИД или таймстампу или по какомунибудь статусу
2. ОДНУ любую из нескольких
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
29.10.2015, 20:13:38
    #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:32:31
    #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, 21:02:20
    #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:34:16
    #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:47:24
    #39090576
Lumix
Lumix
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса с большим объемом данных
miksoft, я что-то не так всекаю или оба два запроса у него вроде как в самом исходном посте и прописаны...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
29.10.2015, 21:54:05
    #39090585
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса с большим объемом данных
Lumixmiksoft, я что-то не так всекаю или оба два запроса у него вроде как в самом исходном посте и прописаны...Да, мой первый похож на первый запрос в исходном посте, только более корректно написан.
Это мы уже круг сделали и вернулись примерно туда же, где и были :)

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

Теоретически, самый быстрый вариант можно написать на переменных. Тогда можно будет обойтись одним сканированием индекса.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
29.10.2015, 22:22:14
    #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, 23:45:40
    #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
| Ответить | Цитировать | Написать  
30.10.2015, 00:03:43
    #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:10:52
    #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:31:06
    #39090650
Lumix
Lumix
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса с большим объемом данных
javajdbcесли на один ЕКЕУ имеется 3 ид id1, id2, id3 то последний выйдет 2 раза
по условию id3 > id1, id3>id2

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

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

miksoftЭто вы с GROUP BY путаете. А DISTINCT всегда детерминистичен. А вот тут уже я не понял. Вроде бы distinct on() order by ... в мускле ещё нет и не предвидится. Или о чём вообще речь?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
  1  все
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Оптимизация запроса с большим объемом данных / 25 сообщений из 27, страница 1 из 2
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 347ms
Закрыть
start [/forum/topic.php?fid=47&mobile=1&tid=1832532]:
 0ms
get settings:
 9ms
get forum list:
 17ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 44ms
get topic data:
 9ms
get forum data:
 3ms
get page messages:
 53ms
get tp. blocked users:
 1ms
others: 203ms
total:  347ms
x
x
Закрыть


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