mysql表分区技术详细介绍

1、概述
数据库单表到达一定量后，性能会有衰减，像mysqlsql server等犹为明显，所以需要把这些数据进行分区处理。同时有时候可能出现数据剥离什么的，分区表就更有用处了！
MySQL 5.1 中新增的分区(Partition)功能就开始增加，优势也越来越明显了：
–与单个磁盘或文件系统分区相比，可以存储更多的数据
–很容易就能删除不用或者过时的数据
–一些查询可以得到极大的优化
–涉及到 SUM()/COUNT() 等聚合函数时，可以并行进行
–IO吞吐量更大
–分区允许可以设置为任意大小的规则，跨文件系统分配单个表的多个部分。实际上，表的不同部分在不同的位置被存储为单独的表。

2、分区技术支持
在5.6之前，使用这个参数查看当将配置是否支持分区
mysql> SHOW VARIABLES LIKE ‘%partition%’;
+———————–+——-+
|Variable_name | Value |
+———————–+——-+
| have_partition_engine 开发云主机域名| YES |
+———————–+——-+
如果是yes表示你当前的配置支持分区
在5.6及以采用后，则采用如下方式进行查看
mysql> SHOW PLUGINS;
+—————————-+———-+——————–+———+———+
| Name | Status | Type | Library | License |
+—————————-+———-+——————–+———+———+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
……………………………………………………………………….
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
+—————————-+———-+——————–+———+———+
42 rows in set (0.00 sec)
最后一行，可以看到partition是ACTIVE的，表示支持分区

3、分区类型及举例
3.1范围分区
RANGE 分区：基于属于一个给定连续区间的列值，把多行分配给分区。如时间，连续的常量值等
–按年分区
mysql> use mytest;
Database changed
mysql> create table range_p(
-> perid int(11),
-> pername char(12) not null,
-> monsalary DECIMAL(10,2),
-> credate datetime
-> ) partition by range(year(credate))(
-> partition p2011 values less than (2011),
-> partition p2012 values less than (2012),
-> partition p2013 values less than (2013),
-> partition p2014 values less than (2014),
-> partition p2015 values less than maxvalue
-> );
Query OK, 0 rows affected (0.12 sec)

3.2列举分区
LIST 分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。比如说类似性别(1,2)等属性值。
mysql> create table list_p(
-> perid int(11),
-> pername char(12) not null,
-> sex int(1) not null,
-> monsalary DECIMAL(10,2),
-> credate datetime
-> ) partition by list(sex) (
-> partition psex1 values in(1),
-> partition psex2 values in(2));
Query OK, 0 rows affected (0.06 sec)

注意，list只能是数字，使用字符会报错ERROR 1697 (HY000): VALUES value for partition ‘psex1’ must have type INT

3.3离散分区
HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包>含MySQL中有效的、产生非负整数值的任何表达式。
–以int字段hash分区
create table hash_p(
perid int(11),
pername char(12) not null,
sex int(1) not null,
monsalary DECIMAL(10,2),
credate datetime
) partition by hash (perid)
partitions 8;
–以时间函数hash分区
mysql> create table hash_p(
-> perid int(11),
-> pername char(12) not null,
-> sex int(1) not null,
-> monsalary DECIMAL(10,2),
-> credate datetime
-> ) partition by hash (year(credate))
-> partitions 8;
Query OK, 0 rows affected (0.11 sec)

3.4键值分区
KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含>整数值。
其分区方法与hash很相似
mysql> create table key_p(
-> perid int(11),
-> pername char(12) not null,
-> sex int(1) not null,
-> monsalary DECIMAL(10,2),
-> credate datetime
-> ) partition by key (perid)
-> partitions 8;
Query OK, 0 rows affected (0.12 sec)

3.5其它说明
mysql-5.5开始支持COLUMNS分区，可视为RANGE和LIST分区的进化，COLUMNS分区可以直接使用非整形数据进行分区。COLUMNS分区支持以下数据类型：
所有整形，如INT SMALLINT TINYINT BIGINT。FLOAT和DECIMAL则不支持。
日期类型，如DATE和DATETIME。其余日期类型不支持。
字符串类型，如CHAR、VARCHAR、BINARY和VARBINARY。BLOB和TEXT类型不支持。
COLUMNS可以使用多个列进行分区。

mysql> create table range_p(
-> perid int(11),
-> pername char(12) not null,
-> monsalary DECIMAL(10,2),
-> credate datetime
-> ) PARTITION BY RANGE COLUMNS (credate)(
-> partition p20151 values less than (‘2015-04-01’),
-> partition p20152 values less than (‘2015-07-01’),
-> partition p20153 values less than (‘2015-10-01’),
-> partition p20154 values less than (‘2016-01-01’),
-> partition p20161 values less than (‘2016-04-01’),
-> partition partlog values less than maxvalue
-> );
Query OK, 0 rows affected (0.12 sec)

4、综述
总体说来，mysql的分区还在进步，需要进一步的加强，与oracle相比，还有差距！比如说基于非数据类型的分区技术，目前还没有！

相关推荐: 测服务响应时间的工具tcprstat

tcprstat是percona开源的一款测试mysql服务响应时间的工具，不过对于任何运行在TCP协议上的响应时间，都可以用，只需要指定对应的端口即可。详情可参考percona官方文档https://www.percona.com/docs/wiki/tcp…