什么是表分区？

-- a huge `orders` table partitioned BY RANGE on the order date
CREATE TABLE orders (id INT, order_date DATE, amount DECIMAL)
PARTITION BY RANGE (order_date);

CREATE TABLE orders_2023 PARTITION OF orders
  FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
CREATE TABLE orders_2024 PARTITION OF orders
  FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
-- orders is ONE logical table, but stored as separate per-year partitions

-- a query filtered by the partition key only scans RELEVANT partitions
SELECT * FROM orders WHERE order_date >= '2024-06-01';
-- → the database skips orders_2023 entirely, scanning only orders_2024 ("partition pruning")

RANGE → by a value range (dates, numeric ranges) — common for time-series data
LIST  → by a list of discrete values (region, category)
HASH  → by a hash of the key (even distribution across partitions)

✓ Maintenance — drop/archive old data by dropping a whole partition (instant vs DELETE)
  e.g. DROP TABLE orders_2020 — far faster than DELETE WHERE year = 2020
✓ Smaller indexes per partition; parallel operations across partitions
✓ Manage huge tables in manageable pieces

✓ VERY LARGE tables (hundreds of millions / billions of rows) where queries filter
  on a natural partition key (often time/date for logs, events, orders)
✓ Time-series data where you regularly archive/drop old data
✗ Small/medium tables — partitioning adds complexity with little benefit
  (proper indexing usually suffices)
→ Partitioning complements indexing for VERY large tables; it's not a substitute.