PostgreSql 项目实战 5 -mysql迁移PG库 分区分表

1 分区策略

1 LIST 分区
含义：根据字段的**离散值**进行分区，每个分区包含一组明确的取值
PARTITION BY LIST (tenant_id)

2 RANGE分区（范围分区）
含义：根据字段的**连续范围**进行分区
PARTITION BY RANGE (order_confirm_date)

3 HASH 分区（哈希分区）
含义：根据字段的**哈希值**均匀分布数据。
PARTITION BY HASH (customer_id)

2 分区表步骤：

2.1 备份表数据

-- 1 备份现有数据
CREATE TABLE erp_product_tem AS TABLE erp_product;

-- 2 验证备份数据
SELECT count(*) FROM erp_product;
SELECT count(*) FROM erp_product_tem;

2.2 重构主表

// 1 删除原表
DROP TABLE IF EXISTS public.erp_product CASCADE;

// 2 构建新表(普通表禁止分区表，所以需要构建新的分区表)
CREATE TABLE "public"."erp_product" (
    ......
    -- 一定要有分区键
    CONSTRAINT pk_erp_product PRIMARY KEY (product_id, tenant_id)
    ......
）PARTITION BY LIST(tenant_id);

2.3 创建索引

CREATE INDEX "idx_erp_product_tenant " ON "public"."erp_product"(tenant_id) ;
-- 复合索引效率更高
CREATE INDEX "idx_erp_product_tenant_category" ON "public"."erp_product" (tenant_id, category_id);
CREATE INDEX "idx_erp_product_tenant_status" ON "public"."erp_product" (tenant_id, product_status);
......

2.4 维护备注

COMMENT ON COLUMN "public"."erp_product"."product_id" IS '商品ID';
COMMENT ON COLUMN "public"."erp_product"."product_code" IS '商品条码';
......

2.5 创建分区

CREATE TABLE erp_product_t1 PARTITION OF erp_product
  FOR VALUES IN (1)

2.6 快速查看所有分区

SELECT 
    inhrelid::regclass AS partition_name,
    -- 表类型: r 普通表 m 物化表  p 分区表
    relkind AS type,
    reltuples AS estimated_rows
FROM pg_inherits 
JOIN pg_class ON pg_inherits.inhrelid = pg_class.oid
WHERE inhparent = 'erp_product'::regclass
ORDER BY partition_name;

2.7 备份数据导回

INSERT INTO erp_product SELECT * FROM erp_product_tem;

2.8 验证数据

SELECT count(*) FROM erp_product;
SELECT count(*) FROM erp_product_tem;