MySQL优化表分区

 转载于：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'
　　)
);