MySQL 分区的使用总结

为什么要分表和分区

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

什么是分表

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

什么是分区

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

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

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

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

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

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

介绍

分区是指根据一定的规则将一个大表分解成多个更小的部分，这里的规则一般就是利用分区规则将表进行水平切分；逻辑上没有发生变化但实际上表已经被拆分成了多个物理对象，每个分成被划分成了一个独立的对象。相对于没有分区的当个表而言分区的表有很多的优势包括： 并发统计查询、快速归档删除分区数据、分散存储、查询性能更佳。

mysql5.7以后查询语句支持指定分区例如：“ SELECT * FROM t PARTITION (p0,p1) WHERE c < 5 ”指定分区同样适用DELETE, INSERT, REPLACE, UPDATE, and LOAD DATA, LOAD XML.

数据库版本：mysql5.7.12

水平分区（根据列属性按行分）

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

水平分区的几种模式：

Range（范围） – 这种模式允许DBA将数据划分不同范围。例如DBA可以将一个表通过年份划分成三个分区，80年代（1980’s）的数据，90年代（1990’s）的数据以及任何在2000年（包括2000年）后的数据。

Hash（哈希） – 这中模式允许DBA通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区，。例如DBA可以建立一个对表主键进行分区的表。

Key（键值） – 上面Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的。

List（预定义列表） – 这种模式允许系统通过DBA定义的列表的值所对应的行数据进行分割。例如：DBA建立了一个横跨三个分区的表，分别根据2004年2005年和2006年值所对应的数据。

Composite（复合模式） - 很神秘吧，哈哈，其实是以上模式的组合使用而已，就不解释了。举例：在初始化已经进行了Range范围分区的表上，我们可以对其中一个分区再进行hash哈希分区。

是否支持分区

1
SHOW PLUGINS;
SHOW PLUGINS

查询partition的的状态是active就代表支持分区，如果是源码安装的话在编译的过程中要添加“-DWITH_PARTITION_STORAGE_ENGINE=1 \”。

注意： MERGE, CSV, or FEDERATED存储引擎不支持分区，同一个表所有的分区必须使用相同的存储引擎，不能分区1使用MYISAM分区2又使用INNODB；不同的分区表可以是不同的存储引擎。

删除增加分区

在每个分区内容介绍中详细介绍了每种分区的用法，但是都是介绍在创建表的时候创建分区和修改删除分区单个，也可以在一张已经存在的表中加入分区，可以一次性删除整个表的分区。

1.移除表的分区

1
2
ALTER TABLE tablename
REMOVE PARTITIONING;
注意：使用remove移除分区是仅仅移除分区的定义，并不会删除数据和drop PARTITION不一样，后者会连同数据一起删除

2.对已经存在记录的表创建分区，以增加range分区为例，和创建表建分区的语法一样。

ALTER TABLE tb_partition.tb_varchar
PARTITION BY RANGE(id) PARTITIONS 3(
PARTITION part0 VALUES LESS THAN (5000),
PARTITION part1 VALUES LESS THAN (10000),
PARTITION part2 VALUES LESS THAN (MAXVALUE)
);
注意：对已有的表创建分区之后，数据会按照分区的定义分布到各个分区文件当中