分类 数据库 下的文章

场景和问题

最近把主力数据库都全部切换成了PostgreSQL，并用于它开发了物料管理系统demo。为了测试大量数据对性能的影响，我给本地的库存表灌入了几十万种物料和80万行库存项。在关键词模糊搜索、多表联查等环节，我对其性能表现都很满意；但是当我在盘库凭证中添加抽盘项，而分页查询库存的页码又超大时，在前端拿到分页结果竟然要1.2s左右。毫无疑问，这个时间是有优化空间的。

查询库存的SQL语句类似于：

SELECT t."Id", t."Amount", t."Batch", t."CreatedAt", t."ExpiredAt", t."ExternalCode", t."IsLocked", t."Locker", t."MaterialCode", t."MaterialId", t."Note", t."ProducedAt", t.xmin, t."Scope", t."Slot", t."StockKind", t."WarehouseId", m0."Id", m0."Code", m0."MeasureUnitId", m0."ShortName", m0."Specification", m0."Status", m1."Id", m1."Abbrev", m1."Description", m1."Name", m2."Id", m2."Code", m2."Name", m2."PlantId"
      FROM (
          SELECT m."Id", m."Amount", m."Batch", m."CreatedAt", m."ExpiredAt", m."ExternalCode", m."IsLocked", m."Locker", m."MaterialCode", m."MaterialId", m."Note", m."ProducedAt", m.xmin, m."Scope", m."Slot", m."StockKind", m."WarehouseId"
          FROM mm_inventory AS m
          WHERE m."StockKind" IN (1, 2, 3) 
          ORDER BY m."Id"
          LIMIT 10 OFFSET 790000
      ) AS t
      INNER JOIN mm_material AS m0 ON t."MaterialId" = m0."Id"
      INNER JOIN mm_material_measureunit AS m1 ON m0."MeasureUnitId" = m1."Id"
      INNER JOIN mm_warehouse AS m2 ON t."WarehouseId" = m2."Id"
      ORDER BY t."Id"

上面这条查询语句稍显啰嗦，不过通过分析，很容易发现瓶颈出现在最内层的子查询上：

阅读剩余部分

基础数据准备

考虑我们有一张物料表和库存表。

CREATE TABLE IF NOT EXISTS public.mm_material
(
    "Id" integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    "Code" character varying(64) COLLATE pg_catalog."default" NOT NULL,
    "ShortName" character varying(256) COLLATE pg_catalog."default" NOT NULL,
    "Specification" character varying(50) COLLATE pg_catalog."default" NOT NULL,
    "Status" integer NOT NULL,
    "MeasureUnitId" integer NOT NULL,
    CONSTRAINT "PK_mm_material" PRIMARY KEY ("Id"),
    CONSTRAINT "AK_mm_material_Code" UNIQUE ("Code"),
    CONSTRAINT "FK_mm_material_mm_material_measureunit_MeasureUnitId" FOREIGN KEY ("MeasureUnitId")
        REFERENCES public.mm_material_measureunit ("Id") MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE
)
-- 索引略
;



CREATE TABLE IF NOT EXISTS public.mm_inventory
(
    "Id" uuid NOT NULL,
    "IsLocked" boolean NOT NULL,
    "Locker" text COLLATE pg_catalog."default" NOT NULL,
    "WarehouseId" integer NOT NULL,
    "Slot" text COLLATE pg_catalog."default" NOT NULL,
    "Scope" text COLLATE pg_catalog."default" NOT NULL,
    "MaterialCode" text COLLATE pg_catalog."default" NOT NULL,
    "MaterialId" integer NOT NULL,
    "Batch" text COLLATE pg_catalog."default" NOT NULL,
    "ExternalCode" text COLLATE pg_catalog."default" NOT NULL,
    "Amount" numeric NOT NULL,
    "StockKind" integer NOT NULL,
    "Note" text COLLATE pg_catalog."default",
    "ProducedAt" timestamp with time zone,
    "ExpiredAt" timestamp with time zone NOT NULL,
    "CreatedAt" timestamp with time zone NOT NULL,
    CONSTRAINT "PK_mm_inventory" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_mm_inventory_mm_material_MaterialId" FOREIGN KEY ("MaterialId")
        REFERENCES public.mm_material ("Id") MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE,
    CONSTRAINT "FK_mm_inventory_mm_warehouse_WarehouseId" FOREIGN KEY ("WarehouseId")
        REFERENCES public.mm_warehouse ("Id") MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE
)
-- 索引略
;

为了测试，我们向这张物料单表中插入大概10万条测试数据：

阅读剩余部分