> 数据库 > mongo > 怒钢DBA系列-mongo索引篇

怒钢DBA系列-mongo索引篇

mongo cyanprobe 8年前 (2017-10-10) 5079次浏览 已收录 1个评论

前言:

此话题说来话长，老大让写多个查询接口，尽量考虑索引使用，因为数据非常多，不使用索引查询很慢，于是乎，我写了一个通用查询接口，指定索引生成query对象。根据mongo权威指南的说法 建立复合索引 A_1_B_1_C_1 此时可用索引为 A-B-C |A-B| A  三种, 跟DBA说了讨论了这个问题(PS:我也不想讨论但是已经写完了真的懒得改了)，老大又说不能使用通用的DB层查询接口，索引也要为业务服务，于是乎我们3个人开始验证索引顺序对查询的影响，半小时被打N次脸，难道mongo权威指南是盗版书？  DBA 测试 A-B 和 B-A 都是用复合索引 A_1_B_1_C_1 且实际扫描文档数 和 返回文档树一致 2者相同。这就有点不科学了。但事实就摆在那里。。。一定有什么误会于是回家做实验了。

结论（PS 正文太长放结论先）:

假设存在索引 A_1_B_1_C_1
AB与BA 都可以正常使用索引效率相同
AC 可使用索引效率较低
BC 不可使用索引 进入 COLLSCAN stage
AXBC 可使用索引与ABCX效率相同
A:{$exists:true}BC 可使用索引  KEEP_MUTATIONS stage  需要复验后返回对性能有影响(很小)
SORT 情况
sort 情况顺序不可变，不可出现 AC 此时无法使用索引排序进入 SORT stage 是、排序效率降低。
即A存在在查询方面可无视对象排序,只排序时关注索引定义。prefix原则是只要存在index首字段即可并不需要考虑位置。

正文:

以公司的测试服务器的数据做实验 ,执行db.collection.getIndexes() 方法，此文档有大约27万数据。我们使用第8个复合索引作为测试索引

  1. {
  2. 	"v" : 1,
  3. 	"key" : {
  4. 		"state" : 1,
  5. 		"user" : 1,
  6. 		"stock" : 1
  7. 	},
  8. 	"name" : "state_1_user_1_stock_1",
  9. 	"ns" : "mango_order.od_reservations",
  10. 	"background" : true
  11. }

我们来看最佳 explain 即ABC db.od_reservations.find({state:1,user:32432424,stock:3243242}).explain(“executionStats”)

  1. {
  2. 	"queryPlanner" : {
  3. 		"plannerVersion" : 1,
  4. 		"namespace" : "mango_order.od_reservations",
  5. 		"indexFilterSet" : false,
  6. 		"parsedQuery" : {
  7. 			"$and" : [
  8. 				{
  9. 					"state" : {
  10. 						"$eq" : 1
  11. 					}
  12. 				},
  13. 				{
  14. 					"stock" : {
  15. 						"$eq" : 3243242
  16. 					}
  17. 				}
  18. 			]
  19. 		},
  20. 		"winningPlan" : {
  21. 			"stage" : "FETCH",
  22. 			"inputStage" : {
  23. 				"stage" : "IXSCAN",
  24. 				"keyPattern" : {
  25. 					"state" : 1,
  26. 					"user" : 1,
  27. 					"stock" : 1
  28. 				},
  29. 				"indexName" : "state_1_user_1_stock_1",
  30. 				"isMultiKey" : false,
  31. 				"isUnique" : false,
  32. 				"isSparse" : false,
  33. 				"isPartial" : false,
  34. 				"indexVersion" : 1,
  35. 				"direction" : "forward",
  36. 				"indexBounds" : {
  37. 					"state" : [
  38. 						"[1.0, 1.0]"
  39. 					],
  40. 					"user" : [
  41. 						"[MinKey, MaxKey]"
  42. 					],
  43. 					"stock" : [
  44. 						"[3243242.0, 3243242.0]"
  45. 					]
  46. 				}
  47. 			}
  48. 		},
  49. 		"rejectedPlans" : [ ]
  50. 	},
  51. 	"executionStats" : {
  52. 		"executionSuccess" : true,
  53. 		"nReturned" : 0,
  54. 		"executionTimeMillis" : 157,
  55. 		"totalKeysExamined" : 29806,
  56. 		"totalDocsExamined" : 0,
  57. 		"executionStages" : {
  58. 			"stage" : "FETCH",
  59. 			"nReturned" : 0,
  60. 			"executionTimeMillisEstimate" : 160,
  61. 			"works" : 29806,
  62. 			"advanced" : 0,
  63. 			"needTime" : 29805,
  64. 			"needYield" : 0,
  65. 			"saveState" : 232,
  66. 			"restoreState" : 232,
  67. 			"isEOF" : 1,
  68. 			"invalidates" : 0,
  69. 			"docsExamined" : 0,
  70. 			"alreadyHasObj" : 0,
  71. 			"inputStage" : {
  72. 				"stage" : "IXSCAN",
  73. 				"nReturned" : 0,
  74. 				"executionTimeMillisEstimate" : 160,
  75. 				"works" : 29806,
  76. 				"advanced" : 0,
  77. 				"needTime" : 29805,
  78. 				"needYield" : 0,
  79. 				"saveState" : 232,
  80. 				"restoreState" : 232,
  81. 				"isEOF" : 1,
  82. 				"invalidates" : 0,
  83. 				"keyPattern" : {
  84. 					"state" : 1,
  85. 					"user" : 1,
  86. 					"stock" : 1
  87. 				},
  88. 				"indexName" : "state_1_user_1_stock_1",
  89. 				"isMultiKey" : false,
  90. 				"isUnique" : false,
  91. 				"isSparse" : false,
  92. 				"isPartial" : false,
  93. 				"indexVersion" : 1,
  94. 				"direction" : "forward",
  95. 				"indexBounds" : {
  96. 					"state" : [
  97. 						"[1.0, 1.0]"
  98. 					],
  99. 					"user" : [
  100. 						"[MinKey, MaxKey]"
  101. 					],
  102. 					"stock" : [
  103. 						"[3243242.0, 3243242.0]"
  104. 					]
  105. 				},
  106. 				"keysExamined" : 29806,
  107. 				"dupsTested" : 0,
  108. 				"dupsDropped" : 0,
  109. 				"seenInvalidated" : 0
  110. 			}
  111. 		}
  112. 	},
  113. 	"serverInfo" : {
  114. 		"host" : "mango",
  115. 		"port" : 27017,
  116. 		"version" : "3.2.8",
  117. 		"gitVersion" : "ed70e33130c977bda0024c125b56d159573dbaf0"
  118. 	},
  119. 	"ok" : 1
  120. }

接下来尝试 A_C 检索 db.od_reservations.find({state:1,stock:3243242}).explain(“executionStats”) 证明 A_C 使用该复合索引

  1. {
  2. 	"queryPlanner" : {
  3. 		"plannerVersion" : 1,
  4. 		"namespace" : "mango_order.od_reservations",
  5. 		"indexFilterSet" : false,
  6. 		"parsedQuery" : {
  7. 			"$and" : [
  8. 				{
  9. 					"state" : {
  10. 						"$eq" : 1
  11. 					}
  12. 				},
  13. 				{
  14. 					"stock" : {
  15. 						"$eq" : 3243242
  16. 					}
  17. 				}
  18. 			]
  19. 		},
  20. 		"winningPlan" : {
  21. 			"stage" : "FETCH",
  22. 			"inputStage" : {
  23. 				"stage" : "IXSCAN",
  24. 				"keyPattern" : {
  25. 					"state" : 1,
  26. 					"user" : 1,
  27. 					"stock" : 1
  28. 				},
  29. 				"indexName" : "state_1_user_1_stock_1",
  30. 				"isMultiKey" : false,
  31. 				"isUnique" : false,
  32. 				"isSparse" : false,
  33. 				"isPartial" : false,
  34. 				"indexVersion" : 1,
  35. 				"direction" : "forward",
  36. 				"indexBounds" : {
  37. 					"state" : [
  38. 						"[1.0, 1.0]"
  39. 					],
  40. 					"user" : [
  41. 						"[MinKey, MaxKey]"
  42. 					],
  43. 					"stock" : [
  44. 						"[3243242.0, 3243242.0]"
  45. 					]
  46. 				}
  47. 			}
  48. 		},
  49. 		"rejectedPlans" : [ ]
  50. 	},
  51. 	"executionStats" : {
  52. 		"executionSuccess" : true,
  53. 		"nReturned" : 0,
  54. 		"executionTimeMillis" : 157,
  55. 		"totalKeysExamined" : 29806,
  56. 		"totalDocsExamined" : 0,
  57. 		"executionStages" : {
  58. 			"stage" : "FETCH",
  59. 			"nReturned" : 0,
  60. 			"executionTimeMillisEstimate" : 160,
  61. 			"works" : 29806,
  62. 			"advanced" : 0,
  63. 			"needTime" : 29805,
  64. 			"needYield" : 0,
  65. 			"saveState" : 232,
  66. 			"restoreState" : 232,
  67. 			"isEOF" : 1,
  68. 			"invalidates" : 0,
  69. 			"docsExamined" : 0,
  70. 			"alreadyHasObj" : 0,
  71. 			"inputStage" : {
  72. 				"stage" : "IXSCAN",
  73. 				"nReturned" : 0,
  74. 				"executionTimeMillisEstimate" : 160,
  75. 				"works" : 29806,
  76. 				"advanced" : 0,
  77. 				"needTime" : 29805,
  78. 				"needYield" : 0,
  79. 				"saveState" : 232,
  80. 				"restoreState" : 232,
  81. 				"isEOF" : 1,
  82. 				"invalidates" : 0,
  83. 				"keyPattern" : {
  84. 					"state" : 1,
  85. 					"user" : 1,
  86. 					"stock" : 1
  87. 				},
  88. 				"indexName" : "state_1_user_1_stock_1",
  89. 				"isMultiKey" : false,
  90. 				"isUnique" : false,
  91. 				"isSparse" : false,
  92. 				"isPartial" : false,
  93. 				"indexVersion" : 1,
  94. 				"direction" : "forward",
  95. 				"indexBounds" : {
  96. 					"state" : [
  97. 						"[1.0, 1.0]"
  98. 					],
  99. 					"user" : [
  100. 						"[MinKey, MaxKey]"
  101. 					],
  102. 					"stock" : [
  103. 						"[3243242.0, 3243242.0]"
  104. 					]
  105. 				},
  106. 				"keysExamined" : 29806,
  107. 				"dupsTested" : 0,
  108. 				"dupsDropped" : 0,
  109. 				"seenInvalidated" : 0
  110. 			}
  111. 		}
  112. 	},
  113. 	"serverInfo" : {
  114. 		"host" : "mango",
  115. 		"port" : 27017,
  116. 		"version" : "3.2.8",
  117. 		"gitVersion" : "ed70e33130c977bda0024c125b56d159573dbaf0"
  118. 	},
  119. 	"ok" : 1
  120. }

接下来验证 _BC db.od_reservations.find({user:32432424,stock:3243242}).explain(“executionStats”) 证明 _BC 不使用索引

  1. {
  2. 	"queryPlanner" : {
  3. 		"plannerVersion" : 1,
  4. 		"namespace" : "mango_order.od_reservations",
  5. 		"indexFilterSet" : false,
  6. 		"parsedQuery" : {
  7. 			"$and" : [
  8. 				{
  9. 					"stock" : {
  10. 						"$eq" : 3243242
  11. 					}
  12. 				},
  13. 				{
  14. 					"user" : {
  15. 						"$eq" : 32432424
  16. 					}
  17. 				}
  18. 			]
  19. 		},
  20. 		"winningPlan" : {
  21. 			"stage" : "COLLSCAN",
  22. 			"filter" : {
  23. 				"$and" : [
  24. 					{
  25. 						"stock" : {
  26. 							"$eq" : 3243242
  27. 						}
  28. 					},
  29. 					{
  30. 						"user" : {
  31. 							"$eq" : 32432424
  32. 						}
  33. 					}
  34. 				]
  35. 			},
  36. 			"direction" : "forward"
  37. 		},
  38. 		"rejectedPlans" : [ ]
  39. 	},
  40. 	"executionStats" : {
  41. 		"executionSuccess" : true,
  42. 		"nReturned" : 0,
  43. 		"executionTimeMillis" : 92,
  44. 		"totalKeysExamined" : 0,
  45. 		"totalDocsExamined" : 277389,
  46. 		"executionStages" : {
  47. 			"stage" : "COLLSCAN",
  48. 			"filter" : {
  49. 				"$and" : [
  50. 					{
  51. 						"stock" : {
  52. 							"$eq" : 3243242
  53. 						}
  54. 					},
  55. 					{
  56. 						"user" : {
  57. 							"$eq" : 32432424
  58. 						}
  59. 					}
  60. 				]
  61. 			},
  62. 			"nReturned" : 0,
  63. 			"executionTimeMillisEstimate" : 80,
  64. 			"works" : 277401,
  65. 			"advanced" : 0,
  66. 			"needTime" : 277390,
  67. 			"needYield" : 10,
  68. 			"saveState" : 2171,
  69. 			"restoreState" : 2171,
  70. 			"isEOF" : 1,
  71. 			"invalidates" : 0,
  72. 			"direction" : "forward",
  73. 			"docsExamined" : 277389
  74. 		}
  75. 	},
  76. 	"serverInfo" : {
  77. 		"host" : "mango",
  78. 		"port" : 27017,
  79. 		"version" : "3.2.8",
  80. 		"gitVersion" : "ed70e33130c977bda0024c125b56d159573dbaf0"
  81. 	},
  82. 	"ok" : 1
  83. }

接下来验证 BA 是否使用索引 db.od_reservations.find({user:32432424,state:1}).explain(“executionStats”) 证明DBA正确,此时使用了复合索引 我们接下来看看和AB是否有性能差距

  1. {
  2. 	"queryPlanner" : {
  3. 		"plannerVersion" : 1,
  4. 		"namespace" : "mango_order.od_reservations",
  5. 		"indexFilterSet" : false,
  6. 		"parsedQuery" : {
  7. 			"$and" : [
  8. 				{
  9. 					"state" : {
  10. 						"$eq" : 1
  11. 					}
  12. 				},
  13. 				{
  14. 					"user" : {
  15. 						"$eq" : 32432424
  16. 					}
  17. 				}
  18. 			]
  19. 		},
  20. 		"winningPlan" : {
  21. 			"stage" : "FETCH",
  22. 			"inputStage" : {
  23. 				"stage" : "IXSCAN",
  24. 				"keyPattern" : {
  25. 					"state" : 1,
  26. 					"user" : 1,
  27. 					"stock" : 1
  28. 				},
  29. 				"indexName" : "state_1_user_1_stock_1",
  30. 				"isMultiKey" : false,
  31. 				"isUnique" : false,
  32. 				"isSparse" : false,
  33. 				"isPartial" : false,
  34. 				"indexVersion" : 1,
  35. 				"direction" : "forward",
  36. 				"indexBounds" : {
  37. 					"state" : [
  38. 						"[1.0, 1.0]"
  39. 					],
  40. 					"user" : [
  41. 						"[32432424.0, 32432424.0]"
  42. 					],
  43. 					"stock" : [
  44. 						"[MinKey, MaxKey]"
  45. 					]
  46. 				}
  47. 			}
  48. 		},
  49. 		"rejectedPlans" : [ ]
  50. 	},
  51. 	"executionStats" : {
  52. 		"executionSuccess" : true,
  53. 		"nReturned" : 0,
  54. 		"executionTimeMillis" : 0,
  55. 		"totalKeysExamined" : 0,
  56. 		"totalDocsExamined" : 0,
  57. 		"executionStages" : {
  58. 			"stage" : "FETCH",
  59. 			"nReturned" : 0,
  60. 			"executionTimeMillisEstimate" : 0,
  61. 			"works" : 1,
  62. 			"advanced" : 0,
  63. 			"needTime" : 0,
  64. 			"needYield" : 0,
  65. 			"saveState" : 0,
  66. 			"restoreState" : 0,
  67. 			"isEOF" : 1,
  68. 			"invalidates" : 0,
  69. 			"docsExamined" : 0,
  70. 			"alreadyHasObj" : 0,
  71. 			"inputStage" : {
  72. 				"stage" : "IXSCAN",
  73. 				"nReturned" : 0,
  74. 				"executionTimeMillisEstimate" : 0,
  75. 				"works" : 1,
  76. 				"advanced" : 0,
  77. 				"needTime" : 0,
  78. 				"needYield" : 0,
  79. 				"saveState" : 0,
  80. 				"restoreState" : 0,
  81. 				"isEOF" : 1,
  82. 				"invalidates" : 0,
  83. 				"keyPattern" : {
  84. 					"state" : 1,
  85. 					"user" : 1,
  86. 					"stock" : 1
  87. 				},
  88. 				"indexName" : "state_1_user_1_stock_1",
  89. 				"isMultiKey" : false,
  90. 				"isUnique" : false,
  91. 				"isSparse" : false,
  92. 				"isPartial" : false,
  93. 				"indexVersion" : 1,
  94. 				"direction" : "forward",
  95. 				"indexBounds" : {
  96. 					"state" : [
  97. 						"[1.0, 1.0]"
  98. 					],
  99. 					"user" : [
  100. 						"[32432424.0, 32432424.0]"
  101. 					],
  102. 					"stock" : [
  103. 						"[MinKey, MaxKey]"
  104. 					]
  105. 				},
  106. 				"keysExamined" : 0,
  107. 				"dupsTested" : 0,
  108. 				"dupsDropped" : 0,
  109. 				"seenInvalidated" : 0
  110. 			}
  111. 		}
  112. 	},
  113. 	"serverInfo" : {
  114. 		"host" : "mango",
  115. 		"port" : 27017,
  116. 		"version" : "3.2.8",
  117. 		"gitVersion" : "ed70e33130c977bda0024c125b56d159573dbaf0"
  118. 	},
  119. 	"ok" : 1
  120. }

验证 AB db.od_reservations.find({state:1,user:32432424}).explain(“executionStats”) 证明 BA与AB索引效率相同,AB可高效使用索引而AC则不行。

  1. {
  2. 	"queryPlanner" : {
  3. 		"plannerVersion" : 1,
  4. 		"namespace" : "mango_order.od_reservations",
  5. 		"indexFilterSet" : false,
  6. 		"parsedQuery" : {
  7. 			"$and" : [
  8. 				{
  9. 					"state" : {
  10. 						"$eq" : "1703282329098"
  11. 					}
  12. 				},
  13. 				{
  14. 					"user" : {
  15. 						"$eq" : 32432424
  16. 					}
  17. 				}
  18. 			]
  19. 		},
  20. 		"winningPlan" : {
  21. 			"stage" : "FETCH",
  22. 			"inputStage" : {
  23. 				"stage" : "IXSCAN",
  24. 				"keyPattern" : {
  25. 					"state" : 1,
  26. 					"user" : 1,
  27. 					"stock" : 1
  28. 				},
  29. 				"indexName" : "state_1_user_1_stock_1",
  30. 				"isMultiKey" : false,
  31. 				"isUnique" : false,
  32. 				"isSparse" : false,
  33. 				"isPartial" : false,
  34. 				"indexVersion" : 1,
  35. 				"direction" : "forward",
  36. 				"indexBounds" : {
  37. 					"state" : [
  38. 						"[\"1703282329098\", \"1703282329098\"]"
  39. 					],
  40. 					"user" : [
  41. 						"[32432424.0, 32432424.0]"
  42. 					],
  43. 					"stock" : [
  44. 						"[MinKey, MaxKey]"
  45. 					]
  46. 				}
  47. 			}
  48. 		},
  49. 		"rejectedPlans" : [ ]
  50. 	},
  51. 	"executionStats" : {
  52. 		"executionSuccess" : true,
  53. 		"nReturned" : 0,
  54. 		"executionTimeMillis" : 0,
  55. 		"totalKeysExamined" : 0,
  56. 		"totalDocsExamined" : 0,
  57. 		"executionStages" : {
  58. 			"stage" : "FETCH",
  59. 			"nReturned" : 0,
  60. 			"executionTimeMillisEstimate" : 0,
  61. 			"works" : 1,
  62. 			"advanced" : 0,
  63. 			"needTime" : 0,
  64. 			"needYield" : 0,
  65. 			"saveState" : 0,
  66. 			"restoreState" : 0,
  67. 			"isEOF" : 1,
  68. 			"invalidates" : 0,
  69. 			"docsExamined" : 0,
  70. 			"alreadyHasObj" : 0,
  71. 			"inputStage" : {
  72. 				"stage" : "IXSCAN",
  73. 				"nReturned" : 0,
  74. 				"executionTimeMillisEstimate" : 0,
  75. 				"works" : 1,
  76. 				"advanced" : 0,
  77. 				"needTime" : 0,
  78. 				"needYield" : 0,
  79. 				"saveState" : 0,
  80. 				"restoreState" : 0,
  81. 				"isEOF" : 1,
  82. 				"invalidates" : 0,
  83. 				"keyPattern" : {
  84. 					"state" : 1,
  85. 					"user" : 1,
  86. 					"stock" : 1
  87. 				},
  88. 				"indexName" : "state_1_user_1_stock_1",
  89. 				"isMultiKey" : false,
  90. 				"isUnique" : false,
  91. 				"isSparse" : false,
  92. 				"isPartial" : false,
  93. 				"indexVersion" : 1,
  94. 				"direction" : "forward",
  95. 				"indexBounds" : {
  96. 					"state" : [
  97. 						"[\"1703282329098\", \"1703282329098\"]"
  98. 					],
  99. 					"user" : [
  100. 						"[32432424.0, 32432424.0]"
  101. 					],
  102. 					"stock" : [
  103. 						"[MinKey, MaxKey]"
  104. 					]
  105. 				},
  106. 				"keysExamined" : 0,
  107. 				"dupsTested" : 0,
  108. 				"dupsDropped" : 0,
  109. 				"seenInvalidated" : 0
  110. 			}
  111. 		}
  112. 	},
  113. 	"serverInfo" : {
  114. 		"host" : "mango",
  115. 		"port" : 27017,
  116. 		"version" : "3.2.8",
  117. 		"gitVersion" : "ed70e33130c977bda0024c125b56d159573dbaf0"
  118. 	},
  119. 	"ok" : 1
  120. }

验证 $exists A:$exists—B db.od_reservations.find({state:{$exists: true},user:32432424}).explain(“executionStats”) 结论 $exites 返回多结果对性能有影响(
KEEP_UTATIONS stage 表示检索到结果后,在检索过程中的删除或者修改，会触发一个复验的过程，当复验成功文档将被返回

  1. {
  2. 	"queryPlanner" : {
  3. 		"plannerVersion" : 1,
  4. 		"namespace" : "mango_order.od_reservations",
  5. 		"indexFilterSet" : false,
  6. 		"parsedQuery" : {
  7. 			"$and" : [
  8. 				{
  9. 					"user" : {
  10. 						"$eq" : 32432424
  11. 					}
  12. 				},
  13. 				{
  14. 					"state" : {
  15. 						"$exists" : true
  16. 					}
  17. 				}
  18. 			]
  19. 		},
  20. 		"winningPlan" : {
  21. 			"stage" : "KEEP_MUTATIONS",
  22. 			"inputStage" : {
  23. 				"stage" : "FETCH",
  24. 				"filter" : {
  25. 					"state" : {
  26. 						"$exists" : true
  27. 					}
  28. 				},
  29. 				"inputStage" : {
  30. 					"stage" : "IXSCAN",
  31. 					"keyPattern" : {
  32. 						"state" : 1,
  33. 						"user" : 1,
  34. 						"stock" : 1
  35. 					},
  36. 					"indexName" : "state_1_user_1_stock_1",
  37. 					"isMultiKey" : false,
  38. 					"isUnique" : false,
  39. 					"isSparse" : false,
  40. 					"isPartial" : false,
  41. 					"indexVersion" : 1,
  42. 					"direction" : "forward",
  43. 					"indexBounds" : {
  44. 						"state" : [
  45. 							"[MinKey, MaxKey]"
  46. 						],
  47. 						"user" : [
  48. 							"[32432424.0, 32432424.0]"
  49. 						],
  50. 						"stock" : [
  51. 							"[MinKey, MaxKey]"
  52. 						]
  53. 					}
  54. 				}
  55. 			}
  56. 		},
  57. 		"rejectedPlans" : [ ]
  58. 	},
  59. 	"executionStats" : {
  60. 		"executionSuccess" : true,
  61. 		"nReturned" : 0,
  62. 		"executionTimeMillis" : 0,
  63. 		"totalKeysExamined" : 3,
  64. 		"totalDocsExamined" : 0,
  65. 		"executionStages" : {
  66. 			"stage" : "KEEP_MUTATIONS",
  67. 			"nReturned" : 0,
  68. 			"executionTimeMillisEstimate" : 0,
  69. 			"works" : 4,
  70. 			"advanced" : 0,
  71. 			"needTime" : 3,
  72. 			"needYield" : 0,
  73. 			"saveState" : 0,
  74. 			"restoreState" : 0,
  75. 			"isEOF" : 1,
  76. 			"invalidates" : 0,
  77. 			"inputStage" : {
  78. 				"stage" : "FETCH",
  79. 				"filter" : {
  80. 					"state" : {
  81. 						"$exists" : true
  82. 					}
  83. 				},
  84. 				"nReturned" : 0,
  85. 				"executionTimeMillisEstimate" : 0,
  86. 				"works" : 4,
  87. 				"advanced" : 0,
  88. 				"needTime" : 3,
  89. 				"needYield" : 0,
  90. 				"saveState" : 0,
  91. 				"restoreState" : 0,
  92. 				"isEOF" : 1,
  93. 				"invalidates" : 0,
  94. 				"docsExamined" : 0,
  95. 				"alreadyHasObj" : 0,
  96. 				"inputStage" : {
  97. 					"stage" : "IXSCAN",
  98. 					"nReturned" : 0,
  99. 					"executionTimeMillisEstimate" : 0,
  100. 					"works" : 4,
  101. 					"advanced" : 0,
  102. 					"needTime" : 3,
  103. 					"needYield" : 0,
  104. 					"saveState" : 0,
  105. 					"restoreState" : 0,
  106. 					"isEOF" : 1,
  107. 					"invalidates" : 0,
  108. 					"keyPattern" : {
  109. 						"state" : 1,
  110. 						"user" : 1,
  111. 						"stock" : 1
  112. 					},
  113. 					"indexName" : "state_1_user_1_stock_1",
  114. 					"isMultiKey" : false,
  115. 					"isUnique" : false,
  116. 					"isSparse" : false,
  117. 					"isPartial" : false,
  118. 					"indexVersion" : 1,
  119. 					"direction" : "forward",
  120. 					"indexBounds" : {
  121. 						"state" : [
  122. 							"[MinKey, MaxKey]"
  123. 						],
  124. 						"user" : [
  125. 							"[32432424.0, 32432424.0]"
  126. 						],
  127. 						"stock" : [
  128. 							"[MinKey, MaxKey]"
  129. 						]
  130. 					},
  131. 					"keysExamined" : 3,
  132. 					"dupsTested" : 0,
  133. 					"dupsDropped" : 0,
  134. 					"seenInvalidated" : 0
  135. 				}
  136. 			}
  137. 		}
  138. 	},
  139. 	"serverInfo" : {
  140. 		"host" : "mango",
  141. 		"port" : 27017,
  142. 		"version" : "3.2.8",
  143. 		"gitVersion" : "ed70e33130c977bda0024c125b56d159573dbaf0"
  144. 	},
  145. 	"ok" : 1
  146. }

sort情况 db.od_reservations.find({state:{$exists:true},user:{$exists:true},stock:{$exists:true}}).sort({ state:1, stock:1}).explain(“executionStats”)
进行多种测试证明 sort排序时不能使用 AC BC 等索引 属于 SORT stage

  1. {
  2. 	"queryPlanner" : {
  3. 		"plannerVersion" : 1,
  4. 		"namespace" : "mango_order.od_reservations",
  5. 		"indexFilterSet" : false,
  6. 		"parsedQuery" : {
  7. 			"$and" : [
  8. 				{
  9. 					"state" : {
  10. 						"$exists" : true
  11. 					}
  12. 				},
  13. 				{
  14. 					"stock" : {
  15. 						"$exists" : true
  16. 					}
  17. 				},
  18. 				{
  19. 					"user" : {
  20. 						"$exists" : true
  21. 					}
  22. 				}
  23. 			]
  24. 		},
  25. 		"winningPlan" : {
  26. 			"stage" : "SORT",
  27. 			"sortPattern" : {
  28. 				"state" : 1,
  29. 				"stock" : 1
  30. 			},
  31. 			"inputStage" : {
  32. 				"stage" : "KEEP_MUTATIONS",
  33. 				"inputStage" : {
  34. 					"stage" : "SORT_KEY_GENERATOR",
  35. 					"inputStage" : {
  36. 						"stage" : "FETCH",
  37. 						"filter" : {
  38. 							"$and" : [
  39. 								{
  40. 									"state" : {
  41. 										"$exists" : true
  42. 									}
  43. 								},
  44. 								{
  45. 									"user" : {
  46. 										"$exists" : true
  47. 									}
  48. 								},
  49. 								{
  50. 									"stock" : {
  51. 										"$exists" : true
  52. 									}
  53. 								}
  54. 							]
  55. 						},
  56. 						"inputStage" : {
  57. 							"stage" : "IXSCAN",
  58. 							"keyPattern" : {
  59. 								"state" : 1,
  60. 								"user" : 1,
  61. 								"stock" : 1
  62. 							},
  63. 							"indexName" : "state_1_user_1_stock_1",
  64. 							"isMultiKey" : false,
  65. 							"isUnique" : false,
  66. 							"isSparse" : false,
  67. 							"isPartial" : false,
  68. 							"indexVersion" : 1,
  69. 							"direction" : "forward",
  70. 							"indexBounds" : {
  71. 								"state" : [
  72. 									"[MinKey, MaxKey]"
  73. 								],
  74. 								"user" : [
  75. 									"[MinKey, MaxKey]"
  76. 								],
  77. 								"stock" : [
  78. 									"[MinKey, MaxKey]"
  79. 								]
  80. 							}
  81. 						}
  82. 					}
  83. 				}
  84. 			}
  85. 		},
  86. 		"rejectedPlans" : [ ]
  87. 	},
  88. 	"executionStats" : {
  89. 		"executionSuccess" : false,
  90. 		"errorMessage" : "Exec error: OperationFailed: Sort operation used more than the maximum 33554432 bytes of RAM. Add an index, or specify a smaller limit., state: FAILURE",
  91. 		"errorCode" : 96,
  92. 		"nReturned" : 0,
  93. 		"executionTimeMillis" : 313,
  94. 		"totalKeysExamined" : 101140,
  95. 		"totalDocsExamined" : 101140,
  96. 		"executionStages" : {
  97. 			"stage" : "SORT",
  98. 			"nReturned" : 0,
  99. 			"executionTimeMillisEstimate" : 310,
  100. 			"works" : 101142,
  101. 			"advanced" : 0,
  102. 			"needTime" : 101141,
  103. 			"needYield" : 0,
  104. 			"saveState" : 790,
  105. 			"restoreState" : 790,
  106. 			"isEOF" : 0,
  107. 			"invalidates" : 0,
  108. 			"sortPattern" : {
  109. 				"state" : 1,
  110. 				"stock" : 1
  111. 			},
  112. 			"memUsage" : 33554524,
  113. 			"memLimit" : 33554432,
  114. 			"inputStage" : {
  115. 				"stage" : "KEEP_MUTATIONS",
  116. 				"nReturned" : 101140,
  117. 				"executionTimeMillisEstimate" : 260,
  118. 				"works" : 101141,
  119. 				"advanced" : 101140,
  120. 				"needTime" : 1,
  121. 				"needYield" : 0,
  122. 				"saveState" : 790,
  123. 				"restoreState" : 790,
  124. 				"isEOF" : 0,
  125. 				"invalidates" : 0,
  126. 				"inputStage" : {
  127. 					"stage" : "SORT_KEY_GENERATOR",
  128. 					"nReturned" : 0,
  129. 					"executionTimeMillisEstimate" : 260,
  130. 					"works" : 101141,
  131. 					"advanced" : 0,
  132. 					"needTime" : 1,
  133. 					"needYield" : 0,
  134. 					"saveState" : 790,
  135. 					"restoreState" : 790,
  136. 					"isEOF" : 0,
  137. 					"invalidates" : 0,
  138. 					"inputStage" : {
  139. 						"stage" : "FETCH",
  140. 						"filter" : {
  141. 							"$and" : [
  142. 								{
  143. 									"state" : {
  144. 										"$exists" : true
  145. 									}
  146. 								},
  147. 								{
  148. 									"user" : {
  149. 										"$exists" : true
  150. 									}
  151. 								},
  152. 								{
  153. 									"stock" : {
  154. 										"$exists" : true
  155. 									}
  156. 								}
  157. 							]
  158. 						},
  159. 						"nReturned" : 101140,
  160. 						"executionTimeMillisEstimate" : 200,
  161. 						"works" : 101140,
  162. 						"advanced" : 101140,
  163. 						"needTime" : 0,
  164. 						"needYield" : 0,
  165. 						"saveState" : 790,
  166. 						"restoreState" : 790,
  167. 						"isEOF" : 0,
  168. 						"invalidates" : 0,
  169. 						"docsExamined" : 101140,
  170. 						"alreadyHasObj" : 0,
  171. 						"inputStage" : {
  172. 							"stage" : "IXSCAN",
  173. 							"nReturned" : 101140,
  174. 							"executionTimeMillisEstimate" : 40,
  175. 							"works" : 101140,
  176. 							"advanced" : 101140,
  177. 							"needTime" : 0,
  178. 							"needYield" : 0,
  179. 							"saveState" : 790,
  180. 							"restoreState" : 790,
  181. 							"isEOF" : 0,
  182. 							"invalidates" : 0,
  183. 							"keyPattern" : {
  184. 								"state" : 1,
  185. 								"user" : 1,
  186. 								"stock" : 1
  187. 							},
  188. 							"indexName" : "state_1_user_1_stock_1",
  189. 							"isMultiKey" : false,
  190. 							"isUnique" : false,
  191. 							"isSparse" : false,
  192. 							"isPartial" : false,
  193. 							"indexVersion" : 1,
  194. 							"direction" : "forward",
  195. 							"indexBounds" : {
  196. 								"state" : [
  197. 									"[MinKey, MaxKey]"
  198. 								],
  199. 								"user" : [
  200. 									"[MinKey, MaxKey]"
  201. 								],
  202. 								"stock" : [
  203. 									"[MinKey, MaxKey]"
  204. 								]
  205. 							},
  206. 							"keysExamined" : 101140,
  207. 							"dupsTested" : 0,
  208. 							"dupsDropped" : 0,
  209. 							"seenInvalidated" : 0
  210. 						}
  211. 					}
  212. 				}
  213. 			}
  214. 		}
  215. 	},
  216. 	"serverInfo" : {
  217. 		"host" : "mango",
  218. 		"port" : 27017,
  219. 		"version" : "3.2.8",
  220. 		"gitVersion" : "ed70e33130c977bda0024c125b56d159573dbaf0"
  221. 	},
  222. 	"ok" : 1
  223. }

 

CyanProbe , 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权
转载请注明原文链接：怒钢DBA系列-mongo索引篇
喜欢 (1)
发表我的评论
取消评论

表情 贴图 加粗 删除线 居中 斜体 签到

Hi，您需要填写昵称和邮箱！

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
(1)个小伙伴在吐槽
  1. 大。。。大佬！
    姜辰2017-10-14 12:35 回复