最优数据库表优化方法

Question

我有一个数据库表变得太大（几亿行）需要优化，但在对其进行分区之前，我想我应该询问建议。

这是用法：

0。表包含约 10 列，每列长度约 20 字节。

1. INSERTS 以每秒数百次的速率执行。

2. 每小时根据列“a”（其中 a='xxxx'）执行几次 SELECT 语句。

3. DELETE 语句是基于 DATE 列执行的。 （删除超过 1 年的日期）通常每天一次。

关键要求是加快 INSERT 和 SELECT 语句的速度，并且能够保留 1 年前的历史数据，而无需在删除时锁定整个表。

我猜想我必须有两个索引，一个用于列“a”，另一个用于日期字段。或者是否可以同时优化两者？

选择速度和删除速度之间是否需要进行权衡？

分区是唯一的解决方案吗？对此类表进行分区的好策略是什么？

我使用的是 PostgreSQL 8.4 数据库。

Answer 1

您是否没有研究过 PostgreSQL 分区<，而不是将其保留为单个物理表/a>?从 8.1 版本开始受支持。

分区可以帮助您避免在快速插入和快速删除性能之间进行选择的问题。您始终可以按年/月对表进行分区，然后删除不再需要的分区。删除分区非常快，插入小分区也非常快。

从手册中：

<块引用>
<块引用>

分区是指将逻辑上的一个大表分割成
较小的物理碎片。分区
可以提供多种好处：

• 对于某些特定情况，查询性能可以显着提高
  各种查询。
• 更新性能也可以得到提高，因为每个部分
  表的索引小于
  整个数据集的索引将是。
  当索引不再容易适应时
  内存，读和写操作
  在指数上逐渐增加
  磁盘访问。
• 只需删除其中一项即可完成批量删除
  分区，如果该要求是
  规划到分区设计中。
  DROP TABLE 比批量操作快得多
  删除，更不用说接下来的事情了
  真空吸尘。
• 很少使用的数据可以迁移到更便宜且速度较慢的存储中
  媒体。

<块引用>
<块引用>

通常只有当桌子可以使用时，这些好处才值得
否则会非常大。确切的
桌子将受益的点
分区取决于
应用程序，尽管是经验法则
就是桌子的大小应该
超过物理内存
数据库服务器。

目前，PostgreSQL 支持通过表继承进行分区。
每个分区必须创建为
单个父表的子表。
父表本身通常是
空的;它的存在只是为了代表
整个数据集。你应该是
熟悉继承（参见部分
5.8）在尝试实现分区之前。

Answer 2

正如其他人所说，分区是您的答案，但是：

我会根据一些 hash(a) 进行分区。如果 a 是一个整数，那么 a%256 就可以了。如果它是文本，则类似于 substring(md5(a) for 2)。

它将加快插入和选择的速度。

对于删除，我会让它们运行得更频繁，但更小，并且也进行分区。我每小时运行一次（在 XX:30），如下所示：

delete from table_name
where date<(current_date - interval '1 year')
and
  hash(a)
  =
  (extract(doy from current_timestamp) * 24
    + extract(hour from current_timestamp))::int % 256;

---

编辑：我刚刚测试了这个：

create function hash(a text) returns text as $ select substring(md5($1) for 1) $ language sql immutable strict;
CREATE TABLE tablename (id text, mdate date);
CREATE TABLE tablename_partition_0 ( CHECK ( hash(id) = '0' ) ) INHERITS (tablename);
CREATE TABLE tablename_partition_1 ( CHECK ( hash(id) = '1' ) ) INHERITS (tablename);
CREATE TABLE tablename_partition_2 ( CHECK ( hash(id) = '2' ) ) INHERITS (tablename);
CREATE TABLE tablename_partition_3 ( CHECK ( hash(id) = '3' ) ) INHERITS (tablename);
CREATE TABLE tablename_partition_4 ( CHECK ( hash(id) = '4' ) ) INHERITS (tablename);
CREATE TABLE tablename_partition_5 ( CHECK ( hash(id) = '5' ) ) INHERITS (tablename);
CREATE TABLE tablename_partition_6 ( CHECK ( hash(id) = '6' ) ) INHERITS (tablename);
CREATE TABLE tablename_partition_7 ( CHECK ( hash(id) = '7' ) ) INHERITS (tablename); 
CREATE TABLE tablename_partition_8 ( CHECK ( hash(id) = '8' ) ) INHERITS (tablename);
CREATE TABLE tablename_partition_9 ( CHECK ( hash(id) = '9' ) ) INHERITS (tablename);
CREATE TABLE tablename_partition_a ( CHECK ( hash(id) = 'a' ) ) INHERITS (tablename);
CREATE TABLE tablename_partition_b ( CHECK ( hash(id) = 'b' ) ) INHERITS (tablename);
CREATE TABLE tablename_partition_c ( CHECK ( hash(id) = 'c' ) ) INHERITS (tablename);
CREATE TABLE tablename_partition_d ( CHECK ( hash(id) = 'd' ) ) INHERITS (tablename);
CREATE TABLE tablename_partition_e ( CHECK ( hash(id) = 'e' ) ) INHERITS (tablename);
CREATE TABLE tablename_partition_f ( CHECK ( hash(id) = 'f' ) ) INHERITS (tablename);
analyze;
explain select * from tablename where id='bar' and hash(id)=hash('bar');

 查询计划                                          
-------------------------------------------------- -------------------------------------------
 结果（成本=0.00..69.20行=2宽度=36）
   ->附加（成本=0.00..69.20行=2宽度=36）
         ->对表名进行顺序扫描（成本=0.00..34.60 行=1 宽度=36）
               过滤器： ((id = 'bar'::text) AND ("子字符串"(md5(id), 1, 1) = '3'::text))
         ->对 tablename_partition_3 表名进行顺序扫描（成本=0.00..34.60 行=1 宽度=36）
               过滤器： ((id = 'bar'::text) AND ("子字符串"(md5(id), 1, 1) = '3'::text))
（6 行）

您需要将 hash(id)=hash('searched_value') 添加到查询中，否则 Postgres 将搜索所有表。

---

编辑：您还可以使用规则系统自动插入以更正表：

create rule tablename_rule_0 as
  on insert to tablename where hash(NEW.id)='0'
  do instead insert into tablename_partition_0 values (NEW.*);
create rule tablename_rule_1 as
  on insert to tablename where hash(NEW.id)='1'
  do instead insert into tablename_partition_1 values (NEW.*);
-- and so on
insert into tablename (id) values ('a');
select * from tablename_partition_0;
 id | mdate 
----+-------
 a  | 
(1 row)

Answer 3

一种解决方案是根据插入日期进行分区。

也就是说，您的应用程序（或 DAO）根据某种逻辑来决定插入哪个表，该逻辑结合了当前日期（或者更确切地说，自上次分区切片启动以来的时间）和/或“最后”分区的大小。或者将此类逻辑卸载到日常脚本中，并让脚本填充一些“这是要使用的分区”以供 DAO 使用。

这立即消除了您删除“旧”行的需要（只需删除旧分区）；
它还确保您的插入定期开始填充小表，这除其他外，加快了“平均”INSERT/SELECT 速度（当然，最坏的情况仍然很慢）

Answer 4

如果您要将此表分解为适当的分区，您将能够使用截断而不是删除，这将降低您的维护成本，因为它不会产生死空间。

Answer 5

我不是专家，但似乎对“a”列进行分区会加快您的选择速度，但对日期进行分区（正如所有其他答案所建议的那样）会加快删除速度（删除表），但对您来说毫无用处选择。

看来，这两种情况都会提高插入性能。

有专家关心这个问题吗？
对两个字段进行分区是否可能/有用？