对已存在的表进行分区时遇到的坑

摘要:
很多关于表分区的数据都可以在网上找到,但大多数都是关于如何为新表创建表分区的,而关于如何对现有表进行分区的文章相对较少,因此一些坑没有被“挖掘”或“暴露”相对较低。对现有表进行分区的最常见方法是重建聚集索引或创建聚集索引。也就是说,如果要在重建唯一索引时进行分区,则必须要求分区列是唯一索引的一部分。

在网上能够找到很多关于表分区的资料,可是大部分都是在介绍如何给一个新表创建表分区,而对已存在的表如何做分区的文章相对比较少,因此一些坑没有被“挖掘”出来或者“曝光率”比较低。

笔者最近遇到了一个这样的案例,刚好踩到了两个坑,现分享给大家。

对已存在的表进行分区最常见的方法就是重建聚集索引或者创建聚集索引(如果表上没有)。因为聚集索引的页级就是实际数据,而重建或者创建聚集索引时会重新组织页,所以如果在重建或者创建索引时指定分区架构,那么该表上所有的数据就会按照分区架构进行表分区。

在使用此法对表进行分区,有两个坑请大家注意:

坑一:重建索引时,提示“新的索引定义与现有的索引强制的约束不匹配”

请看如下代码:

CREATE clustered index

PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID ON Sales.SalesOrderDetail(SalesOrderID, SalesOrderDetailID) WITH DROP_EXISTING

ON [PS_Sod](ModifiedDate)

参数with drop_existing表示给定的索引作为一个事务被删除和重建;

PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID是索引的名称,它建立在表Sales.SalesOrderDetail的SalesOrderID, SalesOrderDetailID字段上;

PS_Sod为分区架构,其参数ModifiedDate表示分区列;

当我执行时,提示错误如下:

消息1907,级别16,状态1,第2 行

无法重新重建索引'PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID'。新的新的索引定义与现有的索引强制的约束不匹配.

从报错信息来看,新创建的索引与原索引的约束不匹配,于是执行如下语句,查看当前表身上的约束信息:

SELECT CONSTRAINT_TYPE FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

WHERE CONSTRAINT_NAME = 'PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID'

返回的结果为:primary

也就是说,当前表上的(SalesOrderID, SalesOrderDetailID)存在主键约束,从而确保(SalesOrderID, SalesOrderDetailID)的唯一性,而重建索引时,没有指定唯一性,因此需要改成如下语句:

CREATE UNIQUE clustered index

PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID ON Sales.SalesOrderDetail(SalesOrderID, SalesOrderDetailID) WITH DROP_EXISTING

ON [PS_Sod](ModifiedDate)

坑二:”唯一索引的分区依据列必须是索引键的子集”

即使上述方法修改了执行语句,还是会报错,只是报错的内容不再是约束有关,而是唯一索引的分区依据列必须是索引键的子集。

消息1908,级别16,状态1,第2 行

列'ModifiedDate' 是索引'PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID' 的分区依据列。唯一索引的分区依据列必须是索引键的子集。

也就是说,如果要在唯一索引重建时分区,必须要求分区的依据列是唯一索引的一部分。

如此看来,要想通过重建聚集索引的方式对表进行分区,需要满足如下2个条件中的任意一个:

1. 将分区列添加到索引中;

     不过,很多时候分区依据列是datetime类型的字段,作为索引的可选择性不强,将其添加到索引中并不满足索引创建的最佳实践。

2. 索引没有被显示标记为unique且不存在主键约束;

    这样就不存在唯一索引的说法,也就不需要分区的依据列是唯一索引的一部分;

    不过,在实际场景中,如果表的索引在创建时被显示指定为unique了,还是有办法通过索引“重建”进行分区的,只不过不能使用在线索引重建的方法,我们必须先手动删除索引,然后再来创建非unique的索引。这种方式相比with drop_existing重建索引性能要差很多,因为删除一个聚集索引时,SQL Server必须重建每一个非聚集索引(假设存在非聚集索引),从而将其书签修改为RID,然后,在创建聚集索引时,又需要将每个非聚集索引的RID更改书签。而with drop_existing只需要重建一次非聚集索引,当然,如果你在完全相同的键上建立索引,非聚集索引根本不需要重建。

   至于主键约束的限制,相比较而言就比较简单了,即使表中存在主键约束,删除它时也不会存在性能上的影响。

免责声明:文章转载自《对已存在的表进行分区时遇到的坑》仅用于学习参考。如对内容有疑问,请及时联系本站处理。

上篇『重构--改善既有代码的设计』读书笔记----代码坏味道【3】Spring中FactoryBean的作用和实现原理下篇

宿迁高防,2C2G15M,22元/月;香港BGP,2C5G5M,25元/月 雨云优惠码:MjYwNzM=

相关文章

索引的增删改成查

1 创建表时就指定索引 create table t1( id int, name char(5), unique key nui_name(name), primary key (id) ); create table t2( id int, name char(5), index idx_name(name) ); 2 在创建完表后为其添加索引 crea...

Mysql的索引

什么是索引: 数据库中的索引与书籍中的目录类似,在一本书中,利用目录可以快速查找所需要的信息,无需阅读整本书.在数据库中,索引使数据库程序无须对整个表进行扫描,就可以在其中找到所需数据.书中的目录是一个词语列表,其中注明了包含各个词的页码,在数据库中,由于数据存储在数据表中,因此索引是创建在数据表对象上的,由表中的一个字段或多个字段生成的键组成,这些键存储...

MySQL规约(阿里巴巴)

建表规约 【强制】表达是与否概念的字段,必须使用 is _ xxx 的方式命名,数据类型是 unsigned tinyint ( 1 表示是,0 表示否 ) ,此规则同样适用于 odps 建表。说明:任何字段如果为非负数,必须是 unsigned 。 【强制】表名、字段名必须使用小写字母或数字 ; 禁止出现数字开头,禁止两个下划线中间只 出现数字。数...

如何优化mysql查询速度

1、应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。 2、对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。 3、应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如: select id fr...

MySQL3:索引

什么是索引 索引是对数据库表中一列或者多列的值进行排序的一种结构,所引用于快速找出在某个列中有一特定值的行。不使用索引,MySQL必须从第一条记录开始读完整个表,直到找出相关的行。表越大,查询数据所花费的时间越多,如果表中查询的列有一个索引,MySQL能快速到达一个位置去搜索数据文件,而不必查看所有数据。 索引的含义和特点 索引是一个单独的、存储在磁盘上的...

MySQL-in和or走不走索引

关于MySQL中in和in走不走索引的问题---具体情况得看优化器自己决定,但大致的规律是:in或or的数据量占比越大越可能不走,比如几百条数据我in上十来条他是会走索引的,但我in上几百条他可能就不走索引了。下面是测试: 简单测试几条数据为例      可以在上面的测试中发现,in是走索引的。 测试1: 测试2:  测试3: 测试4:  可...