Производительность SQL

 
+
-
edit
 

digger

опытный

Запрос примерно такой
select ID from table1 where size < 10000 and ID not in (select ID from table2)

Обе таблицы большие,около 10 миллионов.Очень тормозит.Что мне надо логически? Пройтись по table1 и выбрать набор ID,затем для каждого ID проверить,что он не в table2,где он является также индексом.Тормозить вроде нечему.В чем причина тормозов и можно ли ускорить? Я подозреваю,что select ID from table2 формирует временный рекордсет огромного размера вместо того,чтобы просто смотреть в таблицу.
 34.034.0
+
-
edit
 

Luchnik

аксакал
★☆
А если так :
select ID from table1 t1 where t1.size < 10000
and not exists (select t2.ID from table2 t2 where t2.ID = t1.ID)

?

Или

select distinct t1.ID from table1 t1
left join table2 t2 on t2.ID = t1.ID
where t1.size < 10000 and t2.ID is null
 

yacc

старожил
★☆

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

yacc

старожил
★☆

Distinct - зло.
Особенно на больших таблицах - тормоза с tempdb гарантированы.
 

Akuma

втянувшийся

yacc> Distinct - зло.
yacc> Особенно на больших таблицах - тормоза с tempdb гарантированы.

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

Luchnik

аксакал
★☆
yacc> Distinct - зло.
yacc> Особенно на больших таблицах - тормоза с tempdb гарантированы.

Я согласен.
Но если у него всё упирается в память, то может оказаться лучше оригинального варианта.
Кроме того, возможно, что по логике построения таблиц Distinct и не нужен. Если 1 к 1-му.
 

yacc

старожил
★☆

Akuma> Не зло, конечно
С точки зрения плана - зло.
Просто когда не критично можно использовать.
 

Luchnik

аксакал
★☆
Akuma> Не зло, конечно, если использовать его тогда, когда требуется. А в этом случае он вообще не в кассу - ID, скорее всего, является первичным ключом и его уникальность гарантирована.

Согласен, согласен. Но тут мы точно не знаем - см. мой ответ yacc.
 

yacc

старожил
★☆

Luchnik> Но если у него всё упирается в память, то может оказаться лучше оригинального варианта.
На МS-SQL это с гарантией tempdb независимо от памяти :)
И скорость уже будет определятся скоростью винчестера
 

Luchnik

аксакал
★☆
Luchnik>> Но если у него всё упирается в память, то может оказаться лучше оригинального варианта.
yacc> На МS-SQL это с гарантией tempdb независимо от памяти :)
yacc> И скорость уже будет определятся скоростью винчестера

Я готов рассмотреть твой вариант. :)
 

yacc

старожил
★☆

Luchnik> Я готов рассмотреть твой вариант. :)
Детали надо.
Ключи и индексы.
 

Akuma

втянувшийся

Luchnik> Кроме того, возможно, что по логике построения таблиц Distinct и не нужен. Если 1 к 1-му.

Даже если 1 ко многим - нам по условию задачи нужно выбрать t1.ID, для которых нет соответствия t2.ID.
 34.034.0

Akuma

втянувшийся

yacc> Детали надо.
yacc> Ключи и индексы.

Для начала нужно узнать, какой сервер БД.
 34.034.0

16-й

опытный
★☆
Luchnik> select ID from table1 t1 where t1.size < 10000
Luchnik> and not exists (select t2.ID from table2 t2 where t2.ID = t1.ID)

Это первое, что стоит попробовать. Самое простое.
А вообще полезно знать корреляцию t1 и t2.
Если она низкая и есть индексы по id обеих таблиц, то:

select t.id
from
(select t1.ID from table1 t1
inner join table2 t2
on t2.ID = t1.ID) t inner join table1 tt on t.id = tt.id
where tt.size < 10000

Внутренний запрос здесь он сделает по merge join, т.к. оба входа у него отсортированы, на выходе будет немного записей, которые добьет по size. Это микрософак как я понимаю? На оракле я б внутри написал select t1.ROWID, а на MS SQL не помню, если индекс по id кластерный то смысла нет.
 

yacc

старожил
★☆

16-й> Если она низкая и есть индексы по id обеих таблиц, то:
Там NOT IN стоит - в этом-то и прикол :)
 

16-й

опытный
★☆
yacc> Там NOT IN стоит - в этом-то и прикол :)

Посыпаю чувствительные места, и точно, cтоит :)

Значит захинтовать его, гада, каким-нибудь hash anti-join, чтоб он за каждой записью не бегал в подзапрос.
 
Это сообщение редактировалось 11.12.2014 в 17:19
+
-
edit
 

Balancer

администратор
★★★★☆
digger> select ID from table1 where size < 10000 and ID not in (select ID from table2)

MySQL очень плохо работает с вложенными запросами такого вида. По крайней мере раньше плохо работал. Нужно пользоваться JOIN'ами, типа такого:

code mysql
  1. SELECT t1.ID FROM table1 t1
  2.     LEFT JOIN table2 t2 ON t1.ID = t2.ID
  3.     WHERE size < 10000
  4.         AND t2.ID IS NULL


Ну и, понятно, должны быть индексы по ID, по size, а для table1, может, и составной ID, size.
 3838
RU Balancer #11.12.2014 17:21  @Balancer#11.12.2014 17:18
+
-
edit
 

Balancer

администратор
★★★★☆
Кстати, вложенные SELECT'ы в MySQL работают (опять же, раньше так было, говорят, что в 5.6 и старше много оптимизировали на этот счёт, но я пока не проверял) настолько отвратительно, что при сложных случаях выгодно сделать временную таблицу из результата [бывшего вложенного] запроса, добавить к ней нужные индексы и уже её джойнить к нашему запросу вместо вложенного. Разница в производительности получается на порядки.
 3838

yacc

старожил
★☆

16-й> Значит захинтовать его, гада, каким-нибудь hash anti-join, чтоб он за каждой записью не бегал в подзапрос.
На MS-SQL с хинтами плоховато :)
там бы я использовал времянку, что-то типа такого

CREATE TABLE #t ( ID int, CONSTRAINT [#IQ] UNIQUE ( ID ) )

INSERT #t
SELECT ID FROM table1 WHERE size < 10000

DELETE t1
FROM #t t1
INNER JOIN table2 t2 ON t1.ID = t2.ID

после этого в #t - искомый набор ID
 

16-й

опытный
★☆
yacc> На MS-SQL с хинтами плоховато :)
yacc> там бы я использовал времянку, что-то типа такого
yacc> CREATE TABLE #t ( ID int, CONSTRAINT [#IQ] UNIQUE ( ID ) )
yacc> INSERT #t
yacc> SELECT ID FROM table1 WHERE size < 10000
yacc> DELETE t1
yacc> FROM #t t1
yacc> INNER JOIN table2 t2 ON t1.ID = t2.ID
yacc> после этого в #t - искомый набор ID

Не, ну я настаиваю! Это ж природная редкость, когда два входа с покрывающими индексами под merge.

select t.id
from
(select t1.ID, t2.ID AS id_table2 from table1 t1
left join table2 t2
on t2.ID = t1.ID) t inner join table1 tt on t.id = tt.id
where t.id_table2 IS NULL AND tt.size < 10000
 

yacc

старожил
★☆

16-й> Не, ну я настаиваю!
А я просто покажу - раз

select *
from table1 t1
where t1.size > 3
and t1.Id not in ( select Id from table2 )

/*
(14682 row(s) affected)
Table 'table1'. Scan count 1, logical reads 164, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'table2'. Scan count 1, logical reads 109, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
*/
Прикреплённые файлы:
q1.PNG (скачать) [20,88 кбайт, 3 загрузки] [attach=421203]
 
 
 

yacc

старожил
★☆

то, что предлагал 16-й - два -
select t.id
from
(select t1.ID, t2.ID AS id_table2 from table1 t1
left join table2 t2
on t2.ID = t1.ID) t inner join table1 tt on t.id = tt.id
where t.id_table2 IS NULL AND tt.size > 3

/*
(14682 row(s) affected)
Table 'table1'. Scan count 2, logical reads 328, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'table2'. Scan count 1, logical reads 109, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
*/
Прикреплённые файлы:
q2.PNG (скачать) [28,18 кбайт, 5 загрузок] [attach=421204]
 
 
 

yacc

старожил
★☆

три
select *
from table1 t1
where t1.size > 3
and not exists ( select 1 from table2 t2 where t2.Id = t1.Id )

/*
(14682 row(s) affected)
Table 'table1'. Scan count 1, logical reads 164, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'table2'. Scan count 1, logical reads 109, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)
*/
Прикреплённые файлы:
q3.PNG (скачать) [21,03 кбайт, 3 загрузки] [attach=421205]
 
 
 

yacc

старожил
★☆

С времянкой и апдейтом - четыре
CREATE TABLE #t ( ID int, CONSTRAINT t_Q UNIQUE ( ID ), exclude int default(0) )

INSERT #t ( Id )
SELECT ID FROM table1 WHERE size > 3

UPDATE t1
set exclude = 1
FROM #t t1
INNER JOIN table2 t2 ON t1.ID = t2.ID

SELECT * FROM #t WHERE exclude = 0
DROP TABLE #t

/*
Table '#t_______0000000000E6'. Scan count 0, logical reads 238547, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'table1'. Scan count 1, logical reads 164, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(79167 row(s) affected)

(1 row(s) affected)
Table '#t______0000000000E6'. Scan count 1, logical reads 64664, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'table2'. Scan count 1, logical reads 109, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(64485 row(s) affected)

(1 row(s) affected)

(14682 row(s) affected)
Table '#t_____0000000000E6'. Scan count 1, logical reads 167, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)
*/
Прикреплённые файлы:
q4.PNG (скачать) [40,29 кбайт, 4 загрузки] [attach=421207]
 
 
 

yacc

старожил
★☆

Дополнение - таблицы не особо большие и только кластерный индекс на Id
 

в начало страницы | новое
 
Поиск
Настройки
Твиттер сайта
Статистика
Рейтинг@Mail.ru