全部博文(2065)
分类: Mysql/postgreSQL
2010-02-02 21:30:08
MySQL表分区专题
[整理:hkebao@126.com 整理时间:
通过分区(Partition)提升MySQL性能,数据库的物理设计在对高级数据库的性能影响上远比其他因素重要。DBA如果想要高性能的数据库就必须在数据库的物理设计上多思考的观点,这样才能减少响应时间使终端用户满意而不是引来骂声一片。
一、什么是数据库分区
数据库分区是一种物理数据库设计技术,DBA和数据库建模人员对其相当熟悉。虽然分区技术可以实现很多效果,但其主要目的是为了在特定的SQL操作中减少数据读写的总量以缩减响应时间。
分区主要有两种形式://这里一定要注意行和列的概念(row是行,column是列)
(PS: 物理数据库设计技术。执行特定的SQL语句时,通过一个好的物理数据库设计技术之后可以减少IO读写操作)
二、在MySQL 5.1中进行分区
MySQL5.1中最激动人心的新特性应该就是对水平分区的支持了。这对MySQL的使用者来说确实是个好消息,而且她已经支持分区大部分模式:
Range(范围) – 这种模式允许DBA将数据划分不同范围。例如DBA可以将一个表通过年份划分成三个分区,80年代(1980's)的数据,90年代(1990's)的数据以及任何在2000年(包括2000年)后的数据。
Hash(哈希) – 这中模式允许DBA通过对表的一个或多个列的Hash Key进行计算,最后通过这个Hash码不同数值对应的数据区域进行分区,。例如DBA可以建立一个对表主键进行分区的表。
Key(键值) – 上面Hash模式的一种延伸,这里的Hash Key是MySQL系统产生的。
List(预定义列表) – 这种模式允许系统通过DBA定义的列表的值所对应的行数据进行分割。例如:DBA建立了一个横跨三个分区的表,分别根据2004年2005年和2006年值所对应的数据。
Composite(复合模式) - 很神秘吧,哈哈,其实是以上模式的组合使用而已,就不解释了。举例:在初始化已经进行了Range范围分区的表上,我们可以对其中一个分区再进行hash哈希分区。
分区带来的好处太多太多了,有多少?俺也不知道,自己猜去吧,要是觉得没有多少就别用,反正俺也不求你用。不过在这里俺强调两点好处:
1、性能的提升(Increased performance)
- 在扫描操作中,如果MySQL的优化器知道哪个分区中才包含特定查询中需要的数据,它就能直接去扫描那些分区的数据,而不用浪费很多时间扫描不需要的地方 了。需要举个例子?好啊,百万行的表划分为10个分区,每个分区就包含十万行数据,那么查询分区需要的时间仅仅是全表扫描的十分之一了,很明显的对比。同 时对十万行的表建立索引的速度也会比百万行的快得多得多。如果你能把这些分区建立在不同的磁盘上,这时候的I/O读写速度就“不堪设想”(没用错词,真的 太快了,理论上100倍的速度提升啊,这是多么快的响应速度啊,所以有点不堪设想了)了。
2、对数据管理的简化(Simplified data management)
- 分区技术可以让DBA对数据的管理能力提升。通过优良的分区,DBA可以简化特定数据操作的执行方式。例如:DBA在对某些分区的内容进行删除的同时能保证余下的分区的数据完整性(这是跟对表的数据删除这种大动作做比较的)。
此外分区是由MySQL系统直接管理的,DBA不需要手工的去划分和维护。例如:这个例如没意思,不讲了,如果你是DBA,只要你划分了分区,以后你就不用管了就是了。
站在性能设计的观点上,俺们对以上的内容也是相当感兴趣滴。通过使用分区和对不同的SQL操作的匹配设计,数据库的性能一定能获得巨大提升。下面咱们一起用用这个MySQL 5.1的新功能看看。
下面所有的测试都在Dell Optiplex box with a Pentium 4
3.00GHz processor, 1GB of RAM机器上(炫耀啊……),Fedora Core 4和MySQL
三、如何进行实际分区
看看分区的实际效果吧。我们建立几个同样的MyISAM引擎的表,包含日期敏感的数据,但只对其中一个分区。分区的表(表名为part_tab)我们采用Range范围分区模式,通过年份进行分区:
mysql> CREATE TABLE part_tab
-> ( c1 int default NULL,
-> c2
varchar(30) default NULL,
-> c3
date default NULL
->
-> ) engine=myisam
-> PARTITION BY RANGE (year(c3)) (PARTITION p0
VALUES LESS THAN (1995),
-> PARTITION p1 VALUES LESS THAN (1996) ,
PARTITION p2 VALUES LESS THAN (1997) ,
-> PARTITION p3 VALUES LESS THAN (1998) ,
PARTITION p4 VALUES LESS THAN (1999) ,
-> PARTITION p5 VALUES LESS THAN (2000) ,
PARTITION p6 VALUES LESS THAN (2001) ,
-> PARTITION p7 VALUES LESS THAN (2002) ,
PARTITION p8 VALUES LESS THAN (2003) ,
-> PARTITION p9 VALUES LESS THAN (2004) ,
PARTITION p10 VALUES LESS THAN (2010),
-> PARTITION p11 VALUES LESS THAN MAXVALUE );
Query OK, 0 rows affected (0.00
sec)
注意到了这里的最后一行吗?这里把不属于前面年度划分的年份范围都包含了,这样才能保证数据不会出错,大家以后要记住啊,不然数据库无缘无故出错你就爽了。那下面我们建立没有分区的表(表名为no_part_tab):
mysql> create table no_part_tab
-> (c1
int(11) default NULL,
-> c2
varchar(30) default NULL,
-> c3 date
default NULL) engine=myisam;
Query OK, 0 rows affected (0.02
sec)
下 面咱写一个存储过程(感谢Peter Gulutzan给的代码,如果大家需要Peter Gulutzan的存储过程教程的中文翻译也可以跟我要,chenpengyi◎gmail.com),它能向咱刚才建立的已分区的表中平均的向每个分区 插入共8百万条不同的数据。填满后,咱就给没分区的克隆表中插入相同的数据:
mysql> delimiter //
mysql> CREATE PROCEDURE
load_part_tab()
-> begin
-> declare v int default 0;
-> while v <
8000000
-> do
-> insert into part_tab
-> values (v,'testing partitions',adddate('
-> set
v = v + 1;
-> end
while;
-> end
-> //
Query OK, 0 rows affected (0.00
sec)
mysql> delimiter ;
mysql> call load_part_tab();
Query OK, 1 row affected (8 min
17.75 sec)
mysql> insert into no_part_tab
select * from part_tab;
Query OK, 8000000 rows affected
(51.59 sec)
Records: 8000000 Duplicates:
0 Warnings: 0
表都准备好了。咱开始对这两表中的数据进行简单的范围查询吧。先分区了的,后没分区的,跟着有执行过程解析(MySQL Explain命令解析器),可以看到MySQL做了什么:
PS:第一种情况进行表分区处理可以。
mysql> select count(*) from
no_part_tab where
-> c3 >
date '
+----------+
| count(*) |
+----------+
| 795181 |
+----------+
1 row in set (38.30 sec)
mysql> select count(*) from
part_tab where
-> c3 >
date '
+----------+
| count(*) |
+----------+
| 795181 |
+----------+
1 row in set (3.88 sec)
PS:看得出来通过表分区技术可以大幅提升性能
mysql> explain select count(*)
from no_part_tab where
-> c3 >
date '
*************************** 1. row
***************************
id: 1
select_type: SIMPLE
table: no_part_tab
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 8000000
Extra: Using where
1 row in set (0.00 sec)
mysql> explain partitions
select count(*) from part_tab where
-> c3 >
date '
*************************** 1. row
***************************
id: 1
select_type: SIMPLE
table: part_tab
partitions: p1
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 798458
Extra: Using where
1 row in set (0.00 sec)
从上面结果可以容易看出,设计恰当表分区能比非分区的减少90%的响应时间。而命令解析Explain程序也告诉我们在对已分区的表的查询过程中仅对第一个分区进行了扫描,其他都跳过了。
哔厉吧拉,说阿说……反正就是这个分区功能对DBA很有用拉,特别对VLDB和需要快速反应的系统。
以上内容转自
(明天写一份测试报告对性能表提出表分区技术实现)
chinaunix网友2010-02-02 21:30:46
http://dev.mysql.com/doc/refman/5.1/en/partitioning.html 结合此文章写一篇表分区文章