데이터베이스의 데이터가 커질수록 데이터를 조회하는데 시간이 오래걸리게 된다. 이를 해결하기 위해 인덱스를 사용하게 되는데, 남용하게 될 경우 오히려 데이터베이스의 성능을 저하시킬 수 있다. 그래서 인덱스를 효율적으로 사용하기 위해서는 인덱스가 어떻게 동작하는지 알아야한다.
인덱스 스캔 방식
인덱스 스캔 방식은 인덱스를 탐색하는 방식을 의미한다.(이 설명은 MySQL을 기준으로 설명하였다).
인덱스 레인지 스캔(Index Range Scan)
인덱스 레인지 스캔은 특정 범위 내의 데이터를 검색할 때 사용된다.
이는 일반적으로 WHERE 절에 BETWEEN, IN, >, < 등의 연산자가 사용될 때 발생한다.
MySQL은 루트 노드에서 시작하여 브랜치 노드를 거쳐 검색 시작점 리프 노드를 찾은 후,
리프 노드 간의 링크를 따라 검색 종료점 리프 노드 까지 데이터를 스캔한다.
이때 레코드를 랜덤 엑세스로 읽어 들이게 된다.
예를 들어, 다음과 같은 쿼리를 실행할 때 인덱스 레인지 스캔이 발생한다.
SELECT * FROM users WHERE age BETWEEN 20 AND 30;이 쿼리는 users 테이블에서 age 컬럼에 인덱스가 걸려 있다면, 해당 범위 내의 데이터를 효율적으로 검색할 수 있다.
인덱스 풀 스캔(Index Full Scan)
인덱스 풀 스캔은 인덱스의 처음부터 끝까지 전체를 스캔하는 방식이다.
이는 WHERE 절에 조건에서 인덱스의 특정 순서를 활용할 필요가 없거나, 범위가 명확하지 않은 조건에서 발생한다.
인덱스 풀 스캔은 레인지 스캔에 비해 더 많은 블록을 읽어야 하기 때문에, 레인지 스캔보다 느릴 수 있다.
예를 들어, 다음과 같은 쿼리를 실행할 때 인덱스 풀 스캔이 발생한다.
SELECT * FROM users WHERE job = 'developer';users 테이블에서 job 컬럼에 인덱스가 존재하지만, 검색 조건이 인덱스의 순서를 활용하지 못하는 경우, 인덱스 풀 스캔이 발생한다.
인덱스 설계
인덱스의 스캔 방식과에 대해 알아봤다면, 이젠 어떤 기준으로 인덱스를 설정해야하는지 알아야한다.
효율적인 인덱스 설계
다음은 효율적인 인덱스 설계를 위한 몇 가지 팁이다.
WHERE절에 자주 사용되는 컬럼에 인덱싱을 하는 것이 좋다.SELECT절에 자주 사용되는 컬럼들을 조합해서 인덱스로 만들어 커버링 인덱스를 사용하는 것이 좋다.JOIN절에 자주 사용되는 컬럼에 인덱싱을 하는 것이 좋다.ORDERBY 절에 자주 사용되는 컬럼에 클러스터형 인덱스를 사용하는 것이 좋다.
지양해야할 인덱스 설계
다음은 지양해야할 인덱스 설계를 위한 몇 가지 팁이다.
- 대용량 데이터가 자주 입력되는 경우 클러스터형 인덱스의 페이징이 빈번하게 발생해 시스템에 부하가 걸릴 수 있다. 때문에 클러스터 대신 세컨더리 인덱스를 사용하는 것이 좋을 수 있다.
- 데이터 중복도가 높은 컬럼에 인덱싱을 하는 것은 효율적이지 않다. 중복도가 높은 컬럼에 인덱싱을 하게 되면 인덱스의 크기가 커지고, 인덱스를 탐색하는데 시간이 오래걸릴 수 있다.
- 인덱스를 많이 사용하게 되면 인덱스를 관리하는데 시간이 오래걸리게 되고, 인덱스의 크기가 커지게 된다. 때문에 인덱스를 많이 사용하는 것은 지양해야한다.
- 자주 사용되지 않는 컬럼에 인덱싱을 하는 것은 효율적이지 않다.
인덱스 스캔 방식
인덱스 스캔 방식은 인덱스를 탐색하는 방식을 의미한다. 인덱스 스캔 방식에는 여러가지가 있는데, 그 중에서 대표적인 방식은 다음과 같다. B Tree 기준으로 설명하자면, 인덱스 레인지 스캔은 검색 해야할 인덱스의 범위가 결정 됐을때 사용하는 방식이다. 루트 노드, 브랜치 노드를 거쳐 검색 시작점 리프 노드를 찾고, 리프 노드간의 링크를 통해 검색 종료점 리프 노드까지 스캔하면서 레코드를 랜덤 엑세스로 읽어 들인다. 인덱스 풀 스캔은 인덱스의 처음부터 끝까지 모두 스캔하는 방식이다. 예를들어 a,b,c 컬럼에 복합 인덱스가 걸려있고 쿼리의 조건절에는 b 또는 c 컬럼이 들어가 있을 경우 인덱스 풀 스캔이 발생한다. 테이블 풀 스캔보다는 효율적이다.
쿼리 실행 계획
쿼리 실행 계획(Query Plan)은 데이터베이스 관리 시스템(DBMS)이 SQL 쿼리를 처리하기 위해 사용하는 실행 계획이다. 이것은 DBMS에서 쿼리를 처리할 때 데이터베이스에서 데이터를 검색하고 가져오는 방법을 결정하는데 사용된다. 쿼리 실행에 필요한 단계를 보여주며, 각 단계에서 DBMS가 사용하는 액세스 경로를 보여주고, 쿼리 실행에 필요한 리소스 및 비용 정보를 제공한다. 즉, SQL을 실행하는데 있어 사용되는 cost와 어떠한 방식이 가장 적절한지 판단하고, 올바른 플랜 중 평가하여 어떠한 순서와 방식으로 처리할 것인지 계획을 세우고 필요에 따라 변경하여 성능을 개선하는데 사용된다. 이를 통해 DBMS는 쿼리 실행에 필요한 최소한의 리소스를 사용하면서 최적의 성능을 제공할 수 있다.
쿼리 실행 계획 확인 방법
쿼리 실행 계획을 확인하는 방법은 DBMS마다 다르지만, 대부분의 DBMS에서는 EXPLAIN 명령어를 사용하여 확인할 수 있다.
EXPLAIN 명령어는 쿼리 실행 계획을 확인하는 명령어로, 쿼리를 실행하기 전에 어떠한 방식으로 실행할지에 대한 정보를 제공한다.
예를 들어, MySQL에서는 다음과 같이 사용할 수 있다.
EXPLAIN SELECT * FROM users WHERE age BETWEEN 20 AND 30;위 쿼리를 실행하면, 해당 쿼리의 실행 계획을 확인할 수 있다.
쿼리 실행 계획 확인 방법
이러한 쿼리 실행 계획은 옵티마이저가 사용하게 되는데 이때 예상치 못한 문제가 발생할 수 있다. 이러한 문제를 해결하기 위해 쿼리 힌트를 사용할 수 있다. 쿼리 힌트는 옵티마이저가 쿼리를 처리하는 방식을 제어하는 방법으로, 쿼리에 힌트를 추가하여 옵티마이저가 쿼리를 처리하는 방식을 변경할 수 있다.
인덱스 동작 확인 방법
인덱스 동작을 확인하는 방법은 쿼리에 대한 실행 계획을 보면서 인덱스가 어떻게 걸리는지 확인하여 실제 조회 시 속도와 성능을 확인할 수 있다.
하지만 가끔씩 인덱스가 제대로 동작하지 않는 경우가 있을 수 있다.
이런 경우에는 sys.shema_unused_indexes를 사용해 인덱스 동작 여부를 확인할 수 있다.
SELECT * FROM sys.shema_unused_indexes;이렇게 사용되지 않는 인덱스를 확인하고, 필요한 인덱스를 추가하거나 사용되지 않는 인덱스를 삭제하여 성능을 향상시킬 수 있다.
ORDER BY 와 GROUP BY에 대한 인덱스
인덱스는 WHERE 절에 사용되는 컬럼에 대해서만 동작하는 것이 아니라, ORDER BY와 GROUP BY에도 영향을 줄 수 있다. 하지만 조건이 있는데 GROUP BY 기준으로 예를 들자면, 복합인덱스를 사용한다고 가정했을 때 GROUP BY (복합인덱스 컬럼들)로 사용해야 인덱스가 동작한다. ORDER BY의 경우에는 인덱스의 정렬 방식과 일치해야 인덱스가 동작한다.
커버링 인덱스 : 쿼리 조회 결과가 인덱스에 해당하는 컬럼 값으로만 이루어진 것을 커버링 인덱스라고 한다. 커버링 인덱스로 처리되는 쿼리는 디스크의 레코드를 읽지 않아도 되기 때문에 그만큼 랜덤 엑세스가 줄어들고 성능은 빨라진다.
다중 컬럼 인덱스 : 다중 컬럼 인덱스는 2개 이상의 컬럼으로 이루어진 인덱스다. 2개의 컬럼으로 이루어진 인덱스가 있을때, 인덱스의 두 번째 컬럼은 첫 번째 컬럼에 의존해서 정렬된다는 특징이 있다는 것을 고려해서 사용해야한다.