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