«Точный» SQL-запрос возвращает «чистые» данные в необходимом и достаточном количестве, при этом потребляет как можно меньше памяти и справляется за минимальное время. Скорость работы с базой влияет на производительность. Потребление памяти может негативно сказаться даже на безопасности. Всё это прямо и косвенно влияет на прибыль компании. В статье разберёмся, как не допускать ошибок.
Для наших целей понадобятся тестовые данные. Будем работать с базой данных Oracle Database. Примеры в статье будут приводиться на языке SQL, PL/SQL. Нам важен подход, который можно адаптировать под другую реляционную систему управления базами данных — РСУБД.
Тестовые данные
⚒ Создадим тестовую таблицу 1:
CREATE SEQUENCE TEST_DATA_1_SEQ
NOMAXVALUE
NOMINVALUE
NOCYCLE
/
CREATE TABLE TEST_DATA_1
(
TEST_DATA_1_ID NUMBER DEFAULT TEST_DATA_1_SEQ.NEXTVAL NOT NULL
,TYPE VARCHAR2(64) NOT NULL
,VALUE VARCHAR2(128) NOT NULL
,PC_USR VARCHAR2(30) DEFAULT USER NOT NULL
,PC_DT TIMESTAMP(6) DEFAULT SYSTIMESTAMP NOT NULL
)
/
ALTER TABLE TEST_DATA_1
ADD CONSTRAINT TEST_DATA_1_PK PRIMARY KEY (TEST_DATA_1_ID)
USING INDEX
/
ALTER TABLE TEST_DATA_1
ADD CONSTRAINT TEST_DATA_1_TYPE_CHK CHECK (TYPE in ('CITY', 'DATE', 'EMPLOYEE', 'STOCK MARKET'))
/
CREATE UNIQUE INDEX TEST_DATA_1_UIDX1 ON TEST_DATA_1 (VALUE)
/
COMMENT ON TABLE TEST_DATA_1 IS 'Тестовые данные 1'
/
⚒ Заполним тестовую таблицу 1 данными:
/* Добавление данных */
INSERT INTO TEST_DATA_1 (TYPE, VALUE) VALUES ('CITY', 'МОСКВА');
INSERT INTO TEST_DATA_1 (TYPE, VALUE) VALUES ('CITY', 'САНКТ-ПЕТЕРБУРГ');
INSERT INTO TEST_DATA_1 (TYPE, VALUE) VALUES ('EMPLOYEE', 'СОТРУДНИК 1. ПОЛ М. ВОЗРАСТ 18');
INSERT INTO TEST_DATA_1 (TYPE, VALUE) VALUES ('EMPLOYEE', 'СОТРУДНИК 2. ПОЛ Ж. ВОЗРАСТ 19');
INSERT INTO TEST_DATA_1 (TYPE, VALUE) VALUES ('EMPLOYEE', 'СОТРУДНИК 3. ПОЛ Ж. ВОЗРАСТ 20');
INSERT INTO TEST_DATA_1 (TYPE, VALUE) VALUES ('DATE', '01 января 2000');
INSERT INTO TEST_DATA_1 (TYPE, VALUE) VALUES ('DATE', '02 января 2000');
INSERT INTO TEST_DATA_1 (TYPE, VALUE) VALUES ('DATE', '01.01.2001');
INSERT INTO TEST_DATA_1 (TYPE, VALUE) VALUES ('DATE', '02.01.2001');
INSERT INTO TEST_DATA_1 (TYPE, VALUE) VALUES ('DATE', '03.01.2001');
INSERT INTO TEST_DATA_1 (TYPE, VALUE) VALUES ('DATE', '04.01.2001');
/* Извлечение всех данных */
SELECT t1.*
FROM TEST_DATA_1 t1;
/* Удаление всех данных без проверок */
TRUNCATE TABLE TEST_DATA_1;⚒ Создадим тестовую таблицу 2:
CREATE SEQUENCE TEST_DATA_2_SEQ
NOMAXVALUE
NOMINVALUE
NOCYCLE
/
CREATE TABLE TEST_DATA_2
(
TEST_DATA_2_ID NUMBER DEFAULT TEST_DATA_2_SEQ.NEXTVAL NOT NULL
,TEST_DATA_1_ID NUMBER NOT NULL
,TYPE VARCHAR2(64) NOT NULL
,VALUE VARCHAR2(128) NOT NULL
,PC_USR VARCHAR2(30) DEFAULT USER NOT NULL
,PC_DT TIMESTAMP(6) DEFAULT SYSTIMESTAMP NOT NULL
)
/
ALTER TABLE TEST_DATA_2
ADD CONSTRAINT TEST_DATA_2_PK PRIMARY KEY (TEST_DATA_2_ID)
USING INDEX
/
ALTER TABLE TEST_DATA_2
ADD CONSTRAINT TEST_DATA_2_TYPE_CHK CHECK (TYPE in ('STREET', 'DATE', 'EMPLOYEE', 'STOCK MARKET'))
/
CREATE UNIQUE INDEX TEST_DATA_2_UIDX1 ON TEST_DATA_2 (TEST_DATA_1_ID, VALUE)
/
COMMENT ON TABLE TEST_DATA_2 IS 'Тестовые данные 2'
/
⚒ Заполним тестовую таблицу 2 данными:
/* Добавление данных */
INSERT INTO TEST_DATA_2 (TEST_DATA_1_ID, TYPE, VALUE) VALUES (1 /*ID МОСКВА*/, 'STREET', 'УЛИЦА КАРЛА МАРКСА');
INSERT INTO TEST_DATA_2 (TEST_DATA_1_ID, TYPE, VALUE) VALUES (1 /*ID МОСКВА*/, 'STREET', 'УЛИЦА КРУПСКОЙ');
INSERT INTO TEST_DATA_2 (TEST_DATA_1_ID, TYPE, VALUE) VALUES (1 /*ID МОСКВА*/, 'STREET', 'МАЛЫЙ ПОЛУЯРОСЛАВСКИЙ ПЕРЕУЛОК');
INSERT INTO TEST_DATA_2 (TEST_DATA_1_ID, TYPE, VALUE) VALUES (2 /*ID САНКТ-ПЕТЕРБУРГ*/, 'STREET', 'УЛИЦА КАРЛА МАРКСА');
INSERT INTO TEST_DATA_2 (TEST_DATA_1_ID, TYPE, VALUE) VALUES (2 /*ID САНКТ-ПЕТЕРБУРГ*/, 'STREET', 'УЛИЦА КРУПСКОЙ');
INSERT INTO TEST_DATA_2 (TEST_DATA_1_ID, TYPE, VALUE) VALUES (3 /*ID СОТРУДНИК 1*/, 'EMPLOYEE', 'ПРОЖИВАЕТ В ГОРОДЕ МОСКВА');
INSERT INTO TEST_DATA_2 (TEST_DATA_1_ID, TYPE, VALUE) VALUES (4 /*ID СОТРУДНИК 2*/,'EMPLOYEE', 'ПРОЖИВАЕТ В ГОРОДЕ МОСКВА');
INSERT INTO TEST_DATA_2 (TEST_DATA_1_ID, TYPE, VALUE) VALUES (5 /*ID СОТРУДНИК 3*/,'EMPLOYEE', 'ПРОЖИВАЕТ В ГОРОДЕ САНКТ-ПЕТЕРБУРГ');
INSERT INTO TEST_DATA_2 (TEST_DATA_1_ID, TYPE, VALUE) VALUES (6 /*ID 01 января 2000*/, 'DATE', 'Формат день числом, месяц словом, год числом');
INSERT INTO TEST_DATA_2 (TEST_DATA_1_ID, TYPE, VALUE) VALUES (7 /*ID 02 января 2000*/, 'DATE', 'Формат день числом, месяц словом, год числом');
INSERT INTO TEST_DATA_2 (TEST_DATA_1_ID, TYPE, VALUE) VALUES (8 /*ID 01.01.2001*/, 'DATE', 'Формат день числом, месяц числом, год числом');
INSERT INTO TEST_DATA_2 (TEST_DATA_1_ID, TYPE, VALUE) VALUES (9 /*ID 02.01.2001*/, 'DATE', 'Формат день числом, месяц числом, год числом');
INSERT INTO TEST_DATA_2 (TEST_DATA_1_ID, TYPE, VALUE) VALUES (10 /*ID 03.01.2001*/, 'DATE', 'Формат день числом, месяц числом, год числом');
INSERT INTO TEST_DATA_2 (TEST_DATA_1_ID, TYPE, VALUE) VALUES (11 /*ID 04.01.2001*/, 'DATE', 'Формат день числом, месяц числом, год числом');
/* Извлечение всех данных */
SELECT t2.*
FROM TEST_DATA_2 t2;
/* Удаление всех данных без проверок */
TRUNCATE TABLE TEST_DATA_2;1. Объявляя имена таблиц, обращайся к записям через псевдонимы таблиц
Допустим, есть таблица с некоторым количество колонок. К ней можно обратиться двумя разными способами:
⚠️ Опасный подход:
SELECT TYPE
,VALUE
FROM TEST_DATA_1;
✅ Безопасный подход заключается в обращении через псевдоним:
SELECT t1.TYPE AS TYPE
,t1.VALUE AS VALUE
FROM TEST_DATA_1 t1;Псевдоним (анг. Alias) — это имя, назначенное источнику данных в SQL-запросе при использовании выражения в качестве источника данных или для упрощения ввода и прочтения инструкции SQL. Это полезно, если имя источника слишком длинное или его трудно вводить.
Псевдонимы можно использовать для переименования таблиц и колонок. В отличие от настоящих имён, они могут не соответствовать ограничениям базы данных и содержать до 255 знаков (включая пробелы, цифры и специальные символы).
В случае извлечения данных из одной таблицы без псевдонимов можно обойтись. Рисков нет. Синтаксический анализатор базы данных однозначно знает, данные из какой колонки таблицы запрашиваются. Но рекомендуется всё же использовать их — чтобы выработать привычку.
В случае извлечения данных из нескольких таблиц отказ от использования псевдонимов увеличивает риск получения некорректного результата. Допустим, что у таблиц есть колонки с одинаковым именем. Когда данные извлекаются и SQL-запрос звучит как: «Получаю записи из таблиц колонку А», то о какой колонке «А» идёт речь: из первой или второй таблицы? Если для таблицы назначен псевдоним, то SQL-запрос может звучать уже так: «Получаю записи из таблицы Т1 колонку А».
К SQL-запросу, возможно, придётся вернуться через какое-то время, чтобы внести в него изменения. В таких случаях подсказки в виде псевдонима (alias) помогут определить нужную колонку. Практически со стопроцентной уверенностью будет понятно, из какой таблицы что извлекали.
⚠️ Опасный подход:
SELECT TEST_DATA_1.TYPE
,TEST_DATA_1.VALUE
,TEST_DATA_2.TYPE
,TEST_DATA_2.VALUE
FROM TEST_DATA_1
,TEST_DATA_2
WHERE TEST_DATA_1.TEST_DATA_1_ID = TEST_DATA_2.TEST_DATA_1_ID;
✅ Безопасный подход заключается в обращении через псевдоним:
SELECT t1.TYPE AS TYPE_1 /* Колонка TYPE из таблицы TEST_DATA_1 */
,t1.VALUE AS VALUE_1 /* Колонка VALUE из таблицы TEST_DATA_1 */
,t2.TYPE AS TYPE_2 /* Колонка TYPE из таблицы TEST_DATA_2 */
,t2.VALUE AS VALUE_2 /* Колонка VALUE из таблицы TEST_DATA_2 */
FROM TEST_DATA_1 t1
,TEST_DATA_2 t2
WHERE t1.TEST_DATA_1_ID = t2.TEST_DATA_1_ID;
2. Извлекай только те данные, которые планируешь использовать
База данных зачастую является неотъемлемой частью приложения. По мере усложнения функционала в отдельной взятой таблице может увеличиваться количество колонок.
Рассмотрим пример «Карточка сотрудника». У нас есть таблица «Сотрудник» с колонками ФИО, пол, возраст. Данные из них извлекаются и выводятся на форму «Карточка сотрудника». SQL-запрос можно написать следующим образом: «Извлекаю все колонки из таблицы по указанному сотруднику». В таком случае извлекаются все колонки.
⚠️ Опасный подход заключается в извлечении всех данных:
SELECT t1.*
FROM TEST_DATA_1 t1
WHERE t1.TEST_DATA_1_ID = 3 /* ID EMPLOYEE = СОТРУДНИК 1 */;
В будущем могут появиться дополнительные колонки в базе данных — например, описание должностных обязанностей или адрес проживания — в рамках нового информационного потока использования базы данных. То есть вне «Карточки сотрудника».
/* Добавление новой колонки в таблицу */
ALTER TABLE TEST_DATA_1
ADD DESCRIPTION VARCHAR2(4000)
/
/* Обновление данных в таблице */
UPDATE TEST_DATA_1 t1
SET T1.DESCRIPTION = 'ДОПОЛНИТЕЛЬНЫЕ ДАННЫЕ ПО СОТРУДНИКУ 1'
WHERE t1.TEST_DATA_1_ID = 3 /* ID EMPLOYEE = СОТРУДНИК 1 */;
/* Извлечение данных из таблицы */
SELECT t1.*
FROM TEST_DATA_1 t1
WHERE t1.TEST_DATA_1_ID = 3 /* ID EMPLOYEE = СОТРУДНИК 1 */;
В результате данные по новым полям заполняются уже не только формой «Карточки сотрудника». И SQL-запрос получения информации для формы начинает работать медленнее. Причина в том, что приходится извлекать данные из большего количества колонок.
Деградация скорости получения данных может происходить постепенно или резко — но в самый неподходящий момент. Зачастую это связано с тем, что поля свободного ввода данных могут быть большими. То есть база данных должна больше информации подгрузить в память и потом отдать клиенту, приложение которого не готово к такому потоку данных.
Рассмотрим пример «Телефон». На телефоне пользователя установлено приложение. Сам телефон старый. Пользователь не выполнял обновления программного обеспечения (ПО), но замечает, что с какого-то момента времени приложение начало работать медленнее. У другого пользователя на новом телефоне то же приложение работает быстро. Ошибка «плавающая», но для разработчика неприятная.
Как правило, дело в том, как написано приложение. Данных извлекается больше, чем надо, и более современный телефон, у которого памяти больше, этого не заметит. Но старый не может себе этого позволить.
Чтобы таких неожиданностей не возникало, нужно извлекать строго те данные, которые требуется использовать и показывать на форме. В данном случае нужно было написать: «Извлекаю колонки ФИО, возраст, пол из таблички сотрудника, с фильтрацией по сотруднику».
✅ Безопасный подход заключается в получении нужных данных:
SELECT t1.TYPE AS TYPE_1 /* Колонка TYPE из таблицы TEST_DATA_1 */
,t1.VALUE AS VALUE_1 /* Колонка VALUE из таблицы TEST_DATA_1 */
FROM TEST_DATA_1 t1
WHERE t1.TEST_DATA_1_ID = 3 /* ID EMPLOYEE = СОТРУДНИК 1 */;
3. По максимуму используй данные, которые извлёк из таблицы
Каждый SQL-запрос к базе данных чего-то стоит. В тот момент, когда данные извлечены и находятся в памяти, надо по максимуму использовать то, что получено, чтобы оптимизировать время и ресурсы.
После обращения к таблице Table1, нужно постараться написать SQL-запрос так, чтобы не пришлось извлекать данные из неё несколько раз. Это не всегда возможно, но попытаться стоит.
⚠️ Опасный подход:
SELECT t1.TYPE AS TYPE_1
,t1.VALUE AS VALUE_1
,t2.VALUE AS VALUE_2
FROM TEST_DATA_1 t1
,TEST_DATA_2 T2
WHERE T1.TEST_DATA_1_ID = T2.TEST_DATA_1_ID
AND t2.VALUE = 'ПРОЖИВАЕТ В ГОРОДЕ МОСКВА'
UNION ALL
SELECT t1.TYPE AS TYPE_1
,t1.VALUE AS VALUE_1
,t2.VALUE AS VALUE_2
FROM TEST_DATA_1 t1
,TEST_DATA_2 T2
WHERE T1.TEST_DATA_1_ID = T2.TEST_DATA_1_ID
AND t2.VALUE = 'ПРОЖИВАЕТ В ГОРОДЕ САНКТ-ПЕТЕРБУРГ'
ORDER BY VALUE_1;
/* План запроса */
✅ Безопасный подход заключается в использовании полученных данных максимально продуктивно:
SELECT t1.TYPE AS TYPE_1
,t1.VALUE AS VALUE_1
,t2.VALUE AS VALUE_2
FROM TEST_DATA_1 t1
,TEST_DATA_2 T2
WHERE T1.TEST_DATA_1_ID = T2.TEST_DATA_1_ID
AND t2.VALUE IN ('ПРОЖИВАЕТ В ГОРОДЕ МОСКВА', 'ПРОЖИВАЕТ В ГОРОДЕ САНКТ-ПЕТЕРБУРГ')
ORDER BY VALUE_1;
/* План запроса */
Неоптимальный SQL-запрос может выполняться дольше, уронить инфраструктуру и даже повлиять на безопасность системы.
⚒ Рассмотрим тестовый пример:
/*
* Тестовый пример
* Каждый случай запроса выполняется 1 000 000 раз в “холостую”
*/
declare
start_time pls_integer;
end_time pls_integer;
begin
/* 1 Случай */
start_time := dbms_utility.get_time;
for indx in 1 .. 1000000 loop
for cur in (select t1.TYPE as TYPE_1
,t1.VALUE as VALUE_1
,t2.VALUE as VALUE_2
from TEST_DATA_1 t1
,TEST_DATA_2 T2
where T1.TEST_DATA_1_ID = T2.TEST_DATA_1_ID
and t2.VALUE = 'ПРОЖИВАЕТ В ГОРОДЕ МОСКВА'
union all
select t1.TYPE as TYPE_1
,t1.VALUE as VALUE_1
,t2.VALUE as VALUE_2
from TEST_DATA_1 t1
,TEST_DATA_2 T2
where T1.TEST_DATA_1_ID = T2.TEST_DATA_1_ID
and t2.VALUE = 'ПРОЖИВАЕТ В ГОРОДЕ САНКТ-ПЕТЕРБУРГ'
order by VALUE_1) loop
null;
end loop;
end loop;
end_time := dbms_utility.get_time;
dbms_output.put_line('execution time 1 --> ' || (end_time - start_time) / 100 || ' sec');
/* 2 Случай */
start_time := dbms_utility.get_time;
for indx in 1 .. 1000000 loop
for cur in (select t1.TYPE as TYPE_1
,t1.VALUE as VALUE_1
,t2.VALUE as VALUE_2
from TEST_DATA_1 t1
,TEST_DATA_2 T2
where T1.TEST_DATA_1_ID = T2.TEST_DATA_1_ID
and t2.VALUE in ('ПРОЖИВАЕТ В ГОРОДЕ МОСКВА', 'ПРОЖИВАЕТ В ГОРОДЕ САНКТ-ПЕТЕРБУРГ')
order by VALUE_1) loop
null;
end loop;
end loop;
end_time := dbms_utility.get_time;
dbms_output.put_line('execution time 2 --> ' || (end_time - start_time) / 100 || ' sec');
end;
/
/*
* Результат выполнения
* Важно не время, которое зависит от ресурсов на ПК, а разница выполнения
*/
/*
Done in 64,516 seconds
*/
execution time 1 --> 46.83 sec
execution time 2 --> 17.67 sec
Рассмотрим пример «Работа ЦОД». Есть Центр Обработки Данных (ЦОД). В нём, на одном из ресурсов внутри приложения, выполняется некий SQL-запрос, который постепенно использует всю доступную память без ограничений. И приложениям, которые стоят на том же ресурсе, со временем перестаёт хватать памяти на стабильную работу. Это может привести к их падению.
4. Проверяй запросы SQL на индексы
SQL-запросы бывают простые и сложные. Иногда извлекается мало данных, иногда — много. Если таблица большая, и в ней очень разнообразные данные, то в зависимости от того, как обращаться к этим данным, использовать индекс или нет, можно потерять время.
Рассмотрим пример «Брокерская биржа». В рамках отдельного процесса извлекаются данные для покупки-продажи акций. Используя оптимизированный SQL-запрос, можно быстро получать информацию, по какой цене торгуется каждая акция. И делать прогноз — покупать или продавать.
Если SQL-запрос не оптимизирован, извлечение данных занимает больше времени. И пользователь вынужден ждать, хотя мог за это время сделать что-то, что принесло бы ему деньги.
Индексы — это инструмент оптимизации извлечения данных. Конечно, это не панацея, и если таблица маленькая, по ней проще пройти прямым перебором и получить данные.
Добавим в тестовую таблицу 1 новые данные:
/* Добавление новых данных */
INSERT INTO TEST_DATA_1 (TYPE, VALUE) VALUES ('STOCK MARKET', 'АКЦИЯ 1. СТОИМОСТЬ 101 РУБ');
INSERT INTO TEST_DATA_1 (TYPE, VALUE) VALUES ('STOCK MARKET', 'АКЦИЯ 2. СТОИМОСТЬ 102 РУБ');
INSERT INTO TEST_DATA_1 (TYPE, VALUE) VALUES ('STOCK MARKET', 'АКЦИЯ 3. СТОИМОСТЬ 103 РУБ');
INSERT INTO TEST_DATA_1 (TYPE, VALUE) VALUES ('STOCK MARKET', 'АКЦИЯ 4. СТОИМОСТЬ 104 РУБ');
⚠️ Опасный подход заключается в игнорировании использования индексов:
/* Извлечение всех данных TYPE = STOCK MARKET */
SELECT t1.TYPE AS TYPE_1
,t1.VALUE AS VALUE_1
FROM TEST_DATA_1 t1
WHERE t1.TYPE = 'STOCK MARKET';
/* План запроса */
Добавим в тестовую таблицу 1 новый индекс
/* Добавление нового индекса */
CREATE INDEX TEST_DATA_1_IDX1 ON TEST_DATA_1 (TYPE)
/
✅ Безопасный подход заключается в использовании индексов:
/* Извлечение всех данных TYPE = STOCK MARKET */
SELECT t1.TYPE AS TYPE_1
,t1.VALUE AS VALUE_1
FROM TEST_DATA_1 t1
WHERE t1.TYPE = 'STOCK MARKET';
/* План запроса */
Рассмотрим пример «Доставка почты». Показательный пример работы индексов — доставка почты из точки А в одном городе, в точку Б в другом. Зная, куда конкретно нужно доставить посылку, мы можем идти по индексам и определить, где и когда повернуть, чтобы довезти посылку за максимально короткое время. Если везти посылку на машине, то это сокращает расход топлива — а значит, и материальные издержки на доставку.
В противном случае можно сворачивать не там, спрашивать дорогу у прохожих, которые знают её плохо. И, вместо того чтобы доставить посылку за Время Т1, опоздать на Время Т2. В итоге покупатель ждёт, а продавец теряет деньги.
5. Начинай запрос SQL с таблицы с меньшим набором записей
Допустим, нам нужно соединить две таблицы: с маленьким количеством записей и с большим. Стоит сделать следующее:
- начинать извлечение данных из таблицы с меньшим набором данных;
- продолжать извлечение данных из таблицы с большим набором данных.
⚒ Добавим в тестовую таблицу 2 новые данные:
/* Добавление новых данных */
declare
l_type test_data_2.type%type := 'STOCK MARKET';
l_value_2 test_data_2.value%type := '';
l_sql varchar2(128) := '';
begin
/* Извлечение данных из тестовой таблицы 1 */
for cur_t1 in (select t1.test_data_1_id as test_data_1_id
,t1.type as type_1
,t1.value as value_1
from TEST_DATA_1 t1
where type = l_type) loop
/* Цикл до 1 000 000 на каждую полученную запись из тестовой таблицы 1 */
for indx in 1 .. 1000000 loop
l_value_2 := cur_t1.value_1 || '. ' || 'ЗАПИСЬ ' || indx;
l_sql := 'INSERT INTO TEST_DATA_2 (TEST_DATA_1_ID, TYPE, VALUE) VALUES (' || --
cur_t1.TEST_DATA_1_ID || ', ' || --
'''' || l_type || ''', ' || --
'''' || l_value_2 || ''')';
/* Выполнение динамического запроса */
execute immediate l_sql;
end loop;
end loop;
end;
/
/* Общее число записей в таблице TEST_DATA_1 */
SELECT COUNT(1) AS CNT
FROM TEST_DATA_1
/
/* Общее число записей в таблице TEST_DATA_2 */
SELECT COUNT(1) AS CNT
FROM TEST_DATA_2
/
/* Добавление нового индекса для таблицы TEST_DATA_1 */
CREATE INDEX TEST_DATA_1_IDX2 ON TEST_DATA_1 (TEST_DATA_1_ID, TYPE)
/
/* Добавление нового индекса для таблицы TEST_DATA_2 */
CREATE INDEX TEST_DATA_2_IDX1 ON TEST_DATA_2 (TEST_DATA_1_ID, TYPE)
/
CREATE INDEX TEST_DATA_2_IDX2 ON TEST_DATA_2 (TEST_DATA_1_ID)
/
CREATE INDEX TEST_DATA_2_IDX3 ON TEST_DATA_2 (TYPE)
/
/* Сбор статистики после добавления данных */
declare
l_user varchar2(30 char) := user;
begin
/* Для таблицы TEST_DATA_1 */
DBMS_STATS.GATHER_TABLE_STATS(ownname => l_user --
,tabname => 'TEST_DATA_1'
,cascade => true);
/* Для таблицы TEST_DATA_2 */
DBMS_STATS.GATHER_TABLE_STATS(ownname => l_user --
,tabname => 'TEST_DATA_2'
,cascade => true);
end;
/
Если поступить наоборот, то мы потеряем время, потому что перебирать данные из большей таблицы дольше.
⚒ Рассмотрим тестовый пример:
/* * Тестовый пример * Каждый случай запроса выполняется 100 раз в “холостую” * Запросы усложнены и их можно упростить, добиваясь большей производительности и схожего результата * Попробуйте поэкспериментировать */ declare start_time pls_integer; end_time pls_integer; begin /* 1 Случай. От большего к меньшему */ start_time := dbms_utility.get_time; for indx in 1 .. 100 loop for cur in (select t1.type as type_1 ,t1.value as value_1 ,t2_.type_2 as type_2 ,t2_.value_2_min as value_2_min ,t2_.value_2_max as value_2_max ,t2_.value_2_cnt as value_2_cnt from (select t2.TEST_DATA_1_ID as TEST_DATA_1_ID ,t2.TYPE as TYPE_2 ,min(t2.VALUE) as VALUE_2_MIN ,max(t2.VALUE) as VALUE_2_MAX ,count(t2.VALUE) as VALUE_2_CNT from TEST_DATA_2 t2 where t2.type = 'STOCK MARKET' group by t2.TEST_DATA_1_ID ,t2.TYPE order by t2.TEST_DATA_1_ID) t2_ join TEST_DATA_1 t1 on t1.TEST_DATA_1_ID = t2_.TEST_DATA_1_ID and t1.type = 'STOCK MARKET' order by t1.value) loop null; end loop; end loop;end_time := dbms_utility.get_time; dbms_output.put_line('execution time 1 --> ' || (end_time - start_time) / 100 || ' sec'); /* 2 Случай. От меньшего к большему */ start_time := dbms_utility.get_time; for indx in 1 .. 100 loop for cur in (select t1_.type_1 as type_1 ,t1_.value_1 as value_1 ,t2.type as type_2 ,min(t2.value) as value_2_min ,max(t2.value) as value_2_max ,count(t2.value) as value_2_cnt from (select t1.TEST_DATA_1_ID as TEST_DATA_1_ID ,t1.TYPE as TYPE_1 ,t1.VALUE as VALUE_1 from TEST_DATA_1 t1 where t1.TYPE = 'STOCK MARKET' order by t1.value) t1_ join TEST_DATA_2 t2 on t2.TEST_DATA_1_ID = t1_.TEST_DATA_1_ID and t2.TYPE = 'STOCK MARKET' group by t1_.type_1 ,t1_.value_1 ,t2.type) loop null; end loop; end loop; end_time := dbms_utility.get_time; dbms_output.put_line('execution time 2 --> ' || (end_time - start_time) / 100 || ' sec'); end; / /* * Результат выполнения * Важно не время, которое зависит от ресурсов на ПК, а разница выполнения * Каждый вариант возвращает одинаковые данные */
/* Executed in 269,203 seconds */
execution time 1 --> 149.49 sec
execution time 2 --> 119.68 sec
Рассмотрим пример «Очередь клиентов». Есть поток клиентов, каждого из которых нужно обслужить. Операторы, заполняя форму «Анкета» задают серию вопросов. Один из них, влияет на дальнейший ход общения: «Вам исполнилось 18 лет?». Если клиент отвечает нет, то оператор прекращает общение, иначе продолжает задавать вопросы.
Если оператор задаст вопрос про возраст в конце общения, то любой потенциальный клиент должен будет заполнить всю анкету, даже если в этом нет смысла. Рациональный подход в общении с клиентами помогает операторам за одно и то же время обслужить большее число клиентов. С базами данных всё так же.
6. Не допускай декартового произведения между таблицами
Результатом декартового — или перекрёстного — произведения множеств будет такое множество, элементами которого являются все возможные упорядоченные пары элементов исходных множеств. Рассмотрим пример «Адрес». Возьмём две таблицы «Город», «Улица». В первой таблице «Город» есть две записи: Москва и Санкт-Петербург. Во второй таблице «Улица» сохранены следующие записи:
- улица Карла Маркса, которая одновременно есть и в Москве, и в Санкт-Петербурге;
- улица Крупской аналогично и в Москве, и в Санкт-Петербурге;
- Малый Полуярославский переулок только в Москве.
Пишем запрос: «Получаю из таблицы «Улица», которые принадлежат городу Москва».
⚠️ Опасный подход:
SELECT t1.TYPE AS TYPE_1 /* Колонка TYPE из таблицы TEST_DATA_1 */
,t1.VALUE AS VALUE_1 /* Колонка VALUE из таблицы TEST_DATA_1 */
,t2.TYPE AS TYPE_2 /* Колонка TYPE из таблицы TEST_DATA_2 */
,t2.VALUE AS VALUE_2 /* Колонка VALUE из таблицы TEST_DATA_2 */
FROM TEST_DATA_1 t1
,TEST_DATA_2 t2
WHERE t1.TYPE = 'CITY'
AND t2.TYPE = 'STREET';
SQL-запрос написан без условия, то есть: «Извлекаю улицы, относящиеся к городам, без соединения таблиц». База данных, не понимая, по какому городу делается SQL-запрос, соединит со всеми улицами и Москву, и Санкт-Петербург. Всего вернётся 2* 5 = 10 записей.
✅ Безопасный подход заключается в наличии связей:
SELECT t1.TYPE AS TYPE_1 /* Колонка TYPE из таблицы TEST_DATA_1 */
,t1.VALUE AS VALUE_1 /* Колонка VALUE из таблицы TEST_DATA_1 */
,t2.TYPE AS TYPE_2 /* Колонка TYPE из таблицы TEST_DATA_2 */
,t2.VALUE AS VALUE_2 /* Колонка VALUE из таблицы TEST_DATA_2 */
FROM TEST_DATA_1 t1
,TEST_DATA_2 t2
WHERE t1.TEST_DATA_1_ID = t2.TEST_DATA_1_ID
AND t1.TYPE = 'CITY'
AND t2.TYPE = 'STREET';
Этот SQL-запрос написан с условием, то есть: «Извлекаю улицы, относящиеся к городу Москве, соединяя две таблицы условием». В нём указывается, по какому городу нужно выполнить фильтрацию. Поэтому возвращено 3 записи.
Когда данные извлекаются больше чем из одной таблицы, важно, как они соединяются между собой. Неправильное соединение будет возвращать неверные данные и не в ожидаемом количестве.
7. Проверяй, что имена параметров процедур не совпадают с именами колонок
Процедуры, функции могут использоваться для разных целей. Одно из возможных предназначений — обновление записей в таблице.
Допустим, есть строковый параметр А, который передаётся на вход процедуры с целью фильтрации. Можно сказать, что написано так: «Обновляю таблицу, задав новое значение для колонки, где выполняется фильтрация по колонке А равной параметру А». В этом случае наблюдается полное совпадение А = А. База данных обновит все записи в этой таблице.
Чтобы этого не было, параметру добавляют префикс или постфикс. Например, параметр будет называться не А, а РА. В изменённом виде можно сказать, что написано так: «Обновляю таблицу, задав новое значение для колонки, где выполняется фильтрация по колонке А равной параметру PА».
⚠️ Опасный подход:
/* 1 вариант процедуры с ошибкой */
create or replace procedure e_test_data_1_upd_description(test_data_1_id in TEST_DATA_1.TEST_DATA_1_ID%type
,description in TEST_DATA_1.DESCRIPTION%type) as
begin
update TEST_DATA_1 t1 –
set t1.DESCRIPTION = description /* Обновление записи */
where t1.TEST_DATA_1_ID = test_data_1_id;
exception
when others then
/* Блок перехвата ошибок */
null;
end e_test_data_1_upd_description;
/
/* Пример вызова 1 варианта процедуры с ошибкой */
declare
begin
e_test_data_1_upd_description(test_data_1_id => 4 /* ID EMPLOYEE = СОТРУДНИК 2 */
,description => 'ДОПОЛНИТЕЛЬНЫЕ ДАННЫЕ ПО СОТРУДНИКУ 2');
end;
/
/*
* Результат изменений
* Визуально изменений нет
*/
SELECT T1.TEST_DATA_1_ID AS TEST_DATA_1_ID
,T1.TYPE AS TYPE_1
,T1.VALUE AS VALUE_1
,T1.DESCRIPTION AS DESCRIPTION
FROM TEST_DATA_1 T1;
⚠️ Опасный подход:
/* 2 вариант процедуры с ошибкой */
create or replace procedure e_test_data_1_upd_description(test_data_1_id in TEST_DATA_1.TEST_DATA_1_ID%type
,description_new in TEST_DATA_1.DESCRIPTION%type) as
begin
update TEST_DATA_1 t1 –
set t1.DESCRIPTION = description_new /* Обновление записи */
where t1.TEST_DATA_1_ID = test_data_1_id;
exception
when others then
/* Блок перехвата ошибок */
null;
end e_test_data_1_upd_description;
/
/* Пример вызова 2 варианта процедуры с ошибкой */
declare
begin
e_test_data_1_upd_description(test_data_1_id => 4 /* ID EMPLOYEE = СОТРУДНИК 2 */
,description_new => 'ДОПОЛНИТЕЛЬНЫЕ ДАННЫЕ ПО СОТРУДНИКУ 2');
end;
/
/*
* Результат изменений
* Изменены все записи
*/
SELECT T1.TEST_DATA_1_ID AS TEST_DATA_1_ID
,T1.TYPE AS TYPE_1
,T1.VALUE AS VALUE_1
,T1.DESCRIPTION AS DESCRIPTION
FROM TEST_DATA_1 T1;
✅ Безопасный подход заключается в передаче параметра, имя которого не совпадает с именем колонки в таблице:
/* Вариант процедуры без ошибки */
create or replace procedure e_test_data_1_upd_description(p_test_data_1_id in TEST_DATA_1.TEST_DATA_1_ID%type
,p_description in TEST_DATA_1.DESCRIPTION%type) as
begin
update TEST_DATA_1 t1 –
set t1.DESCRIPTION = p_description /* Обновление записи */
where t1.TEST_DATA_1_ID = p_test_data_1_id;
exception
when others then
/* Блок перехвата ошибок */
null;
end e_test_data_1_upd_description;
/
/* Пример вызова варианта процедуры без ошибки */
declare
begin
e_test_data_1_upd_description(p_test_data_1_id => 4 /* ID EMPLOYEE = СОТРУДНИК 2 */
,p_description => 'ДОПОЛНИТЕЛЬНЫЕ ДАННЫЕ ПО СОТРУДНИКУ 2');
end;
/
/*
* Результат изменений
* Изменена 1 требуемая запись
*/
SELECT T1.TEST_DATA_1_ID AS TEST_DATA_1_ID
,T1.TYPE AS TYPE_1
,T1.VALUE AS VALUE_1
,T1.DESCRIPTION AS DESCRIPTION
FROM TEST_DATA_1 T1;
8. Следи за временем выполнения SQL-запроса
Время, пожалуй, один из самых бесценных ресурсов. Пренебрежение за контролем времени выполнения SQL-запроса приведёт к трате усилий и денег.
Рассмотрим пример «Мониторинг времени выполнения». Допустим, на уровне базы данных продуктовой среды настроен специальный триггер. Его предназначение сводится к следующему:
- прерывать сессию, которая выполняется дольше N-минут;
- сохранить информацию об SQL-запросе в журнал для последующего анализа или постановки на мониторинг.
Вариант триггера на таблицу с искусственно генерируемой ошибкой в момент обновления данных:
/* Вариант триггера */
create or replace trigger TEST_DATA_1_AIUDR_PTCL
after insert or update or delete on TEST_DATA_1
for each row
begin
if UPDATING then
if (:old.test_data_1_id = 5 and :new.description is not null) then
DBMS_OUTPUT.PUT_LINE('Log entry.');
raise_application_error(-20001, 'No Update with id 5 and new description.');
rollback;
end if;
end if;
end;
/Специалисту рассказывали про этот триггер. Он проигнорировал это или забыл — и реализовал, поставленную задачу на непродуктовой среде таким образом, что одно из действий выполняется больше N-минут. Передал всё на установку в продуктовую среду. Получилось, что реализованный функционал не работает полностью или частично.
Вариант процедуры с искусственно завышенным временем выполнения
/* Вариант процедуры */
create or replace procedure e_test_data_1_upd_description(p_test_data_1_id in TEST_DATA_1.TEST_DATA_1_ID%type
,p_description in TEST_DATA_1.DESCRIPTION%type) as
begin
/* Цикл добавлен для увеличения времени выполнения блока программной логики */
for indx in 1 .. 1000000 loop
for cur in (select t1.TYPE as TYPE_1
,t1.VALUE as VALUE_1
,t2.VALUE as VALUE_2
from TEST_DATA_1 t1
,TEST_DATA_2 T2
where t1.TEST_DATA_1_ID = t2.TEST_DATA_1_ID
and t1.TEST_DATA_1_ID = p_test_data_1_id) loop
null;
end loop;
end loop;
/* Блок программной логики */
update TEST_DATA_1 t1 –
set t1.DESCRIPTION = p_description /* Обновление записи */
where t1.TEST_DATA_1_ID = p_test_data_1_id;
end e_test_data_1_upd_description;
/
/* Пример вызова процедуры */
declare
begin
e_test_data_1_upd_description(p_test_data_1_id => 5 /* ID EMPLOYEE = СОТРУДНИК 3 */
,p_description => 'ДОПОЛНИТЕЛЬНЫЕ ДАННЫЕ ПО СОТРУДНИКУ 3');
end;
/
Задача специалиста смотреть на поставленную задачу шире, учитывая разные аспекты, применяя разные подходы. Можно попробовать оптимизировать SQL-запрос, например, добавляя индексы. Можно менять алгоритмы выполнения действий, добиваясь требуемого результата.
9. Используй копию данных для построения отчётности
Отчётность — это извлечение массива данных из базы для последующей обработки, аналитики, построения прогноза, прочее. Для неё может извлекаться значительный объём данных.
Рассмотрим пример «Отчёт о расходах за период». У нас есть промышленная среда, на которой развёрнуто приложение с подключением к базе данных. С приложением работают сотрудники. Задачей одних является внесение информации о приходе и расходе денежных средств. Задачей других — подготовка отчёта о расходе денежных средств за период. Информация вносится периодически и в небольшом объёме. Извлекается реже, но вся, что была внесена за конкретный период.
При ограниченных ресурсах базы данных извлечение может приводить к замедлению работы приложения. Потому что на стороне БД подключаются сотрудники из обеих групп, ресурсы делятся между ними, и отклик происходит медленнее. Избежать подобного эффекта можно при помощи копии базы данных с применением механизма репликации. Так, клон клон с определённой периодичностью синхронизируется с основной базой данных (их может быть несколько).
Создание копии базы данных — задача администраторов базы данных (Database administrator, DBA). Для большего погружения в механизм репликации можно обратиться к официальной справочной информации соответствующей базы данных. Например:
- Oracle — Setting Up Replication (oracle.com);
- MSSQL — Учебник. Подготовка к репликации — SQL Server | Microsoft Learn;
- PostgreSQL — PostgreSQL : Документация: 15: Глава 27. Отказоустойчивость, балансировка нагрузки и репликация : Компания Postgres Professional.
- MySQL — MySQL :: MySQL 8.0 Reference Manual :: 17.1.2.6 Setting Up Replicas.
Взаимодействие с базой данных можно трансформировать следующим образом. Сотрудники, которые вводят информацию, так и продолжают работать с основной базой данных. Сотрудники, которые заняты отчётностью, работают с её копией. Информационные потоки разведены. Влияние устранено.
10. Проверяй формат данных
Бывает, что отчёт, который обычно работает хорошо, возвращает ошибку, если ввести другие входные данные. Это связано с тем, что у новых входных данных другой формат.
Рассмотрим пример «Отчёт». У нас есть отчёт, строящийся на данных, которые заполняются внешним приложением. Одна из его колонок — дата. Поле ввода на форме, в которой происходит её заполнение — строковое. В подавляющем большинстве случаев формат: день числом, месяц числом, год числом, например, 01.01.2001. Изредка — день числом, месяц словом, год числом, например, «1 января 2001».
Приложение позволяет вводить в любом виде. Конечные пользователи ошибку не видят, но для отчёта это — потенциальная проблема. Она может заключаться в неверном предположении, что дата всегда заносится в базу данных в одном виде.
⚠️ Опасный подход заключается в игнорировании формата используемых данных:
SELECT t1.TYPE AS TYPE_1 /* Колонка TYPE из таблицы TEST_DATA_1 */
,to_date(t1.VALUE, 'DD.MM.RRRR') AS VALUE_1 /* Колонка VALUE из таблицы TEST_DATA_1 */
,t2.TYPE AS TYPE_2 /* Колонка TYPE из таблицы TEST_DATA_2 */
,t2.VALUE AS VALUE_2 /* Колонка VALUE из таблицы TEST_DATA_2 */
FROM TEST_DATA_1 t1
,TEST_DATA_2 t2
WHERE t1.TEST_DATA_1_ID = t2.TEST_DATA_1_ID
AND t1.TYPE = 'DATE'
AND t2.TYPE = 'DATE';
✅ Безопасный подход заключается в понимании формата используемых данных:
SELECT t1.TYPE AS TYPE_1 /* Колонка TYPE из таблицы TEST_DATA_1 */
,to_date(t1.VALUE, 'DD.MM.RRRR') AS VALUE_1 /* Колонка VALUE из таблицы TEST_DATA_1 */
,t2.TYPE AS TYPE_2 /* Колонка TYPE из таблицы TEST_DATA_2 */
,t2.VALUE AS VALUE_2 /* Колонка VALUE из таблицы TEST_DATA_2 */
FROM TEST_DATA_1 t1
,TEST_DATA_2 t2
WHERE t1.TEST_DATA_1_ID = t2.TEST_DATA_1_ID
AND t1.TYPE = 'DATE'
AND t2.TYPE = 'DATE'
AND t2.VALUE = 'Формат день числом, месяц числом, год числом';
Наличие разных данных можно узнать заранее. Для этого, когда делается отчёт, можно выполнить проверку на всех данных, а не только на части. Это — залог стабильной работать и уверенность, что созданный отчёт будет работать.
Вспомним, что написано выше, и закрепим правила:
- Объявляя имена таблиц, обращайся к записям через имена таблиц.
- Извлекай только те данные, которые планируешь использовать.
- По максимуму используй данные, которые извлёк из таблицы.
- Проверяй запросы SQL на индексы.
- Начинай запрос SQL с таблицы с меньшим набором записей.
- Не допускай декартового произведения между таблицами.
- Проверяй, что имена параметров процедур не совпадают с именами колонок.
- Следи за временем выполнения SQL-запроса.
- Используй копию данных для построения отчётности.
- Проверяй формат данных.
От автора
Подходов к оптимизации великое множество. Цель статьи — пробудить интерес искать и находить места роста производительности и снижения издержек. И помните Зако́н Ме́рфи: «Если что-нибудь может пойти не так, оно пойдёт не так».
