Multi-table join ($lookup)

The MongoDB `lookup` operator provides cross-collection join-like query functionality similar to SQL's JOIN. Its basic syntax includes the target collection, local field, foreign field, and output field name. It supports multi-field associations and nested document associations, with complex conditional joins achieved through `let` and `pipeline` parameters. After joining, `unwind` can be used to expand arrays or filter results. Performance optimization requires indexing the join fields, and sharded collections need special declarations. It supports multi-level joins and self-referencing associations for complex scenarios. Compared to application-layer JOINs and pre-stored reference design patterns, each approach has suitable use cases. Finally, an e-commerce order query example demonstrates practical application, combining stages like `match`, `lookup`, and `project` to implement complete business queries.

Data grouping and statistics ($group, $sum, $avg, etc.)

The group stage in MongoDB's aggregation framework is a core operation for data processing. By specifying the _id field, it enables data grouping, and when combined with accumulators like sum and avg, it can accomplish complex statistical calculations. The sum operator supports both numerical accumulation and document counting, while avg computes the average value of grouped data. The max and min operators retrieve extreme values. Multi-field grouping can be achieved using object notation. Array operations like push and addToSet are used to collect field values. Performance optimization includes leveraging indexes and memory control. Practical applications span e-commerce user behavior analysis, IoT device monitoring, and more. By flexibly combining these operators, diverse data analysis requirements can be efficiently fulfilled.

Expressions and operators (arithmetic, comparison, date, string, etc.)

MongoDB provides a rich set of expressions and operators for data processing, including arithmetic, comparison, date, string, logical, array, conditional, type conversion, variables and expressions, aggregation accumulators, and geospatial operators. Arithmetic operators support basic operations like addition, subtraction, multiplication, and division. Comparison operators are used for value comparison and return Boolean results. Date operators handle date fields and calculations. String operators process text data, such as concatenation, truncation, and case conversion. Logical operators combine multiple conditions to build complex expressions. Array operators handle array fields, such as querying elements and manipulating array content. Conditional expressions return different values based on conditions. Type conversion operators convert between different data types. Variables and expressions allow the use of variables and complex expressions in aggregation pipelines. Aggregation accumulators are used in grouping operations to calculate summary values. Geospatial operators specialize in handling geospatial data, such as querying nearby points.

Common aggregation stages ($match, $project, $group, $sort, etc.)

The MongoDB aggregation pipeline is a powerful data processing tool that processes collection documents through a series of connected stages for complex transformations and analysis. Common stages include `$match` for filtering documents, `$project` for reshaping document structures, `$group` for grouping and calculating aggregated values, `$sort` for ordering documents, `$limit` and `$skip` for pagination, `$unwind` for expanding array fields, `$lookup` for left joins, `$addFields` and `$set` for adding or modifying fields, `$count` for document statistics, `$facet` for executing multiple sub-pipelines, `$bucket` and `$bucketAuto` for grouping into range intervals, `$graphLookup` for handling tree or graph data, and `$merge` and `$out` for writing results to collections. Each stage has distinct functions, and their flexible combination can meet diverse data processing needs.

Overview of Aggregation Pipeline

MongoDB aggregation pipeline is a powerful data processing tool that achieves complex document transformation and analysis by chaining multiple operation stages. The basic structure includes stages like `match` for filtering documents, `group` for grouping and calculations, `project` for reshaping document structure, and `sort` for ordering. Advanced operations involve array handling, conditional expressions, and date processing. Performance optimization recommendations include leveraging indexes, managing memory, and considering sharded clusters. Practical application examples demonstrate e-commerce data analysis and user behavior analysis. Pipeline expressions provide arithmetic, string, and accumulator operations. The order of stages significantly impacts both results and performance. Special stages like `facet` execute multiple sub-pipelines, `lookup` enables join-like operations, and `graphLookup` handles hierarchical data. The aggregation pipeline is comprehensive and suitable for various data analysis scenarios.

Index selection and sorting optimization

When selecting MongoDB indexes, it is essential to understand query patterns and data distribution. B-tree indexes are suitable for equality and range queries, while hash indexes are optimized for equality queries. Compound indexes should follow the ESR principle: place exact-match fields first, sort fields in the middle, and range fields last. High-selectivity fields should be prioritized for indexing. Sorting operations may cause memory issues, but proper indexing can prevent out-of-memory errors. Compound index design must balance query coverage and write performance. Partial indexes reduce storage space and maintenance overhead. Regularly analyze index usage and remove unused indexes. Special indexes, such as geospatial, text, and wildcard indexes, each have their own use cases. TTL indexes are ideal for temporary data. Query selectivity affects index efficiency. Covered queries are entirely satisfied by indexes. In sharded environments, shard key selection is critical. Each additional index increases write overhead. Index size impacts memory usage, and the working set should fit in memory for optimal performance.

Covered Query

A covered query refers to a query operation that is entirely satisfied by an index without needing to access the actual document data, resulting in extremely high efficiency. In MongoDB, the database only needs to scan the index without loading the documents themselves. A covered query occurs when the index includes all the fields required by the query. The `explain` method can be used to confirm whether a query is covered by an index. Covered queries offer advantages such as performance improvement, memory efficiency, reduced CPU consumption, and faster query speeds. To achieve a covered query, certain conditions must be met, including projection restrictions (e.g., excluding the `_id` field). The design of compound indexes directly affects the possibility of covered queries, but there are limitations, such as with geospatial indexes and text indexes. Practical applications of covered queries include report generation and fast counting. Monitoring tools can identify optimization opportunities, and index selection and aggregation pipelines can also leverage the principles of covered queries to optimize performance.

Index optimization strategies and common issues

MongoDB index optimization is a key method to improve query performance, supporting various index types including single-field indexes, compound indexes, and multikey indexes. Single-field indexes are suitable for frequently queried individual fields, while compound indexes follow the leftmost prefix principle and are ideal for multi-condition queries. Covered queries retrieve data directly from the index, offering the highest efficiency. Fields with high index selectivity are more suitable for indexing. Common issues include excessive index usage leading to degraded write performance and unused indexes, which can be analyzed using the `explain` method. Index size requires monitoring. Special scenarios, such as array fields, require careful use of multikey indexes. TTL indexes automatically delete expired documents, and text indexes support full-text search but increase index size. Regularly monitor index usage and remove unused indexes promptly. In sharded clusters, shard key selection is particularly critical—poor shard key choices can lead to uneven data distribution. Queries should include the shard key whenever possible to avoid broadcast operations that impact performance.

Execution plan (explain) and query performance analysis

MongoDB's `explain` method is a crucial tool for diagnosing query performance. By appending `explain`, you can obtain the query execution plan, which includes information such as index usage, the number of scanned documents, and execution time. The execution plan has three modes: `queryPlanner` (only shows the plan selected by the optimizer), `executionStats` (includes actual execution statistics), and `allPlansExecution` (displays all candidate plans). Key metrics include query selectivity, memory usage, and the order of stage execution. Common issues include full collection scans, inefficient index usage, and in-memory sorting. Optimization methods involve creating appropriate indexes, refining query conditions, and indexing fields used for sorting. Advanced techniques cover index intersection, covered queries, and query shape analysis. In sharded clusters, execution plans are more complex and require attention to shard load balancing. Analyzing the execution plan of aggregation pipelines involves observing pipeline optimization flags and memory usage. Optimization best practices recommend following the ESR rule, refactoring queries, and using monitoring tools.

Creation, viewing, and deletion of indexes

The `db.collection.createIndex` method in MongoDB supports creating single-field, compound indexes, and various index types such as unique indexes, text indexes, geospatial indexes, hash indexes, and TTL indexes. To view indexes, use the `getIndexes` method to retrieve index information, including name, key, and type. Use `totalIndexSize` to check index space usage and `indexStats` to obtain usage statistics. To delete indexes, use `dropIndex` for a single index or `dropIndexes` to remove all indexes. Rebuild indexes with `reIndex` (in a sharded cluster, connect to `mongos` for index operations). Best practices for index management include monitoring index usage, analyzing queries with `explain`, checking for unused indexes, creating partial indexes, and using hidden indexes. Performance optimization involves leveraging covered queries, index intersection, index sort direction, and index prefix utilization. Proper use of indexes can significantly improve query efficiency.