Scenarios
In the same business scenario, the architecture design, as well as the design of databases, collections, and indexes, can significantly affect query performance. Good design improves query efficiency, whereas poor design can lead to a large number of slow queries (statements with excessively long execution times), which consume substantial system resources. This section describes the common causes of slow queries and their solutions.
Fault Locating
DDS allows you to view slow query logs on the console. You can start from the slowest operation recorded in the log and optimize the operations one by one.
- If a query takes longer than 1s, the corresponding operation may be abnormal. You need to analyze the problem based on the actual situation.
- If a query takes longer than 10s, the operation needs to be optimized.
If an aggregate operation takes more than 10s, it is normal.
Procedure
Analysis Method
- Connect to the database.
- Run the following command to check the execution plan of a slow query:
explain()
Example:
db.test.find({"data_id" : "ae4b5769-896f-465c-9fbd-3fd2f3357637"}).explain();
db.test.find({"data_id" : "775f57c2-b63e-45d7-b581-3822dba231b4"}).explain("executionStats"); A covered query does not need to read a document, but directly returns a result from an index, which is very efficient. You can use covering indexes as much as possible. If the output of explain() shows that indexOnly is true, the query is covered by an index.
- Parse the execution plan.
- Check the execution time.
The smaller the values of the following parameters, the better the performance: executionStats.executionStages.executionTimeMillisEstimate and executionStats.executionStages.inputStage. executionTimeMillisEstimate
Table 1 Parameter description | Parameter | Description |
| executionStats.executionTimeMillis | Execution plan selection and execution time |
| executionStats.executionStages.executionTimeMillisEstimate | Execution completion time of the execution plan |
| executionStats.executionStages.inputStage. executionTimeMillisEstimate | Execution completion time of the sub-phase of the execution plan |
- Check the number of scanned records.
If the three items in Table 2 have the same value, the query performance is the best.
Table 2 Parameter description | Parameter | Description |
| executionStats. nReturned | Number of documents matching the search criteria |
| executionStats .totalKeysExamined | Number of rows scanned through indexes |
| executionStats .totalDocsExamined | Number of scanned documents |
- Check the stage status.
The combinations of stage statuses with better performance are as follows:
- Fetch+IDHACK
- Fetch+ixscan,
- Limit+ (Fetch+ixscan)
- PROJECTION+ixscan
Table 3 Status description | Status Name | Description |
| COLLSCAN | Full table scan |
| SORT | In-memory sorting |
| IDHACK | _id-based query |
| TEXT | Full-text index |
| COUNTSCAN | Number of unused indexes |
| FETCH | Index scanning |
| LIMIT | Using Limit to limit the number of returned records |
| SUBPLA | $or query stage without using an index |
| PROJECTION | Number of used indexes |
| COUNT_SCAN | Number of used indexes |
Optimization Plan
- For queries without indexes, create indexes based on the search criteria.
- Hash indexes can be created for point queries.
- Create composite indexes for multi-field queries where a single field is highly repeated.
- Create an ascending or descending index for range lookups with ordered result sets.
- Compound indexes follow the leftmost-prefix rule, so the sequence of query conditions must match that of indexed fields.
- For partitioned collections (tables) and large collections (with more than 100,000 records), do not use fuzzy query (or do not use LIKE) for tables with a large amount of data. As a result, a large number of records are scanned. You can query data based on the index field, filter out small collections, and then perform fuzzy queries.
- Do not use $not. MongoDB does not index missing data. The $not query requires that all records be scanned in a single result collection. If $not is the only query condition, a full table scan will be performed on the collection.
- If you use $and, place the conditions that match the fewest results first. If you use $or, place the conditions that match the most results first.
- Check the performance baseline of instance specifications and analyze whether the current service requirements can be met. If the performance bottleneck of the current instance is reached, upgrade the instance specifications in a timely manner.
