-- 先看看現(xiàn)在的“房子”有多大
SELECT 
    TABLE_SCHEMA as '數(shù)據(jù)庫',
    TABLE_NAME as '表名',
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) as '大小(MB)',
    TABLE_ROWS as '行數(shù)'
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = '你的數(shù)據(jù)庫名'
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;

-- 思考人生三連問：
-- 1. 按業(yè)務(wù)分？(用戶相關(guān)、訂單相關(guān)、商品相關(guān))
-- 2. 按時(shí)間分？(2024訂單、2025訂單)
-- 3. 按地域分？(北京用戶、上海用戶)

-- 原來都擠在一個(gè)庫里
CREATE DATABASE single_apartment;

-- 現(xiàn)在買棟樓，每層一個(gè)專業(yè)戶
CREATE DATABASE user_villa;      -- 用戶專屬樓層
CREATE DATABASE order_mansion;   -- 訂單豪華層
CREATE DATABASE product_tower;   -- 商品展示層

-- 用戶表搬到用戶樓層
CREATE TABLE user_villa.user_info (
    user_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    -- 用戶相關(guān)的其他字段...
);

-- 訂單表搬到訂單樓層  
CREATE TABLE order_mansion.order_info (
    order_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    amount DECIMAL(10, 2),
    status TINYINT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    -- 訂單相關(guān)的其他字段...
);

-- 訂單表按月份拆分，再也不怕找去年的訂單像考古了
-- 2024年訂單表
CREATE TABLE order_mansion.order_202401 (
    order_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    -- ... 其他字段
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id),
    INDEX idx_created_at (created_at)
) PARTITION BY RANGE (MONTH(created_at)) (
    PARTITION p1 VALUES LESS THAN (2),
    PARTITION p2 VALUES LESS THAN (3),
    -- ... 更多分區(qū)
);

-- 2024年2月訂單表
CREATE TABLE order_mansion.order_202402 (
    -- 結(jié)構(gòu)同上
);

-- 用戶表按ID取模分表，16張表夠不夠？
-- 先創(chuàng)建用戶表模板
DELIMITER $$

CREATE PROCEDURE create_user_tables()
BEGIN
    DECLARE i INT DEFAULT 0;
    WHILE i < 16 DO
        SET @table_name = CONCAT('user_villa.user_info_', LPAD(i, 2, '0'));
        SET @sql = CONCAT('
            CREATE TABLE IF NOT EXISTS ', @table_name, ' (
                user_id BIGINT PRIMARY KEY AUTO_INCREMENT,
                username VARCHAR(50) NOT NULL,
                email VARCHAR(100),
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_username (username)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        ');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        SET i = i + 1;
    END WHILE;
END$$

DELIMITER ;

-- 執(zhí)行創(chuàng)建
CALL create_user_tables();

// Java代碼示例：數(shù)據(jù)路由導(dǎo)航
public class ShardingNavigator {
    
    // 用戶表路由：根據(jù)user_id決定去哪個(gè)表
    public static String getUserTableName(long userId) {
        int tableIndex = (int) (userId % 16);
        return String.format("user_info_%02d", tableIndex);
    }
    
    // 訂單表路由：根據(jù)時(shí)間決定去哪個(gè)表
    public static String getOrderTableName(Date orderTime) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMM");
        return "order_" + sdf.format(orderTime);
    }
    
    // 組合查詢：跨表查詢就像組織家庭聚會(huì)
    public List<Order> getUserOrders(long userId, Date startTime, Date endTime) {
        List<Order> allOrders = new ArrayList<>();
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(startTime);
        
        // 遍歷時(shí)間范圍內(nèi)的所有月份表
        while (calendar.getTime().before(endTime)) {
            String tableName = getOrderTableName(calendar.getTime());
            String sql = "SELECT * FROM " + tableName + " WHERE user_id = ?";
            // 執(zhí)行查詢并添加到結(jié)果集
            // ...
            calendar.add(Calendar.MONTH, 1);
        }
        return allOrders;
    }
}

# ShardingSphere配置文件示例（YAML格式）
# 這位管家知道所有房間在哪
dataSources:
  ds0:
    url: jdbc:mysql://localhost:3306/user_villa?useSSL=false
    username: root
    password: your_password
  ds1:
    url: jdbc:mysql://localhost:3307/order_mansion?useSSL=false
    username: root
    password: your_password

shardingRule:
  tables:
    user_info:
      actualDataNodes: ds0.user_info_${0..15}
      tableStrategy:
        inline:
          shardingColumn: user_id
          algorithmExpression: user_info_${user_id % 16}
    order_info:
      actualDataNodes: ds1.order_${2024..2025}${1..12}
      tableStrategy:
        standard:
          shardingColumn: created_at
          preciseAlgorithmClassName: com.example.TimeShardingAlgorithm
  
  bindingTables:
    - user_info,order_info

-- 第一步：先抄家（備份）
CREATE TABLE order_mansion.order_202401_new LIKE order_mansion.order_202401;

-- 第二步：慢慢搬（增量遷移）
INSERT INTO order_mansion.order_202401_new
SELECT * FROM single_apartment.orders 
WHERE created_at >= '2024-01-01' 
  AND created_at < '2024-02-01';

-- 第三步：檢查有沒有落下的
SELECT 
    (SELECT COUNT(*) FROM single_apartment.orders 
     WHERE created_at >= '2024-01-01' AND created_at < '2024-02-01') as old_count,
    (SELECT COUNT(*) FROM order_mansion.order_202401_new) as new_count;

-- 第四步：切換門牌號(hào)（重命名）
RENAME TABLE 
    order_mansion.order_202401 TO order_mansion.order_202401_backup,
    order_mansion.order_202401_new TO order_mansion.order_202401;

// 雪花算法：Twitter出品，必屬精品
public class SnowflakeIdGenerator {
    private final long twepoch = 1288834974657L;
    private final long workerIdBits = 5L;
    private final long datacenterIdBits = 5L;
    private final long sequenceBits = 12L;
    
    private long workerId;
    private long datacenterId;
    private long sequence = 0L;
    private long lastTimestamp = -1L;
    
    public synchronized long nextId() {
        long timestamp = timeGen();
        
        if (timestamp < lastTimestamp) {
            throw new RuntimeException("時(shí)間倒流了，檢查系統(tǒng)時(shí)間！");
        }
        
        if (lastTimestamp == timestamp) {
            sequence = (sequence + 1) & 4095;  // 2^12-1
            if (sequence == 0) {
                timestamp = tilNextMillis(lastTimestamp);
            }
        } else {
            sequence = 0L;
        }
        
        lastTimestamp = timestamp;
        
        return ((timestamp - twepoch) << 22) |
               (datacenterId << 17) |
               (workerId << 12) |
               sequence;
    }
    
    // 全局唯一，趨勢(shì)遞增，適合分庫分表
}

// 使用Seata等分布式事務(wù)解決方案
@GlobalTransactional
public void transferMoney(long fromUser, long toUser, BigDecimal amount) {
    // 1. 從用戶庫扣款
    // 2. 向訂單庫插入記錄
    // 要么都成功，要么都回滾
}

-- 分表前：一步到位
SELECT * FROM orders WHERE user_id = 123;

-- 分表后：變成偵查行動(dòng)
SELECT * FROM order_202401 WHERE user_id = 123
UNION ALL
SELECT * FROM order_202402 WHERE user_id = 123
UNION ALL
-- ... 查遍所有月份表

// 1. 先從用戶庫查用戶
List<User> users = userDao.getUsers(condition);
List<Long> userIds = users.stream().map(User::getId).collect(Collectors.toList());

// 2. 再去訂單庫查這些用戶的訂單
List<Order> orders = orderDao.getOrdersByUserIds(userIds);

// 3. 在內(nèi)存中組裝
Map<Long, List<Order>> userOrdersMap = groupOrdersByUser(orders);