虚拟分片扩容执行步骤

一、执行扩容

1.1 创建新表

-- 在 damai_order_0 库创建新表
USE damai_order_0;
DROP TABLE IF EXISTS `d_order_4`;
CREATE TABLE d_order_4 LIKE d_order_0;
DROP TABLE IF EXISTS `d_order_5`;
CREATE TABLE d_order_5 LIKE d_order_0;
DROP TABLE IF EXISTS `d_order_6`;
CREATE TABLE d_order_6 LIKE d_order_0;
DROP TABLE IF EXISTS `d_order_7`;
CREATE TABLE d_order_7 LIKE d_order_0;

-- 在 damai_order_1 库创建新表
USE damai_order_1;
DROP TABLE IF EXISTS `d_order_4`;
CREATE TABLE d_order_4 LIKE d_order_0;
DROP TABLE IF EXISTS `d_order_5`;
CREATE TABLE d_order_5 LIKE d_order_0;
DROP TABLE IF EXISTS `d_order_6`;
CREATE TABLE d_order_6 LIKE d_order_0;
DROP TABLE IF EXISTS `d_order_7`;
CREATE TABLE d_order_7 LIKE d_order_0;

1.2 执行扩容迁移

• 先启动 migrate-service 服务
• 调用扩容迁移接口：

http://127.0.0.1:6085/damai/migrate/order/data/virtual/expand

迁移规则（基于用户ID）：

用户ID虚拟分片64-127的所有订单 → 迁移到 d_order_4
用户ID虚拟分片192-255的所有订单 → 迁移到 d_order_5
用户ID虚拟分片320-383的所有订单 → 迁移到 d_order_6
用户ID虚拟分片448-511的所有订单 → 迁移到 d_order_7

二、数据验证（扩容后）

2.1 验证数据分布均匀性

快速查看16张表的数据量：

SELECT '库0-表0' AS 表名, COUNT(*) AS 数据量 FROM damai_order_0.d_order_0
UNION ALL SELECT '库0-表1', COUNT(*) FROM damai_order_0.d_order_1
UNION ALL SELECT '库0-表2', COUNT(*) FROM damai_order_0.d_order_2
UNION ALL SELECT '库0-表3', COUNT(*) FROM damai_order_0.d_order_3
UNION ALL SELECT '库0-表4', COUNT(*) FROM damai_order_0.d_order_4
UNION ALL SELECT '库0-表5', COUNT(*) FROM damai_order_0.d_order_5
UNION ALL SELECT '库0-表6', COUNT(*) FROM damai_order_0.d_order_6
UNION ALL SELECT '库0-表7', COUNT(*) FROM damai_order_0.d_order_7
UNION ALL SELECT '库1-表0', COUNT(*) FROM damai_order_1.d_order_0
UNION ALL SELECT '库1-表1', COUNT(*) FROM damai_order_1.d_order_1
UNION ALL SELECT '库1-表2', COUNT(*) FROM damai_order_1.d_order_2
UNION ALL SELECT '库1-表3', COUNT(*) FROM damai_order_1.d_order_3
UNION ALL SELECT '库1-表4', COUNT(*) FROM damai_order_1.d_order_4
UNION ALL SELECT '库1-表5', COUNT(*) FROM damai_order_1.d_order_5
UNION ALL SELECT '库1-表6', COUNT(*) FROM damai_order_1.d_order_6
UNION ALL SELECT '库1-表7', COUNT(*) FROM damai_order_1.d_order_7;

预期结果：

• 每张表约6.25%（1/16）
• 数据量偏差不超过±10%为正常

2.2 ⭐ 核心验证：同一用户订单一致性

-- 验证扩容后，同一用户的所有订单是否仍在同一张表
-- 这是验证新迁移逻辑是否正确的关键

SELECT 
    '🔍 用户订单一致性验证（扩容后）' AS 验证项,
    CASE 
        WHEN inconsistent_count = 0 THEN '✅ 完美！所有用户的订单都在同一张表'
        WHEN inconsistent_count <= total_users * 0.01 THEN '⚠️  有少量用户订单分散'
        ELSE '❌ 严重问题！大量用户订单分散'
    END AS 验证结果,
    total_users AS 总用户数,
    inconsistent_count AS 订单分散的用户数,
    CONCAT(ROUND(inconsistent_count * 100.0 / total_users, 2), '%') AS 问题比率
FROM (
    SELECT 
        COUNT(DISTINCT user_id) AS total_users,
        SUM(CASE WHEN table_count > 1 THEN 1 ELSE 0 END) AS inconsistent_count
    FROM (
        SELECT 
            user_id,
            COUNT(DISTINCT CONCAT(database_name, '.', table_name)) AS table_count
        FROM (
            SELECT user_id, 'damai_order_0' AS database_name, 'd_order_0' AS table_name FROM damai_order_0.d_order_0
            UNION ALL SELECT user_id, 'damai_order_0', 'd_order_1' FROM damai_order_0.d_order_1
            UNION ALL SELECT user_id, 'damai_order_0', 'd_order_2' FROM damai_order_0.d_order_2
            UNION ALL SELECT user_id, 'damai_order_0', 'd_order_3' FROM damai_order_0.d_order_3
            UNION ALL SELECT user_id, 'damai_order_0', 'd_order_4' FROM damai_order_0.d_order_4
            UNION ALL SELECT user_id, 'damai_order_0', 'd_order_5' FROM damai_order_0.d_order_5
            UNION ALL SELECT user_id, 'damai_order_0', 'd_order_6' FROM damai_order_0.d_order_6
            UNION ALL SELECT user_id, 'damai_order_0', 'd_order_7' FROM damai_order_0.d_order_7
            UNION ALL SELECT user_id, 'damai_order_1', 'd_order_0' FROM damai_order_1.d_order_0
            UNION ALL SELECT user_id, 'damai_order_1', 'd_order_1' FROM damai_order_1.d_order_1
            UNION ALL SELECT user_id, 'damai_order_1', 'd_order_2' FROM damai_order_1.d_order_2
            UNION ALL SELECT user_id, 'damai_order_1', 'd_order_3' FROM damai_order_1.d_order_3
            UNION ALL SELECT user_id, 'damai_order_1', 'd_order_4' FROM damai_order_1.d_order_4
            UNION ALL SELECT user_id, 'damai_order_1', 'd_order_5' FROM damai_order_1.d_order_5
            UNION ALL SELECT user_id, 'damai_order_1', 'd_order_6' FROM damai_order_1.d_order_6
            UNION ALL SELECT user_id, 'damai_order_1', 'd_order_7' FROM damai_order_1.d_order_7
        ) all_orders
        GROUP BY user_id
    ) user_table_count
) summary;

-- 详细：列出订单分散的用户（前20个）
SELECT 
    '❌ 订单分散的用户明细' AS 明细,
    user_id AS 用户ID,
    GROUP_CONCAT(DISTINCT table_location ORDER BY table_location) AS 订单分布,
    COUNT(DISTINCT table_location) AS 跨表数量,
    COUNT(*) AS 订单总数
FROM (
    SELECT user_id, CONCAT('damai_order_0.d_order_0') AS table_location FROM damai_order_0.d_order_0
    UNION ALL SELECT user_id, 'damai_order_0.d_order_1' FROM damai_order_0.d_order_1
    UNION ALL SELECT user_id, 'damai_order_0.d_order_2' FROM damai_order_0.d_order_2
    UNION ALL SELECT user_id, 'damai_order_0.d_order_3' FROM damai_order_0.d_order_3
    UNION ALL SELECT user_id, 'damai_order_0.d_order_4' FROM damai_order_0.d_order_4
    UNION ALL SELECT user_id, 'damai_order_0.d_order_5' FROM damai_order_0.d_order_5
    UNION ALL SELECT user_id, 'damai_order_0.d_order_6' FROM damai_order_0.d_order_6
    UNION ALL SELECT user_id, 'damai_order_0.d_order_7' FROM damai_order_0.d_order_7
    UNION ALL SELECT user_id, 'damai_order_1.d_order_0' FROM damai_order_1.d_order_0
    UNION ALL SELECT user_id, 'damai_order_1.d_order_1' FROM damai_order_1.d_order_1
    UNION ALL SELECT user_id, 'damai_order_1.d_order_2' FROM damai_order_1.d_order_2
    UNION ALL SELECT user_id, 'damai_order_1.d_order_3' FROM damai_order_1.d_order_3
    UNION ALL SELECT user_id, 'damai_order_1.d_order_4' FROM damai_order_1.d_order_4
    UNION ALL SELECT user_id, 'damai_order_1.d_order_5' FROM damai_order_1.d_order_5
    UNION ALL SELECT user_id, 'damai_order_1.d_order_6' FROM damai_order_1.d_order_6
    UNION ALL SELECT user_id, 'damai_order_1.d_order_7' FROM damai_order_1.d_order_7
) all_orders
GROUP BY user_id
HAVING COUNT(DISTINCT table_location) > 1
LIMIT 20;

2.3 验证扩容成功

核心指标：

验证项	预期结果	说明
数据分布	16张表数据量接近（偏差≤10%）	执行2.1查看
用户订单一致性	跨表用户数=0	执行2.2查看
数据总量	扩容前后一致	对比前后总数

快速验证脚本：

-- 统计总数据量
SELECT 
    '扩容后总数据量' AS 统计项,
    SUM(cnt) AS 总数
FROM (
    SELECT COUNT(*) AS cnt FROM damai_order_0.d_order_0
    UNION ALL SELECT COUNT(*) FROM damai_order_0.d_order_1
    UNION ALL SELECT COUNT(*) FROM damai_order_0.d_order_2
    UNION ALL SELECT COUNT(*) FROM damai_order_0.d_order_3
    UNION ALL SELECT COUNT(*) FROM damai_order_0.d_order_4
    UNION ALL SELECT COUNT(*) FROM damai_order_0.d_order_5
    UNION ALL SELECT COUNT(*) FROM damai_order_0.d_order_6
    UNION ALL SELECT COUNT(*) FROM damai_order_0.d_order_7
    UNION ALL SELECT COUNT(*) FROM damai_order_1.d_order_0
    UNION ALL SELECT COUNT(*) FROM damai_order_1.d_order_1
    UNION ALL SELECT COUNT(*) FROM damai_order_1.d_order_2
    UNION ALL SELECT COUNT(*) FROM damai_order_1.d_order_3
    UNION ALL SELECT COUNT(*) FROM damai_order_1.d_order_4
    UNION ALL SELECT COUNT(*) FROM damai_order_1.d_order_5
    UNION ALL SELECT COUNT(*) FROM damai_order_1.d_order_6
    UNION ALL SELECT COUNT(*) FROM damai_order_1.d_order_7
) t;