当前位置:Gxlcms > mysql > MySQL5.1分区表学习笔记

MySQL5.1分区表学习笔记

时间:2021-07-01 10:21:17 帮助过:30人阅读

MySQL 从 5.1.x 的版本开始支持分区表,直到现在的最新版本 5.1.56 分区表已经比较成熟,并且该版本也是很稳定的 MySQL 版本。另

MySQL 从 5.1.x 的版本开始支持分区表,直到现在的最新版本 5.1.56 分区表已经比较成熟,并且该版本也是很稳定的 MySQL 版本。另外,MySQL 5.5开始支持RANGE COLUMNS和LIST COLUMNS的分区,也就是说非整型的列不再需要通过函数转化为整型,同时也可以对多个列进行分区。

由于分区功能并不是在存储引擎完成的,因此大部分常见的引擎都支持,例如 InnoDB、MyISAM 和 NDB 等,但 CSV、FEDERATED和MERGE等不支持。并且仅支持水平分区,不支持垂直分区。

分区表的优势可想而知,正如官方的参考手册中所提到的:与单个磁盘或文件系统分区相比,可以存储更多的数据;一些查询可以得到极大的优化,这主要是借助于满足一个给定WHERE 语句的数据可以只保存在一个或多个分区内,这样在查找时就不用查找其他剩余的分区;涉及到例如SUM() 和 COUNT()这样聚合函数的查询,可以很容易地进行并行处理;通过跨多个磁盘来分散数据查询,来获得更大的查询吞吐量等等。

MySQL 支持四种类型的分区:
1、RANGE 分区:基于属于一个给定连续区间的列值,把多行分配给分区;
2、LIST 分区:类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择;
3、HASH分区:基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算;
4、KEY 分区:类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL 服务器提供其自身的哈希函数。

MySQL 5.1 提供了许多修改分区表的方式。添加、删除、重新定义、合并或拆分已经存在的分区是可能的。所有这些操作都可以通过使用ALTER TABLE 命令的分区扩展来实现。关于如何添加和删除分区的处理,RANGE和LIST分区非常相似,,HASH和KEY分区也非常相似。基于这个原因,我们先介绍RANGE和HASH这两种分区的管理。

下面通过 RANGE 分区的实例操作学习分区表的所支持的操作,稍候将介绍 HASH 分区的实例操作:
首先,可以通过使用SHOW VARIABLES命令来确定MySQL是否支持分区(注意:mysql> 为提示符)
mysql> show variables like '%partition%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| have_partition_engine | YES |
+-----------------------+-------+
如果 value 值为 YES,则说明可以继续接下来的操作。

按照官方手册中提供的例子(稍有改动),创建 RANGE 类型的分区表:
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
PARTITION p0 VALUES LESS THAN (1),
PARTITION p1 VALUES LESS THAN (11),
PARTITION p2 VALUES LESS THAN (21)
)

增加分区,名称为 p3
mysql> alter table employees add partition ( partition p3 values less than (31));

删除名称为 p3 分区
mysql> alter table employees drop partition p3;

拆分名称为 p2 分区为 p2 p3 两个分区,注意被拆分的分区只能是分区表的最后一个分区
mysql> alter table employees reorganize partition p2 into (partition p2 values less than (21), partition p3 values less than (31));

合并 名称为 p2 p3 的两个分区为一个分区 p2 ,注意合并后分区 p2 的值不能小于原来 p3 分区的值
mysql> alter table employees reorganize partition p2,p3 into (partition p2 values less than (31));

注意:
1、如果不存在手工扩展分区的问题,可以使用 “VALUES LESS THAN MAXVALUE” 定义分区。
2、LIST分区没有类似如 “VALUES LESS THAN MAXVALUE” 这样的包含其他值在内的定义,将要匹配的任何值都必须在值列表中找到。
3、值为 NULL 的情况,如果是RANGE分区则MySQL 会将该值放到最左边的分区,因为 NULL 值被视为小于任何一个非 NULL 值得,这和 Oracle 刚好相反;如果是LIST分区则必须明确的指出哪个分区放 NULL 值。


再创建 HASH 分区表
CREATE TABLE employees2 (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4

增加分区数量
mysql> alter table employees2 add partition partitions 1;

减少分区数量
mysql> alter table employees2 coalesce partition 1;

注意:“ALTER TABLE ... REORGANIZE PARTITION”不能用于按照HASH或HASH分区的表。

同样,也可以优化上述的两张表
mysql> alter table employees rebuild partition p0,p1;

注意:“ALTER TABLE ... REORGANIZE PARTITION”也能让分区的数据文件重建。

查看 SQL 执行计划
mysql> explain partitions select * from employees;

同时,MySQL 也支持子分区,也可以每个RANGE分区的数据和索引都使用一个单独的磁盘。
CREATE TABLE employees3 (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL
)
PARTITION BY RANGE (YEAR(hired))
SUBPARTITION BY HASH(TO_DAYS(hired)) (
PARTITION p0 VALUES LESS THAN (2010) (
SUBPARTITION s0 DATA DIRECTORY = '/disk0/data' INDEX DIRECTORY = '/disk0/idx',
SUBPARTITION s1 DATA DIRECTORY = '/disk1/data' INDEX DIRECTORY = '/disk1/idx'
),
PARTITION p1 VALUES LESS THAN (2011) (
SUBPARTITION s2 DATA DIRECTORY = '/disk2/data' INDEX DIRECTORY = '/disk2/idx',
SUBPARTITION s3 DATA DIRECTORY = '/disk3/data' INDEX DIRECTORY = '/disk3/idx'
),
PARTITION p2 VALUES LESS THAN (2012) (
SUBPARTITION s4 DATA DIRECTORY = '/disk4/data' INDEX DIRECTORY = '/disk4/idx',
SUBPARTITION s5 DATA DIRECTORY = '/disk5/data' INDEX DIRECTORY = '/disk5/idx'
)
);
需要注意的是 InnoDB 存储引擎会忽略 DATA DIRECTORY 和 INDEX DIRECTORY语法,因此上述分区表的数据和索引文件分开放置是无效的。

详细请参考官方的手册。

linux

人气教程排行