Background Image
조회 수 29880 추천 수 0 댓글 0
?

단축키

Prev이전 문서

Next다음 문서

크게 작게 위로 아래로 댓글로 가기 인쇄 첨부

CUBRID 2008 R4.0에서는 커버링 인덱스를 지원합니다. Wikipedia에서는 커버링 인덱스를 다음과 같이 설명하고 있습니다.

A covering index is a special case where the index itself contains the required data field(s) and can return the data.


간단하게 말해서, 하나의 질의 내에 특정 인덱스를 구성하는 컬럼만 사용하는 경우를 생각하면 됩니다. 다음과 같은 예제에서, SELECT 질의의 WHERE 조건에 사용된 컬럼 i, SELECT 리스트로 주어진 컬럼 j는 모두 인덱스 idx를 구성하는 컬럼입니다. 이와 같은 경우에 CUBRID SELECT 질의를 수행할 때, 커버링 인덱스를 스캔 하게 됩니다. 이는 하나의 인덱스가 SELECT 문이 요구하는 조건과 결과를 모두 포함하고 있기 때문에 가능한 일입니다.

CREATE TABLE tbl (i INT, j INT);

CREATE INDEX idx ON tbl(i, j);

INSERT INTO tbl VALUES(1,11), (2,12);

SELECT j FROM tbl WHERE i > 0;

그렇다면 왜 커버링 인덱스라는 개념이 필요할까요?

이 설명에 앞서, 우선 CUBRID의 인덱스 구조에 대해 간단하게 설명 하겠습니다.

CUBRIDB+Tree를 이용하여 인덱스를 구현하고 있습니다. B+Tree는 다음 그림과 같이 루트 노드, leaf 노드, non-leaf 노드로 구성되는데, leaf 노드에는 데이터 행(row)의 위치(OID, Object Identifier)가 저장됩니다. 데이터는 힙(heap) 파일 내에 저장이 되며 데이터의 저장 순서는 인덱스의 순서와 관련이 없습니다.

cubrid_index_covered_techwriting.jpg  

앞서 언급한대로, CUBRID의 인덱스 구조에서는 데이터가 인덱스 외부에 위치하며, 데이터의 저장 순서는 인덱스 구성에 영향을 주지 않습니다. 이와 같은 형태의 인덱스를 non-clustered 인덱스라고 부릅니다. 이 구조에서는 인덱스가 데이터의 위치만 가지고 있기 때문에, 인덱스 탐색 중에 데이터를 읽어와야 하고 그로 인해 디스크 I/O가 추가로 발생할 수 있습니다.

그런데, 만약 이 때 사용되는 인덱스 내에서 SELECT 질의에 대한 결과를 모두 얻을 수 있는 상황이라면 어떨까요? 이 경우에는 힙 파일에 저장되어 있는 데이터를 읽어오지 않아도 인덱스 키의 값으로 결과를 만들어 낼 수 있을 것입니다. 이와 같이 인덱스가 하나의 질의를 모두 커버한 경우에 대해서 커버링 인덱스라고 말합니다.


 

커버링 인덱스는 I/O양을 줄일 수 있는 방법으로 알려져 있습니다. 힙 파일에서 데이터를 읽어오지 않음으로 인해 줄어드는 I/O 외에도, 일반적으로 인덱스 키가 데이터 행 전체의 크기보다 훨씬 작다는 점, 질의 처리에 인덱스를 자주 사용하게 되면 인덱스가 CUBRID 메모리 버퍼에 캐싱(caching)되어 있을 가능성이 높은 점이 I/O를 줄이는 역할을 합니다. 또한, 인덱스는 값에 따라 정렬되어 있기 때문에, 순차적(sequential) 접근이 가능하므로 데이터를 읽기 위해 힙 파일을 랜덤(random)하게 접근하는 것에 비해 I/O 비용이 적게 드는 장점도 생각해 볼 수 있습니다.


 

앞서 설명한 것과 같이, 커버링 인덱스는 많은 양의 I/O를 줄일 수 있고, SELECT 질의의 성능을 향상시킬 수 있습니다. 하지만 커버링 인덱스를 사용하기 위해 무작정 인덱스 컬럼을 늘이거나 인덱스를 추가로 생성하게 되면, 그에 따른 인덱스 관리 비용이 증가하고 입력/갱신 비용이 증가할 수 있습니다. 따라서 커버링 인덱스 사용 여부를 결정할 때에는 질의의 성능, I/O 비용, 저장 공간에 대한 비용 등을 전체적으로 고려해야 합니다. 일반적으로 데이터 행 전체의 크기에 비해 인덱스 키의 크기가 작고, 커버링 인덱스를 이용하는 질의가 자주 수행되는 것이 확실하다면, 커버링 인덱스를 사용하는 편이 합리적입니다.


참고

clustered 인덱스는 인덱스를 생성한 테이블의 레코드 각 행이 인덱스 컬럼 값의 순서로 물리적으로 정렬되어 있는 것을 말합니다. 쉽게 생각해서, 인덱스 내에 테이블의 모든 레코드가 포함되어 있는 구조를 생각하면 됩니다. 따라서 하나의 테이블에는 하나의 clustered 인덱스만 정의할 수 있습니다. 이 경우에는 레코드가 입력될 때마다 물리적인 정렬이 필요하지만, 레코드를 좀 더 빠르게 찾아올 수 있는 장점이 있습니다. 반면, non-clustered 인덱스에 레코드를 입력할 때에는, 물리적인 정렬이 필요 없기 때문에, 인덱스 컬럼의 값이 자주 수정되는 경우에 유리하다고 볼 수 있습니다.

 

MySQL clustered 인덱스를 primary 인덱스로 사용하고, non-clustered 인덱스를 secondary 인덱스로 사용합니다. , PK unique 속성을 가진 인덱스가 primary 인덱스가 되고, 그 외의 인덱스는 secondary 인덱스로 정의되며 leaf PK 값을 저장하는 형태가 됩니다. 따라서 secondary 인덱스를 스캔하여 결과를 얻어오는 경우에는 primary 인덱스를 한번 더 스캔해야 하므로 총 2번의 인덱스 스캔이 필요합니다.

http://dev.mysql.com/doc/refman/5.6/en/innodb-table-and-index.html#innodb-index-types

 

CUBRID에서 커버링 인덱스가 사용되는 것을 확인하기 위해서는 질의 실행 계획(plan)을 확인하면 됩니다. 앞서 예를 든 질의의 쿼리를 살펴보면, 다음과 같이 “covers”라는 단어를 확인할 수 있습니다. 인덱스 스캔 계획에서 “covers”라는 단어가 보이면 커버링 인덱스가 사용 된 것입니다.

Query plan:

iscan

    class: tbl node[0]

    index: idx term[0] (covers)

    cost:  fixed 0(0.0/0.0) var 1(0.0/1.0) card 0


서버 실행 통계 정보에서는 다음 항목을 통해 커버링 인덱스를 사용한 횟수를 알 수 있습니다. 이 숫자는 커버링 인덱스를 사용하여 만들어 낸 튜플(tuple)의 개를 의미합니다.

 

서버 실행 통계 정보 확인 방법은 매뉴얼을 참고해주시기 바랍니다. (http://www.cubrid.com/online_manual/840/admin/admin_db_statdump.htm)

참고

커버링 인덱스 기능으로 인해 index_scan_key_buffer_size 파라미터가 추가되었습니다.

이 파라미터는 커버링 인덱스 사용 시, 인덱스 컬럼의 값을 저장하는 메모리 버퍼의 크기를 결정해 주는 버퍼입니다. 인덱스 스캔 중, 이 버퍼가 모두 소진되면 스캔을 잠시 멈추고 버퍼 내의 데이터를 모두 처리한 후에 멈춘 부분부터 다시 스캔을 시작하게 됩니다. 가급적 인덱스 스캔을 멈추지 않는 편이 성능에 유리하겠지만, 그만큼 많은 메모리를 사용해야 하므로 꼭 좋은 것만은 아닙니다. 커버링 인덱스를 사용하는 경우에는 인덱스 컬럼의 크기와 데이터베이스의 동시 사용자 수를 고려하여 이 파라미터의 값을 적절하게 설정하는 편이 좋습니다. 이 파라미터의 기본 값은 320K입니다.

본 문서의 내용은 CUBRID 2008 R4.0 매뉴얼의 다음 부분에서 찾아볼 수 있습니다.

  >  CUBRID SQL 설명서  >  질의 최적화  >  인덱스 활용  >  커버링 인덱스

http://www.cubrid.com/online_manual/840/syntax/syntax_retreive_index_cover.htm 

CUBRID 2008 R4.0에서 커버링 인덱스를 도입하면서, 커버링 인덱스의 효과를 측정하기 위해 다음과 같은 간단한 시나리오를 이용하여 성능 시험을 해 보았습니다.

1. DB 스키마

한 행(row)의 크기가 2K 정도 되도록 CHAR 타입의 dummy 필드를 추가 하였고, C1, C2, C3 컬럼에 대해서 인덱스를 생성 하였습니다.

 

CREATE TABLE T (C1 INT, C2 INT, C3 INT, C4 INT, C5 CHAR(2032));

CREATE INDEX IDX ON T (C1, C2, C3);

 

2. 테스트 질의

-- [Q1] 커버링 인덱스 사용

SELECT C1, C3 FROM T WHERE C1 BETWEEN ? AND ?

-- [Q2] 커버링 인덱스 미사용

SELECT C1, C4 FROM T WHERE C1 BETWEEN ? AND ?


SELECT 리스트에 정의한 컬럼의 개수 및 크기, 인덱스 스캔 조건은 동일하게 설정하였고, SELECT 리스트 내의 컬럼 하나만 변경시켜서 인덱스가 커버링 되는 경우와 그렇지 않은 경우를 시험할 수 있도록 하였습니다. 각 테스트 질의에 사용되는 BETWEEN 조건을 이용하여 100개의 레코드를 무작위로 가져오도록 하였습니다.

각 테스트 질의에 대한 실행 계획(plan)은 다음과 같습니다. Q1은 인덱스로 커버 되는 것을 확인할 수 있습니다.

[Q1]

Query plan:

iscan

    class: T node[0]

    index: idx term[0] (covers)

    cost:  fixed 184(0.0/184.0) var 10301(300.0/10001.0) card 100000

[Q2]

Query plan:

iscan

    class: T node[0]

    index: idx term[0]

    cost:  fixed 184(0.0/184.0) var 10301(300.0/10001.0) card 100000

3. 시험 환경

- CPU: Xeon 2.27GHz Quad * 2 (8 threads)

- Memory: 24G

- HDD: SAS 300G * 6

- 테이블 내 레코드 개수: 1천만건 (C1, C2, C3는 각각 0~9,999,999까지 저장되어 있음)

- DB 크기: 인덱스 약 300M, 힙 약 21.8G

- CUBRID 페이지 버퍼 크기: 2G

- Fetched row 수: 100건

 

4. 시험 결과

커버링 효과를 확인하기 위해, 질의 Q1, Q2를 각각 10000회씩 수행하는 자바 프로그램을 작성하였고, 해당 프로그램 내에서 PreparedStatement.executeQuery() 메소드가 수행되는 시간을 측정하였습니다.

537cfc043340d5ce205f8b7dbbefc424_techwriting.jpg 

 

질의 수행시간이 차이가 나는 이유는 디스크 I/O 때문입니다. 시험을 수행하는 동안 데이터를 패치한 횟수와 CUBRID의 페이지 버퍼에서의 cache miss로 인해 실제 디스크 I/O가 일어난 횟수는 다음과 같습니다. 평균 hit ratio는 Q1이 83%, Q2가 15%로 나타났습니다.

 

  78cb0955bd3c39a35c6dbd4739e57b90_techwriting.jpg

 

위 시험 결과에서 볼 수 있듯이, 커버링 인덱스를 잘 사용하면 질의의 성능을 크게 개선할 수 있습니다. 하지만 이전 글에서도 밝힌 바와 같이 무조건 커버링 인덱스를 사용하는 것이 정답은 아니므로, 서비스 특성과 환경을 잘 고려하여 사용해야 합니다. 또한, 본 시험 결과 역시 절대적인 것이 아니며, 시험 모델에 따라 다른 결과가 나올 수 있습니다.

TAG •

List of Articles
번호 분류 제목 글쓴이 날짜 조회 수
126 기타 CUBRID 2008 R4.0 GA 버전의 주요 기능만 요약한 PPT 문서 file admin 2011.07.14 15460
125 기타 CUBRID 2008 R4.0 파라미터 변경 사항 (일부) file admin 2011.07.14 13154
124 기타 CUBRID 볼륨 공간 재사용률 비교 - 2008 R3.1 vs. 2008 R4.0 file admin 2011.07.14 16554
123 튜닝 SNS 유형 서비스에서 CUBRID와 MySQL 조회 성능 비교 file admin 2011.07.14 21187
» 튜닝 CUBRID 2008 R4.0의 커버링 인덱스(covering index)는 무엇인가? file admin 2011.07.14 29880
121 튜닝 [질의튜닝]order by desc가 인덱스 타게 하려면 admin 2011.07.14 28455
120 튜닝 CUBRID 세미나 자료(개요 및 SQL 활용) file admin 2011.07.14 22768
119 PHP apache, php rpm package 생성 시 필요한 rpm 스펙 file admin 2011.07.14 25230
118 Install CUBRID Windows버전 삭제 및 재설치 실패시 강제 삭제하는 방법입니다. admin 2011.07.14 29547
117 PHP MySQL,PHP 기반에서 CUBRID,PHP 기반으로 포팅하기(CUBRID 2008 R3.1) 손승일 2011.03.30 24446
116 PHP phpize 를 이용한 PHP 모듈 설치 - LINUX file 남재우 2011.03.08 23080
115 PHP apache, PHP 설치하기 - LINUX file 남재우 2011.03.08 31301
114 기타 CUBRID에서의 BLOB/CLOB 타입 사용법 cubebridge 2011.03.08 30436
113 Install CUBRID 설치 안내 - LINUX 남재우 2011.03.08 30412
112 기타 데이터베이스 생성하기 file 남재우 2011.03.07 29058
111 기타 CUBRID 활용 - 질의 튜닝 사례 중심 file cubrid 2010.12.02 45960
110 기타 CUBRID Migration Toolkit 튜토리얼 file cubrid 2010.10.22 30471
109 기타 CUBRID 2008 R3.0 추가기능 file janus 2010.09.03 15455
108 기타 트랜잭션과 LOCK 초급과정 정만영 2010.07.01 20760
107 Linux CUBRID DB와 Broker 분리방안 정만영 2010.04.28 28411
Board Pagination Prev 1 2 3 4 5 6 7 8 9 Next
/ 9

Contact Cubrid

대표전화 070-4077-2110 / 기술문의 070-4077-2113 / 영업문의 070-4077-2112 / Email. contact_at_cubrid.com
Contact Sales