- 浏览: 123014 次
- 性别:
- 来自: 杭州
文章分类
- 全部博客 (145)
- java (145)
- Java网络编程 (1)
- SWT 文本框Text通过GC重绘改变边框颜色 (1)
- tomcat部署web工程的两种方法 (1)
- JAX-RS 从傻逼到牛叉 1:REST 基础知识 (1)
- FreyjaJdbcTemplate 大致上完工了,想请人重构。。 (1)
- 开始认识自己 (1)
- 设计模式-Abstract Factory 模式 (1)
- 数据库中主键的设计原则 (1)
- JNI中jstring类型与c语言中的字符串的转换 (1)
- mac环境变量 (1)
- STC单片机ADC转换的例子 (1)
- myeclipse 8下安装Ibator . (1)
- OSGI与Android结合 (1)
- CSDN BLOG EXPERT (1)
- Java中网络操作的开源库CommonsNet (1)
- Apache License Version 2.0 英文内容及中文翻译 (1)
- JTest (1)
- GeoCon 用C#编写的开源的地理信息数据转换工具 (1)
- ERP简易教程 (1)
- 提高站点在搜索引擎上的排名 (1)
- Wifi (1)
- 腾讯Q+开放平台,相信又是一次成功的模仿 (1)
- C#坦克大战网络版代码 (1)
- Problem16 (1)
- Ajax 应该变成 Ajaj (关于JSON 与 XML 的比较) (1)
- ava框架数据库连接池比较(c3p0 (1)
- dbcp和proxool)bonecp (1)
- 继续向成熟男人靠拢 (1)
- Qt4.7中 默认的构造函数 (1)
- xml CDATA (1)
- 只针对中英文混合分词的中文分词器 (1)
- 典型相关分析及其适用范围和spss操作(转) (1)
- llvm (1)
- java连接数据库Oracle|DB2|Sql Server|Sybase|Informix|MySQL||PostgreSQL|access (1)
最新评论
-
xm3530:
什么鬼?都没法看,发出来干嘛
Android中利用App实现消息推送机制的代码实例 -
lvtenglongxiaohei:
太经典了!
学习一下!
ERP简易教程 -
lvtenglongxiaohei:
<br> 一天中午,丈 ...
ERP简易教程 -
hzw2312:
加油~~~!!!
开始认识自己 -
123048591:
显示乱码
tomcat部署web工程的两种方法
深入了解mysql 5.5分区功能增强
http://database.51cto.com 2010-02-22 10:08 黄永兵 译 51cto 我要评论(2)
摘要:oracle在并购sun后对于mysql的态度令人寻味。在新发布的mysql 5.5中带来了许多增强的功能,在这篇文章中,我们将解释一下mysql 5.5分区功能的增强特性。
标签:mysql 5.5分区
限时报名参加“甲骨文全球大会o2010o北京”及“javaone和甲骨文开发者大会2010”
【51cto经典译文】mysql 5.5的发布带来了许多增强的功能,虽然已经报道了很多增强功能,如半同步复制,但大家却忽略了分区方面的增强,有时甚至还对其真正意义产生了误解,在这篇文章中,我们希望解释一下这些很酷的增强,特别是我们大多数人还没有完全理解的地方。51cto向您推荐《mysql数据库入门与精通教程》。
图 1 大家还没注意到我mysql的分区功能也很强了哦
非整数列分区
任何使用过分区的人应该都遇到过不少问题,特别是面对非整数列分区时,mysql 5.1只能处理整数列分区,如果你想在日期或字符串列上进行分区,你不得不使用函数对其进行转换。
mysql 5.5中新增了两类分区方法,rang和list分区法,同时在新的函数中增加了一个columns关键词。我们假设有这样一个表:
create table expenses (
expense_date date not null,
category varchar(30),
amount decimal (10,3)
);
如果你想使用mysql 5.1中的分区类型,那你必须将类型转换成整数,需要使用一个额外的查找表,到了mysql 5.5中,你可以不用再进行类型转换了,如:
alter table expenses
partition by list columns (category)
(
partition p01 values in ( 'lodging', 'food'),
partition p02 values in ( 'flights', 'ground transportation'),
partition p03 values in ( 'leisure', 'customer entertainment'),
partition p04 values in ( 'communications'),
partition p05 values in ( 'fees')
);
这样的分区语句除了更加易读外,对数据的组织和管理也非常清晰,上面的例子只对category列进行分区。
在mysql 5.1中使用分区另一个让人头痛的问题是date类型(即日期列),你不能直接使用它们,必须使用year或to_days转换这些列,如:
/* 在mysql 5.1中*/
create table t2
(
dt date
)
partition by range (to_days(dt))
(
partition p01 values less than (to_days('2007-01-01')),
partition p02 values less than (to_days('2008-01-01')),
partition p03 values less than (to_days('2009-01-01')),
partition p04 values less than (maxvalue));
show create table t2 \g
*************************** 1. row ***************************
table: t2
create table: create table `t2` (
`dt` date default null
) engine=myisam default charset=latin1
/*!50100 partition by range (to_days(dt))
(partition p01 values less than (733042) engine = myisam,
partition p02 values less than (733407) engine = myisam,
partition p03 values less than (733773) engine = myisam,
partition p04 values less than maxvalue engine = myisam) */
看上去非常糟糕,当然也有变通办法,但麻烦确实不少。使用year或to_days定义一个分区的确让人费解,查询时不得不使用赤裸列,因为加了函数的查询不能识别分区。
但在mysql 5.5中情况发生了很大的变化,现在在日期列上可以直接分区,并且方法也很简单。
/*在mysql 5.5中*/
create table t2
(
dt date
)
partition by range columns (dt)
(
partition p01 values less than ('2007-01-01'),
partition p02 values less than ('2008-01-01'),
partition p03 values less than ('2009-01-01'),
partition p04 values less than (maxvalue));
show create table t2 \g
*************************** 1. row ***************************
table: t2
create table: create table `t2` (
`dt` date default null
) engine=myisam default charset=latin1
/*!50500 partition by range columns(dt)
(partition p01 values less than ('2007-01-01') engine = myisam,
partition p02 values less than ('2008-01-01') engine = myisam,
partition p03 values less than ('2009-01-01') engine = myisam,
partition p04 values less than (maxvalue) engine = myisam) */
在这里,通过函数定义和通过列查询之间没有冲突,因为是按列定义的,我们在定义中插入的值是保留的。
多列分区
columns关键字现在允许字符串和日期列作为分区定义列,同时还允许使用多个列定义一个分区,你可能在官方文档中已经看到了一些例子,如:
create table p1 (
a int,
b int,
c int
)
partition by range columns (a,b)
(
partition p01 values less than (10,20),
partition p02 values less than (20,30),
partition p03 values less than (30,40),
partition p04 values less than (40,maxvalue),
partition p05 values less than (maxvalue,maxvalue)
);
create table p2 (
a int,
b int,
c int
)
partition by range columns (a,b)
(
partition p01 values less than (10,10),
partition p02 values less than (10,20),
partition p03 values less than (10,30),
partition p04 values less than (10,maxvalue),
partition p05 values less than (maxvalue,maxvalue)
)
同样还有partition by range columns (a,b,c)等其它例子。由于我很长时间都在使用mysql 5.1的分区,我对多列分区的含义不太了解,less than (10,10)是什么意思?如果下一个分区是less than (10,20)会发生什么?相反,如果是(20,30)又会如何?
所有这些问题都需要一个答案,在回答之前,他们需要更好地理解我们在做什么。
开始时可能有些混乱,当所有分区有一个不同范围的值时,实际上,它只是在表的一个列上进行了分区,但事实并非如此,在下面的例子中:
create table p1_single (
a int,
b int,
c int
)
partition by range columns (a)
(
partition p01 values less than (10),
partition p02 values less than (20),
partition p03 values less than (30),
partition p04 values less than (40),
partition p05 values less than (maxvalue)
);
它和前面的表p1不一样,如果你在表p1中插入(10,1,1),它将会进入第一个分区,相反,在表p1_single中,它将会进入第二个分区,其原因是(10,1)小于(10,10),如果你仅仅关注第一个值,你还没有意识到你在比较一个元组,而不是一个单一的值。
现在我们来分析一下最难懂的地方,当你需要确定某一行应该放在哪里时会发生什么?你是如何确定类似(10,9) < (10,10)这种运算的值的?答案其实很简单,当你对它们进行排序时,使用相同的方法计算两条记录的值。
a=10
b=9
(a,b) < (10,10) ?
# evaluates to:
(a < 10)
or
((a = 10) and ( b < 10))
# which translates to:
(10 < 10)
or
((10 = 10) and ( 9 < 10))
如果有三列,表达式会更长,但不会更复杂。你首先在第一个项目上测试小于运算,如果有两个或更多的分区与之匹配,接着就测试第二个项目,如果不止一个候选分区,那还需要测试第三个项目。
下图所显示的内容表示将遍历三条记录插入到使用以下代码定义的分区中:
(10,10),
(10,20),
(10,30),
(10, maxvalue)
图 2 元组比较。当第一个值小于分区定义的第一个范围时,那么该行将属于这里了。
图 3 元组比较。当第一个值等于分区定义的第一个范围,我们需要比较第二个项目,如果它小于第二个范围,那么该行将属于这里了。
图 4 元组比较。当第一个值和第二个值等于他们对应的范围时,如果元组不小于定义的范围,那么它就不属于这里,继续下一步。
图 5 元组比较。在下一个范围时,第一个项目是等于,第二个项目是小于,因此元组更小,那么该行就属于这里了。
在这些图的帮助下,我们对插入一条记录到多列分区表的步骤有了更深的了解,这些都是理论上的,为了帮助你更好地掌握新功能,我们再来看一个更高级一点的例子,对于比较务实的读者更有意义,下面是表的定义脚本:
create table employees (
emp_no int(11) not null,
birth_date date not null,
first_name varchar(14) not null,
last_name varchar(16) not null,
gender char(1) default null,
hire_date date not null
) engine=myisam
partition by range columns(gender,hire_date)
(partition p01 values less than ('f','1990-01-01') ,
partition p02 values less than ('f','2000-01-01') ,
partition p03 values less than ('f',maxvalue) ,
partition p04 values less than ('m','1990-01-01') ,
partition p05 values less than ('m','2000-01-01') ,
partition p06 values less than ('m',maxvalue) ,
partition p07 values less than (maxvalue,maxvalue)
和上面的例子不同,这个例子更好理解,第一个分区用来存储雇佣于1990年以前的女职员,第二个分区存储股用于1990-2000年之间的女职员,第三个分区存储所有剩下的女职员。对于分区p04到p06,我们策略是一样的,只不过存储的是男职员。最后一个分区是控制情况。
看完后你可能要问,我怎么知道某一行存储在那个分区中的?有两个办法,第一个办法是使用与分区定义相同的条件作为查询条件进行查询。
select
case
when gender = 'f' and hire_date < '1990-01-01'
then 'p1'
when gender = 'f' and hire_date < '2000-01-01'
then 'p2'
when gender = 'f' and hire_date < '2999-01-01'
then 'p3'
when gender = 'm' and hire_date < '1990-01-01'
then 'p4'
when gender = 'm' and hire_date < '2000-01-01'
then 'p5'
when gender = 'm' and hire_date < '2999-01-01'
then 'p6'
else
'p7'
end as p,
count(*) as rows
from employees
group by p;
+------+-------+
| p | rows |
+------+-------+
| p1 | 66212 |
| p2 | 53832 |
| p3 | 7 |
| p4 | 98585 |
| p5 | 81382 |
| p6 | 6 |
+------+-------+
如果表是myisam或archive,你可以信任由information_schema提供的统计信息。
select
partition_name part,
partition_expression expr,
partition_description descr,
table_rows
from
information_schema.partitions
where
table_schema = schema()
and table_name='employees';
+------+------------------+-------------------+------------+
| part | expr | descr | table_rows |
+------+------------------+-------------------+------------+
| p01 | gender,hire_date | 'f','1990-01-01' | 66212 |
| p02 | gender,hire_date | 'f','2000-01-01' | 53832 |
| p03 | gender,hire_date | 'f',maxvalue | 7 |
| p04 | gender,hire_date | 'm','1990-01-01' | 98585 |
| p05 | gender,hire_date | 'm','2000-01-01' | 81382 |
| p06 | gender,hire_date | 'm',maxvalue | 6 |
| p07 | gender,hire_date | maxvalue,maxvalue | 0 |
+------+------------------+-------------------+------------+
如果存储引擎是innodb,上面的值就是一个近似值,如果你需要确切的值,那你就不能信任它们。
另一个问题是它的性能,这些增强触发了分区修整吗?答案毫不含糊,是的。与mysql 5.1有所不同,在5.1中日期分区只能与两个函数工作,在mysql 5.5中,任何使用了columns关键字定义的分区都可以使用分区修整,下面还是测试一下吧。
select count(*) from employees where gender='f' and hire_date < '1990-01-01';
+----------+
| count(*) |
+----------+
| 66212 |
+----------+
1 row in set (0.05 sec)
explain partitions select count(*) from employees where gender='f' and hire_date < '1990-01-01'\g
*************************** 1. row ***************************
id: 1
select_type: simple
table: employees
partitions: p01
type: all
possible_keys: null
key: null
key_len: null
ref: null
rows: 300024
extra: using where
使用定义第一个分区的条件,我们获得了一个非常优化的查询,不仅如此,部分条件也将从分区修整中受益。
select count(*) from employees where gender='f';
+----------+
| count(*) |
+----------+
| 120051 |
+----------+
1 row in set (0.12 sec)
explain partitions select count(*) from employees where gender='f'\g
*************************** 1. row ***************************
id: 1
select_type: simple
table: employees
partitions: p01,p02,p03,p04
type: all
possible_keys: null
key: null
key_len: null
ref: null
rows: 300024
extra: using where
它和复合索引的算法一样,如果你的条件指的是索引最左边的部分,mysql将会使用它。与此类似,如果你的条件指的是分区定义最左边的部分,mysql将会尽可能修整。它和复合索引一起出现,如果你只使用最右边的条件,分区修整不会工作。
select count(*) from employees where hire_date < '1990-01-01';
+----------+
| count(*) |
+----------+
| 164797 |
+----------+
1 row in set (0.18 sec)
explain partitions select count(*) from employees where hire_date < '1990-01-01'\g
*************************** 1. row ***************************
id: 1
select_type: simple
table: employees
partitions: p01,p02,p03,p04,p05,p06,p07
type: all
possible_keys: null
key: null
key_len: null
ref: null
rows: 300024
extra: using where
如果不用分区定义的第一部分,使用分区定义的第二部分,那么将会发生全表扫描,在设计分区和编写查询时要紧记这一条。
可用性增强:truncate分区
分区最吸引人的一个功能是瞬间移除大量记录的能力,dba都喜欢将历史记录存储到按日期分区的分区表中,这样可以定期删除过时的历史数据,这种方法相当管用,假设第一个分区存储的是最旧的历史记录,那么你可以直接删除第一个分区,然后再在末尾建立一个新分区保存最近的历史记录,这样循环下去就可以实现历史记录的快速清除。
但当你需要移除分区中的部分数据时,事情就不是那么简单了,删除分区没有问题,但如果是清空分区,就很头痛了,要移除分区中的所有数据,但需要保留分区本身,你可以:
使用delete语句,但我们知道delete语句的性能都很差。
使用drop partition语句,紧跟着一个eorganize partitions语句重新创建分区,但这样做比前一个方法的成本要高出许多。
mysql 5.5引入了truncate partition,它和drop partition语句有些类似,但它保留了分区本身,也就是说分区还可以重复利用。truncate partition应该是dba工具箱中的必备工具。
更多微调功能:to_seconds
分区增强包有一个新的函数处理date和datetime列,使用to_seconds函数,你可以将日期/时间列转换成自0年以来的秒数,如果你想使用小于1天的间隔进行分区,那么这个函数就可以帮到你。
to_seconds会触发分区修整,与to_days不同,它可以反过来使用,就是from_days,对于to_seconds就没有这样的反向函数了,但要自己动手diy一个也不是难事。
drop function if exists from_seconds;
delimiter //
create function from_seconds (secs bigint)
returns datetime
begin
declare days int;
declare secs_per_day int;
declare zh int;
declare zm int;
declare zs int;
set secs_per_day = 60 * 60 * 24;
set days = floor(secs / secs_per_day);
set secs = secs - (secs_per_day * days);
set zh = floor(secs / 3600);
set zm = floor(secs / 60) - zh * 60;
set zs = secs - (zh * 3600 + zm * 60);
return cast(concat(from_days(days), ' ', zh, ':', zm, ':', zs) as datetime);
end //
delimiter ;
有了这些新武器,我们可以有把握地创建一个小于1天的临时分区,如:
create table t2 (
dt datetime
)
partition by range (to_seconds(dt))
(
partition p01 values less than (to_seconds('2009-11-30 08:00:00')) ,
partition p02 values less than (to_seconds('2009-11-30 16:00:00')) ,
partition p03 values less than (to_seconds('2009-12-01 00:00:00')) ,
partition p04 values less than (to_seconds('2009-12-01 08:00:00')) ,
partition p05 values less than (to_seconds('2009-12-01 16:00:00')) ,
partition p06 values less than (maxvalue)
);
show create table t2\g
*************************** 1. row ***************************
table: t2
create table: create table `t2` (
`dt` datetime default null
) engine=myisam default charset=latin1
/*!50500 partition by range (to_seconds(dt))
(partition p01 values less than (63426787200) engine = myisam,
partition p02 values less than (63426816000) engine = myisam,
partition p03 values less than (63426844800) engine = myisam,
partition p04 values less than (63426873600) engine = myisam,
partition p05 values less than (63426902400) engine = myisam,
partition p06 values less than maxvalue engine = myisam) */
因为我们没有使用columns关键字,我们也不能使用它,因为它不支持混合列和函数,表定义中的记录值就是to_seconds函数的计算结果。
但我们还是要感谢新的函数,我们可以反推这个值,换算成一个更容易读懂的日期。
select
partition_name part,
partition_expression expr,
from_seconds(partition_description) descr,
table_rows
from
information_schema.partitions
where
table_schema = 'test'
and table_name='t2';
+------+----------------+---------------------+------------+
| part | expr | descr | table_rows |
+------+----------------+---------------------+------------+
| p01 | to_seconds(dt) | 2009-11-30 08:00:00 | 0 |
| p02 | to_seconds(dt) | 2009-11-30 16:00:00 | 0 |
| p03 | to_seconds(dt) | 2009-12-01 00:00:00 | 0 |
| p04 | to_seconds(dt) | 2009-12-01 08:00:00 | 0 |
| p05 | to_seconds(dt) | 2009-12-01 16:00:00 | 0 |
| p06 | to_seconds(dt) | 0000-00-00 00:00:00 | 0 |
+------+----------------+---------------------+------------+
总结
mysql 5.5对分区用户绝对是个好消息,虽然没有提供直接的性能增强的方法(如果你按响应时间评估性能),但更易于使用的增强功能,以及truncate partition命令都可以为dba节省大量的时间,有时对最终用户亦如此。
这些增强的功能可能会在下一个里程碑发布时得到更新,最终版本预计会在2010年年中发布,届时所有分区用户都可以尝试一下!
http://database.51cto.com 2010-02-22 10:08 黄永兵 译 51cto 我要评论(2)
摘要:oracle在并购sun后对于mysql的态度令人寻味。在新发布的mysql 5.5中带来了许多增强的功能,在这篇文章中,我们将解释一下mysql 5.5分区功能的增强特性。
标签:mysql 5.5分区
限时报名参加“甲骨文全球大会o2010o北京”及“javaone和甲骨文开发者大会2010”
【51cto经典译文】mysql 5.5的发布带来了许多增强的功能,虽然已经报道了很多增强功能,如半同步复制,但大家却忽略了分区方面的增强,有时甚至还对其真正意义产生了误解,在这篇文章中,我们希望解释一下这些很酷的增强,特别是我们大多数人还没有完全理解的地方。51cto向您推荐《mysql数据库入门与精通教程》。
图 1 大家还没注意到我mysql的分区功能也很强了哦
非整数列分区
任何使用过分区的人应该都遇到过不少问题,特别是面对非整数列分区时,mysql 5.1只能处理整数列分区,如果你想在日期或字符串列上进行分区,你不得不使用函数对其进行转换。
mysql 5.5中新增了两类分区方法,rang和list分区法,同时在新的函数中增加了一个columns关键词。我们假设有这样一个表:
create table expenses (
expense_date date not null,
category varchar(30),
amount decimal (10,3)
);
如果你想使用mysql 5.1中的分区类型,那你必须将类型转换成整数,需要使用一个额外的查找表,到了mysql 5.5中,你可以不用再进行类型转换了,如:
alter table expenses
partition by list columns (category)
(
partition p01 values in ( 'lodging', 'food'),
partition p02 values in ( 'flights', 'ground transportation'),
partition p03 values in ( 'leisure', 'customer entertainment'),
partition p04 values in ( 'communications'),
partition p05 values in ( 'fees')
);
这样的分区语句除了更加易读外,对数据的组织和管理也非常清晰,上面的例子只对category列进行分区。
在mysql 5.1中使用分区另一个让人头痛的问题是date类型(即日期列),你不能直接使用它们,必须使用year或to_days转换这些列,如:
/* 在mysql 5.1中*/
create table t2
(
dt date
)
partition by range (to_days(dt))
(
partition p01 values less than (to_days('2007-01-01')),
partition p02 values less than (to_days('2008-01-01')),
partition p03 values less than (to_days('2009-01-01')),
partition p04 values less than (maxvalue));
show create table t2 \g
*************************** 1. row ***************************
table: t2
create table: create table `t2` (
`dt` date default null
) engine=myisam default charset=latin1
/*!50100 partition by range (to_days(dt))
(partition p01 values less than (733042) engine = myisam,
partition p02 values less than (733407) engine = myisam,
partition p03 values less than (733773) engine = myisam,
partition p04 values less than maxvalue engine = myisam) */
看上去非常糟糕,当然也有变通办法,但麻烦确实不少。使用year或to_days定义一个分区的确让人费解,查询时不得不使用赤裸列,因为加了函数的查询不能识别分区。
但在mysql 5.5中情况发生了很大的变化,现在在日期列上可以直接分区,并且方法也很简单。
/*在mysql 5.5中*/
create table t2
(
dt date
)
partition by range columns (dt)
(
partition p01 values less than ('2007-01-01'),
partition p02 values less than ('2008-01-01'),
partition p03 values less than ('2009-01-01'),
partition p04 values less than (maxvalue));
show create table t2 \g
*************************** 1. row ***************************
table: t2
create table: create table `t2` (
`dt` date default null
) engine=myisam default charset=latin1
/*!50500 partition by range columns(dt)
(partition p01 values less than ('2007-01-01') engine = myisam,
partition p02 values less than ('2008-01-01') engine = myisam,
partition p03 values less than ('2009-01-01') engine = myisam,
partition p04 values less than (maxvalue) engine = myisam) */
在这里,通过函数定义和通过列查询之间没有冲突,因为是按列定义的,我们在定义中插入的值是保留的。
多列分区
columns关键字现在允许字符串和日期列作为分区定义列,同时还允许使用多个列定义一个分区,你可能在官方文档中已经看到了一些例子,如:
create table p1 (
a int,
b int,
c int
)
partition by range columns (a,b)
(
partition p01 values less than (10,20),
partition p02 values less than (20,30),
partition p03 values less than (30,40),
partition p04 values less than (40,maxvalue),
partition p05 values less than (maxvalue,maxvalue)
);
create table p2 (
a int,
b int,
c int
)
partition by range columns (a,b)
(
partition p01 values less than (10,10),
partition p02 values less than (10,20),
partition p03 values less than (10,30),
partition p04 values less than (10,maxvalue),
partition p05 values less than (maxvalue,maxvalue)
)
同样还有partition by range columns (a,b,c)等其它例子。由于我很长时间都在使用mysql 5.1的分区,我对多列分区的含义不太了解,less than (10,10)是什么意思?如果下一个分区是less than (10,20)会发生什么?相反,如果是(20,30)又会如何?
所有这些问题都需要一个答案,在回答之前,他们需要更好地理解我们在做什么。
开始时可能有些混乱,当所有分区有一个不同范围的值时,实际上,它只是在表的一个列上进行了分区,但事实并非如此,在下面的例子中:
create table p1_single (
a int,
b int,
c int
)
partition by range columns (a)
(
partition p01 values less than (10),
partition p02 values less than (20),
partition p03 values less than (30),
partition p04 values less than (40),
partition p05 values less than (maxvalue)
);
它和前面的表p1不一样,如果你在表p1中插入(10,1,1),它将会进入第一个分区,相反,在表p1_single中,它将会进入第二个分区,其原因是(10,1)小于(10,10),如果你仅仅关注第一个值,你还没有意识到你在比较一个元组,而不是一个单一的值。
现在我们来分析一下最难懂的地方,当你需要确定某一行应该放在哪里时会发生什么?你是如何确定类似(10,9) < (10,10)这种运算的值的?答案其实很简单,当你对它们进行排序时,使用相同的方法计算两条记录的值。
a=10
b=9
(a,b) < (10,10) ?
# evaluates to:
(a < 10)
or
((a = 10) and ( b < 10))
# which translates to:
(10 < 10)
or
((10 = 10) and ( 9 < 10))
如果有三列,表达式会更长,但不会更复杂。你首先在第一个项目上测试小于运算,如果有两个或更多的分区与之匹配,接着就测试第二个项目,如果不止一个候选分区,那还需要测试第三个项目。
下图所显示的内容表示将遍历三条记录插入到使用以下代码定义的分区中:
(10,10),
(10,20),
(10,30),
(10, maxvalue)
图 2 元组比较。当第一个值小于分区定义的第一个范围时,那么该行将属于这里了。
图 3 元组比较。当第一个值等于分区定义的第一个范围,我们需要比较第二个项目,如果它小于第二个范围,那么该行将属于这里了。
图 4 元组比较。当第一个值和第二个值等于他们对应的范围时,如果元组不小于定义的范围,那么它就不属于这里,继续下一步。
图 5 元组比较。在下一个范围时,第一个项目是等于,第二个项目是小于,因此元组更小,那么该行就属于这里了。
在这些图的帮助下,我们对插入一条记录到多列分区表的步骤有了更深的了解,这些都是理论上的,为了帮助你更好地掌握新功能,我们再来看一个更高级一点的例子,对于比较务实的读者更有意义,下面是表的定义脚本:
create table employees (
emp_no int(11) not null,
birth_date date not null,
first_name varchar(14) not null,
last_name varchar(16) not null,
gender char(1) default null,
hire_date date not null
) engine=myisam
partition by range columns(gender,hire_date)
(partition p01 values less than ('f','1990-01-01') ,
partition p02 values less than ('f','2000-01-01') ,
partition p03 values less than ('f',maxvalue) ,
partition p04 values less than ('m','1990-01-01') ,
partition p05 values less than ('m','2000-01-01') ,
partition p06 values less than ('m',maxvalue) ,
partition p07 values less than (maxvalue,maxvalue)
和上面的例子不同,这个例子更好理解,第一个分区用来存储雇佣于1990年以前的女职员,第二个分区存储股用于1990-2000年之间的女职员,第三个分区存储所有剩下的女职员。对于分区p04到p06,我们策略是一样的,只不过存储的是男职员。最后一个分区是控制情况。
看完后你可能要问,我怎么知道某一行存储在那个分区中的?有两个办法,第一个办法是使用与分区定义相同的条件作为查询条件进行查询。
select
case
when gender = 'f' and hire_date < '1990-01-01'
then 'p1'
when gender = 'f' and hire_date < '2000-01-01'
then 'p2'
when gender = 'f' and hire_date < '2999-01-01'
then 'p3'
when gender = 'm' and hire_date < '1990-01-01'
then 'p4'
when gender = 'm' and hire_date < '2000-01-01'
then 'p5'
when gender = 'm' and hire_date < '2999-01-01'
then 'p6'
else
'p7'
end as p,
count(*) as rows
from employees
group by p;
+------+-------+
| p | rows |
+------+-------+
| p1 | 66212 |
| p2 | 53832 |
| p3 | 7 |
| p4 | 98585 |
| p5 | 81382 |
| p6 | 6 |
+------+-------+
如果表是myisam或archive,你可以信任由information_schema提供的统计信息。
select
partition_name part,
partition_expression expr,
partition_description descr,
table_rows
from
information_schema.partitions
where
table_schema = schema()
and table_name='employees';
+------+------------------+-------------------+------------+
| part | expr | descr | table_rows |
+------+------------------+-------------------+------------+
| p01 | gender,hire_date | 'f','1990-01-01' | 66212 |
| p02 | gender,hire_date | 'f','2000-01-01' | 53832 |
| p03 | gender,hire_date | 'f',maxvalue | 7 |
| p04 | gender,hire_date | 'm','1990-01-01' | 98585 |
| p05 | gender,hire_date | 'm','2000-01-01' | 81382 |
| p06 | gender,hire_date | 'm',maxvalue | 6 |
| p07 | gender,hire_date | maxvalue,maxvalue | 0 |
+------+------------------+-------------------+------------+
如果存储引擎是innodb,上面的值就是一个近似值,如果你需要确切的值,那你就不能信任它们。
另一个问题是它的性能,这些增强触发了分区修整吗?答案毫不含糊,是的。与mysql 5.1有所不同,在5.1中日期分区只能与两个函数工作,在mysql 5.5中,任何使用了columns关键字定义的分区都可以使用分区修整,下面还是测试一下吧。
select count(*) from employees where gender='f' and hire_date < '1990-01-01';
+----------+
| count(*) |
+----------+
| 66212 |
+----------+
1 row in set (0.05 sec)
explain partitions select count(*) from employees where gender='f' and hire_date < '1990-01-01'\g
*************************** 1. row ***************************
id: 1
select_type: simple
table: employees
partitions: p01
type: all
possible_keys: null
key: null
key_len: null
ref: null
rows: 300024
extra: using where
使用定义第一个分区的条件,我们获得了一个非常优化的查询,不仅如此,部分条件也将从分区修整中受益。
select count(*) from employees where gender='f';
+----------+
| count(*) |
+----------+
| 120051 |
+----------+
1 row in set (0.12 sec)
explain partitions select count(*) from employees where gender='f'\g
*************************** 1. row ***************************
id: 1
select_type: simple
table: employees
partitions: p01,p02,p03,p04
type: all
possible_keys: null
key: null
key_len: null
ref: null
rows: 300024
extra: using where
它和复合索引的算法一样,如果你的条件指的是索引最左边的部分,mysql将会使用它。与此类似,如果你的条件指的是分区定义最左边的部分,mysql将会尽可能修整。它和复合索引一起出现,如果你只使用最右边的条件,分区修整不会工作。
select count(*) from employees where hire_date < '1990-01-01';
+----------+
| count(*) |
+----------+
| 164797 |
+----------+
1 row in set (0.18 sec)
explain partitions select count(*) from employees where hire_date < '1990-01-01'\g
*************************** 1. row ***************************
id: 1
select_type: simple
table: employees
partitions: p01,p02,p03,p04,p05,p06,p07
type: all
possible_keys: null
key: null
key_len: null
ref: null
rows: 300024
extra: using where
如果不用分区定义的第一部分,使用分区定义的第二部分,那么将会发生全表扫描,在设计分区和编写查询时要紧记这一条。
可用性增强:truncate分区
分区最吸引人的一个功能是瞬间移除大量记录的能力,dba都喜欢将历史记录存储到按日期分区的分区表中,这样可以定期删除过时的历史数据,这种方法相当管用,假设第一个分区存储的是最旧的历史记录,那么你可以直接删除第一个分区,然后再在末尾建立一个新分区保存最近的历史记录,这样循环下去就可以实现历史记录的快速清除。
但当你需要移除分区中的部分数据时,事情就不是那么简单了,删除分区没有问题,但如果是清空分区,就很头痛了,要移除分区中的所有数据,但需要保留分区本身,你可以:
使用delete语句,但我们知道delete语句的性能都很差。
使用drop partition语句,紧跟着一个eorganize partitions语句重新创建分区,但这样做比前一个方法的成本要高出许多。
mysql 5.5引入了truncate partition,它和drop partition语句有些类似,但它保留了分区本身,也就是说分区还可以重复利用。truncate partition应该是dba工具箱中的必备工具。
更多微调功能:to_seconds
分区增强包有一个新的函数处理date和datetime列,使用to_seconds函数,你可以将日期/时间列转换成自0年以来的秒数,如果你想使用小于1天的间隔进行分区,那么这个函数就可以帮到你。
to_seconds会触发分区修整,与to_days不同,它可以反过来使用,就是from_days,对于to_seconds就没有这样的反向函数了,但要自己动手diy一个也不是难事。
drop function if exists from_seconds;
delimiter //
create function from_seconds (secs bigint)
returns datetime
begin
declare days int;
declare secs_per_day int;
declare zh int;
declare zm int;
declare zs int;
set secs_per_day = 60 * 60 * 24;
set days = floor(secs / secs_per_day);
set secs = secs - (secs_per_day * days);
set zh = floor(secs / 3600);
set zm = floor(secs / 60) - zh * 60;
set zs = secs - (zh * 3600 + zm * 60);
return cast(concat(from_days(days), ' ', zh, ':', zm, ':', zs) as datetime);
end //
delimiter ;
有了这些新武器,我们可以有把握地创建一个小于1天的临时分区,如:
create table t2 (
dt datetime
)
partition by range (to_seconds(dt))
(
partition p01 values less than (to_seconds('2009-11-30 08:00:00')) ,
partition p02 values less than (to_seconds('2009-11-30 16:00:00')) ,
partition p03 values less than (to_seconds('2009-12-01 00:00:00')) ,
partition p04 values less than (to_seconds('2009-12-01 08:00:00')) ,
partition p05 values less than (to_seconds('2009-12-01 16:00:00')) ,
partition p06 values less than (maxvalue)
);
show create table t2\g
*************************** 1. row ***************************
table: t2
create table: create table `t2` (
`dt` datetime default null
) engine=myisam default charset=latin1
/*!50500 partition by range (to_seconds(dt))
(partition p01 values less than (63426787200) engine = myisam,
partition p02 values less than (63426816000) engine = myisam,
partition p03 values less than (63426844800) engine = myisam,
partition p04 values less than (63426873600) engine = myisam,
partition p05 values less than (63426902400) engine = myisam,
partition p06 values less than maxvalue engine = myisam) */
因为我们没有使用columns关键字,我们也不能使用它,因为它不支持混合列和函数,表定义中的记录值就是to_seconds函数的计算结果。
但我们还是要感谢新的函数,我们可以反推这个值,换算成一个更容易读懂的日期。
select
partition_name part,
partition_expression expr,
from_seconds(partition_description) descr,
table_rows
from
information_schema.partitions
where
table_schema = 'test'
and table_name='t2';
+------+----------------+---------------------+------------+
| part | expr | descr | table_rows |
+------+----------------+---------------------+------------+
| p01 | to_seconds(dt) | 2009-11-30 08:00:00 | 0 |
| p02 | to_seconds(dt) | 2009-11-30 16:00:00 | 0 |
| p03 | to_seconds(dt) | 2009-12-01 00:00:00 | 0 |
| p04 | to_seconds(dt) | 2009-12-01 08:00:00 | 0 |
| p05 | to_seconds(dt) | 2009-12-01 16:00:00 | 0 |
| p06 | to_seconds(dt) | 0000-00-00 00:00:00 | 0 |
+------+----------------+---------------------+------------+
总结
mysql 5.5对分区用户绝对是个好消息,虽然没有提供直接的性能增强的方法(如果你按响应时间评估性能),但更易于使用的增强功能,以及truncate partition命令都可以为dba节省大量的时间,有时对最终用户亦如此。
这些增强的功能可能会在下一个里程碑发布时得到更新,最终版本预计会在2010年年中发布,届时所有分区用户都可以尝试一下!
发表评论
-
java连接数据库Oracle|DB2|Sql Server|Sybase|Informix|MySQL||PostgreSQL|access
2012-02-08 14:17 1018<div>Java数据库连接(JDBC)由 ... -
llvm
2012-02-07 16:29 848llvm ... -
典型相关分析及其适用范围和spss操作(转)
2012-02-07 15:43 1518看文章《科学学研 ... -
只针对中英文混合分词的中文分词器
2012-02-03 10:39 981该版本说明 1、只针对中英文混合分词 需要一些中文和 ... -
xml CDATA
2012-02-03 08:45 1159<h2 style="font-si ... -
Qt4.7中 默认的构造函数
2012-02-01 09:14 1053<p><span style=&qu ... -
继续向成熟男人靠拢
2012-01-11 17:04 851转自徒儿的人人。 ... -
ava框架数据库连接池比较(c3p0,dbcp和proxool)bonecp
2012-01-11 14:13 1080<h1 style="text ... -
Ajax 应该变成 Ajaj (关于JSON 与 XML 的比较)
2011-12-28 15:23 913<span style="font- ... -
Problem16
2011-12-28 12:53 633package com.shui.mu.yao.io. ... -
C#坦克大战网络版代码
2011-12-20 13:09 931简单C#坦克大战网络版代码 写完单机版 http ... -
腾讯Q+开放平台,相信又是一次成功的模仿
2011-12-20 10:44 832今天看到两则新 ... -
Wifi
2011-12-19 13:14 1037. Confirm if Wifi is On ... -
提高站点在搜索引擎上的排名
2011-12-19 12:04 887对于拥有网站的各位站长来说,都希望自己的站点能够在各种 ... -
ERP简易教程
2011-12-16 16:47 878注明:下面的帖子 ... -
GeoCon 用C#编写的开源的地理信息数据转换工具
2011-12-14 12:29 928<p class="MsoNorma ... -
JTest
2011-12-14 09:00 1000接到parasoft公司一位先生打来的电话,说下个月第 ... -
Apache License Version 2.0 英文内容及中文翻译
2011-12-13 12:59 2220</span> <p class= ... -
Java中网络操作的开源库CommonsNet
2011-12-13 12:39 775<p class="MsoNorma ... -
CSDN BLOG EXPERT
2011-12-13 08:59 1062<img src="http://p. ...
相关推荐
1.mysql 备份数据 2.Mysql 更新数据 3.MySQL常用操作命令 4.MySQL的数据类型和建库策略详解 5.MySQL多表操作和备份处理 6.MySQL索引分类和各自用途...12.深入了解MySQL 5.5分区功能增强.doc 13.在MySQL中操作日期和时间
介绍 RANGE分区基于一个给定的连续...本文将给大家介绍MySQL 5.5 range分区增加删除处理的相关内容,分享给大家供大家参考学习,下面来看看详细的介绍: 一、删除分区 ##查看要处理的分区的数据量,并导出作为备份 mys
COLUMN分区是5.5开始引入的分区功能,只有RANGE COLUMN和LIST COLUMN这两种分区;支持整形、日期、字符串;RANGE和LIST的分区方式非常的相似。 COLUMNS和RANGE和LIST分区的区别 1.针对日期字段的分区就不需要再使用...
深入浅出MySQL生产环境高可用架构MyCat教程 尚硅谷Redis视频 高性能mysql优化 打造扛得住的MySQL数据库架构(5.7 阿里大神讲授MySQL数据库运维(5.6) SQL语句完全掌握 MySQL数据库调优技术百万级数据库优化方案MySQL...
改资料对高级和中级以及初学者都有很大的帮助和数据库设计《高性能MySQL(影印版)(第3版)(英文版)》学习MySQL5.5版提供的新特性,包括存储过程、数据库分区、触发器和视图,实现在复制、高可用性和集群上的改进,实现...
《高性能MySQL(影印版)(第3版)(英文版)》学习MySQL5.5版提供的新特性,包括存储过程、数据库分区、触发器和视图,实现在复制、高可用性和集群上的改进,实现MySQL在云环境中运行的高性能,优化高级查询特性,如全文...
第一部分 mysql5.5 新特性篇 第1章 mysql5.5介绍 2 1.1 性能上的显著改变 2 1.1.1 mysql5.5默认存储引擎的调整 2 1.1.2 充分利用cpu多核的处理能力 7 1.1.3 提高刷新脏页数量和合并插入数量,改善磁盘i/o处理...
5.5. MySQL服务器关机进程 5.6. 一般安全问题 5.6.1. 通用安全指南 5.6.2. 使MySQL在攻击者面前保持安全 5.6.3. Mysqld安全相关启动选项 5.6.4. LOAD DATA LOCAL安全问题 5.7. MySQL访问权限系统 5.7.1. 权限系统的...
《高性能MySQL(影印版)(第3版)(英文版)》学习MySQL5.5版提供的新特性,包括存储过程、数据库分区、触发器和视图,实现在复制、高可用性和集群上的改进,实现MySQL在云环境中运行的高性能,优化高级查询特性,如全文...
5.5. MySQL服务器关机进程 5.6. 一般安全问题 5.6.1. 通用安全指南 5.6.2. 使MySQL在攻击者面前保持安全 5.6.3. Mysqld安全相关启动选项 5.6.4. LOAD DATA LOCAL安全问题 5.7. MySQL访问权限系统 5.7.1. 权限系统的...