MySQL分区性能初探

一，      分区概念 

分区允许根据指定的规则，跨文件系统分配单个表的多个部分。表的不同部分在不同的位置被存储为单独的表。MySQL从5.1.3开始支持Partition。

分区和手动分表对比

手动分表	分区
多张数据表	一张数据表
重复数据的风险	没有数据重复的风险
写入多张表	写入一张表
没有统一的约束限制	强制的约束限制

 

MySQL支持RANGE，LIST，HASH，KEY分区类型，其中以RANGE最为常用：

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

 

二，分区能做什么

• 逻辑数据分割
• 提高单一的写和读应用速度
• 提高分区范围读查询的速度
• 分割数据能够有多个不同的物理文件路径
• 高效的保存历史数据
• 一个表上的约束检查
• 不同的主从服务器分区策略，例如master按Hash分区，slave按range分区

 

三，分区的限制(截止5.1.44版)

•   只能对数据表的整型列进行分区，或者数据列可以通过分区函数转化成整型列

•   最大分区数目不能超过1024

•   如果含有唯一索引或者主键，则分区列必须包含在所有的唯一索引或者主键在内

•   不支持外键

•   不支持全文索引（fulltext）

• 按日期进行分区很非常适合，因为很多日期函数可以用。但是对于字符串来说合适的分区函数不太多

四，什么时候使用分区

•   海量数据表

•   历史表快速的查询，可以采用ARCHIVE+PARTITION的方式。

•   数据表索引大于服务器有效内存

•   对于大表，特别是索引远远大于服务器有效内存时，可以不用索引，此时分区效率会更有效。

一、为什么要分区？

需求：大数据。解决方案：分而治之，更细一点即为。将大表和大索引分为一个更小的操作单元
在mysql中，分区允许将表、索引和索引编排表细分为更小的单元。分区后，每个分区有自己单独的名称。对于DBA来讲，这些分区可以统一管理，也可以分开管理。
但是对于应用程序来讲，分区的表和没有分区的表示一样的。换句话来讲，分区对于应用是透明的，只是 数据库对于数据的重新整理。
Mysql分区，这个是 mysql对于分区的官方解释。

1.分区的作用

a)提升性能


应该说，几乎所有分区的目的都是用来提升性能的。不是谁哪天早上吃着鸡蛋灌饼，说咱们分个区玩玩吧！不是的。那么分区是如何来提升检索数据的性能的呢？没有分区之前，数据每次在查询和检索总是面向的是整个数据库和表。分区之后，mysql针对每个分区生成特定的数据文件与索引文件。每次只会检索特定的部分数据，因此可以更好的来执行和维护数据库。究其原因还是因为在分区之后表指派到不同的物理驱动器上，这样便可在同时访问多个分区时减少物理 I/O 争用。
b)易于管理
对于一些无用的冗余的历史数据来讲，可以根据分区之后直接删除相应的分区。操作起来更加简便，因为执行数以万计的数据远比直接删除文件来的困难的多、时间也更加的长。
c)容错
分区之后相比分区之前，一张表从之前一个文件分为现在的三个文件。分区之后即使一个文件出现破坏，也不会影响其他数据。
2..常用分区方法

RANGE分区
LIST分区
HASH分区
KEY分区

二、如何分区？

1.查看数据库是否支持分区

?

1	SHOW VARIABLES LIKE '%partition%' ;

 

对于mysql来讲，现阶段支持分区操作的版本有5.1和5.5.如图显示为YES则表明该数据库支持分区操作。

2.四种常见分区

Range分区

俗称：范围分区。根据表的字段的值，依据给定某段连续的区间来分区。

 

直接创建表时分区

 

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13

create table teacher (id varchar (20) not null , name varchar (20), age varchar (20), birthdate date not null , salary int ) partition by range( year (birthdate)) ( partition p1 values less than (1970), partition p2 values less than (1990), partition p3 values less than maxvalue );

 

 

Ps：创建teacher表，并在创建teacher表同时根据birthdate字段将表划分为p1、p2、p3三个分区。

 

 

在创建表后分区

 

 

?

1 2 3 4 5 6 7

ALTER TABLE teacher partition by range( year (birthdate)) ( partition p1 values less than (1970), partition p2 values less than (1990), partition p3 values less than maxvalue );

 

Ps：给已经创建了的表分区，分为p1、p2、p3.

 

LIST分区

俗名：列表分区。其实list分区和range分区应该说都是一样的，不同的是range分区在分区是的依据是一段连续的区间；而list分区针对的分区依据是一组分布的散列值。

 

?

1 2 3 4 5 6 7 8 9 10 11 12

create table student   (id varchar (20) not null ,   studentno int (20) not null ,   name varchar (20),   age varchar (20)   )   partition by list(studentno)   (   partition p1 values in (1,2,3,4),   partition p2 values in  (5,6,7,8),   partition p3 values in (9,10,11)   );

 

Ps：如上创建表student，并将student表分为p1、p2、p3三个分区。需要注意的是一般情况下，针对表的分区字段为int等数值类型。

 

HASH分区

 

小名：哈希分区。哈希分区主要是依据表的某个字段以及指定分区的数量。

 

?

1 2 3 4 5 6 7

create table user (    id int (20) not null ,    role varchar (20) not null ,    description varchar (50) ) partition by hash(id) partitions 10;

 

 

Ps:如上创建user表，并将user表平均分为十个分区。比较有限制的就是需要知道表的数据有多少才能更好平均分配分区。

 

 

key分区

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

 

 

?

1 2 3

create table role( id int (20) not null , name varchar (20) not null ) partition by linear key (id) partitions 10;

 

 

 

3.分区表管理

对指定表添加分区

 

?

1	alter table user add partition(partition p4 values less than MAXVALUE);

 

删除指定表指定分区

alter table student drop partition p1;

 

创建子分区

 

?

1 2 3 4 5 6 7 8

create table role_subp(id int (20) not null , name int (20) not null ) partition by list(id) subpartition by hash( name ) subpartitions 3 (    partition p1 values in (10),    partition p2 values in (20) )

 

 

复合分区

 

 

?

1 2 3

alter table user reorganize partition p1,p3 into (partition p1 values less than (1000));