Mysql必读MySQL中表分区技术详细解析

《Mysql必读MySQL中表分区技术详细解析》要点：
本文介绍了Mysql必读MySQL中表分区技术详细解析，希望对您有用。如果有疑问，可以联系我们。

MYSQL学习MySQL 分区技术(是mysql 5.1以版本后开始用->是甲骨文mysql技术团队维护人员以插件形式插入到mysql里面的技术)

MYSQL学习1、概述

MYSQL学习数据库单表到达必定量后,性能会有衰减,像mysqlsql server等犹为明显,所以需要把这些数据进行分区处理.同时有时候可能出现数据剥离什么的,分区表就更有用处了!

MYSQL学习MySQL 5.1 中新增的分区(Partition)功能就开始增加,优势也越来越明显了：

1. 与单个磁盘或文件系统分区相比,可以存储更多的数据
2. 很容易就能删除不用或者过时的数据
3. 一些查询可以得到极大的优化
4. 涉及到 SUM()/COUNT() 等聚合函数时,可以并行进行
5. IO吞吐量更大
6. 分区允许可以设置为任意大小的规则,跨文件系统分配单个表的多个部分.实际上,表的分歧部分在分歧的位置被存储为单独的表.
   

MYSQL学习2、分区技术支持

MYSQL学习在5.6之前,使用这个参数查看当将配置是否支持分区：

MYSQL学习
mysql> SHOW VARIABLES LIKE '%partition%';
+-----------------------+-------+
|Variable_name     | Value |
+-----------------------+-------+
| have_partition_engine | YES  |
+-----------------------+-------+

MYSQL学习如果是yes表现你当前的配置支持分区. 在5.6及以采用后,则采用如下方式进行查看：

MYSQL学习
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   |
+----------------------------+----------+--------------------+---------+---------+

MYSQL学习42 rows in set (0.00 sec) 最后一行,可以看到partition是ACTIVE的,表现支持分区.

MYSQL学习3、分区类型及举例

MYSQL学习3.1范围分区

MYSQL学习RANGE 分区：基于属于一个给定连续区间的列值,把多行分配给分区.如时间,连续的常量值等 --按年分区

MYSQL学习
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)

MYSQL学习3.2列举分区

MYSQL学习LIST 分区：类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择.好比说类似性别(1,2)等属性值.

MYSQL学习
mysql> create table list_p( ?  -> perid int(11),?  -> pername char(12) not null,?  -> sex int(1) not null,?  -> monsalary DECIMAL(10,?  -> credate datetime ?  -> ) partition by list(sex) (?  -> partition psex1 values in(1),?  -> partition psex2 values in(2));?Query OK,0 rows affected (0.06 sec)

MYSQL学习注意,list只能是数字,使用字符会报错ERROR 1697 (HY000): VALUES value for partition 'psex1' must have type INT.

MYSQL学习3.3离散分区

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

MYSQL学习--以int字段hash分区

MYSQL学习
create table hash_p( 
perid int(11),pername char(12) not null,sex int(1) not null,monsalary DECIMAL(10,credate datetime 
) partition by hash (perid) 
partitions 8;

MYSQL学习--以时间函数hash分区

MYSQL学习
mysql> create table hash_p( 
  -> perid int(11),-> sex int(1) not null,-> credate datetime 
  -> ) partition by hash (year(credate)) 
  -> partitions 8;
Query OK,0 rows affected (0.11 sec)

MYSQL学习3.4键值分区

MYSQL学习KEY分区：类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL服务器提供其自身的哈希函数.必须有一列或多列包含>整数值. 其分区办法与hash很相似：

MYSQL学习
mysql> create table key_p( 
  -> perid int(11),-> credate datetime 
  -> ) partition by key (perid) 
  -> partitions 8;
Query OK,0 rows affected (0.12 sec)

MYSQL学习3.5其它说明

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

MYSQL学习
mysql> create table range_p( 
  -> perid int(11),-> 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)

MYSQL学习总结：

MYSQL学习分区表是在MySQL5.1中新增的的功能,截止到MySQL5.1.22-rc,分区技术并不很成熟,很多分区的维护和管理功能未实现.如,分区内数据存储空间的回收、分区的修复、分区的优化等,MySQL的分区可以用在可以按分区删除的表中,且对数据库的修改操作不大,且频繁依照分区字段进行查询的表中(如恶意代码中的统计表按天分区,经常依照时间进行查询、分组等,且可以按天删除分区).此外,由于MySQL无全局索引只有分区索引,当一张有2个唯一索引[z5] 的时候,不能将此表分区,分区列中必须包含主键.否则MySQL会报错.

MYSQL学习总之,MySQL对于分区的限制很多,且个人认为hash和key的分区实际意义不是太大.

MYSQL学习分区引入了一种新的优化查询的方式(当然,也有相应的缺点).优化器可以使用分区函数修整分区,或者把分区从查询中完全移除掉.它通过推断是否可以在特定的分区上找到数据来达成这种优化.因此在最好的情况下,修整可以让查询拜访更少的数据.重要的是要在WHERE子句中定义分区键,即使它看上去像是多余的.通过分区键,优化器就可以去掉不用的分区,否则的话,执行引擎就会像合并表那样拜访表的所有分区,这在大表上会非常慢.分区数据比非分区数据更好维护,并且可以通过删除分区来移除老的数据.分区数据可以被分布到不同的物理位置,这样服务器可以更有效地使用多个硬盘驱动器.

MYSQL学习[z1]分区函数的返回值必须是整数,新增分区的分区函数返回值应大于任何一个现有分区的分区函数的返回值.
?[z2]对于有主键的表错误提示：#1503
?A PRIMARY KEY MUST INCLUDE ALL COLUMNS INTHE TABLE'S PARTITIONING FUNCTION,没有主键的则无此约束
?[z3]注意：对于通过RANGE分区的表,只可以使用ADD PARTITION添加新的分区到分区列表的高端.即不能添加比这个分区的范围小的分区.
?[z4] 对于依照RANGE分区的表,只能重新组织相邻的分区；不能跳过RANGE分区.不能使用REORGANIZEPARTITION来改变表的分区类型；也就是说,例如,不能把RANGE分区变为HASH分区,反之亦然.也不能使用该命令来改变分区表达式或列.
?[z5]注意主键和唯一索引的区别

MYSQL学习官方材料：https://dev.mysql.com/doc/refman/5.5/en/partitioning.html

《Mysql必读MySQL中表分区技术详细解析》是否对您有启发，欢迎查看更多与《Mysql必读MySQL中表分区技术详细解析》相关教程，学精学透。编程之家PHP学院为您提供精彩教程。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!