ESR Rule - Compound Index 설계의 핵심 원칙

// query
db.users.find({
    status: "active",                    // Equality
    age: { $gte: 25 }                    // Range
}).sort({ createdAt: -1 });              // Sort

// 잘못된 설계 (field 순서 부적절)
db.users.createIndex({ age: 1, status: 1, createdAt: -1 });
// age 범위 검색 후 memory에서 정렬 필요

// 올바른 설계 (완전한 ESR rule)
db.users.createIndex({ status: 1, createdAt: -1, age: 1 });
// status로 filtering → createdAt으로 정렬 (index 활용) → age 범위 (index 활용)

// query
db.orders.find({
    customerId: "123",                   // Equality
    status: "completed",                 // Equality
    amount: { $gte: 100 }                // Range
}).sort({ orderDate: -1 });              // Sort

// 잘못된 설계
db.orders.createIndex({ amount: 1, orderDate: 1, customerId: 1, status: 1 });

// 올바른 설계 (완전한 ESR rule)
db.orders.createIndex({ customerId: 1, status: 1, orderDate: -1, amount: 1 });
// customerId와 status로 filtering → orderDate로 정렬 → amount 범위

// query
db.products.find({
    category: "electronics",             // Equality
    price: { $lt: 1000 }                 // Range
});

// 설계 (E-R rule 부분 적용)
db.products.createIndex({ category: 1, price: 1 });
// category로 filtering → price 범위 (E-R)

// query
db.articles.find({
    author: "John"                       // Equality
}).sort({ views: -1 });                  // Sort

// 설계 (E-S rule 부분 적용)
db.articles.createIndex({ author: 1, views: -1 });
// author로 filtering → views 정렬 (E-S)

// query
db.products.find({
    status: "active",                    // Equality
    category: "electronics",             // Equality
    price: { $gte: 100, $lte: 1000 }    // Range
}).sort({ rating: -1 }).limit(20);      // Sort

// 설계 (완전한 ESR rule)
db.products.createIndex({
    status: 1,        // E1
    category: 1,      // E2 (selectivity 고려)
    rating: -1,       // S
    price: 1          // R
});

// 실행 계획 : status filtering → category filtering → rating 정렬 (index 활용) → price 범위

// query
db.userActivity.find({
    userId: "user123",                   // Equality
    eventType: "purchase",               // Equality
    timestamp: {                         // Range
        $gte: new Date("2025-01-01"),
        $lte: new Date("2025-12-31")
    }
}).sort({ amount: -1 }).limit(100);     // Sort

// 설계 (완전한 ESR rule)
db.userActivity.createIndex({
    userId: 1,        // E
    eventType: 1,     // E
    amount: -1,       // S
    timestamp: 1      // R
});

// query
db.logs.find({
    level: "error",                      // Equality
    service: "payment",                  // Equality
    timestamp: { $gte: Date.now() - 86400000 }  // Range (최근 24시간)
}).sort({ timestamp: -1 }).limit(50);   // Sort

// 설계 (부분 적용: timestamp가 Sort에 포함)
db.logs.createIndex({
    level: 1,         // E
    service: 1,       // E
    timestamp: -1,    // S
});

// 참고 : timestamp가 이미 Sort에 포함되어 있으므로 Range로 따로 지정할 필요 없음

// query
db.users.find({
    status: "active",
    age: { $gte: 20 }
}).sort({ createdAt: -1 });

// ESR rule 미적용 : { age: 1, status: 1, createdAt: -1 }
// 1. age 범위 검색 : 50,000개 document 검사
// 2. memory sort : 50,000개를 createdAt으로 정렬
// 3. memory 제한 : 104MB 이상 정렬 시 실패 가능
{
    "nReturned": 100,
    "totalDocsExamined": 50000,
    "executionTimeMillis": 2500
}

// ESR rule 적용 : { status: 1, createdAt: -1, age: 1 }
// 1. status filtering : 5,000개로 축소
// 2. createdAt 정렬 : index에 이미 정렬됨 (추가 작업 없음)
// 3. age 범위 : 100개만 반환
{
    "nReturned": 100,
    "totalDocsExamined": 100,
    "executionTimeMillis": 25
}

// ESR rule 미적용 시 MongoDB의 상황
db.users.createIndex({ age: 1 });
db.users.createIndex({ status: 1 });
db.users.createIndex({ createdAt: -1 });

db.users.find({
    status: "active",
    age: { $gte: 20 }
}).sort({ createdAt: -1 });

// MongoDB가 여러 index를 조합하려 시도 → 복잡하고 비효율적
// 또는 하나의 index만 선택 후 나머지는 memory에서 처리

// query pattern 1 : status로 많이 filtering
db.users.find({ status: "active" }).sort({ createdAt: -1 });

// query pattern 2 : age 범위로 많이 검색
db.users.find({ age: { $gte: 20 } }).sort({ createdAt: -1 });

// 가장 자주 사용되는 query를 기준으로 index 설계
// 1번 pattern이 더 많으면 : { status: 1, createdAt: -1, age: 1 }
// 2번 pattern이 더 많으면 : age는 range이므로 ESR rule 위반

// 높은 selectivity : status 값이 적음 (10개 정도)
// 낮은 selectivity : age 범위가 너무 넓음 (전체 범위)

// 이 경우 더 selective한 field를 먼저 배치
db.users.createIndex({ status: 1, createdAt: -1, age: 1 });
// status로 빠르게 축소 → createdAt 정렬 → age 범위

// ESR rule을 따르는 여러 compound index 생성
db.users.createIndex({ status: 1, createdAt: -1, age: 1 });
db.users.createIndex({ department: 1, createdAt: -1, salary: 1 });
db.users.createIndex({ region: 1, createdAt: -1, experience: 1 });

// document 삽입 시 모든 index를 update해야 함
db.users.insertOne({ ... }); // 3개 index 모두 update

// query 1
db.products.find({ category: "electronics" })
    .sort({ price: 1 });  // ascending

// query 2
db.products.find({ category: "electronics" })
    .sort({ price: -1 }); // descending

// 두 query를 모두 index에서 처리하려면
db.products.createIndex({ category: 1, price: 1 });
// query 1은 최적 (ascending 일치)
// query 2는 backward scan 필요 (약간의 성능 저하)