`
grzrt
  • 浏览: 183017 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

MySQL水平分区表初体验总结

 
阅读更多

本文总结个这段时间研究MySQL水平分区表总结,列举分区表的相关操作和通过实际数据对分区表读写的性能比较.

在网上看了很多文章,都太过于概念,注意集中在介绍分区表的优点,而不注重时间操作,跟大学教授论文似的,唯一由于的一片文章和大家分享一下吧http://fanqiang.chinaunix.net/db/mysql/2006-05-08/4135.shtml.

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次平均值统计
统计表:

开始时间

结束时间

查询结果

无分区耗时

有分区耗时

涉及分区

全部

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

小结:

1) 分区对单条数据的插入操作无较大影响.

 

以上是我对MySQL的初体验总结,没啥心得体会,只有一点点成就感,希望和大家分享.

另外分区表尚存在问题:

1,是否可将分区表设置在不同硬盘,innodb可行?

2,是否可根据多条件进行水平分区,类似group by 列1,列2...

3,是否能将分区设置成不同引擎,例如当前使用中的分区为innodb,老的分区使用MyISAM

分享到：
| 小议同步IO ：fsync与fdatasync
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    MySQL分区表自动创建及删除存储过程

    用存储过程实现了MySQL数据库分区表的自动创建和自动删除功能。亲测有效。希望有用。

    MYSQL分区表测试

    MYSQL分区表测试过程详细说明。MYSQL分区表测试过程详细说明。

    创建mysql表分区的方法

    表分区的测试使用，主要内容来自于其他博客文章以及mysql5.1的参考手册mysql测试版本：mysql5.5.28mysql物理存储文件（有mysql配置的datadir决定存储路径）格式简介数据库engine为MYISAMfrm表结构文件，myd表数据...

    互联网公司为啥不使用mysql分区表

    互联网公司为啥不使用mysql分区表

    mysql表分区

    mysql表分区策略，包含range分区、list分区、hash分区等方法介绍及详解

    mysql数据库表分区教程

    mysql数据库表分区教程，详细说明mysql表分区的每一个步骤

    MYSQL学习资料

    MySQL水平分区表实际操作总结 Mysql水平分表 mysql水平分表和垂直分表和数据库分区 sysbench mysql 坚持不懈 sysbench安装和使用 SHOW INNODB STATUS 探秘 体验Innodb with Memcached –安装 数据切分及整合方案 ...

    MySQL 5.5.8 分区表性能测试

    测试结果 博文链接：https://hawthorstein.iteye.com/blog/888755

    mysql 实现定时给表追加分区

    实现mysql 每天定时自动给数据库表追加分区，包含存储计划和存储过程

    详解MySQL分区表

    本篇文章给大家带来的内容是关于MySQL中分区表的介绍及使用场景，有需要的朋友可以参考一下，希望对你有所帮助。 1.分区的目的及分区类型 MySQL在创建表的时候可以通过使用PARTITION BY子句定义每个分区存放的数据。...

    mysql实现自动创建与删除分区

    实现mysql按时间分区方式自动创建与删除分区，包括创建/删除日志记录，通过存储过程与事件联合实现，自动创建数量与删除数量可动态配置

    mysql 数据库表分区

    php for mysql的表分区类,为mysql的大数据查询提供分区支持. 该类包含追加 创建 删除分区方法.可以通过索引 分类等字段为条件对数据库物理文件分离. 数据表的查询不受任何影响

    生产中使用和管理MySQL分区表

    分库什么的，如果是垂直的话，dba 没法一个人搞的，肯定要和研发的做下来谈的。水平可以自己搞！

    mysql分区表管理(完整版)

    数据库分区是一种物理数据库设计技术。虽然分区技术可以实现很多效果，但其主要目的是为了在特定的SQL操作中减少数据读写的总量以缩减sql语句的响应时间，同时对于...MYSQL的分区主要有两种形式：水平分区和垂直分区。

    MySQL数据库分区技术

    MySQL数据库分区技术，解决多并发问题，详细描述分区流程及操作步骤

    mysql使用教程之分区表的使用方法(删除分区表)

    MySQL使用分区表的好处： 1，可以把一些归类的数据放在一个分区中，可以减少服务器检查数据的数量加快查询。2，方便维护，通过删除分区来删除老的数据。3，分区数据可以被分布到不同的物理位置，可以做分布式有效...

    MySQL分区和分表技术总结.docx

    MySQL分区和分表技术总结.docx

    MySQL交换分区的实例详解

    在MySQL中，分区是可以交换的，可以将一个分区表中的一个分区和一个普通表中的数据互换。 交换分区的实现 1、交换分区的语法 alter table pt exchange partition p with table nt; 解释： 将 分区表pt 的 分区p 和 ...

    （mysql面试题）MySQL中的分区表的概念及其作用及代码展示.txt

    解释： - 在上述代码中，我们首先创建了一个名为`orders`的分区表，该表包含三个字段：`id`、`order_date`和`customer_id`。...由于`orders`表是分区表，MySQL会根据分区的范围选择需要扫描的分区，从而提高查询性能。

    MySQL分区表的最佳实践指南

    本篇文章给大家带来的内容是关于MySQL中分区表的介绍及使用场景，有需要的朋友可以参考一下，希望对你有所帮助。 1.分区的目的及分区类型 MySQL在创建表的时候可以通过使用PARTITION BY子句定义每个分区存放的数据...

Magicbox
Global site tag (gtag.js) - Google Analytics