MongoDB sharding collection 与 unique index

dbDao 百度贴吧：http://tieba.baidu.com/dbdao

MongoDB技术学习QQ群： 421431253

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"  } ]

总结：

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

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