MySQL优化表分区

2022-10-19 17:45:36

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

码农公寓

相关文章