Урок 1. Первые SQL запросы
Добро пожаловать на первый урок по реляционным базам данных и языку SQL.
Реляционные базы данных представляют собой набор таблиц с информацией.
Вроде такой:
| id | name | count | price |
|---|---|---|---|
| 1 | Телевизор | 3 | 43200.00 |
| 2 | Микроволновая печь | 4 | 3200.00 |
| 3 | Холодильник | 3 | 12000.00 |
| 4 | Роутер | 1 | 1340.00 |
| 5 | Компьютер | 0 | 26150.00 |
| id | first_name | last_name | birthday | age |
|---|---|---|---|---|
| 1 | Дмитрий | Иванов | 1996-12-11 | 20 |
| 2 | Олег | Лебедев | 2000-02-07 | 17 |
| 3 | Тимур | Шевченко | 1998-04-27 | 19 |
| 4 | Светлана | Иванова | 1993-08-06 | 23 |
| 5 | Олег | Ковалев | 2002-02-08 | 15 |
| 6 | Алексей | Иванов | 1993-08-05 | 23 |
| 7 | Алена | Процук | 1997-02-28 | 18 |
Каждая таблица состоит из столбцов и строк.
Посмотрим внимательней на таблицу products, которая хранит данные о товарах в интернет-магазине. Таблица содержит 4 столбца: id, name, count и price. Каждый из столбцов отвечает за какой-то определенный тип информации: id — это уникальный номер товара, name — его имя, count — количество, price — цена.
Строка отвечает за конкретный товар в таблице. Если мы посмотрим на третью строку, то найдем там «Холодильник» с ценой 12 000 рублей в количестве 3 штук.
Другая таблица — это users, которая хранит данные о пользователях в системе. В таблице 5 столбцов: также уникальный номер пользователя id, имя, фамилия, возраст — age и дата рождения — birthday.
Как я уже говорил, каждый столбец отвечает за какую-то информацию и эта информация относится к определенному типу данных. Столбцы first_name и last_name строковые, age и id содержат числа, а birthday — дату.
Название столбца, его тип и порядок строго задаются на этапе создания таблицы. Об этом мы поговорим в других уроках.
А вот записи таблицы (или строки) заполняются в процессе её использования. Поэтому столбцов у нас жестко 5. А строк может быть сколько угодно. Зарегистрировался пользователь на сайте — добавили строку. Привезли новые товары в магазин — таблица растет.
Добавление, удаление, изменение или получение данных из таблиц, выполняется с помощью языка SQL.
SQL — это язык общения с базами данных.
Давайте попробуем получить информацию из таблицы users. Для этого надо написать и выполнить такой SQL-запрос:
SELECT * FROM usersПолучили всех пользователей из таблицы users:
| id | first_name | last_name | birthday | age |
|---|---|---|---|---|
| 1 | Дмитрий | Иванов | 1996-12-11 | 20 |
| 2 | Олег | Лебедев | 2000-02-07 | 17 |
| 3 | Тимур | Шевченко | 1998-04-27 | 19 |
| 4 | Светлана | Иванова | 1993-08-06 | 23 |
| 5 | Олег | Ковалев | 2002-02-08 | 15 |
| 6 | Алексей | Иванов | 1993-08-05 | 23 |
| 7 | Алена | Процук | 1997-02-28 | 18 |
Рассмотрим SQL запрос подробнее.
Оператор SELECT говорит, что мы будем извлекать данные. После него идет список столцов, которые мы хотим получить. Если указать звездочку (*), как у нас, то получим все столбцы в том порядке, в котором они определены в таблице: id, first_name, last_name и тд. Далее идет конструкция FROM users, которая буквально означает ИЗ users.
То есть вся SQL конструкция читается как ВЫБРАТЬ все столбцы ИЗ таблицы users.
Теперь вместо звездочки напишем: last_name, first_name, birthday, чтобы у нас получился такой SQL-запрос:
SELECT last_name, first_name, birthday FROM usersЕсли его выполнить, то мы снова получим всех пользователей из таблицы users, но на этот раз только фамилию, имя и дату рождения. То есть записи все, а столбцы нет:
| id | last_name | first_name | birthday |
|---|---|---|---|
| 1 | Иванов | Дмитрий | 1996-12-11 |
| 2 | Лебедев | Олег | 2000-02-07 |
| 3 | Шевченко | Тимур | 1998-04-27 |
| 4 | Иванова | Светлана | 1993-08-06 |
| 5 | Ковалев | Олег | 2002-02-08 |
| 6 | Иванов | Алексей | 1993-08-05 |
| 7 | Процук | Алена | 1997-02-28 |
Кроме того, что мы получили не все столбцы, мы дополнительно изменили их порядок на тот, который нам удобен. В оригинальной таблице first_name стоит перед last_name, а у нас наоборот.
Еще обратите внимание, что результатом работы SQL запроса является таблица. То есть мы берем исходную таблицу, которая хранится в базе, и с помощью SQL запроса получаем другую таблицу — с теми данными, которые нам нужны.
И часто требуется получить не все данные, а только те, которые соответствуют какому-то условию. Давайте снова изменим наш SQL-запрос, чтобы он стал таким:
SELECT last_name, first_name, birthday FROM users WHERE age > 18Если его выполнить, то мы получим список пользователей которым уже исполнилось 19 лет:
| id | last_name | first_name | birthday |
|---|---|---|---|
| 1 | Иванов | Дмитрий | 1996-12-11 |
| 3 | Шевченко | Тимур | 1998-04-27 |
| 4 | Иванова | Светлана | 1993-08-06 |
| 6 | Иванов | Алексей | 1993-08-05 |
Конструкция WHERE позволяет фильтровать исходные данные в соответствии с нашими условиями. В данном случае мы получаем данные из таблицы users ГДЕ (WHERE) в столбце age значение больше 18.
Так как age — это числовой столбец, то его уместно сравнивать с числами. Если заменить знак больше на равно и снова запустить, то получим всех 18 летних пользователей. А если поставим >= , то получим совершеннолетних пользователей:
SELECT last_name, first_name, birthday FROM users WHERE age >= 18| id | last_name | first_name | birthday |
|---|---|---|---|
| 1 | Иванов | Дмитрий | 1996-12-11 |
| 3 | Шевченко | Тимур | 1998-04-27 |
| 4 | Иванова | Светлана | 1993-08-06 |
| 6 | Иванов | Алексей | 1993-08-05 |
| 7 | Процук | Алена | 1997-02-28 |
Как видите SQL запросы просто составлять и читать. Язык создавался для того, чтобы им могли пользоваться люди, которые не умеют программировать: менеджеры, аналитики, маркетологи. В том числе начинающие специалисты.
А теперь самое время потренироваться в SQL, для этого к каждому уроку привязано несколько задач, которые вы можете решать в специальном тренажере прямо на сайте.
Следующий урок
Урок 2. Составные условия
В этом уроке вы узнаете как формировать сложные условия в SQL-запросах с использованием операторов AND и OR.
Полный курс с практикой
- 57 уроков
- 261 задание
- Сертификат
- Поддержка преподавателя
- Доступ к курсу навсегда
- Можно в рассрочку
SQL для начинающих: 10 правил построения «точных» запросов
Научимся писать SQL-запросы, которые будут предоставлять данные в нужном объёме и за минимальное время.
Денис Карпов
Отдел автоматизации процессов информационных технологий
«Точный» 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; /* 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-запроса.
- Используй копию данных для построения отчётности.
- Проверяй формат данных.
От автора
Подходов к оптимизации великое множество. Цель статьи — пробудить интерес искать и находить места роста производительности и снижения издержек. И помните Зако́н Ме́рфи: «Если что-нибудь может пойти не так, оно пойдёт не так».
Следите за новыми постами по любимым темам
Подпишитесь на интересующие вас теги, чтобы следить за новыми постами и быть в курсе событий.
SQL-запросы по-быстрому: краткий и понятный гайд
SQL (Structured Query Language) — это язык структурированных запросов. Он позволяет читать, записывать, удалять, сортировать и фильтровать информацию в базе данных.
Інноваційний курс від robotdreams: Android Developer.
Творіть для мобільного світу.
В SQL используется немного слов. Он напоминает человеческий язык и поэтому его легко изучить. С его помощью можно работать с реляционными базами данных: пользователь отправляет SQL-запрос к базе данных через систему управления базами данных (СУБД). Последняя обрабатывает запрос и отправляет полученные данные пользователю.
Структура SQL-запроса
Запрос на выборку данных выглядит вот так:
SELECT ('столбцы через запятую или символ * для выбора всех столбцов') FROM ('таблицы через запятую') WHERE ('условие или фильтр') GROUP BY ('столбцы через запятую, по которым нужно сгруппировать данные') HAVING ('условие в уже сгрупированных данных') ORDER BY ('столбцы через запятую, по которым нужно отсортировать вывод')
Рассмотрим подробнее, как производится выборка.
SELECT и FROM
SELECT и FROM — обязательные ключевые слова в этом запросе. С их помощью можно указать, откуда и какие данные можно выбрать:
Професійний курс від mate.academy: Java.
Погрузьтеся у світ програмування.
- К примеру, выбрать фамилии сотрудников из таблицы Employees:
SELECT last_name FROM Employees
- Получить только фамилию и размер зарплаты из этой же таблицы:
SELECT last_name, salary FROM Employees
Обратите внимание: имена столбцов указываются через запятую.
-
Выбрать все столбцы из таблицы Employees:
SELECT * FROM Employees
Для выборки всех столбцов применяется групповой символ «*». При его использовании столбцы будут возвращены, но иногда порядок может не соблюдаться.
Групповой символ упрощает запрос, но при этом снижает производительность. Поэтому лучше использовать его в редких случаях.
WHERE
Обычно нам нужна определенная информация из таблицы. Но как ее быстро найти? WHERE помогает извлечь информацию, отфильтровав ее по одному или нескольким условиям. Это очень удобно!
С WHERE применяются такие операции:
Практичний курс від skvot: Артменеджер.
Управляйте творчим процесом.
Некоторые из операций приведены в нескольких вариантах, потому что в разных СУБД они указываются по-разному. Чтобы узнать, какие операции используются в вашей СУБД — смотрите ее документацию.
Теперь вернемся к практике. Например, вам нужно выбрать фамилии сотрудников с зарплатой свыше 1000. Применим WHERE:
SELECT last_name FROM Employees WHERE salary > 1000
Если требуется указать значение строки , заключите его в апострофы:
Експертний курс від mate.academy: IT Рекрутмент Вечірній.
Експертний курс від mate.academy: IT Рекрутмент Вечірній.
SELECT * FROM Employees WHERE department = 'Sales'
Фильтр по нескольким условиям
Данные можно фильтровать не только по одному, а и по нескольким условиям и значениям. Для этого используются операторы IN, NOT IN, AND, OR.
- Отфильтровать по нескольким значениям с дополнительными условиями:
SELECT * FROM Employees WHERE department IN ('IT', 'Marketing')
В результате этого запроса будут выбраны все сотрудники из подразделений ИТ и маркетинга.
- Отфильтровать по нескольким значениям с исключением:
SELECT * FROM Employees WHERE department NOT IN ('IT', 'Marketing')
Будут выбраны все сотрудники, кроме тех, кто работает в подразделениях ИТ и маркетинга.
- Выбрать сотрудников из ИТ-подразделения с зарплатой свыше 1000:
SELECT * FROM Employees WHERE department = 'IT' AND salary > 1000
- Выбрать сотрудников из ИТ-подразделения или с зарплатой свыше 1000:
SELECT * FROM Employees WHERE department = 'IT' OR salary > 1000
GROUP BY
С помощью необязательного предложения GROUP BY создаются группы данных. Это удобно для получения итоговых значений. Например, нужно узнать, сколько человек работает в отделе продаж. Инструкция может выглядеть так:
SELECT department, COUNT (*) AS cnt FROM Employees GROUP BY department
Этот код возвращает названия подразделений и количество работников в каждом из них. Количество сотрудников помещается в столбец с псевдонимом cnt, который мы задали с помощью ключевого слова AS.
Предложение GROUP BY указывается после WHERE и перед ORDER BY.
В GROUP BY можно указать столько столбцов, сколько нужно. В результате группы вкладываются друг в друга.
При вложении данные будут суммироваться для последней заданной группы, а не для отдельно для каждого столбца.
В предложении GROUP BY можно указать только столбцы выборки или выражения. В нем не указывается функция группирования и не применяются псевдонимы.
Если в столбце, по которому производится группирование, встречается одна или несколько строк со значением NULL, они выделяются в отдельную группу.
HAVING
С помощью предложения GROUP BY можно также указывать, какие группы включить в результат, а какие — исключить из него. Для этого используется предложение HAVING. Оно очень напоминает WHERE, но фильтрует не строки, а группы.
HAVING можно использовать с любыми операторами. В этом предложении используется тот же синтаксис, что и в предложении WHERE:
SELECT department, COUNT (*) AS cnt FROM Employees GROUP BY department HAVING COUNT(*) >= 3
Этот код похож на предыдущий, но возвращает только те группы, в которых найдены три или больше сотрудников. Фильтрация выполняется по итоговому значению группы. Этим HAVING отличается от WHERE, которое фильтрует по значениям строк.
Эти предложения можно использовать вместе. Например, можно узнать, сколько сотрудников в подразделениях со штатом более трех человек, получают более 1000:
SELECT department, COUNT (*) AS cnt FROM Employees WHERE salary > 1000 GROUP BY department HAVING COUNT(*) >= 3
Сначала выбираются все строки, где в столбце salary содержатся значения больше 1000. А затем выбираются только те группы, в которых не меньше трех записей.
ORDER BY
Предложение ORDER BY используется для сортировки результатов запроса. В нем указываются имена столбцов, по которым нужна сортировка.
Давайте отсортируем список фамилий сотрудников:
SELECT last_name FROM Employees ORDER BY last_name
В предложении ORDER BY можно указывать и те столбцы, которые не выбраны в операторе SELECT:
SELECT last_name FROM Employees ORDER BY salary
Так список фамилий сотрудников будет отсортирован по размеру зарплаты.
Сортировку можно выполнять и по нескольким столбцам. Для этого имена столбцов указывают через запятую:
SELECT first_name, last_name FROM Employees ORDER BY last_name, first_name
Так мы увидим список сотрудников, который сначала отсортирован по фамилии, а затем — по имени.
Вместо имен столбцов можно указать их порядковые номера в операторе SELECT:
SELECT first_name, last_name FROM Employees ORDER BY 2, 1
Этот код также возвращает список сотрудников с сортировкой по фамилии, а затем — по имени.
Сортировка по убыванию
В предыдущих примерах мы сортировали по возрастанию (это делается по умолчанию). Но можно сортировать и по убыванию. Для этого укажем слово DESC:
SELECT first_name, last_name FROM Employees ORDER BY last_name DESC
Так мы отсортируем список с именами и фамилиями в обратном алфавитном порядке.
Если обратная сортировка выполняется по нескольким столбцам, укажите ключевое слово DESC после каждого из них.
Слово DESC — это сокращение от слова DESCENDING. В запросах можно использовать как полную, так и сокращенную форму. Для сортировки в порядке возрастания тоже существует ключевое слово. Его полная форма — ASCENDING, а сокращенная — ASC. Поскольку по умолчанию выполняется сортировка по возрастанию, то это слово не указывают.
Объединение таблиц
Иногда нам нужны данные из нескольких таблиц. Рассмотрим пример:
SELECT first_name, last_name, order_id FROM Employees, Orders WHERE Employees. employee_id = Orders.employee_id
Этот код возвратит имена и фамилии сотрудников из таблицы Employees и номера заказов из таблицы Orders, которые выполнены соответствующими сотрудниками. В предложении WHERE имена столбцов указаны с именами соответствующих таблиц. Это необходимо, чтобы СУБД могла различать столбцы employee_id из разных таблиц.
Такое объединение называется внутренним. Для него можно использовать специальный синтаксис с ключевым словом INNER JOIN. Приведенный ниже код выдаст те же результаты, что и предыдущий фрагмент:
SELECT first_name, last_name, order_id FROM Employees INNER JOIN Orders ON Employees.employee_id = Orders.employee_id
Вместо предложения WHERE используется предложение ON, синтаксис которого совпадает с синтаксисом WHERE.
Число объединяемых таблиц в SQL не ограничено, но может ограничиваться в разных СУБД. Обратите внимание: чем больше таблиц объединяется, тем ниже производительность. Поэтому не рекомендуем объединять таблицы без особой необходимости.
Вместо заключения
SQL — простой для освоения и при этом мощный язык. Он появился в 1970-х и до сих пор используется, хотя наряду с ним появляются новые похожие языки. Этот язык используется различными СУБД: MySQL, SQLite, Oracle Database, Microsoft Access, Microsoft SQL Server, dBASE, IBM DB2.
Сегодня SQL — не просто язык формирования запросов. С его помощью можно упорядочивать и изменять данные, делать выборки, управлять доступом к ним, совместно использовать информацию и обеспечивать ее целостность. Пользуйтесь!
SQL-запросы: виды и механизм работ
Что это такое? SQL-запросы необходимы для работы с информацией из базы данных. Это может быть внесение, извлечение, сортировка, удаление и ряд других операций. При этом не указывается способ осуществления запрашиваемого действия.
Где применяется? Так как разного рода информация присутствует во многих сферах деятельности, то SQL-запросы применяются как в работе с онлайн-ресурсами, так и с программами и приложениями.
В статье рассказывается:
- Структура базы данных
- Механизм работы SQL-запроса
- Виды SQL-запросов
- Примеры SQL-запросов
Пройди тест и узнай, какая сфера тебе подходит:
айти, дизайн или маркетинг.
Бесплатно от Geekbrains
Структура базы данных
Прежде всего, давайте рассмотрим, что представляет собой база данных и каковы особенности ее иерархии.
База данных SQL есть не что иное, как совокупность сведений, хранящихся в виде таблицы. Наименования ее столбцов отражают краткую характеристику данных, в то время как в строках прописывается полная информация. Поле – это компонент структуры таблицы, содержащий данные об одном аспекте ее элемента.
Допустим, что у компании имеется несколько баз данных. Для того чтобы можно было увидеть их полный перечень, введем команду: SHOW DATABASES, после чего произойдет подключение к базе данных сотрудников.
Полученный результат выглядит подобным образом:
Узнай, какие ИТ — профессии
входят в ТОП-30 с доходом
от 210 000 ₽/мес
Павел Симонов
Исполнительный директор Geekbrains
Команда GeekBrains совместно с международными специалистами по развитию карьеры подготовили материалы, которые помогут вам начать путь к профессии мечты.
Подборка содержит только самые востребованные и высокооплачиваемые специальности и направления в IT-сфере. 86% наших учеников с помощью данных материалов определились с карьерной целью на ближайшее будущее!
Скачивайте и используйте уже сегодня:
Павел Симонов
Исполнительный директор Geekbrains
Топ-30 самых востребованных и высокооплачиваемых профессий 2023
Поможет разобраться в актуальной ситуации на рынке труда
Подборка 50+ бесплатных нейросетей для упрощения работы и увеличения заработка
Только проверенные нейросети с доступом из России и свободным использованием
ТОП-100 площадок для поиска работы от GeekBrains
Список проверенных ресурсов реальных вакансий с доходом от 210 000 ₽
Получить подборку бесплатно
Уже скачали 23658
Также одна база данных может состоять из нескольких таблиц. В таком случае запрос SHOW TABLES in employees позволит увидеть полный их список. Визуально это выглядит примерно так:
Структуру каждой таблицы формирует различный набор столбцов, в которых описываются данные.
Увидеть их можно с помощью выполнения SQL-запроса Describe engineering. Допустим, таблица содержит столбцы, в которых определен один конкретный признак, к примеру, employee_id, first_name, last_name, email, country и salary.
| Name | Null | Type |
|EMPLOYEE_ID| NOT NULL | INT(6) |
|FIRST_NAME | NOT NULL |VARCHAR2(20) |
|LAST_NAME | NOT NULL |VARCHAR2(25) |
|EMAIL | NOT NULL |VARCHAR2(255) |
|COUNTRY | NOT NULL |VARCHAR2(30) |
|SALARY | NOT NULL |DECIMAL(10,2) |
Для вас подарок! В свободном доступе до 29.10 —>
Скачайте ТОП-10
бесплатных нейросетей
для программирования
Помогут писать код быстрее на 25%
Чтобы получить подарок, заполните информацию в открывшемся окне
Строки таблицы, в которых отражена основная информация, называются записями. То есть, они содержат сведения, соответствующие наименованию столбцов (employee_id, first_name, last_name, e-mail, salary и country). Другими словами, в нашем примере строки определяют и выводят информацию об одном сотруднике из группы.
Механизм работы SQL-запроса
Чтобы правильно сформировать SQL-запрос и получить ожидаемый результат, следует четко понимать процесс его выполнения.
Итак, первое действие, которые совершает программа – это грамматическая разбивка и построение синтаксического дерева запроса. Анализ необходим для того, чтобы определить соответствие SQL-запроса требованиям синтаксиса и семантики. С помощью парсера формируется внутреннее определение команды, которое далее поступает обработчику кода.
После этого оптимизатор выстраивает оптимальный план выполнения запроса, который помогает выявить алгоритм, использующийся для проведения каждой операции, а также контролирует их исполнение.
Прежде чем оптимальный план будет найден, оптимизатор просчитывает все допустимые варианты выполнения, дает оценку качеству и стоимости каждого из возможных планов, анализирует информацию об актуальном состоянии базы данных. Разумеется, работа оптимизатора не всегда может стать идеальной. Именно по этой причине, пользователи и администраторы баз данных нередко вынуждены производить проверку и настройку предложенного плана вручную. Это необходимо для повышения производительности выполнения SQL-запроса.
Дарим скидку от 60%
на обучение «Разработчик» до 29 октября
Уже через 9 месяцев сможете устроиться на работу с доходом от 150 000 рублей
Так какой же план может считаться идеальным и пригодным для выполнения?
Повторим, что одним из критериев отбора становится оценка стоимости, в частности, мы говорим о таких аспектах, как число дисковых операций ввода-вывода, стоимость процессора, время, требующееся на отклик и выполнение процесса в целом. Специалисты определяют это как временные сложности.
Результатом обработки запроса данных SQL становится запуск в работу выбранного оптимального плана, после чего пользователь может ознакомиться с результатами.
Виды SQL-запросов
Существуют следующие виды запросов в SQL:
- DDL (Data Definition Language). Это язык определения данных, с помощью которого создается база данных и дается описание ее структуры. DDL запрос позволяет настроить правила размещения различной информации в таблице базы данных.
- DML (Data Manipulation Language) запрос – это язык работы с данными. Как правило, применяемые команды нужны для внесения изменений в уже существующие данные, их удаления и сохранения, обновления записей и т.д.