数据库分表架构图 数据库分表分区

数据库表分区

文章目录

• 数据库表分区

• 定义
• 分区的两种形式

• 水平分区（以 row 分）
• 垂直分区（以 column 分）

• 使用分区的场景

• ORACLE 分区表

• 表分区主要类型

• 范围分区 (Range)

• 创建Range分区

• 列表分区（list)

• 创建List分区

• Hash分区

• 创建hash分区

• 组合分区

• 创建 range-list

• 分区执行计划
• 注意

• 分区索引

• 索引类别
• GLOBAL索引
• LOCAL索引

定义

数据库分区是一种物理数据库设计技术，主要目的是为了在特定的SQL操作中减少数据读写的总量以缩减响应时间。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，查询数据时不用扫描整张表，提高数据检索的效率。

本人对于表分区使用较少，理解较浅，如有错误或不足的地方，欢迎指正，共同学习。

分区的两种形式

水平分区（以 row 分）

这种形式分区是对表的行进行分区，通过这样的方式不同分组里面的物理列分割的数据集得以组合，从而进行个体分割（单分区）或集体分割（1个或多个分区）。所有在表中定义的列在每个数据集中都能找到，所以表的特性依然得以保持。

例如 有个以时间为记录 ，大约有10年的数据集，可以已年划分为10个分区，每个分区包含的是其中一年的记录（注：一定要通过某个属性列来分割，譬如这里使用的列就是年份）

垂直分区（以 column 分）

这种分区方式一般来说是通过对表的垂直划分来减少目标表的宽度，使某些特定的列被划分到特定的分区，每个分区都包含了其中的列所对应的行。

如：一个包含了大text和BLOB列的表，这些text和BLOB列又不经常被访问，这时候就要把这些不经常使用的text和BLOB了划分到另一个分区，在保证它们数据相关性的同时还能提高访问速度。

在数据库供应商开始在他们的数据库引擎中建立分区（主要是水平分区）时，DBA和建模者必须设计好表的物理分区结构，不要保存冗余的数据（不同表中同时都包含父表中的数据）或相互联结成一个逻辑父对象（通常是视图）。这种做法会使水平分区的大部分功能失效，有时候也会对垂直分区产生影响。

使用分区的场景

• 表的大小超过2GB。
• 表中包含历史数据，新的数据被增加都新的分区中。
• 对数据的操作往往只涉及一部分数据，而不是所有的数据

ORACLE 分区表

ORACLE对于分区表方式其实就是将表分段存储，一般普通表格是一个段存 储，而分区表会分成多个段，所以查找数据过程都是先定位根据查询条件定位分区范围，即数据在那个分区或那几个内部，然后在分区内部去查找数据。

表分区主要类型

范围分区 (Range)

以列的值的范围来做为分区的划分条件，将记录存放到列值所在的range分区中,支持指定多列做为依赖列,以逗号间隔。

每一个分区都必须有一个VALUES LESS THEN子句，它指定了一个不包括在该分区中的上限值。分区键的任何值等于或者大于这个上限值的记录都会被加入到下一个高一些的分区中。

所有分区，除了第一个，都会有一个隐式的下限 值，这个值就是此分区的前一个分区的上限值。

在最高的分区中，MAXVALUE被定义。MAXVALUE代表了一个不确定的值。这个值高于其它分区中的任何分区键的值，也可以理解为高于任何分区中指定的VALUE LESS THEN的值，同时包括空值。

创建Range分区

create table 表名 (
   id number,
   name varchar2(50),
   use_date date
)
-- 按id
partition by range(id)(
   partition id_range1 values less than (10000) tablespace id_tps1,
   partition id_range2 values less than (20000) tablespace id_tps2,
   partition id_range_max values less than (maxvalue) tablespace id_tps_max
);
-- 多字段 按id ，name
partition by range(id,name)(
   partition id_range1 values less than (10000,f) tablespace id_tps1,
   partition id_range2 values less than (20000,n) tablespace id_tps2,
   partition id_range_max values less than (maxvalue,z) tablespace id_tps_max
);
-- 或 按日期
partition by range(use_date)(
   partition M201906 values less than (to_date('2019-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) tablespace use_date_tps,
   partition M201907 values less than (to_date('2019-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) tablespace use_date_tps,
   partition M201908 values less than (to_date('2019-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) tablespace use_date_tps
);

列表分区（list)

需指定 依赖列 且 依赖列分区值必须明确指定，分区时必须确定分区列可能存在的值，一旦插入的列值不在分区范围内，则插入/更新就会失败，因此通常建议使用list分区时，要创建一个default分区存储那些不在指定范围内的记录，类似range分区中的maxvalue分区。例如 某状态列值域固定为几个，基于这样的特点我们可以采用列表分区

创建List分区

create table 表名 (
   id number,
   name varchar2(50),
   use_date date,
   status_code number
)
partition by list(status_code)(
   partition activate_list values (1) tablespace status_tps1,
   partition no_activate_list values (0) tablespace status_tps2,
   partition default_list values (default) tablespace status_tps3
);

Hash分区

对于那些无法有效划分范围的表，可以使用hash分区，这样对于提高性能还是会有一定的帮助。hash分区会将表中的数据平均分配到你 指定的几个分区中，列所在分区是依据分区列的hash值自动分配，因此你并不能控制也不知道哪条记录会被放到哪个分区中，hash分区也可以支持多个依赖列。

创建hash分区

create table 表名 (
   id number,
   name varchar2(50),
   use_date date,
   status_code number
)
partition by hash(id)
(
   partition id_hash1 tablespace tps1,
   partition id_hash2 tablespace tps2,
   partition id_hash3 tablespace tps3
);

组合分区

通过分区内再建子分区的方式将分区再分区，即组合分区的方式。ORACLE 11g 数据库 组合方式： range-hash，range-list，range-range,list-range,list-list,list-hash。(网查未验证)

创建 range-list

create table 表名 (
   id number,
   name varchar2(50),
   use_date date,
   status_code number
)
partition by range(use_date)(
   partition M201906 values less than (to_date('2019-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) tablespace use_date_tps
   (
      subpartition M6activate_list values (1) tablespace use_date_tps,
      subpartition M6no_activate_list values (0) tablespace use_date_tps,
      subpartition M6default_list values (default) tablespace use_date_tps
   ),
   partition M201907 values less than (to_date('2019-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) tablespace use_date_tps1 (
      subpartition M7activate_list values (1) tablespace use_date_tps1,
      subpartition M7no_activate_list values (0) tablespace use_date_tps1,
      subpartition M7default_list values (default) tablespace use_date_tps1
   )
);

分区执行计划

分区表建立后 ，查看sql执行计划 看看是否走了分区

• PARTITION RANGE ALL：扫描所有分区
• PARTITION RANGE ITERATOR：扫描多个分区，小于所有个分区数量
• PARTITION RANGE SINGLE：扫描单一的分区

注意

写sql where 条件时,分区依赖列不要做任何函数或计算操作，否则走全分区查询，如：P_DATE（varchar2）为分区依赖列 ,where to_date(P_DATE,‘yyyy-mm-dd’) between xxx and xxx ,全分区查询。

分区索引

索引类别

GLOBAL索引和LOCAL索引，GLOBAL索引又可分为：GLOBAL不分区索引，和GLOBAL分区索引。

GLOBAL不分区索引一般不太推荐，因为是用一颗大的索引树来映射一个表，这个过程，这样速度不见得比不分区快。

GLOBAL分区索引，查找数据若通过要通过索引，是先定位了索引内部的分区，然后在这个分区索引中找到ROWID，然后回表提取数据。

LOCAL索引是和分区的个数逐个对应的，可以说先定位分区表的分区也可以说先定位索引的分区，因为他们是一一对应的，找到对应分区后，分区内部索引数据集合。

GLOBAL索引

GLOBAL索引可以不分区，那就是一个普通的索引

create index id_global on 表名(id) global; -- 普通索引

创建global索引的分区与创建表的分区语句格式完全相同，但全局索引的分区键和分区数和表的分区键和分区数可能都不相同，表和全局索引的分区机制不一样，一个分区索引（全局）可能指向任何（或全部的）表分区，全局索引必须是前缀索引，即全局索引的索引列必须是以索引分区键作为其前几列，索引分区键必须作为索引定义的第一列，否则执行会报错。。

create index  id_global on 表名 (id，score)  -- id 可以不是表的分区列
global partition by range(id)(
   partition id_range1 values less than (10000) tablespace id_tps1,
   partition id_range2 values less than (20000) tablespace id_tps2,
   partition id_range_max values less than (maxvalue) tablespace id_tps_max
);

-- ERRROR : 必须为前缀索引
create index  id_global on 表名 (score,id)
global partition by range(id)(  -- 应该使用 score
   partition id_range1 values less than (10000) tablespace id_tps1,
   partition id_range2 values less than (20000) tablespace id_tps2,
   partition id_range_max values less than (maxvalue) tablespace id_tps_max
);

LOCAL索引

LOCAL的索引只能是表的分区方式，不能自己写分区方式，与分区表 一 一对应，其中有两个概念：
局部前缀索引（local prefixed index）：以分区键作为索引定义的第一列
局部非前缀索引（local nonprefixed index）：分区键没有作为索引定义的第一列

create index id_local on 表名(id，score) local;   -- id是表的分区依赖列 为前缀索引

create index id_local on 表名(score，id) local;   -- id是表的分区依赖列, 则为非前缀索引

一般使用LOCAL索引较为方便，而且维护代价较低，并且LOCAL索引是在分区的基础上去创建索引，类似于在一个子表内部去创建索引，这样开销主要是区 分分区上。注意如果sql查询跨多个分区（iterator），就可能不走分区索引。只有在单个分区内才走local索引（实验所得，但是也有跨分区，走local索引的情况，比较疑惑）