MySQL高可用架构：主从复制与分库分表

-- 主库操作
INSERT INTO users (name, age) VALUES ('Zhang San', 25);

-- 主库写binlog
-- binlog内容：
-- Event_type: Query
-- Query: INSERT INTO users (name, age) VALUES ('Zhang San', 25)

-- 从库IO线程：
-- 1. 连接主库，请求binlog
-- 2. 主库binlog dump线程发送binlog
-- 3. 从库IO线程写入relay log

-- 从库SQL线程：
-- 1. 读取relay log
-- 2. 解析SQL语句
-- 3. 执行SQL：INSERT INTO users...
-- 4. 完成数据同步

-- 主库执行
UPDATE users SET age = age + 1 WHERE id > 100;

-- binlog记录
-- Event: UPDATE users SET age = age + 1 WHERE id > 100;

-- 从库执行
-- 执行相同的SQL语句

优势：
  ✅ binlog体积小
  ✅ 日志清晰易读

劣势：
  ❌ 不确定性函数导致主从不一致
  -- 如：NOW()、UUID()、RAND()
  ❌ 主从执行顺序可能不同

-- 主库执行
UPDATE users SET age = 26 WHERE id = 1;

-- binlog记录（实际是二进制格式）
-- Event: UPDATE_ROWS_EVENT
-- Table: users
-- Before: id=1, name='Zhang San', age=25
-- After:  id=1, name='Zhang San', age=26

-- 从库执行
-- 直接应用行变更

优势：
  ✅ 数据一致性强
  ✅ 支持所有SQL语句
  ✅ 可恢复任意时间点数据

劣势：
  ❌ binlog体积大
  ❌ 批量更新日志量大

-- 1. 并行复制（MySQL 5.7+）
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';
SET GLOBAL slave_parallel_workers = 8;

-- 从库使用8个worker线程并行回放

-- 2. 避免大事务
-- 将大事务拆分为小事务
BEGIN;
UPDATE users SET status = 1 WHERE id BETWEEN 1 AND 1000;
COMMIT;

BEGIN;
UPDATE users SET status = 1 WHERE id BETWEEN 1001 AND 2000;
COMMIT;

-- 3. 提升从库硬件
-- 使用SSD、增加内存、提升CPU

-- 4. 读写分离时处理延迟
-- 方案1：强制从主库读（写后读）
-- 方案2：延迟检测，延迟过大时从主库读
SHOW SLAVE STATUS\G
-- Seconds_Behind_Master: 延迟秒数

@Service
public class UserService {

    @Autowired
    private DataSource masterDataSource;  // 主库

    @Autowired
    private DataSource slaveDataSource;   // 从库

    // 写操作：主库
    public void createUser(User user) {
        JdbcTemplate template = new JdbcTemplate(masterDataSource);
        template.update("INSERT INTO users VALUES (?, ?)",
            user.getName(), user.getAge());
    }

    // 读操作：从库
    public User getUser(Long id) {
        JdbcTemplate template = new JdbcTemplate(slaveDataSource);
        return template.queryForObject("SELECT * FROM users WHERE id = ?",
            new Object[]{id}, new UserRowMapper());
    }
}

# ShardingSphere配置
dataSources:
  master:
    url: jdbc:mysql://master:3306/db

  slave1:
    url: jdbc:mysql://slave1:3306/db

  slave2:
    url: jdbc:mysql://slave2:3306/db

rules:
  readwriteSplitting:
    dataSources:
      readwrite_ds:
        writeDataSourceName: master
        readDataSourceNames:
          - slave1
          - slave2
        loadBalancerName: round_robin

// 问题场景
userService.createUser(user);        // 写主库
User newUser = userService.getUser(userId);  // 读从库（可能还未同步）
// newUser可能为null

// 解决方案1：写后读强制走主库
@Transactional
public void createAndGet(User user) {
    userService.createUser(user);
    User newUser = userService.getUserFromMaster(userId);  // 强制主库
}

// 解决方案2：缓存
userService.createUser(user);
cache.put(userId, user);  // 写缓存
User newUser = cache.get(userId);  // 从缓存读

@Transactional
public void transfer(Long fromId, Long toId, BigDecimal amount) {
    // 事务中的所有操作都必须在主库执行
    Account from = accountService.getAccount(fromId);  // 必须主库
    Account to = accountService.getAccount(toId);      // 必须主库

    from.setBalance(from.getBalance().subtract(amount));
    to.setBalance(to.getBalance().add(amount));

    accountService.update(from);
    accountService.update(to);
}

rules:
  sharding:
    tables:
      orders:
        actualDataNodes: ds_${0..15}.orders_${0..63}

        databaseStrategy:
          standard:
            shardingColumn: user_id
            shardingAlgorithmName: db_mod

        tableStrategy:
          standard:
            shardingColumn: user_id
            shardingAlgorithmName: table_mod

    shardingAlgorithms:
      db_mod:
        type: MOD
        props:
          sharding-count: 16

      table_mod:
        type: MOD
        props:
          sharding-count: 64

public class SnowflakeIdGenerator {

    private final long epoch = 1609459200000L;  // 2021-01-01
    private final long workerIdBits = 10L;
    private final long sequenceBits = 12L;

    private final long workerId;
    private long sequence = 0L;
    private long lastTimestamp = -1L;

    public synchronized long nextId() {
        long timestamp = System.currentTimeMillis();

        // 时钟回拨检测
        if (timestamp < lastTimestamp) {
            throw new RuntimeException("Clock moved backwards");
        }

        if (timestamp == lastTimestamp) {
            // 同一毫秒内，序列号+1
            sequence = (sequence + 1) & ((1 << sequenceBits) - 1);
            if (sequence == 0) {
                // 序列号用完，等待下一毫秒
                timestamp = waitNextMillis(lastTimestamp);
            }
        } else {
            sequence = 0L;
        }

        lastTimestamp = timestamp;

        return ((timestamp - epoch) << (workerIdBits + sequenceBits))
            | (workerId << sequenceBits)
            | sequence;
    }
}

-- ID分配表
CREATE TABLE id_generator (
    biz_type VARCHAR(50) PRIMARY KEY,
    max_id BIGINT NOT NULL,
    step INT NOT NULL
);

-- 每次批量获取一段ID
UPDATE id_generator
SET max_id = max_id + step
WHERE biz_type = 'order';

-- 应用层缓存：[1000, 2000)
-- 用完后再申请下一段：[2000, 3000)

特点：
  ✅ 趋势递增
  ✅ 数据库压力小（批量获取）
  ❌ 服务器重启会浪费一段ID

-- 1. 主从延迟
SHOW SLAVE STATUS\G
-- Seconds_Behind_Master: < 1秒正常

-- 2. QPS/TPS
SHOW GLOBAL STATUS LIKE 'Questions';
SHOW GLOBAL STATUS LIKE 'Com_commit';

-- 3. 慢查询
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';

-- 4. 连接数
SHOW STATUS LIKE 'Threads_connected';
SHOW VARIABLES LIKE 'max_connections';