Продвинутый SQL: оптимизация, транзакции, Big Data, безопасность

В этой разделе мы рассмотрим более сложные возможности языка SQL при работе с запросами. Ключевые аспекты, на которых мы сфокусируемся: расширенные конструкции SELECT и JOIN, различные типы подзапросов, а также использование оконных функций. Отдельно поговорим о том, как структурировать запрос, чтобы он был понятным и при этом легко поддавался оптимизации.

1.1. Расширенные возможности SELECT

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

SELECT department_id,
       COUNT(*) AS employee_count,
       AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;

• GROUP BY department_id: Группирует строки таблицы по значению department_id.
• COUNT(*) AS employee_count: Считает количество строк в каждой группе.
• AVG(salary) AS avg_salary: Вычисляет среднюю зарплату в каждой группе.
• HAVING COUNT(*) > 5: Отбирает только те группы, где количество сотрудников больше 5.

1.2. Использование WITH (CTE)

Общий табличный выражения (Common Table Expressions, WITH) помогают разбить сложный запрос на логические части. Это делает код более читаемым и удобным для сопровождения.

WITH high_salary AS (
    SELECT employee_id, salary
    FROM employees
    WHERE salary > 5000
)
SELECT h.employee_id, h.salary
FROM high_salary h
JOIN employees e ON e.employee_id = h.employee_id
WHERE e.department_id = 10;

• WITH high_salary AS (...): Создаёт временную таблицу (CTE) с названием high_salary, содержащую сотрудников с зарплатой выше 5000.
• Основной запрос: Использует high_salary для выполнения JOIN с таблицей employees и фильтрует результаты по department_id = 10.

2.1. Разновидности JOIN

• INNER JOIN: возвращает только те строки, у которых есть совпадающие ключи в обеих таблицах.
• LEFT JOIN: возвращает все строки из левой таблицы и совпадающие из правой. Если в правой таблице нет соответствия, будут значения NULL.
• RIGHT JOIN: аналогично LEFT JOIN, только с правой таблицей.
• FULL OUTER JOIN: возвращает все строки из обеих таблиц, подставляя NULL, если соответствий нет.

SELECT e.employee_id, e.name, d.department_name
FROM employees e
LEFT JOIN departments d 
       ON e.department_id = d.id;

• LEFT JOIN возвращает все строки из таблицы employees (левой таблицы).
• Если для строки в employees нет соответствующей строки в departments, значения для столбцов из departments будут NULL.
• Это удобно для поиска сотрудников, не прикреплённых к департаментам.

2.2. FULL OUTER JOIN:

• FULL OUTER JOIN объединяет результат LEFT JOIN и RIGHT JOIN.

• Пример (если таблица projects хранит проекты, а employees — сотрудников):

  SELECT e.employee_id, e.name, p.project_name
  FROM employees e
  FULL OUTER JOIN projects p 
         ON e.project_id = p.id;
  

  Здесь возвращаются:

  • Все сотрудники, даже если они не привязаны к проектам.
  • Все проекты, даже если у них нет сотрудников.

2.3. SELF JOIN

Само-соединение (SELF JOIN) используется, когда нужно сопоставить строки одной и той же таблицы между собой. Часто это необходимо в иерархических структурах (например, сотрудники и их менеджеры).

SELECT e.name AS employee_name,
       m.name AS manager_name
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id;

• e и m — алиасы одной и той же таблицы employees.
• Каждая строка таблицы связывается сама с собой на основе отношения "сотрудник ↔ менеджер" через e.manager_id = m.employee_id.
• Результат включает:
  • Имя сотрудника (e.name).
  • Имя его менеджера (m.name).

3.1. Обычные подзапросы

Подзапросы позволяют выполнять вложенные SELECT-выражения для фильтрации, вычислений или сопоставлений значений.

SELECT employee_id, name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary) 
    FROM employees
);

• Подзапрос возвращает среднюю зарплату по всем сотрудникам, а внешний запрос выбирает тех, чья зарплата выше этой средней.

3.2. Коррелированные подзапросы

В коррелированных подзапросах вложенный SELECT зависит от данных внешнего запроса.

SELECT e.employee_id, e.name, e.salary
FROM employees e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e.department_id
);

• Каждый раз при выборке строки из employees e во вложенном SELECT идёт фильтрация по департаменту текущего сотрудника e.

Оконные (аналитические) функции дают возможность вычислять агрегаты по группам строк без группировки всего набора данных, а также использовать разные способы разбивки (PARTITION BY) и сортировки (ORDER BY).

4.1. Пример с RANK

Функция RANK() вычисляет позицию строки в рамках определённой группы (окна), учитывая сортировку.

SELECT employee_id,
       department_id,
       salary,
       RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM employees;

• RANK(): Вычисляет ранг строки внутри окна, определённого PARTITION BY department_id.
• PARTITION BY department_id: Разделяет строки на группы по department_id.
• ORDER BY salary DESC: Указывает порядок сортировки внутри каждой группы.
• Одинаковый ранг для одинаковых значений:
  • Если два сотрудника имеют одинаковую зарплату, они получают одинаковый salary_rank, а следующий ранг пропускается (например, 1, 2, 2, 4).

4.2. Функция LAG/LEAD

Позволяет “заглянуть” в предыдущие или последующие строки без дополнительного JOIN.

SELECT employee_id,
       salary,
       LAG(salary, 1, 0) OVER (ORDER BY hire_date) AS prev_salary
FROM employees;

• LAG(salary, 1, 0): Возвращает значение зарплаты из предыдущей строки. Если предыдущей строки нет, возвращается значение по умолчанию (в данном случае 0).
  • Параметры:
    • salary: Столбец, из которого берётся значение.
    • 1: Сколько строк "назад" заглянуть.
    • 0: Значение по умолчанию, если предыдущей строки нет.
• OVER (ORDER BY hire_date): Указывает порядок строк (по дате приёма на работу).

5.1. Правила форматирования

• Отбивка блоков кода: используйте отступы, чтобы отделять SELECT, FROM, JOIN и WHERE.
• Читаемые алиасы: давайте понятные названия алиасам (e для employees, d для departments и т.д.).
• Комментарии: подписывайте логику ключевых частей, чтобы упростить понимание для коллег.

5.2. Разбиение запроса на несколько частей

• Используйте WITH (CTE) там, где логика запроса становится слишком громоздкой.
• Стройте большие запросы по принципу: “один шаг расчёта — одно CTE”.

WITH dept_salary AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
),
high_paid AS (
    SELECT e.employee_id, e.name, e.department_id
    FROM employees e
    JOIN dept_salary d ON e.department_id = d.department_id
    WHERE e.salary > d.avg_salary
)
SELECT * 
FROM high_paid
ORDER BY department_id;

• Такое разбиение позволяет сначала вычислить среднюю зарплату в каждом департаменте (dept_salary), а затем на её основе сформировать выборку высокооплачиваемых сотрудников (high_paid).

Рассмотрим инструменты и подходы, позволяющие повысить скорость выполнения запросов и лучше понять, как СУБД обрабатывает операции. Мы научимся пользоваться оператором EXPLAIN, разберёмся с основными видами индексов и узнаем популярные паттерны оптимизации запросов.

1.1. Назначение EXPLAIN

Оператор EXPLAIN (или его расширения EXPLAIN ANALYZE в некоторых СУБД) показывает, как база данных планирует выполнять запрос. Это позволяет:

• Понять, какие индексы задействованы.
• Определить предполагаемый порядок соединения таблиц.
• Узнать ожидаемую стоимость (cost) выполнения каждого шага.

1.2. Пример использования (PostgreSQL)

EXPLAIN
SELECT e.name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE e.salary > 6000;

Результат может выглядеть примерно так:

Nested Loop  (cost=0.43..8.72 rows=1 width=64)
  ->  Index Scan using employees_salary_idx on employees e  (cost=0.43..4.54 rows=1 width=32)
        Filter: (salary > 6000)
  ->  Index Scan using departments_pkey on departments d  (cost=0.29..4.17 rows=1 width=32)
        Index Cond: (id = e.department_id)

• Index Scan показывает, что оптимизатор применил индекс для фильтрации по salary (employees_salary_idx) и для соединения по ключу department_id.
• Nested Loop указывает на выбранный тип соединения (выполняется итеративное соединение по ключам).

Если использовать EXPLAIN ANALYZE, система реально выполнит запрос и покажет фактические показатели (время, количество возвращённых строк), что позволяет сравнить план с реальной статистикой.

2.1. Основные типы индексов

• B-Tree индекс: самый распространённый тип; эффективен для точного поиска, диапазонных запросов и сортировки.
• Hash индекс: специализируется на точном сравнении (=), быстро ищет точное совпадение, но неэффективен для других операций.
• GIN/GiST (для PostgreSQL): применяются для полнотекстового поиска, геоданных и сложных типов.

2.2. Создание индексов

Создание индекса в SQL обычно осуществляется так:

CREATE INDEX idx_salary ON employees(salary);

Индекс по полю salary ускорит операции WHERE salary > ..., WHERE salary = ... и т.д. Однако стоит помнить, что избыточное количество индексов замедляет операции вставки и обновления, так как индексы нужно поддерживать в актуальном состоянии.

2.3. Сложные индексы

Можно создавать составные (многоколонковые) индексы, если часто используется фильтрация сразу по нескольким полям:

CREATE INDEX idx_dept_salary
    ON employees (department_id, salary);

Однако в большинстве случаев порядок колонок в индексе имеет значение (сначала department_id, потом salary). При запросе типа:

WHERE department_id = 10 AND salary > 6000

такой индекс может быть очень эффективным, а при фильтрации только по salary — уже не всегда.

3.1. Сокращение объёма данных

• Использование нужных колонок: вместо SELECT * выбирайте конкретные поля.
• Возможность агрегации: если нужны итоги, используйте группировку (GROUP BY), чтобы получить меньше строк.

3.2. Правильная разбивка (партиционирование) таблиц

Если таблица содержит очень много строк, имеет смысл хранить данные в разделах (партициях) по датам, регионам или другим критериям. Это ускоряет выборку, так как СУБД может сосредоточиться на нужных партициях.

3.3. Переписывание запросов

Иногда перестройка логики запроса даёт больший выигрыш, чем добавление индексов. Например, заменив сложный коррелированный подзапрос на JOIN с группировкой, мы можем снизить нагрузку на сервер.

Вместо:

SELECT e.employee_id, e.name, e.salary
FROM employees e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e.department_id
);

Иногда выгоднее:

WITH avg_dept_salary AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT e.employee_id, e.name, e.salary
FROM employees e
JOIN avg_dept_salary a ON e.department_id = a.department_id
WHERE e.salary > a.avg_salary;

Так можно задействовать индексы и упростить план выполнения.

3.4. Использование правильных типов данных

Старайтесь, чтобы типы данных колонок в условии соответствовали передаваемым параметрам (например, не хранить даты в строковых полях, не сравнивать числа с текстом). Если типы не совпадают, может происходить неявное преобразование, которое усложняет использование индексов.

1. Статистика: регулярное обновление статистики (ANALYZE в PostgreSQL или аналогичные команды в других СУБД) помогает оптимизатору выбирать лучший план.
2. Профайлинг: многие СУБД позволяют собирать более детальные сведения о выполнении запросов. Например, PostgreSQL даёт возможность посмотреть BUFFERS или TIMING для диагностики узких мест.
3. Мониторинг: системные метрики (CPU, память, диск) также влияют на производительность. Даже самый оптимизированный запрос будет работать медленно при постоянном свопе или нехватке оперативной памяти.

В этой главе рассмотрим механизмы, которые позволяют нескольким пользователям одновременно работать с базой данных, не нарушая целостность данных. Мы разберём принципы ACID, уровни изоляции транзакций и то, как СУБД управляет блокировками. Кроме того, узнаем, как избежать ситуаций взаимной блокировки (deadlock).

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

1. Atomicity (Атомарность)
   Все операции внутри транзакции рассматриваются как единое целое: либо они все успешно завершаются (COMMIT), либо отменяются (ROLLBACK), возвращая систему в исходное состояние.
2. Consistency (Согласованность)
   Каждая завершённая транзакция сохраняет базу данных в согласованном состоянии, удовлетворяя всем бизнес-правилам, ограничениям целостности и триггерам.
3. Isolation (Изоляция)
   Действия одной транзакции не должны быть видны или влиять на другую до тех пор, пока первая не будет зафиксирована (COMMIT). Существует несколько уровней изоляции, которые определяют, как именно обеспечивается эта невидимость.
4. Durability (Надёжность)
   После фиксации транзакции (COMMIT) её результаты должны быть сохранены и не потеряются даже в случае сбоя системы. Это достигается записью журнала транзакций на диск.

Разные СУБД предлагают несколько уровней изоляции, каждый из которых решает свои задачи и влияет на производительность.

2.1. READ UNCOMMITTED

• Характеристики: Транзакция может видеть “грязные” данные, то есть изменения, которые другая транзакция ещё не зафиксировала.
• Проблемы: Возможны “грязные” чтения (dirty reads).

2.2. READ COMMITTED

• Характеристики: Чтение только зафиксированных данных. Если другая транзакция изменила данные и сделала COMMIT, вы можете увидеть обновлённое значение при повторном чтении.
• Проблемы: Возможны “неповторяющиеся” чтения (non-repeatable reads), т. е. при повторном чтении данных их значение может измениться.

2.3. REPEATABLE READ

• Характеристики: Все чтения в рамках одной транзакции “замораживаются” на момент её старта — повторные чтения дают одинаковый результат.
• Проблемы: В некоторых СУБД (например, в MySQL InnoDB до определённых версий) возможны фантомные чтения (phantom reads), когда появляется новая строка, удовлетворя условию выборки, которая ранее отсутствовала.

2.4. SERIALIZABLE

• Характеристики: Максимальная изоляция — транзакции выполняются так, будто работают последовательно одна за другой.
• Проблемы: Высокая нагрузка на блокировки и частые конфликты при параллельном выполнении, что может приводить к снижению производительности.

Блокировки (locks) — один из основных механизмов, который обеспечивает изоляцию транзакций.

3.1. Типы блокировок

• Блокировка на уровне строки (row-level lock): блокируется конкретная запись. Это самый распространённый способ для большинства OLTP-систем.
• Блокировка на уровне страницы (page-level lock): блокируется страница данных (обычно 8 KB и более, в зависимости от СУБД).
• Блокировка на уровне таблицы (table-level lock): может устанавливаться при массовых операциях (например, при ALTER TABLE).

3.2. Режимы блокировок

• Shared lock (S): несколько транзакций могут читать одну и ту же строку одновременно, но не изменять её.
• Exclusive lock (X): транзакция полностью блокирует запись для чтения и записи другими транзакциями.

Также существуют дополнительные режимы (например, Intent locks в Microsoft SQL Server или Row Share/Row Exclusive в Oracle), используемые для более тонкого управления конкурентностью.

4.1. Основные операторы

• BEGIN (или START TRANSACTION): начало транзакции.
• COMMIT: фиксация изменений.
• ROLLBACK: откат всех изменений с начала транзакции.

Пример (PostgreSQL):

BEGIN;

-- Уменьшаем баланс на счёте 1
UPDATE accounts
SET balance = balance - 100
WHERE account_id = 1;

-- Проверяем, если баланс стал отрицательным
IF EXISTS (SELECT 1 FROM accounts WHERE account_id = 1 AND balance < 0) THEN
    ROLLBACK;
    RAISE EXCEPTION 'Ошибка: недостаточно средств на счёте 1';
END IF;

-- Увеличиваем баланс на счёте 2
UPDATE accounts
SET balance = balance + 100
WHERE account_id = 2;

-- Проверяем, существует ли счёт 2
IF NOT EXISTS (SELECT 1 FROM accounts WHERE account_id = 2) THEN
    ROLLBACK;
    RAISE EXCEPTION 'Ошибка: счёт 2 не найден';
END IF;

-- Если всё успешно, фиксируем транзакцию
COMMIT;

• Мы уменьшаем баланс на счёте 1 и увеличиваем на счёте 2. Если какой-то из апдейтов не сработал (например, ошибка ограничения), мы можем выполнить ROLLBACK и вернуть систему в состояние “до транзакции”.

Взаимная блокировка (deadlock) возникает, когда две транзакции ждут друг друга, каждая удерживая ресурсы, необходимые другой.

5.1. Пример deadlock-ситуации

1. Транзакция A блокирует строку X, транзакция B блокирует строку Y.
2. Транзакция A пытается заблокировать Y, но ждет, пока B освободит её.
3. Транзакция B пытается заблокировать X, но ждет, пока A освободит её.

Обе транзакции ждут бесконечно.

5.2. Стратегии предотвращения

1. Зафиксированный порядок доступа: всегда изменяйте таблицы/строки в определённой последовательности (например, сначала по возрастанию ID), чтобы исключить циклы.
2. Явные блокировки (SELECT ... FOR UPDATE): лучше контролировать порядок получения блокировок.
3. Короткие транзакции: чем меньше логики внутри одной транзакции, тем ниже риск попасть в deadlock.
4. Настройки СУБД: многие системы сами обнаруживают и устраняют deadlock, откатывая одну из транзакций (deadlock victim).

В этой главе мы рассмотрим, каким образом SQL находит себе применение в мире больших данных, где объёмы информации не помещаются в привычные рамки классической реляционной СУБД. Обсудим ключевые принципы, лежащие в основе Apache Hive и Spark SQL, а также особенности распределённых запросов и причины, по которым такие решения становятся всё более популярны.

1.1. Краткий обзор

Когда данные выходят за пределы возможностей традиционных реляционных СУБД (по объёму или нагрузке), на сцену выходят экосистемы Big Data. К ним относятся:

• Apache Hadoop: фреймворк для распределённого хранения и обработки больших данных.
• Apache Hive: поверх Hadoop предлагает SQL-подобный язык (HiveQL) для чтения и анализа данных.
• Apache Spark: платформа для кластерных вычислений с поддержкой Spark SQL.
• Apache Drill, Presto, Apache Impala и другие системы, позволяющие выполнять SQL-запросы к распределённым источникам данных.

1.2. Преимущества SQL в Big Data

• Знакомый синтаксис: миллионы разработчиков и аналитиков уже владеют SQL, поэтому обучения и адаптации требуется меньше.
• Гибкость: SQL может применяться для широкого круга задач: от простых выборок до сложной аналитики.
• Высокий уровень абстракции: пользователям не нужно детально понимать механику MapReduce или Spark — движок SQL сам определяет план выполнения.

2.1. Архитектура Hive

Hive хранит свои данные в распределённой файловой системе (HDFS). Запросы, написанные на HiveQL (расширение SQL), преобразуются в задачи MapReduce или Spark. Благодаря этому можно анализировать огромные наборы данных, не заботясь о ручном написании MapReduce-кода.

2.2. Основные концепции

• Таблицы и базы: логическая организация данных в виде таблиц; физически данные могут быть в файлах различных форматов (Text, ORC, Parquet).
• Partition & Buckets: для оптимизации чтения большие таблицы делятся на части (партиции) по ключу (например, по дате). Бакеты (buckets) позволяют дополнительную сегментацию внутри партиции.
• Метастор (Metastore): служба, где Hive хранит метаданные: структуру таблиц, пути к файлам и типы столбцов.

2.3. Пример запроса HiveQL

SELECT region, COUNT(*) AS cnt
FROM sales
WHERE year = 2024
GROUP BY region;

Hive сконвертирует этот запрос в набор задач, которые выполнятся на кластере Hadoop, вернув агрегированные результаты по регионам.

3.1. Особенности Spark SQL

Spark SQL также позволяет обращаться к данным через SQL-синтаксис. При этом “под капотом” используются высокопроизводительные механизмы Spark для кластерных вычислений в памяти.

Преимущества:

• Высокая скорость: благодаря механизму in-memory вычислений Spark работает быстрее классического MapReduce.
• DataFrames и Datasets: помимо SQL, Spark предлагает API для работы со структурированными данными на различных языках (Python, Scala, Java).

3.2. Пример кода на Spark SQL (Scala)

val spark = SparkSession.builder
  .appName("Spark SQL Example")
  .getOrCreate()

// Загрузка данных из CSV
val df = spark.read
  .option("header", "true")
  .csv("/path/to/sales.csv")

// Регистрация DataFrame как временной таблицы
df.createOrReplaceTempView("sales")

val result = spark.sql("""
  SELECT region, COUNT(*) AS cnt
  FROM sales
  WHERE year = 2024
  GROUP BY region
""")

result.show()

Внутри вызывается движок Spark, который распределяет вычисления по узлам кластера.

4.1. Принцип распределения

В классических СУБД (PostgreSQL, MySQL, Oracle) план выполнения запроса обычно строится на одном сервере. В случае Big Data система разбивает запрос на подзадачи, которые параллельно обрабатываются множеством узлов. Итоговые результаты затем объединяются.

4.2. Планировщик (Query Planner)

Распределённые системы используют планировщик (Query Planner), который решает, как эффективно разбить задачу между узлами. При этом учитываются:

• Размер партиций
• Ресурсы узлов
• Сетевые затраты

4.3. Сложности распределённых систем

1. Сеть: основное “узкое место” — передача больших объёмов данных между узлами.
2. Отказоустойчивость: при сбое отдельных узлов система должна уметь восстановить вычисления.
3. Балансировка нагрузки: равномерное распределение работы между узлами.

Выбор между классическим SQL и Big Data-решениями

1. Объём данных: если данные легко умещаются на одном сервере, нет смысла усложнять архитектуру.
2. Тип нагрузки: для скоростных транзакций (OLTP) больше подходят классические СУБД. Для аналитики огромных массивов (OLAP) — Hadoop и Spark.
3. Сложность инфраструктуры: Big Data-системы требуют кластеров, распределённых файловых систем и навыков DevOps.

В этой главе разберём, как создавать и использовать хранимые процедуры и пользовательские функции в реляционных базах данных. Посмотрим, как они помогают инкапсулировать бизнес-логику, сократить объём сетевых передач и повысить производительность.

• Хранимая процедура (Stored Procedure)
  Чаще всего не возвращает значения напрямую (хотя может передавать их через выходные параметры), но может выполнять любые SQL-операции (SELECT, INSERT, UPDATE, DELETE, DDL).
• Пользовательская функция (User-Defined Function, UDF)
  Возвращает скалярное значение (число, строку, дату и пр.) или табличный результат. Функции обычно ограничены в части модификации данных (не могут выполнять UPDATE и DELETE в большинстве СУБД).

2.1. Пример в PostgreSQL (PL/pgSQL)

CREATE OR REPLACE PROCEDURE update_salary(
    p_employee_id INT,
    p_new_salary NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE employees
    SET salary = p_new_salary
    WHERE employee_id = p_employee_id;
END;
$$;

• Параметры: p_employee_id и p_new_salary.
• Тело: обновление строки в таблице employees.
• В некоторых СУБД вместо ключевого слова PROCEDURE используется CREATE PROCEDURE ..., а синтаксис тела может отличаться.

2.2. Вызов процедуры

CALL update_salary(101, 7000);

• Теперь для сотрудника с employee_id = 101 зарплата обновится до 7000.

3.1. Скалярная функция (возвращает одно значение)

Пример (T-SQL, Microsoft SQL Server):

CREATE FUNCTION fn_total_price(
    @orderId INT
)
RETURNS DECIMAL(10, 2)
AS
BEGIN
    DECLARE @total DECIMAL(10, 2);

    SELECT @total = SUM(price * quantity)
    FROM order_items
    WHERE order_id = @orderId;

    RETURN @total;
END;

• Функция fn_total_price принимает @orderId и возвращает итоговую стоимость по данному заказу.
• В PostgreSQL аналогичная функция может выглядеть немного иначе по синтаксису, но идея та же.

Использование функции:

SELECT dbo.fn_total_price(1234) AS total_price;

• Получаем итоговую стоимость заказа с ID 1234.

3.2. Табличная функция (возвращает набор строк)

Пример (PostgreSQL):

CREATE OR REPLACE FUNCTION get_high_salary_employees(
    min_salary NUMERIC
)
RETURNS TABLE (
    employee_id INT,
    name TEXT,
    salary NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT employee_id, name, salary
    FROM employees
    WHERE salary > min_salary;
END;
$$;

• Эта функция возвращает набор строк, содержащих только три столбца: employee_id, name и salary. Строки фильтруются по условию, что зарплата (salary) больше значения min_salary.
• В T-SQL (Microsoft SQL Server) для табличных функций может использоваться RETURNS TABLE и RETURN SELECT ....

Использование функции:

SELECT * 
FROM get_high_salary_employees(5000);

• Возвращает список сотрудников с зарплатой выше 5000.

4.1. Входные и выходные параметры

• Входной параметр: принимает значение при вызове, внутри процедуры или функции это значение может использоваться для фильтрации, расчётов и т. п.
• Выходной параметр (OUT) в некоторых СУБД: может использоваться для возвращения одного или нескольких значений из процедуры (чаще встречается в Oracle и MS SQL Server).

Пример (PL/SQL, Oracle):

CREATE OR REPLACE PROCEDURE get_employee_name(
    p_employee_id IN NUMBER,
    p_employee_name OUT VARCHAR2
)
AS
BEGIN
    SELECT name
    INTO p_employee_name
    FROM employees
    WHERE employee_id = p_employee_id;
END;
/

• p_employee_id — входной (IN) параметр.
• p_employee_name — выходной (OUT) параметр, туда записывается результат.

5.1. Инкапсуляция бизнес-логики

Вместо того чтобы размазывать логику по клиентскому коду или приложению, многие операции проще держать в процедурном коде СУБД. Например, в интернет-магазине может быть процедура, которая:

1. Сохраняет заказ.
2. Уменьшает остаток на складе.
3. Создаёт запись в журнале транзакций.

Таким образом, весь процесс покупки оформлен как одна транзакция на уровне базы данных.

5.2. Предварительная проверка данных

В некоторых проектах логично поместить логику в процедуру, которая проверяет данные перед вставкой/обновлением, чтобы повысить надёжность. Например, процедура add_employee, которая:

• Проверяет, есть ли уже сотрудник с таким e-mail.
• Проверяет, не превышен ли лимит по сотрудникам в отделе.
• Только потом вставляет запись в таблицу employees.

5.3. Ускорение и снижение сетевой нагрузки

Когда большой объём изменений происходит “разом”, выгоднее вызывать одну процедуру, которая выполнит все UPDATE и INSERT на стороне сервера, чем гонять множество отдельных запросов по сети.

1. Соблюдайте стандарты: используйте согласованные имена (префиксы sp_, fn_ и т. д.), комментируйте логику.
2. Избегайте бизнес-логики на уровне базы, если планируете микросервисную архитектуру: иначе придётся дублировать её во многих СУБД при масштабировании.
3. Учитывайте тонкости СУБД: разные движки по-разному обрабатывают транзакции внутри процедур (особенно Oracle vs PostgreSQL vs MS SQL).
4. Тестируйте производительность: иногда избыточная сложность внутри одной процедуры может стать “узким местом”.

В этой главе рассмотрим основные аспекты безопасности в SQL-базах: от управления правами с помощью команд GRANT и REVOKE до шифрования данных и общих подходов к защите информации. Основная цель – понять, как обеспечить конфиденциальность, целостность и доступность данных, не усложняя при этом жизнь администраторам и пользователям.

1.1. Роли и пользователи

В большинстве СУБД безопасность строится на связке “пользователи – роли – привилегии”.

• Пользователи (users): конкретные аккаунты, часто привязанные к сотрудникам или приложениям.
• Роли (roles): наборы прав доступа, которые можно назначать пользователям для удобства управления.

1.2. Команды GRANT и REVOKE

• GRANT: предоставляет пользователю или роли определённые права.
• REVOKE: отбирает ранее выданные права.

Пример (PostgreSQL):

-- Создаём роль (role) readonly, которая может только читать данные
CREATE ROLE readonly;

-- Назначаем права SELECT на таблицу employees
GRANT SELECT ON employees TO readonly;

-- Привязываем пользователя user_test к роли readonly
GRANT readonly TO user_test;

-- Если нужно отозвать права:
REVOKE SELECT ON employees FROM readonly;

• Так достигается модель управления доступом, когда права назначаются не напрямую пользователям, а через роли.

1.3. Уровни прав

• Уровень базы данных: можно выдавать права на создание/удаление таблиц, изменение структуры и др.
• Уровень таблицы/схемы: доступ на чтение (SELECT), изменение (INSERT, UPDATE, DELETE).
• Уровень столбца (в некоторых СУБД): более тонкий контроль, если нужно ограничить видимость определённых полей.

2.1. Шифрование “на уровне” соединения

• SSL/TLS: используется для безопасной передачи данных между клиентом и сервером (например, postgresql://...?sslmode=require в PostgreSQL). Это предотвращает перехват данных в открытом виде.

2.2. Шифрование на уровне хранения

• TDE (Transparent Data Encryption): многие СУБД (Oracle, Microsoft SQL Server, MySQL) поддерживают прозрачное шифрование всего или части файла базы данных.
• Шифрование на уровне приложения: данные шифруются до записи в базу, что обеспечивает дополнительный уровень защиты от несанкционированного доступа на стороне сервера.

2.3. Управление ключами

Если данные зашифрованы, необходимо безопасно хранить и управлять ключами (чаще всего это отдельный модуль/сервис — KMS). Потеря ключа равносильна потере данных.

3.1. Принцип наименьших привилегий

• Давайте пользователям только те права, которые действительно нужны для выполнения их задач.
• Регулярно пересматривайте выданные привилегии, чтобы удалить устаревшие или избыточные доступы.

3.2. Сегрегация (разделение) обязанностей

• Администраторы баз данных не всегда должны иметь доступ к бизнес-данным; иногда достаточно технических прав для обслуживания.
• Аналитики, разработчики, операторы — у каждой роли свой уровень доступа.

3.3. Защита учётных данных

• Храните пароли в зашифрованном или хешированном виде.
• Используйте сильные пароли и, где возможно, механизмы одноразовых токенов или Kerberos/LDAP-аутентификации.
• Ограничивайте доступ к файлам конфигурации, в которых могут храниться логины и пароли.

3.4. Мониторинг и аудит

• Логи аудита (аудит DDL и DML команд) помогают выявлять подозрительные действия.
• Настройка Alert-систем: если кто-то пытается получить доступ к таблице, для которой у него нет прав, или выполняет необычные действия, система должна сигнализировать.

Хотя SQL-инъекции — это больше вопрос уровня приложения, правильная конфигурация СУБД тоже играет роль.

1. Используйте подготовленные выражения (prepared statements) вместо динамической конкатенации строк.
2. Ограничьте права: даже если злоумышленник получит доступ через уязвимость, он не должен иметь полномочий на полный обзор и модификацию базы.
3. Регулярно обновляйте версию СУБД и патчи безопасности.

1. Создание ролей: readonly, readwrite, admin.
2. Назначение пользователям соответствующих ролей в зависимости от их должностных обязанностей.
3. Включение SSL/TLS для всех подключений к базе и TDE для хранения данных на дисках.
4. Регулярный аудит: периодический обзор логов и активности пользователей.
5. Ротация ключей: если используются ключи шифрования, менять их согласно политике безопасности.

1. Сложные пароли и регулярная смена: делать это автоматизированно по расписанию.
2. Ограничение доступа по IP или VPN: не разрешать внешние подключения без особой необходимости.
3. Мультифакторная аутентификация (MFA): если СУБД интегрирована с корпоративным каталогом (например, Active Directory), использовать MFA.
4. Минимизация открытых портов: держать только нужные службы доступными извне.
5. Регулярное резервное копирование: хранить бэкапы в зашифрованном виде и тестировать их восстановление.