Мифы SQL. Продолжение.
Недавно опубликованная на хабре статья Мифы SQL хабаролюдям понравилась. И многие попросили привести примеры почему все работает именно так и не иначе.
Весь написанный код работает и проверен на
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 — Prod
PL/SQL Release 10.2.0.4.0 — Production
CORE 10.2.0.4.0 Production
TNS for 32-bit Windows: Version 10.2.0.4.0 — Production
NLSRTL Version 10.2.0.4.0 — Production
Executed in 0,094 seconds* This source code was highlighted with Source Code Highlighter.
Бесплатную версию Оракла можно взять тут.
К вопросу о переносимости кода. Посмотрите, попробуйте запустить и получить такие же зультаты на вашей любимой СУБД. Все вопросы о переносимости сразу отпадут.
Магический параметр где-нибудь глубоко в конфигах сервера fast = true
Можно взять и оптимизировать любой отдельный запрос, или даже часть запроса не трогая ничего кроме него
Сам вопрос что именно надо сделать чтобы ускорить работу БД не имеет смысла.
Как правило надо пересматривать структуру таблиц, переписывать запросы, переделывать индексы. Нужен целый комплекс мер. Нет никаких магических действий.
Понять как работает запрос и улучшить его можно просто посмотрев на код запроса
Покажите мне план запроса и тогда можно начинать думать. Без плана вообще ничего нельзя сказать про оптимальность работы.
Во временных таблицах не надо делать ни Primary Key, ни индексов
Временные таблицы ни чем не хуже обычных. Если мы используем временную таблицу чтобы запустить по ней какой-либо алгоритм, то действительно индексы будут только вредить. Но бывает что временная таблица используется как самая обычная, то есть мы кладем в нее данные потом делаем к ней много разных запросов. В этом случае PK и индексы сильно облегчат жизнь.
Внешний ключ = Индекс по полю в дочерней таблице
Очень хорошая статья на эту тему
Основные выводы оттуда:
Проблемы, которые могут возникнуть при наличии неиндексированных внешних ключей. • большая вероятность возникновения взаимных блокировок. • при удалении из основной таблицы, если установлено ограничение on delete cascade, вызывается каскадное удаление из починенной таблицы. И если нет индекса на внешний ключ, то Оракл вынужден делать полный просмотр починенной таблицы. А это не есть хорошо - требуются большие ресурсы и значительные расходы времени. • для запроса из главной таблицы в починенную, при наличии в конструкции where условия на ключ, будет значительное замедление работы. Можно не беспокоиться о наличии индекса на внешний ключ при следующих условиях: • никогда записи из главной таблицы не удаляются. • Никогда не изменяется значение уникального, первичного ключа. • Никогда в запросах главная и подчиненная таблица не соединяются.
Дополнение от меня к пункту «Никогда в запросах главная и починенная таблица не соединяются.»
Имеются в виду только запросы вида
SELECT *
FROM table1 t1
INNER JOIN table2 t2 ON t1.id = t2.id* This source code was highlighted with Source Code Highlighter.
Если у нас все запросы вида
SELECT *
FROM table1 t1
INNER JOIN table2 t2 ON t1.id = t2.id
WHERE t1.code = 'A'
AND t2.code = ‘B’* This source code was highlighted with Source Code Highlighter.
То будет гораздо эффективнее создать составной индекс (id, code) и автоматически созданный (id) будет только мешать.
Запрос с меньшей стоимостью обязан работать быстрее
Лучше Тома Кайта я не напишу.
Первая ссылка
Вторая ссылка
По ссылкам лежит одно и тоже, на всякий случай если вдруг один из серверов исчезнет.
Абсолютная идентичность следующих вариантов кода
Просто запустим этот код.
— Create table
create table TABLE1
(
ID NUMBER(13) not null
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
insert into table1
select rownum
from dual
connect by level < 100000
SQL> declare
2 summ number(10);
3 i number(10);
4 cursor c is select id from table1;
5 begin
6 summ := 0;
7 open c;
8
9 loop
10 fetch c into i;
11 exit when c%notfound;
12
13 summ := summ + i;
14 end loop;
15
16 close c;
17
18 dbms_output.put_line(summ);
19
20 end;
21 /
PL/SQL procedure successfully completed
Executed in 1,515 seconds
SQL> select sum(id) from table1;
SUM(ID)
----------
4999950000
Executed in 0,031 seconds* This source code was highlighted with Source Code Highlighter.
Разница в 50 раз.
Любое обращение по индексу лучше чем без него
Для запросов, которые возвращают большую часть таблицы доступ по индексу будет медленнее полного сканирования таблицы.
Это подверждается на простеньком примере:
— Create table
create table TABLE1
(
ID NUMBER(13) not null
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
— Create/Recreate indexes
create index IX_TABLE1 on TABLE1 (ID)
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
insert into table1
select rownum
from dual
connect by level < 1000000;
SQL> select /*+ index(table1 IX_TABLE1) */
2 COUNT(*) from table1;
COUNT(*)
----------
1099998
Executed in 0,172 seconds
SQL>
SQL> select /*+ no_index(table1 IX_TABLE1) */
2 COUNT(*) from table1;
COUNT(*)
----------
1099998
Executed in 0,063 seconds* This source code was highlighted with Source Code Highlighter.
Разница в 2.7 раза.
Запрос работает медленно, надо добавить индекс
Добавление индексов помогает, но это не панацея. В какой-то момент INSERT и UPDATE начнут работать очень медленно из-за большого числа индексов, а изменить что-либо будет уже очень сложно. Придется переделывать большую часть запросов. Лучше сразу писать так, чтобы такая ситуация не возникла.
Скорость соединения таблиц сильно зависит от типов и количества полей по которым мы их соединяем
Проверим скорость соединения таблиц по id и тех же самых таблиц по паре текстовых полей.
Создаем таблицы
— Create table
create table TABLE1
(
ID NUMBER(13) not null,
CODEA VARCHAR2(1024) not null,
CODEB VARCHAR2(1024) not null
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
— Create/Recreate indexes
create index IX_TABLE1 on TABLE1 (ID)
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create index IX1_TABLE1 on TABLE1 (CODEA, CODEB)
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
— Create table
create table TABLE2
(
ID NUMBER(13) not null,
CODEA VARCHAR2(1024) not null,
CODEB VARCHAR2(1024) not null
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
— Create/Recreate indexes
create index IX_TABLE2 on TABLE2 (ID)
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create index IX2_TABLE2 on TABLE2 (CODEA, CODEB)
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);* This source code was highlighted with Source Code Highlighter.
Заполняем
insert into table1
select rownum, rownum, rownum
from dual
connect by level < 100000;
insert into table2
select rownum, rownum, rownum
from dual
connect by level < 100000;* This source code was highlighted with Source Code Highlighter.
Проверяем
SQL>
SQL> select count(*)
2 from table1 t1
3 INNER JOIN table2 t2 ON t2.codea = t1.codea
4 AND t2.codeb = t2.codeb;
COUNT(*)
----------
99999
Executed in 0,078 seconds
SQL>
SQL> select count(*)
2 from table1 t1
3 INNER JOIN table2 t2 ON t2.id = t1.id;
COUNT(*)
----------
99999
Executed in 0,079 seconds* This source code was highlighted with Source Code Highlighter.
Разброс значений в пределах погрешности.
При запросе с фильтром по 2 полям работают оба индекса
Том Кайт писал про то когда используется несколько индексов.
Первая ссылка
Вторая ссылка
Скорость работы запроса оценивается по тому как быстро вывелись первые 20 строк
Для проверки этого мифа нужно довольно сложное окружение. Более простой пример приветствуется в коментариях.
Создаем таблицы:
— Create table
create table TABLE_SMALL
(
ID NUMBER(13) not null,
D_BEG DATE not null,
D_END DATE not null
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
— Create/Recreate primary, unique and foreign key constraints
alter table TABLE_SMALL
add constraint PK_SMALL primary key (ID)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
— Create/Recreate indexes
create index IX_SMALL on TABLE_SMALL (D_BEG, D_END)
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
— Create table
create table TABLE_BIG
(
ID NUMBER(13) not null,
D DATE not null
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
— Create/Recreate primary, unique and foreign key constraints
alter table TABLE_BIG
add constraint PK_BIG primary key (ID)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
— Create/Recreate indexes
create index IX_BIG on TABLE_BIG (D)
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);* This source code was highlighted with Source Code Highlighter.
Заполняем таблицы
insert into table_big t
(
SELECT rownum,
TO_DATE('01.01.2000','dd.mm.yyyy') + (rownum — 1) / 24 AS thour
FROM dual
CONNECT BY LEVEL <=(TO_DATE('01.03.2000','dd.mm.yyyy') — TO_DATE('01.01.2000','dd.mm.yyyy')) * 24
);
insert into table_small
values (1, TO_DATE('01.01.2000 01:00', 'dd.mm.yyyy HH24:MI'), TO_DATE('02.01.2000 01:00', 'dd.mm.yyyy HH24:MI'));
insert into table_small
values (2, TO_DATE('01.01.2000 01:00', 'dd.mm.yyyy HH24:MI'), TO_DATE('02.01.2000 01:00', 'dd.mm.yyyy HH24:MI'));
insert into table_small
values (3, TO_DATE('01.01.2000 01:00', 'dd.mm.yyyy HH24:MI'), TO_DATE('02.01.2000 01:00', 'dd.mm.yyyy HH24:MI'));
insert into table_small
values (4, TO_DATE('01.01.2000 01:00', 'dd.mm.yyyy HH24:MI'), TO_DATE('02.01.2000 01:00', 'dd.mm.yyyy HH24:MI'));
insert into table_small
values (5, TO_DATE('01.01.2000 01:00', 'dd.mm.yyyy HH24:MI'), TO_DATE('02.01.2000 01:00', 'dd.mm.yyyy HH24:MI'));
insert into table_small
values (6, TO_DATE('01.01.2000 01:00', 'dd.mm.yyyy HH24:MI'), TO_DATE('02.01.2000 01:00', 'dd.mm.yyyy HH24:MI'));
insert into table_small
values (7, TO_DATE('01.01.2000 01:00', 'dd.mm.yyyy HH24:MI'), TO_DATE('02.01.2000 01:00', 'dd.mm.yyyy HH24:MI'));
insert into table_small
values (8, TO_DATE('01.01.2000 01:00', 'dd.mm.yyyy HH24:MI'), TO_DATE('02.01.2000 01:00', 'dd.mm.yyyy HH24:MI'));
insert into table_small
values (9, TO_DATE('01.01.2000 01:00', 'dd.mm.yyyy HH24:MI'), TO_DATE('02.01.2000 01:00', 'dd.mm.yyyy HH24:MI'));
insert into table_small
values (10, TO_DATE('01.01.2000 01:00', 'dd.mm.yyyy HH24:MI'), TO_DATE('02.01.2000 01:00', 'dd.mm.yyyy HH24:MI'));
insert into table_small
values (11, TO_DATE('01.01.2000 01:00', 'dd.mm.yyyy HH24:MI'), TO_DATE('02.01.2000 01:00', 'dd.mm.yyyy HH24:MI'));
insert into table_small
values (12, TO_DATE('01.01.2000 01:00', 'dd.mm.yyyy HH24:MI'), TO_DATE('02.01.2000 01:00', 'dd.mm.yyyy HH24:MI'));
* This source code was highlighted with Source Code Highlighter.
Создаем типы и функции
create or replace type t_data as object
(
id number(13),
d date
);
create or replace type t_data_table as table of t_data;
create or replace function expand_small return t_data_table
pipelined
is
begin
FOR v IN (
SELECT
s.id AS id,
hours.thour AS tdate
FROM
(
SELECT TO_DATE('01.01.2000', 'dd.mm.yyyy') + (rownum — 1) / 24 AS thour
FROM dual
CONNECT BY LEVEL <= 10 * 24 — 10 дней
) hours
INNER JOIN table_small s ON s.d_beg BETWEEN TO_DATE('01.01.2000', 'dd.mm.yyyy') AND TO_DATE('10.01.2000', 'dd.mm.yyyy')
OR s.d_beg BETWEEN TO_DATE('01.01.2000', 'dd.mm.yyyy') AND TO_DATE('10.01.2000', 'dd.mm.yyyy')
OR s.d_beg < TO_DATE('01.01.2000', 'dd.mm.yyyy') AND s.d_end > TO_DATE('10.01.2000', 'dd.mm.yyyy')
)
LOOP
PIPE ROW(t_data(v.id, v.tdate));
END LOOP;
RETURN;
end expand_small;
create or replace function expand_big return t_data_table
pipelined
is
begin
FOR v IN (
SELECT
b.id,
b.d as tdate
FROM table_big b
WHERE
b.d BETWEEN TO_DATE('01.01.2000', 'dd.mm.yyyy') AND TO_DATE('10.01.2000', 'dd.mm.yyyy')
)
LOOP
PIPE ROW(t_data(v.id, TO_DATE(v.tdate)));
END LOOP;
RETURN;
end expand_big;* This source code was highlighted with Source Code Highlighter.
Проверяем.
Нормальное поведение, на которое часто закладываются.
SQL>
SQL> select COUNT(*) from table_big
2 where rownum < 10;
COUNT(*)
----------
9
Executed in 0,015 seconds
SQL>
SQL> select COUNT(*) from table_big
2 where rownum < 200;
COUNT(*)
----------
199
Executed in 0,016 seconds
* This source code was highlighted with Source Code Highlighter.
COUNT(*) стоит чтобы исключить время необходимое на передачу данных на клиент и на отрисовку.
Время получения первых 10 строк и первых 200 строк отличается незначительно. И таких запросов большинство.
Но иногда бывает и так
SQL>
SQL> select COUNT(*) from table(expand_big) t
2 where t.id not in
3 (
4 SELECT t1.id from table(expand_small) t1
5 )
6 and rownum < 10;
COUNT(*)
----------
9
Executed in 0,578 seconds
SQL>
SQL> select COUNT(*) from table(expand_big) t
2 where t.id not in
3 (
4 SELECT t1.id from table(expand_small) t1
5 )
6 and rownum < 200;
COUNT(*)
----------
199
Executed in 12,063 seconds* This source code was highlighted with Source Code Highlighter.
Время отличается в 24 раза. И это не исключительный случай. Такое часто бывает при сложных запросах.