Database Manual / Data Modeling / Schema Design Anti-Patterns

Remove Unnecessary Indexes删除不必要的索引

Creating indexes for every query can lead to unnecessary indexes, which can degrade database performance. Unnecessary indexes might be rarely used, redundant due to coverage by another compound index, or not used at all. 为每个查询创建索引可能会导致不必要的索引，从而降低数据库性能。不必要的索引可能很少使用，由于被另一个复合索引覆盖而冗余，或者根本不使用。To optimize database performance, it's important to minimize the number of indexes you use. Identify and remove unnecessary indexes to maintain efficient query execution and resource use.为了优化数据库性能，重要的是尽量减少使用的索引数量。识别并删除不必要的索引，以保持高效的查询执行和资源使用。

About this Task关于此任务

Consider the following courses collection, where each document stores information about a different school course.考虑以下courses集合，其中每个文档存储有关不同学校课程的信息。

// Biology course document
db.courses.insertOne(
   {
      _id: 1,
      course_name: "Biology 101",
      professor: "Tate",
      semester: "Fall",
      days: "Monday, Friday",
      time: "12:00",
      building: "Olson"
   }
 )

The courses collection has an index for every field:courses集合中的每个字段都有一个索引：

  • _id is indexed by default默认情况下已编入索引
  • { course_name: 1 }
  • { professor: 1 }
  • { semester: 1 }
  • { building: 1 }
  • { days: 1 }
  • { time: 1 }
  • { day: 1, time: 1 }

Creating indexes for every field in a collection can lead to bloated collections and negatively impact write performance.为集合中的每个字段创建索引可能会导致集合膨胀，并对写入性能产生负面影响。

Steps步骤

1

Evaluate index use评估指标使用情况

To determine which indexes are rarely used, use the $indexStats aggregation stage:要确定哪些索引很少使用，请使用$indexStats聚合阶段：

db.courses.aggregate( [ { $indexStats: { } } ] )

The operation returns the following:该操作返回以下内容：

[
   {
      name: "building_1",
      key: { "building": 1 },
      host: "M-C02FJ3BDML85:27017",
      accesses: { "ops": "Long('0')", "since": "ISODate('2024-06-24T17:35:00.000Z')" },
      spec: { "v": 2, "key": { "building": 1 }, "name": "building_1" }
   },
   {
      name: "day_1",
      key: { "day": 1 },
      host: "M-C02FJ3BDML85:27017",
      accesses: { "ops": "Long('1')", "since": "ISODate('2024-06-24T17:35:30.000Z')" },
      spec: { "v": 2, "key": { "day": 1 }, "name": "day_1" }
   },
   {
      name: "time_1",
      key: { "time": 1 },
      host: "M-C02FJ3BDML85:27017",
      accesses: { "ops": "Long('1')", "since": "ISODate('2024-06-24T17:36:00.000Z')" },
      spec: { "v": 2, "key": { "time": 1 }, "name": "time_1" }
   },
   {
      name: "day_1_time_1",
      key: { "day": 1, "time": 1 },
      host: "M-C02FJ3BDML85:27017",
      accesses: { "ops": "Long('110')", "since": "ISODate('2024-06-24T17:31:21.800Z')" },
      spec: { "v": 2, "key": { "day": 1, "time": 1 }, "name": "day_1_time_1" }
   },
   {
      name: "_id_",
      key: { "_id": 1 },
      host: "M-C02FJ3BDML85:27017",
      accesses: { "ops": "Long('150')", "since": "ISODate('2024-06-24T15:31:49.463Z')" },
      spec: { "v": 2, "key": { "_id": 1 }, "name": "_id_" }
   },
   {
      name: "course_name_1",
      key: { "course_name": 1 },
      host: "M-C02FJ3BDML85:27017",
      accesses: { "ops": "Long('120')", "since": "ISODate('2024-06-24T17:29:26.344Z')" },
      spec: { "v": 2, "key": { "course_name": 1 }, "name": "course_name_1" }
   },
   ...
]
  • The building_1 index can be dropped because it is not used for any queries, as indicated by its accesses count of zero.building_1索引可以删除，因为它不用于任何查询，如accesses计数为零所示。
  • The { days: 1 } and { time: 1 } indexes can be dropped because the compound index { day: 1, time: 1 } covers time-related queries.可以删除{ days: 1 }和{ time: 1 }索引，因为复合索引{ day: 1, time: 1 }涵盖了与时间相关的查询。

You can also use MongoDB Atlas Performance Advisor (available for M10 clusters or higher) and MongoDB Compass to determine, hide, and drop unnecessary indexes.您还可以使用MongoDB Atlas Performance Advisor（适用于M10或更高级别的集群）和MongoDB Compass来确定、隐藏和删除不必要的索引。

2

Hide Indexes隐藏索引

After you identify unnecessary indexes, you can use the db.collection.hideIndex() method to hide the indexes and evaluate their impact on the database before you remove them.在识别出不必要的索引后，您可以使用db.collection.hideIndex()方法隐藏索引，并在删除它们之前评估它们对数据库的影响。

db.courses.hideIndex( "days_1" )
db.courses.hideIndex( "time_1" )
db.courses.hideIndex( "building_1" )
3

Drop Indexes删除索引

If you determine the indexes are unnecessary and have a negative impact on performance, drop the indexes using the db.collection.dropIndexes() method.如果您确定这些索引是不必要的，并且对性能有负面影响，请使用db.collection.dropIndexes()方法删除这些索引。

db.courses.dropIndexes( [ "days_1", "time_1", "building_1" ] )

In this example, only the following indexes remain because they are the most frequently used and help optimize queries:在这个例子中，只剩下以下索引，因为它们是最常用的索引，有助于优化查询：

  • _id is indexed by default默认情况下已编入索引
  • { course_name: 1 }
  • { professor: 1 }
  • { semester: 1 }
  • { day: 1, time: 1 }

Learn More了解更多