如何处理大量数据
如何处理大量数据
提高超大量数据数据库处理速度的方法-表分区
庞大的数据量不光是查询操作,删除起来也痛苦.
使用表分区的效果比较明显.特别是删除操作比较方便,速度也快.直接truncate掉按照rule分区以后的
分区表数据,索引什么都会快速删除掉.
至于查询速度的问题,索引比必不可少的(,如何建立高效的索引这篇文章就不再说明了.)
还有就是负载均衡. 数据库postgresql + postgresforest 可以达到很好的效果.(其实中心思想也是表分区.)
PostgreSQL 支持基本的表分区功能。 本节描述为何需要表分区以及你如何在你的数据库设计里面实现表分区。
概述
分区的意思是把逻辑上的一个大表分割成物理上的几块儿。 分区可以提供若干好处：
某些类型的查询性能可以得到极大提升。
更新的性能也可以得到提升，因为表的每块的索引要比在整个数据集上的索引要小。 如果索引不能全部放在内存里，那么在索引上的读和写都会产生更多的磁盘访问。
批量删除可以用简单地删除某个分区来实现 － 只要需求已经在分区设计是进行了规划。 DROP TABLE 比批量 DELETE 要快很多， 因为不需要有 VACUUM 的开销。
很少用的数据可以移动到便宜的、慢一些地存储介质上。
这种好处通常只有在表可能会变得非常大的情况下才有价值。 表在多大的情况下会从分区中收益取决于应用，不过有个基本的拇指规则就是表的大小超过了数据库服务器的物理内存大小。
目前，PostgreSQL 支持通过表继承进行分区。 每个分区必须做为单独一个父表的子表进行创建。父表自身通常是空的； 它的存在只是为了代表整个数据集。你在试图实现分区之前，应该先熟悉继承（参阅 Section 5.8）。
PostgreSQL 里面可以实现下面形式的分区：
范围分区
表被一个或者多个键字字段分区成"范围"， 在这些范围之间没有重叠的数值分布到不同的分区里。 比如，我们可以为特定的商业对象根据数据范围分区，或者根据标识符范围分区。
列表分区
表是通过明确地列出每个分区里应该出现那些键字值实现的。
目前还不支持散列分区。
实现分区
要设置一个分区的表，做下面的步骤：
创建"主表"，所有分区都从它上面继承下去。
这个表将没有什么数据，不要在这个表上定义任何检查约束， 除非你希望约束同样也适用于所有分区。同时在其上定义任何索引或者唯一约束也没有意义。
创建几个"子"表，每个都从主表上继承。 通常，这些表将不会对从主表继承过来集合增加任何字段。
我们将把子表称作分区，尽管它们就是普通的 PostgreSQL 表。
给分区表增加约束，定义每个分区允许的健值。
典型的例子是：
CHECK ( x = 1 )
CHECK ( county IN ( 'Oxfordshire', 'Buckinghamshire', 'Warwickshire' ))
CHECK ( outletID >= 100 AND outletID < 200 )
确信这些约束保证在不同的分区里不会有重叠的键字。一个常见的错误是设置下面这样的范围：
CHECK ( outletID BETWEEN 100 AND 200 )
CHECK ( outletID BETWEEN 200 AND 300 )
这样做是错误的，因为它没说清楚健值 200 属于那个范围。
请注意在范围和列表分区的语法方面没有什么区别；这些术语只是用于描述的。
对于每个分区，在键字字段上创建一个索引，以及其它你想创建的索引。 （键字索引并非严格要求的，但是在大多数情况下它是很有帮助的。 如果你希望键字值是唯一的，那么你应该总是给每个分区创建一个唯一或者主键约束。
另外，定义一个规则或者触发器，把对主表的修改重定向到合适的分区表。
确保 postgresql.conf 里的配置参数 constraint_exclusion 是打开的。 没有这个参数，查询不会按照需要进行优化。
比如，假设我们为一个巨大的冰激凌公司构造数据库。 该公司每天都测量最高温度，以及每个地区的冰激凌销售。 概念上，我们需要一个这样的表：
CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
);
我们知道大多数查询都只会访问最后一周，最后一个月或者最后一个季度的数据， 因为这个表的主要用途是为管理准备在线报告。 为了减少需要存储的旧数据，我们决定值保留最近三年的有用数据。 在每个月的开头，我们都会删除最旧的一个月的数据。
在这种情况下，我们可以使用分区来帮助我们实现所有我们对表的不同需求。 下面的步骤描述了上面的需求，分区可以这样设置：
主表是 measurement 表，就像上面那样声明。
然后我们为每个月创建一个分区：
CREATE TABLE measurement_yy04mm02 ( ) INHERITS (measurement);
CREATE TABLE measurement_yy04mm03 ( ) INHERITS (measurement);
...
CREATE TABLE measurement_yy05mm11 ( ) INHERITS (measurement);
CREATE TABLE measurement_yy05mm12 ( ) INHERITS (measurement);
CREATE TABLE measurement_yy06mm01 ( ) INHERITS (measurement);
每个分区都是拥有自己内容的完整的表，只是它们从 measurement 表继承定义。
这样就解决了我们的一个问题：删除旧数据。 每个月，我们需要做的只是在最旧的子表上执行一个 DROP TABLE， 然后为新月份创建一个新的子表。
我们必须增加非重叠的表约束，所以我们的建表脚本就变成：
CREATE TABLE measurement_yy04mm02 (
CHECK ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' )
) INHERITS (measurement);
CREATE TABLE measurement_yy04mm03 (
CHECK ( logdate >= DATE '2004-03-01' AND logdate < DATE '2004-04-01' )
) INHERITS (measurement);
...
CREATE TABLE measurement_yy05mm11 (
CHECK ( logdate >= DATE '2005-11-01' AND logdate < DATE '2005-12-01' )
) INHERITS (measurement);
CREATE TABLE measurement_yy05mm12 (
CHECK ( logdate >= DATE '2005-12-01' AND logdate < DATE '2006-01-01' )
) INHERITS (measurement);
CREATE TABLE measurement_yy06mm01 (
CHECK ( logdate >= DATE '2006-01-01' AND logdate < DATE '2006-02-01' )
) INHERITS (measurement);
我们可能还需要在键字字段上有索引：
CREATE INDEX measurement_yy04mm02_logdate ON measurement_yy04mm02 (logdate);
CREATE INDEX measurement_yy04mm03_logdate ON measurement_yy04mm03 (logdate);
...
CREATE INDEX measurement_yy05mm11_logdate ON measurement_yy05mm11 (logdate);
CREATE INDEX measurement_yy05mm12_logdate ON measurement_yy05mm12 (logdate);
CREATE INDEX measurement_yy06mm01_logdate ON measurement_yy06mm01 (logdate);
我们选择先不建立更多的索引。
如果数据只进入最新的分区，我们可以设置一个非常简单的规则来插入数据。 我们必须每个月都重新定义这个规则，这样它总是指向当前分区。
CREATE OR REPLACE RULE measurement_current_partition AS
ON INSERT TO measurement
DO INSTEAD
INSERT INTO measurement_yy06mm01 VALUES ( NEW.city_id,
NEW.logdate,
NEW.peaktemp,
NEW.unitsales );
我们可能想插入数据并且想让服务器自动定位应该向哪个分区插入数据。 我们可以用像下面这样的更复杂的规则集来实现这个目标。
CREATE RULE measurement_insert_yy04mm02 AS
ON INSERT TO measurement WHERE
( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' )
DO INSTEAD
INSERT INTO measurement_yy04mm02 VALUES ( NEW.city_id,
NEW.logdate,
NEW.peaktemp,
NEW.unitsales );
...
CREATE RULE measurement_insert_yy05mm12 AS
ON INSERT TO measurement WHERE
( logdate >= DATE '2005-12-01' AND logdate < DATE '2006-01-01' )
DO INSTEAD
INSERT INTO measurement_yy05mm12 VALUES ( NEW.city_id,
NEW.logdate,
NEW.peaktemp,
NEW.unitsales );
CREATE RULE measurement_insert_yy06mm01 AS
ON INSERT TO measurement WHERE
