mysql分区的功能,

　　Partition-fanqiang.com的《提高MySQL性能》

　　通过分区提高MySQL性能作者：foxcai来源：foxcai的博客(2006-05-08 14:30:34)

　　通过分区提高MySQL性能—— MySQL 5.1翻译系列的新特性几年前，我写过一篇名为《卓越性能的基础》的文章(在http://www.tdan.com/i016fe03.htm中仍能看到)，我对SQL语句是影响数据库驱动系统性能的第一因素的观点提出了质疑。其实当时我坚信数据库的物理设计远比高级数据库性能的其他因素重要。同时，我也向你们展示了Oracle的研究，他们解释了为什么糟糕的物理设计是数据库停机(无论是计划内还是计划外)背后的主要原因。这么多年过去了(还好没有多少人向我扔砖头)，我的观点有了一点改变，但我还是坚持认为，DBA如果想要一个高性能的数据库，应该多考虑数据库的物理设计，这样才能减少响应时间，让最终用户满意，而不是招来谩骂。(陈：别这么严肃，呵呵)今天之所以这么激动，想写一篇文章，是因为MySQL5.1的发布带来了设计超动态数据库的有力武器。任何一个MySQL DBA都应该尽快学习和使用。我想如果我能利用好这个5.1版本带来的新特性，DBA可以让VLDB自己管理(我想知道VLDB是什么？我告诉你，这意味着“超大型数据库”或“数据仓库”奇迹般地实现了巨大的性能提升。

　　什么是数据库分区？数据库分区是一种物理数据库设计技术，DBA和数据库建模人员对此很熟悉。虽然分区技术可以达到很多效果，但是它的主要目的是减少具体SQL操作中的数据读写总量，以缩短响应时间。分区主要有两种形式：//这里一定要注意行和列的概念(行是行，列是列)。这种形式的分区是对表中的行进行分区。这样，由不同组中的物理列划分的数据集可以被组合，以便单独地(单个分区)或共同地(一个或多个分区)划分它们。表中定义的所有列都可以在每个数据集中找到，因此表的特征仍然保持不变。

　　举个简单的例子：一个包含十年发票记录的表可以分成十个不同的分区，每个分区包含一年的记录。(朋友们注意：我们后面会谈到这里使用的具体分区方法。可以先说一下，一定是用某个属性列分区的，比如这里用的列是年份。)垂直分区一般通过对表进行垂直分区来减小目标表的宽度，使某些特定的列被分成特定的分区，每个分区包含与列对应的行。

　　举一个简单的例子：一个包含大量文本和BLOB列的表，这些文本和BLOB列不经常被访问。这时候就需要将这些不常用的文本和blobs划分到另一个分区中，这样可以在保证其数据相关性的同时提高访问速度。当数据库供应商开始在其数据库引擎中构建分区(主要是水平分区)时，DBA和建模人员必须设计表的物理分区结构，并且不保存冗余数据(不同的表同时包含来自父表的数据)或将它们联接成一个逻辑父对象(通常是视图)。这样会让水平分区的大部分功能失效，有时候还会影响到垂直分区。

　　MySQL5.1中的分区MySQL 5.1中最令人兴奋的新特性应该是对水平分区的支持。这对MySQL用户来说确实是个好消息，她已经支持了大部分分区模式：Range——这种模式允许DBA将数据划分到不同的范围。例如，DBA可以按年份将一个表分为三个部分，即20世纪80年代的数据、20世纪90年代的数据和2000年以后的任何数据(包括2000年)。

　　Hash(散列)该模式允许DBA计算表中一列或多列的散列键，并最终划分与该散列码的不同值相对应的数据区域。例如，DBA可以创建一个对表的主键进行分区的表。

　　Key(Key value)——上述哈希模式的扩展，其中哈希键由MySQL系统生成。

　　List(预定义列表)该模式允许系统根据DBA定义的列表值，按行划分数据。例如，DBA根据分别对应于2004、2005和2006年值的数据，建立了一个跨越三个分区的表。

　　复合(复合模式)——很神秘吧？哈哈，其实是以上几种模式的结合，就不解释了。例如，当初始化一个已经在Range范围内分区的表时，我们可以散列其中一个分区。

　　分区带来的好处太多了。有多少？我不知道。自己猜。如果你觉得不多，就不要用。反正我不会让你用。但这里我强调两个优点：

　　性能提升)——在扫描操作中，如果MySQL的优化器知道哪个分区包含了特定查询中需要的数据，就可以直接扫描那些分区的数据，而不用浪费大量时间扫描不必要的地方。需要举例吗？好吧，一个百万行的表被分成10个分区，每个分区包含10万行数据，那么查询分区所需的时间只有全表扫描的十分之一，这是一个明显的对比。同时，十万行的表的索引速度会比一百万行的表快很多。如果能把这些分区设置在不同的磁盘上，此时的I/O读写速度将是“难以想象的”(不使用错别字，真的太快了。理论上快100倍。这是多么快的反应速度啊，所以有点不可想象)。简化数据管理)-分区技术可以提高DBA管理数据的能力。通过出色的分区，DBA可以简化特定数据操作的执行。例如，DBA可以在删除某些分区的内容的同时，确保其余分区的数据完整性(这是与从表中删除数据的大动作相比较的)。另外，分区由MySQL系统直接管理，DBA不需要手动划分和维护。比如这个很无聊，就不要说了。如果你是DBA，只要分区，以后就不用担心了。

　　从性能设计的角度来看，我们对以上内容也颇感兴趣。通过对不同的SQL操作使用分区和匹配设计，数据库的性能将得到极大的提高。我们来看看MySQL 5.1的这个新功能。

　　以下所有测试都是在配备奔腾4 3.00 GHz处理器、1gb RAM机器(展示…)、Fedora Core 4和MySQL 5.1.6 alpha的Dell OptiPlex Box上运行的。

　　如何进行实际分区看看分区的实际效果吧。我们建立几个同样的米沙姆引擎的表，包含日期敏感的数据，但只对其中一个分区。分区的表（表名为part_tab)我们采用范围范围分区模式，通过年份进行分区：MySQL创建表part _ tab-(C1 int默认NULL，- c2 varchar(30)默认NULL，-C3日期默认NULL - )engine=myisam-按范围分区(年份(c3))(分区p0蛋白值小于(1995),-分区第一亲代值小于(1996),分区p2值小于(1997),-分区p3值小于(1998),分区p4值小于(1999),-分区孕烯醇酮值小于(2000),分区p6值小于(2001) -分区p7值小于(2002),分区p8值小于(2003),-分区p9值小于(2004),分区p10值小于(2010),-分区p11值小于最大值)；查询正常，0行受影响(0.00秒)注意到了这里的最后一行吗？这里把不属于前面年度划分的年份范围都包含了，这样才能保证数据不会出错，大家以后要记住啊，不然数据库无缘无故出错你就爽了。那下面我们建立没有分区的表（表名为no_part_tab): mysql创建表no_part_tab - (c1 int(11)默认NULL，- c2 varchar(30)默认空，- c3日期默认NULL)engine=myisam；查询正常，0行受影响(0.02秒)下面咱写一个存储过程（感谢彼得古卢赞给的代码，如果大家需要彼得古卢赞的存储过程教程的中文翻译也可以跟我要，chenpengyigmail.com)，它能向咱刚才建立的已分区的表中平均的向每个分区插入共8百万条不同的数据。填满后，咱就给没分区的克隆表中插入相同的数据：mysql分隔符//mysql创建过程load _ part _ tab()-begin-declare v int默认0;-while v 8000000-do-insert into part _ tab-values(v，’测试分区，adddate(1995-01-01 ，(rand(v)* 36520)mod 3652))；-设置v=v 1；-end while；- end - //查询好的，0行受影响(0.00秒)mysql分隔符；关系型数据库调用load _ part _ tab()；查询正常，1行受影响(8分17.75秒)mysql插入到no _ part _ tab select * from part _ tab；查询正常，8000000行受影响(51.59秒)记录：8000000重复：0警告：0

　　表都准备好了。咱开始对这两表中的数据进行简单的范围查询吧。先分区了的，后没分区的，跟着有执行过程解析(MySQL解释命令解析器),可以看到关系型数据库做了什么：MySQL select count(*)from no _ part _ tab where-C3日期 1995-01-01 和C3日期“1995年12月31日”；- count(*) - 795181 -集合中的一行(38.30秒)MySQL select count(*)from part _ tab where-C3日期 1995-01-01 和C3日期“1995年12月31日”；- count(*) - 795181 -集合中的一行(3.88秒)MySQL解释select count(*)from no _ part _ tab where-C3日期 1995-01-01 和C3日期 1995年12月31日 \ G * * * * * * * * * * * * * * * * * * * * .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日期 1995-01-01 和C3日期 1995年12月31日 \ G * * * * * * * * * * * * * * * * * * * * * * * * * * .row * * * * * * * * * * * * * * * * * * * * * * * id:1 select _ type:SIMPLE table:part _ tab partitions:P1类型：ALL possible _ keys:NULL key:NULL key _ len:NULL ref:NULL rows:798458 Extra:Using where 1 row in set(0.00 sec)从上面结果可以容易看出，设计恰当表分区能比非分区的减少90%的响应时间。而命令解析解释程序也告诉我们在对已分区的表的查询过程中仅对第一个分区进行了扫描，其他都跳过了。哔厉吧拉，说阿说……反正就是这个分区功能对工商管理学博士(工商管理博士)很有用拉，特别对超大型数据库和需要快速反应的系统。

　　对垂直分区的一些看法虽然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 --set中的3行(0.03秒)MySQL alter table part _ tab drop column C2；查询正常，8000000行受影响(42.20秒)记录：8000000副本：0警告：0 MySQL desc part _ tab；--Field Type Null Key Default Extra --C1 int(11) YES Null C3 date YES Null --集合中的2行(0.00秒)MySQL select count(*)from part _ tab where-C3 date 1995-01-01 和C3 date 1995-12-31 ；- count (*) - 795181 -集合中的1行(0.34秒)被设计为删除了VARCHAR字段。不仅是你，我也发现查询响应速度提高了。因此，在设计表时，必须考虑表中的字段在查询中是否真正相关和有用。

　　附加备注

　　这么简单的一篇文章当然不能说全MySQL 5.1分区机制的所有好处和要点(虽然我对自己的写作水平很自信)。下面是几个有趣的：支持所有存储引擎(MyISAM、Archive、InnoDB等)的表。)支持分区的索引，包括本地索引，它们以一对一的视图进行镜像。假设一个表有十个分区，它的本地索引也包含十个分区。关于分区的元数据的表可以在INFORMATION_SCHEMA数据库中找到，它的名字是PARTITIONS。show命令支持返回分区表的索引和元数据。其操作和实现的维护功能的命令是(多于整个表的操作):

　　添加分区丢弃分区合并分区重组分区分析分区检查分区优化分区重建分区修复部分从面向性能的角度来看，MySQL 5.1的分区功能可以大大提高数据性能，减轻DBA的管理负担，如果分区合理的话。如果你需要更多的信息，你可以去http://dev.mysql.com/doc/refman/5.1/en/partitioning.html或http://forums.mysql.com/list.php? 06获取相关信息。MySQL分区的用法即将发布。如有错误，请在此指出，或致电——2006-05-05陈与我联系。

　　(http://www.fanqiang.com)

郑重声明：本文由网友发布，不代表盛行IT的观点，版权归原作者所有，仅为传播更多信息之目的，如有侵权请联系，我们将第一时间修改或删除，多谢。