转载于:https://www.bilibili.com/video/BV1E7411q7Nx?p=1
一、RANGE分区
- 基于属于一个给定连续区间的列值,把多行分配给分区。
- 这些区间要连续且不能相互重叠,使用VALUES LESS THAN操作符来进行定义。
-- RANGE分区案例 create table t_student ( id varchar(50) not null, name varchar(20), age int, primary key (id,age) ) partition by range(age) ( partition p01 VALUES less than (10), partition p02 VALUES less than (20), partition p03 VALUES less than (30), partition p04 VALUES less than (MAXVALUE) );
二、LIST分区
- 类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
- LIST分区通过使用"PARTITION BY LIST(expr)"来实现,其中"expr" 是某列值或一个基于某个列值、并返回一个整数值的表达式,然后通过"VALUES IN (value list)"的方式来定义每个分区,其中"value list"是一个通过逗号分隔的整数列表。
-- LIST分区案例 -- LIST分区案例对于一个综合性的网店来说,商品分为诸多种类。我们可以按照商品ID进行range分区,也可以按照商品的类型划分分区。在这个例子中, LIST分区给了我们更多的选择。 create table t_commodity ( id varchar(50) not null, cid int, name varchar(20), date datetime ) partition by list (cid) ( partition po1 values in (1,2,3), partition po2 values in (4,5,6), partition po3 values in (7,8,9) );
三、HASH分区
- 基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式。
- 要使用HASH分区来分割一个表,要在CREATE TABLE语句上添加一个"PARTITION BY HASH (expr)"子旬,其中"expr"是一个返回一个整数的表达式。它可以仅仅是字段类型为MySQL整型的一列的名字。此外,你很可能需要在后面再添加一个"PARTITIONS num"子句,其中num是一个非负的整数,它表示表将要被分割成分区的数量。如果没有"PARTITIONS num"子句,那么分区的数量将默认为1。
- 使用HASH分区的优点在于数据分布较为均匀。
-- HASH分区案例 -- 另外,在MySQL Cluster中,分区行为是自动的。默认情况下,分区的数量和ndb node数量相同。通常在节点数很多的情况下,会通过配置分区数和node group搭配进行调整。 create table t_company1 ( id varchar(50) not null, cid int, name varchar(20), date datetime ) partition by hash (cid) partitions 4;
四、LINEAR HASH分区
- 线性与常规哈希的区别在于,线性哈希功能使用的一个线性的2的幂(powers-of-two )运算法则,而常规哈希使用的是求哈希函数值的模数。
- 按照线性哈希分区的优点在于增加、删除、合并和拆分分区将变得更加快捷,有利于处理含有极其大量(1T)数据的表。不过, MysQL的线性哈希算法导致相比较常规哈希,数据可能分布的不那么均衡,容易产生"hotspot nodes"。
- 关于LINEAR HASH算法参见MySQL官方文档:http://dev.mysql.com/doc/refman/5.1/zh/partitioninq.html#partitioning-linear-hash
-- LINEAR HASH分区案例 -- 线性哈希分区和常规哈希分区在语法上的唯一区别在于,在"PARTITION BY" 子句中添加"LINEAR"关键字。 create table t_company2 ( id varchar(50) not null, cid int, name varchar(20), date datetime ) partition by linear hash (cid) partitions 4;
五、KEY分区
- 按照KEY进行分区类似于按照HASH分区,除了HASH分区使用的用户定义的表达式,而KEY分区的哈希函数是由MysQL服务器提供。MySQL簇( Cluster )使用函数MD5()来实现KEY分区;对于使用其他存储引擎的表,服务器使用其自己内部的哈希函数,这些函数是基于与PASSWORD()一样的运算法则。
-- KEY分区案例 create table t_company3 ( id varchar(50) not null, cid int, name varchar(20), date datetime ) partition by linear key (cid) partitions 4;
六、多列分区
- COLUMNS关键字允许字符串和日期列作为分区定义列,同时还允许使用多个列定义一个分区。
-- 多列分区案例 create table t_order ( a int, b int, c int ) partition by range columns(a,b) ( partition p01 values less than (10,10), partition po2 values less than (10,20), partition p03 values less than (10,30), partition p04 values less than (10,maxvalue), partition p05 values less than (maxvalue,maxvalue) );
-- 多列分区案例 -- 多列分区案例第一个分区用来存储雇佣于1990年以前的女职员,第二个分区存储股用于1990-2000年之间的女职员,第三个分区存储所有剩下的女职员。对于分区p04到p06 ,我们策略是一样的,只不过存储的是男职员。最后一个分区是控制情况。 create table t_emplovees ( emp_no int, birth_date datetime, first_name varchar(20), last_name varchar(20), gender char(1), hire_date datetime ) engine=myisam partition by range columns(gender,hire_date) ( partition p01 values less than ('F','1990-01-01'), partition p02 values less than ('F','2000-01-01'), partition p03 values less than ('F',maxvalue), partition p04 values less than ('M', '1990-01-01'), partition p05 values less than ('M','2000-01-01'), partition p06 values less than ('M',maxvalue), partition p07 values less than (maxvalue,maxvalue) );
七、子分区
- 子分区是分区表中每个分区的再次分割。子分区可以用于特别大的表,在多个磁盘间分配数据和索引。
-- 子分区案例 create table t_order_details ( id int, udate datetime ) partition by range(year(udate)) subpartition by hash(to_days(udate)) subpartitions 2 ( partition po values less than (1990), partition p1 values less than (2000), partition p2 values less than maxvalue ) -- 子分区案例 -- 将每个子分区保存在不同的存储上,优化1/0性能。 -- 注释:data directory 后跟的是数据存放的文件位置,index directory后跟的是索引存放的文件位置 create table t_order_content ( id int, udate datetime ) partition by range(year(udate)) subpartition by hash(to_days(udate)) ( partition p01 values less than (1990) ( subpartition s0 data directory ='/var/a/data' index directory = '/var/a/idx', subpartition s1 data directory = '/var/b/data' index directory = '/var/b/idx' ), partition p02 values less than (2000) ( subpartition s2 data directory ='/var/c/data' index directory = '/var/c/idx', subpartition s3 data directory = '/var/d/data' index directory = '/var/d/idx' ) );