mysql表分区语句举例

欢迎进入Linux社区论坛，与200万技术人员互动交流 >>进入 要按照属于同一个地区商店的行保存在同一个分区中的方式来分割表，可以使用下面的“CREATE TABLE”语句： [sql] CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30),

欢迎进入Linux社区论坛，与200万技术人员互动交流 >>进入

　　要按照属于同一个地区商店的行保存在同一个分区中的方式来分割表，可以使用下面的“CREATE TABLE”语句：

　　[sql]

　　CREATE TABLE employees (

　　id INT NOT NULL,

　　fname VARCHAR(30),

　　lname VARCHAR(30),

　　hired DATE NOT NULL DEFAULT '1970-01-01',

　　separated DATE NOT NULL DEFAULT '9999-12-31',

　　job_code INT,

　　store_id INT

　　)

　　PARTITION BY LIST(store_id)

　　PARTITION pNorth VALUES IN (3,5,6,9,17),

　　PARTITION pEast VALUES IN (1,2,10,11,19,20),

　　PARTITION pWest VALUES IN (4,12,13,14,18),

　　PARTITION pCentral VALUES IN (7,8,15,16)

　　)；

　　这使得在表中增加或删除指定地区的雇员记录变得容易起来。例如，假定西区的所有音像店都卖给了其他公司。那么与在西区音像店工作雇员相关的所有记录（行）可以使用查询“ALTER TABLE employees DROP PARTITION pWest；”来进行删除，它与具有同样作用的DELETE （删除）查询“DELETE query DELETE FROM employees WHERE store_id IN (4,12,13,14,18)；”比起来，要有效得多。

　　【要点】：如果试图插入列值（或分区表达式的返回值）不在分区值列表中的一行时，那么“INSERT”查询将失败并报错。例如，假定LIST分区的采用上面的方案，下面的查询将失败：

　　[sql]

　　INSERT INTO employees VALUES(224, 'Linus', 'Torvalds', '2002-05-01', '2004-10-12', 42, 21);

　　这是因为“store_id”列值21不能在用于定义分区pNorth, pEast, pWest,或pCentral的值列表中找到。要重点注意的是，LIST分区没有类似如“VALUES LESS THAN MAXVALUE”这样的包含其他值在内的定义。将要匹配的任何值都必须在值列表中找到。

　　LIST分区除了能和RANGE分区结合起来生成一个复合的子分区，与HASH和KEY分区结合起来生成复合的子分区也是可能的。

　　HASH分区

　　基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。

　　要使用HASH分区来分割一个表，要在CREATE TABLE 语句上添加一个“PARTITION BY HASH (expr)”子句，其中“expr”是一个返回一个整数的表达式。它可以仅仅是字段类型为MySQL 整型的一列的名字。此外，你很可能需要在后面再添加一个“PARTITIONS num”子句，其中num 是一个非负的整数，它表示表将要被分割成分区的数量。

　　[sql]

　　CREATE TABLE employees (

　　id INT NOT NULL,

　　fname VARCHAR(30),

　　lname VARCHAR(30),

　　hired DATE NOT NULL DEFAULT '1970-01-01',

　　separated DATE NOT NULL DEFAULT '9999-12-31',

　　job_code INT,

　　store_id INT

　　)

　　PARTITION BY HASH(store_id)

　　PARTITIONS 4；

　　如果没有包括一个PARTITIONS子句，那么分区的数量将默认为1。 例外： 对于NDB Cluster（簇）表，默认的分区数量将与簇数据节点的数量相同，

　　这种修正可能是考虑任何MAX_ROWS 设置，以便确保所有的行都能合适地插入到分区中。

　　LINER HASH

　　MySQL还支持线性哈希功能，它与常规哈希的区别在于，线性哈希功能使用的一个线性的2的幂（powers-of-two）运算法则，而常规 哈希使用的是求哈希函数值的模数。

　　线性哈希分区和常规哈希分区在语法上的唯一区别在于，在“PARTITION BY” 子句中添加“LINEAR”关键字。

　　[delphi]

　　CREATE TABLE employees (

　　id INT NOT NULL,

　　fname VARCHAR(30),

　　lname VARCHAR(30),

　　hired DATE NOT NULL DEFAULT '1970-01-01',

　　separated DATE NOT NULL DEFAULT '9999-12-31',

　　job_code INT,

　　store_id INT

　　)

　　PARTITION BY LINEAR HASH(YEAR(hired))

　　PARTITIONS 4；

　　假设一个表达式expr, 当使用线性哈希功能时，记录将要保存到的分区是num 个分区中的分区N，其中N是根据下面的算法得到：

　　1. 找到下一个大于num.的、2的幂，我们把这个值称为V ，它可以通过下面的公式得到：

　　2. V = POWER(2, CEILING(LOG(2, num)))

　　（例如，假定num是13。那么LOG(2,13)就是3.7004397181411。 CEILING(3.7004397181411)就是4，则V = POWER(2,4), 即等于16）。

　　3. 设置 N = F(column_list) & (V - 1).

　　4. 当 N >= num:

　　? 设置 V = CEIL(V / 2)

　　? 设置 N = N & (V - 1)

　　例如，假设表t1，使用线性哈希分区且有4个分区，是通过下面的语句创建的：

　　CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)

　　PARTITION BY LINEAR HASH( YEAR(col3) )

　　PARTITIONS 6;

　　现在假设要插入两行记录到表t1中，其中一条记录col3列值为'2003-04-14'，另一条记录col3列值为'1998-10-19'。第一条记录将要保存到的分区确定如下：

　　V = POWER(2, CEILING(LOG(2,7))) = 8

　　N = YEAR('2003-04-14') & (8 - 1)

　　= 2003 & 7

　　= 3

　　(3 >= 6 为假（FALSE）: 记录将被保存到#3号分区中)

　　第二条记录将要保存到的分区序号计算如下：

　　V = 8

　　N = YEAR('1998-10-19') & (8-1)

　　= 1998 & 7

　　= 6

　　(6 >= 4 为真（TRUE）: 还需要附加的步骤)

　　N = 6 & CEILING(5 / 2)

　　= 6 & 3

　　= 2

　　(2 >= 4 为假（FALSE）: 记录将被保存到#2分区中)

　　按照线性哈希分区的优点在于增加、删除、合并和拆分分区将变得更加快捷，有利于处理含有极其大量（1000吉）数据的表。它的缺点在于，与使用

　　常规HASH分区得到的数据分布相比，各个分区间数据的分布不大可能均衡。

　　KSY分区

　　类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

　　[sql]

　　CREATE TABLE tk (

　　col1 INT NOT NULL,

　　col2 CHAR(5),

　　col3 DATE

　　)

　　PARTITION BY LINEAR KEY (col1)

　　PARTITIONS 3;

　　在KEY分区中使用关键字LINEAR和在HASH分区中使用具有同样的作用，分区的编号是通过2的幂（powers-of-two）算法得到，而不是通过模数算法。

[1] [2] [3]