接口时间优化

背景，一个详情接口（需要主单和子单里面的sku信息和store门店信息），执行时间大概30秒左右，时间太长了

表结构

create table adjust_price
(
    id              bigint unsigned auto_increment
        primary key,
    name            varchar(20)  default '' not null comment '调价单名称',
    start_at        datetime                null comment '调价开始时间',
    end_at          datetime                null comment '调价结束时间',
    description     varchar(500) default '' not null comment '调价单描述',
    sell_channel_id int unsigned default 0  not null comment '销售渠道id',
    create_by       int unsigned default 0  not null comment '创建人',
    update_by       int unsigned default 0  not null comment '更新人',
    created_at      timestamp               null,
    updated_at      timestamp               null,
    deleted_at      timestamp               null
)
    comment '调价单主表' collate = utf8mb4_unicode_ci;

create table adjust_price_product_sku_store
(
    id              bigint unsigned auto_increment
        primary key,
    adjust_price_id int unsigned   default 0    not null comment '调价单id',
    sell_channel_id int unsigned   default 0    not null comment '销售渠道id',
    sku_id          int unsigned   default 0    not null comment '渠道skuId',
    promotion_price decimal(10, 2) default 0.00 not null comment '促销价',
    store_id        int unsigned   default 0    not null comment '门店id',
    start_at        datetime                    null comment '调价开始时间',
    end_at          datetime                    null comment '调价结束时间',
    created_at      timestamp                   null,
    updated_at      timestamp                   null,
    deleted_at      timestamp                   null
)
    comment '调价单子表' collate = utf8mb4_unicode_ci;

常规做法

skuId数量M = 100, storeId的数量N = 450，做笛卡尔积组合，那么子表adjust_price_product_sku_store每次插入的数据会有 M*N条，大概45000条。如果使用Laravel框架，detail 接口常规做法，通过做法通过with把子表带出来

AdjustPrice::whereId($id)->with(['adjustPriceStoreGroup', 'adjustPriceProductSkuStore'])->first();

但是，这样带出的主子表数据会特别多，数据大小大概11Mb，执行时间大概10s，外加一些逻辑处理接口的整体响应时间大概25s左右，用户体验特别不好。优化思路：将子表的数据数量级进行降级处理，把 M*N 的数量级，变成 M+N 的数量级

优化做法

• 查询某个调价单 adjust_price 第一条子表的数据 adjust_price_product_sku_store

$adjustPriceProductSkuStore = app(AdjustPriceProductSkuStoreRepository::class)->getByAdjustPriceIdLimitOne($adjustPrice->id);


select sku_id, store_id from adjust_price_product_sku_store limit 1;

• 通过第一条记录，找出来一个storeId，通过storeId获取所有的skuIds

  $adjustPriceProductSkuStoresToGetSkuIds = app(AdjustPriceProductSkuStoreRepository::class)->list([
      'adjustPriceId' => $adjustPrice->id,
      'storeId'       => $adjustPriceProductSkuStore->store_id,
  ]);
  
  $skuIds = collect(adjustPriceProductSkuStoresToGetSkuIds)->plick('sku_id')->toArray();

• 通过第一条记录，找出来一个skuId，通过skuId获取所有的storeIds

$adjustPriceProductSkuStoresToGetStoreIds = app(AdjustPriceProductSkuStoreRepository::class)->list([
    'adjustPriceId' => $adjustPrice->id,
    'skuId'         => $adjustPriceProductSkuStore->sku_id,
]);

$storeIds = collect(adjustPriceProductSkuStoresToGetStoreIds)->plick('store_id')->toArray();

最终效果：

时间从  20 秒 降到了 1秒
数据从11Mb 降到了 400Kb

---

20211209 数据设计修改

原来的数据库设计在skuId和storeId数据量不大的时候还是可行的，但是数据量大的时候，性能瓶颈很快就出现了，故重新修改数据库设计，将上面的 adjust_price_product_sku_store，分拆成2个表 adjust_price_product_sku 和 adjust_price_store，在数据库层直接把 M*N 的关系变成 M+N 的关系。接口响应时间都可以控制在1秒以内，5年内应该不会出现性能问题。

create table adjust_price_product_sku
(
    id              bigint unsigned auto_increment
        primary key,
    adjust_price_id int unsigned   default 0    not null comment '调价单id',
    sku_id          int unsigned   default 0    not null comment '渠道skuId',
    promotion_price decimal(10, 2) default 0.00 not null comment '促销价',
    start_at        datetime                    null comment '调价开始时间',
    end_at          datetime                    null comment '调价结束时间',
    created_at      timestamp                   null,
    updated_at      timestamp                   null,
    deleted_at      timestamp                   null
)
    collate = utf8mb4_unicode_ci;

create table adjust_price_store
(
    id              bigint unsigned auto_increment
        primary key,
    adjust_price_id int unsigned default 0 not null comment '调价单id',
    store_id        int unsigned default 0 not null comment '门店id',
    start_at        datetime               null comment '调价开始时间',
    end_at          datetime               null comment '调价结束时间',
    created_at      timestamp              null,
    updated_at      timestamp              null,
    deleted_at      timestamp              null
)
    comment '调价单sku子表' collate = utf8mb4_unicode_ci;