[数据库/mysql/pgsql/sql server] 表分区功能综述

mysql

mysql 的群体最多,我们先来看它的.

http://www.ooso.net/index.php/archives/217


mysql 5.1新功能 －－ 按日期分区

2006-08-08 @ 07:42:42 · 作者 volcano · 归类于 MYSQL
你可能会感兴趣的内容
混合使用SQL和shell命令
在命令行里执行mysql的sql
PDO_MYSQL的一些预定义常量
Sun今日宣布收购Mysql
MySQL Proxy的Alpha版本发布

mysql 5.1已经到了beta版，官方网站上也陆续有一些文章介绍，比如上次看到的Improving Database Performance with Partitioning。在使用分区的前提下，可以用mysql实现非常大的数据量存储。今天在mysql的站上又看到一篇进阶的文章 —— 按日期分区存储。如果能够实现按日期分区，这对某些时效性很强的数据存储是相当实用的功能。下面是从这篇文章中摘录的一些内容。
错误的按日期分区例子

最直观的方法，就是直接用年月日这种日期格式来进行常规的分区：
PLAIN TEXT
CODE:
mysql>  create table rms (d date)
    ->  partition by range (d)
    -> (partition p0 values less than ('1995-01-01'),
    ->  partition p1 VALUES LESS THAN ('2010-01-01'));

上面的例子中，就是直接用"Y-m-d"的格式来对一个table进行分区，可惜想当然往往不能奏效，会得到一个错误信息:

ERROR 1064 (42000): VALUES value must be of same type as partition function near '),
partition p1 VALUES LESS THAN ('2010-01-01'))' at line 3

上述分区方式没有成功，而且明显的不经济，老练的DBA会用整型数值来进行分区：
PLAIN TEXT
CODE:
mysql> CREATE TABLE part_date1
    ->      (  c1 int default NULL,
    ->  c2 varchar(30) default NULL,
    ->  c3 date default NULL) engine=myisam
    ->      partition by range (cast(date_format(c3,'%Y%m%d') as signed))
    -> (PARTITION p0 VALUES LESS THAN (19950101),
    -> PARTITION p1 VALUES LESS THAN (19960101) ,
    -> PARTITION p2 VALUES LESS THAN (19970101) ,
    -> PARTITION p3 VALUES LESS THAN (19980101) ,
    -> PARTITION p4 VALUES LESS THAN (19990101) ,
    -> PARTITION p5 VALUES LESS THAN (20000101) ,
    -> PARTITION p6 VALUES LESS THAN (20010101) ,
    -> PARTITION p7 VALUES LESS THAN (20020101) ,
    -> PARTITION p8 VALUES LESS THAN (20030101) ,
    -> PARTITION p9 VALUES LESS THAN (20040101) ,
    -> PARTITION p10 VALUES LESS THAN (20100101),
    -> PARTITION p11 VALUES LESS THAN MAXVALUE );
Query OK, 0 rows affected (0.01 sec)

搞定？接着往下分析

PLAIN TEXT
CODE:
mysql> explain partitions
    -> select count(*) from part_date1 where
    ->      c3> date '1995-01-01' and c3 <date '1995-12-31'\G
*************************** 1. row ***************************
          id: 1
  select_type: SIMPLE
        table: part_date1
  partitions: p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11
        type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
        rows: 8100000
        Extra: Using where
1 row in set (0.00 sec)

万恶的mysql居然对上面的sql使用全表扫描，而不是按照我们的日期分区分块查询。原文中解释到MYSQL的优化器并不认这种日期形式的分区，花了大量的篇幅来引诱俺走上歧路，过分。
正确的日期分区例子

mysql优化器支持以下两种内置的日期函数进行分区：
TO_DAYS()
YEAR()

看个例子：
PLAIN TEXT
CODE:
mysql> CREATE TABLE part_date3
    ->      (  c1 int default NULL,
    ->  c2 varchar(30) default NULL,
    ->  c3 date default NULL) engine=myisam
    ->      partition by range (to_days(c3))
    -> (PARTITION p0 VALUES LESS THAN (to_days('1995-01-01')),
    -> PARTITION p1 VALUES LESS THAN (to_days('1996-01-01')) ,
    -> PARTITION p2 VALUES LESS THAN (to_days('1997-01-01')) ,
    -> PARTITION p3 VALUES LESS THAN (to_days('1998-01-01')) ,
    -> PARTITION p4 VALUES LESS THAN (to_days('1999-01-01')) ,
    -> PARTITION p5 VALUES LESS THAN (to_days('2000-01-01')) ,
    -> PARTITION p6 VALUES LESS THAN (to_days('2001-01-01')) ,
    -> PARTITION p7 VALUES LESS THAN (to_days('2002-01-01')) ,
    -> PARTITION p8 VALUES LESS THAN (to_days('2003-01-01')) ,
    -> PARTITION p9 VALUES LESS THAN (to_days('2004-01-01')) ,
    -> PARTITION p10 VALUES LESS THAN (to_days('2010-01-01')),
    -> PARTITION p11 VALUES LESS THAN MAXVALUE );
Query OK, 0 rows affected (0.00 sec)

以to_days()函数分区成功，我们分析一下看看：
PLAIN TEXT
CODE:
mysql> explain partitions
    -> select count(*) from part_date3 where
    ->      c3> date '1995-01-01' and c3 <date '1995-12-31'\G
*************************** 1. row ***************************
          id: 1
  select_type: SIMPLE
        table: part_date3
  partitions: p1
        type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
        rows: 808431
        Extra: Using where
1 row in set (0.00 sec)

可以看到，mysql优化器这次不负众望，仅仅在p1分区进行查询。在这种情况下查询，真的能够带来提升查询效率么？下面分别对这次建立的part_date3和之前分区失败的part_date1做一个查询对比：
PLAIN TEXT
CODE:
mysql> select count(*) from part_date3 where
    ->      c3> date '1995-01-01' and c3 <date '1995-12-31';
+----------+
| count(*) |
+----------+
|  805114 |
+----------+
1 row in set (4.11 sec)

mysql> select count(*) from part_date1 where
    ->      c3> date '1995-01-01' and c3 <date '1995-12-31';
+----------+
| count(*) |
+----------+
|  805114 |
+----------+
1 row in set (40.33 sec)

可以看到，分区正确的话query花费时间为4秒，而分区错误则花费时间40秒（相当于没有分区），效率有90％的提升！所以我们千万要正确的使用分区功能，分区后务必用explain验证，这样才能获得真正的性能提升。

热切期待msyql 5.1稳定版发布!

版权声明：可以任意转载，转载时请务必以超链接形式标明文章原始出处和作者信息及本声明


作者: volcano 发表于8月 8, 2006 at 7:42 am

版权信息: 可以任意转载, 转载时请务必以超链接形式标明文章原始出处和作者信息及此声明

永久链接 - http://www.ooso.net/index.php/archives/217


发表:clq 时间:2008-7-8 12:28



再看 pgsql 的

http://bluefire.wordpress.com.cn/2008/01/04/zzpgsql表分区官方文档中译版/
--------------------------------------------------

pgsql表分区官方文档中译版
4 01月 2008

转载自老何的地盘。英文版在这里，可以对照着看。

PostgreSQL 支持基本的表分区功能。 本节描述为何需要表分区以及你如何在你的数据库设计里面实现表分区。

概述

分区的意思是把逻辑上的一个大表分割成物理上的几块儿。 分区可以提供若干好处：
某些类型的查询性能可以得到极大提升。
更新的性能也可以得到提升，因为表的每块的索引要比在整个数据集上的索引要小。 如果索引不能全部放在内存里，那么在索引上的读和写都会产生更多的磁盘访问。
批量删除可以用简单地删除某个分区来实现 － 只要需求已经在分区设计是进行了规划。 DROP TABLE 比批量 DELETE 要快很多， 因为不需要有 VACUUM 的开销。
很少用的数据可以移动到便宜的、慢一些地存储介质上。

这种好处通常只有在表可能会变得非常大的情况下才有价值。 表在多大的情况下会从分区中收益取决于应用，不过有个基本的拇指规则就是表的大小超过了数据库服务器的物理内存大小。

目前，PostgreSQL 支持通过表继承进行分区。 每个分区必须做为单独一个父表的子表进行创建。父表自身通常是空的； 它的存在只是为了代表整个数据集。你在试图实现分区之前，应该先熟悉继承（参阅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 ( logdate >= DATE ‘2006-01-01′ AND

logdate < DATE ‘2006-02-01′ ) DO INSTEAD INSERT INTO

measurement_yy06mm01 VALUES ( NEW.city_id, NEW.logdate,

NEW.peaktemp, NEW.unitsales );
请注意每个规则里面的 WHERE 子句正好匹配其分区的 CHECK 约束。

我们可以看出来，一个复杂的分区方案可能要求相当不少的 DDL。 在上面的例子里我们需要每个月创建一次新分区，因此写一个脚本自动生成需要的 DDL 是明智的。

还要注意下面的事项：
目前还没有什么办法校验所有 CHECK 是相互排他的。 数据库设计者必须注意这一点。
目前还没有简单的办法声明数据行绝对不能插入主表。 主表上的一个 CHECK (false) 约束将被所有子表继承，因此不能这么用。一个可行的办法是在主表上设置一个 ON INSERT 触发器，总是抛出错误。（另外，这样的触发器也可以用于重定向数据到合适的子表， 而不是用上面建议的那样一套规则。）

分区也可以使用一个 UNION ALL 视图来安排：

CREATE VIEW measurement AS SELECT * FROM measurement_yy04mm02
  UNION ALL SELECT * FROM measurement_yy04mm03

…

UNION ALL SELECT * FROM measurement_yy05mm11

UNION ALL SELECT * FROM measurement_yy05mm12

UNION ALL SELECT * FROM measurement_yy06mm01;

不过，约束排除目前还不支持用这种方式定义的分区。 还有，重建试图也给增加和删除数据集里面的独立分区增加了额外的步骤。

分区和约束排除

约束排除（Constraint exclusion）是一种查询优化技巧， 它改进了用上面方法定义的表分区的性能。比如：

SET constraint_exclusion = on; SELECT count(*) FROM
  measurement WHERE logdate >= DATE ‘2006-01-01′;

如果没有约束排除，上面的查询会扫描 measurement 表中的每一个分区。打开了约束排除之后，规划器将检查每个分区的约束然后试图证明该分区不需要被扫描，因为它不能包含任何符合 WHERE 子句条件的数据行。如果规划器可以证明这个，它就把该分区从查询规划里排除出去。

你可以使用 EXPLAIN 命令显示一个规划在 constraint_exclusion 打开和关闭情况下的不同。用上面方法设置的表的典型的缺省规划是：

SET constraint_exclusion = off; EXPLAIN SELECT count(*)
  FROM measurement WHERE logdate >= DATE ‘2006-01-01′;

QUERY PLAN
  Aggregate (cost=158.66..158.68 rows=1 width=0) ->

Append (cost=0.00..151.88 rows=2715 width=0) ->

Seq Scan on measurement (cost=0.00..30.38 rows=543 width=0)

Filter: (logdate >= ‘2006-01-01′::date) ->

Seq Scan on measurement_yy04mm02 measurement

(cost=0.00..30.38 rows=543 width=0)

Filter: (logdate >= ‘2006-01-01′::date) ->

Seq Scan on measurement_yy04mm03 measurement

(cost=0.00..30.38 rows=543 width=0)

Filter: (logdate >= ‘2006-01-01′::date) … ->

Seq Scan on measurement_yy05mm12 measurement

(cost=0.00..30.38 rows=543 width=0)

Filter: (logdate >= ‘2006-01-01′::date) ->

Seq Scan on measurement_yy06mm01 measurement

(cost=0.00..30.38 rows=543 width=0)

Filter: (logdate >= ‘2006-01-01′::date)

部分或者全部分区可能会使用索引扫描而不是全表扫描， 不过这里要表达的意思是我们没有必要扫描旧的分区旧可以回答这个查询。 在我们打开约束排除之后，我们可以得到生成同样回答的明显节省的规划：

SET constraint_exclusion = on;
  EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE ‘2006-01-01′;

QUERY PLAN
  Aggregate (cost=63.47..63.48 rows=1 width=0) ->

Append (cost=0.00..60.75 rows=1086 width=0) ->

Seq Scan on measurement (cost=0.00..30.38 rows=543 width=0)

Filter: (logdate >= ‘2006-01-01′::date) ->

Seq Scan on measurement_yy06mm01 measurement

(cost=0.00..30.38 rows=543 width=0)

Filter: (logdate >= ‘2006-01-01′::date)

请注意，约束排除只由 CHECK 约束驱动，而不会由索引驱动。因此，在键字字段上定义索引是没有必要的。在给出的分区上是否需要建立索引取决于那些扫描该分区的查询通常是扫描该分区的一大部分还是只是一小部分。对于后者，索引通常都有帮助，对于前者则没有什么好处。

还有下面的注意：
约束排除只是在查询的 WHERE 子句包含约束的时候才生效。一个参数化的查询不会被优化，因为在运行时规划器不知道改参数会选择哪个分区。由于某些原因，像 CURRENT_DATE 这样的"稳定的（stable）"函数必须避免。把分区键字和另外一个表的字段连接起来也不会得到优化。
在 CHECK 约束里面避免跨数据类型的比较， 因为目前规划器会无法证明这样的条件为假。比如，下面的约束会在 x 是整数字段的时候可用，但是在 x 是一个 bigint 的时候不能用：
CHECK ( x = 1 )
对于 bigint 字段，我们必须使用类似下面这样的约束：
CHECK ( x = 1::bigint )
这个问题并不仅仅局限于 bigint 数据类型 — 它可能会发生在任何约束的缺省数据类型与其比较的字段的数据类型不匹配的场合。 在提交的查询里的跨数据类型的比较通常是 OK 的，只是不能在 CHECK 条件里。
目前，在主表上的 UPDATE 和 DELETE 命令并不执行约束排除。
主表的所有分区上面的所有约束都认为是约束排除了的，因此，大量的分区会显著增加查询规划的时间。
别忘记你仍然需要为每个分区独立运行 ANALYZE。 类似下面的命令

ANALYZE measurement;
是只会处理主表的。


发表:clq 时间:2008-7-8 12:30



SQL Server 2005 也支持了
--------------------------------------------------

SQL Server表分区操作详解
作者: cyw, 　出处:IT专家网,　责任编辑: 李书琴,　2007-10-18 17:56
　　SQL Server 2005引入的表分区技术，让用户能够把数据分散存放到不同的物理磁盘中，提高这些磁盘的并行处理性能以优化查询性能……


　　【IT专家网独家】你是否在千方百计优化SQL Server 数据库的性能?如果你的数据库中含有大量的表格，把这些表格分区放入独立的文件组可能会让你受益匪浅。SQL Server 2005引入的表分区技术，让用户能够把数据分散存放到不同的物理磁盘中，提高这些磁盘的并行处理性能以优化查询性能。

　　SQL Server数据库表分区操作过程由三个步骤组成：

　　1. 创建分区函数

　　2. 创建分区架构

　　3. 对表进行分区

　　下面将对每个步骤进行详细介绍。

　　步骤一：创建一个分区函数

　　此分区函数用于定义你希望SQL Server如何对数据进行分区的参数值(how)。这个操作并不涉及任何表格，只是单纯的定义了一项技术来分割数据。

　　我们可以通过指定每个分区的边界条件来定义分区。例如，假定我们有一份Customers表，其中包含了关于所有客户的信息，以一一对应的客户编号(从1到1,000,000)来区分。我们将通过以下的分区函数把这个表分为四个大小相同的分区：　　

CREATE PARTITION FUNCTION customer_partfunc (int)
　　AS RANGE RIGHT
　　FOR VALUES (250000, 500000, 750000)   

　　这些边界值定义了四个分区。第一个分区包括所有值小于250,000的数据，第二个分区包括值在250,000到49,999之间的数据。第三个分区包括值在500,000到7499,999之间的数据。所有值大于或等于750,000的数据被归入第四个分区。

　　请注意，这里调用的"RANGE RIGHT"语句表明每个分区边界值是右界。类似的，如果使用"RANGE LEFT"语句，则上述第一个分区应该包括所有值小于或等于250,000的数据，第二个分区的数据值在250,001到500,000之间，以此类推。

　　步骤二：创建一个分区架构

　　一旦给出描述如何分割数据的分区函数，接着就要创建一个分区架构，用来定义分区位置(where)。创建过程非常直截了当，只要将分区连接到指定的文件组就行了。例如，如果有四个文件组，组名从"fg1"到"fg4"，那么以下的分区架构就能达到想要的效果：　　

CREATE PARTITION SCHEME customer_partscheme
　　AS PARTITION customer_partfunc
　　TO (fg1, fg2, fg3, fg4)   

　　注意，这里将一个分区函数连接到了该分区架构，但并没有将分区架构连接到任何数据表。这就是可复用性起作用的地方了。无论有多少数据库表，我们都可以使用该分区架构(或仅仅是分区函数)。

　　步骤三：对一个表进行分区

　　定义好一个分区架构后，就可以着手创建一个分区表了。这是整个分区操作过程中最简单的一个步骤。只需要在表创建指令中添加一个"ON"语句，用来指定分区架构以及应用该架构的表列。因为分区架构已经识别了分区函数，所以不需要再指定分区函数了。

　　例如，使用以上的分区架构创建一个客户表，可以调用以下的Transact-SQL指令：　　

CREATE TABLE customers (FirstName nvarchar(40), LastName nvarchar(40), CustomerNumber int)
　　ON customer_partscheme (CustomerNumber)   

　　关于SQL Server的表分区功能，你知道上述的相关知识就足够了。记住!编写能够用于多个表的一般的分区函数和分区架构就能够大大提高可复用性。