informix分片表的几种写法和使用习惯

本文详细介绍了数据库分片的几种常见方法，包括间隔分片、MOD分片、表达式分片、列表分片和轮转分片，并讨论了每种分片策略在不同查询场景下的优缺点。同时，解释了附加索引和独立索引的概念，强调了索引在分片表中的重要性以及如何创建和管理分片表的索引。

分片1：

间隔分片的写法

CREATE TABLE employee
(
   id INTEGER,
   name CHAR(32),
   basepay DECIMAL (10,2),
   varpay DECIMAL (10,2),
   dept CHAR(2),
   hiredate DATE)
FRAGMENT BY RANGE (id)
INTERVAL (100) STORE IN (dbs1, dbs2, dbs3, dbs4)
PARTITION p0 VALUES IS NULL IN dbs0,
PARTITION p1 VALUES < 200 IN dbs1,
PARTITION p2 VALUES < 400 IN dbs2;

create table sales
(amount int,
id int,
data_time datetime year to second)
fragment by range(data_time)
interval(10 units day)
store in (datadbs01,datadbs02,datadbs03,datadbs04,datadbs05,datadbs06)
partition p_sales0 values < '2011-01-01 00:00:00' in bigdatadbs;
create unique index idx_sales on sales(data_time,id);

分片2：

mod分片的写法

CREATE TABLE employee (
id_num integer,
name char(50),
salary integer)
FRAGMENT BY EXPRESSION
MOD(id_num, 3) = 0 IN datadbs01,
MOD(id_num, 3) = 1 IN datadbs02,
MOD(id_num, 3) = 2 IN datadbs03;
CREATE INDEX idx_empoyee ON employee(id_num);

我理解类似的用法对于where id_num=XXX应该是不错的，但是对于范围查询，应该就是比较狗血了。

分片3：

普通的表达式分片

CREATE TABLE tab1(
col1 integer,
col2 date)
FRAGMENT BY EXPRESSION
col1 >= 0 and col1 < 100 in datadbs01,
col1 >= 100 and col1 < 200 in datadbs02,
remainder in datadbs03;
CREATE INDEX idx_tab1 ON tab1(col1);

分片4：

create table customer
(
cust_id integer,
name varchar(128),
street varchar(255),
state char(2),
zipcode char(5),
phone char(15)
)
Fragment by list(state)
PARTITION p0 values('RS','IL') in datadbs01,
PARTITION p1 values('CA','OR') in datadbs02,
PARTITION p2 values('NY','MN') in datadbs03,
PARTITION p3 values(NULL) in datadbs04;

据说list表达式是为了应对or 和 in 操作。

我理解list也是对于等值查询靠谱，但是与范围查询，也不咋地。

分片5:

round robin分片规则

CREATE TABLE frag_exp_tab2(
sale_time datetime year to second,
product_id int,
product_time datetime year to second,
price float,
sale_amount int
) FRAGMENT BY round robin in workdbs01,workdbs02,workdbs03 ;

round robin的索引在不指定存储空间的情况下，也是小每个分区一个索引，但是缺点是对于查询最小的代价也必须是全索引扫描。

关于分片表的索引：

如果表和索引采用相同的分片策略，则索引是一个attached 索引。而索引如果索引创建的时候单独指定了存储空间，则索引是detached索引。

换言之detached索引是一个索引大树，而attached 索引是若干小索引构建成的B+树。

Informix 内部自动为主健、外健、唯一约束创建一个索引，在默认情况下该索引是一个detached 索引。

为了创建 attatched索引，我们可以进行如下操作：
Create unique index idx_primary_key on frag_exp_tab(sale_time,product_id); 此时为attached索引。
ALTER TABLE frag_exp_tab ADD CONSTRAINT PRIMARY KEY (sale_time,product_id); 此时仅仅是创建了一个约束。