设计购物车表结构并优化

Sumery
250 阅读4分钟

设计购物车表

在构建购物车微服务时，先设计数据库,根据已有的文档:

syntax = "proto3";

package cart;

option go_package = '/cart';

service CartService {
  rpc AddItem(AddItemReq) returns (AddItemResp) {}
  rpc GetCart(GetCartReq) returns (GetCartResp) {}
  rpc EmptyCart(EmptyCartReq) returns (EmptyCartResp) {}
}

message CartItem {
  uint32 product_id = 1;
  int32  quantity = 2;
}

message AddItemReq {
  uint32 user_id = 1;
  CartItem item = 2;
}

message AddItemResp {}

message EmptyCartReq {
  uint32 user_id = 1;
}

message GetCartReq {
  uint32 user_id = 1;
}

message GetCartResp {
  Cart cart = 1;
}

message Cart {
  uint32 user_id = 1;
  repeated CartItem items = 2;
}

message EmptyCartResp {}

很容易得出初步的设计:

  1. 购物车和商品项的基本设计

两个核心表：carts（购物车表）和 cart_items（购物车商品表）。

购物车表 carts 购物车表包含每个用户的购物车信息。每个用户在系统中只能拥有一个购物车，因此 user_id 是唯一的。

CREATE TABLE carts
(
    id         SERIAL PRIMARY KEY,         -- 购物车唯一标识
    user_id    INT UNIQUE NOT NULL,        -- 用户ID，唯一标识每个用户的购物车
    created_at timestamptz DEFAULT (now()) NOT NULL, -- 创建时间
    updated_at timestamptz DEFAULT (now()) NOT NULL  -- 更新时间
);

id：购物车的唯一标识

user_id：每个用户唯一对应一个购物车，因此 user_id 必须唯一

created_at 和 updated_at：记录购物车的创建和更新时间

  1. 购物车商品表 cart_items

购物车商品表记录购物车中所有商品的信息，每个商品的数量和对应的购物车 ID。

sql
CREATE TABLE cart_items
(
    id         SERIAL PRIMARY KEY,        -- 商品项唯一标识
    cart_id    INT NOT NULL,              -- 购物车ID，指向 carts 表
    product_id INT NOT NULL,              -- 商品ID，指向 products 表
    quantity   INT NOT NULL,              -- 商品数量
    created_at timestamptz DEFAULT (now()) NOT NULL, -- 创建时间
    updated_at timestamptz DEFAULT (now()) NOT NULL  -- 更新时间
);

cart_id：关联购物车表，每个商品项都属于一个购物车

product_id：商品 ID，指向商品表

quantity：商品数量

created_at 和 updated_at：记录商品项的创建和更新时间

  1. 外键约束与索引设计

为了确保数据一致性和提高查询效率，我们对 cart_items 表添加外键约束，并且在表中创建适当的索引。

  1. 外键约束: cart_items.cart_id 与 carts.id 之间的外键关系：确保 cart_items 表中的商品项只能属于 carts 表中已存在的购物车。

cart_items.product_id 与 products.id 之间的外键关系：确保 cart_items 表中的商品项对应的商品在 products 表中是有效的。

  1. 级联删除：在删除购物车时，使用 ON DELETE CASCADE 确保相关的商品项也会被删除，避免留下无用数据。
ALTER TABLE cart_items
    ADD CONSTRAINT fk_cart
    FOREIGN KEY (cart_id)
    REFERENCES carts (id)
    ON DELETE CASCADE;

ALTER TABLE cart_items
    ADD CONSTRAINT fk_product
    FOREIGN KEY (product_id)
    REFERENCES products (id)
    ON DELETE CASCADE;

索引设计 为了提高查询效率，尤其是在通过 user_id 查找购物车和查询购物车商品时，为 carts 表的 user_id 和 cart_items 表的 cart_id 创建索引。

-- 索引：加速通过用户查询购物车
CREATE INDEX idx_user_id ON carts (user_id);

-- 索引：加速通过购物车查询商品项
CREATE INDEX idx_cart_id ON cart_items (cart_id);

这些索引能够加速查询操作，特别是在通过用户 ID 查询购物车，或者通过购物车 ID 查询商品项时，数据库会利用索引快速定位数据。

  1. 删除购物车商品时的优化 在删除购物车中的商品时，确保操作高效且不影响其他用户的数据。最关键的部分是通过正确的查询语句，确保只删除当前用户的商品，而不会误删其他用户的数据。

优化

在最初的方案首先想到的是使用 EXISTS 子查询: 确保删除操作只会影响特定用户的商品。具体 SQL 如下：

DELETE FROM cart_items
WHERE cart_id = $1
  AND product_id = $2
  AND EXISTS (
      SELECT 1
      FROM carts
      WHERE carts.id = $1
        AND carts.user_id = $3
  );

这个 SQL 语句首先检查 cart_items 中是否有符合条件的商品项，同时通过 EXISTS 子查询确保 cart_id 对应的购物车属于用户 $3。这个查询方式的优点是能够保证删除操作只影响当前用户的商品，但是使用了 EXISTS 子查询，可能导致性能上的瓶颈，尤其是当 carts 表数据量较大时，查询性能会受到影响。

在压测试该sql的查询性能很差,尤其是表记录越来越多时, 然后引入新的方案：

冗余 user_id 字段: 为了提高查询效率，特别是在删除商品时，另一种优化方案是将 user_id 字段冗余到 cart_items 表中。通过将 user_id 字段直接存储在 cart_items 表中，我们可以避免使用 EXISTS 子查询，从而提升性能。

-- 删除特定用户购物车中的商品
DELETE FROM cart_items
WHERE cart_id = $1
  AND product_id = $2
  AND user_id = $3;

在这个优化方案中，删除操作只需要在 cart_items 表中根据 cart_id、product_id 和 user_id 进行筛选，避免了子查询操作，直接执行删除。这个方案会在查询效率上有显著提升

avatar
文章
阅读
粉丝