索引

db.collection.createIndex(keys, options)

// 创建索引后台执行
db.values.createIndex({open: 1, close: 1}, {background: true})
// 创建唯一索引
db.values.createIndex({title:1},{unique:true})

// 查看索引信息
db.books.getIndexes()
// 查看索引键
db.books.getIndexKeys()

// 删除索引
db.collection.dropIndex(indexName)
// 删除所有索引，不能删除主键索引
db.collection.dropIndexes()

db.books.createIndex({title:1}) // 升序索引
// 对内嵌文档的字段进行索引
db.books.createIndex({"author.name":1})

db.books.createIndex({type:1,favCount:1})
// 查看执行计划
db.books.find({type:"novel",favCount:{$gt:50}}).explain()

db.inventory.insertMany([
    { _id: 5, type: "food", item: "aaa", ratings: [ 5, 8, 9 ] },
    { _id: 6, type: "food", item: "bbb", ratings: [ 5, 9 ] },
    { _id: 7, type: "food", item: "ccc", ratings: [ 9, 5, 8 ] },
    { _id: 8, type: "food", item: "ddd", ratings: [ 9, 5 ] },
    { _id: 9, type: "food", item: "eee", ratings: [ 5, 9, 5 ] }
])

// 创建多键索引
db.inventory.createIndex( { ratings: 1 } )

// 创建复合多值索引
db.inventory.createIndex( { item:1,ratings: 1 } )

db.inventory.insertMany([
{
  _id: 1,
  item: "abc",
  stock: [
    { size: "S", color: "red", quantity: 25 },
    { size: "S", color: "blue", quantity: 10 },
    { size: "M", color: "blue", quantity: 50 }
  ]
},
{
  _id: 2,
  item: "def",
  stock: [
    { size: "S", color: "blue", quantity: 20 },
    { size: "M", color: "blue", quantity: 5 },
    { size: "M", color: "black", quantity: 10 },
    { size: "L", color: "red", quantity: 2 }
  ]
},
{
  _id: 3,
  item: "ijk",
  stock: [
    { size: "M", color: "blue", quantity: 15 },
    { size: "L", color: "blue", quantity: 100 },
    { size: "L", color: "red", quantity: 25 }
  ]
}
])

// 在包含嵌套对象的数组字段上创建多键索引
db.inventory.createIndex( { "stock.size": 1, "stock.quantity": 1 } )

db.inventory.find({"stock.size":"S","stock.quantity":{$gt:20}}).explain()

db.users.createIndex({username : 'hashed'})

db.restaurant.insert({
    restaurantId: 0,
    restaurantName:"兰州牛肉面",
    location : {
        type: "Point",
        coordinates: [ -73.97, 40.77 ]
    }
})

db.restaurant.createIndex({location : "2dsphere"})

db.restaurant.find( { 
    location:{ 
        $near :{
            $geometry :{ 
                type : "Point" ,
                coordinates : [ -73.88, 40.78 ] 
            } ,
            $maxDistance: 10000 
        } 
    } 
})

db.reviews.createIndex( { comments: "text" } )

db.stores.insertMany(
   [
     { _id: 1, name: "Java Hut", description: "Coffee and cakes" },
     { _id: 2, name: "Burger Buns", description: "Gourmet hamburgers" },
     { _id: 3, name: "Coffee Shop", description: "Just coffee" },
     { _id: 4, name: "Clothes Clothes Clothes", description: "Discount clothing" },
     { _id: 5, name: "Java Shopping", description: "Indonesian goods" }
   ]
)

// 创建 name 和 description 的全文索引
db.stores.createIndex({name: "text", description: "text"})

db.stores.find({$text: {$search: "java coffee shop"}})

db.products.insert([
    {
      "product_name" : "Spy Coat",
      "product_attributes" : { // 每个商品的 product_attributes 中包含的属性是不一样的
        "material" : [ "Tweed", "Wool", "Leather" ],
        "size" : {
          "length" : 72,
          "units" : "inches"
        }
      }
    },
    {
      "product_name" : "Spy Pen",
      "product_attributes" : {
         "colors" : [ "Blue", "Black" ],
         "secret_feature" : {
           "name" : "laser",
           "power" : "1000",
           "units" : "watts",
         }
      }
    },
    {
      "product_name" : "Spy Book"
    }
])

// 创建通配符索引
// $** 表示任意字段
db.products.createIndex( { "product_attributes.$**" : 1 } )

db.products.find( { "product_attributes.size.length": { $gt : 60 } } )
db.products.find( { "product_attributes.material": "Leather" } )
db.products.find( { "product_attributes.secret_feature.name": "laser" } )

// 通配符索引不能支持以下查询
db.products.find( {"product_attributes" : { $exists : false } } )
db.products.aggregate([
  { $match : { "product_attributes" : { $exists : false } } }
])

// 精确匹配查数组中的其中一个条目，这条查询是支持的
db.products.find({ "product_attributes.colors" : "Blue" } )

// 通配符索引不能支持以下查询

// 匹配数组中的所有条目，这条查询是不支持的
db.products.find({ "product_attributes.colors" : [ "Blue", "Black" ] } )

db.products.aggregate([{
  $match : { "product_attributes.colors" : [ "Blue", "Black" ] } 
}])

// 创建唯一索引
db.values.createIndex({title:1},{unique:true})
// 复合索引支持唯一性约束
db.values.createIndex({title:1，type:1},{unique:true})
// 多键索引支持唯一性约束
db.inventory.createIndex( { ratings: 1 },{unique:true} )

db.restaurants.createIndex(
   { cuisine: 1, name: 1 }, // 索引字段
   { partialFilterExpression: { rating: { $gt: 5 } } } // 增加过滤条件，只对符合条件的文档才会加索引
)

// 符合条件，使用索引
db.restaurants.find( { cuisine: "Italian", rating: { $gte: 8 } } )
// 不符合条件，不能使用索引
db.restaurants.find( { cuisine: "Italian" } )

db.restaurants.insert({
   "_id" : ObjectId("5641f6a7522545bc535b5dc9"),
   "address" : {
      "building" : "1007",
      "coord" : [
         -73.856077,
         40.848447
      ],
      "street" : "Morris Park Ave",
      "zipcode" : "10462"
   },
   "borough" : "Bronx",
   "cuisine" : "Bakery",
   "rating" : { "date" : ISODate("2014-03-03T00:00:00Z"),
                "grade" : "A",
                "score" : 2
              },
   "name" : "Morris Park Bake Shop",
   "restaurant_id" : "30075445"
})

// 创建索引
db.restaurants.createIndex(
   { borough: 1, cuisine: 1 },
   { partialFilterExpression: { 'rating.grade': { $eq: "A" } } }
)

// 符合条件，使用索引
db.restaurants.find( { borough: "Bronx", 'rating.grade': "A" } )
// 不符合条件，不能使用索引
db.restaurants.find( { borough: "Bronx", cuisine: "Bakery" } )

db.users.insertMany( [
   { username: "david", age: 29 },
   { username: "amanda", age: 35 },
   { username: "rajiv", age: 57 }
] )

// 创建索引，指定 username 字段
// 唯一约束
// 部分过滤器表达式 age: {$gte: 21}，表示只有年龄大于等于 21 的文档才会有索引
// 因此，只有年龄大于等于 21 的文档并且 username 是唯一的，才会有索引
db.users.createIndex(
   { username: 1 },
   { unique: true, partialFilterExpression: { age: { $gte: 21 } } }
)

db.users.insertMany( [
   { username: "david", age: 27 },
   { username: "amanda", age: 25 },
   { username: "rajiv", age: 32 }
])

db.users.insertMany( [
   { username: "david", age: 20 },
   { username: "amanda" },
   { username: "rajiv", age: null }
])

// 不索引不包含 xmpp_id 字段的文档
db.addresses.createIndex( { "xmpp_id": 1 }, { sparse: true } )

db.scores.insertMany([
    {"userid" : "newbie"},
    {"userid" : "abby", "score" : 82},
    {"userid" : "nina", "score" : 90}
])

// 创建稀疏索引
db.scores.createIndex( { score: 1 } , { sparse: true } )

// 使用稀疏索引
db.scores.find( { score: { $lt: 90 } } )

// 即使排序是通过索引字段，MongoDB 也不会选择稀疏索引来完成查询，以返回完整的结果
db.scores.find().sort( { score: -1 } )

// 要使用稀疏索引，使用 hint() 显式指定索引
db.scores.find().sort( { score: -1 } ).hint( { score: 1 } )

db.scores.dropIndex({score:1})
// 创建具有唯一约束的稀疏索引
db.scores.createIndex( { score: 1 } , { sparse: true, unique: true } )

db.scores.insertMany( [
   { "userid": "AAAAAAA", "score": 50 },
   { "userid": "BBBBBBB", "score": 64 },
   { "userid": "CCCCCCC" },
   { "userid": "CCCCCCC" }
] )

db.scores.insertMany( [
   { "userid": "DDDDDDD", "score": 50 },
])

// 创建 TTL 索引，TTL 值为 3600 秒
db.eventlog.createIndex( { "lastModifiedDate": 1 }, { expireAfterSeconds: 3600 } )

db.log_events.insertOne( {
   "createdAt": new Date(),
   "logEvent": 2,
   "logMessage": "Success!"
})

// 创建 TTL 索引
db.log_events.createIndex( { "createdAt": 1 }, { expireAfterSeconds: 20 } )

db.runCommand({collMod:"log_events",index:{keyPattern:{createdAt:1},expireAfterSeconds:600}})

// 创建隐藏索引
db.restaurants.createIndex({ borough: 1 },{ hidden: true });
// 隐藏现有索引
db.restaurants.hideIndex( { borough: 1} );
db.restaurants.hideIndex( "索引名称" )
// 取消隐藏索引
db.restaurants.unhideIndex( { borough: 1} );
db.restaurants.unhideIndex( "索引名

db.scores.insertMany([
    {"userid" : "newbie"},
    {"userid" : "abby", "score" : 82},
    {"userid" : "nina", "score" : 90}
])

// 创建隐藏索引
db.scores.createIndex(
   { userid: 1 },
   { hidden: true }
)

// 查看索引信息
db.scores.getIndexes()

[
    // ...
    {
        "v": 2,
        "hidden": true,
        "key": {
            "userid": 1
        }
        // ...
    }
]

// 不使用索引
db.scores.find({userid:"abby"}).explain()

// 取消隐藏索引
db.scores.unhideIndex( { userid: 1} )
// 使用索引
db.scores.find({userid:"abby"}).explain()

// 查找所有年龄小于30岁的深圳市马拉松运动员
db.athelets.find({sport: "marathon", location: "sz", age: {$lt: 30}}})
// 创建复合索引
db.athelets.createIndex({sport:1, location:1, age:1})

db.collection.find().explain(<verbose>)

// title 字段无索引
db.books.find({title:"book-1"}).explain("queryPlanner")

{
   "queryPlanner" : {
      "plannerVersion" : 1,
      "namespace" : "test.books",
      "indexFilterSet" : false,
      "parsedQuery" : {
         "title" : {
            "$eq" : "book-1"
         }
      },
      "winningPlan" : {
         "stage" : "COLLSCAN", // 全表扫描
         "direction" : "forward"
      },
      "rejectedPlans" : []
   }
   // ...
}

// 创建索引
db.books.createIndex({title:1})

db.books.find({title:"book-1"}).explain("executionStats")

{
   "queryPlanner" : {
      // ...
      "winningPlan" : {
         "stage" : "FETCH", // 使用索引
         "inputStage" : {
            "stage" : "IXSCAN", // 索引扫描
            "keyPattern" : {
               "title" : 1
            },
            "indexName" : "title_1",
            "isMultiKey" : false,
            "multiKeyPaths" : {
               "title" : [ ]
            },
            "isUnique" : false,
            "isSparse" : false,
            "isPartial" : false,
            "indexVersion" : 2,
            "direction" : "forward",
            "indexBounds" : {
               "title" : [
                  "[\"book-1\", \"book-1\"]"
               ]
            }
         }
      },
      "rejectedPlans" : [ ]
   },
   "executionStats" : {
      "executionSuccess" : true,
      "nReturned" : 1, // 返回记录数
      "executionTimeMillis" : 1, // 执行时间
      "totalKeysExamined" : 1, // 索引扫描的次数
      "totalDocsExamined" : 1, // 扫描的文档数
      // ...
   }
}

"allPlansExecution" : [
      {
         "nReturned" : <int>,
         "executionTimeMillisEstimate" : <int>,
         "totalKeysExamined" : <int>,
         "totalDocsExamined" :<int>,
         "executionStages" : {
            "stage" : <STAGEA>,
            "nReturned" : <int>,
            "executionTimeMillisEstimate" : <int>,
            ...
            }
         }
      },
      ...
   ]