- 浏览: 982172 次
- 性别:
- 来自: 杭州
文章分类
- 全部博客 (826)
- 硬件 (8)
- 软件 (24)
- 软件工程 (34)
- JAVA (229)
- C/C++/C# (77)
- JavaScript (8)
- PHP (1)
- Ruby (3)
- MySQL (14)
- 数据库 (19)
- 心情记事 (12)
- 团队管理 (19)
- Hadoop (1)
- spring (22)
- mybatis(ibatis) (7)
- tomcat (16)
- velocity (0)
- 系统架构 (6)
- JMX (8)
- proxool (1)
- 开发工具 (16)
- python (10)
- JVM (27)
- servlet (5)
- JMS (26)
- ant (2)
- 设计模式 (5)
- 智力题 (2)
- 面试题收集 (1)
- 孙子兵法 (16)
- 测试 (1)
- 数据结构 (7)
- 算法 (22)
- Android (11)
- 汽车驾驶 (1)
- lucene (1)
- memcache (12)
- 技术架构 (7)
- OTP-Erlang (7)
- memcached (17)
- redis (20)
- 浏览器插件 (3)
- sqlite (3)
- Heritrix (9)
- Java线程 (1)
- scala (0)
- Mina (6)
- 汇编 (2)
- Netty (15)
- libevent (0)
- CentOS (12)
- mongod (5)
- mac os (0)
最新评论
-
kingasdfg:
你这里面存在一个错误添加多个任务 应该是这样的 /** * ...
Quartz的任务的临时启动和暂停和恢复【转】 -
kyzeng:
纠正一个错误,long型对应的符号是J,不是L。
Jni中C++和Java的参数传递 -
zhaohaolin:
抱歉,兄弟,只是留下作记录,方便学习,如果觉得资料不好,可以到 ...
netty的个人使用心得【转】 -
cccoooccooco:
谢谢!自己一直以为虚机得使用网线才可以与主机连接呢。。
主机网卡无网线连接与虚拟机通信 -
yuqilin001:
要转别人的东西,请转清楚点嘛,少了这么多类,误人子弟
netty的个人使用心得【转】
通过分区(Partition)提升MySQL性能
——MySQL5.1新特性翻译系列
几年前,俺写过一篇题为“The Foundation of Excellent Performance”的文章(现在仍然可以在http://www.tdan.com/i016fe03.htm看到),俺对SQL语句是影响数据库驱动系统性能的第一要素的观点有点质疑。其实在那时我在文章中就坚信数据库的物理设计在对高级数据库的性能影响上远比其他因素重要。同时俺还给大家看了Oracle的研究,他们解释了为什么拙劣的物理设计是数据库停机(无论是有计划的还是没计划的)背后的主要原因。这么多年都过来啦(幸好没多少人朝俺扔砖头),俺的观点是改变了一些,但在这点上俺还是坚持DBA如果想要高性能的数据库就必须在数据库的物理设计上多思考的观点,这样才能减少响应时间使终端用户满意而不是引来骂声一片。(陈朋奕语:不要那么严肃,嘿嘿)
俺今天这么激动又想写文章的原因是MySQL5.1的发布带来了设计超强动力数据库的强有力的武器,任何MySQL的DBA都应该尽快学习并使用它。俺觉得如果能很好滴使用这个5.1版带来的新特性,DBA可以使自己管理的VLDB(不知道什么是VLDB?告诉你,是好大好大的数据库的意思,Very Large DB)或数据仓库奇迹般的获得巨大的性能提升。
什么是数据库分区?
数据库分区是一种物理数据库设计技术,DBA和数据库建模人员对其相当熟悉。虽然分区技术可以实现很多效果,但其主要目的是为了在特定的SQL操作中减少数据读写的总量以缩减响应时间。
分区主要有两种形式://这里一定要注意行和列的概念(row是行,column是列)
-
水平分区(Horizontal Partitioning) 这种形式分区是对表的行进行分区,通过这样的方式不同分组里面的物理列分割的数据集得以组合,从而进行个体分割(单分区)或集体分割(1个或多个分区)。所有在表中定义的列在每个数据集中都能找到,所以表的特性依然得以保持。
举个简单例子:一个包含十年发票记录的表可以被分区为十个不同的分区,每个分区包含的是其中一年的记录。(朋奕注:这里具体使用的分区方式我们后面再说,可以先说一点,一定要通过某个属性列来分割,譬如这里使用的列就是年份) -
垂直分区(Vertical Partitioning) 这种分区方式一般来说是通过对表的垂直划分来减少目标表的宽度,使某些特定的列被划分到特定的分区,每个分区都包含了其中的列所对应的行。
举个简单例子:一个包含了大text和BLOB列的表,这些text和BLOB列又不经常被访问,这时候就要把这些不经常使用的text和BLOB了划分到另一个分区,在保证它们数据相关性的同时还能提高访问速度。
在数据库供应商开始在他们的数据库引擎中建立分区(主要是水平分区)时,DBA和建模者必须设计好表的物理分区结构,不要保存冗余的数据(不同表中同时都包含父表中的数据)或相互联结成一个逻辑父对象(通常是视图)。这种做法会使水平分区的大部分功能失效,有时候也会对垂直分区产生影响。
在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哈希分区。
Hash(哈希) – 这中模式允许DBA通过对表的一个或多个列的Hash Key进行计算,最后通过这个Hash码不同数值对应的数据区域进行分区,。例如DBA可以建立一个对表主键进行分区的表。
Key(键值) – 上面Hash模式的一种延伸,这里的Hash Key是MySQL系统产生的。
List(预定义列表) – 这种模式允许系统通过DBA定义的列表的值所对应的行数据进行分割。例如:DBA建立了一个横跨三个分区的表,分别根据2004年2005年和2006年值所对应的数据。
Composite(复合模式) - 很神秘吧,哈哈,其实是以上模式的组合使用而已,就不解释了。举例:在初始化已经进行了Range范围分区的表上,我们可以对其中一个分区再进行hash哈希分区。
分区带来的好处太多太多了,有多少?俺也不知道,自己猜去吧,要是觉得没有多少就别用,反正俺也不求你用。不过在这里俺强调两点好处:
性能的提升(Increased performance) - 在扫描操作中,如果MySQL的优化器知道哪个分区中才包含特定查询中需要的数据,它就能直接去扫描那些分区的数据,而不用浪费很多时间扫描不需要的地方了。需要举个例子?好啊,百万行的表划分为10个分区,每个分区就包含十万行数据,那么查询分区需要的时间仅仅是全表扫描的十分之一了,很明显的对比。同时对十万行的表建立索引的速度也会比百万行的快得多得多。如果你能把这些分区建立在不同的磁盘上,这时候的I/O读写速度就“不堪设想”(没用错词,真的太快了,理论上100倍的速度提升啊,这是多么快的响应速度啊,所以有点不堪设想了)了。
对数据管理的简化(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 5.1.6 alpha上运行通过。
站在性能设计的观点上,俺们对以上的内容也是相当感兴趣滴。通过使用分区和对不同的SQL操作的匹配设计,数据库的性能一定能获得巨大提升。下面咱们一起用用这个MySQL 5.1的新功能看看。
下面所有的测试都在Dell Optiplex box with a Pentium 4 3.00GHz processor, 1GB of RAM机器上(炫耀啊……),Fedora Core 4和MySQL 5.1.6 alpha上运行通过。
如何进行实际分区
看看分区的实际效果吧。我们建立几个同样的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('1995-01-01',(rand(v)*36520) mod 3652));
-> 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做了什么:
mysql> select count(*) from no_part_tab where
-> c3 > date '1995-01-01' and c3 < date '1995-12-31';
+----------+
| count(*) |
+----------+
| 795181 |
+----------+
1 row in set (38.30 sec)
mysql> select count(*) from part_tab where
-> c3 > date '1995-01-01' and c3 < date '1995-12-31';
+----------+
| count(*) |
+----------+
| 795181 |
+----------+
1 row in set (3.88 sec)
mysql> explain select count(*) from no_part_tab where
-> c3 > date '1995-01-01' and c3 < date '1995-12-31'\G
*************************** 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 '1995-01-01' and c3 < date '1995-12-31'\G
*************************** 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和需要快速反应的系统。
对Vertical Partitioning的一些看法
虽然MySQL 5.1自动实现了水平分区,但在设计数据库的时候不要轻视垂直分区。虽然要手工去实现垂直分区,但在特定场合下你会收益不少的。例如在前面建立的表中,VARCHAR字段是你平常很少引用的,那么对它进行垂直分区会不会提升速度呢?咱们看看测试结果:
mysql> desc part_tab;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c1 | int(11) | YES | | NULL | |
| c2 | varchar(30) | YES | | NULL | |
| c3 | date | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.03 sec)
mysql> alter table part_tab drop column c2;
Query OK, 8000000 rows affected (42.20 sec)
Records: 8000000 Duplicates: 0 Warnings: 0
mysql> desc part_tab;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c1 | int(11) | YES | | NULL | |
| c3 | date | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> select count(*) from part_tab where
-> c3 > date '1995-01-01' and c3 < date '1995-12-31';
+----------+
| count(*) |
+----------+
| 795181 |
+----------+
1 row in set (0.34 sec)
在设计上去掉了VARCHAR字段后,不止是你,俺也发现查询响应速度上获得了另一个90%的时间节省。所以大家在设计表的时候,一定要考虑,表中的字段是否真正关联,又是否在你的查询中有用?
补充说明
这么简单的文章肯定不能说全MySQL 5.1 分区机制的所有好处和要点(虽然对自己写文章水平很有信心),下面就说几个感兴趣的:
- 支持所有存储引擎(MyISAM, Archive, InnoDB, 等等)
- 对分区的表支持索引,包括本地索引local indexes,对其进行的是一对一的视图镜像,假设一个表有十个分区,那么其本地索引也包含十个分区。
- 关于分区的元数据Metadata的表可以在INFORMATION_SCHEMA数据库中找到,表名为PARTITIONS。
- All SHOW 命令支持返回分区表以及元数据的索引。
-
对其操作的命令和实现的维护功能有(比对全表的操作还多):
- ADD PARTITION
- DROP PARTITION
- COALESCE PARTITION
- REORGANIZE PARTITION
- ANALYZE PARTITION
- CHECK PARTITION
- OPTIMIZE PARTITION
- REBUILD PARTITION
- REPAIR PARTITION
站在性能主导的观点上来说,MySQL 5.1的分区功能能给数据性能带来巨大的提升的同时减轻DBA的管理负担,如果分区合理的话。如果需要更多的资料可以去http://dev.mysql.com/doc/refman/5.1/en/partitioning.html或http://forums.mysql.com/list.php?106获得相关资料。
关于MySQL分区的使用方法很快发布上来,这里有什么错误欢迎指出,或给我来信
——2006-05-05陈朋奕
(http://www.fanqiang.com)
发表评论
-
Linux下MySql出现#1036 - Table ' user' is read only 错误解决方法(转)
2012-10-10 21:27 1469前几天网站备案被注销 ... -
修改MySQL数据库的root密码(转)
2012-10-10 20:58 772知识库 修改MySQL数据库的r ... -
mysql 主从同步实例-从同步备份步骤
2011-07-13 10:25 14152009-09-13 16:28 环境: A、B的MyS ... -
mysql 主从同步原理
2011-07-13 10:24 9542009-09-13 16:12 Repli ... -
MySQL复制和性能优化
2011-06-16 23:01 1213复制(Replication):通 ... -
轻松构建MySQL高可用集群系统
2011-06-16 21:59 982一、 MySQL复制的实现 ... -
BoneCP数据源配置详解
2011-05-13 17:23 2057BoneCP是一个快速,开源的数据库连接池。帮你管理数据连接让 ... -
Java中的BoneCP数据库连接池用法
2011-05-13 17:22 1305在2006年8月的时候我在项目中使用过Proxool这个Jav ... -
基于Hash算法的Mysql分表处理
2011-04-16 13:29 1125下面为您介绍的Mysql分表处理是基于Hash算法的,在了解该 ... -
RDMA技术分析
2011-02-28 19:24 1529RDMA技术分析 1 什么是RDMA ... -
MySQL海量数据优化
2011-02-26 09:52 1407MySQL海量数据优化 经常见到有人对M ... -
data too long for column
2008-10-17 00:09 1728mysql 写道 从MySql5中运行本地脚本创建数据库,当插 ... -
MySQL:InnoDB和MyISAM的差别
2008-10-17 00:05 1871InnoDB和MyISAM是在使用MySQL最常用的两个表类型 ...
相关推荐
MYSQL 通过分区(Partition)提升MySQL性能,看清楚,不要浪费分数,是MYSQL,不是SQL SERVER2000,有需要的可以下载.
如果想在已经建好的表上进行分区,如果使用alter添加分区的话,mysql会提示错误: 代码如下: ERROR 1505 <HY000> Partition management on a not partitioned table is not possible 正确的方法是新建一个具有分区...
表分区的测试使用,主要内容来自于其他博客文章以及mysql5.1的参考手册mysql测试版本:mysql5.5.28mysql物理存储文件(有mysql配置的datadir决定存储路径)格式简介数据库engine为MYISAMfrm表结构文件,myd表数据...
MySQL Partition Manager 是雅虎开源的 MySQL 分区管理器。它可以帮助你以最小的配置自动创建、维护、清除分区。 标签:MySQL
MySQL交换分区的实例详解 前言 在介绍交换分区之前,我们先了解一下 mysql 分区。 数据库的分区有两种:水平分区和垂直分区。而MySQL暂时不支持垂直分区,因此接下来说的都是水平分区。水平分区即:以行为单位对表...
MySQL从5.1.3开始支持Partition。 分区和手动分表对比 手动分表 分区 多张数据表 一张数据表 重复数据的风险 没有数据重复的风险 写入多张表 写入一张表 没有统一的约束限制 强制的约束限制 MySQL支持...
mysql 分区 partition 当进行分区操作,了解对性能所产生的影响是非常有帮助的: 1.创建分区表比无分区的正规表要稍微慢些; 2.通过alter table….drop partition语句进行删除比delete语句要快些; 3.在range或list...
解释: - 在上述代码中,我们首先创建了一个名为`orders`的分区表,该表包含三个字段:`id`、`order_date`和`customer_id`。...由于`orders`表是分区表,MySQL会根据分区的范围选择需要扫描的分区,从而提高查询性能。
01-MySQL优化大的思路.wmv 02-Awk简洁入门.wmv 03-观察服务器周期性变化.wmv 04-观察MySQL进程状态.wmv 05-列选取原则.wmv 06-多列索引生效规则.wmv 07-多列索引实验.wmv ...31-Partition分区.wmv 32-事务讲解.wmv
MySQL在创建表的时候可以通过使用PARTITION BY子句定义每个分区存放的数据。在执行查询的时候,优化器根据分区定义过滤那些没有我们需要的数据的分区,这样查询就可以无需扫描所有分区,只需要查找包含需要数据的...
mysql可以通过下面语句判断是否支持分区: SHOW VARIABLES LIKE ‘%partition%’; 如果输出: have_partitioning YES 表示支持分区。 或者通过: SHOW PLUGINS; 显示所有插件,如果有partition ACTIVE STORAGE ...
主要介绍了Mysql数据表分区技术PARTITION浅析,分别介绍了 Mysql 中的分区技术 RANGE、LIST、 HASH,需要的朋友可以参考下
2,方便维护,通过删除分区来删除老的数据。3,分区数据可以被分布到不同的物理位置,可以做分布式有效利用多个硬盘驱动器。 MySQL可以建立四种分区类型的分区: RANGE 分区:基于属于一个给定连续区间的列值,把...
前言 分区是指根据一定的规则将一个...mysql5.7以后查询语句支持指定分区例如:“ SELECT * FROM t PARTITION (p0,p1) WHERE c < 5 ”指定分区同样适用DELETE, INSERT, REPLACE, UPDATE, and LOAD DATA, LOAD XML.
数据库单表到达一定量后,性能会有衰减,像mysql\sql server等犹为明显,所以需要把这些数据进行分区处理。同时有时候可能出现数据剥离什么的,分区表就更有用处了! MySQL 5.1 中新增的分区(Partition)功能就开始...
MySQL在创建表的时候可以通过使用PARTITION BY子句定义每个分区存放的数据。在执行查询的时候,优化器根据分区定义过滤那些没有我们需要的数据的分区,这样查询就可以无需扫描所有分区,只需要查找包含需要数据的...
mysql表分区的建立,索引的建立,原理说明,还有就是实例的演示
MySQL分区检查前置条件-- 查看分区插件是否激活 partition -> active-- 查看分区插件是否激活方法2,plugin_status ->
在最近的项目中,我们需要保存大量的数据,而且这些数据是有有效期的,为了提供查询效率以及快速删除过期数据,我们选择了MySQL的分区机制。把数据按照时间进行分区。 分区类型 Range分区:最为常用,基于属于一个...