一, 分区概念
分区允许根据指定的规则,跨文件系统分配单个表的多个部分。表的不同部分在不同的位置被存储为单独的表。MySQL从5.1.3开始支持Partition。
分区和手动分表对比
手动分表 分区多张数据表 一张数据表
重复数据的风险 没有数据重复的风险
写入多张表 写入一张表
没有统一的约束限制 强制的约束限制
MySQL支持RANGE,LIST,HASH,KEY分区类型,其中以RANGE最为常用:
Range(范围)–这种模式允许将数据划分不同范围。例如可以将一个表通过年份划分成若干个分区。 Hash(哈希)–这中模式允许通过对表的一个或多个列的Hash Key进行计算,最后通过这个Hash码不同数值对应的数据区域进行分区。例如可以建立一个对表主键进行分区的表。 Key(键值)-上面Hash模式的一种延伸,这里的Hash Key是MySQL系统产生的。 List(预定义列表)–这种模式允许系统通过预定义的列表的值来对数据进行分割。 Composite(复合模式) –以上模式的组合使用二,分区能做什么
逻辑数据分割 提高单一的写和读应用速度 提高分区范围读查询的速度 分割数据能够有多个不同的物理文件路径 高效的保存历史数据 一个表上的约束检查 不同的主从服务器分区策略,例如master按Hash分区,slave按range分区三,分区的限制(截止5.1.44版)
• 只能对数据表的整型列进行分区,或者数据列可以通过分区函数转化成整型列
• 最大分区数目不能超过1024
• 如果含有唯一索引或者主键,则分区列必须包含在所有的唯一索引或者主键在内
• 不支持外键
• 不支持全文索引(fulltext)
按日期进行分区很非常适合,因为很多日期函数可以用。但是对于字符串来说合适的分区函数不太多四,什么时候使用分区
• 海量数据表
• 历史表快速的查询,可以采用ARCHIVE+PARTITION的方式。
• 数据表索引大于服务器有效内存
• 对于大表,特别是索引远远大于服务器有效内存时,可以不用索引,此时分区效率会更有效。
五,分区实验
实验一:
使用 US Bureau of Transportation Statistics发布的数据(CSV格式).目前, 包括 1.13 亿条记录,7.5 GB数据5.2 GB索引。时间从1987到2007。
服务器使用4GB内存,这样数据和索引的大小都超过了内存大小。设置为4GB原因是数据仓库大小远远超过可能内存的大小,可能达几TB。对普通OLTP数据库来说,索引缓存在内存中,可以快速检索。如果数据超出内存大小,需要使用不同的方式。
创建有主键的表,因为通常表都会有主键。表的主键太大导致索引无法读入内存,这样一般来说不是高效的,意味着要经常访问磁盘,访问速度完全取决于你的磁盘和处理器。目前在设计很大的数据仓库里,有一种普遍的做法是不使用索引。所以也会比较有和没有主键的性能。
测试方法:
使用三种数据引擘MyISAM, InnoDB, Archive.
对于每一种引擘, 创建一个带主键的未分区表 (除了archive) 和两个分区表,一个按月一个按年。分区表分区方式如下:
CREATE TABLE by_year (
d DATE
)
PARTITION BY RANGE (YEAR(d))
(
PARTITION P1 VALUES LESS THAN (2001),
PARTITION P2 VALUES LESS THAN (2002),
PARTITION P3 VALUES LESS THAN (2003),
PARTITION P4 VALUES LESS THAN (MAXVALUE)
)
CREATE TABLE by_month (
d DATE
)
PARTITION BY RANGE (TO_DAYS(d))
(
PARTITION P1 VALUES LESS THAN (to_days(‘2001-02-01′)), — January
PARTITION P2 VALUES LESS THAN (to_days(‘2001-03-01′)), — February
PARTITION P3 VALUES LESS THAN (to_days(‘2001-04-01′)), — March
PARTITION P4 VALUES LESS THAN (MAXVALUE)
)
每一个都在 mysql服务器上的单独的实例上测试, 每实例只有一个库一个表。每种引擘, 都会启动服务, 运行查询并记录结果, 然后关闭服务。服务实例通过MySQL Sandbox创建。