Написать пост

SQL для начинающих: 10 правил построения «точных» запросов

Научимся писать SQL-запросы, которые будут предоставлять данные в нужном объёме и за минимальное время.

11К открытий15К показов

«Точный» 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;
		
SQL для начинающих: 10 правил построения «точных» запросов 1
			/* Удаление всех данных без проверок */ 
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;
		
SQL для начинающих: 10 правил построения «точных» запросов 2
			/* Удаление всех данных без проверок */ 
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;
		
SQL для начинающих: 10 правил построения «точных» запросов 3

✅ Безопасный подход заключается в обращении через псевдоним:

			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;
		
SQL для начинающих: 10 правил построения «точных» запросов 4

2. Извлекай только те данные, которые планируешь использовать

База данных зачастую является неотъемлемой частью приложения. По мере усложнения функционала в отдельной взятой таблице может увеличиваться количество колонок.

Рассмотрим пример «Карточка сотрудника». У нас есть таблица «Сотрудник» с колонками ФИО, пол, возраст. Данные из них извлекаются и выводятся на форму «Карточка сотрудника». SQL-запрос можно написать следующим образом: «Извлекаю все колонки из таблицы по указанному сотруднику». В таком случае извлекаются все колонки.

⚠️ Опасный подход заключается в извлечении всех данных:

			SELECT t1.*
  FROM TEST_DATA_1 t1
 WHERE t1.TEST_DATA_1_ID = 3 /* ID EMPLOYEE = СОТРУДНИК 1 */;
		
SQL для начинающих: 10 правил построения «точных» запросов 5


В будущем могут появиться дополнительные колонки в базе данных — например, описание должностных обязанностей или адрес проживания — в рамках нового информационного потока использования базы данных. То есть вне «Карточки сотрудника».

			/* Добавление новой колонки в таблицу */
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 для начинающих: 10 правил построения «точных» запросов 6


В результате данные по новым полям заполняются уже не только формой «Карточки сотрудника». И 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 */;
		
SQL для начинающих: 10 правил построения «точных» запросов 7

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;
		
SQL для начинающих: 10 правил построения «точных» запросов 8
			/* План запроса */
		
SQL для начинающих: 10 правил построения «точных» запросов 9


✅ Безопасный подход заключается в использовании полученных данных максимально продуктивно:

			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 для начинающих: 10 правил построения «точных» запросов 10
			/* План запроса */
		
SQL для начинающих: 10 правил построения «точных» запросов 11


Неоптимальный 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';

/* План запроса */
		
SQL для начинающих: 10 правил построения «точных» запросов 12


Добавим в тестовую таблицу 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';
		
SQL для начинающих: 10 правил построения «точных» запросов 13
			/* План запроса */
		
SQL для начинающих: 10 правил построения «точных» запросов 14


Рассмотрим пример «Доставка почты». Показательный пример работы индексов — доставка почты из точки А в одном городе, в точку Б в другом. Зная, куда конкретно нужно доставить посылку, мы можем идти по индексам и определить, где и когда повернуть, чтобы довезти посылку за максимально короткое время. Если везти посылку на машине, то это сокращает расход топлива — а значит, и материальные издержки на доставку.

В противном случае можно сворачивать не там, спрашивать дорогу у прохожих, которые знают её плохо. И, вместо того чтобы доставить посылку за Время Т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
/
		
SQL для начинающих: 10 правил построения «точных» запросов 15
			/* Общее число записей в таблице TEST_DATA_2 */ 
SELECT COUNT(1) AS CNT 
  FROM TEST_DATA_2 
/
		
SQL для начинающих: 10 правил построения «точных» запросов 16
			/* Добавление нового индекса для таблицы 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;
		
SQL для начинающих: 10 правил построения «точных» запросов 17
			/* 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 для начинающих: 10 правил построения «точных» запросов 18


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 для начинающих: 10 правил построения «точных» запросов 19


Этот 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;
		
SQL для начинающих: 10 правил построения «точных» запросов 20


⚠️ Опасный подход:

			/* 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;
		
SQL для начинающих: 10 правил построения «точных» запросов 21


✅ Безопасный подход заключается в передаче параметра, имя которого не совпадает с именем колонки в таблице:

			/* Вариант процедуры без ошибки */
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;
		
SQL для начинающих: 10 правил построения «точных» запросов 22

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 для начинающих: 10 правил построения «точных» запросов 23


Задача специалиста смотреть на поставленную задачу шире, учитывая разные аспекты, применяя разные подходы. Можно попробовать оптимизировать SQL-запрос, например, добавляя индексы. Можно менять алгоритмы выполнения действий, добиваясь требуемого результата.

9. Используй копию данных для построения отчётности

Отчётность — это извлечение массива данных из базы для последующей обработки, аналитики, построения прогноза, прочее. Для неё может извлекаться значительный объём данных.

Рассмотрим пример «Отчёт о расходах за период». У нас есть промышленная среда, на которой развёрнуто приложение с подключением к базе данных. С приложением работают сотрудники. Задачей одних является внесение информации о приходе и расходе денежных средств. Задачей других — подготовка отчёта о расходе денежных средств за период. Информация вносится периодически и в небольшом объёме. Извлекается реже, но вся, что была внесена за конкретный период.

При ограниченных ресурсах базы данных извлечение может приводить к замедлению работы приложения. Потому что на стороне БД подключаются сотрудники из обеих групп, ресурсы делятся между ними, и отклик происходит медленнее. Избежать подобного эффекта можно при помощи копии базы данных с применением механизма репликации. Так, клон клон с определённой периодичностью синхронизируется с основной базой данных (их может быть несколько).

Создание копии базы данных — задача администраторов базы данных (Database administrator, DBA). Для большего погружения в механизм репликации можно обратиться к официальной справочной информации соответствующей базы данных. Например:

Взаимодействие с базой данных можно трансформировать следующим образом. Сотрудники, которые вводят информацию, так и продолжают работать с основной базой данных. Сотрудники, которые заняты отчётностью, работают с её копией. Информационные потоки разведены. Влияние устранено.

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';
		
SQL для начинающих: 10 правил построения «точных» запросов 24


✅ Безопасный подход заключается в понимании формата используемых данных:

			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 для начинающих: 10 правил построения «точных» запросов 25


Наличие разных данных можно узнать заранее. Для этого, когда делается отчёт, можно выполнить проверку на всех данных, а не только на части. Это — залог стабильной работать и уверенность, что созданный отчёт будет работать.

Вспомним, что написано выше, и закрепим правила:

  1. Объявляя имена таблиц, обращайся к записям через имена таблиц.
  2. Извлекай только те данные, которые планируешь использовать.
  3. По максимуму используй данные, которые извлёк из таблицы.
  4. Проверяй запросы SQL на индексы.
  5. Начинай запрос SQL с таблицы с меньшим набором записей.
  6. Не допускай декартового произведения между таблицами.
  7. Проверяй, что имена параметров процедур не совпадают с именами колонок.
  8. Следи за временем выполнения SQL-запроса.
  9. Используй копию данных для построения отчётности.
  10. Проверяй формат данных.

От автора

Подходов к оптимизации великое множество. Цель статьи — пробудить интерес искать и находить места роста производительности и снижения издержек. И помните Зако́н Ме́рфи: «Если что-нибудь может пойти не так, оно пойдёт не так».

Следите за новыми постами
Следите за новыми постами по любимым темам
11К открытий15К показов