데이터베이스 스터디 3주차

랜덤 I/O와 순차 I/O

	랜덤 I/O	순차 I/O
의미	데이터에 임의로 접근하는 입출력 방식 디스크 헤드를 특정 위치로 직접 이동하여 데이터를 읽거나 쓰는 것	데이터를 연속적인 순서로 접근하는 입출력 방식 디스크 헤드를 한 방향으로 움직이는 것
언제 사용하는가	데이터베이스에서 특정 레코드 조회, 갱신 또는 삭제	전체 테이블을 스캔하거나 대량의 데이터를 일괄 처리해야 할 때

 

인덱스(Index)

데이터베이스에서 효율적인 검색을 위해 사용되는 자료구조

원하는 데이터를 빨리 찾기 위해 투플의 키 값에 대한 물리적 위치를 기록합니다.

주로 B-Tree 또는 B+Tree 구조를 사용하여 데이터를 저장하고 검색합니다.

 

인덱스의 동작 방식은 B-tree와 B+tree에 따라서 달라집니다.

우선 B-Tree에 대해 설명하겠습니다.

 

B-tree(Balanced Tree)

균형 트리 구조를 가진 자료구조입니다.

균형 트리는 모든 리프노드가 같은 레벨에 존재하는 것을 의미합니다.

최상단 루트 노드, 중간의 브랜치 노드, 최하위의 리프노드로 이루어져 있습니다.

인덱스는 항상 키값을 기준으로 정렬되어 있고 특유의 트리구조를 이용하여 빠른 검색과 효율적인 레코드 접근이 가능합니다.

 

B-tree의 경우 모든 내부 노드에는 데이터 값과 데이터 포인터 값을 가지고 있습니다

또한 브랜치 노드의 경우 데이터 값 기준으로 크거나 작은 값을 가진 다음 레벨의 트리 포인터를 가지고 있습니다

 

값이 추가되거나 삭제될 경우, 동적으로 노드를 분할하거나 통합하여 항상 균형 상태를 유지합니다.

 

B-tree (Fundamentals of Database Systems)

 

 

 

B+tree

B-tree를 변형한 것으로 모든 데이터 포인터는 리프노드만 가지고 있습니다

B+tree는 리프노드가 링크드 리스트로 이루어져 있기 때문에 범위 쿼리나 순차적인 탐색이 용이합니다.

또한 리프 노드에만 키 값이 있어 메모리 사용량이 더 효율적일 수 있습니다.

 

B+tree (Fundamentals of Database Systems)

 

 

B+Tree의 데이터 추가 과정

(Fundamentals of Database Systems)

 

 

B+Tree의 데이터 삭제 과정

 

인덱스 설정 기준

 

1. 데이터의 양을 고려해야 합니다.

데이터의 양이 별로 없거나 데이터 값이 몇 종류 안되는 경우 오히려 인덱스가 없는 게 더 빠를 수 있습니다.

2. WHERE 절에 자주 사용되는 속성이어야 합니다.

3. 검색이 자주 발생하는 컬럼이나 조인 조건에 인덱스를 설정해야 합니다.

4. 단일 테이블에 인덱스가 많으면 속도가 느려질 수 있습니다(테이블 당 4~5개 정도 권장)

5. 속성이 가공되는 경우 사용하지 않습니다.

6. 속성의 선택도가 낮을 때 유리합니다(속성의 모든 값이 다른 경우)

 

하지만 인덱스를 많이 설정하면 쓰기 연산의 성능이 저하될 수 있으므로 적절한 균형을 유지해야 합니다.

 

커버링 인덱스

쿼리의 결과를 인덱스 자체에서 가져올 수 있는 경우를 말합니다. 

 

다중 컬럼 인덱스(Multi-column Index)

두 개 이상의 컬럼에 대한 인덱스입니다.

여러 조건을 함께 고려해야 할 때 유용합니다.

Hash 인덱스

해시 함수를 사용하여 키와 연결된 버킷에 데이터를 저장하는 인덱스입니다.

주로 등값 비교에서 사용되며, 범위 검색에는 적합하지 않습니다.

클러스터링 인덱스

테이블 데이터를 인덱스의 순서대로 저장하는 방식으로, 인덱스와 실제 데이터의 물리적인 순서가 일치합니다.

클러스터링 인덱스는 테이블당 하나만 생성할 수 있으며, 키 값에 의한 동등 및 범위(BETWEEN) 검색 모두에 유리합니다.

테이블 생성 시 PK를 생성하면 자동으로 생성됩니다.

인덱스 스캔 방식

인덱스를 사용하여 특정 범위의 데이터를 찾는 방식. 인덱스의 종류에 따라 다양한 스캔 방식이 있습니다.

 

1. 인덱스 전체 스캔 (Index Full Scan)

인덱스의 전체 범위를 스캔하는 방식입니다.

주로 전체 데이터를 조회하는 쿼리에서 사용됩니다.

2. 인덱스 범위 스캔 (Index Range Scan):

특정 범위의 키 값을 가진 레코드를 인덱스에서 스캔하는 방식입니다.

주로 BETWEEN, >, >=, <, <= 연산과 같은 범위 쿼리에서 사용됩니다.

 

3. 유니크 인덱스 스캔 (Unique Index Scan):

유일한 값을 가지는 고유 인덱스를 활용하여 스캔하는 방식입니다.

주로 PRIMARY KEY 또는 UNIQUE 제약 조건이 있는 컬럼에 대한 조회에서 사용됩니다.

 

4. 루스 스캔 (Loose Index Scan):

인덱스의 일부만을 스캔하는 방식입니다.

즉, 모든 레코드를 순차적으로 접근하면서 일치하는지 확인합니다.

5. 병합 스캔 (Merge Scan):

두 개 이상의 인덱스를 이용하여 여러 개의 인덱스 스캔을 병합하는 방식입니다.

여러 인덱스 간의 병합을 통해 최종 결과를 얻습니다.

 

쿼리 실행 계획

데이터베이스 엔진이 쿼리를 처리하기 위해 선택한 최적의 실행 방법입니다.

이를 확인할 수 있는 도구가 있으며, 대표적으로 EXPLAIN문을 사용합니다.

힌트

데이터베이스 옵티마이저에게 특정 실행 계획을 사용하도록 알려주는 명령어 또는 주석입니다.

인덱스 사용 확인

EXPLAIN문을 사용하여 쿼리의 실행 계획을 확인하고, 인덱스가 어떻게 사용되는지 파악할 수 있습니다.

인덱스 사용 주의점

인덱스를 과도하게 사용하면 쓰기 연산의 성능이 저하될 수 있으므로, 신중하게 필요한 곳에만 인덱스를 생성해야 합니다.

GROUP BY 인덱스

GROUP BY를 사용하는 경우, GROUP BY에 사용된 컬럼들에 인덱스를 설정하면 집계 함수를 효율적으로 처리할 수 있습니다.

이름, 국가, 성별 테이블의 인덱스 설정

이름, 국가, 성별이 있는 테이블에서는 자주 검색되는 조건에 따라 인덱스를 설정해야 합니다.

검색이 자주 발생하는 경우, 해당 컬럼들에 인덱스를 설정하면 성능을 향상시킬 수 있습니다.

또한 속성의 선택도가 낮을 수록 유리하기 때문에 값이 남,여 2가지인 성별 속성은 인덱스를 만들기에 적합하지 않을 수 있다.

 

 

---

참고

Fundamentals of Database Systems

MySQL로 배우는 데이터베이스 개론과 실습

https://dncjf64.tistory.com/436