Sharding Concepts

-- Good shard keys:
-- ✓ user_id (for user-centric workloads — queries are per-user)
-- ✓ tenant_id (SaaS multi-tenancy — each tenant to one shard)
-- ✓ order_id with hash (uniform write distribution)

-- Bad shard keys:
-- ✗ created_at (sequential inserts → all writes to last shard — hot spot)
-- ✗ status ('pending'/'shipped' — low cardinality → uneven distribution)
-- ✗ country (most traffic from US/IN → imbalanced shards)

-- Hash-based routing example (application layer)
public class ShardRouter {
    private static final int NUM_SHARDS = 8;

    public int getShardId(long userId) {
        return (int) (userId % NUM_SHARDS);
    }

    public DataSource getDataSource(long userId) {
        int shard = getShardId(userId);
        return dataSourceMap.get("shard-" + shard);
    }
}

-- Directory-based routing (lookup table on a config DB)
-- SELECT shard_id FROM shard_directory WHERE tenant_id = ?

# Vitess VSchema — define sharding rules
{
  "sharded": true,
  "vindexes": {
    "hash": {
      "type": "hash"     // hash vindex on the shard key
    }
  },
  "tables": {
    "orders": {
      "columnVindexes": [
        { "column": "user_id", "name": "hash" }
      ]
    }
  }
}

# Application uses standard MySQL driver — Vitess routes transparently
mysql -h vtgate-host -P 3306 -u app -p shop

-- This query is automatically routed to the correct shard:
SELECT * FROM orders WHERE user_id = 12345;

-- This scatter-gather (no shard key) fans out to all shards:
SELECT COUNT(*) FROM orders WHERE status = 'PENDING';
-- Vitess aggregates results — expensive, avoid for hot paths

-- Cross-shard JOIN — avoid in application hot paths
-- Instead of: SELECT o.*, c.email FROM orders o JOIN customers c ON o.customer_id = c.id
-- ✓ Denormalise: store email inside orders row (accept duplication)
-- ✓ Or: fetch orders, then batch-fetch customers by ID in application code

-- Global unique ID — Snowflake-style (64-bit)
// 41 bits timestamp | 10 bits machine ID | 12 bits sequence
public class SnowflakeIdGenerator {
    private final long machineId;
    private long lastTimestamp = -1L;
    private long sequence = 0L;

    public synchronized long nextId() {
        long ts = System.currentTimeMillis();
        if (ts == lastTimestamp) {
            sequence = (sequence + 1) & 0xFFF;  // 12-bit max
            if (sequence == 0) ts = waitNextMs(ts);
        } else { sequence = 0; }
        lastTimestamp = ts;
        return ((ts - EPOCH) << 22) | (machineId << 12) | sequence;
    }
}