java执行查询postgresql得到中文乱码_实战 PostgreSQL 分区表

我们在之前的《实战PostgreSQL》 详细描述的 PostgreSQL 的版本差别、JSON/JSONB、全文检索等相关信息，PostgreSQL 远比上述三点内容，这篇文章将实战 PostgreSQL 分区表。

01，什么是分区表？

如下图，分区表就是根据分区策略，将数据数据分散到不同的子表中，并通过父表建立关联关系，从而实现数据物理上的分区。

---

02，PostgreSQL 提供的分区表功能

分区表在不同的文档描述中使用了多个名词：原生分区 = 内置分区表 = 分区表。

PostgreSQL 10.x 之前的版本提供了一种“手动”方式使用分区表的方式，需要使用继承 + 触发器的来实现分区表，步骤较为繁琐，需要定义附表、子表、子表的约束、创建子表索引，创建分区删除、修改，触发器等。

PostgreSQL 10.x 开始提供了内置分区表（内置是相对于 10.x 之前的手动方式）。内置分区简化了操作，将部分操作内置，最终简单三步就能够创建分区表。但是只支持范围分区（RANGE）和列表分区（LIST），11.x 版本添加了对 HASH 分区。

本文将使用 PostgreSQL 10.x 版本及后续版本中的的内置分区表的使用方式，通过三步来创建分区表

1，创建父表------------指定分区键、分区策略（RANGE | LIST | HASH（11.x 才提供HASH策略））
2，创建分区表----------指定父表，分区键范围（分区键范围重叠之后会直接报错）
3，在分区上创建索引-----通常分区键上的索引是必须的

下面直接通过一个简单的例子来看 PostgreSQL 如何操作分区表

---

03，PostgreSQL 实战代码

启动 PostgreSQL

docker pull postgre:12.2

docker run -e POSTGRES_PASSWORD=123456 -p 5432:5432 --name potgre12 postgres:12.2

psql -h 127.0.0.1 -p 5432 -U postgres --passsword

下面的例子我们将为把订单 orders 表使用分区表来实现。order 中含有 id、user_id、create_time 三个属性，并根据 create_time 按照月份进行分区。

（1）创建父表

CREATE TABLE orders (
    id serial,
    user_id int4,
    create_time timestamp(0) 
) PARTITION BY RANGE(create_time);

（2）创建分区表

CREATE TABLE orders_history PARTITION OF orders FOR VALUES FROM ('2000-01-01') TO ('2020-03-01');

CREATE TABLE orders_202003 PARTITION OF orders FOR VALUES FROM ('2020-03-01') TO ('2020-04-01');

CREATE TABLE orders_202004 PARTITION OF orders FOR VALUES FROM ('2020-04-01') TO ('2020-05-01');

CREATE TABLE orders_202005 PARTITION OF orders FOR VALUES FROM ('2020-05-01') TO ('2020-06-01');

CREATE TABLE orders_202006 PARTITION OF orders FOR VALUES FROM ('2020-06-01') TO ('2020-07-01');

（3）在分区上创建索引

CREATE INDEX order_idx_history_create_time ON orders_history USING btree(create_time);

CREATE INDEX order_idx_202003_create_time ON orders_202003 USING btree(create_time);

CREATE INDEX order_idx_202004_create_time ON orders_202004 USING btree(create_time);

CREATE INDEX order_idx_202005_create_time ON orders_202005 USING btree(create_time);

CREATE INDEX order_idx_202006_create_time ON orders_202006 USING btree(create_time);

通过以上步骤 order 的分区表就创建完成。我们通过 dt+ orders* 来查看创建好的主表和分区表。

（4）向分区表中插入数据

INSERT INTO orders (user_id, create_time) select 1000, generate_series('2020-01-01'::date, '2020-05-31'::date, '1 minute');

结果插入 217,441 条数据。在 List of relations 中我们能看到 Size 其中 order 表中并没有数据，超过 2020-05-31 也无数据。

从分区表中查询数据和普通查询语句没有什么区别，只是从父表中查询的效率较低，从分表中直接查询速度要更好。

上面的例子我们主要是使用 SQL 来创建并向分区表中插入数据。日常工作中 Spring Data JPA 是我们常用的开发框架，接下来看一下在 Spring Data JPA 中我们对分区表的操作。

04，当 JPA 遇上 PostgreSQL 分区表

Gitlab 上的源码 中是 Spring Data JPA 操作 PostgreSQL 内置分区表的例子。

例子中，分区表使用的是 03 中的 orders （根据 create_time 来创建的分区），通过 Spring Data JPA 向 Orders 中添加记录，并从 Orders 中查询所有记录和根据条件进行查询。通过代码可以看到 Spring Data JPA 操作分区表和操作普通表没有什么区别。

将 Java 中的对象映射到分区表上也开始可以的，查询出来的范围将是分区表中的数据。

但是分区表也是有局限的：性能上、分区策略上都是有限制的。

05，什么时候应该使用分区表

我们先看看分区表的优缺点：

优点：

（1）数据维护成本降低。比如：某一部分数据需要失效，不需要执行命令来更新数据，可以直接接触绑定关系，接触绑定的数据和分区表都依然保留，需要时可以随时恢复。通过 Flyway 等数据脚本管理能够方便的控制数据维护，避免人为直接操作数据。

（2）一个表只能放在一个物理空间上，使用分区表之后可以将不同的表放置在不同的物理空间上，从而达到冷数据放在廉价的物理机器上，热点数据放置在性能强劲的机器上。

（3）直接从分区表查询数据比从一个大而全的全量数据表中读取数据效率更高。

缺点：

（1）性能上的两面性。性能上通过分区表的父表查数据相对于普通的数据全量表查询效率要低。直接分区表中查询数据比在全量表中查询数据效率要高。

（2）10.x 版本不支持跨分区更新，11.x 版本中才支持快分区的更新。

（3）主键有可能重复。由于分区表的的主键约束都是分别建立的，因此可能存在主键重复。只能通过一些策略来规避主键相同的问题。

使用场景：

（1）借助分区表提升性能。如果历史数据查询机率较低，将历史数据放置在单独数据表中，增量数据放置在分区表中，程序直接查询分区表能够带来更好的查询效率。

（2）利用分区表做好数据管理。通过 Flyway 的版本，能够控制分区表中哪些表可以解除绑定，从而达到数据失效的目的，避免了直接操作线上数据库。