最新消息:

【分享】MySQL 分表和分区原理及应用方法详解

数据存储 摘星怪 803浏览 0评论

日常开发中课课家经常会遇到大表的情况,所谓的大表是指存储了百万级乃至千万级条记录的表。这样的表过于庞大,导致数据库在查询和插入的时候耗时太长,性能低下,如果涉及联合查询的情况,性能会更加糟糕。分表和表分区的目的就是减少数据库的负担,提高数据库的效率,通常点来讲就是提高表的增删改查效率。

1. 什么是什么是分表和分区? 有什么区别和联系?

1.1 分表

分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表,每个表都对应三个文件,MYD数据文件,.MYI索引文件,.frm表结构文件。这些表可以分布在同一块磁盘上,也可以在不同的机器上。app读写的时候根据事先定义好的规则得到对应的表名,然后去操作它。

将单个数据库表进行拆分,拆分成多个数据表,然后用户访问的时候,根据一定的算法(如用hash的方式,也可以用求余(取模)的方式),让用户访问不同的表,这样数据分散到多个数据表中,减少了单个数据表的访问压力。提升了数据库访问性能。分表的目的就在于此,减小数据库的负担,缩短查询时间。

Mysql分表分为垂直切分和水平切分

垂直切分是指数据表列的拆分,把一张列比较多的表拆分为多张表

通常我们按以下原则进行垂直拆分:

把不常用的字段单独放在一张表;

把text,blob(binary large object,二进制大对象)等大字段拆分出来放在附表中;

经常组合查询的列放在一张表中;

垂直拆分更多时候就应该在数据表设计之初就执行的步骤,然后查询的时候用jion关键起来即可。

水平拆分是指数据表行的拆分,把一张的表的数据拆成多张表来存放。

水平拆分原则

通常情况下,我们使用hash、取模等方式来进行表的拆分

比如一张有400W的用户表users,为提高其查询效率我们把其分成4张表users1,users2,users3,users4

通过用ID取模的方法把数据分散到四张表内Id%4= [0,1,2,3]

然后查询,更新,删除也是通过取模的方法来查询

部分业务逻辑也可以通过地区,年份等字段来进行归档拆分;

进行拆分后的表,这时我们就要约束用户查询行为。比如我们是按年来进行拆分的,这个时候在页面设计上就约束用户必须要先选择年,然后才能进行查询。

1.2 分区

mysql5.1开始支持数据表分区,分区和分表相似,都是按照规则分解表。不同在于分表将大表分解为若干个独立的实体表,而分区是将数据分段划分在多个位置存放,可以是同一块磁盘也可以在不同的机器。分区后,表面上还是一张表,但数据散列到多个位置了。app读写的时候操作的还是大表名字,db自动去组织分区的数据。 

分区的主要目的是为了在特定的SQL操作中减少数据读写的总量以缩减响应时间。

分区主要有两种形式:

水平分区(Horizontal Partitioning) 这种形式分区是对表的行进行分区,所有在表中定义的列在每个数据集中都能找到,所以表的特性依然得以保持。

举个简单例子:一个包含十年发票记录的表可以被分区为十个不同的分区,每个分区包含的是其中一年的记录。

垂直分区(Vertical Partitioning)这种分区方式一般来说是通过对表的垂直划分来减少目标表的宽度,使某些特定的列被划分到特定的分区,每个分区都包含了其中的列所对应的行。

举个简单例子:一个包含了大text和BLOB列的表,这些text和BLOB列又不经常被访问,这时候就要把这些不经常使用的text和BLOB了划分到另一个分区,在保证它们数据相关性的同时还能提高访问速度。

1.3. 表分区与分表的区别和联系
  • 分表与分区的区别在于:分区从逻辑上来讲只有一张表,而分表则是将一张表分解成多张表。
  • 都能提高mysql的性能,在高并发状态下都有一个良好的表现。
  • 分表和分区不矛盾,可以相互配合的,对于那些大访问量,并且表数据比较多的表,我们可以采取分表和分区结合的方式,访问量不大,但是表数据很多的表,我们可以采取分区的方式等。
  • 分表技术是比较麻烦的,需要手动去创建子表,app服务端读写时候需要计算子表名。采用merge好一些,但也要创建子表和配置子表间的union关系。
  • 表分区相对于分表,操作方便,不需要创建子表

2.分表的几种实现方式:

2.1 mysql集群

它并不是分表,但起到了和分表相同的作用。集群可分担数据库的操作次数,将任务分担到多台数据库上。集群可以读写分离,减少读写压力。从而提升数据库性能。

2.2 自定义规则分表

大表可以按照业务的规则来分解为多个子表。通常为以下几种类型,也可自己定义规则。

  • Range(范围)– 这种模式允许将数据划分不同范围。例如可以将一个表通过年份划分成若干个分区。
  • Hash(哈希)– 这中模式允许通过对表的一个或多个列的Hash Key进行计算,最后通过这个Hash码不同数值对应的数据区域进行分区。例如可以建立一个对表主键进行分区的表。
  • Key(键值)– 上面Hash模式的一种延伸,这里的Hash Key是MySQL系统产生的。
  • List(预定义列表 – 这种模式允许系统通过预定义的列表的值来对数据进行分割。
  • Composite(复合模式)– 以上模式的组合使用 

分表规则与分区规则一样,在分区模块详细介绍。 下面以Range简单介绍下如何分表(按照年份表)。

假设表结构有4个字段:自增id,姓名,存款金额,存款日期,把存款日期作为规则分表,分别创建几个表

2011年:account_2011
2012年:account_2012
……
2015年:account_2015

app在读写的时候根据日期来查找对应的表名,需要手动来判定。

var getTableName = function() {
var data = {
name: ‘tom’,
money: 2800.00,
date: ‘201410013059’
};
var tablename = ‘account_’;
var year = parseInt(data.date.substring(0, 4));
if (year < 2012) {
tablename += 2011; // account_2011
} else if (year < 2013) {
tablename += 2012; // account_2012
} else if (year < 2014) {
tablename += 2013; // account_2013
} else if (year < 2015) {
tablename += 2014; // account_2014
} else {
tablename += 2015; // account_2015
}
return tablename;
}

2.3 利用merge存储引擎来实现分表

merge分表,分为主表和子表,主表类似于一个壳子,逻辑上封装了子表,实际上数据都是存储在子表中的。

我们可以通过主表插入和查询数据,如果清楚分表规律,也可以直接操作子表。

子表2011年:

CREATE TABLE `account_2011` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`money` float NOT NULL ,
`tradeDate` datetime NOT NULL
PRIMARY KEY (`id`)
)
ENGINE=MyISAM
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=2
CHECKSUM=0
ROW_FORMAT=DYNAMIC
DELAY_KEY_WRITE=0
;

子表2012年:

CREATE TABLE `account_2012` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`money` float NOT NULL ,
`tradeDate` datetime NOT NULL
PRIMARY KEY (`id`)
)
ENGINE=MyISAM
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=2
CHECKSUM=0
ROW_FORMAT=DYNAMIC
DELAY_KEY_WRITE=0
;

主表,所有年:

CREATE TABLE `account_all` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`money` float NOT NULL ,
`tradeDate` datetime NOT NULL
PRIMARY KEY (`id`)
)
ENGINE=MRG_MYISAM
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
UNION=(`account_2011`,`account_2012`)
INSERT_METHOD=LAST
ROW_FORMAT=DYNAMIC
;

创建主表的时候有个INSERT_METHOD,指明插入方式,取值可以是:

0 不允许插入;

FIRST 插入到UNION中的第一个表;

LAST 插入到UNION中的最后一个表。

通过主表查询的时候,相当于将所有子表合在一起查询。这样并不能体现分表的优势,建议还是查询子表。

3. 表分区有什么好处?

  • 分区表的数据可以分布在不同的物理设备上,从而高效地利用多个硬件设备。
  • 和单个磁盘或者文件系统相比,可以存储更多数据
  • 如果需要,还可以备份和恢复独立的分区,这在非常大的数据集的场景下效果非常好

  • 优化查询。在where语句中包含分区条件时,可以只扫描一个或多个分区表来提高查询效率;涉及sum和count语句时,也可以在多个分区上并行处理,最后汇总结果。
  • 分区表更容易维护。例如:想批量删除大量数据可以清除整个分区。
  • 可以使用分区表来避免某些特殊的瓶颈,例如InnoDB的单个索引的互斥访问,ext3问价你系统的inode锁竞争等。

4. 分区表的限制因素

  • Mysql 5.6.5版本以前一个表最多只能有1024个分区,mysql 5.6.6 版本之后支持8192个分区。
  • MySQL 5.1中,分区表达式必须是整数,或者返回整数的表达式,通常使用year或者to_days等函数,在MySQL5.5中提供了非整数表达式分区的支持,mysql 5.6 对限制开始放开了。
  • 如果分区字段中有主键或者唯一索引的列,那么多有主键列和唯一索引列都必须包含进来。即:分区字段要么不包含主键或者索引列,要么包含全部主键和索引列。
  • 分区表中无法使用外键约束
  • MySQL的分区适用于一个表的所有数据和索引,不能只对表数据分区而不对索引分区,也不能只对索引分区而不对表分区,也不能只对表的一部分数据分区。
  • 目前mysql不支持空间类型和临时表类型进行分区,不支持全文索引。

  • mysql数据库的分区总是把null当作比任何非null更小的值,这和数据库中处理null值的order by操作是一样的,升序排序时null总是在最前面,因此对于不同的分区类型,mysql数据库对于null的处理也各不相同。对于range分区,如果向分区列插入了null,则mysql数据库会将该值放入最左边的分区,注意,如果删除分区,分区下的所有内容都从磁盘中删掉了,null所在分区被删除,null值也就跟着被删除了。在list分区下要使用null,则必须显式地定义在分区的散列值中,否则插入null时会报错。hash和key分区对于null的处理方式和range,list分区不一样,任何分区函数都会将null返回为0.

5. 如何判断当前MySQL是否支持分区?

命令:show variables like ‘%partition%’ 运行结果:

  mysql> show variables like ‘%partition%’;

  +——————-+——-+

  | Variable_name | Value |

  +——————-+——-+

  | have_partitioning | YES |

  +——————-+——-+

  1 row in set (0.00 sec)

have_partintioning 的值为YES,表示支持分区。

6. MySQL支持的分区类型有哪些?

  • RANGE分区:按照数据的区间范围分区
  • LIST分区:按照List中的值分区,与RANGE的区别是,range分区的区间范围值是连续的。
  • HASH分区
  • KEY分区

说明 :在MySQL5.1版本中,RANGE,LIST,HASH分区要求分区键必须是INT类型,或者通过表达式返回INT类型。但KEY分区的时候,可以使用其他类型的列(BLOB,TEXT类型除外)作为分区键。

7. Range 分区

利用取值范围进行分区,区间要连续并且不能互相重叠。

语法:

partition by range(exp)( //exp可以为列名或者表达式,比如to_date(created_date)

partition p0 values less than(num)

)

例如:

mysql> create table emp(

-> id INT NOT null,

-> store_id int not null

-> )

-> partition by range(store_id)(

-> partition p0 values less than(10),

-> partition p1 values less than(20)

-> );

上面的语句创建了emp表,并根据store_id字段进行分区,小于10的值存在分区p0中,大于等于10,小于20的值存在分区p1中。

注意:每个分区都是按顺序定义的,从最低到最高。上面的语句,如果将less than(10) 和less than (20)的顺序颠倒过来,那么将报错,如下:

ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition

RANGE分区存在的问题:

range范围覆盖问题:当插入的记录中对应的分区键的值不在分区定义的范围中的时候,插入语句会失败。 上面的例子,如果我插入一条store_id = 30的记录会怎么样呢? 我们上面分区的时候,最大值是20,如果插入一条超过20的记录,会报错:

mysql> insert into emp(id,store_id) values(2,30);

ERROR 1526 (HY000): Table has no partition for value 30

提示30这个值没有对应的分区。

解决办法 :

A. 预估分区键的值,及时新增分区。

B. 设置分区的时候,使用values less than maxvalue 子句,MAXVALUE表示最大的可能的整数值。

例如:

create table range(
  id int(11),
  money int(11) unsigned not null,
  date datetime
  )partition by range(year(date))(
  partition p2007 values less than (2008),
  partition p2008 values less than (2009),
  partition p2009 values less than (2010)
  partition p2010 values less than maxvalue
);

C. 尽量选择能够全部覆盖的字段作为分区键,比如一年的十二个月等。

Range分区中,分区键的值如果是NULL,将被作为一个最小值来处理。

分区实例:

1.将用户表分成4个分区,以每300万条记录为界限,每个分区都有自己独立的数据、索引文件的存放目录。还可以将这些分区所在的物理磁盘分开完全独立,可以提高磁盘IO吞吐量。

CREATE TABLE users (  
       id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,  
       usersname VARCHAR(30) NOT NULL DEFAULT '',  
       email VARCHAR(30) NOT NULL DEFAULT ''  
)  
PARTITION BY RANGE (id) (  
       PARTITION p0 VALUES LESS THAN (3000000)  
       DATA DIRECTORY = '/data0/data'  
       INDEX DIRECTORY = '/data0/index',  
  
       PARTITION p1 VALUES LESS THAN (6000000)  
       DATA DIRECTORY = '/data1/data'  
       INDEX DIRECTORY = '/data1/index',  
  
       PARTITION p2 VALUES LESS THAN (9000000)  
       DATA DIRECTORY = '/data2/data'  
       INDEX DIRECTORY = '/data2/index',  
  
       PARTITION p3 VALUES LESS THAN MAXVALUE     
       DATA DIRECTORY = '/data3/data'   
       INDEX DIRECTORY = '/data3/index'  
);

8. LIST 分区

List分区是建立离散的值列表告诉数据库特定的值属于哪个分区。 语法:

  partition by list(exp)( //exp为列名或者表达式

  partition p0 values in (3,5) //值为3和5的在p0分区

  )

  与Range不同的是,list分区不必生命任何特定的顺序。例如:

  mysql> create table emp1(

  -> id int not null,

  -> store_id int not null

  -> )

  -> partition by list(store_id)(

  -> partition p0 values in (3,5),

  -> partition p1 values in (2,6,7,9)

  -> );

注意:如果插入的记录对应的分区键的值不在list分区指定的值中,将会插入失败。并且,list不能像range分区那样提供maxvalue。

 

9. Columns 分区

MySQL5.5中引入的分区类型,解决了5.5版本之前range分区和list分区只支持整数分区的问题。

Columns分区可以细分为 range columns分区和 list columns分区,他们都支持整数,日期时间,字符串三大数据类型。(不支持text和blob类型作为分区键) columns分区还支持多列分区(这里不详细展开)。

10. Hash分区

Hash分区主要用来分散热点读,确保数据在预先确定个数的分区中尽可能平均分布。

MySQL支持两种Hash分区: 常规Hash分区和线性Hash分区。

A. 常规Hash分区: 使用取模算法

语法:

partition by hash(store_id) partitions 4;

上面的语句,根据store_id对4取模,决定记录存储位置。 比如store_id = 234的记录,MOD(234,4)=2,所以会被存储在第二个分区。

常规Hash分区的优点和不足 优点:能够使数据尽可能的均匀分布。 缺点:不适合分区经常变动的需求。假如我要新增加两个分区,现在有6个分区,那么MOD(234,6)的结果与之前MOD(234,4)的结果就会出现不一致,这样大部分数据就需要重新计算分区。为解决此问题,MySQL提供了线性Hash分区。

实例:

create table hash(
  a int(11),
  b datetime
  )partition by hash (YEAR(b)
  partitions 4;

B. 线性Hash分区:分区函数是一个线性的2的幂的运算法则。

语法:

partition by LINER hash(store_id) partitions 4;

与常规Hash的不同在于,“Liner”关键字。 算法介绍: 假设要保存记录的分区编号为N,num为一个非负整数,表示分割成的分区的数量,那么N可以通过以下步骤得到:

Step 1. 找到一个大于等于num的2的幂,这个值为V,V可以通过下面公式得到:

V = Power(2,Ceiling(Log(2,num)))

例如:刚才设置了4个分区,num=4,Log(2,4)=2,Ceiling(2)=2,power(2,2)=4,即V=4

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

例如:刚才V=4,store_id=234对应的N值,N = 234&(4-1) =2

Step 3. 当N>=num,设置V=Ceiling(V/2),N=N&(V-1)

例如:store_id=234,N=2<4,所以N就取值2,即可。

假设上面算出来的N=5,那么V=Ceiling(2.5)=3,N=234&(3-1)=1,即在第一个分区。

线性Hash的优点和不足 优点:在分区维护(增加,删除,合并,拆分分区)时,MySQL能够处理得更加迅速。 缺点:与常规Hash分区相比,线性Hash各个分区之间的数据分布不太均衡。

11. Key分区

类似Hash分区,Hash分区允许使用用户自定义的表达式,但Key分区不允许使用用户自定义的表达式。Hash仅支持整数分区,而Key分区支持除了Blob和text的其他类型的列作为分区键。

语法:

partition by key(exp) partitions 4;//exp是零个或多个字段名的列表

key分区的时候,exp可以为空,如果为空,则默认使用主键作为分区键,没有主键的时候,会选择非空惟一键作为分区键。

实例:

create table t_key(
  a int(11),
  b datetime)
  partition by key (b)
  partitions 4;

12. 子分区

分区表中对每个分区再次分割,又成为复合分区。

13. 分区对于NULL值的处理

MySQ允许分区键值为NULL,分区键可能是一个字段或者一个用户定义的表达式。一般情况下,MySQL在分区的时候会把NULL值当作零值或者一个最小值进行处理。

注意:

Range分区中:NULL值被当作最小值来处理

List分区中:NULL值必须出现在列表中,否则不被接受

Hash/Key分区中:NULL值会被当作零值来处理

14. 分区管理

分区管理包括对于分区的增加,删除,以及查询。

14.1 增加分区:

新增 RANGE 分区:

alter table user add partition(partition user_3 values less than maxvalue);

新增 LIST 分区:

ALTER TABLE category ADD PARTITION (PARTITION p4 VALUES IN (16,17,18,19));

新增 HASH/KEY 分区:

将分区总数扩展到8个。

ALTER TABLE users ADD PARTITION PARTITIONS 8;

14.2 删除分区:

对于Range分区和List分区:

alter table table_name drop partition p0;  //p0为要删除的分区名称

删除了分区,同时也将删除该分区中的所有数据。同时,如果删除了分区导致分区不能覆盖所有值,那么插入数据的时候会报错。

对于Hash和Key分区:

alter table table_name coalesce partition 2; //将分区缩减到2个

coalesce [ˌkəʊəˈles] vi. 联合,合并

14.3 分区查询:

1)、查询某张表一共有多少个分区

  mysql> select

  -> partition_name,

  -> partition_expression,

  -> partition_description,

  -> table_rows

  -> from

  -> INFORMATION_SCHEMA.partitions

  -> where

  -> table_schema=’test’

  -> and table_name = ’emp’;

  +—————-+———————-+———————–+————+

  | partition_name | partition_expression | partition_description | table_rows |

  +—————-+———————-+———————–+————+

  | p0 | store_id | 10 | 0 |

  | p1 | store_id | 20 | 1 |

  +—————-+———————-+———————–+————+

  即,可以从information_schema.partitions表中查询。

2)、查看执行计划,判断查询数据是否进行了分区过滤

  mysql> explain partitions select * from emp where store_id=10 \\G;

  *************************** 1. row ***************************

  id: 1

  select_type: SIMPLE

  table: emp

  partitions: p1

  type: system

  possible_keys: NULL

  key: NULL

  key_len: NULL

  ref: NULL

  rows: 1

  Extra:

  1 row in set (0.00 sec)

上面的结果:partitions:p1 表示数据在p1分区进行检索。

14.4 合并重建分区

RANGE 分区重建:

ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES LESS THAN (6000000));

上面的语句将原来的 p0,p1 分区合并起来,放到新的 p0 分区中。

下面的SQL,将p201001 – p201009 合并为3个分区p2010Q1 – p2010Q3

ALTER TABLE sale_data
REORGANIZE PARTITION p201001,p201002,p201003,
p201004,p201005,p201006,
p201007,p201008,p201009 INTO
(
PARTITION p2010Q1 VALUES LESS THAN (201004),
PARTITION p2010Q2 VALUES LESS THAN (201007),
PARTITION p2010Q3 VALUES LESS THAN (201010)
);

LIST 分区重建:

ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES IN(0,1,4,5,8,9,12,13));
将原来的 p0,p1 分区合并起来,放到新的 p0 分区中。

HASH/KEY 分区重建:

ALTER TABLE users REORGANIZE PARTITION COALESCE PARTITION 2;
用 REORGANIZE 方式重建分区的数量变成2,在这里数量只能减少不能增加。想要增加可以用 ADD PARTITION 方法。

 

转载请注明:架构迷 » 【分享】MySQL 分表和分区原理及应用方法详解

发表我的评论
取消评论

表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址