mysql分区

mysql自5.1开始支持分区功能，如果很多朋友对分库、分表和分区这些概念还比较模糊的话，可以查看我的另外一篇文章《mysql之分库、分表和分区》。既然有分区这个概念，我个人觉得首先应该弄明白的是，为什么要分区，分区的好处是什么？

mysql的表(myisam)的物理存储是分为三个文件：frm文件是表定义，MYD是数据文件，MYI是索引文件，如果当一个表达到一定的数量级的时候，那自然相应的物理文件也会很庞大，即使索引的物理文件也会变得比较臃肿。

分区就是为了解决这种大数据量而产生的，应该注意的是：分区不是分表，分区之后表还是一个表，它是把存储文件进行拆分，那数据库在读取物理文件的时候，每次读入的是切分后的数据量较小的存储文件，这样自然会提高效率，而且多个小的存储文件之间还可以并行。

 

分区应该注意的事项：

1、 做分区时，要么不定义主键，要么把分区字段加入到主键中。

2、 分区字段不能为NULL，要不然怎么确定分区范围呢，所以尽量NOT NULL

 

分区类型：

1、 RANGE分区：按照一个连续范围(列值)分区，把多行分配给分区。

2、 LIST分区：类似于RANGE分区，不过他的这个范围不是连续的，是按照业务逻辑定的，一个离散值集合。

3、  HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。

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

 

RANGE分区：

 

建立分区：

 

create table fenqu(

id int not null auto_increment,

tid int not null,

add_time date,

primary key(id,tid)

)

partition by range(tid)

(

partition p0 values less than (5),

partition p1 values less than (10),

partition p2 values less than (15),

partition p3 values less than MAXVALUE

);

这是在建表的时候直接建立分区，共分成四个分区。曾经看到有人在问主键的问题，如果非要定义主键，那就选择把分区字段加入到主键中primary key(id,tid)。

如果想在已经建好的表上进行分区，如果使用alter添加分区的话，mysql会提示错误：

ERROR 1505 <HY000> Partition management on a not partitioned table is not possible

正确的方法是新建一个具有分区的表，结构一致，然后用insert into 分区表 select * from 原始表;

 

LIST分区:

 

建立分区采用手册上的实例：

CREATE TABLE employees (

    id INT NOT NULL,

    fname VARCHAR(30),

    lname VARCHAR(30),

    hired DATE NOT NULL DEFAULT '1970-01-01',

    separated DATE NOT NULL DEFAULT '9999-12-31',

    job_code INT,

    store_id INT

)

PARTITION BY LIST(store_id)

    PARTITION pNorth VALUES IN (3,5,6,9,17),

    PARTITION pEast VALUES IN (1,2,10,11,19,20),

    PARTITION pWest VALUES IN (4,12,13,14,18),

    PARTITION pCentral VALUES IN (7,8,15,16)

)；

 

网店store_id是分属于不同经销权的地区的，所以按照不同地区进行切分，确定每个地区的store_id.(具体请参看mysql手册)。 LIST分区也有自身的限制，那就是如果已经确按照各网店进行切分，那如果要插入新数据时，网店编号是分区是没有的，那插入会失败。

INSERT INTO employees VALUES 

    (224, 'Linus', 'Torvalds', '2002-05-01', '2004-10-12', 42, 21);

想上面这条语句就会执行失败。

 

HASH分区：

Hash分区是希望能够将数据平均的分布到每个分区上，除了基本的hash分区外，还支持线性hash，个人觉得有线性hash分区，也就是为了追求“y=nx”这种线性分布，手册上有介绍，在此就不多做赘述了。

 

KEY分区：

按照KEY进行分区类似于按照HASH分区，除了HASH分区使用的用户定义的表达式，而KEY分区的 哈希函数是由MySQL服务器提供。 MySQL 簇（Cluster）使用函数MD5()来实现KEY分区；对于使用其他存储引擎的表，服务器使用其自己内部的 哈希函数，这些函数是基于与 PASSWORD()一样的运算法则。（手册定义）

 

 

分区管理：

ALTER TABLE table_name (DROP|ADD) PARTITION #####;

 

当然还有一些合并分区啊，子分区的概念，在此就不一一介绍了，手册上都有

 

======================

当 MySQL的总记录数超过了100万后，会出现性能的大幅度下降吗？答案是肯定的，但是，性 能下降>的比率不一而同，要看系统的架构、应用程序、还有>包括索引、服务器硬件等多种因素而定。当有网友问我这个问题的时候，我最常见的回 答>就是：分表，可以根据id区间或者时间先后顺序等多种规则来分表。分表很容易，然而由此所带来的应用程序甚至是架构方面的改动工作却不> 容小觑，还包括将来的扩展性等。

在以前，一种解决方案就是使用 MERGE
类型，这是一个非常方便的做饭。架构和程序基本上不用做改动，不过，它的缺点是显见的：

• 只能在相同结构的 MyISAM 表上使用
• 无法享受到 MyISAM 的全部功能，例如无法在 MERGE 类型上执行 FULLTEXT 搜索
• 它需要使用更多的文件描述符
• 读取索引更慢

这个时候，MySQL 5.1 中新增的分区(Partition)功能的优势也就很明显了：

• 与单个磁盘或文件系统分区相比，可以存储更多的数据
• 很容易就能删除不用或者过时的数据
• 一些查询可以得到极大的优化
• 涉及到 SUM()/COUNT() 等聚合函数时，可以并行进行
• IO吞吐量更大

分区允许可以设置为任意大小的规则，跨文件系统分配单个表的多个部分。实际上，表的不同部分在不同的位置被存储为单独的表。

 

分区应该注意的事项：

1、 做分区时，要么不定义主键，要么把分区字段加入到主键中。

2、 分区字段不能为NULL，要不然怎么确定分区范围呢，所以尽量NOT NULL

二、分区的类型

• RANGE 分区：基于属于一个给定连续区间的列值，把多行分配给分区。
• LIST 分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
• HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包>含MySQL中有效的、产生非负整数值的任何表达式。
• KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含>整数值。

可以通过使用SHOW VARIABLES命令来确定MySQL是否支持分区，例如：

mysql> SHOW VARIABLES LIKE '%partition%';

+-----------------------+-------+

| Variable_name         | Value |

+-----------------------+-------+

| have_partition_engine | YES   |

+-----------------------+-------+

1 row in set (0.00 sec)

1 。range分区

　create table t_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 
　　)；

2.list分区

 

create table t_list( 
　　a int(11), 
　　b int(11) 
　　)(partition by list (b) 
　　partition p0 values in (1,3,5,7,9), 
　　partition p1 values in (2,4,6,8,0) 
　　);

对于innodb和myisam引擎，一条语句插入多条记录的时候，如果中间有值不能插入，innodb会全部回滚，myisam在错误值之前的数据可以插入到表中。

对于innodb和myisam引擎，一条语句插入多条记录的时候，如果中间有值不能插入，innodb会全部回滚，myisam在错误值之前的数据可以插入到表中。

3.hash分区

hash分区的目的是将数据均匀的分布到预先定义的各个分区中，保证各分区的数据量大致一致。

 

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

 

　　hash的分区函数页需要返回一个整数值。partitions子句中的值是一个非负整数，不加的partitions子句的话，默认为分区数为1。

4.key分区

key分区和hash分区相似，不同在于hash分区是用户自定义函数进行 分区，key分区使用mysql数据库提供的函数进行分区，NDB cluster使用MD5函数来分区，对于其他存储引擎mysql使用内部的hash函数，这些函数基于password()一样的算法。

 

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

5。columns分区

上面的RANGE、LIST、HASH、KEY四种分区中，分区的条件必须是整形，如果不是整形需要通过函数将其转换为整形。

　　mysql-5.5开始支持COLUMNS分区，可视为RANGE和LIST分区的进化，COLUMNS分区可以直接使用非整形数据进行分区。COLUMNS分区支持以下数据类型：

　　所有整形，如INT SMALLINT TINYINT BIGINT。FLOAT和DECIMAL则不支持。

　　日期类型，如DATE和DATETIME。其余日期类型不支持。

　　字符串类型，如CHAR、VARCHAR、BINARY和VARBINARY。BLOB和TEXT类型不支持。

　　COLUMNS可以使用多个列进行分区。

新增分区

mysql> ALTER TABLE sale_data

    ->   ADD PARTITION (PARTITION p201010 VALUES LESS THAN (201011));

Query OK, 0 rows affected (0.36 sec)

Records: 0  Duplicates: 0  Warnings: 0

删除分区

--当删除了一个分区，也同时删除了该分区中所有的数据。

mysql> ALTER TABLE sale_data DROP PARTITION p201010;

Query OK, 0 rows affected (0.22 sec)

Records: 0  Duplicates: 0  Warnings: 0

分区的合并

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

mysql> 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)

    -> );

Query OK, 0 rows affected (1.14 sec)

Records: 0  Duplicates: 0  Warnings: 0