mongoDB 索引的用法

標籤：des   style   blog   http   io   color   ar   os   sp   

http://www.cnblogs.com/lipan/archive/2011/03/28/1997202.html MongoDB中的索引其實類似於關係型資料庫，都是為了提高查詢和排序的效率的，並且實現原理也基本一致。由於集合中的鍵(欄位)可以是普通資料類型，也可以是子文檔。MongoDB可以在各種類型的鍵上建立索引。下面分別講解各種類型的索引的建立，查詢，以及索引的維護等。

 

本文是一篇轉載文章，作者在對MongoDB文檔進行了細緻的閱讀後，總結出了MongoDB的各種索引的用法。

原文連結：http://iamcaihuafeng.blog.sohu.com/151638529.html

索引能提高檢索資料的速度，你可以想像成在MySQL中建立索引一樣，同樣索引也是用B-Tree也實現的。

1.單列索引
在欄位x上建立索引，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的值，此時已經用到索引了

> 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.預設索引
上述1中db.data.getIndexes()顯示出來的一共有2個索引，其中_id是建立表的時候自動建立的索引，此索引是不能夠刪除的。

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的官方文檔上面是這樣說的:
Documents as Keys

Indexed fields may be of any type, including documents:

往資料庫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"}});

對欄位info建立索引

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

尋找指定的記錄，此時會用到索引

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

下面幾個操作均會用到索引

> 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中的複合式索引是有點不同的，4裡面是對一級欄位建立複合式索引，而上述3中是對二級欄位建立複合式索引。

在欄位name及info上面建立複合式索引

> db.data.ensureIndex({name: 1, info: -1});

當建立複合式索引時，欄位後面的1表示升序，-1表示降序，是用1還是用-1主要是跟排序的時候或指定範圍內查詢的時候有關的，具體看下面的英文原文的說明。
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"}]

下面的排序將用到上面的索引
最後一行的”name” : “ll4la”實際上是”name” : “114la”(就是將數字一寫成了字母l)，但是我錄入的時候寫成了”name” : “ll4la”，是我寫錯了，但是排序的結果是對的。

> 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複合式索引規則
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

如果用過MySQL的話，看起來是不是很熟悉，原理跟MySQL是一樣的。

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

> db.data.insert({firstname: "cai", lastname: "huafeng"});

由於表data中只有一記錄有欄位firstname及lastname，其它的行均沒有相應的值，也就是均為null，為null就說明是相同的，而唯一索引是不允許有相同的值的，所以下面建立唯一複合式索引時報錯了。

所以建立唯一索引時，不管是對單個欄位還是多個欄位建立索引，則最好每一行均有此欄位，否則會報錯。

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

下面我們用另外一個表person來進行測試

> db.person.ensureIndex({firstname:1, lastname: 1},{unique: true});> db.person.insert({firstname: ‘cai‘, lastname: ‘huafeng‘});

第二次插入同樣值的時候報錯了，說明唯一索引生效了，其實跟MySQL裡面是一樣的。

> db.person.insert({firstname: ‘cai‘, lastname: ‘huafeng‘});E11000 duplicate key error index: recommender.person.$firstname_1_lastname_1 dup key: { : "cai", : "huafeng" }

6.唯一索引中的重複值處理
刪除上述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" }

如果現在直接在欄位firstname及lastname上面建立唯一複合式索引的時候肯定會報錯，我們來試一試:

> 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的索引，我們可以看到，新建立的索引沒有產生。

> db.person.getIndexes();[{"name" : "_id_","ns" : "recommender.person","key" : {"_id" : 1}}]

可以在第二個json對象加入一項dropDups: true，這樣在建立唯一複合式索引的時候不會報錯，保留文檔中第一個重複的值，其它重複的值均刪除。

再次測試一下，加入dropDups選項，雖然報錯了，但是唯一複合式索引已經建立了。

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

重新查詢表person中的記錄，發現重複的記錄已經自動刪除了。

> db.person.find();{ "_id" : ObjectId("4befcda6b0e29ba1ce20e0cf"), "firstname" : "cai", "lastname" : "huafeng" }

MongoDB官方文檔的說明
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.刪除某個表中的所有索引
To delete all indexes on the specified collection:

db.collection.dropIndexes();

b.刪除某個表中的單一索引
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 foodb.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/DOCS/Indexes#Indexes-DocumentsasKeys
http://www.mongodb.org/display/DOCS/min+and+max+Query+Specifiers
http://www.mongodb.org/display/DOCS/Advanced+Queries

mongoDB 索引的用法