Нормализация, индексы, транзакции, isolation levels, оптимизация запросов, шардирование и репликация.
Правильно спроектированная база данных — это фундамент, на котором держится всё приложение
Представьте: ваш стартап вырос с 100 до 100 000 пользователей. Приложение, которое раньше работало мгновенно, теперь отвечает по 5-10 секунд. Логи показывают медленные запросы к базе данных. Вы смотрите на EXPLAIN ANALYZE и видите полное сканирование таблиц с миллионами строк.
Правильное проектирование базы данных с самого начала экономит сотни часов на оптимизацию и миграцию данных.
В этой теме разберём:
Нормализация — это процесс организации данных для уменьшения избыточности и улучшения целостности.
Требования 1NF:
-- Плохо: не атомарные значения, повторяющиеся группы
CREATE TABLE users_bad (
id INTEGER,
name TEXT,
phones TEXT, -- "555-1234,555-5678" — несколько значений в одной ячейке
favorite_colors TEXT -- "red,blue,green"
);
-- Хорошо: атомарные значения
CREATE TABLE users_1nf (
id INTEGER PRIMARY KEY,
name TEXT
);
CREATE TABLE user_phones (
id INTEGER PRIMARY KEY,
user_id INTEGER REFERENCES users_1nf(id),
phone TEXT -- Одно значение
);
CREATE TABLE user_favorite_colors (
id INTEGER PRIMARY KEY,
user_id INTEGER REFERENCES users_1nf(id),
color TEXT -- Одно значение
);Требования 2NF:
Частичная зависимость возникает, когда неключевой атрибут зависит только от части составного ключа.
-- Плохо: частичная зависимость
-- order_items зависит только от order_id, а не от (order_id, product_id)
CREATE TABLE order_items_bad (
order_id INTEGER,
product_id INTEGER,
quantity INTEGER,
order_date DATE, -- Зависит только от order_id!
customer_name TEXT, -- Зависит только от order_id!
PRIMARY KEY (order_id, product_id)
);
-- Хорошо: выносим зависимости в отдельные таблицы
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
order_date DATE,
customer_name TEXT
);
CREATE TABLE order_items (
order_id INTEGER REFERENCES orders(order_id),
product_id INTEGER,
quantity INTEGER,
PRIMARY KEY (order_id, product_id)
);Требования 3NF:
Транзитивная зависимость: A → B → C, где C зависит от B, а B зависит от A.
-- Плохо: транзитивная зависимость
-- city зависит от postal_code, а postal_code зависит от user_id
CREATE TABLE users_2nf (
user_id INTEGER PRIMARY KEY,
name TEXT,
postal_code TEXT,
city TEXT, -- Зависит от postal_code, а не напрямую от user_id
country TEXT -- Тоже зависит от postal_code
);
-- Хорошо: выносим транзитивную зависимость
CREATE TABLE users_3nf (
user_id INTEGER PRIMARY KEY,
name TEXT,
postal_code TEXT REFERENCES postal_codes(code)
);
CREATE TABLE postal_codes (
code TEXT PRIMARY KEY,
city TEXT,
country TEXT
);Рассмотрим пример интернет-магазина:
-- Денормализованная таблица (плохо)
CREATE TABLE orders_denormalized (
order_id INTEGER,
order_date DATE,
customer_id INTEGER,
customer_name TEXT,
customer_email TEXT,
customer_address TEXT,
product_id INTEGER,
product_name TEXT,
product_price DECIMAL,
category_id INTEGER,
category_name TEXT,
quantity INTEGER,
supplier_id INTEGER,
supplier_name TEXT,
supplier_contact TEXT
);
-- Проблемы:
-- 1. Избыточность: customer_name повторяется для каждого заказа
-- 2. Аномалии обновления: изменение email требует обновления всех строк
-- 3. Аномалии вставки: нельзя добавить клиента без заказа
-- 4. Аномалии удаления: удаление заказа удаляет информацию о клиенте
-- Нормализованная схема (хорошо)
CREATE TABLE customers (
customer_id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE customer_addresses (
address_id INTEGER PRIMARY KEY,
customer_id INTEGER REFERENCES customers(customer_id),
address_line1 TEXT,
address_line2 TEXT,
city TEXT,
postal_code TEXT,
country TEXT,
is_default BOOLEAN DEFAULT FALSE
);
CREATE TABLE categories (
category_id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
parent_category_id INTEGER REFERENCES categories(category_id)
);
CREATE TABLE suppliers (
supplier_id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
contact_email TEXT,
contact_phone TEXT
);
CREATE TABLE products (
product_id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
description TEXT,
price DECIMAL(10, 2) NOT NULL,
category_id INTEGER REFERENCES categories(category_id),
supplier_id INTEGER REFERENCES suppliers(supplier_id),
stock_quantity INTEGER DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER REFERENCES customers(customer_id),
order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
status TEXT CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled')),
total_amount DECIMAL(10, 2),
shipping_address_id INTEGER REFERENCES customer_addresses(address_id)
);
CREATE TABLE order_items (
order_item_id INTEGER PRIMARY KEY,
order_id INTEGER REFERENCES orders(order_id) ON DELETE CASCADE,
product_id INTEGER REFERENCES products(product_id),
quantity INTEGER NOT NULL CHECK (quantity > 0),
unit_price DECIMAL(10, 2) NOT NULL, -- Фиксируем цену на момент заказа
subtotal DECIMAL(10, 2) GENERATED ALWAYS AS (quantity * unit_price) STORED
);Индексы ускоряют чтение данных, но замедляют запись (INSERT, UPDATE, DELETE).
B-Tree (Balanced Tree) — сбалансированное дерево, оптимальное для:
=)>, <, BETWEEN)ORDER BY)LIKE 'prefix%')-- Создание B-Tree индекса
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_date ON orders(order_date);
CREATE INDEX idx_products_price ON products(price);
-- Составной индекс (порядок колонок важен!)
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);
-- Этот индекс можно использовать для:
-- WHERE customer_id = ? ✓
-- WHERE customer_id = ? AND order_date = ? ✓
-- WHERE customer_id = ? AND order_date > ? ✓
-- WHERE order_date = ? ✗ (левая колонка пропущена)
-- EXPLAIN ANALYZE показывает использование индекса
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'test@example.com';
-- Результат:
-- Index Scan using idx_users_email on users (cost=0.29..8.31 rows=1 width=100)
-- Index Cond: (email = 'test@example.com'::text)
-- Actual Time: 0.050..0.051 rows=1 loops=1Когда использовать B-Tree:
Hash индексы оптимальны только для точного совпадения (=).
-- Создание Hash индекса
CREATE INDEX idx_users_email_hash ON users USING HASH(email);
-- Работает только для:
-- WHERE email = 'test@example.com' ✓
-- WHERE email LIKE 'test%' ✗
-- WHERE email > 'test' ✗
-- ORDER BY email ✗
-- EXPLAIN ANALYZE
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'test@example.com';
-- Результат:
-- Hash Scan using idx_users_email_hash on users (cost=0.28..8.30 rows=1 width=100)
-- Hash Cond: (email = 'test@example.com'::text)Когда использовать Hash:
GIN оптимален для:
ARRAY)tsvector)-- Индекс для массивов
CREATE TABLE articles (
id SERIAL PRIMARY KEY,
title TEXT,
tags TEXT[] -- Массив тегов
);
CREATE INDEX idx_articles_tags ON articles USING GIN(tags);
-- Запрос: найти статьи с тегом 'python'
EXPLAIN ANALYZE
SELECT * FROM articles WHERE tags @> ARRAY['python'];
-- @> означает "contains"
-- Индекс для JSONB
CREATE TABLE events (
id SERIAL PRIMARY KEY,
event_type TEXT,
payload JSONB -- Гибкая структура данных
);
CREATE INDEX idx_events_payload ON events USING GIN(payload);
-- Запрос: найти события с определённым значением в JSONB
EXPLAIN ANALYZE
SELECT * FROM events WHERE payload @> '{"user_id": 123}';
-- Индекс для конкретного ключа JSONB (более эффективный)
CREATE INDEX idx_events_payload_user_id ON events USING GIN((payload->'user_id'));
-- Полнотекстовый поиск
CREATE INDEX idx_articles_title_fts ON articles USING GIN(to_tsvector('english', title));
-- Запрос полнотекстового поиска
EXPLAIN ANALYZE
SELECT * FROM articles
WHERE to_tsvector('english', title) @@ to_tsquery('python & database');GiST оптимален для:
-- Геометрические данные (PostGIS)
CREATE TABLE locations (
id SERIAL PRIMARY KEY,
name TEXT,
coordinates GEOGRAPHY(POINT, 4326) -- Широта/долгота
);
CREATE INDEX idx_locations_coords ON locations USING GIST(coordinates);
-- Запрос: найти точки в радиусе 1 км
EXPLAIN ANALYZE
SELECT * FROM locations
WHERE ST_DWithin(
coordinates,
ST_GeogFromText('POINT(37.6176 55.7558)'), -- Москва
1000 -- метры
);
-- Нечёткий поиск (pg_trgm extension)
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_users_name_trgm ON users USING GIST(name gist_trgm_ops);
-- Запрос: нечёткий поиск по имени
EXPLAIN ANALYZE
SELECT * FROM users WHERE name % 'Александр'; -- % означает similarity| Тип индекса | Лучше всего для | Операции | Размер |
|---|---|---|---|
| B-Tree | Большинство случаев | =, >, <, BETWEEN, ORDER BY, LIKE 'prefix%' | Средний |
| Hash | Точное совпадение | = | Маленький |
| GIN | Массивы, JSONB, полнотекст | @>, &&, @@ | Большой |
| GiST | Геометрия, нечёткий поиск | специфичные | Средний |
-- 1. Не создавайте индексы на маленьких таблицах (< 1000 строк)
-- PostgreSQL часто предпочтёт sequential scan
-- 2. Избегайте избыточных индексов
CREATE INDEX idx_email ON users(email);
CREATE INDEX idx_email_active ON users(email, is_active); -- Избыточный!
-- 3. Мониторьте неиспользуемые индексы
SELECT
schemaname,
indexrelname,
idx_scan, -- Количество сканирований
pg_size_pretty(pg_relation_size(indexrelid)) as size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;
-- 4. Удаляйте неиспользуемые индексы (они замедляют запись!)
DROP INDEX IF EXISTS unused_index;
-- 5. Используйте частичные индексы для частых запросов с фильтром
CREATE INDEX idx_orders_pending ON orders(created_at)
WHERE status = 'pending'; -- Индекс только для pending заказов
-- 6. Используйте функциональные индексы для запросов с функциями
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
-- Запрос должен использовать ту же функцию:
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';
-- 7. CONCURRENTLY для создания индекса без блокировки таблицы
CREATE INDEX CONCURRENTLY idx_large_table_column ON large_table(column);
-- Важно: нельзя использовать внутри транзакцииТранзакция — это последовательность операций, которая выполняется как единое целое.
Atomicity (Атомарность):
Consistency (Согласованность):
Isolation (Изоляция):
Durability (Долговечность):
from sqlalchemy import create_engine, Column, Integer, String, Decimal, ForeignKey
from sqlalchemy.orm import sessionmaker, declarative_base, relationship
from contextlib import contextmanager
Base = declarative_base()
class Account(Base):
__tablename__ = 'accounts'
id = Column(Integer, primary_key=True)
name = Column(String, nullable=False)
balance = Column(Decimal(10, 2), default=0)
transactions = relationship('Transaction', back_populates='account')
class Transaction(Base):
__tablename__ = 'transactions'
id = Column(Integer, primary_key=True)
account_id = Column(Integer, ForeignKey('accounts.id'))
amount = Column(Decimal(10, 2), nullable=False)
type = Column(String) # 'debit' или 'credit'
description = Column(String)
account = relationship('Account', back_populates='transactions')
# Создание двигателя и сессии
engine = create_engine('postgresql://user:pass@localhost/dbname')
Session = sessionmaker(bind=engine)
@contextmanager
def transactional_session():
"""Контекстный менеджер для транзакций"""
session = Session()
try:
yield session
session.commit()
except Exception:
session.rollback()
raise
finally:
session.close()
def transfer_money(from_account_id: int, to_account_id: int, amount: Decimal):
"""
Перевод денег между счетами — атомарная операция.
ACID в действии:
- Atomicity: если второй UPDATE упадёт, первый откатится
- Consistency: CHECK constraint гарантирует неотрицательный баланс
- Isolation: другие транзакции не видят промежуточных изменений
- Durability: после commit изменения сохранены на диске
"""
with transactional_session() as session:
# Блокируем строки для предотвращения race condition
from_account = session.query(Account).filter(
Account.id == from_account_id
).with_for_update().one()
to_account = session.query(Account).filter(
Account.id == to_account_id
).with_for_update().one()
# Проверка достаточности средств
if from_account.balance < amount:
raise ValueError("Insufficient funds")
# Списание
from_account.balance -= amount
debit = Transaction(
account_id=from_account_id,
amount=amount,
type='debit',
description=f'Transfer to account {to_account_id}'
)
session.add(debit)
# Зачисление
to_account.balance += amount
credit = Transaction(
account_id=to_account_id,
amount=amount,
type='credit',
description=f'Transfer from account {from_account_id}'
)
session.add(credit)
# commit происходит автоматически при выходе из контекста
# Пример с явной транзакцией
def complex_operation():
session = Session()
try:
# Начало транзакции
session.begin()
# Операции...
account = session.query(Account).get(1)
account.balance += 100
# Точка сохранения (savepoint) — можно откатиться к ней
session.begin_nested()
try:
# Рискованная операция
risky_operation(session)
except Exception:
# Откат к savepoint, основная транзакция продолжается
session.rollback()
# Фиксация транзакции
session.commit()
except Exception:
session.rollback()
raise
finally:
session.close()PostgreSQL поддерживает 4 уровня изоляции:
-- Проверка текущего уровня
SHOW transaction_isolation;
-- Установка уровня для сессии
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;| Уровень | Dirty Read | Non-Repeatable Read | Phantom Read | Serialization Anomaly |
|---|---|---|---|---|
| Read Uncommitted | Возможно | Возможно | Возможно | Возможно |
| Read Committed (по умолчанию) | Нет | Возможно | Возможно | Возможно |
| Repeatable Read | Нет | Нет | Возможно | Возможно |
| Serializable | Нет | Нет | Нет | Нет |
Важно: В PostgreSQL Read Uncommitted ведёт себя как Read Committed.
Dirty Read (Грязное чтение): Чтение незакоммиченных данных другой транзакции.
-- Транзакция A -- Транзакция B
BEGIN;
UPDATE accounts SET balance = 500
WHERE id = 1;
BEGIN;
-- Dirty Read: видит незакоммиченные данные
SELECT balance FROM accounts WHERE id = 1;
-- Вернёт 500, хотя A ещё не закоммитил
COMMIT; -- или ROLLBACK
COMMIT;Non-Repeatable Read (Неповторяемое чтение): Один и тот же запрос в одной транзакции возвращает разные результаты.
-- Транзакция A -- Транзакция B
BEGIN;
SELECT balance FROM accounts WHERE id = 1;
-- Вернёт 1000
BEGIN;
UPDATE accounts SET balance = 500
WHERE id = 1;
COMMIT;
SELECT balance FROM accounts WHERE id = 1;
-- Вернёт 500 — данные изменились!
COMMIT;Phantom Read (Фантомное чтение): Появление новых строк при повторном выполнении запроса с условием.
-- Транзакция A -- Транзакция B
BEGIN;
SELECT COUNT(*) FROM orders WHERE status = 'pending';
-- Вернёт 10
BEGIN;
INSERT INTO orders (status) VALUES ('pending');
COMMIT;
SELECT COUNT(*) FROM orders WHERE status = 'pending';
-- Вернёт 11 — появилась новая строка!
COMMIT;Serialization Anomaly: Результат параллельных транзакций не соответствует никакому последовательному выполнению.
-- Классический пример: два инкремента
-- Ожидаемый результат: balance = 100 + 10 + 10 = 120
-- Транзакция A -- Транзакция B
BEGIN;
BEGIN;
SELECT balance FROM accounts WHERE id = 1;
-- 100
SELECT balance FROM accounts WHERE id = 1;
-- 100
UPDATE accounts SET balance = 110
WHERE id = 1;
UPDATE accounts SET balance = 110
WHERE id = 1;
COMMIT;
COMMIT;
-- Результат: balance = 110 (один инкремент потерян!)from sqlalchemy import event
from sqlalchemy.orm import Session
def read_committed_example():
"""
Read Committed — по умолчанию в PostgreSQL.
Подходит для большинства случаев.
"""
with transactional_session() as session:
# Каждое SELECT видит только закоммиченные данные
accounts = session.query(Account).filter(Account.balance > 1000).all()
# Другая транзакция может изменить данные между запросами
def repeatable_read_example():
"""
Repeatable Read — гарантирует, что данные не изменятся
в рамках транзакции.
"""
with transactional_session() as session:
# Установка уровня изоляции
session.connection().execution_options(
isolation_level="REPEATABLE_READ"
)
# Первое чтение
account = session.query(Account).get(1)
balance1 = account.balance
# Другая транзакция может закоммитить изменения...
# Второе чтение — вернёт то же значение!
session.expire(account) # Обновляем из БД
balance2 = account.balance
assert balance1 == balance2 # Гарантировано
def serializable_example():
"""
Serializable — максимальная изоляция.
Используется для критичных финансовых операций.
Может вызывать ошибки serialization failure.
"""
max_retries = 3
for attempt in range(max_retries):
try:
with transactional_session() as session:
session.connection().execution_options(
isolation_level="SERIALIZABLE"
)
# Чтение и запись
account = session.query(Account).get(1)
account.balance += 100
# Если произошла конкуренция, PostgreSQL выбросит:
# sqlalchemy.exc.OperationalError: could not serialize access
# Нужно повторить транзакцию
except Exception as e:
if "serialize" in str(e).lower() and attempt < max_retries - 1:
continue # Повторить
raise
# Рекомендации по выбору уровня:
# - Read Committed: 95% случаев (веб-приложения, CRUD)
# - Repeatable Read: отчёты, аналитика, консистентное чтение
# - Serializable: финансовые транзакции, инвентарь-- EXPLAIN показывает план выполнения без запуска
EXPLAIN
SELECT * FROM users WHERE email = 'test@example.com';
-- EXPLAIN ANALYZE выполняет запрос и показывает реальное время
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'test@example.com';
-- Результат:
-- Index Scan using idx_users_email on users (cost=0.29..8.31 rows=1 width=100)
-- Index Cond: (email = 'test@example.com'::text)
-- Actual Time: 0.050..0.051 rows=1 loops=1
-- Planning Time: 0.100 ms
-- Execution Time: 0.080 msКлючевые метрики:
cost: оценка стоимости (первое число — startup cost, второе — total cost)rows: оценочное количество строкwidth: средний размер строки в байтахActual Time: реальное время выполнения (только в ANALYZE)loops: сколько раз выполнился узелSequential Scan (полное сканирование):
EXPLAIN ANALYZE
SELECT * FROM users WHERE name LIKE '%john%';
-- Seq Scan on users (cost=0.00..100.00 rows=10 width=100)
-- Filter: (name ~~ '%john%'::text)
-- Rows Removed by Filter: 9990
-- Actual Time: 5.000..10.000 rows=10 loops=1Происходит когда:
Index Scan:
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'test@example.com';
-- Index Scan using idx_users_email on users
-- Index Cond: (email = 'test@example.com'::text)
-- Actual Time: 0.050..0.051 rows=1 loops=1Использует индекс для поиска, затем обращается к таблице за данными.
Index Only Scan (самый быстрый):
EXPLAIN ANALYZE
SELECT email FROM users WHERE email = 'test@example.com';
-- Index Only Scan using idx_users_email on users
-- Index Cond: (email = 'test@example.com'::text)
-- Actual Time: 0.030..0.031 rows=1 loops=1Все данные есть в индексе, таблица не нужна. Требует VACUUM для актуальности.
Bitmap Index Scan + Bitmap Heap Scan:
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE status = 'pending' AND created_at > '2025-01-01';
-- Bitmap Heap Scan on orders
-- Recheck Cond: ((status = 'pending'::text) AND (created_at > '2025-01-01'))
-- Actual Time: 0.500..2.000 rows=100 loops=1
-- -> Bitmap Index Scan on idx_orders_status
-- Index Cond: (status = 'pending'::text)
-- -> Bitmap Index Scan on idx_orders_date
-- Index Cond: (created_at > '2025-01-01'::date)Используется когда нужно объединить несколько индексов.
-- Добавим foreign key индекс (часто забывают!)
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);
-- EXPLAIN ANALYZE для JOIN
EXPLAIN ANALYZE
SELECT o.order_id, o.order_date, p.name, oi.quantity
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.customer_id = 123;
-- Результат:
-- Nested Loop (cost=0.86..50.00 rows=10 width=100)
-- -> Index Scan using idx_orders_customer on orders o
-- Index Cond: (customer_id = 123)
-- -> Index Scan using idx_order_items_order_id on order_items oi
-- Index Cond: (order_id = o.order_id)
-- -> Index Scan using products_pkey on products p
-- Index Cond: (product_id = oi.product_id)Типы JOIN:
Проблема 1: Функция в WHERE ломает индекс
-- Плохо: функция на колонке
EXPLAIN ANALYZE
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';
-- Seq Scan!
-- Хорошо: функциональный индекс
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
-- Теперь Index ScanПроблема 2: Неправильный порядок колонок в составном индексе
-- Индекс: (customer_id, order_date)
-- Запрос:
EXPLAIN ANALYZE
SELECT * FROM orders WHERE order_date > '2025-01-01';
-- Seq Scan! customer_id пропущен
-- Решение: создать отдельный индекс
CREATE INDEX idx_orders_date ON orders(order_date);**Проблема 3: SELECT ***
-- Плохо: выбираем все колонки
SELECT * FROM orders;
-- Хорошо: только нужные колонки
SELECT order_id, order_date, total_amount FROM orders;
-- Может использовать Index Only ScanПроблема 4: Отсутствие LIMIT при сортировке
-- Плохо: сортируем всю таблицу
SELECT * FROM orders ORDER BY created_at DESC;
-- Хорошо: ограничиваем результат
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20;
-- Использует индекс для сортировкиfrom sqlalchemy.orm import joinedload, selectinload
from sqlalchemy import select
# Проблема N+1 запросов
def get_users_with_orders_bad(session):
"""Плохо: N+1 запрос"""
users = session.query(User).all() # 1 запрос
for user in users:
print(user.orders) # N запросов!
def get_users_with_orders_good(session):
"""Хорошо: 2 запроса с eager loading"""
users = session.query(User).options(
joinedload(User.orders) # Предзагрузка через JOIN
).all()
for user in users:
print(user.orders) # Без дополнительных запросов
def get_users_with_many_orders(session):
"""Для коллекций лучше selectinload"""
users = session.query(User).options(
selectinload(User.orders) # Отдельный запрос с IN (...)
).all()
# Использование EXPLAIN в SQLAlchemy
from sqlalchemy.dialects import postgresql
query = select(User).where(User.email == 'test@example.com')
print(str(query.compile(dialect=postgresql.dialect())))-- Создание foreign key
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
ON DELETE CASCADE -- Удаление клиента удаляет заказы
ON UPDATE CASCADE -- Изменение ID клиента обновляет заказы
);
-- Варианты ON DELETE:
-- CASCADE: удаляет зависимые строки
-- SET NULL: устанавливает NULL (колонка должна быть nullable)
-- SET DEFAULT: устанавливает значение по умолчанию
-- RESTRICT: запрещает удаление (проверяет сразу)
-- NO ACTION: запрещает удаление (проверяет в конце транзакции)
-- Добавление FK к существующей таблице
ALTER TABLE order_items
ADD CONSTRAINT fk_order_items_order
FOREIGN KEY (order_id)
REFERENCES orders(order_id)
ON DELETE CASCADE;
-- Удаление FK
ALTER TABLE order_items
DROP CONSTRAINT fk_order_items_order;-- Простые проверки
CREATE TABLE products (
product_id INTEGER PRIMARY KEY,
price DECIMAL(10, 2) CHECK (price > 0),
stock_quantity INTEGER CHECK (stock_quantity >= 0),
status TEXT CHECK (status IN ('active', 'inactive', 'discontinued'))
);
-- Сложные проверки
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
total_amount DECIMAL(10, 2),
shipping_cost DECIMAL(10, 2),
CHECK (total_amount >= shipping_cost), -- Общая сумма >= доставка
CHECK (total_amount >= 0)
);
-- Проверка с несколькими колонками
CREATE TABLE discounts (
product_id INTEGER,
discount_percent DECIMAL(5, 2),
valid_from DATE,
valid_to DATE,
CHECK (discount_percent BETWEEN 0 AND 100),
CHECK (valid_to > valid_from) -- Дата окончания больше даты начала
);-- Уникальность одной колонки
CREATE TABLE users (
user_id INTEGER PRIMARY KEY,
email TEXT UNIQUE NOT NULL -- UNIQUE создаёт индекс автоматически
);
-- Уникальность нескольких колонок (составной UNIQUE)
CREATE TABLE user_roles (
user_id INTEGER,
role_id INTEGER,
granted_by INTEGER,
granted_at TIMESTAMP,
PRIMARY KEY (user_id, role_id),
UNIQUE (user_id, role_id) -- Один пользователь — одна роль
);
-- Частичный UNIQUE (уникальность с условием)
CREATE TABLE user_sessions (
session_id INTEGER PRIMARY KEY,
user_id INTEGER,
is_active BOOLEAN,
UNIQUE (user_id) WHERE is_active = TRUE -- Только одна активная сессия
);CREATE TABLE accounts (
account_id INTEGER PRIMARY KEY,
name TEXT NOT NULL, -- Обязательно
balance DECIMAL(10, 2) NOT NULL DEFAULT 0, -- Значение по умолчанию
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
-- Изменение на NOT NULL (если нет NULL значений)
ALTER TABLE users ALTER COLUMN email SET NOT NULL;
-- Добавление DEFAULT
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active';from sqlalchemy import CheckConstraint, UniqueConstraint, ForeignKeyConstraint
class Product(Base):
__tablename__ = 'products'
id = Column(Integer, primary_key=True)
price = Column(Decimal(10, 2), nullable=False)
stock = Column(Integer, nullable=False, default=0)
status = Column(String, nullable=False)
__table_args__ = (
CheckConstraint('price > 0', name='check_price_positive'),
CheckConstraint('stock >= 0', name='check_stock_non_negative'),
CheckConstraint(
"status IN ('active', 'inactive', 'discontinued')",
name='check_status_valid'
),
UniqueConstraint('name', 'category_id', name='uq_product_name_category'),
)
class Order(Base):
__tablename__ = 'orders'
id = Column(Integer, primary_key=True)
customer_id = Column(
Integer,
ForeignKey('customers.id', ondelete='CASCADE', onupdate='CASCADE'),
nullable=False
)
total_amount = Column(Decimal(10, 2))
shipping_cost = Column(Decimal(10, 2))
__table_args__ = (
CheckConstraint(
'total_amount >= shipping_cost',
name='check_total_ge_shipping'
),
)Иногда нормализация вредит производительности. Денормализация — сознательное нарушение нормализации для ускорения чтения.
Сценарии для денормализации:
Пример 1: Кэширование агрегатов
-- Нормализованная схема
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER,
total_amount DECIMAL(10, 2)
);
-- Запрос: общая сумма заказов клиента (медленно при большом объёме)
SELECT customer_id, SUM(total_amount)
FROM orders
WHERE customer_id = 123
GROUP BY customer_id;
-- Денормализация: храним агрегат
ALTER TABLE customers ADD COLUMN total_orders_amount DECIMAL(10, 2) DEFAULT 0;
-- Обновляем агрегат при каждом изменении orders
CREATE OR REPLACE FUNCTION update_customer_total()
RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
UPDATE customers SET total_orders_amount = total_orders_amount + NEW.total_amount
WHERE customer_id = NEW.customer_id;
ELSIF TG_OP = 'UPDATE' THEN
UPDATE customers SET total_orders_amount = total_orders_amount + NEW.total_amount - OLD.total_amount
WHERE customer_id = NEW.customer_id;
ELSIF TG_OP = 'DELETE' THEN
UPDATE customers SET total_orders_amount = total_orders_amount - OLD.total_amount
WHERE customer_id = OLD.customer_id;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_update_customer_total
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION update_customer_total();
-- Теперь запрос мгновенный
SELECT total_orders_amount FROM customers WHERE customer_id = 123;Пример 2: Дублирование данных для JOIN
-- Нормализованная схема (требует JOIN)
SELECT o.order_id, o.order_date, c.name, c.email
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;
-- Денормализация: дублируем имя и email в orders
ALTER TABLE orders ADD COLUMN customer_name TEXT;
ALTER TABLE orders ADD COLUMN customer_email TEXT;
-- Запрос без JOIN
SELECT order_id, order_date, customer_name, customer_email
FROM orders
WHERE customer_email = 'test@example.com';
-- Триггер для синхронизации
CREATE OR REPLACE FUNCTION sync_customer_data()
RETURNS TRIGGER AS $$
BEGIN
NEW.customer_name := (SELECT name FROM customers WHERE customer_id = NEW.customer_id);
NEW.customer_email := (SELECT email FROM customers WHERE customer_id = NEW.customer_id);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_sync_customer_data
BEFORE INSERT OR UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION sync_customer_data();Пример 3: Материализованные представления
-- Создаём материализованное представление
CREATE MATERIALIZED VIEW customer_order_summary AS
SELECT
c.customer_id,
c.name,
c.email,
COUNT(o.order_id) AS order_count,
SUM(o.total_amount) AS total_spent,
MAX(o.order_date) AS last_order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name, c.email;
-- Создаём индекс на представлении
CREATE INDEX idx_customer_summary_total ON customer_order_summary(total_spent);
-- Запрос к представлению (очень быстро)
SELECT * FROM customer_order_summary
WHERE total_spent > 1000
ORDER BY total_spent DESC
LIMIT 100;
-- Обновление представления (периодически)
REFRESH MATERIALIZED VIEW customer_order_summary;
-- Обновление без блокировки чтений (PostgreSQL 9.4+)
REFRESH MATERIALIZED VIEW CONCURRENTLY customer_order_summary;| Аспект | Нормализация | Денормализация |
|---|---|---|
| Чтение | Медленнее (JOIN) | Быстрее |
| Запись | Быстрее | Медленнее (обновление дубликатов) |
| Целостность | Гарантирована БД | Требует триггеров/кода |
| Размер | Меньше | Больше (дубликаты) |
| Сложность | Выше (много таблиц) | Ниже (меньше JOIN) |
Рекомендации:
Репликация — копирование данных на несколько серверов для:
Типы репликации в PostgreSQL:
Streaming Replication (асинхронная):
-- Настройка primary (postgresql.conf)
wal_level = replica
max_wal_senders = 3
wal_keep_size = 64MB
-- Настройка standby (postgresql.conf)
hot_standby = on
-- standby создаёт файл standby.signal и настраивает primary_conninfoPrimary (read-write)
│
├──► Replica 1 (read-only) ← Для отчётов
├──► Replica 2 (read-only) ← Для аналитики
└──► Replica 3 (read-only) ← Для бэкапа
Синхронная репликация (гарантирует сохранность данных):
-- primary (postgresql.conf)
synchronous_standby_names = 'replica1,replica2'
synchronous_commit = on
-- COMMIT ждёт подтверждения от реплик
-- Задержка записи, но данные не потеряютсяПодключение к реплике в SQLAlchemy:
from sqlalchemy import create_engine
from sqlalchemy.pool import QueuePool
# Primary для записи
primary_engine = create_engine(
'postgresql://user:pass@primary:5432/dbname',
poolclass=QueuePool,
pool_size=10
)
# Replica для чтения
replica_engine = create_engine(
'postgresql://user:pass@replica:5432/dbname',
poolclass=QueuePool,
pool_size=20 -- Больше пул для чтения
)
# Роутинг запросов
class RoutingSession(Session):
def get_bind(self, mapper=None, clause=None):
if self._flushing or self._is_write_operation():
return primary_engine
return replica_engine
def _is_write_operation(self):
# Проверка типа операции
return False
Session = sessionmaker()
session = RoutingSession()
# Запись
session.add(User(name='Alice'))
session.commit() → primary
# Чтение
users = session.query(User).all() → replicaШардирование — горизонтальное разделение данных по разным серверам.
Стратегии шардирования:
1. Шардирование по диапазону:
Шард 1: user_id 1-1000000
Шард 2: user_id 1000001-2000000
Шард 3: user_id 2000001-3000000
2. Шардирование по хэшу:
def get_shard_id(user_id, num_shards=4):
return hash(user_id) % num_shards
# user_id 1 → шард 1
# user_id 2 → шард 2
# user_id 3 → шард 3
# user_id 4 → шард 03. Шардирование по географии:
Шард EU: европейские пользователи
Шард US: американские пользователи
Шард ASIA: азиатские пользователи
Пример шардирования в Python:
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
class ShardedDatabase:
def __init__(self, num_shards=4):
self.num_shards = num_shards
self.engines = {
i: create_engine(f'postgresql://user:pass@shard{i}:5432/dbname')
for i in range(num_shards)
}
self.sessions = {
i: sessionmaker(bind=engine)()
for i, engine in self.engines.items()
}
def get_shard_id(self, user_id):
return hash(user_id) % self.num_shards
def get_session(self, user_id):
shard_id = self.get_shard_id(user_id)
return self.sessions[shard_id]
def get_user(self, user_id):
session = self.get_session(user_id)
return session.query(User).get(user_id)
def create_user(self, user):
session = self.get_session(user.id)
session.add(user)
session.commit()
# Использование
db = ShardedDatabase(num_shards=4)
# Запросы автоматически идут на нужный шард
user = db.get_user(user_id=12345)
db.create_user(User(id=67890, name='Bob'))PostgreSQL Citus для шардирования:
-- Установка расширения Citus
CREATE EXTENSION citus;
-- Шардирование таблицы
SELECT create_distributed_table('users', 'user_id');
-- Citus автоматически распределяет данные по шардам
-- Запросы выполняются параллельно на всех шардах
SELECT COUNT(*) FROM users; -- Параллельно на всех шардах| Подход | Масштабирование | Сложность | Когда использовать |
|---|---|---|---|
| Вертикальное | Ограничено | Низкая | До 100GB данных |
| Репликация | Чтение | Средняя | Чтение >> записи |
| Шардирование | Запись и чтение | Высокая | Очень большие объёмы |
Рекомендации:
Правильное проектирование базы данных критично для производительности и надёжности приложения:
Помните: оптимизируйте только после измерения. Используйте EXPLAIN ANALYZE и мониторинг медленных запросов для выявления реальных проблем.
Вопросы ещё не добавлены
Вопросы для этой подтемы ещё не добавлены.