MySQL 分区和分表总结

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

什么是分表？

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

什么是分区？

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

mysql分表和分区有什么联系呢？

1.都能提高mysql的性高，在高并发状态下都有一个良好的表现。

2.分表和分区不矛盾，可以相互配合的，对于那些大访问量，并且表数据比较多的表，我们可以采取分表和分区结合的方式（如果merge这种分表方式，不能和分区配合的话，可以用其他的分表试），访问量不大，但是表数据很多的表，我们可以采取分区的方式等。

3.分表技术是比较麻烦的，需要手动去创建子表，app服务端读写时候需要计算子表名。采用merge好一些，但也要创建子表和配置子表间的union关系。

4.表分区相对于分表，操作方便，不需要创建子表。

分区又可以分为两种：

水平分区（Horizontal Partitioning）

这种形式分区是对表的行进行分区，通过这样的方式不同分组里面的物理列分割的数据集得以组合，从而进行个体分割（单分区）或集体分割（1个或多个分区）。所有在表中定义的列在每个数据集中都能找到，

所以表的特性依然得以保持。

举个简单例子：一个包含十年发票记录的表可以被分区为十个不同的分区，每个分区包含的是其中一年的记录。（注：这里具体使用的分区方式我们后面再说，可以先说一点，一定要通过某个属性列来分割，譬如这里使用的列就是年份）

垂直分区（Vertical Partitioning）

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

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

分区表的原理

分区表是由多个相关的底层表实现，这些底层表也是由句柄对象表示，所以我们也可以直接访问各个分区，存储引擎管理分区的各个底层表和管理普通表一样（所有的底层表都必须使用相同的存储引擎），分区表的索引只是在各个底层表上各自加上一个相同的索引，从存储引擎的角度来看，底层表和一个普通表没有任何不同，存储引擎也无须知道这是一个普通表还是一个分区表的一部分。

在分区表上的操作按照下面的操作逻辑进行：

select查询：

当查询一个分区表的时候，分区层先打开并锁住所有的底层表，优化器判断是否可以过滤部分分区，然后再调用对应的存储引擎接口访问各个分区的数据

insert操作：

当写入一条记录时，分区层打开并锁住所有的底层表，然后确定哪个分区接受这条记录，再将记录写入对应的底层表

delete操作：

当删除一条记录时，分区层先打开并锁住所有的底层表，然后确定数据对应的分区，最后对相应底层表进行删除操作

update操作：

当更新一条数据时，分区层先打开并锁住所有的底层表，mysql先确定需要更新的记录在哪个分区，然后取出数据并更新，再判断更新后的数据应该放在哪个分区，然后对底层表进行写入操作，并对原数据所在的底层表进行删除操作

虽然每个操作都会打开并锁住所有的底层表，但这并不是说分区表在处理过程中是锁住全表的，如果存储引擎能够自己实现行级锁，如：innodb，则会在分区层释放对应的表锁，这个加锁和解锁过程与普通Innodb上的查询类似。

在下面的场景中，分区可以起到非常大的作用：

A：表非常大以至于无法全部都放在内存中，或者只在表的最后部分有热点数据，其他都是历史数据

B：分区表的数据更容易维护，如：想批量删除大量数据可以使用清除整个分区的方式。另外，还可以对一个独立分区进行优化、检查、修复等操作

C：分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备

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

E：如果需要，还可以备份和恢复独立的分区，这在非常大的数据集的场景下效果非常好

F：优化查询，在where字句中包含分区列时，可以只使用必要的分区来提高查询效率，同时在涉及sum()和count()这类聚合函数的查询时，可以在每个分区上面并行处理，最终只需要汇总所有分区得到的结果。

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

分区

分区就是将数据库或其构成元素划分为不同的独立部分

－－是一种预先组织表存储的方法

mysql支持水平分区

将特定表行分配为行的子集

分区的分布是跨物理存储进行的

－－根据用户在需要时设置的指定规则

－－每个分区存储为其自己的单元

数据的划分

－－根据分区功能将数据划分为子集

－－分区类型和表达式是表定义的一部分

－－表达式可以是整数或返回整数值的函数。

－－此值根据定义确定将每条记录存储在哪个分区中

1.primary key和unique key必须包含在分区key的一部分，否则在创建primary key和unique index时会报”ERROR 1503 (HY000)“

2.范围分区添加分区只能在最大值后面追加分区

3.所有分区的engine必须一样

4.范围分区分区字段：integer、数值表达式、日期列，日期函数表达式(如year(),to_days(),to_seconds(),unix_timestamp())

分表的几种方式：

1、mysql集群

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

2、自定义规则分表

分区的类型

range 根据属于指定范围的列值将行分配到分区

list 根据与离散值集之一匹配的列将行分配到分区

hash 基于由用户定义的表达式返回的值而选择的分区，对要插入表中的行的列值进行操作

key 与hash类似，不同之处在于仅提供要评估的一个或多个列

Composite（复合模式） –以上模式的组合使用

InnoDB foreign keys and MySQL partitioning are not compatible. Partitioned InnoDB tables cannot have foreign key references, nor can they have columns referenced by foreign keys. InnoDB tables which have or which are referenced by foreign keys cannot be partitioned.

分区的几种方式实践：

Range：

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

)；

List：

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

);

Hash：

create table hash(

a int(11),

b datetime

)partition by hash (YEAR(b)

partitions 4;

Key：

create table t_key(

a int(11),

b datetime)

partition by key (b)

partitions 4;

分区管理

新增分区

ALTER TABLE sale_data

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

删除分区

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

ALTER TABLE sale_data DROP PARTITION p201010;

分区的合并

下面的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)

);