May 12, 2015

MongoDB sharding collection 与 unique index

作者 · 阅读 4 分钟

 

MongoDB中对于已经分片的collection ，仅有索引对应的field是shard key is a prefix的情况才可以建unique index唯一索引，否则不能建为唯一索引。
例如:

mongos>  sh.status();
--- Sharding Status --- 
  sharding version: {
        "_id" : 1,
        "minCompatibleVersion" : 5,
        "currentVersion" : 6,
        "clusterId" : ObjectId("554b241f4df23a46a60f6a9c")
}
  shards:
        {  "_id" : "shard0000",  "host" : "shard0.dbdao.com:35001" }
        {  "_id" : "shard0001",  "host" : "shard1.dbdao.com:35001" }
        {  "_id" : "shard0002",  "host" : "shard2.dbdao.com:35001" }
  balancer:
        Currently enabled:  yes
        Currently running:  no
        Failed balancer rounds in last 5 attempts:  0
        Migration Results for the last 24 hours: 
                No recent migrations
  databases:
        {  "_id" : "admin",  "partitioned" : false,  "primary" : "config" }
        {  "_id" : "test_db",  "partitioned" : true,  "primary" : "shard0000" }
                test_db.test_collection
                        shard key: { "_id" : "hashed" }
                        chunks:
                                shard0000       2
                                shard0001       2
                                shard0002       2
                        { "_id" : { "$minKey" : 1 } } -->> { "_id" : NumberLong("-6148914691236517204") } on : shard0000 Timestamp(3, 2) 
                        { "_id" : NumberLong("-6148914691236517204") } -->> { "_id" : NumberLong("-3074457345618258602") } on : shard0000 Timestamp(3, 3) 
                        { "_id" : NumberLong("-3074457345618258602") } -->> { "_id" : NumberLong(0) } on : shard0001 Timestamp(3, 4) 
                        { "_id" : NumberLong(0) } -->> { "_id" : NumberLong("3074457345618258602") } on : shard0001 Timestamp(3, 5) 
                        { "_id" : NumberLong("3074457345618258602") } -->> { "_id" : NumberLong("6148914691236517204") } on : shard0002 Timestamp(3, 6) 
                        { "_id" : NumberLong("6148914691236517204") } -->> { "_id" : { "$maxKey" : 1 } } on : shard0002 Timestamp(3, 7) 
        {  "_id" : "test",  "partitioned" : false,  "primary" : "shard0000" }

mongos> 
mongos> 
mongos> db.test_collection.find();
{ "_id" : ObjectId("554b296c160953211da4b523"), "x" : 2 }
{ "_id" : ObjectId("554b296c160953211da4b522"), "x" : 1 }
{ "_id" : ObjectId("554b296c160953211da4b524"), "x" : 3 }
{ "_id" : ObjectId("554b296c160953211da4b526"), "x" : 5 }
{ "_id" : ObjectId("554b296c160953211da4b529"), "x" : 8 }
{ "_id" : ObjectId("554b296c160953211da4b525"), "x" : 4 }
{ "_id" : ObjectId("554b296c160953211da4b52c"), "x" : 11 }
{ "_id" : ObjectId("554b296c160953211da4b52b"), "x" : 10 }
{ "_id" : ObjectId("554b296c160953211da4b527"), "x" : 6 }
{ "_id" : ObjectId("554b296c160953211da4b52d"), "x" : 12 }
{ "_id" : ObjectId("554b296c160953211da4b52f"), "x" : 14 }
{ "_id" : ObjectId("554b296c160953211da4b528"), "x" : 7 }
{ "_id" : ObjectId("554b296c160953211da4b52e"), "x" : 13 }
{ "_id" : ObjectId("554b296c160953211da4b530"), "x" : 15 }
{ "_id" : ObjectId("554b296c160953211da4b52a"), "x" : 9 }
{ "_id" : ObjectId("554b296c160953211da4b531"), "x" : 16 }
{ "_id" : ObjectId("554b296c160953211da4b532"), "x" : 17 }
{ "_id" : ObjectId("554b296c160953211da4b533"), "x" : 18 }
{ "_id" : ObjectId("554b296c160953211da4b53b"), "x" : 26 }
{ "_id" : ObjectId("554b296c160953211da4b534"), "x" : 19 }
Type "it" for more

mongos> db.test_index.ensureIndex( { x : 1 } , {unique: true} );
{
        "raw" : {
                "shard0.dbdao.com:35001" : {
                        "createdCollectionAutomatically" : true,
                        "numIndexesBefore" : 1,
                        "numIndexesAfter" : 2,
                        "ok" : 1
                }
        },
        "ok" : 1
}
mongos> db.test_index.ensureIndex( { y : 1 } , {unique: true} );
{
        "raw" : {
                "shard0.dbdao.com:35001" : {
                        "createdCollectionAutomatically" : false,
                        "numIndexesBefore" : 2,
                        "numIndexesAfter" : 3,
                        "ok" : 1
                }
        },
        "ok" : 1
}
mongos> sh.shardCollection("test_db.test_index", { x : 1 } );
{
        "ok" : 0,
        "errmsg" : "can't shard collection 'test_db.test_index' with unique index on { y: 1.0 } and proposed shard key { x: 1.0 }. Uniqueness can't be maintained unless shard key is a prefix"
}
mongos> db.test_index.drop();
true

 

 

如果分片key是index filed的一部分则可以建立唯一索引

true
mongos> db.test_index.ensureIndex( { x : 1 , y:1 } , {unique: true} );
{
        "raw" : {
                "shard0.dbdao.com:35001" : {
                        "createdCollectionAutomatically" : true,
                        "numIndexesBefore" : 1,
                        "numIndexesAfter" : 2,
                        "ok" : 1
                }
        },
        "ok" : 1
}
mongos> sh.shardCollection("test_db.test_index", { x : 1 } );
{ "collectionsharded" : "test_db.test_index", "ok" : 1 }

 

建非unique 索引总是可以：

 

mongos> db.test_index.ensureIndex( { z : 1 }  );
{
        "raw" : {
                "shard0.dbdao.com:35001" : {
                        "createdCollectionAutomatically" : false,
                        "numIndexesBefore" : 2,
                        "numIndexesAfter" : 3,
                        "ok" : 1
                },
                "shard1.dbdao.com:35001" : {
                        "createdCollectionAutomatically" : false,
                        "numIndexesBefore" : 2,
                        "numIndexesAfter" : 2,
                        "note" : "all indexes already exist",
                        "ok" : 1
                },
                "shard2.dbdao.com:35001" : {
                        "createdCollectionAutomatically" : false,
                        "numIndexesBefore" : 2,
                        "numIndexesAfter" : 2,
                        "note" : "all indexes already exist",
                        "ok" : 1
                }
        },
        "ok" : 1
}
mongos> db.test_index.getIndexes();
[
 {
 "v" : 1,
 "key" : {
 "_id" : 1
 },
 "name" : "_id_",
 "ns" : "test_db.test_index"
 },
 {
 "v" : 1,
 "unique" : true,
 "key" : {
 "x" : 1,
 "y" : 1
 },
 "name" : "x_1_y_1",
 "ns" : "test_db.test_index"
 },
 {
 "v" : 1,
 "key" : {
 "z" : 1
 },
 "name" : "z_1",
 "ns" : "test_db.test_index"
 }
]


mongos> db.test_index.drop();
true
mongos> 
mongos> db.test_index.ensureIndex( { x : 1 } , {unique: true} );
{
 "raw" : {
 "shard0.dbdao.com:35001" : {
 "createdCollectionAutomatically" : true,
 "numIndexesBefore" : 1,
 "numIndexesAfter" : 2,
 "ok" : 1
 }
 },
 "ok" : 1
}
mongos> sh.shardCollection("test_db.test_index", { x : 1 } );
{ "collectionsharded" : "test_db.test_index", "ok" : 1 }
mongos> for (var i = 1; i <= 500; i++) db.test_collection.insert( { x : i , y:i+1} );
WriteResult({ "nInserted" : 1 })
mongos> db.test_index.ensureIndex( { x : 1 , y:1 } , {unique: true} );
{
 "raw" : {
 "shard0.dbdao.com:35001" : {
 "createdCollectionAutomatically" : false,
 "numIndexesBefore" : 2,
 "numIndexesAfter" : 3,
 "ok" : 1
 },
 "shard1.dbdao.com:35001" : {
 "createdCollectionAutomatically" : false,
 "numIndexesBefore" : 2,
 "numIndexesAfter" : 3,
 "ok" : 1
 },
 "shard2.dbdao.com:35001" : {
 "createdCollectionAutomatically" : false,
 "numIndexesBefore" : 2,
 "numIndexesAfter" : 3,
 "ok" : 1
 }
 },
 "ok" : 1
}

总结：

MongoDB中对于已经分片的collection ，仅有索引对应的field是shard key is a prefix的情况才可以建unique index唯一索引，否则不能建为唯一索引。

对于shard collection建立non-unique index总是可以的

 

 

” A attempt to create a unique index in previously sharded collection will result in an error message”

这句话说得是不严谨的， 如果创建的unique index 以shard key作为prefix的话那么是可以被创建了，仅仅当index prefix和shard key没关系时是会报错的

 

这位作者还没有填写简介。

查看所有文章

相关文章

发表评论

Your email address will not be published. Required fields are marked *