标签 SQL 下的文章

场景和问题

最近把主力数据库都全部切换成了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"

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

阅读剩余部分