千万级订单表加字段：从 “不敢动“ 到 “大胆改“ 的实战指南

-- 新增字段定义
ALTER TABLE `order` 
ADD COLUMN `promotion_type` tinyint(1) NOT NULL DEFAULT 0 COMMENT '促销类型：0-无促销，1-满减，2-折扣，3-优惠券' 
AFTER `pay_amount`;

-- 说明：
-- 1. 使用tinyint类型，节省存储空间
-- 2. 设置NOT NULL DEFAULT 0，避免NULL值对索引的影响
-- 3. 指定AFTER，控制字段位置，保持表结构清晰
-- 4. 添加COMMENT，提高可维护性

-- 备份订单表
mysqldump -u root -p --databases order_db --tables `order` > order_backup_20231001.sql

# 监控MySQL连接数
while true; do
  date
  mysql -u root -p -e "show global status like 'Threads_connected'"
  sleep 5
done > mysql_connections_monitor.log 2>&1

-- 为订单表新增促销类型字段
ALTER TABLE `order` 
ADD COLUMN `promotion_type` tinyint(1) NOT NULL DEFAULT 0 COMMENT '促销类型：0-无促销，1-满减，2-折扣，3-优惠券' 
AFTER `pay_amount`;

import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
import io.swagger.v3.oas.annotations.media.Schema;

/**
 * 订单实体类
 *
 * @author 果酱
 */
@Data
@Slf4j
@TableName("`order`")
@Schema(description = "订单信息")
public class Order {

    @TableId(type = IdType.AUTO)
    @Schema(description = "订单ID")
    private Long id;

    @Schema(description = "用户ID")
    private Long userId;

    @Schema(description = "订单金额")
    private BigDecimal orderAmount;

    @Schema(description = "支付金额")
    private BigDecimal payAmount;

    /**
     * 新增促销类型字段
     * 0-无促销，1-满减，2-折扣，3-优惠券
     */
    @TableField("promotion_type")
    @Schema(description = "促销类型：0-无促销，1-满减，2-折扣，3-优惠券")
    private Integer promotionType;

    // 其他字段...
}

import org.springframework.stereotype.Service;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.apache.commons.lang3.StringUtils;
import java.util.Objects;
import lombok.extern.slf4j.Slf4j;
import io.swagger.v3.oas.annotations.Operation;
import io.swagger.v3.oas.annotations.tags.Tag;

/**
 * 订单服务实现类
 *
 * @author 果酱
 */
@Service
@Slf4j
@Tag(name = "订单服务", description = "订单相关操作")
public class OrderServiceImpl extends ServiceImpl<OrderMapper, Order> implements OrderService {

    @Override
    @Operation(summary = "创建订单", description = "新增订单记录")
    public Long createOrder(OrderCreateDTO orderCreateDTO) {
        Objects.requireNonNull(orderCreateDTO, "订单创建参数不能为空");
        log.info("开始创建订单，用户ID：{}", orderCreateDTO.getUserId());

        Order order = new Order();
        // 复制其他字段...
        order.setUserId(orderCreateDTO.getUserId());
        order.setOrderAmount(orderCreateDTO.getOrderAmount());
        order.setPayAmount(orderCreateDTO.getPayAmount());

        // 设置新增字段的默认值
        order.setPromotionType(Objects.requireNonNullElse(orderCreateDTO.getPromotionType(), 0));

        save(order);
        log.info("订单创建成功，订单ID：{}", order.getId());
        return order.getId();
    }

    // 其他方法...
}

-- 使用Online DDL新增字段
ALTER TABLE `order` 
ADD COLUMN `promotion_type` tinyint(1) NOT NULL DEFAULT 0 COMMENT '促销类型：0-无促销，1-满减，2-折扣，3-优惠券' 
AFTER `pay_amount`
ALGORITHM=INPLACE 
LOCK=NONE;

-- 查看DDL进度
SELECT * FROM information_schema.processlist WHERE state LIKE 'alter%';

-- 创建影子表，复制原表结构
CREATE TABLE `order_shadow` LIKE `order`;

-- 向影子表添加新字段
ALTER TABLE `order_shadow` 
ADD COLUMN `promotion_type` tinyint(1) NOT NULL DEFAULT 0 COMMENT '促销类型：0-无促销，1-满减，2-折扣，3-优惠券' 
AFTER `pay_amount`;

-- 批量同步历史数据，每次同步10万条
INSERT INTO `order_shadow` 
SELECT *, 0 FROM `order` 
WHERE id > (SELECT MAX(id) FROM `order_shadow`) 
LIMIT 100000;

-- 创建INSERT触发器
DELIMITER //
CREATE TRIGGER `order_after_insert` 
AFTER INSERT ON `order`
FOR EACH ROW
BEGIN
    INSERT INTO `order_shadow` 
    VALUES (NEW.*, 0);
END //
DELIMITER ;

-- 创建UPDATE触发器
DELIMITER //
CREATE TRIGGER `order_after_update` 
AFTER UPDATE ON `order`
FOR EACH ROW
BEGIN
    UPDATE `order_shadow` 
    SET 
        user_id = NEW.user_id,
        order_amount = NEW.order_amount,
        pay_amount = NEW.pay_amount,
        -- 其他字段...
        promotion_type = 0  -- 新增字段默认值
    WHERE id = NEW.id;
END //
DELIMITER ;

-- 比较记录数
SELECT COUNT(*) FROM `order`;
SELECT COUNT(*) FROM `order_shadow`;

-- 随机抽查部分记录
SELECT * FROM `order` WHERE id IN (SELECT id FROM `order` ORDER BY RAND() LIMIT 100);
SELECT * FROM `order_shadow` WHERE id IN (SELECT id FROM `order` ORDER BY RAND() LIMIT 100);

import com.baomidou.mybatisplus.extension.plugins.handler.TableNameHandler;
import org.springframework.stereotype.Component;

/**
 * 订单表动态表名处理器
 *
 * @author 果酱
 */
@Component
public class OrderTableNameHandler implements TableNameHandler {

    /**
     * 是否切换到影子表的开关
     */
    private static boolean useShadowTable = false;

    @Override
    public String dynamicTableName(String sql, String tableName) {
        if ("order".equals(tableName) && useShadowTable) {
            return "order_shadow";
        }
        return tableName;
    }

    /**
     * 设置是否使用影子表
     */
    public static void setUseShadowTable(boolean useShadow) {
        useShadowTable = useShadow;
    }
}

import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.handler.TenantLineHandler;
import com.baomidou.mybatisplus.extension.plugins.inner.DynamicTableNameInnerInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.TenantLineInnerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import java.util.HashMap;
import java.util.Map;

/**
 * MyBatis-Plus配置
 *
 * @author 果酱
 */
@Configuration
public class MyBatisPlusConfig {

    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor(OrderTableNameHandler orderTableNameHandler) {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();

        // 配置动态表名插件
        DynamicTableNameInnerInterceptor dynamicTableNameInnerInterceptor = new DynamicTableNameInnerInterceptor();
        Map<String, TableNameHandler> tableNameHandlerMap = new HashMap<>();
        tableNameHandlerMap.put("order", orderTableNameHandler);
        dynamicTableNameInnerInterceptor.setTableNameHandlerMap(tableNameHandlerMap);

        interceptor.addInnerInterceptor(dynamicTableNameInnerInterceptor);
        return interceptor;
    }
}

import org.springframework.stereotype.Service;
import lombok.extern.slf4j.Slf4j;
import io.swagger.v3.oas.annotations.Operation;
import io.swagger.v3.oas.annotations.tags.Tag;

/**
 * 表切换服务
 *
 * @author 果酱
 */
@Service
@Slf4j
@Tag(name = "表切换服务", description = "订单表与影子表切换")
public class TableSwitchService {

    @Operation(summary = "切换到影子表", description = "将读写流量切换到影子表")
    public void switchToShadowTable() {
        log.info("开始切换到影子表");
        OrderTableNameHandler.setUseShadowTable(true);
        log.info("已切换到影子表");
    }

    @Operation(summary = "切换回原表", description = "将读写流量切换回原表")
    public void switchToOriginalTable() {
        log.info("开始切换回原表");
        OrderTableNameHandler.setUseShadowTable(false);
        log.info("已切换回原表");
    }
}

// 伪代码，实际应通过接口调用
tableSwitchService.switchToShadowTable();

-- 删除原表
DROP TABLE `order`;

-- 将影子表重命名为原表名
ALTER TABLE `order_shadow` RENAME TO `order`;

-- 删除触发器
DROP TRIGGER `order_after_insert`;
DROP TRIGGER `order_after_update`;

// 伪代码
tableSwitchService.switchToOriginalTable();

#!/bin/bash
# 批量为分表新增字段

# 数据库连接信息
USER="root"
PASSWORD="your_password"
DATABASE="order_db"

# 分表数量
TABLE_COUNT=16

# 循环为每个分表添加字段
for ((i=0; i<TABLE_COUNT; i++)); do
  TABLE_NAME="order_$i"
  echo "开始处理表：$TABLE_NAME"

  # 执行ALTER语句
  mysql -u $USER -p$PASSWORD $DATABASE -e "
    ALTER TABLE $TABLE_NAME 
    ADD COLUMN \`promotion_type\` tinyint(1) NOT NULL DEFAULT 0 COMMENT '促销类型：0-无促销，1-满减，2-折扣，3-优惠券' 
    AFTER \`pay_amount\`
    ALGORITHM=INPLACE 
    LOCK=NONE;
  "

  # 检查执行结果
  if [ $? -eq 0 ]; then
    echo "表 $TABLE_NAME 处理成功"
  else
    echo "表 $TABLE_NAME 处理失败"
    exit 1
  fi
done

echo "所有分表处理完成"

-- 检查分表结构
DESCRIBE order_0;
DESCRIBE order_1;
-- ... 检查其他分表

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import static org.junit.jupiter.api.Assertions.*;

/**
 * 订单表新增字段功能测试
 *
 * @author 果酱
 */
@SpringBootTest
public class OrderFieldAdditionTest {

    @Autowired
    private OrderService orderService;

    @Autowired
    private OrderMapper orderMapper;

    @Test
    public void testPromotionTypeField() {
        // 创建测试订单
        OrderCreateDTO createDTO = new OrderCreateDTO();
        createDTO.setUserId(10001L);
        createDTO.setOrderAmount(new BigDecimal("99.99"));
        createDTO.setPayAmount(new BigDecimal("99.99"));
        createDTO.setPromotionType(1); // 满减

        Long orderId = orderService.createOrder(createDTO);
        assertNotNull(orderId, "订单创建失败");

        // 查询订单，验证字段值
        Order order = orderMapper.selectById(orderId);
        assertNotNull(order, "订单查询失败");
        assertEquals(1, order.getPromotionType().intValue(), "促销类型字段值不正确");

        // 测试默认值
        OrderCreateDTO defaultDTO = new OrderCreateDTO();
        defaultDTO.setUserId(10001L);
        defaultDTO.setOrderAmount(new BigDecimal("199.99"));
        defaultDTO.setPayAmount(new BigDecimal("199.99"));
        // 不设置promotionType，使用默认值

        Long defaultOrderId = orderService.createOrder(defaultDTO);
        Order defaultOrder = orderMapper.selectById(defaultOrderId);
        assertEquals(0, defaultOrder.getPromotionType().intValue(), "促销类型默认值不正确");
    }
}

-- 为新增字段添加索引
ALTER TABLE `order` 
ADD INDEX idx_promotion_type (`promotion_type`) 
ALGORITHM=INPLACE 
LOCK=NONE;

-- 查看SQL执行计划
EXPLAIN SELECT * FROM `order` WHERE promotion_type = 1;

/**
 * 订单缓存服务
 *
 * @author 果酱
 */
@Service
@Slf4j
public class OrderCacheService {

    @Autowired
    private StringRedisTemplate redisTemplate;

    @Autowired
    private OrderMapper orderMapper;

    private static final String ORDER_KEY_PREFIX = "order:info:";
    private static final Duration EXPIRATION = Duration.ofHours(24);

    /**
     * 获取订单信息（带缓存）
     */
    public Order getOrderById(Long orderId) {
        Objects.requireNonNull(orderId, "订单ID不能为空");

        String key = ORDER_KEY_PREFIX + orderId;
        String json = redisTemplate.opsForValue().get(key);

        if (StringUtils.hasText(json)) {
            log.info("从缓存获取订单信息，订单ID：{}", orderId);
            return JSON.parseObject(json, Order.class);
        }

        log.info("从数据库获取订单信息，订单ID：{}", orderId);
        Order order = orderMapper.selectById(orderId);
        if (Objects.nonNull(order)) {
            redisTemplate.opsForValue().set(key, JSON.toJSONString(order), EXPIRATION);
        }

        return order;
    }

    /**
     * 更新订单缓存，包含新增字段
     */
    public void updateOrderCache(Order order) {
        Objects.requireNonNull(order, "订单对象不能为空");
        Objects.requireNonNull(order.getId(), "订单ID不能为空");

        String key = ORDER_KEY_PREFIX + order.getId();
        redisTemplate.opsForValue().set(key, JSON.toJSONString(order), EXPIRATION);
        log.info("更新订单缓存，订单ID：{}", order.getId());
    }
}

# prometheus.yml 配置示例
scrape_configs:
  - job_name: 'mysql'
    static_configs:
      - targets: ['mysql-exporter:9104']
    metrics_path: '/metrics'
    scrape_interval: 10s

-- 查看所有进程
SHOW PROCESSLIST;

-- 终止指定进程
KILL [进程ID];

# 使用pt-online-schema-change新增字段
pt-online-schema-change --alter "ADD COLUMN promotion_type tinyint(1) NOT NULL DEFAULT 0 COMMENT '促销类型'" \
  D=order_db,t=order \
  --user=root --password=your_password \
  --execute

EXPLAIN [问题SQL语句];

-- 重建索引
ALTER TABLE `order` 
DROP INDEX idx_promotion_type,
ADD INDEX idx_promotion_type (`promotion_type`)
ALGORITHM=INPLACE 
LOCK=NONE;

-- 临时增大从库的IO和SQL线程缓存
SET GLOBAL slave_net_timeout = 3600;
SET GLOBAL sql_slave_skip_counter = 1;