Как хранятся данные в реляционных базах данных?

Как хранятся данные в реляционных базах данных

Реляционные БД организуют данные в структурированном виде с использованием таблиц, строк и столбцов. Это один из самых популярных подходов к хранению данных.

Основная структура

Database
  └── Schema (схема, нарпример public)
       └── Table 1
            ├── Column (id, name, email)
            ├── Row (запись)
            └── Index (для быстрого поиска)
       └── Table 2
            └── ...

Таблица и её компоненты

Таблица — это двумерная структура данных:

┌────┬───────────┬─────────────────────┐
│ id │   name    │      email          │
├────┼───────────┼─────────────────────┤
│  1 │ John Doe  │ john@example.com    │
│  2 │ Jane Smith│ jane@example.com    │
│  3 │ Bob Jones │ bob@example.com     │
└────┴───────────┴─────────────────────┘

• Столбец (Column) — атрибут (name, email)
• Строка (Row) — запись о конкретной сущности
• Ячейка — пересечение строки и столбца

Типы данных и хранение

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    age INT,
    balance DECIMAL(10, 2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT true
);

Физическое хранилище:

• Данные хранятся в блокахпамяти (обычно 4-8 KB)
• Каждая таблица — это файлы на диске
• Строки распределены по блокам последовательно или согласно индексам

Индексы

Ускоряют поиск и сортировку:

# Primary Key Index (B-Tree)
CREATE TABLE users (
    id INT PRIMARY KEY,  -- автоматически индексируется
    ...
);

# Обычный индекс
CREATE INDEX idx_email ON users(email);

# Составной индекс
CREATE INDEX idx_name_email ON users(name, email);

# Уникальный индекс
CREATE UNIQUE INDEX idx_username ON users(username);

B-Tree индекс (наиболее частый):

            [50]
          /      \\
       [20]        [80]
      /   \\      /   \\
    [10] [35]  [60] [95]

Бинарное дерево для быстрого поиска за O(log n).

Отношения между таблицами

# Таблица пользователей
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(255)
);

# Таблица заказов
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT NOT NULL,
    amount DECIMAL(10, 2),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

Один-ко-многим (1:N):

User 1 ----< Orders (1:N)
  id           user_id
  1      ┌─────────────┐
         │  order 1    │
         │  order 2    │
         │  order 3    │
         └─────────────┘

Многие-ко-многим (N:M):

# Таблица студентов
CREATE TABLE students (id INT PRIMARY KEY, name VARCHAR(255));

# Таблица курсов
CREATE TABLE courses (id INT PRIMARY KEY, name VARCHAR(255));

# Промежуточная таблица (junction table)
CREATE TABLE student_courses (
    student_id INT,
    course_id INT,
    grade VARCHAR(2),
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

Нормализация (Normalization)

Уменьшает дублирование данных:

# ❌ Ненормализованно (дублирование)
CREATE TABLE orders (
    id INT,
    customer_name VARCHAR(255),
    customer_email VARCHAR(255),
    customer_phone VARCHAR(20),
    product_name VARCHAR(255),
    product_price DECIMAL(10, 2)
);

# ✅ Нормализовано (1NF, 2NF, 3NF)
CREATE TABLE customers (id INT PRIMARY KEY, name, email, phone);
CREATE TABLE products (id INT PRIMARY KEY, name, price);
CREATE TABLE orders (id INT, customer_id INT, product_id INT, ...);

ACID свойства

Реляционные БД гарантируют:

# Atomicity — всё или ничего
BEGIN TRANSACTION
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;  # Обе операции или ни одна

# Consistency — логическая целостность
# FOREIGN KEY обеспечивает ссылочную целостность

# Isolation — независимость транзакций
# Одна транзакция не видит незавершённые изменения другой

# Durability — стойкость данных
# Закоммиченные данные сохраняются даже при сбое

Физическое хранилище на диске

Database Files
  ├── users.dat       (данные таблицы users)
  ├── users.idx       (индексы для users)
  ├── orders.dat      (данные таблицы orders)
  ├── orders.idx      (индексы для orders)
  └── log file        (журнал транзакций для восстановления)

Примеры использования

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import sessionmaker

engine = create_engine("postgresql://user:password@localhost/mydb")
Session = sessionmaker(bind=engine)
session = Session()

# Запись данных
user = User(name="John", email="john@example.com")
session.add(user)
session.commit()

# Чтение данных
users = session.query(User).filter(User.age > 25).all()

Преимущества реляционных БД

• Структурированность — чёткая схема
• ACID — надёжность транзакций
• Query Language — мощный SQL
• Нормализация — минимум дублирования
• Масштабируемость — мощные инструменты оптимизации

Реляционные БД подходят для большинства бизнес-приложений с хорошо определённой структурой данных.