인덱스

랜덤 I/O && 순차 I/O

데이터베이스의 기본 작업 중 하나는 디스크에서 데이터를 읽고 쓰는 것이다. 데이터의 양이 많아질수록 디스트 I/O가 얼마나 효율적인지가 성능에 큰 영향을 끼친다. I/O 방법엔 크게 두가지가 있다.

랜덤 I/O

하드 디스크 드라이브(HDD)의 원판을 돌려서 읽으나 디스크 헤드가 여러 위치로 움직이기 때문에 비효율적이다.

순차 I/O

있는 순서대로 디스크 헤드가 한번만 움직인다.

랜덤 I/O와 순차 I/O의 차이

예를 들어, 10개의 페이지를 디스크에 기록한다고 해보자. 순차 I/O는 1번 시스템 콜을 요청하지만, 랜덤 I/O는 10번 시스템 콜을 요청하게 된다.

즉, 랜덤 I/O일 때 헤드가 10배나 많이 움직이게 되고 시간이 10배나 더 오래걸린다고 할 수 있다.

디스크의 성능은 디스크 헤더의 위치 이동없이 얼마나 많은 데이터를 한 번에 기록하느냐에 결정된다.

따라서, 쿼리를 튜닝해서 성능을 높일 때 이 랜덤 I/O를 줄여주는 것이 핵심이다. 이 역할을 하는 것이 인덱스이다.

인덱스

인덱스란?

인덱스는 데이터베이스에서 데이터를 빠르게 찾을 수 있도록 도와주는 자료구조이다. 테이블의 컬럼에 대한 포인터를 포함하여, 검색과 정렬 작업의 속도를 높힌다.

인덱스 동작 방식

• 칼럼의 값과 해당 레코드가 저장된 주소를 키와 값(key- Value pair)로 삼아 인덱스를 만든다.

• 저장되는 칼럼의 값을 이용해 항상 정렬된 상태를 유지한다.

인덱스 설정 기준

• 검색에 용이하기 때문에 주로 검색 빈도가 높아야 한다.

• 중복도가 낮은 칼럼

• WHERE절이나 JOIN, ORDER BY에 자주 사용되는 칼럼

단순히 테이블에 인덱스를 많이 설정하면 좋을까??

인덱스는 검색에 특화된 자료구조이기 때문에 쓰기, 수정, 삭제와 같은 DML 작업은 느려질 수 있고, 추가적인 저장 공간이 필요하기 때문에 상황에 따라 판단을 해야한다.

커버링 인덱스(Covering index)

커버링 인덱스는 쿼리에 필요한 모든 데이터를 인덱스 자체에서 제공할 수 있을 때 사용된다. 이는 데이터베이스 엔진이 테이블을 참조할 필요 없이 인덱스만으로 쿼리를 해결할 수 있게 해, 성능을 크게 향상시킨다.

다중 컬럼 인덱스(Multi-column index, 복합 인덱스)

다중 컬럼 인덱스는 두 개 이상의 컬럼을 포함하는 인덱스다. 이는 복합적인 쿼리 조건을 효율적으로 처리할 수 있도록 도와주며, 컬럼의 순서가 중요하다.

B-Tree && B+Tree 인덱스

B-Tree 인덱스는 균형잡힌 트리 구조로, 모든 리프 노드가 같은 깊이에 있어 검색, 삽입, 삭제가 효율적이다. B+Tree는 B-Tree의 변형으로, 모든 값과 포인터가 리프 노드에 있으며, 리프 노드끼리 연결되어 있어 범위 검색에 유리하다.

Hash 인덱스

Hash 인덱스는 키 값을 해시 함수를 통해 해시 값으로 변환하고, 이 해시 값을 사용하여 데이터를 빠르게 찾는 인덱스이다. 일반적으로 등가 조건 검색에 유리하다.

클러스터링 인덱스

클러스터링 인덱스는 데이터를 인덱스의 순서에 따라 물리적으로 저장하는 인덱스다. 이는 데이터의 물리적 순서와 인덱스 순서가 일치하게 만들어, 특정 범위의 데이터를 검색할 때 효율적이다.

자세한 내용은 클러스터링 인덱스에 가면 볼 수 있다.

인덱스 스캔 방식

인덱스 스캔은 데이터베이스가 인덱스를 사용하여 데이터를 검색하는 방식이다. 이는 Full Index Scan, Index Range Scan, Index Unique Scan 등 여러 형태가 있으며, 쿼리의 조건과 인덱스의 유형에 따라 달라진다.

쿼리 실행 계획

쿼리 실행 계획은 데이터베이스가 쿼리를 어떻게 실행할 것인지를 보여주는 정보이다. 이를 통해 쿼리의 성능을 분석하고 최적화할 수 있다.

예를 들어, MySQL에서는 EXPLAIN 명령을 사용하여 쿼리의 실행 계획을 볼 수 있다.

힌트

SQL 힌트는 데이터베이스 쿼리 최적화기에게 특정 쿼리를 어떻게 실행할지 제안하는 방법이다.

잘못 사용하면 예상치 못한 성능 저하를 초래할 수 있기 때문이다.

인덱스가 잘 동작하고 있는지 확인하려면??

여러가지 방법이 있다. 그 중에서 실행 계획을 확인하는게 제일 간단하고 효율적이다.

EXPLAIN
SELECT prod_id
FROM PRODUCT
WHERE prod_id = 100306;

• type: 레코드를 어떤 방식으로 읽었는지 나타낸다.
• key: 최종 선택된 실행 계획에서 사용하는 인덱스를 의미한다.
• Extra: 내부적인 알고리즘에 대해 나타낸다.
  • Using Index: 커버링 인덱스를 나타낸다.

인덱스 사용시 주의 사항

인덱스는 적절히 사용될 때 매우 유용하지만, 과도하게 사용하면 오히려 성능 저하를 일으킬 수 있다. 쓰기 작업(INSERT, UPDATE, DELETE) 시 인덱스는 추가적인 작업을 필요로 하기 때문에, 불필요한 인덱스는 오히려 부담이 된다. 또한, 인덱스의 크기가 커질수록 유지 관리 비용도 증가한다. 따라서, 중요한 쿼리와 테이블에 대해서만 인덱스를 사용하는 것이 바람직하다.

GROUP BY 사용시 인덱스가 걸리는 조건

GROUP BY 절에서 인덱스가 효과적으로 작동하려면, 그룹화하려는 컬럼에 인덱스가 설정되어 있어야 한다. 예를 들어, GROUP BY 절에 이름과 나이 컬럼이 사용된다면, 이 두 컬럼에 대한 인덱스 설정이 성능을 향상시킬 수 있다. 특히, 인덱스가 그룹화 순서와 일치할 때 가장 효율적이다.

이름, 국가, 성별이 있는 테이블에서 인덱스를 어떻게 걸어야 할까??

인덱스는 쿼리 패턴과 데이터의 특성을 고려하여 결정해야 한다. 이름이 자주 검색되고, 국가와 성별이 필터 조건으로 사용된다면, 이름에 단일 컬럼 인덱스를, 국가와 성별에는 별도의 인덱스를 고려할 수 있다. 또한, 이름, 국가, 성별을 동시에 사용하는 쿼리가 많다면, 이 세 컬럼을 포함하는 복합 인덱스도 고려해 볼 수 있다.