Как хранятся данные в реляционной БД?

Хранение данных в реляционной БД

Реляционная база данных (РБДС) — это система управления данными, основанная на реляционной модели. Это наиболее популярный тип БД для приложений.

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

Таблицы (Relations)

Данные организованы в таблицы, каждая таблица представляет сущность:

Таблица USERS
┌────┬────────┬────────┬──────────────┐
│ id │ name   │ email  │ created_at   │
├────┼────────┼────────┼──────────────┤
│ 1  │ Alice  │ a@ex.. │ 2024-01-01   │
│ 2  │ Bob    │ b@ex.. │ 2024-01-02   │
│ 3  │ Carol  │ c@ex.. │ 2024-01-03   │
└────┴────────┴────────┴──────────────┘

Строки (Records)

Каждая строка представляет один экземпляр сущности.

Столбцы (Attributes)

Каждый столбец определяет атрибут сущности и имеет тип данных.

2. Типы данных

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100),           -- Строковый тип
    price DECIMAL(10, 2),        -- Число с плавающей точкой
    quantity INTEGER,            -- Целое число
    description TEXT,            -- Длинный текст
    created_at TIMESTAMP,        -- Дата и время
    is_active BOOLEAN,           -- Логический тип
    image BYTEA                  -- Двоичные данные
);

3. Ключи и индексы

Primary Key

Уникально идентифицирует каждую строку:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(100) UNIQUE,
    name VARCHAR(100)
);

Foreign Key

Связывает таблицы между собой:

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    user_id INTEGER NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

Индексы

Ускоряют поиск данных:

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_posts_created ON posts(created_at DESC);

4. Нормализация БД

1НФ (First Normal Form)

Все значения атомарны (неделимы):

-- Плохо: несколько значений в одной ячейке
CREATE TABLE books (
    id INTEGER,
    title VARCHAR(100),
    authors VARCHAR(200)  -- "John Smith, Jane Doe"
);

-- Хорошо: отдельная таблица
CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title VARCHAR(100)
);

CREATE TABLE book_authors (
    book_id INTEGER,
    author_name VARCHAR(100),
    FOREIGN KEY (book_id) REFERENCES books(id)
);

2НФ (Second Normal Form)

Нет зависимостей от части первичного ключа:

-- Плохо: студент зависит от части ключа
CREATE TABLE enrollments (
    student_id INTEGER,
    course_id INTEGER,
    student_name VARCHAR(100),  -- Зависит только от student_id!
    PRIMARY KEY (student_id, course_id)
);

-- Хорошо: отдельная таблица
CREATE TABLE students (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE enrollments (
    student_id INTEGER,
    course_id INTEGER,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(id)
);

3НФ (Third Normal Form)

Нет зависимостей между неключевыми атрибутами:

-- Плохо: город зависит от страны
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100),
    country VARCHAR(100),
    city VARCHAR(100),
    country_code VARCHAR(2)
);

-- Хорошо: отдельные таблицы
CREATE TABLE countries (
    code VARCHAR(2) PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE cities (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100),
    country_code VARCHAR(2),
    FOREIGN KEY (country_code) REFERENCES countries(code)
);

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100),
    city_id INTEGER,
    FOREIGN KEY (city_id) REFERENCES cities(id)
);

5. ACID свойства

-- Atomicity (Атомарность): либо всё, либо ничего
BEGIN TRANSACTION;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;  -- Обе операции выполнены или откачены

-- Consistency (Согласованность): БД остаётся в валидном состоянии
-- Isolation (Изоляция): транзакции не влияют друг на друга
-- Durability (Надёжность): коммитованные данные не теряются

6. Связи между таблицами

One-to-Many (1:N)

CREATE TABLE authors (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title VARCHAR(100),
    author_id INTEGER,
    FOREIGN KEY (author_id) REFERENCES authors(id)
);

Many-to-Many (N:N)

CREATE TABLE students (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE courses (
    id INTEGER PRIMARY KEY,
    title VARCHAR(100)
);

CREATE TABLE enrollments (
    student_id INTEGER,
    course_id INTEGER,
    enrollment_date DATE,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

7. Примеры запросов

-- SELECT: выборка данных
SELECT name, email FROM users WHERE created_at > '2024-01-01';

-- JOIN: объединение таблиц
SELECT u.name, p.title
FROM users u
JOIN posts p ON u.id = p.user_id;

-- GROUP BY: группировка
SELECT user_id, COUNT(*) as post_count
FROM posts
GROUP BY user_id;

-- INSERT: вставка данных
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');

-- UPDATE: обновление
UPDATE users SET email = 'new@example.com' WHERE id = 1;

-- DELETE: удаление
DELETE FROM users WHERE id = 1;

8. Физическое хранение на диске

Динамический файл БД
┌─────────────────────────────────────┐
│ Заголовок (metadata)                │
├─────────────────────────────────────┤
│ Таблица 1 (USERS)                   │
│  ├─ Страница 1 (Page 1)             │
│  │  ├─ Строка 1: id=1, name=Alice   │
│  │  ├─ Строка 2: id=2, name=Bob     │
│  │  └─ Свободное место              │
│  ├─ Страница 2 (Page 2)             │
│  │  └─ Строка 3: id=3, name=Carol   │
├─────────────────────────────────────┤
│ Таблица 2 (POSTS)                   │
│  ├─ Страница 1                      │
│  └─ ...                             │
├─────────────────────────────────────┤
│ Индексы (B-Tree структуры)          │
├─────────────────────────────────────┤
│ Логи транзакций (Write-ahead Log)   │
└─────────────────────────────────────┘

9. В контексте Django ORM

from django.db import models

class User(models.Model):
    name = models.CharField(max_length=100)
    email = models.EmailField(unique=True)
    created_at = models.DateTimeField(auto_now_add=True)
    
    class Meta:
        indexes = [
            models.Index(fields=['created_at']),
        ]

class Post(models.Model):
    title = models.CharField(max_length=200)
    user = models.ForeignKey(User, on_delete=models.CASCADE)
    created_at = models.DateTimeField(auto_now_add=True)

# Оптимизированный запрос
posts = Post.objects.select_related('user').all()

Best Practices

1. Нормализация: избегайте дублирования данных 2. Индексы: добавляйте индексы на часто ищущиеся поля 3. Типы данных: выбирайте правильные типы (VARCHAR vs TEXT) 4. Внешние ключи: используйте для обеспечения целостности данных 5. Транзакции: группируйте связанные операции 6. Мониторинг: отслеживайте размер БД и производительность запросов