MongoDB中索引的用法

聽(tīng)老歌

MongoDB中索引的用法

本文是一篇轉(zhuǎn)載文章，作者在對(duì)MongoDB文檔進(jìn)行了細(xì)致的閱讀后，總結(jié)出了MongoDB的各種索引的用法。

原文鏈接：http://iamcaihuafeng.blog.sohu.com/151638529.html

索引能提高檢索數(shù)據(jù)的速度，你可以想像成在MySQL中創(chuàng)建索引一樣，同樣索引也是用B-Tree也實(shí)現(xiàn)的。

1.單列索引
在字段x上創(chuàng)建索引，1 (ascending) or -1 (descending)

> db.data.ensureIndex({x:1})顯示表data里面的所有索引

> db.data.getIndexes()
[
{
"name" : "_id_",
"ns" : "recommender.data",
"key" : {
"_id" : 1
}
},
{
"_id" : ObjectId("4befb146b0e29ba1ce20e0bb"),
"ns" : "recommender.data",
"key" : {
"x" : 1
},
"name" : "x_1"
}
]查找字段x為6的值，此時(shí)已經(jīng)用到索引了

> db.data.find({x:6})
{ "_id" : ObjectId("4bee804ba23d558eb6687117"), "x" : 6, "name" : "caihuafeng1" }
{ "_id" : ObjectId("4bee804ba23d558eb6687118"), "x" : 6, "name" : "caihuafeng2" }
{ "_id" : ObjectId("4bee804ba23d558eb6687119"), "x" : 6, "name" : "caihuafeng3" }
{ "_id" : ObjectId("4bee804ba23d558eb668711a"), "x" : 6, "name" : "caihuafeng4" }
{ "_id" : ObjectId("4bee804ba23d558eb668711b"), "x" : 6, "name" : "caihuafeng5" }
{ "_id" : ObjectId("4bee804ba23d558eb668711c"), "x" : 6, "name" : "caihuafeng6" }
{ "_id" : ObjectId("4bee804ba23d558eb668711d"), "x" : 6, "name" : "caihuafeng7" }
{ "_id" : ObjectId("4bee804ba23d558eb668711e"), "x" : 6, "name" : "caihuafeng8" }
{ "_id" : ObjectId("4bee804ba23d558eb668711f"), "x" : 6, "name" : "caihuafeng9" }
{ "_id" : ObjectId("4bee804ba23d558eb6687120"), "x" : 6, "name" : "caihuafeng10" }2.默認(rèn)索引
上述1中db.data.getIndexes()顯示出來(lái)的一共有2個(gè)索引，其中_id是創(chuàng)建表的時(shí)候自動(dòng)創(chuàng)建的索引，此索引是不能夠刪除的。

An index is always created on _id. This index is special and cannot be deleted. The _id index enforces uniqueness for its keys.

3.文檔作為索引的鍵值
a.單列索引
MongoDB的官方文檔上面是這樣說(shuō)的:
Documents as Keys

Indexed fields may be of any type, including documents:

往數(shù)據(jù)庫(kù)recommender的表data中插入三條記錄

> db.data.insert({name:"1616",info:{url:"http://www.1616.net/",city:"beijing"}});
> db.data.insert({name:"hao123",info:{url:"http://www.hao123.com/",city:"beijing"}});
> db.data.insert({name:"ll4la",info:{url:"http://www.114la.com/",city:"dongguan"}});對(duì)字段info創(chuàng)建索引

> db.data.ensureIndex({info: 1});顯示表data上的所有索引

> db.data.getIndexes();
[
{
"name" : "_id_",
"ns" : "recommender.data",
"key" : {
"_id" : 1
}
},
{
"_id" : ObjectId("4befb146b0e29ba1ce20e0bb"),
"ns" : "recommender.data",
"key" : {
"x" : 1
},
"name" : "x_1"
},
{
"_id" : ObjectId("4befb76bb0e29ba1ce20e0bf"),
"ns" : "recommender.data",
"key" : {
"info" : 1
},
"name" : "info_1"
}
]查找指定的記錄，此時(shí)會(huì)用到索引

> db.data.find({info: {url:"http://www.1616.net/",city:"beijing"}});
{ "_id" : ObjectId("4befb711b0e29ba1ce20e0bc"), "name" : "1616", "info" : { "url" : "http://www.1616.net/", "city" : "beijing" } }b.組合索引
建立組合索引

> db.data.ensureIndex({"info.url":1, "info.city":1});
> db.data.getIndexes();
[
{
"name" : "_id_",
"ns" : "recommender.data",
"key" : {
"_id" : 1
}
},
{
"_id" : ObjectId("4befb146b0e29ba1ce20e0bb"),
"ns" : "recommender.data",
"key" : {
"x" : 1
},
"name" : "x_1"
},
{
"_id" : ObjectId("4befb76bb0e29ba1ce20e0bf"),
"ns" : "recommender.data",
"key" : {
"info" : 1
},
"name" : "info_1"
},
{
"_id" : ObjectId("4befb9d1b0e29ba1ce20e0c0"),
"ns" : "recommender.data",
"key" : {
"info.url" : 1,
"info.city" : 1
},
"name" : "info.url_1_info.city_1"
}
]下面幾個(gè)操作均會(huì)用到索引

> db.data.find({"info.url": "http://www.1616.net/", "info.city": "beijing"});
{ "_id" : ObjectId("4befb711b0e29ba1ce20e0bc"), "name" : "1616", "info" : { "url" : "http://www.1616.net/", "city" : "beijing" } }

> db.data.find({"info.url": "http://www.1616.net/"});
{ "_id" : ObjectId("4befb711b0e29ba1ce20e0bc"), "name" : "1616", "info" : { "url" : "http://www.1616.net/", "city" : "beijing" } }1表示升序(asc)，-1表示降序(desc)

> db.data.find({"info.url": /http:*/i}).sort({"info.url": 1, "info.city": 1});
{ "_id" : ObjectId("4befb740b0e29ba1ce20e0be"), "name" : "ll4la", "info" : { "url" : "http://www.114la.com/", "city" : "dongguan" } }
{ "_id" : ObjectId("4befb711b0e29ba1ce20e0bc"), "name" : "1616", "info" : { "url" : "http://www.1616.net/", "city" : "beijing" } }
{ "_id" : ObjectId("4befb723b0e29ba1ce20e0bd"), "name" : "hao123", "info" : { "url" : "http://www.hao123.com/", "city" : "beijing" } }

> db.data.find({"info.url": /http:*/i}).sort({"info.url": 1});
{ "_id" : ObjectId("4befb740b0e29ba1ce20e0be"), "name" : "ll4la", "info" : { "url" : "http://www.114la.com/", "city" : "dongguan" } }
{ "_id" : ObjectId("4befb711b0e29ba1ce20e0bc"), "name" : "1616", "info" : { "url" : "http://www.1616.net/", "city" : "beijing" } }
{ "_id" : ObjectId("4befb723b0e29ba1ce20e0bd"), "name" : "hao123", "info" : { "url" : "http://www.hao123.com/", "city" : "beijing" } }

> db.data.find({"info.url": /http:*/i}).sort({"info.url": -1});
{ "_id" : ObjectId("4befb723b0e29ba1ce20e0bd"), "name" : "hao123", "info" : { "url" : "http://www.hao123.com/", "city" : "beijing" } }
{ "_id" : ObjectId("4befb711b0e29ba1ce20e0bc"), "name" : "1616", "info" : { "url" : "http://www.1616.net/", "city" : "beijing" } }
{ "_id" : ObjectId("4befb740b0e29ba1ce20e0be"), "name" : "ll4la", "info" : { "url" : "http://www.114la.com/", "city" : "dongguan" } }4.組合索引
注意，這里的組合索引與上述3中的b中的組合索引是有點(diǎn)不同的，4里面是對(duì)一級(jí)字段建立組合索引，而上述3中是對(duì)二級(jí)字段建立組合索引。

在字段name及info上面創(chuàng)建組合索引

> db.data.ensureIndex({name: 1, info: -1});當(dāng)創(chuàng)建組合索引時(shí)，字段后面的1表示升序，-1表示降序，是用1還是用-1主要是跟排序的時(shí)候或指定范圍內(nèi)查詢(xún)的時(shí)候有關(guān)的，具體看下面的英文原文的說(shuō)明。
When creating an index, the number associated with a key specifies the direction of the index, so it should always be 1 (ascending) or -1 (descending). Direction doesn’t matter for single key indexes or for random access retrieval but is important if you are doing sorts or range queries on compound indexes.

顯示所有的索引

> db.data.getIndexes();
[
{
"name" : "_id_",
"ns" : "recommender.data",
"key" : {
"_id" : 1
}
},
{
"_id" : ObjectId("4befb146b0e29ba1ce20e0bb"),
"ns" : "recommender.data",
"key" : {
"x" : 1
},
"name" : "x_1"
},
{
"_id" : ObjectId("4befb76bb0e29ba1ce20e0bf"),
"ns" : "recommender.data",
"key" : {
"info" : 1
},
"name" : "info_1"
},
{
"_id" : ObjectId("4befb9d1b0e29ba1ce20e0c0"),
"ns" : "recommender.data",
"key" : {
"info.url" : 1,
"info.city" : 1
},
"name" : "info.url_1_info.city_1"
},
{
"_id" : ObjectId("4befbfcfb0e29ba1ce20e0c1"),
"ns" : "recommender.data",
"key" : {
"name" : 1,
"info" : -1
},
"name" : "name_1_info_-1"
}
]下面的排序?qū)⒂玫缴厦娴乃饕?br /> 最后一行的”name” : “l(fā)l4la”實(shí)際上是”name” : “114la”(就是將數(shù)字一寫(xiě)成了字母l)，但是我錄入的時(shí)候?qū)懗闪恕眓ame” : “l(fā)l4la”，是我寫(xiě)錯(cuò)了，但是排序的結(jié)果是對(duì)的。

> db.data.find({"info.url": /http:*/i}).sort({name:1, info: -1});
{ "_id" : ObjectId("4befb711b0e29ba1ce20e0bc"), "name" : "1616", "info" : { "url" : "http://www.1616.net/", "city" : "beijing" } }
{ "_id" : ObjectId("4befb723b0e29ba1ce20e0bd"), "name" : "hao123", "info" : { "url" : "http://www.hao123.com/", "city" : "beijing" } }
{ "_id" : ObjectId("4befb740b0e29ba1ce20e0be"), "name" : "ll4la", "info" : { "url" : "http://www.114la.com/", "city" : "dongguan" } }MongoDB組合索引規(guī)則
If you have a compound index on multiple fields, you can use it to query on the beginning subset of fields. So if you have an index on

a,b,c

you can use it query on

a

a,b

a,b,c

如果用過(guò)MySQL的話(huà)，看起來(lái)是不是很熟悉，原理跟MySQL是一樣的。

5.唯一索引
往表data中插入一條記錄。

> db.data.insert({firstname: "cai", lastname: "huafeng"});由于表data中只有一記錄有字段firstname及l(fā)astname，其它的行均沒(méi)有相應(yīng)的值，也就是均為null，為null就說(shuō)明是相同的，而唯一索引是不允許有相同的值的，所以下面創(chuàng)建唯一組合索引時(shí)報(bào)錯(cuò)了。

所以建立唯一索引時(shí)，不管是對(duì)單個(gè)字段還是多個(gè)字段建立索引，則最好每一行均有此字段，否則會(huì)報(bào)錯(cuò)。

> db.data.find();
{ "_id" : ObjectId("4bee745a0863b1c233b8b7ea"), "name" : "caihuafeng" }
{ "_id" : ObjectId("4bee745f0863b1c233b8b7eb"), "website" : "1616.net" }
{ "_id" : ObjectId("4bee804ba23d558eb6687117"), "x" : 6, "name" : "caihuafeng1" }
{ "_id" : ObjectId("4bee804ba23d558eb6687118"), "x" : 6, "name" : "caihuafeng2" }
{ "_id" : ObjectId("4bee804ba23d558eb6687119"), "x" : 6, "name" : "caihuafeng3" }
{ "_id" : ObjectId("4bee804ba23d558eb668711a"), "x" : 6, "name" : "caihuafeng4" }
{ "_id" : ObjectId("4bee804ba23d558eb668711b"), "x" : 6, "name" : "caihuafeng5" }
{ "_id" : ObjectId("4bee804ba23d558eb668711c"), "x" : 6, "name" : "caihuafeng6" }
{ "_id" : ObjectId("4bee804ba23d558eb668711d"), "x" : 6, "name" : "caihuafeng7" }
{ "_id" : ObjectId("4bee804ba23d558eb668711e"), "x" : 6, "name" : "caihuafeng8" }
{ "_id" : ObjectId("4bee804ba23d558eb668711f"), "x" : 6, "name" : "caihuafeng9" }
{ "_id" : ObjectId("4bee804ba23d558eb6687120"), "x" : 6, "name" : "caihuafeng10" }
{ "_id" : ObjectId("4befb711b0e29ba1ce20e0bc"), "name" : "1616", "info" : { "url" : "http://www.1616.net/", "city" : "beijing" } }
{ "_id" : ObjectId("4befb723b0e29ba1ce20e0bd"), "name" : "hao123", "info" : { "url" : "http://www.hao123.com/", "city" : "beijing" } }
{ "_id" : ObjectId("4befb740b0e29ba1ce20e0be"), "name" : "ll4la", "info" : { "url" : "http://www.114la.com/", "city" : "dongguan" } }
{ "_id" : ObjectId("4befc51ab0e29ba1ce20e0c2"), "firstname" : "cai", "lastname" : "huafeng" }

> db.data.ensureIndex({firstname: 1, lastname: 1}, {unique: true});
E11000 duplicate key error index: recommender.data.$firstname_1_lastname_1 dup key: { : null, : null }下面我們用另外一個(gè)表person來(lái)進(jìn)行測(cè)試

> db.person.ensureIndex({firstname:1, lastname: 1},{unique: true});
> db.person.insert({firstname: 'cai', lastname: 'huafeng'});第二次插入同樣值的時(shí)候報(bào)錯(cuò)了，說(shuō)明唯一索引生效了，其實(shí)跟MySQL里面是一樣的。

> db.person.insert({firstname: 'cai', lastname: 'huafeng'});
E11000 duplicate key error index: recommender.person.$firstname_1_lastname_1 dup key: { : "cai", : "huafeng" }6.唯一索引中的重復(fù)值處理
刪除上述5中的索引，插入兩行一樣的記錄

> db.person.dropIndexes();
{
"nIndexesWas" : 2,
"msg" : "non-_id indexes dropped for collection",
"ok" : 1
}
> db.person.find();
{ "_id" : ObjectId("4befcda6b0e29ba1ce20e0cf"), "firstname" : "cai", "lastname" : "huafeng" }
> db.person.insert({firstname: 'cai', lastname: 'huafeng'});
> db.person.find();
{ "_id" : ObjectId("4befcda6b0e29ba1ce20e0cf"), "firstname" : "cai", "lastname" : "huafeng" }
{ "_id" : ObjectId("4befcef0b0e29ba1ce20e0d1"), "firstname" : "cai", "lastname" : "huafeng" }如果現(xiàn)在直接在字段firstname及l(fā)astname上面創(chuàng)建唯一組合索引的時(shí)候肯定會(huì)報(bào)錯(cuò)，我們來(lái)試一試:

> db.person.ensureIndex({firstname: 1, lastname: 1}, {unique: true});
E11000 duplicate key error index: recommender.person.$firstname_1_lastname_1 dup key: { : "cai", : "huafeng" }查看表person的索引，我們可以看到，新創(chuàng)建的索引沒(méi)有生成。

> db.person.getIndexes();
[
{
"name" : "_id_",
"ns" : "recommender.person",
"key" : {
"_id" : 1
}
}
]可以在第二個(gè)json對(duì)象加入一項(xiàng)dropDups: true，這樣在創(chuàng)建唯一組合索引的時(shí)候不會(huì)報(bào)錯(cuò)，保留文檔中第一個(gè)重復(fù)的值，其它重復(fù)的值均刪除。

再次測(cè)試一下，加入dropDups選項(xiàng)，雖然報(bào)錯(cuò)了，但是唯一組合索引已經(jīng)建立了。

> db.person.ensureIndex({firstname: 1, lastname: 1}, {unique: true, dropDups: true});
E11000 duplicate key error index: recommender.person.$firstname_1_lastname_1 dup key: { : "cai", : "huafeng" }
> db.person.getIndexes();
[
{
"name" : "_id_",
"ns" : "recommender.person",
"key" : {
"_id" : 1
}
},
{
"_id" : ObjectId("4befcfd9b0e29ba1ce20e0d3"),
"ns" : "recommender.person",
"key" : {
"firstname" : 1,
"lastname" : 1
},
"name" : "firstname_1_lastname_1",
"unique" : true,
"dropDups" : true
}
]再次查詢(xún)表person中的記錄，發(fā)現(xiàn)重復(fù)的記錄已經(jīng)自動(dòng)刪除了。

> db.person.find();
{ "_id" : ObjectId("4befcda6b0e29ba1ce20e0cf"), "firstname" : "cai", "lastname" : "huafeng" }MongoDB官方文檔的說(shuō)明
A unique index cannot be created on a key that has duplicate values. If you would like to create the index anyway, keeping the first document the database indexes and deleting all subsequent documents that have duplicate values, add the dropDups option.

db.things.ensureIndex({firstname : 1}, {unique : true, dropDups : true})7.刪除索引
a.刪除某個(gè)表中的所有索引
To delete all indexes on the specified collection:

db.collection.dropIndexes();b.刪除某個(gè)表中的單一索引
To delete a single index:

db.collection.dropIndex({x: 1, y: -1})

> db.data.dropIndex({firstname: 1, lastname: 1});
{ "nIndexesWas" : 6, "ok" : 1 }Running directly as a command without helper:

// note: command was "deleteIndexes", not "dropIndexes", before MongoDB v1.3.2
// remove index with key pattern {y:1} from collection foo
db.runCommand({dropIndexes:'foo', index : {y:1}})
// remove all indexes:
db.runCommand({dropIndexes:'foo', index : '*'})

> db.person.ensureIndex({firstname: 1, lastname: 1});

> db.runCommand({dropIndexes:'person', index:{firstname:1, lastname:1}});
{ "nIndexesWas" : 2, "ok" : 1 }延伸閱讀:
http://www.mongodb.org/display/D ... xes-DocumentsasKeys
http://www.mongodb.org/display/DOCS/min+and+max+Query+Specifiers
http://www.mongodb.org/display/DOCS/Advanced+Queries

如果有一天21

謝謝分享