MongoDB sharding collection 与 unique index

 

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没关系时是会报错的

 

Comment

*

沪ICP备14014813号-2

沪公网安备 31010802001379号