Аномалии в исходной таблице Orders(order_id, customer_name, customer_address, product_id, product_name, quantity, order_date)
- Аномалия вставки:
- Нельзя добавить продукт в каталог без заказа (нет отдельной сущности Product) — потеря семантики.
- Нельзя создать клиента без заказа (customer_name хранится только в строках заказов).
- Аномалия обновления:
- Изменить имя клиента/адрес/имя продукта нужно в множествах строк — риск несогласованности.
- Аномалия удаления:
- Удалив последний заказ клиента, потеряются данные о клиенте; удалив последний заказ с продуктом — потеря информации о продукте.
Нормализованная схема (до $\text{3NF}$)
Таблицы и ключи (основные атрибуты, PK/FK):
- Customers
- customer_id (PK)
- name
- address
- дополнительные поля (email, phone) при необходимости
- Products
- product_id (PK)
- name
- price (опционально)
- other_attributes
- Orders
- order_id (PK)
- customer_id (FK → Customers.customer_id)
- order_date
- status, total_amount (вычисляемое/денормализуемое при необходимости)
- OrderItems
- (PK) $(order\_id,product\_id)$ - order_id (FK → Orders.order_id)
- product_id (FK → Products.product_id)
- quantity
- unit_price (фиксировать цену на момент заказа, опционально)
Обоснование 3NF: все неключевые атрибуты зависят только от PK соответствующей таблицы (product_name — в Products, customer_address — в Customers, quantity — в OrderItems). Нет транзитивных зависимостей.
ER-диаграмма (текстово)
Customers (1) —< Orders (M)
Orders (1) —— (1) Products
ASCII-вид:
Customers
PK customer_id
|
1
|
Orders
PK order_id
FK customer_id
|
1
|
OrderItems
PK (order_id, product_id)
FK order_id -> Orders
FK product_id -> Products
|
1
|
Products
PK product_id
Индексы — выбор и обоснование
Рекомендации (OLTP, частые операции: вставка заказов, чтение заказов по клиенту/датам, агрегации по продуктам):
- Базовые PK/кластерные:
- Orders: кластерный индекс на $\text{order\_id}$ (PK).
- OrderItems: кластерный/PK составной $(order\_id,product\_id)$ — быстрый доступ к позициям заказа.
- Customers: PK на $\text{customer\_id}$.
- Products: PK на $\text{product\_id}$.
- Частые запросы и вторичные индексы:
- Поиск заказов клиента (последние заказы): индекс на $(customer\_id,order\_dateDESC)$ — покрывающий для выборок по клиенту и сортировки по дате.
- Диапазонные выборки по дате: индекс на $(order\_date)$ или совместный с customer: $(customer\_id,order\_date)$.
- Отчет по продажам продукта: индекс на $(product\_id,order\_id)$ (может быть некластерный на OrderItems) или покрывающий индекс $(product\_id,order\_date)$ при частых агрегациях по времени.
- Если часто выбирают «последние N заказов» глобально — индекс на $(order\_date)$.
- Для поиска по имени/адресу клиента — полнотекст/GIN/индекс по полям name или по нормализованным ключам, если нужны фильтры.
- Покрывающие индексы (include):
- Для критичных аналитических точечных запросов добавить include-колонки (например, unit_price, quantity) в индекс, чтобы избежать обращений к таблице.
- Примечания:
- Минимизировать число вторичных индексов на сильно изменяемых таблицах (OrderItems) — вставки/обновления стоят дороже.
- Рассмотреть партиционирование Orders по диапазону дат (например, по месяцу/кварталу) для облегчения удаления старых данных и повышения производительности сканирования.
Транзакционная изоляция и стратегия для интенсивной OLTP-нагрузки
- Режим изоляции по умолчанию: рекомендую $\text{READ COMMITTED}$ (PostgreSQL/MySQL InnoDB можно выбрать), потому что он даёт хороший компромисс между согласованностью и низкой конкуренцией блокировок. При необходимости жёсткой консистенции отдельных операций — использовать явные блокировки/транзакции.
- Принципы:
- Держать транзакции короткими: только необходимые операции (INSERT Order + INSERT OrderItems + обновление агрегатов), затем commit.
- Использовать подготовленные выражения/batch inserts для ускорения массовых вставок.
- Для критических обновлений инвентаря/баланса применять SELECT ... FOR UPDATE или оптимистическую стратегию с проверкой версии (row_version) и повтором при конфликте.
- При частых конкурентных обновлениях целесообразен optimistic concurrency + повтор транзакции при сериализационных ошибках (retry policy).
- Настройки БД:
- В PostgreSQL: режим $\text{READ COMMITTED}$ обычно оптимален; при необходимости использовать SERIALIZABLE для критических бизнес-транзакций с логикой retry.
- В MySQL InnoDB: дефолтный $\text{REPEATABLE READ}$ может привести к gap locks при вставках; если это проблема, переключить на $\text{READ COMMITTED}$ или настроить innodb_autoinc_lock_mode, применять short transactions.
- Дополнительно:
- Использовать индексы и FK для поддержания целостности, но избегать тяжёлых каскадов при пиковых нагрузках (ON DELETE RESTRICT или ручная логика удаления/архивации).
- Горизонтальное масштабирование: шардирование по customer_id или по времени (для Orders) при очень большой нагрузке.
- Мониторить и оптимизировать точки блокировок (deadlocks), latency на коммиты и частоту повторов транзакций.
Кратко: нормализуйте в Customers/Products/Orders/OrderItems, ставьте PK/составные PK как предложено, создавайте индексы на $(customer\_id,order\_date)$, $(product\_id)$ и используйте $\text{READ COMMITTED}$ + короткие транзакции и optimistic concurrency для высокой OLTP-нагрузки.