先说说分区优点
1,分区可以分在多个磁盘,存储更大一点
2,根据查找条件,也就是where后面的条件,查找只查找相应的分区不用全部查找了
3,进行大数据搜索时可以进行并行处理。
4,跨多个磁盘来分散数据查询,来获得更大的查询吞吐量
可惜实际测试结果并非如此,实际应用根据应用需要分表可以达到比分区更好的效果,包括跨库,一样通过分表即可,无需分区。
一、什么是数据库分区
下面来说一下什么是数据库分区,以mysql为例。mysql数据库中的数据是以文件的形势存在磁盘上的,默认放在/mysql/data下面 (可以通过my.cnf中的datadir来查看),一张表主要对应着三个文件,一个是frm存放表结构的,一个是myd存放表数据的,一个是myi存表 索引的。如果一张表的数据量太大的话,那么myd,myi就会变的很大,查找数据就会变的很慢,这个时候我们可以利用mysql的分区功能,在物理上将这 一张表对应的三个文件,分割成许多个小块,这样呢,我们查找一条数据时,就不用全部查找了,只要知道这条数据在哪一块,然后在那一块找就行了。如果表的数 据太大,可能一个磁盘放不下,这个时候,我们可以把数据分配到不同的磁盘里面去。
分区的二种方式
1,横向分区
什么是横向分区呢?就是横着来分区了,举例来说明一下,假如有100W条数据,分成十 份,前10W条数据放到第一个分区,第二个10W条数据放到第二个分区,依此类推。也就是把表分成了十分,根用merge来分表,有点像哦。取出一条数据 的时候,这条数据包含了表结构中的所有字段,也就是说横向分区,并没有改变表的结构。
2,纵向分区
什么是纵向分区呢?就是竖来分区了,举例来说明,在设计用户表的时候,开始的时候没有考 虑好,而把个人的所有信息都放到了一张表里面去,这样这个表里面就会有比较大的字段,如个人简介,而这些简介呢,也许不会有好多人去看,所以等到有人要看 的时候,在去查找,分表的时候,可以把这样的大字段,分开来。
感觉数据库的分区好像是切苹果,到底是横着切呢,还是竖着切,根据个人喜好了,mysql提供的分区属于第一种,横向分区,并且细分成很多种方式。下面将举例说明一下。
二、mysql的分区
我觉着吧,mysql的分区只有一种方式,只不过运用不同的算法,規则将数据分配到不同的区块中而已。
MySQL分区表操作代码(本案例按月分区):
1. 创建分区表:
CREATE TABLE `表名` (
`EQUIPMENTID`char(17) NOT NULL,
`ATTRIBUTEID`char(4) NOT NULL,
`VALUE`varchar(20) NOT NULL,
`COLLECTTIME`datetime NOT NULL
) ENGINE=InnoDB(适用大部分引擎,可根据需要调整) DEFAULT CHARSET=latin1
PARTITION BY RANGE(to_days(`时间字段名`))
(PARTITION pminVALUES LESS THAN (to_days('2010-01-01')),
PARTITION p201001VALUES LESS THAN (to_days('2010-02-01')) ,
PARTITION p201002VALUES LESS THAN (to_days('2010-03-01')) ,
PARTITION p201003VALUES LESS THAN (to_days('2010-04-01')) ,
PARTITION p201004VALUES LESS THAN (to_days('2010-05-01')) ,
PARTITION p201005VALUES LESS THAN (to_days('2010-06-01')) ,
PARTITION p201006VALUES LESS THAN (to_days('2010-07-01')) ,
PARTITION p201007VALUES LESS THAN (to_days('2010-08-01')) ,
PARTITION p201008VALUES LESS THAN (to_days('2010-09-01')) ,
PARTITION p201009VALUES LESS THAN (to_days('2010-10-01')) ,
PARTITION p201010VALUES LESS THAN (to_days('2010-11-01')),
PARTITION p201011VALUES LESS THAN (to_days('2010-12-01')),
PARTITION p201012VALUES LESS THAN (to_days('2011-01-01')),
PARTITION pmax VALUESLESS THAN MAXVALUE );
2. 为现有表创建分区:
alter table 表名
PARTITION BY RANGE(to_days(`时间字段名`))
(PARTITION pminVALUES LESS THAN (to_days('2010-01-01')),
PARTITION p201001VALUES LESS THAN (to_days('2010-02-01')) ,
PARTITION p201002VALUES LESS THAN (to_days('2010-03-01')) ,
PARTITION p201003VALUES LESS THAN (to_days('2010-04-01')) ,
PARTITION p201004 VALUESLESS THAN (to_days('2010-05-01')) ,
PARTITION p201005VALUES LESS THAN (to_days('2010-06-01')) ,
PARTITION p201006VALUES LESS THAN (to_days('2010-07-01')) ,
PARTITION p201007VALUES LESS THAN (to_days('2010-08-01')) ,
PARTITION p201008VALUES LESS THAN (to_days('2010-09-01')) ,
PARTITION p201009VALUES LESS THAN (to_days('2010-10-01')) ,
PARTITION p201010VALUES LESS THAN (to_days('2010-11-01')),
PARTITION p201011VALUES LESS THAN (to_days('2010-12-01')),
PARTITION p201012VALUES LESS THAN (to_days('2011-01-01')),
PARTITION pmax VALUESLESS THAN MAXVALUE );
3. 删除表中的指定分区(删除分区会导致分区数据丢失,建议先备份):
ALTERTABLE 表名DROP PARTITION p0;
4. 追加表分区
需要先删除MAXVALUE分区后增加分区后再重建MAXVALUE分区,删除前需要先备份MAXVALUE分区数据.
ALTER TABLE 表名 DROPPARTITION pmax;
ALTER TABLE表名
ADD PARTITION (
PARTITION p201201VALUES LESS THAN (to_days('2012-2-1')),
PARTITION pmax VALUESLESS THAN MAXVALUE);
5. 查看标分区信息
SELECT
partition_namepart,
partition_expressionexpr,
partition_descriptiondescr,
table_rows
FROM
INFORMATION_SCHEMA.partitions
WHERE
TABLE_SCHEMA= schema()
AND
TABLE_NAME='表名';
6. 查看查询语句涉及分区信息
explainpartitions
select …from 表名 where …;
性能对比:
1. 测试环境
CPU: Intel 奔腾双核 E5300
硬盘: 西数(320GB/7200/16M 蓝盘)
内存: 南亚易胜 DDR2 800MHz 1GB + 三星 DDR2 800MHz 1GB
操作系统:Windows XP
MySQL版本: 5.1.57(5.1+版本支持分区表)
2. 表信息
表结构:
名 |
类型 |
长度 |
|
EQUIPMENTID |
char |
17 |
主键1 |
ATTRIBUTEID |
char |
4 |
主键2 |
VALUE |
varchar |
20 |
|
COLLECTTIME |
datetime |
|
主键3 |
总记录数:580W
分区信息(红色为主要测试区域):
part |
expr |
descr |
table_rows |
pmin |
to_days(COLLECTTIME) |
734138 |
2686 |
p201001 |
to_days(COLLECTTIME) |
734169 |
2511883 |
p201002 |
to_days(COLLECTTIME) |
734197 |
192497 |
p201003 |
to_days(COLLECTTIME) |
734228 |
811103 |
p201004 |
to_days(COLLECTTIME) |
734258 |
82894 |
p201005 |
to_days(COLLECTTIME) |
734289 |
109297 |
p201006 |
to_days(COLLECTTIME) |
734319 |
555065 |
p201007 |
to_days(COLLECTTIME) |
734350 |
742949 |
p201008 |
to_days(COLLECTTIME) |
734381 |
525900 |
p201009 |
to_days(COLLECTTIME) |
734411 |
89 |
p201010 |
to_days(COLLECTTIME) |
734442 |
71665 |
p201011 |
to_days(COLLECTTIME) |
734472 |
85964 |
p201012 |
to_days(COLLECTTIME) |
734503 |
1612 |
p201101 |
to_days(COLLECTTIME) |
734534 |
176 |
p201102 |
to_days(COLLECTTIME) |
734562 |
253 |
p201103 |
to_days(COLLECTTIME) |
734593 |
44824 |
p201104 |
to_days(COLLECTTIME) |
734623 |
62324 |
p201105 |
to_days(COLLECTTIME) |
734654 |
50658 |
p201106 |
to_days(COLLECTTIME) |
734684 |
0 |
p201107 |
to_days(COLLECTTIME) |
734715 |
0 |
p201108 |
to_days(COLLECTTIME) |
734746 |
0 |
p201109 |
to_days(COLLECTTIME) |
734776 |
0 |
p201110 |
to_days(COLLECTTIME) |
734807 |
0 |
p201111 |
to_days(COLLECTTIME) |
734837 |
0 |
p201112 |
to_days(COLLECTTIME) |
734868 |
0 |
p201201 |
to_days(COLLECTTIME) |
734899 |
0 |
p201202 |
to_days(COLLECTTIME) |
734928 |
0 |
pmax |
to_days(COLLECTTIME) |
MAXVALUE |
921 |
3. 查询效率对比
对比表:无分区表名nopart_data,有分区表名part_data
查询条件:select count(*) from 表名 where COLLECTTIME > 起始时间 and COLLECTTIME < 终止时间
查询耗时按照3次平均值统计
统计表:教学网 eduyo.com
开始时间 |
结束时间 |
查询结果 |
无分区耗时 |
有分区耗时 |
涉及分区 |
全部 |
5848859 |
6.26s |
9.58s |
全部 |
|
2010-5-1 |
2010-6-1 |
109086 |
7.04s |
0.48s |
pmin,p201005 |
2010-6-1 |
2010-7-1 |
554695 |
8.34s |
0.38s |
pmin,p201006 |
2010-7-1 |
2010-8-1 |
742565 |
7.57s |
0.43s |
pmin,p201007 |
2010-5-1 |
2010-7-1 |
663781 |
7.07s |
0.51s |
pmin,p201005,p201006 |
2010-6-1 |
2010-8-1 |
1297260 |
6.84s |
1.93s |
pmin,p201006,p201007 |
2010-5-1 |
2010-8-1 |
1406346 |
6.97s |
2.30s |
pmin,p201006,p201007,p201008 |
小结:
1) 分区表查询在查询上有明显优势.但在跨区查询时会有查询时间消耗,因此需要注意分区的疏密程度.
2) 每次查询都会查询pmin(第一个分区),因此需要尽量减少这个分区的数据.
4. 写入数据效率对比
COLLECTTIME |
无分区耗时 |
有分区耗时 |
2010-5-22 15:36 |
0.05s |
0.03s |
2010-6-22 15:36 |
0.02s |
0.05s |
2010-7-22 15:36 |
0.03s |
0.03s |
小结:
分区对单条数据的插入操作无较大影响.
大表进行分区后,查询效率有所降低,
分区过程出现错误提示:
ERROR 1503 A PRIMARY KEY must include all columns in the table's partitioning function
默認分區限制分區字段必須是主鍵(PRIMARY KEY)的一部分,為了去除此限制:
PARTITION p1 VALUES LESS THAN (6000000)
DATA DIRECTORY = '/data2/data' //指定磁盘分区
INDEX DIRECTORY = '/data3/idx' //指定磁盘分区