Category Archives: Database

SQL Server 2005中的分区表（二）：如何添加、查询、修改分区表中的数据

在创建完分区表后，可以向分区表中直接插入数据，而不用去管它这些数据放在哪个物理上的数据表中。接上篇文章，我们在创建好的分区表中插入几条数据： 从以上代码中可以看出，我们一共在数据表中插入了13条数据，其中第1至3条数据是插入到第1个物理分区表中的；第4、5条数据是插入到第2个物理分区表中的；第6至8条数据是插入到第3个物理分区表中的；第9至11条数据是插入到第4个物理分区表中的；第12、13条数据是插入到第5个物理分区表中的。 从SQL语句中可以看出，在向分区表中插入数据方法和在普遍表中插入数据的方法是完全相同的，对于程序员而言，不需要去理会这13条记录研究放在哪个数据表中。当然，在查询数据时，也可以不用理会数据到底是存放在哪个物理上的数据表中。如使用以下SQL语句进行查询：   [c-sharp] view plaincopy select * from Sale     查询的结果如下图所示： 从上面两个步骤中，根本就感觉不到数据是分别存放在几个不同的物理表中，因为在逻辑上，这些数据都属于同一个数据表。如果你非想知道哪条记录是放在哪个物理上的分区表中，那么就必须使用到$PARTITION函数，这个函数的可以调用分区函数，并返回数据所在物理分区的编号。 说起来有点难懂，不过用起来很简单。$PARTITION的语法是：     $PARTITION.分区函数名(表达式) 假设，你想知道2010年10月1日的数据会放在哪个物理分区表中，你就可以使用以下语句来查看。   [c-sharp] view plaincopy select $PARTITION.partfunSale ('2010-10-1')     在以上语句中，partfunSale()为分区函数名，括号中的表达式必须是日期型的数据或可以隐式转换成日期型的数据，如果要问我为什么，那么就回想一个怎么定义分区函数的吧（CREATE PARTITION FUNCTION partfunSale (datetime)）。在定义partfunSale()函数时，指定了参数为日期型，所以括号中的表达式必须是日期型或可以隐式转换成日期型的数据。以上代码的运行结果如下图所示： 在该图中可以看出，分区函数返回的结果为2，也就是说，2010年10月1日的数据会放在第2个物理分区表中。   再进一步考虑，如果想具体知道每个物理分区表中存放了哪些记录，也可以使用$PARTITION函数。因为$PARTITION函数可以得到物理分区表的编号，那么只要将$PARTITION.partfunSale(SaleTime)做为where的条件使用即可，如以下代码 所示：   [c-sharp] view plaincopy select * from Sale where $PARTITION.partfunSale(SaleTime)=1 select * from Sale where $PARTITION.partfunSale(SaleTime)=2 select * from Sale where $PARTITION.partfunSale(SaleTime)=3 select * from Sale where $PARTITION.partfunSale(SaleTime)=4 select * from Sale where $PARTITION.partfunSale(SaleTime)=5     以上代码的运行结果如下图所示： 从上图中我们可以看到每个分区表中的数据记录情况——和我们插入时设置的情况完全一致。同理可得，如果要统计每个物理分区表中的记录数，可以使用如下代码：   [c-sharp] view plaincopy select $PARTITION.partfunSale(SaleTime) as 分区编号,count(id) as 记录数 from Sale group by $PARTITION.partfunSale(SaleTime)     以上代码的运行结果如下图所示：   除了在插入数据时程序员不需要去考虑分区表的物理情况之外，就是连修改数据也不需要考虑。SQL Server会自动将记录从一个分区表移到另一个分区表中，如以下代码所示：   [c-sharp] view plaincopy --统计所有分区表中的记录总数 select $PARTITION.partfunSale(SaleTime) as 分区编号,count(id) as 记录数 from Sale group by $PARTITION.partfunSale(SaleTime) --修改编号为1的记录，将时间改为2019年1月1日 update Sale set SaleTime=’2019-1-1′ where id=1 --重新统计所有分区表中的记录总数 select $PARTITION.partfunSale(SaleTime) as 分区编号,count(id) as 记录数 from Sale group by $PARTITION.partfunSale(SaleTime)     在以上代码中，程序员将其中一条数据的时间改变了，从分区函数中可以得知，这条记录应该从第一个分区表移到第五个分区表中，如下图所示。而整个操作过程，程序员是完全不需要干预的。   原创不容易，转载请注明出处。http://blog.csdn.net/smallfools/archive/2009/12/03/4932936.aspx

SQL Server 2005中的分区表（一）：什么是分区表？为什么要用分区表？如何创建分区表？

如果你的数据库中某一个表中的数据满足以下几个条件，那么你就要考虑创建分区表了。   1、数据库中某个表中的数据很多。很多是什么概念？一万条？两万条？还是十万条、一百万条？这个，我觉得是仁者见仁、智者见智的问题。当然数据表中的数据多到查询时明显感觉到数据很慢了，那么，你就可以考虑使用分区表了。如果非要我说一个数值的话，我认为是100万条。 2、但是，数据多了并不是创建分区表的惟一条件，哪怕你有一千万条记录，但是这一千万条记录都是常用的记录，那么最好也不要使用分区表，说不定会得不偿失。只有你的数据是分段的数据，那么才要考虑到是否需要使用分区表。 3、什么叫数据是分段的？这个说法虽然很不专业，但很好理解。比如说，你的数据是以年为分隔的，对于今年的数据而言，你常进行的操作是添加、修改、删除和查询，而对于往年的数据而言，你几乎不需要操作，或者你的操作往往只限于查询，那么恭喜你，你可以使用分区表。换名话说，你对数据的操作往往只涉及到一部分数据而不是所有数据的话，那么你就可以考虑什么分区表了。   那么，什么是分区表呢？ 简单一点说，分区表就是将一个大表分成若干个小表。假设，你有一个销售记录表，记录着每个每个商场的销售情况，那么你就可以把这个销售记录表按时间分成几个小表，例如说5个小表吧。2009年以前的记录使用一个表，2010年的记录使用一个表，2011年的记录使用一个表，2012年的记录使用一个表，2012年以后的记录使用一个表。那么，你想查询哪个年份的记录，就可以去相对应的表里查询，由于每个表中的记录数少了，查询起来时间自然也会减少。 但将一个大表分成几个小表的处理方式，会给程序员增加编程上的难度。以添加记录为例，以上5个表是独立的5个表，在不同时间添加记录的时候，程序员要使用不同的SQL语句，例如在2011年添加记录时，程序员要将记录添加到2011年那个表里；在2012年添加记录时，程序员要将记录添加到2012年的那个表里。这样，程序员的工作量会增加，出错的可能性也会增加。 使用分区表就可以很好的解决以上问题。分区表可以从物理上将一个大表分成几个小表，但是从逻辑上来看，还是一个大表。 接着上面的例子，分区表可以将一个销售记录表分成五个物理上的小表，但是对于程序员而言，他所面对的依然是一个大表，无论是2010年添加记录还是2012年添加记录，对于程序员而言是不需要考虑的，他只要将记录插入到销售记录表——这个逻辑中的大表里就行了。SQL Server会自动地将它放在它应该呆在的那个物理上的小表里。 同样，对于查询而言，程序员也只需要设置好查询条件，OK，SQL Server会自动将去相应的表里查询，不用管太多事了。   这一切是不是很诱人？ 的确，那么我们就可以开始动手创建分区表了。   第一、创建分区表的第一步，先创建数据库文件组，但这一步可以省略，因为你可以直接使用PRIMARY文件。但我个人认为，为了方便管理，还是可以先创建几个文件组，这样可以将不同的小表放在不同的文件组里，既便于理解又可以提高运行速度。创建文件组的方法很简单，打开SQL Server Management Studio，找到分区表所在数据库，右键单击，在弹出的菜单里选择“属性”。然后选择“文件组”选项，再单击下面的“添加”按钮，如下图所示：     第二，创建了文件组之后，还要再创建几个数据库文件。为什么要创建数据库文件，这很好理解，因为分区的小表必须要放在硬盘上，而放在硬盘上的什么地方呢？当然是文件里啦。再说了，文件组中没有文件，文件组还要来有啥用呢？还是在上图的那个界面，选择“文件”选项，然后添加几个文件。在添加文件的时候要注意以下几点： 1、不要忘记将不同的文件放在文件组中。当然一个文件组中也可以包含多个不同的文件。 2、如果可以的话，将不同的文件放在不同的硬盘分区里，最好是放在不同的独立硬盘里。要知道IQ的速度往往是影响SQL Server运行速度的重要条件之一。将不同的文件放在不同的硬盘上，可以加快SQL Server的运行速度。 在本例中，为了方便起见，将所有数据库文件都放在了同一个硬盘下，并且每个文件组中只有一个文件。如下图所示。   第三、创建一个分区函数。这一步是必须的了，创建分区函数的目的是告诉SQL Server以什么方式对分区表进行分区。这一步必须要什么SQL脚本来完成。以上面的例子，我们要将销售表按时间分成5个小表。假设划分的时间为： 第1个小表：2010-1-1以前的数据（不包含2010-1-1）。 第2个小表：2010-1-1（包含2010-1-1）到2010-12-31之间的数据。 第3个小表：2011-1-1（包含2011-1-1）到2011-12-31之间的数据。 第4个小表：2012-1-1（包含2012-1-1）到2012-12-31之间的数据。 第5个小表：2013-1-1（包含2013-1-1）之后的数据。 那么分区函数的代码如下所示：   [c-sharp] view plaincopy CREATE PARTITION FUNCTION partfunSale (datetime) AS RANGE RIGHT FOR VALUES ('20100101′,’20110101′,’20120101′,’20130101')     其中： 1、CREATE PARTITION FUNCTION意思是创建一个分区函数。 2、partfunSale为分区函数名称。 3、AS RANGE RIGHT为设置分区范围的方式为Right，也就是右置方式。 4、FOR VALUES ('20100101′,’20110101′,’20120101′,’20130101')为按这几个值来分区。 这里需要说明的一下，在Values中，’20100101’、’20110101’、’20120101’、’20130101’，这些都是分区的条件。“ 20100101”代表2010年1月1日，在小于这个值的记录，都会分成一个小表中，如表1；而小于或等于’20100101’并且小于’20110101’的值，会放在另一个表中，如表2。以此类推，到最后，所有大小或等于’20130101’的值会放在另一个表中，如表5。 也许有人会问，为什么值“ 20100101”会放在表2中，而不是表1中呢？这是由AS RANGE RIGHT中的RIGHT所决定的，RIGHT的意思是将等于这个值的数据放在右边的那个表里，也就是表2中。如果您的SQL语句中使用的是Left而不是RIGHT，那么就会放在左边的表中，也就是表1中。   第四、创建一个分区方案。分区方案的作用是将分区函数生成的分区映射到文件组中去。分区函数的作用是告诉SQL Server，如何将数据进行分区，而分区方案的作用则是告诉SQL Server将已分区的数据放在哪个文件组中。分区方案的代码如下所示：   [c-sharp] view plaincopy CREATE PARTITION SCHEME partschSale AS PARTITION partfunSale TO (     Sale2009,     Sale2010,     Sale2011,     Sale2012,     Sale2013)     其中： 1、CREATE PARTITION SCHEME意思是创建一个分区方案。 2、partschSale为分区方案名称。 3、AS PARTITION partfunSale说明该分区方案所使用的数据划分条件（也就是所使用的分区函数）为partfunSale。 4、TO后面的内容是指partfunSale分区函数划分出来的数据对应存放的文件组。   […]

第18章：分区

本章讨论MySQL 5.1.中实现的分区。关于分区和分区概念的介绍可以在18.1节，“MySQL中的分区概述”中找到。MySQL 5.1 支持哪几种类型的分区，在18.2节，“分区类型” 中讨论。关于子分区在18.2.5节，“子分区” 中讨论。现有分区表中分区的增加、删除和修改的方法在18.3节，“分区管理” 中介绍。 和分区表一同使用的表维护命令在18.3.3节，“分区维护” 中介绍。 请注意：MySQL 5.1中的分区实现仍然很新（pre-alpha品质），此时还不是可生产的（not production-ready）。 同样，许多也适用于本章：在这里描述的一些功能还没有实际上实现(分区维护和重新分区命令），其他的可能还没有完全如所描述的那样实现(例如, 用于分区的数据目录（DATA DIRECTORY）和索引目录（INDEX DIRECTORY）选项受到Bug #13520) 不利的影响). 我们已经设法在本章中标出这些差异。在提出缺陷报告前，我们鼓励参考下面的一些资源： MySQL 分区论坛这是一个为对MySQL分区技术感兴趣或用MySQL分区技术做试验提供的官方讨论论坛。来自MySQL 的开发者和其他的人，会在上面发表和更新有关的材料。它由分区开发和文献团队的成员负责监控。 分区缺陷报告已经归档在缺陷系统中的、所有分区缺陷的一个列表，而无论这些缺陷的年限、严重性或当前的状态如何。根据许多规则可以对这些缺陷进行筛选，或者可以从MySQL缺陷系统主页开始，然后查找你特别感兴趣的缺陷。 Mikael Ronström’s BlogMySQL分区体系结构和领先的开发者Mikael Ronström 经常在这里贴关于他研究MySQL 分区和MySQL簇的文章。 PlanetMySQL一个MySQL 新闻网站，它以汇集MySQL相关的网誌为特点，那些使用我的MySQL的人应该对此有兴趣。我们鼓励查看那些研究MySQL分区的人的网誌链接，或者把你自己的网誌加到这些新闻报道中。 MySQL 5.1的二进制版本目前还不可用；但是，可以从BitKeeper知识库中获得源码。要激活分区，需要使用--with-分区选项编译服务器。关于建立MySQL 的更多信息，请参见2.8节，“使用源码分发版安装MySQL”。如果在编译一个激活分区的MySQL 5.1创建中碰到问题，可以在MySQL分区论坛中查找解决办法，如果在论坛中已经贴出的文章中没有找到问题的解决办法，可以在上面寻找帮助。 18.1. MySQL中的分区概述 本节提供了关于MySQL 5.1.分区在概念上的概述。 SQL标准在数据存储的物理方面没有提供太多的指南。SQL语言的使用独立于它所使用的任何数据结构或图表、表、行或列下的介质。但是，大部分高级数据库管理系统已经开发了一些根据文件系统、硬件或者这两者来确定将要用于存储特定数据块物理位置的方法。在MySQL中，InnoDB存储引擎长期支持表空间的概念，并且MySQL服务器甚至在分区引入之前，就能配置为存储不同的数据库使用不同的物理路径(关于如何配置的解释，请参见7.6.1节，“使用符号链接”)。 分区又把这个概念推进了一步，它允许根据可以设置为任意大小的规则，跨文件系统分配单个表的多个部分。实际上，表的不同部分在不同的位置被存储为单独的表。用户所选择的、实现数据分割的规则被称为分区函数，这在MySQL中它可以是模数，或者是简单的匹配一个连续的数值区间或数值列表，或者是一个内部HASH函数，或一个线性HASH函数。函数根据用户指定的分区类型来选择，把用户提供的表达式的值作为参数。该表达式可以是一个整数列值，或一个作用在一个或多个列值上并返回一个整数的函数。这个表达式的值传递给分区函数，分区函数返回一个表示那个特定记录应该保存在哪个分区的序号。这个函数不能是常数，也不能是任意数。它不能包含任何查询，但是实际上可以使用MySQL 中任何可用的SQL表达式，只要该表达式返回一个小于MAXVALUE（最大可能的正整数）的正数值。分区函数的例子可以在本章后面关于分区类型的讨论中找到 (请参见18.2节，“分区类型” )，也可在13.1.5节，“CREATE TABLE语法”的分区语法描述中找到。 当二进制码变成可用时（也就是说，5.1 -max 二进制码将通过--with-partition 建立），分区支持就将包含在MySQL 5.1的-max 版本中。如果MySQL二进制码是使用分区支持建立的，那么激活它不需要任何其他的东西 (例如，在my.cnf 文件中，不需要特殊的条目)。可以通过使用SHOW VARIABLES命令来确定MySQL是否支持分区，例如：

在如上列出的一个正确的SHOW VARIABLES 命令所产生的输出中，如果没有看到变量have_partition_engine的值为YES，那么MySQL的版本就不支持分区。（注意：在显示任何有关分区支持信息的命令SHOW ENGINES的输出中，不会给出任何信息；必须使用SHOW VARIABLES命令来做出这个判断）。 对于创建了分区的表，可以使用你的MySQL 服务器所支持的任何存储引擎；MySQL 分区引擎在一个单独的层中运行，并且可以和任何这样的层进行相互作用。在MySQL 5.1版中，同一个分区表的所有分区必须使用同一个存储引擎；例如，不能对一个分区使用MyISAM，而对另一个使用InnoDB。但是，这并不妨碍在同一个 MySQL 服务器中，甚至在同一个数据库中，对于不同的分区表使用不同的存储引擎。 要为某个分区表配置一个专门的存储引擎，必须且只能使用[STORAGE] ENGINE 选项，这如同为非分区表配置存储引擎一样。但是，必须记住[STORAGE] ENGINE（和其他的表选项）必须列在用在CREATE TABLE语句中的其他任何分区选项之前。下面的例子给出了怎样创建一个通过HASH分成6个分区、使用InnoDB存储引擎的表：

（注释：每个PARTITION 子句可以包含一个 [STORAGE] ENGINE 选项，但是在MySQL 5.1版本中，这没有作用）。 创建分区的临时表也是可能的；但是，这种表的生命周期只有当前MySQL 的会话的时间那么长。对于非分区的临时表，这也是一样的。 注释：分区适用于一个表的所有数据和索引；不能只对数据分区而不对索引分区，反之亦然，同时也不能只对表的一部分进行分区。 可以通过使用用来创建分区表的CREATE TABLE语句的PARTITION子句的DATA DIRECTORY（数据路径）和INDEX DIRECTORY（索引路径）选项，为每个分区的数据和索引指定特定的路径。此外，MAX_ROWS和MIN_ROWS选项可以用来设定最大和最小的行数，它们可以各自保存在每个分区里。关于这些选项的更多信息，请参见18.3节，“分区管理”。注释：这个特殊的功能由于Bug #13250的原因，目前还不能实用。在第一个5.1二进制版本投入使用时，我们应该已经把这个问题解决了。 分区的一些优点包括： ·         与单个磁盘或文件系统分区相比，可以存储更多的数据。 ·         对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据。相反地，在某些情况下，添加新数据的过程又可以通过为那些新数据专门增加一个新的分区，来很方便地实现。 通常和分区有关的其他优点包括下面列出的这些。MySQL 分区中的这些功能目前还没有实现，但是在我们的优先级列表中，具有高的优先级；我们希望在5.1的生产版本中，能包括这些功能。 ·         一些查询可以得到极大的优化，这主要是借助于满足一个给定WHERE 语句的数据可以只保存在一个或多个分区内，这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改，所以在第一次配置分区方案时还不曾这么做时，可以重新组织数据，来提高那些常用查询的效率。 ·         涉及到例如SUM() 和 COUNT()这样聚合函数的查询，可以很容易地进行并行处理。这种查询的一个简单例子如 “SELECT salesperson_id, COUNT(orders) as order_total FROM sales GROUP BY salesperson_id；”。通过“并行”， 这意味着该查询可以在每个分区上同时进行，最终结果只需通过总计所有分区得到的结果。 ·         通过跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。 要经常检查本页和本章，因为它将随MySQL 5.1后续的分区进展而更新。 18.2. 分区类型 18.2.1. RANGE分区 18.2.2. LIST分区 18.2.3. HASH分区 18.2.4. KEY分区 18.2.5. 子分区 18.2.6. MySQL分区处理NULL值的方式 本节讨论在MySQL 5.1中可用的分区类型。这些类型包括： ·         RANGE 分区：基于属于一个给定连续区间的列值，把多行分配给分区。参见18.2.1节，“RANGE分区”。 […]

MYSQL表分区

范围分区： CREATE TABLE BIGTABLE ( ID INT, SNPTIME DATETIME NOT NULL, VALUE VARCHAR(20), PRIMARY KEY (SNPTIME, ID) ) ENGINE=InnoDB partition by range (TO_DAYS(SNPTIME)) ( PARTITION p1 VALUES LESS THAN (to_days('2009-1-31')), PARTITION p2 VALUES LESS THAN (to_days('2009-2-28')), PARTITION p3 VALUES LESS THAN (to_days('2008-3-31')), PARTITION p4 VALUES LESS THAN (to_days('2008-4-30')), PARTITION p5 VALUES LESS THAN (to_days('2008-5-31')), PARTITION p6 VALUES LESS THAN (to_days('2008-6-30')), PARTITION p7 VALUES LESS THAN (to_days('2008-7-31')), PARTITION p8 VALUES LESS THAN (to_days('2008-8-31')), PARTITION p9 VALUES LESS THAN (to_days('2008-9-30')), PARTITION p10 VALUES LESS THAN (to_days('2008-10-31')), PARTITION p11 VALUES LESS THAN (to_days('2008-11-30')), PARTITION p12 VALUES LESS THAN (to_days('2008-12-31')), PARTITION p13 VALUES LESS THAN MAXVALUE ) ; 注意一点：一定要有主键，并且主键要包括分区键。 但是，如果必须要分区，而分区中的分区键不想使用（业务不允许）主键的时候，可以采用两步走的办法。 1、建立表，带有主键。 2、删除主键，建立独立索引。 这样在插入数据的时候还是能够按部就班地进入各自所属的分区表。   给已存在的表加分区 ALTER TABLE SNP_SWITCH partition by RANGE (TO_DAYS(RPTTIME)) (PARTITION P1210 VALUES LESS THAN (735172) ENGINE = MYISAM, PARTITION P1211 VALUES LESS THAN (735202) ENGINE = MYISAM, […]

理解SQL SERVER中的分区表

简介 分区表是在SQL SERVER2005之后的版本引入的特性。这个特性允许把逻辑上的一个表在物理上分为很多部分。而对于SQL SERVER2005之前版本，所谓的分区表仅仅是分布式视图,也就是多个表做union操作. 分区表在逻辑上是一个表，而物理上是多个表.这意味着从用户的角度来看，分区表和普通表是一样的。这个概念可以简单如下图所示： 而对于SQL SERVER2005之前的版本，是没有分区这个概念的，所谓的分区仅仅是分布式视图: 本篇文章所讲述的分区表指的是SQL SERVER2005之后引入的分区表特性.   为什么要对表进行分区 在回答标题的问题之前，需要说明的是，表分区这个特性只有在企业版或者开发版中才有,还有理解表分区的概念还需要理解SQL SERVER中文件和文件组的概念. 对表进行分区在多种场景下都需要被用到.通常来说，使用表分区最主要是用于:      存档，比如将销售记录中1年前的数据分到一个专门存档的服务器中      便于管理，比如把一个大表分成若干个小表，则备份和恢复的时候不再需要备份整个表，可以单独备份分区      提高可用性，当一个分区跪了以后，只有一个分区不可用，其它分区不受影响      提高性能，这个往往是大多数人分区的目的，把一个表分布到不同的硬盘或其他存储介质中，会大大提升查询的速度.   分区表的步骤 分区表的定义大体上分为三个步骤：     定义分区函数     定义分区构架     定义分区表 分区函数，分区构架和分区表的关系如下：     分区表依赖分区构架，而分区构架又依赖分区函数.值得注意的是，分区函数并不属于具体的分区构架和分区表，他们之间的关系仅仅是使用关系. 下面我们通过一个例子来看如何定义一个分区表: 假设我们需要定义的分区表结构如下: 第一列为自增列，orderid为订单id列，SalesDate为订单日期列，也就是我们需要分区的依据. 下面我们按照上面所说的三个步骤来实现分区表.     定义分区函数 分区函数是用于判定数据行该属于哪个分区,通过分区函数中设置边界值来使得根据行中特定列的值来确定其分区，上面例子中，我们可以通过SalesDate的值来判定其不同的分区.假设我们想定义两个边界值(boundaryValue)进行分区,则会生成三个分区,这里我设置边界值分别为2004-01-01和2007-01-01，则前面例子中的表会根据这两个边界值分成三个区: 在MSDN中，定义分区函数的原型如下:

    通过定义分区函数的原型，我们看出其中并没有具体涉及具体的表.因为分区函数并不和具体的表相绑定.上面原型中还可以看到Range left和right.这个参数是决定临界值本身应该归于“left”还是“right”： 下面我们根据上面的参数定义分区函数: 通过系统视图，可以看见这个分区函数已经创建成功   定义分区构架 定义完分区函数仅仅是知道了如何将列的值区分到了不同的分区。而每个分区的存储方式，则需要分区构架来定义.使用分区构架需要你对文件和文件组有点了解. 我们先来看MSDN的分区构架的原型:

从原型来看，分区构架仅仅是依赖分区函数.分区构架中负责分配每个区属于哪个文件组，而分区函数是决定如何在逻辑上分区:            基于之前创建的分区函数,创建分区构架:   定义分区表 接下来就该创建分区表了.表在创建的时候就已经决定是否是分区表了。虽然在很多情况下都是你在发现已经表已经足够大的时候才想到要把表分区，但是分区表只能够在创建的时候指定为分区表。 为刚建立的分区表PartitionedTable加入5万条测试数据，其中SalesDate随机生成，从2001年到2010年随机分布.加入数据后，我们通过如下语句来看结果:

  可以看到我们分区的数据分布:           分区表的分割 分区表的分割。相当于新建一个分区，将原有的分区需要分割的内容插入新的分区，然后删除老的分区的内容,概念如下图: 假设我新加入一个分割点：2009-01-01，则概念如下: 通过上图我们可以看出，如果分割时，被分割的分区3内有内容需要分割到分区4，则这些数据需要被复制到分区4，并删除分区3上对应数据。 这种操作非常非常消耗IO，并且在分割的过程中锁定分区三内的内容，造成分区三的内容不可用。不仅仅如此，这个操作生成的日志内容会是被转移数据的4倍！ 所以我们如果不想因为这种操作给客户带来麻烦而被老板爆菊的话…最好还是把分割点建立在未来（也就是预先建立分割点)，比如2012-01-01。则分区3内的内容不受任何影响。在以后2012的数据加入时，自动插入到分区4. 分割现有的分区需要两个步骤: 1.首先告诉SQL SERVER新建立的分区放到哪个文件组 2.建立新的分割点 可以通过如下语句来完成:   如果我们的分割构架在定义的时候已经指定了NEXT USED，则直接添加分割点即可。 通过文中前面查看分区的长语句..再来看: 新的分区已经加入！   […]

SQL Server中数据库文件的存放方式，文件和文件组

写在前面：上次我关于索引的文章有几个园友发站内信问我如何将索引和表存储在不同的硬盘上。我觉的需要专门写一篇文章来讲述一下文件和文件组应该更容易理解.     简介 在SQL SERVER中，数据库在硬盘上的存储方式和普通文件在Windows中的存储方式没有什么不同，仅仅是几个文件而已.SQL SERVER通过管理逻辑上的文件组的方式来管理文件.理解文件和文件组的概念对于更好的配置数据库来说是最基本的知识。   理解文件和文件组 在SQL SERVER中，通过文件组这个逻辑对象对存放数据的文件进行管理. 先来看一张图: 我们看到的逻辑数据库由一个或者多个文件组构成 而文件组管理着磁盘上的文件.而文件中存放着SQL SERVER的实际数据.   为什么通过文件组来管理文件 对于用户角度来说，需对创建的对象指定存储的文件组只有三种数据对象：表,索引和大对象（LOB） 使用文件组可以隔离用户和文件，使得用户针对文件组来建立表和索引，而不是实际磁盘中的文件。当文件移动或修改时，由于用户建立的表和索引是建立在文件组上的，并不依赖具体文件，这大大加强了可管理性. 还有一点是，使用文件组来管理文件可以使得同一文件组内的不同文件分布在不同的硬盘中，极大的提高了IO性能. SQL SERVER会根据每个文件设置的初始大小和增长量会自动分配新加入的空间，假设在同一文件组中的文件A设置的大小为文件B的两倍，新增一个数据占用三页(Page),则按比例将2页分配到文件A中，1页分配到文件B中.   文件的分类     首要文件:这个文件是必须有的，而且只能有一个。这个文件额外存放了其他文件的位置等信息.扩展名为.mdf     次要文件:可以建任意多个，用于不同目的存放.扩展名为.ndf     日志文件：存放日志,扩展名为.ldf 在SQL SERVER 2008之后，还新增了文件流数据文件和全文索引文件. 上述几种文件名扩展名可以随意修改，但是我推荐使用默认的扩展名。 我们可以通过如下语句查看数据库中的文件情况: 还有一点要注意的是，如果一个表是存在物理上的多个文件中时，则表的数据页的组织为N(N为具体的几个文件)个B树.而不是一个对象为一个B树.   创建和使用文件组 创建文件或是文件组可以通过在SSMS中或者使用T-SQL语句进行。对于一个数据库来说，既可以在创建时增加文件和文件组，也可以向现有的数据库添加文件和文件组.这几种方式大同小异.下面来看一下通过SSMS向现有数据库添加文件和文件组. 首先创建文件组: 文件组创建好后就可以向现有文件组中添加文件了:   下面我们就可以通过语句将创建的表或者索引加入到新的文件组中了:   使用多个文件的优点与缺点 通常情况下，小型的数据库并不需要创建多个文件来分布数据。但是随着数据的增长，使用单个文件的弊端就开始显现。 首先：使用多个文件分布数据到多个硬盘中可以极大的提高IO性能. 其次：多个文件对于数据略多的数据库来说，备份和恢复都会轻松很多.我碰见过遇到一个150G的数据库，手头却没有这么大的存储设备…   但是，在数据库的世界中，每一项好处往往伴随着一个坏处： 显而易见，使用多文件需要占用更多的磁盘空间。这是因为每个文件中都有自己的一套B树组织方式，和自己的增长空间。当然了，还有一套自己的碎片-.-但是在大多数情况下，多占点磁盘空间带来的弊端要远远小于多文件带来的好处.   总结 本文对SQL SERVER中文件和文件组的概念进行了简单阐述，并在文中讲述了文件和文件组的配置方式。按照业务组织好不同的文件组来分布不同的文件，使得性能的提升，对于你半夜少接几个电话的帮助是灰常大滴:-)   from:http://www.cnblogs.com/CareySon/archive/2011/12/26/2301597.html

MySQL索引分类和各自用途

一、 MySQL: 索引以B树格式保存 Memory存储引擎可以选择Hash或BTree索引，Hash索引只能用于=或<=>的等式比较。 1、普通索引：create index on Tablename(列的列表) alter table TableName add index (列的列表) create table TableName([…], index [IndexName] (列的列表) 2、唯一性索引：create unique index alter … add unique 主键：一种唯一性索引，必须指定为primary key 3、全文索引：从3.23.23版开始支持全文索引和全文检索，FULLTEXT， 可以在char、varchar或text类型的列上创建。 4、单列索引、多列索引： 多个单列索引与单个多列索引的查询效果不同，因为： 执行查询时，MySQL只能使用一个索引，会从多个索引中选择一个限制最为严格的索引。 5、最左前缀(Leftmost Prefixing)：多列索引，例如：fname_lname_age索引，以下的搜索条件MySQL都将使用 fname_lname_age索引：firstname,lastname,age；firstname,lastname；firstname，其他情况将不使用。 二、根据sql查询语句确定创建哪种类型的索引，如何优化查询 选择索引列： a.性能优化过程中，选择在哪个列上创建索引是最重要的步骤之一。可以考虑使用索引的主要有 两种类型的列：在where子句中出现的列，在join子句中出现的列。 b.考虑列中值的分布，索引的列的基数越大，索引的效果越好。 c.使用短索引，如果对字符串列进行索引，应该指定一个前缀长度，可节省大量索引空间，提升查询速度。 d.利用最左前缀 e.不要过度索引，只保持所需的索引。每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能。 在修改表的内容时，索引必须进行更新，有时可能需要重构，因此，索引越多，所花的时间越长。 MySQL只对一下操作符才使用索引：<,<=,=,>,>=,between,in, 以及某些时候的like(不以通配符%或_开头的情形)。 from:http://database.51cto.com/art/200905/122789.htm

浅谈MySQL存储引擎选择 InnoDB还是MyISAM

MyISAM 是MySQL中默认的存储引擎，一般来说不是有太多人关心这个东西。决定使用什么样的存储引擎是一个很tricky的事情，但是还是值我们去研究一下，这里的文章只考虑 MyISAM 和InnoDB这两个，因为这两个是最常见的。 下面先让我们回答一些问题： ◆你的数据库有外键吗？ ◆你需要事务支持吗？ ◆你需要全文索引吗？ ◆你经常使用什么样的查询模式？ ◆你的数据有多大？ 思考上面这些问题可以让你找到合适的方向，但那并不是绝对的。如果你需要事务处理或是外键，那么InnoDB 可能是比较好的方式。如果你需要全文索引，那么通常来说 MyISAM是好的选择，因为这是系统内建的，然而，我们其实并不会经常地去测试两百万行记录。所以，就算是慢一点，我们可以通过使用Sphinx从InnoDB中获得全文索引。 数据的大小，是一个影响你选择什么样存储引擎的重要因素，大尺寸的数据集趋向于选择InnoDB方式，因为其支持事务处理和故障恢复。数据库的大小决定了故障恢复的时间长短，InnoDB可以利用事务日志进行数据恢复，这会比较快。而MyISAM可能会需要几个小时甚至几天来干这些事，InnoDB只需要几分钟。 您操作数据库表的习惯可能也会是一个对性能影响很大的因素。比如： COUNT() 在 MyISAM 表中会非常快，而在InnoDB 表下可能会很痛苦。而主键查询则在InnoDB下会相当相当的快，但需要小心的是如果我们的主键太长了也会导致性能问题。大批的inserts 语句在MyISAM下会快一些，但是updates 在InnoDB 下会更快一些——尤其在并发量大的时候。 所以，到底你检使用哪一个呢？根据经验来看，如果是一些小型的应用或项目，那么MyISAM 也许会更适合。当然，在大型的环境下使用MyISAM 也会有很大成功的时候，但却不总是这样的。如果你正在计划使用一个超大数据量的项目，而且需要事务处理或外键支持，那么你真的应该直接使用InnoDB方式。但需要记住InnoDB 的表需要更多的内存和存储，转换100GB 的MyISAM 表到InnoDB 表可能会让你有非常坏的体验。   from:http://database.51cto.com/art/200905/122382.htm

通过分区（Partition）提升MySQL性能

什么是数据库分区？ 数据库分区是一种物理数据库设计技术，DBA和数据库建模人员对其相当熟悉。虽然分区技术可以实现很多效果，但其主要目的是为了在特定的SQL操作中减少数据读写的总量以缩减响应时间。 分区主要有两种形式：//这里一定要注意行和列的概念（row是行，column是列） 1. 水平分区（Horizontal Partitioning）这种形式分区是对表的行进行分区，通过这样的方式不同分组里面的物理列分割的数据集得以组合，从而进行个体分割（单分区）或集体分割（1个或多个分区）。所有在表中定义的列在每个数据集中都能找到，所以表的特性依然得以保持。 举个简单例子：一个包含十年发票记录的表可以被分区为十个不同的分区，每个分区包含的是其中一年的记录。（朋奕注：这里具体使用的分区方式我们后面再说，可以先说一点，一定要通过某个属性列来分割，譬如这里使用的列就是年份） 2. 垂直分区（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哈希分区。 分区带来的好处太多太多了，有多少？俺也不知道，自己猜去吧，要是觉得没有多少就别用，反正俺也不求你用。不过在这里俺强调两点好处： 性能的提升（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上运行通过。 如何进行实际分区 看看分区的实际效果吧。我们建立几个同样的MyISAM引擎的表，包含日期敏感的数据，但只对其中一个分区。分区的表（表名为part_tab）我们采用Range范围分区模式，通过年份进行分区： mysql> CREATE TABLE part_tab -> ( c1 int default NULL, -> c2 varchar(30) default NULL, -> c3 date default NULL -> -> ) […]

MySQL分区(mysql partition)

一、概述 相信有很多人经常会问同样的一个问题：当 MySQL 的总记录数超过了100万后，会出现性能的大幅度下降吗？答案是肯定的，但是性能下降>的比率不一而同，要看系统的架构、应用程序、还有>包括索引、服务器硬件等多种因素而定。当有网友问我这个问题的时候，我最常见的回答>就是：分表，可以根据id区间或者时间先后顺序等多 种规则来分表。分表很容易，然而由此所带来的应用程序甚至是架构方面的改动工作却不>容小觑，还包括将来的扩展性等。 在以前，一种解决方案就是使用 MERGE 类型，这是一个非常方便的做饭。架构和程序基本上不用做改动，不过，它的缺点是显见的： 只能在相同结构的 MyISAM 表上使用 无法享受到 MyISAM 的全部功能，例如无法在 MERGE 类型上执行 FULLTEXT 搜索 它需要使用更多的文件描述符 读取索引更慢 这个时候，MySQL 5.1 中新增的分区(Partition)功能的优势也就很明显了： 与单个磁盘或文件系统分区相比，可以存储更多的数据 很容易就能删除不用或者过时的数据 一些查询可以得到极大的优化 涉及到 SUM()/COUNT() 等聚合函数时，可以并行进行 IO吞吐量更大 分区允许可以设置为任意大小的规则，跨文件系统分配单个表的多个部分。实际上，表的不同部分在不同的位置被存储为单独的表。 二、分区的类型 RANGE 分区：基于属于一个给定连续区间的列值，把多行分配给分区。参见18.2.1节，RANGE分区 LIST 分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。参见18.2.2节，LIST分区 HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包>含MySQL中有效的、产生非负整数值的任何表达式。参见18.2.3节，HASH分区 KEY 分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含>整数值。 参见18.2.4节，KEY分区 三、分区例子： RANGE 类型 CREATE TABLE users (      uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,      name VARCHAR(30) NOT NULL DEFAULT ",      email VARCHAR(30) NOT NULL DEFAULT " ) PARTITION BY RANGE (uid) (      PARTITION p0 VALUES LESS THAN (3000000)      DATA DIRECTORY = '/data0/data' […]