Database Design and Data Management (数据库设计与数据管理)
Domain 5 tests schema design from an ER model, normalisation judgement, indexing, transaction behaviour, and the scaling techniques used when one database is no longer enough. Calculations on isolation levels and shard keys are common.
Schema and normalisation
1NF removes repeating groups, 2NF removes partial dependency on part of a composite key, 3NF removes transitive dependency. In practice e-commerce schemas are denormalised deliberately: goods are modelled as SPU (standard product unit) plus SKU (stock-keeping unit), and order tables store a snapshot of price and address so later catalogue edits do not rewrite history.
Indexes
B+ tree indexes support range scans and sorting; the leftmost-prefix rule governs composite indexes; a covering index answers a query without touching the table. Indexes speed reads and slow writes, so add them against real query patterns, not every column.
Transactions
ACID: atomicity, consistency, isolation, durability. Isolation levels and their anomalies: 读未提交 → 脏读; 读已提交 → 不可重复读; 可重复读 → 幻读; 串行化 → none, at the cost of concurrency. Distributed consistency is usually 最终一致 via message or TCC rather than 2PC everywhere.
Worked example
An order table reaches 500 million rows; queries are "my orders" by buyer and admin reports by date. Reasoning: shard by buyer id hash so a user's orders stay on one shard and the common query stays single-shard; keep a separate read replica or data warehouse for date-range reporting. Sharding by order date would scatter one user's orders across shards and force cross-shard aggregation - the shard key must follow the dominant access path, and any query on a non-shard key becomes a broadcast.
Traps that cost marks
- Believing higher normal form is always better; snapshot and reporting tables are legitimate.
- Adding indexes indiscriminately and slowing inserts.
- Confusing 幻读 with 不可重复读.
- Choosing a shard key that breaks the most frequent query.
- Ignoring replication lag when reads and writes hit different nodes.