MySQL中表分区技术详细解析
MySQL 分区技术(是mysql 5.1以版本后开始用->是甲骨文mysql技术团队维护人员以插件形式插入到mysql里面的技术)
1、概述
数据库单表到达一定量后,性能会有衰减,像mysql\sql 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)
总结:
分区表是在MySQL5.1中新增的的功能,截止到MySQL5.1.22-rc,分区技术并不很成熟,很多分区的维护和管理功能未实现。如,分区内数据存储空间的回收、分区的修复、分区的优化等,MySQL的分区可以用在可以按分区删除的表中,且对数据库的修改操作不大,且频繁按照分区字段进行查询的表中(如恶意代码中的统计表按天分区,经常按照时间进行查询、分组等,且可以按天删除分区)。此外,由于MySQL无全局索引只有分区索引,当一张有2个唯一索引[z5] 的时候,不能将此表分区,分区列中必须包含主键。否则MySQL会报错。
总之,MySQL对于分区的限制很多,且个人认为hash和key的分区实际意义不是太大。
分区引入了一种新的优化查询的方式(当然,也有相应的缺点)。优化器可以使用分区函数修整分区,或者把分区从查询中完全移除掉。它通过推断是否可以在特定的分区上找到数据来达成这种优化。因此在最好的情况下,修整可以让查询访问更少的数据。重要的是要在WHERE子句中定义分区键,即使它看上去像是多余的。通过分区键,优化器就可以去掉不用的分区,否则的话,执行引擎就会像合并表那样访问表的所有分区,这在大表上会非常慢。分区数据比非分区数据更好维护,并且可以通过删除分区来移除老的数据。分区数据可以被分布到不同的物理位置,这样服务器可以更有效地使用多个硬盘驱动器。
[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]注意主键和唯一索引的区别
官方资料:https://dev.mysql.com/doc/refman/5.5/en/partitioning.html
相关文章
- 这篇文章主要介绍了MySQL性能监控软件Nagios的安装及配置教程,这里以CentOS操作系统为环境进行演示,需要的朋友可以参考下...2015-12-14
- 新版 Mysql 中加入了对 JSON Document 的支持,可以创建 JSON 类型的字段,并有一套函数支持对JSON的查询、修改等操作,下面就实际体验一下...2016-08-23
深入研究mysql中的varchar和limit(容易被忽略的知识)
为什么标题要起这个名字呢?commen sence指的是那些大家都应该知道的事情,但往往大家又会会略这些东西,或者对这些东西一知半解,今天我总结下自己在mysql中遇到的一些commen sense类型的问题。 ...2015-03-15- 这篇文章主要介绍了MySQL 字符串拆分操作(含分隔符的字符串截取),具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-02-22
- 一、先说一下为什么要分表:当一张的数据达到几百万时,你查询一次所花的时间会变多,如果有联合查询的话,有可能会死在那儿了。分表的目的就在于此,减小数据库的负担,缩短查询时间。根据个人经验,mysql执行一个sql的过程如下:1...2014-05-31
- 我们自己鼓捣mysql时,总免不了会遇到这个问题:插入中文字符出现乱码,虽然这是运维先给配好的环境,但是在自己机子上玩的时候咧,总得知道个一二吧,不然以后如何优雅的吹牛B。...2015-03-15
- 这几天在centos下装mysql,这里记录一下安装的过程,方便以后查阅Mysql5.5.37安装需要cmake,5.6版本开始都需要cmake来编译,5.5以后的版本应该也要装这个。安装cmake复制代码 代码如下: [root@local ~]# wget http://www.cm...2015-03-15
- 宿主机使用网线的时候,客户机在Bridged Adapter模式下,使用Atheros AR8131 PCI-E Gigabit Ethernet Controller上网没问题。 宿主机使用无线的时候,客户机在Bridged Adapter模式下,使用可选项里唯一一个WIFI选项,Microsoft Virtual Wifi Miniport Adapter也无法上网,故弃之。...2013-09-19
- 这篇文章主要介绍了node.js如何操作MySQL数据库,帮助大家更好的进行web开发,感兴趣的朋友可以了解下...2020-10-29
- 首先要声明一点,大部分情况下,修改MySQL密码是需要有mysql里的root权限的...2013-09-11
- MySQL命令行导出数据库: 1,进入MySQL目录下的bin文件夹:cd MySQL中到bin文件夹的目录 如我输入的命令行:cd C:/Program Files/MySQL/MySQL Server 4.1/bin (或者直接将windows的环境变量path中添加该目录) ...2013-09-26
- 一、连接Mysql格式: mysql -h主机地址 -u用户名 -p用户密码1、连接到本机上的MYSQL。首先打开DOS窗口,然后进入目录mysql/bin,再键入命令mysql -u root -p,回车后提示你输密码.注意用户名前可以有空格也可以没有空格,但是密...2015-11-08
- Navicat for MySQL注册码用来激活 Navicat for MySQL 软件,只要拥有 Navicat 注册码就能激活相应的 Navicat 产品。这篇文章主要介绍了Navicat for MySQL 11注册码\激活码汇总,需要的朋友可以参考下...2020-11-23
- 这篇文章主要介绍了mysql IS NULL使用索引案例讲解,本篇文章通过简要的案例,讲解了该项技术的了解与使用,以下就是详细内容,需要的朋友可以参考下...2021-08-14
- 一、准备编译环境,安装所需依赖包yum groupinstall 'Development' -y yum install openssl openssl-devel zlib zlib-devel -y yum install readline-devel pcre-devel ncurses-devel bison-devel cmake -y二、编译安...2015-10-21
- 这篇文章主要介绍了基于PostgreSQL和mysql数据类型对比兼容,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2020-12-25
Mysql中 show table status 获取表信息的方法
这篇文章主要介绍了Mysql中 show table status 获取表信息的方法的相关资料,需要的朋友可以参考下...2016-03-12- 这篇文章主要为大家分享了20分钟MySQL基础入门教程,快速掌握MySQL基础知识,真正了解MySQL,具有一定的参考价值,感兴趣的小伙伴们可以参考一下...2016-12-02
- 传统的关系数据库一般由数据库(database)、表(table)、记录(record)三个层次概念组成,MongoDB是由数据库(database)、集合(collection)、文档对象(document)三个层次组成。MongoDB对于关系型数据库里的表,但是集合中没有列、行和关...2013-09-11
- 这篇文章主要介绍了Delphi远程连接Mysql的实现方法,需要的朋友可以参考下...2020-06-30