Логическая и физическая независимость данных

Теоретический конспект лекции по дисциплине «ОП.08 Основы проектирования баз данных» (§2.2 с.7 программы)

1. Проблема файловых систем и происхождение независимости данных

В эпоху файловых систем 1960-х годов программы писались с жесткой привязкой к физической структуре файлов на магнитных лентах и барабанах. Каждое приложение лично знало:

• Точное смещение байтов каждого поля в записи (например, байты с 12 по 35 — фамилия сотрудника).
• Порядок сортировки записей в файле.
• Размер дискового сектора и метод блочной адресации.

Выход из кризиса предложил комитет ANSI/X3/SPARC в 1975 году, разделив архитектуру СУБД на три строго изолированных уровня.

2. Трехуровневая архитектура ANSI/SPARC и трансформация запросов

Архитектура ANSI/SPARC гарантирует, что пользователь и прикладное ПО взаимодействуют не с байтами на накопителе, а с абстрактными логическими отношениями.

Как трансформируется SQL-запрос через уровни ANSI/SPARC:

1. Внешний уровень: Бухгалтерская программа отправляет привычный запрос: SELECT client_name, inn FROM Clients WHERE client_id = 42;.
2. Трансформация «Внешний → Концептуальный»: СУБД анализирует каталог и обнаруживает, что Clients — это представление (VIEW). Парсер раскрывает макроподстановку и переписывает запрос к реальным концептуальным таблицам Legal_Entities и Individuals через UNION ALL.
3. Трансформация «Концептуальный → Внутренний»: Оптимизатор СУБД (Cost-Based Optimizer) строит план выполнения: анализирует наличие B-Tree индекса на первичном ключе client_id, проверяет кэш страниц в оперативной памяти (Shared Buffers) и генерирует команды дискового ввода-вывода (IO) для считывания конкретной 8-килобайтной страницы с накопителя NVMe.

3. Логическая независимость: защита от рефакторинга схемы

Когда бизнес растет, структура сущностей меняется: таблицы разбиваются на части (вертикальная или горизонтальная декомпозиция), объединяются, добавляются новые обязательные атрибуты.

Инженерный пример: Декомпозиция таблицы клиентов

Исходная ситуация: Существовала единая таблица Clients(client_id, name, inn, passport, kpp, address). С ней работает старая монолитная бухгалтерская программа, исходные коды которой утеряны или изменение которых стоит слишком дорого.

Потребность рефакторинга: Для соблюдения 3-й нормальной формы и исключения аномалий решено разделить ее на две специализированные таблицы:

• Legal_Entities (Юридические лица: inn, kpp, ogrn, legal_name)
• Individuals (Физические лица: inn, passport_series, passport_number, full_name)

Решение с помощью VIEW (сохранение логической независимости):

-- 1. Новая концептуальная схема:
CREATE TABLE Legal_Entities (
    client_id SERIAL PRIMARY KEY,
    company_name VARCHAR(255) NOT NULL,
    inn VARCHAR(12) NOT NULL,
    kpp VARCHAR(9) NOT NULL,
    address TEXT NOT NULL
);

CREATE TABLE Individuals (
    client_id SERIAL PRIMARY KEY,
    full_name VARCHAR(255) NOT NULL,
    inn VARCHAR(12),
    passport_data VARCHAR(50) NOT NULL,
    address TEXT NOT NULL
);

-- 2. Экранирующее представление для старой бухгалтерской программы:
CREATE OR REPLACE VIEW Clients AS
SELECT client_id, company_name AS name, inn, NULL AS passport, kpp, address, 'LEGAL' AS client_type
FROM Legal_Entities
UNION ALL
SELECT client_id, full_name AS name, inn, passport_data AS passport, NULL AS kpp, address, 'INDIVIDUAL' AS client_type
FROM Individuals;

-- 3. Обеспечение операций модификации через триггер INSTEAD OF:
CREATE OR REPLACE FUNCTION process_clients_insert()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.kpp IS NOT NULL THEN
        INSERT INTO Legal_Entities (client_id, company_name, inn, kpp, address)
        VALUES (NEW.client_id, NEW.name, NEW.inn, NEW.kpp, NEW.address);
    ELSE
        INSERT INTO Individuals (client_id, full_name, inn, passport_data, address)
        VALUES (NEW.client_id, NEW.name, NEW.inn, COALESCE(NEW.passport, 'N/A'), NEW.address);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_clients_insert
INSTEAD OF INSERT ON Clients
FOR EACH ROW EXECUTE FUNCTION process_clients_insert();

4. Физическая независимость: свобода управления инфраструктурой

Инженерный пример: Ускорение поиска с помощью B-Tree индекса

В интернет-магазине таблица Orders насчитывает 10 000 000 строк. Микросервис чекаута выполняет поиск по идентификатору пользователя:

SELECT order_id, order_date, total_sum 
FROM Orders 
WHERE user_id = 582103;

Ситуация до оптимизации: Индекс отсутствует. СУБД выполняет Sequential Scan (полное последовательное сканирование диска). Запрос читает 800 МБ страниц и выполняется 3.8 секунды. Сервер перегружен.

Действие администратора БД (DBA):

-- Создание B-Tree индекса на физическом уровне:
CREATE INDEX idx_orders_user_id ON Orders USING btree(user_id);

Ситуация после оптимизации: Запрос приложения не изменился ни на один символ. Однако оптимизатор PostgreSQL автоматически выбирает план Bitmap Index Scan / Index Scan. Время отклика падает с 3800 мс до 1.2 мс.

Другие ключевые механизмы физической независимости:

5. Сравнительный анализ: Логическая vs Физическая независимость

6. Вопросы для фундаментальной самопроверки