最近遇到的一道电商下单面试题(附代码)

背景: 存量订单10亿级别，日订单增长百万量级 主查询场景如下:

• 1.买家频繁查询我的订单，高峰期并发100左右，实时性要求高
• 2.卖家频繁查询我的订单，高峰期并发30左右，允许秒级延迟
• 3.平台客服频繁搜素客诉订单(半年之内订单，订单尾号，买家姓名搜索)，高峰期并发10左右，允许分钟级延迟
• 4. 库存扣减仅在缓存实现，假设业务为缓存场景，需要考虑高并发(100每条)，避免超卖，要求无锁设计

目前有两张表 order和Inventory 代表订单和库存

库存扣减方案
- 1.库存扣减可以用redis的"DECRBY"命令将存储库存值减少，这个redis的原子命令，不需要锁

- 2.拿到这个命令返回值就是剩余库存量，通过判断剩余库存量大于0则秒杀成功；如果小于0则秒杀失败，之前"DECRBY"命令扣减的库存要加回来，避免超卖，少买

数据库设计方案:
1.针对10亿数据进行分库分表，对买家，卖家订单分开来村，也就是冗余订单数据，实时性要求不一样
2.针对买家的订单表，分库键用买家id,分表键用买家id加上日期，原则上同一个买家订单不允许落到不同的库，也就是不允许跨库，允许跨少量的分表
3.针对卖家的订单表，分库键用卖家id,分表键用卖家id加上日期，原则上同一个卖家订单不允许落到不同的库，允许跨大量的分表，查询时间限定日期不超过三个月，保证跨到分表也不多
4.平台客服频繁查询订单，先按照用户id确定订单在哪个库，再按买家id，订单后5位，时间作为条件查询，由对应的组合索引提高效率

5.正常可查询的订单范围时间维度在1-3年即可，超过的数据可以同步到搜索引擎变成冷数据

代码用例 数据表结构

CREATE TABLE `orders` (
  `id` int(50) NOT NULL COMMENT '主键id',
  `user_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '购买人',
  `seller_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '卖家',
  `sku_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'SkuId',
  `amount` int(11) NOT NULL DEFAULT '0' COMMENT '购买数量',
  `money` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '购买金额',
  `pay_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '购买时间',
  `pay_status` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '支付状态',
  `del_flag` tinyint(1) NOT NULL DEFAULT '0' COMMENT '删除标志（0代表存在 1代表删除）',
  `create_by` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建人',
  `create_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '修改人',
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  `index_id_last_5` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '存储订单表id的后面5个字符，用作客服订单尾号查询',
  PRIMARY KEY (`id`),
  KEY `orders_index_user_id` (`user_id`) USING BTREE COMMENT '买家id索引，用在买家快速查看自己的订单',
  KEY `orders_index_seller_id` (`seller_id`) USING BTREE COMMENT '卖家id索引，用在卖家快速查看自己的订单',
  KEY `orders_index_create_time` (`create_time`) USING BTREE COMMENT '时间索引',
  KEY `orders_index_for_customer` (`user_id`,`index_id_last_5`,`create_time`) USING BTREE COMMENT '用户id、订单号后面5个字符串、时间的组合索引，供客服搜索客诉订单(半年之内订单, 订单尾号，买家姓名搜索)'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

public class InventoryRepositoryImpl implements InventoryRepository {

    @Autowired
    private RedisTemplate<String, Object> redisTemplate;

    /**
     * 根据skuId获得库存情况
     *
     * @param skuId
     * @return
     */
    @Override
    public Inventory getInventory(String skuId) {
        String key = RedisConf.INVENTORY_KEY + skuId;
        if (redisTemplate.hasKey(key)) {
            Integer sellableQuantity = (Integer) redisTemplate.opsForHash().get(key, RedisConf.INVENTORY_SELLABLE_QUANTITY);
            Integer withholdingQuantity = (Integer) redisTemplate.opsForHash().get(key, RedisConf.INVENTORY_WITHHOLDING_QUANTITY);
            Integer occupiedQuantity = (Integer) redisTemplate.opsForHash().get(key, RedisConf.INVENTORY_OCCUPIED_QUANTITY);
            sellableQuantity = sellableQuantity == null ? Integer.valueOf(0) : sellableQuantity;
            withholdingQuantity = withholdingQuantity == null ? Integer.valueOf(0) : withholdingQuantity;
            occupiedQuantity = occupiedQuantity == null ? Integer.valueOf(0) : occupiedQuantity;
            return new Inventory(skuId, sellableQuantity.longValue(), withholdingQuantity.longValue(), occupiedQuantity.longValue());
        }
        return null;
    }

    /**
     * 预占库存
     *
     * @param skuId
     * @param amount
     * @return
     */
    @Override
    public Boolean withHolding(String skuId, Integer amount) {
        String key = RedisConf.INVENTORY_KEY + skuId;
        if (redisTemplate.hasKey(key) && (Integer) redisTemplate.opsForHash().get(key, RedisConf.INVENTORY_SELLABLE_QUANTITY) >= amount) {
            if (redisTemplate.opsForHash().increment(key, RedisConf.INVENTORY_SELLABLE_QUANTITY, -amount) >= 0) {
                redisTemplate.opsForHash().increment(key, RedisConf.INVENTORY_WITHHOLDING_QUANTITY, amount);
                return true;
            } else {
                //存在超卖，预占库存失败
                redisTemplate.opsForHash().increment(key, RedisConf.INVENTORY_SELLABLE_QUANTITY, amount);
            }
        }
        return false;
    }

    /**
     * 占用库存
     *
     * @param skuId
     * @param amount
     * @return
     */
    @Override
    public Boolean occupied(String skuId, Integer amount) {
        String key = RedisConf.INVENTORY_KEY + skuId;
        if (redisTemplate.hasKey(key) && (Integer) redisTemplate.opsForHash().get(key, RedisConf.INVENTORY_WITHHOLDING_QUANTITY) >= amount) {
            if (redisTemplate.opsForHash().increment(key, RedisConf.INVENTORY_WITHHOLDING_QUANTITY, -amount) >= 0) {
                redisTemplate.opsForHash().increment(key, RedisConf.INVENTORY_OCCUPIED_QUANTITY, amount);
                return true;
            } else {
                //占用异常
                redisTemplate.opsForHash().increment(key, RedisConf.INVENTORY_WITHHOLDING_QUANTITY, amount);
//                throw new BusinessException("异常");
            }
        }
        return false;
    }

    /**
     * 释放预占库存
     * @param skuId
     * @param amount
     * @return
     */
    @Override
    public Boolean releaseHolding(String skuId, Integer amount) {
        String key = RedisConf.INVENTORY_KEY + skuId;
        if (redisTemplate.hasKey(key)) {
            redisTemplate.opsForHash().increment(key, RedisConf.INVENTORY_WITHHOLDING_QUANTITY, -amount);
            redisTemplate.opsForHash().increment(key, RedisConf.INVENTORY_SELLABLE_QUANTITY, amount);
            return true;
        }
        return false;
    }
}

上一种方案当中，为了保证卖家查询秒级延迟将卖家的订单分拆到不同节点的库，以三个月为期限尽可能在同一个节点查询订单，减少跨库查询的延迟时间，但对冷热数据的边界划分的仍不是很好，此外库存基于乐观锁的方法蛮好，但是在并发高的场景下 库存可能还是有概率读写不一致

改进后的方案

 - 1. 买家频繁查询我的订单, 高峰期并发100左右。实时性要求高。
    用户进行查询的时候，根据用户编号可以直接落定到确定数据表中，针对不到1000万行到数据，实时性能够保证。
    月数据量在3000起，以目前表结构并不复杂，2000万行数据性能还不算低下。
    数据库采用多(≥1)主多(≥1)从的形式部署，主库增删改，从库查询。
    但是针对日益增长的的数据，需要进行分库分表存储。订单号规则为 {订单号}{分隔符}{时间}{分隔符}{会员号后6位 不足补齐0}。
    针对存量10亿的数据，进行分库分表存储。为保证表的可用性，和可拓展性，原则上一张表在500-1000万就得进行分表（分库）存储。
    1,000,000,000 / 8库 / 32表 ≈ 平均390万行数据， 考虑到这种方式不可能绝对到平均，但是已经留出冗余的空间了。
    日增长 1,000,000 / 8库 / 32表 ≈ 平均日增长4000行数据。
    根据用户的会员号对8进行取余算库，对32进行取余算表。
    以这种形式进行分库，需要很久的时间才达到瓶颈。但是一般用户查询较早期的订单这种频率是很低的，以4000行数据的日增长，一年约为500万，这个数据量在性能上的影响非常小。
    所以这里设想以年为单位进行数据转移到冷备库，不影响高频访问的主要库（非主从意思）的效率，后面的查询可以在逻辑上进行相应的处理。
    
    - 2. 卖家频繁查询我的订单, 高峰期并发30左右。允许秒级延迟。
    增加一张卖家订单表，针对用户新增订单的事件（消息队列/Canal/flink等）进行监听，然后进行该数据存储。
    虽然说卖家订单和买家订单是一一对应的存在，但是针对查询效率要求没有那么高,可以根据实际硬件情况适当对分表的纬度进行缩小。
    也可同【1】点进行时间纬度的冷准库迁移。
    
    - 3. 平台客服频繁搜索客诉订单(半年之内订单, 订单尾号，买家姓名搜索)，高峰期并发10左右。允许分钟级延迟。
    针对客诉数据，以订单日增长来100万来看，客诉订单理论上远远低于100万的。假设以20%来估计，月增长在600万。
    按时间客诉订单的创建时间的月份进行分表。半年前的数据以冷备的形式移入冷备库。
    - 4. redis采用lua脚本实现库存扣减 纳秒级 效率高

代码用例

数据表结构

-- 订单表
CREATE TABLE `order`
(
    `id`          varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci  NOT NULL COMMENT '订单号',
    `user_id`     varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci  NOT NULL COMMENT '购买人',
    `seller_id`   varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci  NOT NULL COMMENT '卖家id',
    `sku_id`      varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci  NOT NULL COMMENT 'SkuId',
    `amount`      int                                                           NOT NULL COMMENT '购买数量',
    `money`       decimal(10, 2)                                                NOT NULL COMMENT '购买金额',
    `pay_time`    timestamp NULL DEFAULT NULL COMMENT '支付时间',
    `pay_status`  varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '支付状态',
    `del_flag`    tinyint(1) NOT NULL DEFAULT '0' COMMENT '删除标志（0代表存在 1代表删除）',
    `create_by`   varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建人',
    `create_time` datetime                                                     DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_by`   varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '修改人',
    `update_time` datetime                                                     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
    PRIMARY KEY (`id`),
    KEY           `idx_user_id` (`user_id`),
    KEY           `idx_seller_id` (`seller_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单表';
-- 客诉订单表
CREATE TABLE `complainant_order`
(
    `id`                varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci  NOT NULL COMMENT 'id',
    `complainant_id`    varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci  NOT NULL COMMENT '客诉人id',
    `complainant_type`  varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci  NOT NULL COMMENT '客诉人类型 buyer:买家;saller:卖家',
    `complainant_name`  varchar(32) COLLATE utf8mb4_general_ci                        NOT NULL COMMENT '客诉人名称',
    `order_id`          varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci  NOT NULL COMMENT '订单id',
    `order_end_id`      varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci  NOT NULL COMMENT '订单尾号',
    `order_create_time` datetime                                                      NOT NULL COMMENT '订单创建时间',
    `status`            varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '处理状态',
    `del_flag`          tinyint(1) NOT NULL DEFAULT '0' COMMENT '删除标志（0代表存在 1代表删除）',
    `create_by`         varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建人',
    `create_time`       datetime                                                     DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_by`         varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '修改人',
    `update_time`       datetime                                                     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
    PRIMARY KEY (`id`),
    KEY                 `idx_create_time` (`create_time`),
    KEY                 `idx_order_end_id` (`order_end_id`),
    KEY                 `idx_complainant_name` (`complainant_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='客诉订单表';

public interface InventoryConstants {

    /**
     * 分割
     */
    String REDIS_SEPARATOR = ":";

    /**
     * 可售库存
     */
    String SELLABLE_QUANTITY_KEY = "sellable_quantity";

    /**
     * 预占库存
     */
    String WITHHOLDING_QUANTITY_KEY = "withholding_quantity";

    /**
     * 占用库存
     */
    String OCCUPIED_QUANTITY_KEY = "occupied_quantity";

    /**
     * redis key 前缀
     */
    String PREFIX_KEY = "inventory:";


    /**
     * 修改 脚本
     */
    String CHANGE_INVENTORY_SCRIPT = """
            local sellable_quantity_inventory_key = KEYS[1]
            local withholding_quantity_inventory_key = KEYS[2]
            local occupied_quantity_inventory_key = KEYS[3]
                        
            local sellable_quantity_inventory = tonumber(redis.call('GET', sellable_quantity_inventory_key)) or 0
            local withholding_quantity_inventory = tonumber(redis.call('GET', withholding_quantity_inventory_key)) or 0
            local occupied_quantity_inventory = tonumber(redis.call('GET', occupied_quantity_inventory_key)) or 0
                        
            local sellable_quantity_delta = tonumber(ARGV[1]) or 0
            local withholding_quantity_delta = tonumber(ARGV[2]) or 0
            local occupied_quantity_delta = tonumber(ARGV[3]) or 0
                        
            if sellable_quantity_delta == 0 and withholding_quantity_delta == 0 and occupied_quantity_delta == 0 then
                return 0  -- 不做任何修改
            end
                        
            local new_withholding_quantity_inventory = withholding_quantity_inventory + withholding_quantity_delta
            local new_occupied_quantity_inventory = occupied_quantity_inventory + occupied_quantity_delta
                        
            if new_withholding_quantity_inventory + new_occupied_quantity_inventory > sellable_quantity_inventory then
                return -1  -- 库存不足，无法操作
            end
                        
            redis.call('INCRBY', sellable_quantity_inventory_key, sellable_quantity_delta)
            redis.call('INCRBY', withholding_quantity_inventory_key, withholding_quantity_delta)
            redis.call('INCRBY', occupied_quantity_inventory_key, occupied_quantity_delta)
            -- 操作成功
            return 1
            """;

}

public class InventoryRepositoryImpl implements InventoryRepository {

    @Autowired
    private RedisTemplate<String, Object> redisTemplate;

    @Override
    public Inventory getInventory(String skuId) {
        Long sellableQuantity = (Long) redisTemplate.opsForValue().get(this.getRedisKey(skuId, InventoryConstants.SELLABLE_QUANTITY_KEY));
        Long withholdingQuantity = (Long) redisTemplate.opsForValue().get(this.getRedisKey(skuId, InventoryConstants.WITHHOLDING_QUANTITY_KEY));
        Long occupiedQuantity = (Long) redisTemplate.opsForValue().get(this.getRedisKey(skuId, InventoryConstants.OCCUPIED_QUANTITY_KEY));
        return new Inventory(skuId, sellableQuantity, withholdingQuantity, occupiedQuantity);
    }

    /**
     * 修改库存
     *
     * @param skuId
     * @param sellableQuantity    可售库存
     * @param withholdingQuantity 预占库存
     * @param occupiedQuantity    占用库存
     * @return
     */
    @Override
    public Boolean changeInventory(String skuId, Long sellableQuantity, Long withholdingQuantity, Long occupiedQuantity) {
        Long result = redisTemplate.execute(
                new DefaultRedisScript<>(InventoryConstants.CHANGE_INVENTORY_SCRIPT, Long.class),
                Arrays.asList(this.getRedisKey(skuId, InventoryConstants.SELLABLE_QUANTITY_KEY),
                        this.getRedisKey(skuId, InventoryConstants.WITHHOLDING_QUANTITY_KEY),
                        this.getRedisKey(skuId, InventoryConstants.OCCUPIED_QUANTITY_KEY)),
                sellableQuantity, withholdingQuantity, occupiedQuantity
        );
        return result != null && result == 0L;
    }

    /**
     * 获取redis key
     *
     * @param skuId    skuId
     * @param valueKey 具体的值key
     * @return
     */
    private String getRedisKey(String skuId, String valueKey) {
        return InventoryConstants.PREFIX_KEY + skuId + InventoryConstants.REDIS_SEPARATOR + valueKey;
    }
}