表和索引分片

1.分片表

1.1 分片/分区

  分片 fragment是将一个表中的数据分布到不同的dbspace中。

  SinoDB数据库支持智能化横向的表和索引分区，并将其称之为表和索引分片 。

  分片允许您创建一个表，该表在SQL语句中被视为单个表，但由多个tbspaces组成。正常的分片要求每个 dbspace 中有一个片段。这有效地将较大的表划分为若干较小的 tablespaces，因为一个 tablespace 不能跨 dbspace。

  新增保留关键字partition分区 。分区 partition 则允许来自一个分片表的多个片段共存于同一个 dbspace。所以创建分区时需要为每个分区命名，因为dbspace名称在分区内不再唯一。

示例：

create table tab1(a int)
fragment by expression
 partition part1 (a >=0 and a < 5) in dbspace1,
 partition part2 (a >=5 and a < 10) in dbspace1,
…;

  sysfragments 表也已被扩展并添加一个partition 列。如果通过分区创建了一个分片表，那么 sysfragments 表的每一行在partition 列中都将包含一个分区名称。如果创建了一个不含分区的普通分片表，则 dbspace 名称会出现在为partition 列。
  在创建分区表时，也可以使用语法 PARTITION BY 代替 FRAGMENT BY。

  一张表的所有分片/分区所在的dbspaces必须有相同大小的页。

1.2 fragment与extent

  每个分片都被放置在指定的dbspace中。每个分片都有一个独立的tblspace ID，这个tblspace ID也称fragment ID。每个tblspace都包含相互独立的区段extent。

  在非分片表中，extent size是为整个表指定的。在分片表中，extent size是为每个分片指定的。如果没有为分片索引指定extent大小，那分片索引的extent大小是基于索引键数据大小（包括内部键开销）与行大小的比例值。

2. 分片的优势

2.1 分片的优势包括：

• 并行扫描和其他并行操作
    如果在决策支持环境(DSS)中，且使用SinoDB数据库的并行数据库查询（PDQ，parallel database queries）功能，数据库服务器可以并行地读取多个片段。这有利于需要读取大量数据的DSS 查询。

• 平衡的 I/O
    分片可以平衡各磁盘驱动器间的 I/O 操作，单个用户可以访问同一张表的不同片段并且不会产生相互竞争。通过平衡磁盘间的 I/O，可以减少磁盘资源的竞争并消除瓶颈。在OLTP环境中，平衡的 I/O 比并行性更加重要，因为大量并发查询的最大吞吐率至关重要。

• 更细的备份和恢复粒度
    分片为备份和恢复提供了更细的粒度。可以在 dbspace 级别执行备份和恢复操作。因为一个片段位于一个 dbspace 中，这意味着可以在片段级别上执行备份和恢复。

• 更高的可用性
    可以指定是否跳过表中不可访问的片段。这对于 DSS 环境很有利，因为其中存在大量的数据读取操作，并且处理工作不应该因为某个片段无法访问而被中断。

• 提升的安全性
    可以给每个片段授予不同的权限，因此提高了安全性。

• 其他并行操作
    有利于其他并行操作：连接、排序、聚合、分组和插入

2.2 并行扫描和分片

  分片的一个优势是它可以支持并行扫描。并行扫描是对同一张表的多个片段同时进行访问。在SinoDB动态服务器中，一个单独的查询可以有多个执行线程并且每个线程还可能访问不同的片段。一个查询在一台单处理器计算机上可以有多个线程，但是同一时间只能有一个线程被执行。最佳情况是在一台多处理器计算机上并行地执行查询，该计算机可以同时执行多个线程。

  通过以下两种方式启用PDQ来启动并行数据库查询:

① 设置 PDQPRIORITY 环境变量，可以为进程或用户启动并行操作：

export PDQPRIORITY=40

② 使用SQL启用并行操作,指定的并行程度在下一个 SET PDQPRIORITY 语句执行或进程结束前保持不变：

SET PDQPRIORITY 40

  对于DSS查询，建议使用 SET PDQPRIORITY 语句或 PDQPRIORITY 环境变量启用并行操作。
  在 OLTP 查询中，要将 PDQPRIORITY 设为 0，这将保证 OLTP 查询不会被 PDQ 资源分配所限制。数据库服务器仍然可以在这些查询中利用分段的消除特性，这是在 OLTP 环境中表和索引分区的主要好处。

3. 分片模式类型

SinoDB动态服务器提供两种分片模式：

轮循机制

• 轮循分片模式通过将行随机放置在片段中来实现平衡的数据分配；

• 对于插入语句，数据库服务器通过对随机数使用哈希函数确定行放置的位置；

• 对于插入游标，数据库服务器将第一行放于一个随机片段中，第二行放于下一个片段，以此类推，即真正的轮询方式。

基于表达式

  基于表达式的分片允许使用任何 WHERE 条件作为表或索引的分片表达式。该表达式可以是数据库服务器识别的任何有效表达式。动态服务器还允许指定一个remainder片段用来存储不符合任何其他片段条件的行。

3.1 轮循分片

• 示例

create table table1(
 col_1 serial,
 col_2 char(20))
fragment by round robin
 in dbspace1,dbspace2
extent size 10000
next size 3000;

  FRAGMENT BY ROUND ROBIN 选项必须指定至少 2 个 dbspaces 用于放置片段。
  EXTENT SIZE 和 NEXT SIZE 指明每个表片段的大小，而不是整个表的大小。请基于片段的平均大小计算 EXTENT SIZE 和 NEXT SIZE。

• 优缺点

  轮循策略的主要优势是无需了解数据的内容就可以得到一组均匀分布的片段。此外，当列值被更新时，该行不会被移至其他片段，因为片段的分配并不取决于列的值。轮循策略的一个劣势是查询优化器在评估查询时无法消除片段。

• 何时使用轮循模式

  请在查询执行顺序扫描并且对存储的数据知之甚少时使用轮循分配策略。例如，在数据访问方法或数据分布情况未知的时候考虑使用轮循模式。轮循机制在应用需要频繁更新或者快速加载数据时很重要也很有用。

3.2 智能大对象的轮循模式

• 示例

create table movie(
 movie_num integer,
 movie_title char(50),
 video blob,
 audio blob,
 description clob),
put video in (sbsp3, sbsp6, sbsp7),
audio in (sbsp1, sbsp2, sbsp4),
description in (sbsp5);

  如果不使用 PUT子句，否则数据库服务器将在缺省 sbspace(onconfig文件中的配置参数SBSPACENAME）中存储智能大对象。
  智能大对象的分片也与它们相关的数据行的存储完全独立，其它列可以通过表达式、轮循模式来分片，或者根本不分片。在以上例子中，它们就没有被分片。

3.3 表达式分片

• 示例

create table table1(
 customer_num serial,
 col_2 char(20), …)
fragment by expression
 customer_num in (101,7924,9324,3288) in dbs1,
 customer_num = 4983 or zipcode = 01803 in dbs2,
 customer_num < 10000 in dbs3,
 customer_num between 10000 and 20000 in dbs4,
 remainder in dbs5;

  一个基于表达式的分片是使用表达式或者规则 来定义哪些行该被插入到指定的片段。FRAGMENT BY EXPRESSION 选项确定行放置的位置。如果表达式被判定为真，行将被放置于对应的 dbspace 中。一个表中可以包含多达2048个片段及其相关条件。

  REMAINDER IN 子句指定一个 dbspace，这个 dbspace放置未被任何表达式判定为真的行。

  一行应该最多只被一个表达式判定为真。如果一行被多个表达式判定为真，该行将被置于第一个表达式指明的 dbspace 中。

  可以使用表中的任一列作为表达式的一部分。位于其他本地或远程表的列将不被允许。不允许使用子查询或存储过程作为表达式的一部分。

• 优势和劣势

  按照表达式分配数据有很多潜在的优势：
  — 在查询扫描中某些片段可以被消除。
  — 数据可以被隔离以支持某个特定的备份策略。
  — 可以在片段级别授予用户某些特权。
  — 可以创建不平衡的数据分布来抵消不平衡的访问频率。

  按照表达式分配数据的一个劣势是需要消耗CPU 资源用于规则判定。随着规则变得越来越复杂，会消耗越来越多的 CPU 时间。此外，基于表达式的分片比轮循方法需要更多的管理工作。寻找最佳的分片规则可能是一个迭代的过程。

• 何时使用基于表达式的分片

 基于表达式分片的目的是增加 I/O 吞吐率和查询优化中的片段消除。片段消除的最理想情况是多个表达式条件涉及一个单一的列且不会重叠。以下情况时，请考虑使用表达式分片：

  — 可以在单个列上创建不重叠的片段。

  — 对该表的访问具有高度选择性。

  — 对数据的访问不是均匀分布。

  — 在单一或多个列上可以创建重叠的片段。

• 表达式分片指南

  ① 避免使用 REMAINDER IN 子句，因为remainder片段总是被扫描。

  ② 分配数据以平衡磁盘间的 I/O 活动

  ③ 保持分片表达式简单，复杂表达式将占据更多的 CPU 时间来进行判定，而且还会阻止数据库服务器对片段的消除。

  ④ 组织条件使得最具限制性的部分排到第一位。因为在逻辑 AND 运算中，如果第一个子句为假，则针对 dbspace 的其他条件将不会被判定。

  例如，为插入值 25，以下将执行六次判定。

x >= 2 and x <= 10 in dbspace1,
x > 12 and x <= 19 in dbspace2,
x > 21 and x <= 29 in dbspace3,
remainder in dbspace4

  在重新排列的条件中，只需要 4 次判定。

x <= 10 and x >= 2 in dbspace1,
x <= 19 and x > 12 in dbspace2,
x <= 29 and x > 21 in dbspace3,
remainder in dbspace4

  ⑤ 避免任何需要执行数据类型转换操作的表达式

  ⑥ 如果数据的加载是主要的性能目标，或许应该通过将最常访问的片段置于分片语句首位，或使用轮循分片来优化您的数据加载。

  ⑦ 如果没有预见明显的优势，请勿对表进行分片。

• 分区SQL语句的变化

  新增保留关键字PARTITION 。PARTITION允许分区存储在用一个dbspaces中，所以创建分区时需要为每个分区命名，因为dbspace名称在分区内不再唯一。

示例：

create table tab1(a int)
fragment by expression
 partition part1 (a >=0 and a < 5) in dbspace1,
 partition part2 (a >=5 and a < 10) in dbspace1,
…;

  sysfragments 表也已被扩展并添加一个partition 列。如果通过分区创建了一个分片表，那么 sysfragments 表的每一行在partition 列中都将包含一个分区名称。如果创建了一个不含分区的普通分片表，则 dbspace 名称会出现在为partition 列。
  在创建分区表时，也可以使用语法 PARTITION BY 代替 FRAGMENT BY。

3.4 使用哈希函数

• 示例：

create table table1(
 customer_num serial lname char(20)
…)
fragment by expression
 mod(customer_num,3)=0 in dbspace1,
 mod(customer_num,3)=1 in dbspace2,
 mod(customer_num,3)=2 in dbspace3;

  可以使用一个哈希函数在不同片段中平均分配数据，尤其是列值可能无法将经常访问的数据在各片段间均匀划分的时候。

• 优势和劣势

  哈希函数可以获得数据的一个均匀分布。它还能在查询优化时允许片段消除，例：遇到一个等值查询（包括插入和删除）。片段消除在范围查询中不会发生。

• 何时使用哈希表达式

  如果数据访问来自某列，但是列内数值的分布未知或不可预测时，请使用哈希表达式。
  如果目标仅仅是平均分配数据，轮循机制可能会是更好的分段策略，因为哈希表达式在执行哈希计算时需要额外的开销。

4. 分片/分区索引

  您可以决定是否将索引分片。如果决定将索引进行分片，则必须使用基于表达式的分片模式，但不可以使用一个不包含 在索引键中的列的表达式对唯一索引进行分片。不能在索引中使用轮循模式分片。

• 非分片索引

  如果不对索引分片，可以把整个索引放到一个单独的 dbspace 中。在这种策略中，索引和数据页是分开的。

• 何时使用分片索引

  由于 OLTP 应用程序频繁地使用索引访问而不是顺序访问，在 OLTP 环境中对索引分片会很有益处。DSS应用程序通常按顺序访问数据。因此，在 DSS 环境中通常不推荐对索引分片。

  为了支持约束条件而被创建的系统索引会始终保持不分片的状态，并且被创建在数据库所在的 dbspace 中。

  如果您不指定一个 dbspace （未分段的）或一个 dbspace （分段的）列表，则索引默认使用与表相同的分段模式。如果您的表采用的是轮循模式分段，则这种情况是不可取的。建议您在这种情况下为索引指定一个 dbspace。

  同时，要注意 一个索引的所有分段/分区必须存在于拥有相同页大小的 dbspace中。

4.1 CREATE INDEX语句

• 表达式分片索引

create index idx1 on table1(col_1)
fragment by expression
 col_1 < 10000 in dbspace1,
 col_1 >= 10000 in dbspace2;

• 指定非分片模式

create index idx1 on table1(col_1) in dbspace1;

• 分区

create index idx1 on table1(col_1)
partition by expression
 partition part1 col_1 < 10000 in dbspace1,
 partition part2 col_1 >= 10000 in dbspace2;

5. ROWIDS

  分片的表包含不唯一的rowids。为通过 rowid 访问一个分片的表，必须显式的创建一个 rowid 列：

create table orders(
 order_num serial,
 customer_num integer,
 part_num char(20) )
with rowids
fragment by round robin in dbs1,dbs2;

alter table items add rowids;
alter table items drop rowids;

  在未分区的表中 rowid 是一个隐含的列，标识表中唯一的行。在分片的表中，rowid 不再是唯一的因为它们可能在不同的片段中重复。

  在分片的表中， rowid 需要 4 字节的 rowid 列。当向分片的表中增加 rowid 时，数据库服务器会创建一个索引，将内部唯一行的地址映射到新的 rowid 上。使用 rowid 来访问表总是通过这个索引进行的。因此，如果在应用中使用 rowid访问分片表，性能可能会受到影响，因为这需要一个索引来映射 rowid。所以我们建议使用主键而不是 rowid 来唯一地访问一行。

6. 选择分片策略

• 检查关键的 SELECT 语句并识别对应的表。

• 分析表将如何被访问（选择性，筛选器）
  — 识别表将按顺序访问还是按索引访问。
  — 确定用于 SELECT 语句中的筛选列和联接列。
  — 尝试在分片表达式中使用这些列中的一个或多个。如果没有适用于分布表达式的列，或者如果总是顺序读取表，请使用轮循分配模式。

• 确定环境是 DSS 还是 OLTP
  DSS 性能随着片段数量的增加而线性增加，直至 CPU 的数量。
  OLTP 环境中的主要目标是减少数据读取数量（而不是并行地读取数据）。可以根据经常使用的等值和范围条件来实现这一目标以消除那些必须扫描的片段。OLTP 环境经常受益于分片索引的使用。

• 确认以下问题：
  ①有多少 CPU 和磁盘可用？**

   ②数据加载是否是一个重要的因素？**

   如果数据加载是一个持久性的问题，那么轮循分配模式可以提供最佳的性能。

   ③片段权限是否是一个重要的因素？
   权限可以在片段层面上授予。如果这是需要的功能，则按表达式分配是必须使用的。
评价 I/O 并调整分配策略。

• 创建完片段后，评估 I/O 模式，然后尝试通过调整分片规则的方法达到平衡的I/O。

7. 临时表的分片

select * from table1 into temp temp_table with no log;

create temp table temp_table (
 column1 integer,
 column2 char(10)
)
with no log;

  在SinoDB动态服务器中是否对临时表进行分片取决于创建临时表的语句与配置参数DBSPACETEMP中列出的临时表空间。也可以通过设置DBSPACETEMP环境变量来覆盖配置参数的值。例如

export DBSPACETEMP=tempdbs1,tempdbs2,tempdbs3

  在第一个例子中，根据 DBSPACETEMP 配置参数或环境变量的设置，最后的临时表将以轮循分片的方式分配到数据库服务器可使用的临时 dbspace中。

  在第二个例子中，临时表未被分片，而是被置于一个可用的临时 dbspace 中。如果配置了多个临时 dbspace，则下一个被创建的临时表将被置于另一个临时 dbspace 中，以平衡所有临时 dbspace 间的 I/O。

创建分片的临时表

• 轮循分片

create temp table temp_table (
 column1 integer,
 column2 char(10))
with no log
fragment by round robin
tempdbs1, tempdbs2, tempdbs3;

• 基于表达式的分片

create temp table temp_table (
 column1 integer,
 column2 char(10))
with no log
fragment by expression
column1 < 1000 in tempdbs1,
column1 < 2000 in tempdbs2,
column1 >= 2000 in tempdbs3;

8. 系统目录表sysfragments

  sysfragments系统表中对分片表的每一个片段都保存一行数据。以下列需要关注：

• fragtype – 表或索引

• tabid – 表的唯一 ID

• partn – 片段的唯一分区号。这是片段的 tablespace 标识符。一个分片表包含多个 tablespace -每个片段一个

• strategy – 表达式或轮循

• partition – 分区名或 dbspace 名

 更多信息内容请移步星瑞格官方社区，期待大家加入

Sinoregal Tech ForumAsk questions, share solutions, and get to know the Sinoregal community.https://forum.sinoregal.cn/