Аномалии и проблемы
- Дублирование данных: поле product_list (список товаров) и customer_address в Orders хранят повторяющиеся сведения о товарах и клиентах — нарушается нормализация, тяжелые обновления и рост размера строки.
- Ненормализованные данные: product_list как CSV/JSON мешает фильтрации, агрегациям, индексированию и целостности FK.
- Ошибка расчёта total_amount: хранение агрегата, зависящего от OrderItems, приводит к рассинхронизации.
- Транзакции/конкуренция: параллельные продажи могут привести к oversell; длинные транзакции/блокировки ухудшают пропускную способность.
- Индексация: отсутствие индексов по customer_id, order_date, product_id — медленные выборки по клиентам/дате/товарам.
- Архивация/рост: единая таблица Orders быстро растёт — ухудшение планов запросов и IO.
Нормализация (до $3$НФ)
Предлагаемая схема (ключевые таблицы):
Customers
- customer_id PK
- name
- email (UNIQUE)
- phone
- created_at
Addresses
- address_id PK
- customer_id FK -> Customers(customer_id)
- country, region, city, street, postal_code, is_default
Products
- product_id PK
- sku (UNIQUE)
- name
- description
- price
- stock_quantity
- attributes (json) — если нужно
Orders
- order_id PK
- customer_id FK -> Customers(customer_id)
- billing_address_id FK -> Addresses(address_id)
- shipping_address_id FK -> Addresses(address_id)
- order_date
- status
- total_amount (денормализованно, поддерживаемое через транзакцию/триггер)
OrderItems
- order_item_id PK
- order_id FK -> Orders(order_id)
- product_id FK -> Products(product_id)
- quantity (CHECK quantity > $0$)
- unit_price
- line_total (unit_price * quantity)
Payments
- payment_id PK
- order_id FK -> Orders(order_id)
- amount
- method
- status
- paid_at
Дополнительно: OrderEvents/OrderStatusHistory для истории статусов, ProductInventoryTransactions для логов движения склада.
Пример DDL (сильно сокращённо)
- приведите DDL при необходимости — здесь сокращённо:
CREATE TABLE Customers(...);
CREATE TABLE Addresses(...);
CREATE TABLE Products(...);
CREATE TABLE Orders(...);
CREATE TABLE OrderItems(...);
CREATE TABLE Payments(...);
Индексы и партиционирование
- PK и FK автоматически индексировать.
- Дополнительные индексы:
- Customers(email) UNIQUE.
- Orders(customer_id, order_date) — покрывающий для запросов истории клиента.
- Orders(order_date) — для аналитики/партиций.
- Orders(status, order_date) — быстрые выборки по статусу.
- OrderItems(order_id, product_id) — покрывающий для получения позиций заказа.
- Products(sku) UNIQUE, Products(name) — полнотекст для поиска.
- Addresses(customer_id).
- Payments(order_id) UNIQUE/INDEX.
- Партиционирование Orders по RANGE(order_date) (по месяцам/годам) для управления старением данных и ускорения сканов.
- Использовать покрывающие индекс(ы) для часто запрашиваемых комбинаций полей (например, (customer_id, status, order_date)).
Транзакционная стратегия (высокий трафик)
- Общая идея: короткие ACID‑транзакции, минимальные блокировки, атомарные операции на складе.
- Процесс создания заказа (упрощённо):
$(1)$ Проверка идемпотентности запроса (idempotency_key) — чтобы избежать дубли.
$(2)$ Начать транзакцию.
$(3)$ Для каждого товара попытаться атомарно уменьшить запас:
UPDATE Products SET stock_quantity = stock_quantity - :qty
WHERE product_id = :pid AND stock_quantity >= :qty;
Проверить affected_rows; если $0$ — недостаточно товара → откат.
Этот подход — оптимистично-пессимистический, без явной блокировки строк.
$(4)$ Вставить Orders, OrderItems, Payments.
$(5)$ Обновить/пересчитать total_amount на основе OrderItems (или вычислить до вставки) и закоммитить.
$(6)$ Если внешние сервисы (платёж, логистика) — использовать SAGA/компенсации или pattern outbox + worker для асинхронной надёжной интеграции.
- Альтернативы для инвентаря:
- Пессимистическая блокировка: SELECT ... FOR UPDATE (короткие транзакции) — безопасно, но может снижать параллелизм.
- Оптимистичная блокировка: version/timestamp и UPDATE ... WHERE version = :v — откат и retry при конфликте.
- Атомарный UPDATE как выше — обычно предпочтителен для высокой нагрузки.
- Уровень изоляции:
- Рекомендуется Read Committed для высокой производительности; для критичных операций можно использовать Repeatable Read/Serializable выборочно.
- Предотвращение дедлоков:
- Сортировать обновляемые строки по product_id, минимизировать время транзакции; повторять при конфликте.
- Идемпотентность и повторные запросы:
- Хранить idempotency_key/operation_id в Orders/Payments, откатывать дубли.
Производительность и масштабирование
- Кеширование: кэшировать неизменяемые данные (Products) в Redis; использовать write-through/invalidate.
- Асинхронные операции: отправка писем/уведомлений, логирование — вне критической транзакции (через очереди).
- Материализованные представления/ETL для аналитики (OLAP) — отделить от OLTP.
- Горизонтальное масштабирование: шардинг по customer_id или region при экстремальной нагрузке.
- Мониторинг: метрики задержек транзакций, частоты откатов, конфликтов блокировок, горячие индексы.
Контроль целостности
- FK, CHECK‑ы и триггеры для проверки суммы/валидности.
- Outbox pattern для гарантированной доставки событий в другие сервисы без распределённой транзакции.
Краткая сводка действий для внедрения
- Нормализовать: вынести Customers, Addresses, Products, OrderItems, Payments.
- Внедрить индексную стратегию (см. выше).
- Использовать атомарные UPDATE для уменьшения stock и идемпотентность для заказов.
- Партиционировать Orders по дате и кэшировать справочные данные.
- Протестировать нагрузки, конфликты и откаты, затем корректировать изоляцию/ретраи.
Если нужно — могу прислать конкретный SQL DDL и пример транзакционного кода (сниппет) для вашей СУБД (Postgres/MySQL).