亚洲av成人无遮挡网站在线观看,少妇性bbb搡bbb爽爽爽,亚洲av日韩精品久久久久久,兔费看少妇性l交大片免费,无码少妇一区二区三区

Chinaunix

標題: MongoDB中索引的用法 [打印本頁]

作者: 聽老歌 時間: 2012-02-23 19:21
標題: MongoDB中索引的用法

MongoDB中索引的用法

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

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

索引能提高檢索數(shù)據(jù)的速度，你可以想像成在MySQL中創(chuàng)建索引一樣，同樣索引也是用B-Tree也實現(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的值，此時已經(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.默認索引
上述1中db.data.getIndexes()顯示出來的一共有2個索引，其中_id是創(chuà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的官方文檔上面是這樣說的:
Documents as Keys

Indexed fields may be of any type, including documents:

往數(shù)據(jù)庫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創(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"
}
]查找指定的記錄，此時會用到索引

> 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上面創(chuàng)建組合索引

> db.data.ensureIndex({name: 1, info: -1});當創(chuàng)建組合索引時，字段后面的1表示升序，-1表示降序，是用1還是用-1主要是跟排序的時候或指定范圍內(nèi)查詢的時候有關(guān)的，具體看下面的英文原文的說明。
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”實際上是”name” : “114la”(就是將數(shù)字一寫成了字母l)，但是我錄入的時候?qū)懗闪恕眓ame” : “l(fā)l4la”，是我寫錯了，但是排序的結(jié)果是對的。

> 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

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

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

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

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

> 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.唯一索引中的重復(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)建唯一組合索引的時候肯定會報錯，我們來試一試:

> 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)建的索引沒有生成。

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

再次測試一下，加入dropDups選項，雖然報錯了，但是唯一組合索引已經(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
}
]再次查詢表person中的記錄，發(fā)現(xiàn)重復(fù)的記錄已經(jīng)自動刪除了。

> 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 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 時間: 2012-02-24 17:01
謝謝分享

歡迎光臨 Chinaunix (http://www.72891.cn/)