mysql分区

当数据库表的数据量很大的时候，单表的查询效率会变的很低下，这个时候我们可以考虑为表建立索引。然而表的索引建立后，虽然查询效率提升了不少，但是随着数据量的加大索引也将会变得非常的庞大，甚至有可能超过业务数据的的大小，这时插入，更新，删除操作的过程中会更新表的索引，而表的索引又非常的大因此效率会很低下。

使用索引会面临上面的问题，这个时候我们可以采用分表技术。分表技术又分为两种，垂直分表和水平分表。

垂直分表：比如说一个表有10个字段，那么把拆分成两个分别有5个字段的表。

水平分表：是指把一张表由上到下的顺序给她分段。

垂直分区一般意义不大，我也没用过，这里只说水平分区。

分表一般在应用层层实现，即同一个业务查询可能要查询不同的表。比如可以把表按照日期分表，然后根据不同的查询到不同的表中进行select；还比如可以按照一种hash算法进行分表。这种方式可以比较好的避免上述索引带来的问题，但是也存在一些问题：如原来的一个业务逻辑只需要一个sql就可以完成，分表之后可能会需要多个sql进行多次的数据库交互（在业务逻辑比较复杂的情况下），总而言之就是这种分表方式会让程序的逻辑变的复杂且难于维护。

mysql分区的概念：

它允许根据可以设置为任意大小的规则，跨文件系统分配单个表的多个部分。实际上，表的不同部分在不同的位置被存储为单独的表。也就是说在物理上把一个表拆分为多个表单上在逻辑上还是一个表。

分区的类型：

RANGE 分区：基于属于一个给定连续区间的列值，把多行分配给分区。

LIST 分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。

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

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

RANGE 分区：

这些区间要连续且不能相互重叠，使用VALUES LESS THAN操作符来进行定义。在下面的几个例子中，假定你创建了一个如下的一个表，该表保存有20家音像店的职员记录，这20家音像店的编号从1到20。

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 NOT NULL,

    store_id INT NOT NULL

)

PARTITION BY RANGE (store_id) (

    PARTITION p0 VALUES LESS THAN (6),

    PARTITION p1 VALUES LESS THAN (11),

    PARTITION p2 VALUES LESS THAN (16),

    PARTITION p3 VALUES LESS THAN MAXVALUE

)；

上面语句，会划分为4个区：store_id 小于6的，小于11的，小于16的，小于最大值的

LIST 分区：

其实可以理解为range分区的一个特例，分区按照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)

)；

例子很明了，就不解释了。

HASH分区：

HASH分区主要用来确保数据在预先确定数目的分区中平均分布。在RANGE和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中；而在HASH分区中，MySQL 自动完成这些工作，你所要做的只是基于将要被哈希的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。 

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 HASH(store_id)

PARTITIONS 4；	分为4个区

mysql分区的一些优点：

1，与单个磁盘或文件系统分区相比，可以存储更多的数据。

2， 对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据。相反地，在某些情况下，添加新数据的过程又可以通过为那些新数据,专门增加一个新的分区，来很方便地实现。

3，可以使用分区表来避免某些特殊的瓶颈：例如innodb单个索引的互斥访问，ext3文件系统的锁竞争问题。

mysql分区的一些限制和缺点：

1，NULL会使分区过滤无效;

2, 打开并锁住所有底层表的成本可能会很高（水平分表则没有这个问题）;

3, 在5.1之后才有分区的功能;

4, 选择分区类型的成本可能会很高。

5，只能有1024个分区；

6，在5.1中分区字段必须是整数，或者是对字段进行函数运算的表达式必须的整数，在5.5中一些场景下可以直接使用列；

7，分区表中无法使用外键约束；

参考：

MYsql5.1 参考手册；

高性能mysql第三版