Post List

2015년 8월 6일 목요일

MongoDB Study #11 Index의 종류 및 실습 Part.1 B*Tree Index

MongoDB는 RDBMS에서 지원하는 대부분의 Index 들을 지원해주며,
추가로 다른 Index 들도 제공합니다.
No SQL의 탄생배경에 Big Data라는 말을 안 할수 없죠.
Big Data의 탄생배경에는 Cloud, Mobile, SNS 등과 같은 말이 빠질 수 없구요.
RDBMS에서는 잘 사용하지 않았지만, SNS에서는 빈번하게 사용하는 Data가 여러가지 있지만,
그중에 위치정보 Data가 있습니다. 통상적으로 위도, 경도 의 2차원 점으로 표시됩니다.
이런 2차원 점 형태의Data를 Index에 넣어서 해당 점을 이용한 도형 ( 점 , 선 , 다각형 ) 간의 거리 정보를 활용한 Index도 MongoDB에는 존재합니다.

1. Index의 종류

 INDEX TYPEDESCRIPTION 
Non-Unique Index가장 대표적인 Balance Tree Index
아래 다른 Index 들 중 2D Index를 제외하고는 모두 B*Tree Index의 구조를 가집니다.
Single-key Index, Compond-key Index가 가능합니다.
db.collection.createIndex( { field : 1 } )
Unique IndexIndex의 Field가 유일한 속성 값을 가진 Index
db.collection.createIndex( { field : 1 } , { unique : true } )
Sparse Index대부분의 Field 값이 Null 이고 드물게 Data를 가지고 있는 경우 효율적인 Index
db.collection.createIndex( { "field : 1 } , { sparse : true } )
Background Index처음부터 생성하지 않고, System 자원이 충분할 때 생성 작업을 수행하는 Index
db.collection.createIndex( { field : 1 } , { background : true } )
Covered IndexIndex만으로 Data를 검색하여 Document 추출이 가능한 Index
특별히 따로 생성하는 방법이 있는것이 아니라 Index 내의 field 로만 find 할 경우 수행됩니다.
RDBMS의 Fast Index Scan과 같은 뜻의 용어라고 생각하시면 됩니다.
DropDups Index중복된 값이 있을 경우 최초 입력된 Document만 남기고 나머지는 제거하는 Index
db.collection.createIndex( { field : 1 } , { unique : true , dropDups : true } )
라고는 하지만 3.0 이후부터 지원되지 않습니다.
GeoSpartial Index 좌표로 구성된 2D Index
http://docs.mongodb.org/manual/applications/geospatial-indexes/
GeoMetry Index직선, 곡선, 다각형 등의 기하학 구조에서 사용되는 2D Index
http://docs.mongodb.org/manual/applications/geospatial-indexes/
Multikey IndexArray Field에 대한 Index
일반 index와 정의하는 방법은 같지만 해당 field가 Array 인 경우 Multikey Index로 생성됩니다.
http://docs.mongodb.org/manual/core/index-multikey/
Text IndexDocument 내의 String으로 된 내용에서의 검색을 편하게 해주는 Index
db.collection.createIndex( { comments : "text" } )
http://docs.mongodb.org/manual/core/index-text/
Hashed IndexField 값을 Hash로 처리한 Index
그래서 Equal 연산은 가능하지만, Range 연산은 지원하지 않고, Multikey 에 대해서는 적용이 안됩니다.
db.collection.createIndex( { field : "hashed" } )
http://docs.mongodb.org/manual/core/index-hashed/

하나씩 실습을 통해서 살펴보도록 하겠습니다.

2. Single-key Index & Compound-key Index

Single-key Index는 단일 field로 구성된 Index이며,
Compound-key Index는 여러 개의 field로 된 Index 입니다.
RDBMS와 동일한 개념을 가지고 있으므로 자세한 내용은 넘어가겠습니다.
(그렇다고 여기서 대용량 Database Tuning 에서 다룰 내용까지 할 순 없으니깐요.)

db.system.indexes.find()  // 현재 사용중인 test db에 생성된 모든 Index 확인
{ "v" : 1, "key" : { "_id" : 1 }, "name" : "_id_", "ns" : "test.emp" }
{ "v" : 1, "key" : { "comm" : 1 }, "name" : "comm_1", "ns" : "test.emp" }

db.emp.createIndex( { empno : 1 } )
db.emp.createIndex( { empno : 1 , deptno : -1 } ) // Compound-key Index
db.emp.createIndex( { deptno : 1 } )

db.emp.find( { deptno : 10 } )
{ "_id" : ObjectId("55c13bcd2a1c4137302b17fd"), "empno" : 7782, "ename" : "CLARK", "job" : "MANAGER", "hiredate" : "09-06-1981", "sal" : 2450, "deptno" : 10 }
{ "_id" : ObjectId("55c13bcd2a1c4137302b17ff"), "empno" : 7839, "ename" : "PRESIDENT", "job" : "CEO", "hiredate" : "17-11-1981", "sal" : 5000, "deptno" : 10 }
{ "_id" : ObjectId("55c13bcd2a1c4137302b1804"), "empno" : 7934, "ename" : "CLERK", "job" : "CLERK", "hiredate" : "23-01-1982", "sal" : 1300, "deptno" : 10 }

db.emp.find( { deptno : 10 } ).explain() // Execution Plan 확인
{
        "queryPlanner" : {
                "plannerVersion" : 1,
                "namespace" : "test.emp",
                "indexFilterSet" : false,
                "parsedQuery" : {
                        "deptno" : {
                                "$eq" : 10
                        }
                },
                "winningPlan" : {
                        "stage" : "FETCH",
                        "inputStage" : {
                                "stage" : "IXSCAN",
                                "keyPattern" : {
                                        "deptno" : 1
                                },
                                "indexName" : "deptno_1",
                                "isMultiKey" : false,
                                "direction" : "forward",
                                "indexBounds" : {
                                        "deptno" : [
                                                "[10.0, 10.0]"
                                        ]
                                }
                        }
                },
                "rejectedPlans" : [ ]
        },
        "serverInfo" : { ... },
        "ok" : 1
}

db.emp.find( { deptno : 10 } ).sort( { empno: -1 } )
{ "_id" : ObjectId("55c13bcd2a1c4137302b1804"), "empno" : 7934, "ename" : "CLERK", "job" : "CLERK", "hiredate" : "23-01-1982", "sal" : 1300, "deptno" : 10 }
{ "_id" : ObjectId("55c13bcd2a1c4137302b17ff"), "empno" : 7839, "ename" : "PRESIDENT", "job" : "CEO", "hiredate" : "17-11-1981", "sal" : 5000, "deptno" : 10 }
{ "_id" : ObjectId("55c13bcd2a1c4137302b17fd"), "empno" : 7782, "ename" : "CLARK", "job" : "MANAGER", "hiredate" : "09-06-1981", "sal" : 2450, "deptno" : 10 }

db.emp.find( { deptno : 10 } ).sort( { empno: -1 } ).explain()
{
        "queryPlanner" : {
                "plannerVersion" : 1,
                "namespace" : "test.emp",
                "indexFilterSet" : false,
                "parsedQuery" : {
                        "deptno" : {
                                "$eq" : 10
                        }
                },
                "winningPlan" : {
                        "stage" : "SORT",
                        "sortPattern" : {
                                "empno" : -1
                        },
                        "inputStage" : {
                                "stage" : "KEEP_MUTATIONS",
                                "inputStage" : {
                                        "stage" : "FETCH",
                                        "inputStage" : {
                                                "stage" : "IXSCAN",
                                                "keyPattern" : {
                                                        "deptno" : 1
                                                },
                                                "indexName" : "deptno_1",
                                                "isMultiKey" : false,
                                                "direction" : "forward",
                                                "indexBounds" : {
                                                        "deptno" : [
                                                                "[10.0, 10.0]"
                                                        ]
                                                }
                                        }
                                }
                        }
                },
                "rejectedPlans" : [ ... ]
        },
        "serverInfo" : { ... },
        "ok" : 1
}

db.emp.find( { deptno : 10 , empno : 7839 } ).explain()
{
        "queryPlanner" : {
                "plannerVersion" : 1,
                "namespace" : "test.emp",
                "indexFilterSet" : false,
                "parsedQuery" : {
                        "$and" : [
                                {
                                        "deptno" : {
                                                "$eq" : 10
                                        }
                                },
                                {
                                        "empno" : {
                                                "$eq" : 7839
                                        }
                                }
                        ]
                },
                "winningPlan" : {
                        "stage" : "KEEP_MUTATIONS",
                        "inputStage" : {
                                "stage" : "FETCH",
                                "filter" : {
                                        "deptno" : {
                                                "$eq" : 10
                                        }
                                },
                                "inputStage" : {
                                        "stage" : "IXSCAN",
                                        "keyPattern" : {
                                                "empno" : 1
                                        },
                                        "indexName" : "empno_1",
                                        "isMultiKey" : false,
                                        "direction" : "forward",
                                        "indexBounds" : {
                                                "empno" : [
                                                        "[7839.0, 7839.0]"
                                                ]
                                        }
                                }
                        }
                },
                "rejectedPlans" : [ ... ]
        },
        "serverInfo" : { ... },
        "ok" : 1
}

3. Non-Unique Index & Unique Index

db.emp.dropIndex( { empno : 1 })
{ "nIndexesWas" : 5, "ok" : 1 }

db.emp.createIndex( { empno : 1 } , { unique : true } )
{
        "createdCollectionAutomatically" : false,
        "numIndexesBefore" : 4,
        "numIndexesAfter" : 5,
        "ok" : 1
}

db.emp.createIndex( { ename : 1 } )
{
        "createdCollectionAutomatically" : false,
        "numIndexesBefore" : 5,
        "numIndexesAfter" : 6,
        "ok" : 1
}

db.emp.getIndexes()
[
        ...
        {
                "v" : 1,
                "unique" : true,
                "key" : {
                        "empno" : 1
                },
                "name" : "empno_1",
                "ns" : "test.emp"
        },
        {
                "v" : 1,
                "key" : {
                        "ename" : 1
                },
                "name" : "ename_1",
                "ns" : "test.emp"
        }
]

4. Unique Index & DropDups Index

3.0 이상버전에서는 DropDups Index가 지원되지 않습니다.
그냥 아래 실습은 하지 않고 넘어가셔도 됩니다.

db.emp.dropIndex( { empno : 1 } )
{ "nIndexesWas" : 6, "ok" : 1 }

db.emp.createIndex ( { empno : 1 } , { unique : true } )
{
        "createdCollectionAutomatically" : false,
        "numIndexesBefore" : 5,
        "numIndexesAfter" : 6,
        "ok" : 1
}

db.emp.find( { empno : 7369 } )
{ "_id" : ObjectId("55c13bcd2a1c4137302b17f7"), "empno" : 7369, "ename" : "SMITH", "job" : "CLERK", "hiredate" : "17-12-1980", "sal" : 800, "deptno" : 20 }

db.emp.insert( { empno : 7369 , ename : "Luna" } )
WriteResult({
        "nInserted" : 0,
        "writeError" : {
                "code" : 11000,
                "errmsg" : "E11000 duplicate key error index: test.emp.$empno_1 dup key: { : 7369.0 }"
        }
})

db.emp.dropIndex( { empno : 1 } )
{ "nIndexesWas" : 6, "ok" : 1 }

db.emp.insert( { empno : 7369 , ename : "Luna" } )
WriteResult({ "nInserted" : 1 })

db.emp.insert( { empno : 7369 , ename : "LunaStar" } )
WriteResult({ "nInserted" : 1 })

db.emp.find( { empno : 7369 } )
{ "_id" : ObjectId("55c13bcd2a1c4137302b17f7"), "empno" : 7369, "ename" : "SMITH", "job" : "CLERK", "hiredate" : "17-12-1980", "sal" : 800, "deptno" : 20 }
{ "_id" : ObjectId("55c2a325abfcc1e37aa34b42"), "empno" : 7369, "ename" : "Luna" }
{ "_id" : ObjectId("55c2a391abfcc1e37aa34b43"), "empno" : 7369, "ename" : "LunaStar" }

db.emp.createIndex( { empno : 1 } , { unique : true , dropDups : true } ) // 원래 이렇게 하면 성공하고, .find() 했을때 잴 처음꺼 하나만 남아야 하는데 3.0부터 지원하지 않습니다.
{
        "createdCollectionAutomatically" : false,
        "numIndexesBefore" : 5,
        "errmsg" : "exception: E11000 duplicate key error index: test.emp.$empno_1 dup key: { : 7369.0 }",
        "code" : 11000,
        "ok" : 0
}

5. Sparse Index

Sparse Index는 해당 Field가 Null인 경우 Index에 포함시키지 않습니다.
검색 대상 Field 값이 전체 Collection에서 차지하는 밀도가 낮은 경우 효과적입니다.

db.emp.dropIndex( { comm : 1 } )
{ "nIndexesWas" : 5, "ok" : 1 }

db.emp.createIndex( { comm : 1 } , { sparse : true } )
{
        "createdCollectionAutomatically" : false,
        "numIndexesBefore" : 4,
        "numIndexesAfter" : 5,
        "ok" : 1
}

db.emp.find().sort( { comm : -1 } ) // 예전버전에는 이렇게만 하더라도 comm index를 사용하여 comm이 null인건 표시가 안되었다고 하던데, 3.0부터는 다 표시가 되네요.
​{ "_id" : ObjectId("55c13bcd2a1c4137302b17fb"), "empno" : 7654, "ename" : "MARTIN", "job" : "SALESMAN", "hiredate" : "28-09-1981", "sal" : 1250, "comm" : 1400, "deptno" : 30 }
{ "_id" : ObjectId("55c13bcd2a1c4137302b17f9"), "empno" : 7521, "ename" : "WARD", "job" : "SALESMAN", "hiredate" : "22-02-1981", "sal" : 1250, "comm" : 500, "deptno" : 30 }
{ "_id" : ObjectId("55c13bcd2a1c4137302b17f8"), "empno" : 7499, "ename" : "ALLEN", "job" : "SALESMAN", "hiredate" : "20-02-1981", "sal" : 1600, "comm" : 300, "deptno" : 30 }
{ "_id" : ObjectId("55c13bcd2a1c4137302b17f7"), "empno" : 7369, "ename" : "SMITH", "job" : "CLERK", "hiredate" : "17-12-1980", "sal" : 800, "deptno" : 20 }
{ "_id" : ObjectId("55c13bcd2a1c4137302b17fa"), "empno" : 7566, "ename" : "JONES", "job" : "MANAGER", "hiredate" : "02-04-1981", "sal" : 2975, "deptno" : 20 }
{ "_id" : ObjectId("55c13bcd2a1c4137302b17fc"), "empno" : 7698, "ename" : "BLAKE", "job" : "MANAGER", "hiredate" : "01-05-1981", "sal" : 2850, "deptno" : 30 }
{ "_id" : ObjectId("55c13bcd2a1c4137302b17fd"), "empno" : 7782, "ename" : "CLARK", "job" : "MANAGER", "hiredate" : "09-06-1981", "sal" : 2450, "deptno" : 10 }
{ "_id" : ObjectId("55c13bcd2a1c4137302b17fe"), "empno" : 7788, "ename" : "SCOTT", "job" : "ANALYST", "hiredate" : "13-06-1987", "sal" : 3000, "deptno" : 20 }
{ "_id" : ObjectId("55c13bcd2a1c4137302b17ff"), "empno" : 7839, "ename" : "PRESIDENT", "job" : "CEO", "hiredate" : "17-11-1981", "sal" : 5000, "deptno" : 10 }
{ "_id" : ObjectId("55c13bcd2a1c4137302b1800"), "empno" : 7844, "ename" : "TURNER", "job" : "SALESMAN", "hiredate" : "08-09-1981", "sal" : 1500, "deptno" : 30 }
{ "_id" : ObjectId("55c13bcd2a1c4137302b1801"), "empno" : 7876, "ename" : "ADAMS", "job" : "CLERK", "hiredate" : "13-06-1987", "sal" : 1100, "deptno" : 20 }
{ "_id" : ObjectId("55c13bcd2a1c4137302b1802"), "empno" : 7900, "ename" : "JAMES", "job" : "CLERK", "hiredate" : "03-12-1981", "sal" : 950, "deptno" : 30 }
{ "_id" : ObjectId("55c13bcd2a1c4137302b1803"), "empno" : 7902, "ename" : "FORD", "job" : "ANALYST", "hiredate" : "03-12-1981", "sal" : 3000, "deptno" : 20 }
{ "_id" : ObjectId("55c13bcd2a1c4137302b1804"), "empno" : 7934, "ename" : "CLERK", "job" : "CLERK", "hiredate" : "23-01-1982", "sal" : 1300, "deptno" : 10 }
{ "_id" : ObjectId("55c2a325abfcc1e37aa34b42"), "empno" : 7369, "ename" : "Luna" }
{ "_id" : ObjectId("55c2a391abfcc1e37aa34b43"), "empno" : 7369, "ename" : "LunaStar" }

db.emp.find().sort( { comm : -1 } ).explain()
{
        "queryPlanner" : {
                "plannerVersion" : 1,
                "namespace" : "test.emp",
                "indexFilterSet" : false,
                "parsedQuery" : {
                        "$and" : [ ]
                },
                "winningPlan" : {
                        "stage" : "SORT",
                        "sortPattern" : {
                                "comm" : -1
                        },
                        "inputStage" : {
                                "stage" : "COLLSCAN",
                                "filter" : {
                                        "$and" : [ ]
                                },
                                "direction" : "forward"
                        }
                },
                "rejectedPlans" : [ ]
        },
        "serverInfo" : { ... },
        "ok" : 1
}

db.emp.find().sort( { comm : -1 } ).hint( { comm : 1 } ) // hint를 사용해서 comm에 대한 index를 사용하도록 지시
{ "_id" : ObjectId("55c13bcd2a1c4137302b17fb"), "empno" : 7654, "ename" : "MARTIN", "job" : "SALESMAN", "hiredate" : "28-09-1981", "sal" : 1250, "comm" : 1400, "deptno" : 30 }
{ "_id" : ObjectId("55c13bcd2a1c4137302b17f9"), "empno" : 7521, "ename" : "WARD", "job" : "SALESMAN", "hiredate" : "22-02-1981", "sal" : 1250, "comm" : 500, "deptno" : 30 }
{ "_id" : ObjectId("55c13bcd2a1c4137302b17f8"), "empno" : 7499, "ename" : "ALLEN", "job" : "SALESMAN", "hiredate" : "20-02-1981", "sal" : 1600, "comm" : 300, "deptno" : 30 }

db.emp.find().sort( { comm : -1 } ).hint( { comm : 1 } ).explain()
{
        "queryPlanner" : {
                "plannerVersion" : 1,
                "namespace" : "test.emp",
                "indexFilterSet" : false,
                "parsedQuery" : {
                        "$and" : [ ]
                },
                "winningPlan" : {
                        "stage" : "FETCH",
                        "inputStage" : {
                                "stage" : "IXSCAN",
                                "keyPattern" : {
                                        "comm" : 1
                                },
                                "indexName" : "comm_1",
                                "isMultiKey" : false,
                                "direction" : "backward",
                                "indexBounds" : {
                                        "comm" : [
                                                "[MaxKey, MinKey]"
                                        ]
                                }
                        }
                },
                "rejectedPlans" : [ ]
        },
        "serverInfo" : { ... },
        "ok" : 1
}

6. Background Index

Big Data에 대해서 Index 생성시 몇백 GB 및 수 TB에 대한 Data에 대해서 Index를 생성 할 수도 있습니다.
Index 생성에는 많은 System 자원이 요구되는데, 이게 성능 저하현상을 유발 시킬 수도 있습니다.
그래서 만들어 진것이 Background Index 입니다.
이것을 사용하면 일정 공간 이상의 자원이 확보되었을 때 Index 작업을 수행합니다.

db.emp.createIndex( { hiredate : 1 } , { background : true } )
{
        "createdCollectionAutomatically" : false,
        "numIndexesBefore" : 5,
        "numIndexesAfter" : 6,
        "ok" : 1
}

7. Covered Index

Index 내의 Field 만으로 검색이 가능한 경우 빠른 검색을 제공합니다.

db.emp.createIndex( { deptno : 1 , ename : 1 } )
{
        "createdCollectionAutomatically" : false,
        "numIndexesBefore" : 6,
        "numIndexesAfter" : 7,
        "ok" : 1
}

db.emp.find( { deptno : 10 , ename : "CLARK" } , { _id : 0 , ename : 1 , deptno : 1 } )
{ "deptno" : 10, "ename" : "CLARK" }

db.emp.find( { deptno : 10 , ename : "CLARK" } , { _id : 0 , ename : 1 , deptno : 1 } ).explain()
{
        "queryPlanner" : {
                "plannerVersion" : 1,
                "namespace" : "test.emp",
                "indexFilterSet" : false,
                "parsedQuery" : {
                        "$and" : [
                                {
                                        "deptno" : {
                                                "$eq" : 10
                                        }
                                },
                                {
                                        "ename" : {
                                                "$eq" : "CLARK"
                                        }
                                }
                        ]
                },
                "winningPlan" : {
                        "stage" : "PROJECTION",
                        "transformBy" : {
                                "_id" : 0,
                                "ename" : 1,
                                "deptno" : 1
                        },
                        "inputStage" : {
                                "stage" : "IXSCAN",
                                "keyPattern" : {
                                        "deptno" : 1,
                                        "ename" : 1
                                },
                                "indexName" : "deptno_1_ename_1", // 예전 버전에는 indexOnly : true 라는 return 값도 있었지만, 3.0 이상부터는 사라졌습니다.
                                "isMultiKey" : false,
                                "direction" : "forward",
                                "indexBounds" : {
                                        "deptno" : [
                                                "[10.0, 10.0]"
                                        ],
                                        "ename" : [
                                                "[\"CLARK\", \"CLARK\"]"
                                        ]
                                }
                        }
                },
                "rejectedPlans" : [ ... ]
        },
        "serverInfo" : { ... },
        "ok" : 1
}


작성자: 시간:
라벨:

댓글 없음:

댓글 쓰기

피드 구독하기: 댓글 (Atom)