随着数据库应用规模的扩展,需要管理的数据规模也越来越大,普通的数据库查询优化机制在某些情况下已不能再满足性能的要求了。利用数据库分区技术,可以有效地减少 I/O 的数量,提升系统的查询性能。
1.什么是分区?
数据库分区是一种物理数据库设计技术,DBA 和数据库建模人员对其相当熟悉。虽然分区技术可以实现很多效果,但其主要目的是在特定的 SQL 操作中减少数据库读/写的总量以缩减响应的时间。
分区(Partitioning)是数据库优化的一种方式。简单来说就是将同一数据表中的记录通过特定的算法进行分离,分别保存在不同的数据表中,甚至可以部署在不同的物理磁盘上,分散对同一张数据表的操作,提高访问的速度。
举个例子:db_blog 数据库存储了用户发表的博客内容,主要数据都在tb1_posts表中,它的结构如下所示:
我们按照 user_id%10 的规则将它分以下为10个数据表:
tb1_posts_0
tb1_posts_1
tb1_posts_2
tb1_posts_3
tb1_posts_4
tb1_posts_5
tb1_posts_6
tb1_posts_7
tb1_posts_8
tb1_posts_9
这样,当tb1_posts表的记录数目是上百万级时,它实际上是分布在10个分区表中,每一张表的记录数保持在相对较少的数量,有利于减少查询的时间,避免对同一张表的频繁读写,从而为数据库减少不必要的开销。
|
数据表分布在不同的物理硬盘上(水平分区) |
分区主要有两种形式:水平分区与垂直分区。
(1)水平分区
水平分区(Horizontal Partitioning)是对表的行进行分区,通过这样的方式使得不同分组里面的物理列分割的数据集得以组合,从而进行个体分割(单分区)或集体分割(一个或多个分区)。所有在表中定义的列在每个数据集中都能找到,所以表的特性依然得以保持。例如,一个包含10年发票记录的表可以分为10个不同的分区,每个分区都包含其中一年的记录(这里具体使用的分区方式一定要通过某个属性列来分割,譬如这里使用的列是年份)。
(2)垂直分区
垂直分区(Vertical Partitioning)是通过对表的垂直划分来减少目标表的宽度,使某些特定的列被划分到特定的分区,每个分区都包含了其中的列所对应的行。例如,一个包含了大 TEXT 和 BLOB 列的表,因为这些TEXT和BLOB列不经常被访问,所以这时就要把这些不经常使用的 TEXT 和 BLOB 列划分到另一个分区,以保证它们在数据库相关的同时还能提高访问速度。
2.分区类型
- RANGE分区(RANGE Partitioning)
这种算法是指按照分区索引字段的范围进行分区,比如我们可以将user_id为1~10000的记录存储在一个分区中,而将10001~20000的用户存储在另一个分区中,以此类推。
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 (6),
PARTITION p1 VALUES LESS THAN (11),
PARTITION p2 VALUES LESS THAN (16),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
- List分区(LIST Partitioning)
假设有20个音像店,分布在4个有经销权的地区,如下表所示:
地区 | 商店ID 号 |
北区 | 3, 5, 6, 9, 17 |
东区 | 1, 2, 10, 11, 19, 20 |
西区 | 4, 12, 13, 14, 18 |
中心区 | 7, 8, 15, 16 |
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,
store_id INT
)
PARTITION BY LIST(store_id) (
PARTITION pNorth VALUES IN (3,5,6,9,17),--数据和索引放在默认的位置
PARTITION pEast VALUES IN (1,2,10,11,19,20)
DATA DIRECTORY = '/disk0/data'
INDEX DIRECTORY = '/disk0/idx',--把数据和索引放在不同的磁盘上
PARTITION pWest VALUES IN (4,12,13,14,18)
DATA DIRECTORY = '/disk1/data'
INDEX DIRECTORY = '/disk1/idx',--当然,也可以是不同的物理硬盘上,如果有多个物理硬盘的话
PARTITION pCentral VALUES IN (7,8,15,16)
DATA DIRECTORY = '/disk2/data'
INDEX DIRECTORY = '/disk2/idx'
);
- Hash分区(Hash Partitioning)
刚才我们通过 user_id%10 来实现分区便是这种算法,它非常容易实现。
例如,下面的语句创建了一个使用基于"store_id"列进行哈希处理的表,该表被分成了4个分区:
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, store_id INT ) PARTITION BY HASH(store_id) PARTITIONS 4;它有4个分区,MySQL将通过用户函数(HASH(store_id))返回的结果的模数来确定使用哪个编号(N)的分区来保存每条记录。在上例中,N 是这样计算的:
MOD(7,4) -- 7表示的是 store_id = MOD(7,4) -- 取模运算 = 3 -- 表示第4个分区
哈希函数适用只对单个表列进行计算,并且它的值随列值进行一致地增大或减小,表达式值和它所基于的列的值变化越接近,MySQL就可以越有效地使用该表达式来进行HASH分区。
换句话说,如果列值与表达式值之比的曲线图越接近由等式"y=nx"(其中n为非零的常数)描绘出的直线,则该表达式越适合于哈希。这是因为,表达式的非线性越严重,分区中数据产生非均衡分布的趋势也将越严重。
LINEAR HASH分区(LINEAR HASH Partitioning)
线性哈希功能,它与常规哈希的区别在于,线性哈希功能使用的一个线性的2的幂(powers-of-two)运算法则,而常规哈希使用的是求哈希函数值的模数。按照线性哈希分区的优点在于增加、删除、合并和拆分分区将变得更加快捷,有利于处理含有极其大量(terabytes)数据的表。它的缺点在于,与使用常规HASH分区得到的数据分布相比,各个分区间数据的分布不大可能均衡。
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, store_id INT ) PARTITION BY LINEAR HASH( YEAR(hired) ) PARTITIONS 4;
通过以上可知,分区通过设置任意大小的规则,跨文件系统分配单个表的多个部分。实际上,表的不同部分在不同的位置被存储为单独的表。用户所选择的、实现数据分割的规则被称为分区函数。在MySQL中它可以是模数,或者是简单的匹配一个连续的数值区间或数值列表,或者是一个内部HASH函数,或一个线性HASH函数。函数根据用户指定的分区类型来选择,把用户提供的表达式的值作为参数。该表达式可以是一个整数列值,或一个作用在一个或多个列值上并返回一个整数的函数。这个表达式的值传递给分区函数,分区函数返回一个表示那个特定记录应该保存在哪个分区的序号。
3.分区的约束和限制
InnoDB
存储引擎,那么 DATA DIRECTORY
(数据目录)和 INDEX DIRECTORY
(索引目录)配置选项无效。
InnoDB
存储引擎还是 MyISAM
存储引擎, DATA DIRECTORY
(数据目录)和 INDEX DIRECTORY
(索引目录)配置选项也是无效的。
很多时候,使用分区就不要再使用主键,否则可能影响性能。
只能通过 int 类型的字段或者返回 int 类型的表达式来分区。
每个表最多 1024 个分区,不可能无限制扩展分区,而且过度使用分区往往会消耗大量系统内存。
采用分区的表不支持外键,相关的约束逻辑必须通过程序来实现。
分区后,可能会造成索引失效,需要验证分区可行性。
相关推荐
MySQL 5.5版本的驱动是Java应用程序与MySQL数据库交互的关键组件,主要通过JDBC(Java Database Connectivity)接口实现。JDBC是Java平台上的标准,它允许Java开发者使用SQL语句来访问和处理数据库。MySQL的JDBC驱动...
MySQL5.5绿色版是一款无需安装的数据库管理系统,特别适合于对系统资源有限制或者希望快速部署MySQL环境的用户。它的大小约为10MB,这使得它非常轻巧,易于在各种设备上进行快速下载和使用。解压缩后,用户可以直接...
MySQL 5.5是MySQL数据库管理系统的一个重要版本,它提供了许多增强的功能和性能优化,使得这个版本成为当时很多企业和开发者的首选。在这个压缩包中,包含的是MySQL 5.5的Windows 32位安装程序以及一个图形化界面...
这份"MySQL5.5.zip"压缩包包含了一个名为"mysql 5.5 chm 中文手册"的文件,这是一个CHM(Compiled HTML Help)格式的文档,专门针对MySQL 5.5版本,对于学习和理解MySQL 5.5的各种特性和操作非常有帮助。 MySQL 5.5...
java连接mysql5.5的jar包 mysql-connector-java-5.1.35-bin.jar
MySQL 5.5 版本引入了一些性能优化特性,例如 InnoDB 存储引擎的改进,支持更多的并行查询,更高效的查询缓存,以及对分区表的支持等。这些都使得 MySQL 5.5 在处理大数据量时表现更佳。 6. **安全性**: 安装...
标题中的"MySQL安装文件MySQL5.5.zip"指的是包含MySQL 5.5版本安装程序的压缩包。这个压缩文件通常包含了安装MySQL所需的所有组件,包括服务器、客户端工具、连接器以及相关的文档。"mysql-5.5.21.msi"是MySQL ...
MYSQL5.5英文手册,MYSQL5.5手册,MYSQL英文手册,MYSQL手册
MySQL5.5中文版是MySQL数据库管理系统的一个重要版本,它为用户提供了稳定、高效和功能丰富的数据存储解决方案。MySQL是一个开源的关系型数据库管理系统(RDBMS),由瑞典的MySQL AB公司开发,后来被甲骨文公司收购...
MySQL 5.5中文参考手册.pdf MySQL 5.5中文参考手册是 MySQL 官方提供的中文参考手册,涵盖 MySQL 5.5 至 5.5.2-alpha 版本的所有功能和特性。本手册由 MySQL 合作伙伴 GreatLinux 翻译,提供了详细的 MySQL 参考...
MySQL 5.5是MySQL数据库管理系统的一个重要版本,它在2010年发布,针对性能、稳定性以及功能进行了多项优化。本安装包是专为Windows操作系统设计的,支持32位(x86)和64位(x64)架构。MySQL 5.5在Windows平台上的...
本文将详细介绍如何安装MySQL 5.5,这是MySQL的一个稳定版本,提供了强大的数据存储和管理功能。 首先,我们来理解一下MySQL 5.5的主要特点: 1. **更高的性能**:MySQL 5.5通过InnoDB存储引擎的优化,提升了查询...
- MySQL5.5扩展了分区功能,允许用户将大表分割为更小、更易管理的部分,从而提高查询性能和维护效率。 - 支持多种分区类型,如范围分区、列表分区、哈希分区和线性分区。 5. **Full-text Search**: - 全文搜索...
MySQL 5.5是MySQL数据库管理系统的一个重要版本,它在2010年发布,带来了许多性能提升和新特性。`.tar.gz`是Linux和Unix系统中常用的文件压缩格式,用于将多个文件打包成一个单一的压缩文件,方便存储和传输。在本...
3. **分区功能**:MySQL5.5扩展了分区功能,允许用户在更大规模的数据上进行操作。分区可以提高查询性能,尤其在处理大数据集时。 4. **并行复制**:虽然MySQL5.5的复制功能没有达到后来版本的多线程复制,但它已经...
4. **分区表的改进**:MySQL5.5对分区表的支持更加强大,包括更多的分区类型和更灵活的分区策略,可以更好地处理大规模数据。 5. **复制功能强化**:增加了半同步复制(Semi-Synchronous Replication)选项,确保主...
MySQL 5.5是MySQL数据库管理系统的一个重要版本,它在2010年发布,带来了许多性能提升和功能增强。这个一键安装包旨在简化安装过程,让用户能够快速、简便地在计算机上设置MySQL服务器。 首先,MySQL 5.5引入了...
MySQL 5.5是MySQL数据库管理系统的一个重要版本,它在2010年发布,带来了许多性能提升和新特性。MySQL是一种开源、免费的关系型数据库管理系统(RDBMS),广泛应用于网页应用程序、企业级系统以及大数据处理等领域。...
mysql5.5升级的所有包。 1、安装MySQL 5.5.x的yum源: rpm -Uvh http://repo.webtatic.com/yum/centos/5/latest.rpm 2、安装MySQL客户端的支持包: yum install libmysqlclient15 --enablerepo=webtatic 3、卸载...
MySQL 5.5 是 MySQL 数据库的一个重要版本,它在功能和性能上都有显著的提升。配合图形界面工具,MySQL 5.5 可以提供更直观、便捷的数据库管理和开发体验。以下是一些关于 MySQL 5.5 和图形界面工具的关键知识点: ...