// Business query statement
db.t_orders.find({
    userId: 10086,                               // E: Equality
    status: "paid",                              // E: Equality
    createTime: { $gte: ISODate("2024-02-01") }  // R: Range
}).sort({ amount: -1 })                          // S: Sorting

// Error: With Range before Sort, the index diverges on createTime and cannot support amount sorting, triggering in-memory sorting.
db.t_orders.createIndex(
    { userId: 1, status: 1, createTime: 1, amount: -1 },
    { background: true }
)

// Correct: ESR order, equality filtering → index sorting → range truncation
db.t_orders.createIndex(
    { userId: 1, status: 1, amount: -1, createTime: 1 },
    { background: true }
)

 // A compound index already exists.
db.t_orders.createIndex(
    { userId: 1, status: 1, createTime: 1 },
    { background: true }
)

// Correct: Contains the leftmost field userId, and the index is fully hit.
db.t_orders.find({ userId: 10086, status: "paid" })

// Partial hit: Contains the leftmost field userId but skips status, using only userId for positioning.
db.t_orders.find({ userId: 10086, createTime: { $gte: ISODate("2024-01-01") } })

// Error: Missing the leftmost field userId, triggering a full table scan.
db.t_orders.find({ status: "pending" })

// Solution: Create a separate index for the independent query field.
db.t_orders.createIndex(
    { status: 1 },
    { background: true }
)

// Error: The background parameter is not specified, which may block all operations.
db.t_orders.createIndex({ customerId: 1 })

// Single creation: Enable the background mode to avoid blocking business operations.
db.t_orders.createIndex(
    { customerId: 1, createTime: -1 },
    { background: true }
)

// Step 1: View all current indexes of the collection.
db.t_orders.getIndexes()

// Step 2: Investigate the actual usage of each index using $indexStats.
db.t_orders.aggregate([{ $indexStats: {} }]).forEach(function(idx) {
    print(
        "Index Name:", idx.name,
        "| Invocation Count:", idx.accesses.ops,
        "| Last Used:", idx.accesses.since
    )
})

// Step 3: View the storage ratio between indexes and data.
var stats = db.t_orders.stats()
print("Data Size:", (stats.size / 1024 / 1024).toFixed(2), "MB")
print("Index Size:", (stats.totalIndexSize / 1024 / 1024).toFixed(2), "MB")
print("Index/Data Ratio:", (stats.totalIndexSize / stats.size * 100).toFixed(1), "%")

// Step 4: Delete redundant indexes with an invocation count of 0.
// Before performing the operation, ensure that the index has no business dependencies.
db.t_orders.dropIndex("idx_legacy_report_1")
db.t_orders.dropIndex("idx_legacy_report_2")

// Note: Do not delete the default _id index.
// db.t_orders.dropIndex("_id")  // This operation will cause an error because the _id index cannot be deleted.

// Step 1: Execute a statistical aggregation to identify unused indexes.
db.t_orders.aggregate([{ $indexStats: {} }])
/* Output Example Analysis:
{
    "name": "idx_old_field",
    "accesses": {
        "ops": NumberLong(0),    // Key metric: The invocation count is 0.
        "since": ISODate("2024-01-01T00:00:00Z")
    }
}
*/

// Step 2: Logical Hiding (Supports fast rollback, MongoDB 4.4+)
db.t_orders.hideIndex("idx_old_field")

// Observation Period: Continuously monitor for 1-7 days. If business is impacted, you can immediately execute db.t_orders.unhideIndex("idx_old_field") to restore it.

// Step 3: Physical Deletion (Execute after confirming no damage).
db.t_orders.dropIndex("idx_old_field")

// Pattern 1: Fixed-duration expiration (e.g., based on the createTime field, expires after 30 days)
db.t_sessions.createIndex(
    { createTime: 1 },
    { expireAfterSeconds: 2592000, background: true }
)

// Pattern 2: Precise timestamp expiration (Recommended, where the business layer dynamically determines the lifetime)
// Set expireAfterSeconds to 0. The engine will perform cleanup when the expireAt timestamp is reached.
db.t_sessions.createIndex(
    { expireAt: 1 },
    { expireAfterSeconds: 0, background: true }
)

// When inserting at the business layer, directly specify the precise expiration time.
db.t_sessions.insertOne({
    sessionId: "sess_001",
    userId: "user_001",
    expireAt: new Date(Date.now() + 3600000)  // Dynamically specifies expiration after 1 hour
})

// Recommendation: Create a time series collection (sensors report every 5 minutes)
db.createCollection("t_sensor_data", {
    timeseries: {
        timeField: "timestamp",        // Required: Timestamp field
        metaField: "sensorId",         // Recommended: Data source identifier
        granularity: "minutes"         // Granularity matches the collection frequency (5 minutes → minutes)
    }
})

// Recommendation: Create a time series collection (for system monitoring that collects data every second)
db.createCollection("t_metrics", {
    timeseries: {
        timeField: "ts",
        metaField: "metadata",         // metadata can be an object, such as { host: "web01", region: "bj" }
        granularity: "seconds"         // Granularity matches the collection frequency (second-level → seconds)
    }
})

// Insert time series data
db.t_sensor_data.insertMany([
    { sensorId: "sensor_001", timestamp: new Date(), temperature: 23.5, humidity: 65 },
    { sensorId: "sensor_001", timestamp: new Date(), temperature: 23.6, humidity: 64 },
    { sensorId: "sensor_002", timestamp: new Date(), temperature: 18.2, humidity: 72 }
], { ordered: false })  // ordered: false improves batch write performance

// Error: Manually managing time series data on a regular collection
db.createCollection("t_sensor_data_old")
db.t_sensor_data_old.createIndex({ sensorId: 1, timestamp: -1 })
// Requires manual handling of data archiving, compression, and cleanup, resulting in high maintenance costs.

// Query data for a specific device from the last 24 hours (automatically using time series indexes)
db.t_sensor_data.find({
    sensorId: "sensor_001",
    timestamp: { $gte: new Date(Date.now() - 86400000) }
}).sort({ timestamp: -1 })

// Use $group aggregation instead of distinct() (time series collections do not support efficient distinct operations)
db.t_sensor_data.aggregate([
    { $match: { timestamp: { $gte: new Date(Date.now() - 86400000) } } },
    { $group: { _id: "$sensorId" } }
])

// Error: The sort field is not indexed, which may trigger in-memory sorting.
db.t_orders.find({ status: "paid" }).sort({ createTime: -1 })

// Correct: The sort field is included in the index.
db.t_orders.createIndex({ status: 1, createTime: -1 }, { background: true })
db.t_orders.find({ status: "paid" }).sort({ createTime: -1 })  // Index sorting, no memory limit

// Verify whether index sorting is used.
db.t_orders.find({ status: "paid" }).sort({ createTime: -1 }).explain("executionStats")
// Check whether a SORT stage exists. The absence of a SORT stage indicates that index sorting is used.

// Inefficient: $ne cannot be effectively utilized by indexes.
db.t_orders.find({ status: { $ne: "cancelled" } })

// Optimized: $in lists all valid statuses.
db.t_orders.find({ status: { $in: ["pending", "paid", "shipped", "completed"] } })

// Inefficient: Unprefixed regex, full table scan.
db.t_users.find({ name: /Zhang/ })

// Optimized: Prefix-anchored regex can utilize indexes.
db.t_users.find({ name: /^Zhang/ })

db.t_orders.find({ status: "paid" }).explain("executionStats")

// Key metric, close to 1: Index is efficient, scanning results in a hit.
totalDocsExamined / nReturned ≈ 1   // Ideal value

// Locate the sorting stage in the explain results.
executionStats.executionStages.stage
// Or nested within inputStage / inputStages.

// Does not comply with ESR: range field precedes sorting field.
db.t_orders.createIndex({ createTime: 1, status: 1 })
db.t_orders.find({ createTime: { $gte: ISODate("2024-01-01") }, status: "paid" }).sort({ amount: -1 })

// Complies with ESR: equality → sorting → range
db.t_orders.createIndex({ status: 1, amount: -1, createTime: 1 })


// Multiple single-field indexes: MongoDB attempts index intersection, resulting in unstable efficiency.
db.t_orders.createIndex({ status: 1 })
db.t_orders.createIndex({ userId: 1 })
db.t_orders.createIndex({ createTime: 1 })

// Compound index: a single index covers both querying and sorting, adhering to the ESR principle.
db.t_orders.createIndex({ status: 1, userId: 1, createTime: -1 })

// Single-field indexes offer greater flexibility when query patterns are not fixed and field combinations vary:
// Scenario: For dynamic filtering in an operations backend, where users may query based on any combination of fields, creating a compound index for each combination is impractical. Single-field indexes + index intersection is the appropriate choice.
db.t_orders.find({ status: "paid" })                    // Filters only by status
db.t_orders.find({ userId: "u_10001" })                  // Filters only by user
db.t_orders.find({ createTime: { $gte: ISODate("...") } }) // Filters only by time
db.t_orders.find({ status: "paid", userId: "u_10001" })  // Filters by status + user

sh.stopBalancer()

db.orders.createIndex({ userId: 1, createTime: -1 }, { background: true })

// View currently running index build tasks.
db.currentOp({ "command.createIndexes": { $exists: true } })