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语法,因此上述分区表的数据和索引文件分开放置是无效的。
详细请参考官方的手册。
分享到:
相关推荐
详细介绍mysql5.1 分区技术,通过对list range hash key四种分区技术的举例阐述Mysql分区,18.2.1. RANGE分区 18.2.2. LIST分区 18.2.3. HASH分区 18.2.4. KEY分区 18.2.5. 子分区 18.2.6. MySQL分区处理NULL值的...
2. **分区功能增强**:在 MySQL 5.1 中,用户可以对大表进行分区,以提高查询性能。分区可以基于哈希、范围、列表或线性哈希等多种方式。 3. **复制功能优化**:MySQL 5.1 提供了半同步复制,确保主库上的事务在被...
MySQL 5.1是MySQL数据库管理系统的一个重要版本,它在5.0的基础上进行了多项改进和增强,为开发者提供了更高效...这个“mysql5.1详解简体中文版”的教程文件,将帮助读者深入理解这些概念,提升数据库管理和开发技能。
MYSQL5.1 ODBC驱动程序64位
这篇文档将基于提供的"mysql5.1CHM.zip"压缩包,深入探讨MySQL 5.1的主要特性和开发相关的知识。 1. **SQL语言与查询优化**: MySQL 5.1支持标准的SQL语法,包括SELECT、INSERT、UPDATE、DELETE等操作。其查询优化...
这份中文手册是英文原版的翻译版本,虽然可能不包含最新的更新,但对于学习和理解MySQL 5.1的基本概念、功能和操作方法,仍具有很高的参考价值。 手册内容通常包括以下几个主要部分: 1. **安装与升级**:这部分会...
此外,还增加了对分区表的支持,允许用户将大表分成多个逻辑部分,以便更快地处理大量数据。 在存储引擎方面,InnoDB引擎在MySQL 5.1中得到了强化。InnoDB是MySQL中最常用的事务处理引擎,支持ACID(原子性、一致性...
MySQL 5.1 ODBC 连接驱动是用于在Windows操作系统上通过Open Database Connectivity (ODBC) 接口与MySQL数据库进行交互的一种软件组件。ODBC是一种标准的应用程序编程接口(API),允许不同数据库系统之间的互操作性...
该文件里含有MySQL5.1 connector.exe和MySQL5.1 connector java 5.1.16 bin.jar。其中,MySQL5.1 connector.exe是MySQL与odbc的连接工具,MySQL5.1 connector java 5.1.16 bin.jar是MySQL进行jdbc连接时所需要的jar...
通过学习这个MySQL 5.1中文教程,你将能够熟练掌握数据库的安装配置、基本操作、SQL语言、索引创建、视图、存储过程、触发器、事务处理、备份恢复等核心知识,为你的数据库管理和开发工作打下坚实基础。记得实践是...
2. **分区功能**:MySQL 5.1引入了表分区,允许将大表划分为更小、更易管理的部分,从而提高查询性能和管理效率。 3. **触发器和存储过程**:MySQL 5.1支持触发器和存储过程,可以实现复杂的业务逻辑,减少应用程序...
MySQL 5.1驱动程序是Java应用程序连接到MySQL数据库的关键组件,它允许通过JDBC(Java Database Connectivity)接口与MySQL服务器进行通信。JDBC是Java平台上的标准API,用于访问各种类型的数据库。在这个特定的场景...
对于“可视化MySQL5.1中文版”标签,这意味着该版本提供了中文界面和文档,使得中国用户可以更方便地使用和理解MySQL的功能。可视化工具如phpMyAdmin、MySQL Workbench等可以帮助用户直观地管理数据库,创建、编辑和...
总的来说,这份“MYSQL5.1+简体中文参考文档”是学习和工作中不可或缺的工具,无论你是初学者还是经验丰富的DBA,都能从中获益良多。通过深入阅读和实践,你将能够熟练掌握MySQL 5.1的所有关键概念和操作,从而更好...
这是关于5.1版至5.1.2-alpha版MySQL数据库系统的参考手册. 本压缩包内共含有两个chm格式文件: MySQL 5.1中文参考手册(CHM).chm MySQL 5.1英文参考手册(CHM).chm 其中文为翻译版本,均来自互联网,为了节省大家的...
这是关于5.1版至5.1.2-alpha版MySQL数据库系统的参考手册。该手册不适用于旧版本MySQL软件,这是因为在MySQL 5.1和以前的版本存在很多功能性差异和其他差异。如果正在使用MySQL软件的较旧版本,请参阅MySQL 5.0参考...
总的来说,MySQL 5.1的源代码是一个深度学习数据库设计和实现的宝贵资料。它涵盖了数据库管理系统的各个层面,包括存储引擎、查询处理、事务处理、并发控制、复制和性能优化等。对于想要提升数据库知识或者进行...
这个官方文档的中文版确保了中文使用者能够无障碍地学习和理解MySQL5.1的各种特性和功能。 首先,文档会介绍MySQL的基础知识,包括安装与配置过程。这涉及到如何在不同的操作系统上下载和安装MySQL服务器,以及如何...
这份官方中文文档是学习和掌握MySQL 5.1的宝贵资源,涵盖了数据库管理员、开发人员和系统架构师所需的关键知识。 一、MySQL 5.1概述 MySQL 5.1在5.0的基础上增强了事务处理能力,支持ACID(原子性、一致性、隔离性...