教学之友,学习之友。

站长教学网

当前位置: 站长教学网 > 数据库 > MYSQL教程 >

mysql如何分区方法和效率问题解释

时间:2013-03-29 22:25来源:未知 作者:ken 点击:

先说说分区优点

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' //指定磁盘分区

(责任编辑:ken)
TAG标签: mysql 分区
顶一下
(0)
0%
踩一下
(1)
100%
------分隔线----------------------------
发表评论
请自觉遵守互联网相关的政策法规,严禁发布色情、暴力、反动的言论。
评价:
表情:
注册登录:不允许匿名留言,登录后留言无需输入验证码。
栏目列表
最新内容