本文总结个这段时间研究MySQL水平分区表总结,列举分区表的相关操作和通过实际数据对分区表读写的性能比较.
在网上看了很多文章,都太过于概念,注意集中在介绍分区表的优点,而不注重时间操作,跟大学教授论文似的,唯一由于的一片文章和大家分享一下吧http://fanqiang.chinaunix.net/db/mysql/2006-05-08/4135.shtml.
MySQL分区表操作代码(本案例按月分区):
<!--[if !supportLists]-->1.<!--[endif]-->创建分区表:
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 );
<!--[if !supportLists]-->2.<!--[endif]-->为现有表创建分区:
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 );
<!--[if !supportLists]-->3.<!--[endif]-->删除表中的指定分区(删除分区会导致分区数据丢失,建议先备份):
ALTERTABLE 表名DROP PARTITION p0;
<!--[if !supportLists]-->4.<!--[endif]-->追加表分区
需要先删除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);
<!--[if !supportLists]-->5.<!--[endif]-->查看标分区信息
SELECT
partition_namepart,
partition_expressionexpr,
partition_descriptiondescr,
table_rows
FROM
INFORMATION_SCHEMA.partitions
WHERE
TABLE_SCHEMA= schema()
ANDTABLE_NAME='表名';
<!--[if !supportLists]-->6.<!--[endif]-->查看查询语句涉及分区信息
explainpartitions
select …from 表名 where …;
性能对比:
<!--[if !supportLists]-->1.<!--[endif]-->测试环境
CPU: Intel 奔腾双核 E5300
硬盘: 西数(320GB/7200/16M 蓝盘)
内存: 南亚易胜 DDR2 800MHz 1GB + 三星 DDR2 800MHz 1GB
操作系统:Windows XP
MySQL版本: 5.1.57(5.1+版本支持分区表)
<!--[if !supportLists]-->2.<!--[endif]-->表信息
表结构:
名
|
类型
|
长度
|
|
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
|
<!--[if !supportLists]-->3.<!--[endif]-->查询效率对比
对比表:无分区表名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
|
小结:
<!--[if !supportLists]-->1)分区表查询在查询上有明显优势.但在跨区查询时会有查询时间消耗,因此需要注意分区的疏密程度.
<!--[if !supportLists]-->2)每次查询都会查询pmin(第一个分区),因此需要尽量减少这个分区的数据.
<!--[if !supportLists]-->4.<!--[endif]-->写入数据效率对比
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.
分享到:
相关推荐
MySQL分区是一种数据库优化技术,它将大型表分成更小、更易管理的部分,每个部分称为一个分区。这种技术有助于提高查询性能,特别是对于那些需要处理大量数据或执行复杂查询的应用程序。MySQL支持多种分区类型,包括...
"互联网公司为啥不使用mysql分区表" 在互联网公司中,mysql分区表是一个比较少用的技术,这是因为分区表存在一些缺陷和限制,使得互联网公司更多地选择自己分库分表来水平扩展数据库。 首先,分区表的设计需要考虑...
MySQL分区表是一种优化大型数据表查询效率的技术,它将一个大表分成多个逻辑上相连但物理上独立的部分,每个部分称为一个分区。分区可以按照不同的策略进行,如范围、哈希、列表或复合分区。这样做有助于提高数据...
MySQL 分区是数据库管理系统中的一种优化策略,它将大型表的数据分布在不同的物理存储上,以提高查询性能和便于管理大量数据。分区的本质是将一张大表逻辑上分成多个部分,但用户在操作时仍然将其视为单个表。MySQL ...
### MySQL分区分表方案实践手册知识点详述 #### 一、MySQL分区简介 数据库分区是一项重要的物理数据库设计技术,主要用于优化数据库性能并简化数据管理。MySQL的分区主要包括两种形式:水平分区和垂直分区。 - **...
### MySQL分区资源整理 #### 一、分区概念与优势 MySQL中的分区技术是数据库优化的重要手段之一,尤其适用于处理大规模数据集。通过分区,数据库能够将一个庞大的表拆分为若干个小部分,使得管理和操作变得更加...
MySQL分区是数据库管理系统MySQL中的一种数据存储管理技术,它允许用户将数据表拆分成多个较小的、更容易管理的片段。这些片段被称为分区。通过这种技术可以提高数据库的性能,便于维护和备份,并且可以对不同分区...
1. **存储能力增强**:相比于单一的磁盘或文件系统分区,MySQL 分区可以存储更多数据。 2. **数据清理便捷**:可以更简单地删除不再需要或过时的数据。 3. **查询优化**:某些查询可以直接在特定分区上执行,减少...
【MySQL 表分区详解】 MySQL 表分区是一种高级的数据组织技术,它允许将大型表分成较小、更易管理和处理的部分。这种技术对于处理海量数据,尤其是超过亿级别的数据集非常有效,可以显著提升查询性能和数据管理效率...
MySQL分区管理工具是一种优化数据库性能的技术,它将大型表分解为更小、更易管理的部分,以提高查询效率和数据管理的便利性。在MySQL中,分区主要基于表的数据,根据预定义的规则将数据分布到不同的物理部分。这样...
MySQL分区是一种数据库优化技术,它将大型表分成更小、更易管理的部分,以提高查询性能和数据管理效率。在MySQL中,分区主要是为了解决大数据量时的查询速度问题,通过将数据分散到不同的物理存储块上,使得查询可以...
总的来说,Zabbix的MySQL分区优化是一个涉及数据库设计和管理的重要环节。通过合理地利用分区技术,可以有效应对大规模监控数据的挑战,保持Zabbix系统的高效运行。不过,务必根据你的实际情况进行调整,并考虑其他...
#### 一、MySQL分区概述与应用场景 在当今互联网行业中,MySQL数据库作为最常用的关系型数据库之一,在数据管理方面扮演着极其重要的角色。随着数据量的不断增长,对数据库性能的要求也日益提高。为了提高查询效率...
### MySQL分区表:万字详解与实践指南 #### 一、引言 在现代数据库管理中,随着数据量的不断增长,如何高效管理和查询数据成为了一个重要的课题。MySQL作为广泛使用的开源关系型数据库管理系统之一,提供了多种强大...
MySQL分区技术是一种将数据库中的表分成多个较小的、更易于管理的部分的策略,它通过将数据分布到不同的物理区域来提升数据库性能。这种技术在处理大量数据的传统项目中尤为重要,可以帮助改善数据库的扩展性、管理...
### MySQL分区分表的设计及实现 #### 一、引言 随着信息技术的飞速发展,数据量呈现爆炸式增长,海量数据的管理和处理成为了一项挑战。尤其在关系型数据库中,如MySQL这样的系统,当单个表的数据量达到一定规模后...
MySQL 分区和分表技术总结 MySQL 分区和分表技术是数据库性能优化的重要手段,特别是在大型数据库系统中。以下是 MySQL 分区和分表技术的详细介绍: 什么是分表? 分表是将一个大表按照一定的规则分解成多张具有...