3주차 - 인덱스

1. 랜덤 I/O와 순차 I/O에 대해서 설명해주세요.

디스크 읽기 방식에 따라 랜덤 I/O와 순차 I/O로 나뉩니다. 랜덤 I/O(Random I/O)는 임의의 순서대로 데이터에 접근하는 방식을 말합니다. 순차 I/O(Sequencial I/O)는 논리적/물리적 순서대로 데이터에 접근하는 방식을 말합니다.

속도?

일반적으로 순차 I/O가 랜덤 I/O보다 빠릅니다. 3개의 페이지를 기록하는 경우 순차 I/O는 시스템콜을 한 번만 요청합니다. 반면에 랜덤 I/O는 시스템 콜을 세 번 요청합니다. 또한 SSD가 HDD에 비해 성능이 좋습니다. 순차 I/O는 SSD가 HDD에 비해 조금 빠르거나 거의 비슷합니다. 랜덤 I/O는 SSD가 HDD에 비해 훨씬 빠릅니다. 왜냐하면 SSD는 플래터(원판) 대신 플래시 메모리를 장착하고 있기 때문입니다.

예시?

랜덤 I/O는 WHERE 절이 포함된 쿼리를 실행해 데이터를 조작하거나 인덱스 레인지 스캔 시 사용합니다. 순차 I/O는 대량의 데이터를 정렬 및 그룹화하거나 풀 테이블 스캔 시 사용합니다.

성능 개선?

디스크의 성능을 높이려면 쿼리 튜닝이 필요합니다. 랜덤으로 저장된 데이터를 순차적으로 바꾸는 건 쉽지 않습니다. 따라서 랜덤 I/O가 일어나는 상황을 줄여서 성능을 개선할 수 있고 그 방법이 쿼리 튜닝입니다.

모범 예상 답변

펼치기

랜덤 I/O는 임의의 위치에 있는 데이터를 읽거나 쓰는 입출력 작업입니다. 순차 I/O는 연속적인 순서대로 데이터를 읽거나 쓰는 입출력 작업입니다. HDD에서 랜덤 I/O는 순차 I/O에 비해 헤더의 움직임이 많기 때문에 느립니다. 이는 SSD에서도 마찬가지입니다.

2. 인덱스에 대해서 설명해주세요.

인덱스(Index)는 DBMS의 색인으로 데이터 파일에 저장된 레코드 주소 접근을 돕는 자료구조입니다. 사전 정렬된 책의 색인과 마찬가지로 데이터에 저장된 레코드의 주소 값을 주어진 순서로 미리 정렬해서 보관합니다.

장단점?

인덱스는 테이블 조회 속도 및 성능을 향상시킬 수 있습니다. 성능이 향상되므로 시스템의 부하가 줄어듭니다. 반면에 DB의 10%에 해당하는 저장 공간이 필요합니다. 또한 인덱스를 관리하기 위한 추가 작업이 필요하며 잘못 사용하면 성능이 저하되는 역효과가 발생할 수 있습니다. 예를 들어 INSERT, UPDATE, DELETE가 빈번한 속성에 인덱스를 걸어보겠습니다. UPDATE와 DELETE는 기존 인덱스를 '사용하지 않음' 처리합니다. 이 경우 실제 데이터가 10만 건인데 인덱스는 100만 건이 넘어가 성능이 떨어집니다.

모범 예상 답변

펼치기

인덱스는 컬럼의 값과 레코드의 주소를 Key-Value 삼아놓은 정보입니다. 데이터베이스는 인덱스를 통해 빠르게 레코드를 조회할 수 있습니다. 빠른 조회를 위해 인덱스는 항상 정렬되어 있다는 특징이 있는데, 이 때문에 레코드의 삽입/수정/삭제 성능이 희생된다는 단점이 있습니다.

3. 인덱스의 동작 방식에 대해서 설명해주세요.

RDBMS에서 사용하는 알고리즘에 따라 동작 방식이 다릅니다. 보편적인 동작 원리는 다음과 같습니다.

생성?

인덱스를 생성할 때는 데이터가 변경되지 못하게 조치한 후 메모리에서 정렬합니다.

동작 방식?

SELECT ~ WHERE 구문을 실행한다고 가정하겠습니다. 먼저 WHERE 구문의 조건이 인덱스 컬럼인지 확인합니다. 만일 인덱스 컬럼이라면 해당 인덱스의 주소값만 조회합니다. 인덱스 컬럼이 아니라면 풀 테이블 스캔을 통해 WHERE 구문을 비교합니다. 풀 테이블 스캔은 전체 데이터를 읽기 때문에 많은 자원을 사용하고 처리 시간도 느립니다.

모범 예상 답변

펼치기

B Tree 인덱스 기준으로 설명 드리겠습니다. 사용자 요청으로 들어온 select 쿼리의 where에 인덱스가 걸린 컬럼이 조건으로 들어있다면, 컬럼 값에 해당하는 레코드를 찾기위해 B Tree의 루트 노드부터 시작해서 브랜치 노드를 거쳐 해당하는 리프 노드를 찾습니다. 그 다음 리프 노드에 있는 레코드의 실제 주소값을 따라 데이터 파일에서 레코드를 조회해옵니다.

4. 어떤 기준으로 인덱스를 설정해야할까요?

인덱스는 하나 혹은 여러 개의 컬럼에 대해 설정할 수 있습니다. 먼저 카디널리티가 높을 수록 인덱스를 설정하기 좋습니다. 카디널리티가 높다는 건 컬럼이 갖고 있는 값의 중복 정도가 낮다는 이야기입니다. 또한 선택도가 낮을 수록 인덱스를 설정하기 좋습니다. 선택도란 전체 레코드 수에서 특정 필드 값이 차지하는 비율입니다. 예를 들어 학생 테이블의 경우 학번의 선택도는 10%, 성별의 선택도는 50%일 수 있습니다. 또한 중복도가 낮을 수록 인덱스를 설정하기 좋습니다. 인덱스가 중복되면 인덱스를 관리하기 위한 비용이 늘어납니다. 마지막으로 활용도가 높을 수록 인덱스를 설정하기 좋습니다. 실제 작업에서 자주 사용되는 값을 인덱스로 설정하면 좋습니다.

모범 예상 답변

펼치기

검색 조건에 주로 사용되고 카디널리티가 높은 컬럼을 인덱스로 설정하면 좋습니다. 카디널리티는 인덱스에 해당하는 컬럼 기준으로 테이블에서 유일한 레코드 개수를 의미합니다. 카디널리티가 높을 컬럼에 인덱스를 걸면 그만큼 검색 대상이 줄어들기 때문에 빠르게 레코드에 접근할 수 있습니다.

5. 테이블에 인덱스를 많이 설정하면 좋을까요?

무조건 많이 설정하는 건 좋지 않습니다. 인덱스가 DB에서 차지하는 저장 공간이 늘어나 DB 관리 및 백업이 어려워지기 때문입니다. 또한 각각의 인덱스를 유지 관리하기 위한 추가 작업 시 작업량이 많아 처리 시간이 늘어납니다.

모범 예상 답변

펼치기

인덱스를 많이 설정하면 좋지 않습니다. 인덱스 자체를 저장하는 공간이 필요하기도 하고, 인덱스는 빠른 검색을 위해 항상 정렬된 상태를 유지해야하므로, 인덱스가 많아진다면 데이터 삽입, 수정, 삭제 성능이 저하될 우려가 있습니다. 따라서 인덱스를 추가할때는 데이터의 삽입,수정,삭제 성능을 얼마나 희생하고, 읽기 성능은 얼마나 빠르게 해야할지 고민하고 결정해야합니다.

6. 커버링 인덱스(Covering index)에 대해서 설명해주세요.

커버링 인덱스(Covering index)는 쿼리를 실행하는데 필요한 모든 데이터를 가지는 인덱스입니다. SELECT, WHERE, ORDER BY, GROUP BY 등에 사용하는 모든 컬럼이 인덱스의 구성요소입니다. 커버링 인덱스는 인덱스를 스캔하는 것으로 원하는 데이터를 가져올 수 있습니다. 따라서 컬럼을 읽기 위해 디스크에 접근해서 데이터 레코드를 읽지 않아도 됩니다.

모범 예상 답변

펼치기

쿼리 조회 결과가 인덱스에 해당하는 컬럼 값으로만 이루어진 것을 커버링 인덱스라고 합니다. 커버링 인덱스로 처리되는 쿼리는 디스크의 레코드를 읽지 않아도 되기 때문에 그만큼 랜덤 엑세스가 줄어들고 성능은 빨라집니다.

7. 다중 컬럼 인덱스(Multi-column index, 복합 인덱스)에 대해서 설명해주세요.

복합 인덱스(Multi-column index)는 여러 개의 컬럼으로 구성된 인덱스입니다. 복합 인덱스는 계층 구조로 구성되며 생성할 때 열 순서가 중요합니다. 열의 순서대로 뒤의 컬럼은 앞의 컬럼에 의존해서 정렬되기 때문입니다.

모범 예상 답변

펼치기

다중 컬럼 인덱스는 2개 이상의 컬럼으로 이루어진 인덱스입니다. 2개의 컬럼으로 이루어진 인덱스가 있을때, 인덱스의 두 번째 컬럼은 첫 번째 컬럼에 의존해서 정렬된다는 특징이 있다는 것을 고려해서 사용해야합니다.

8. B-Tree 인덱스와 B+Tree 인덱스에 대해 설명해주세요.

B 트리 인덱스는 B 트리 형식으로 구현된 인덱스입니다. B 트리는 모든 리프 노드가 동일한 계층을 갖는 BST이므로 균일한 검색 시간을 보장합니다.

저장?

먼저 새로운 키 값이 저장될 적절한 위치를 탐색합니다. 적절한 노드 위치가 결졍되면 레코드의 키 값과 주소 정보를 저장합니다. 만약 노드가 꽉 찬 경우 노드가 분리되고 트리가 재정렬됩니다. 따라서 B 트리는 상대적으로 쓰기 작업에 비용이 많이 듭니다.

삭제?

값이 삭제되는 경우 해당 키 값이 저장된 노드를 찾아서 '사용하지 않음' 처리를 합니다. 삭제 마킹된 인덱스 공간은 재활용할 수 있습니다.

변경?

키 값이 변경되는 경우 기존의 키 값을 삭제 마킹 작업을 하고 새로운 키 값을 추가합니다.

검색?

일반적인 트리 탐색 과정과 동일하게 루트 노드부터 시작해 브랜치 노드를 거쳐 리프 노드까지 이동합니다. 100% 일치 또는 값의 앞부분만 일치하는 경우 사용할 수 있습니다. 함수나 연산자를 사용하는 경우 B 트리의 장점을 이용할 수 없습니다. 인덱스의 동작 방식과 함수 및 연산자 동작 방식이 다르기 때문입니다.

B+ 트리는 B 트리의 확장으로 많은 부분이 동일합니다. 다만 B 트리와 달리 브랜치 노드에는 키 값만 저장하고 리프 노드에만 데이터에 대한 레코드 주소 값을 저장합니다. 또한 리프 노드끼리 연결 리스트로 연결되어 있습니다. 따라서 풀 스캔 시 선형 탐색이 가능해서 B 트리보다 빠릅니다.

모범 예상 답변

펼치기

B-Tree와 B+Tree는 둘다 Balanced Tree 자료구조로 구현된 인덱스 저장 방식입니다. B-Tree와 달리 B+Tree는 리프 노드에만 값을 담아 놓는다는 특징이 있습니다.

9. Hash 인덱스에 대해서 설명해주세요.

해시 인덱스는 해시 알고리즘으로 구현된 인덱스입니다. 컬럼의 값으로 해시 값을 계산해서 해시 함수의 결과를 저장합니다. 따라서 동등 비교 검색 시 빠르게 검색할 수 있습니다. 그러나 값을 변형해서 인덱싱하므로 범위 탐색이나 정렬에는 비효율적입니다. 해시 인덱스는 인메모리 DB에서 많이 사용합니다.

모범 예상 답변

펼치기

Hash 인덱스는 컬럼의 값으로 해시값을 계산해서 인덱스로 사용하는 알고리즘 입니다. 매우 빠른 검색을 지원하지만 값을 변형해서 인덱스로 사용하므로 prefix 일치 검색 또는 범위 검색에서는 사용될 수 없다는 단점이 있습니다.

10. 클러스터링 인덱스에 대해서 설명해주세요.

클러스터형 인덱스(Clustered Index)는 테이블 전체가 물리적으로 정렬되어 인덱스 역할을 하는 인덱스입니다. 클러스터형 인덱스는 리프 페이지가 키와 실제 데이터의 레코드 값을 가지고 있습니다. 특정 컬럼을 PK로 지정하면 클러스터형 인덱스가 생성됩니다. 클러스터형 인덱스는 테이블 당 한 번만 생성 가능합니다. InnoDB 테이블에서 레코드는 클러스터링 방식이라 PK 순서대로 정렬되어 저장됩니다.

비클러스터형 인덱스?

비클러스터형 인덱스(Non-Clustered Index, Secondary Index)는 테이블이 정렬되지 않는 인덱스입니다. 정렬된 인덱스 페이지를 생성하고 관리하며 리프 노드가 실제 데이터의 레코드 값을 가지고 있지 않습니다.

혼합?

현실적으로 하나의 테이블에는 클러스터형 인덱스와 비클러스터형 인덱스가 혼합되어 있습니다. PK가 기본적으로 존재하고 추가적으로 조회가 잦은 칼럼에 대한 인덱스를 추가하기 때문입니다.

모범 예상 답변

펼치기

클러스터링 인덱스 기본키가 비슷한 레코드끼리 묶어서 인접한 물리적 위치에 저장하는 것을 뜻합니다. 주로 비슷한 값들을 동시에 조회하는 경우가 많은데, 이에 착안해서 생긴 레코드 저장 방식입니다. 클러스터링 인덱스로 저장되는 테이블은 기본키 기반 검색이 매우 빠릅니다. MySQL의 innoDB 엔진은 기본적으로 클러스터링 인덱스로 저장됩니다.

11. 인덱스 스캔 방식에 대해서 설명해주세요.

인덱스 스캔 방식은 네 가지가 있습니다.

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

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

  3. 인덱스 고유 스캔(Index Unique Scan)

  4. 인덱스 루스 스캔(Index Loose Scan)

  5. 인덱스 병합 스캔(Index Merge Scan)

인덱스 풀 스캔은 인덱스의 전체를 검색합니다. 전체 데이터를 검색하기 때문에 DB 성능에 영향을 미칠 수 있습니다. 인덱스 레인지 스캔은 인덱스의 일부만 검색합니다. 일부 데이터를 검색하기 때문에 전체 스캔보다 효율적입니다. 인덱스 고유 스캔은 중복되지 않는 유일한 값을 검색합니다. 하나의 데이터만 읽어오기 때문에 비용이 가장 적게 듭니다. 인덱스 루스 스캔은 인덱스 레인지 스캔과 비슷하나 필요없는 키 값을 건너뛰고 검색합니다. 인덱스 병합 스캔은 두 개 이상의 인덱스를 병합해서 검색합니다. 각 인덱스를 병합하기 때문에 전체적인 속도가 느릴 수 있습니다. 그러나 각각의 인덱스를 사용하는 것보다 효율적일 때 사용합니다.

모범 예상 답변

펼치기

B Tree 기준으로 인덱스 레인지 스캔과 인덱스 풀 스캔에 대해 설명해주세요. 인덱스 레인지 스캔은 검색 해야할 인덱스의 범위가 결정 됐을때 사용하는 방식입니다. 루트 노드, 브랜치 노드를 거쳐 검색 시작점 리프 노드를 찾고, 리프 노드간의 링크를 통해 검색 종료점 리프 노드까지 스캔하면서 레코드를 랜덤 엑세스로 읽어 들입니다. 인덱스 풀 스캔은 인덱스의 처음부터 끝까지 모두 스캔하는 방식입니다. 예를들어 a,b,c 컬럼에 복합 인덱스가 걸려있고 쿼리의 조건절에는 b 또는 c 컬럼이 들어가 있을 경우 인덱스 풀 스캔이 발생합니다. 테이블 풀 스캔보다는 효율적입니다.

12. 쿼리 실행 계획에 대해서 설명해주세요. 실행 계획을 확인해본 적이 있나요?

쿼리 실행 계획은 DBMS가 SQL 쿼리를 처리하기 위해 수립한 실행 계획입니다.

실행 계획 순서?

  1. SELECT STATEMENT

  2. FILTER

  3. NESTED LOOPS

  4. NESTED LOOPS

  5. TABLE ACCESS BY INDEX ROWID

  6. INDEX RANGE SCAN

  7. INDEX UNIQUE SCAN

  8. TABLE ACCESS BY INDEX ROWID

  9. TABLE ACCESS FULL

위에서 아래로 읽어 내려가면서 제일 먼저 읽을 스탭을 찾습니다. 같은 들여 쓰기가 존재하면 위 -> 아래 순으로 읽습니다. 읽고자 하는 스탭보다 더 들여 쓰기가 된 하위 스텝이 있으면 하위 스탭에서 한 단계 씩 상위 스탭으로 읽습니다.

명령어?

  • EXPLAIN PLAN: 실행 계획 생성

  • SET AUTOTRACE: 실행 계획 자동 추적

  • SQL TRACE: 실혱 계획 및 여러 세션의 SQL 통계 정보 기록

모범 예상 답변

펼치기

실행 계획은 옵티마이저가 SQL을 어떻게 실행할지에 대한 계획입니다. 옵티마이저는 SQL을 효율적으로 실행하기 위해, SQL의 비용을 고려하거나 규칙 기반으로 실행계획을 생성하여 SQL을 실행 시킵니다. EXPLAIN 키워드를 통해 사용자가 직접 쿼리에 대한 실행 계획을 확인할 수 있습니다.

13. 힌트에 대해서 설명해주세요.

힌트(Hint)는 SQL 튜닝의 핵심 부분으로 일종의 지시 구문입니다. 사용자는 직접 최적의 실행 구문을 작성해서 옵티마이저가 실행하도록 지정할 수 있습니다.

모범 예상 답변

펼치기

쿼리 힌트(Query Hint)는 데이터베이스 옵티마이저에게 특정 실행 계획을 사용하도록 지시하는 역할을 합니다. 힌트를 사용하여 옵티마이저가 쿼리를 처리하는 방식을 직접 제어할 수 있습니다. 힌트는 주로 성능 최적화나 예상치 못한 실행 계획을 수정하고자 할 때 사용됩니다.

14. 인덱스가 잘 동작하고 있는지 어떻게 확인할 수 있을까요?

EXPLAIN
SELECT * FROM table_name
WHERE column_name = 'condition';

위와 같이 EXPLAIN 명령어를 사용하면 인덱스가 동작하는지 확인할 수 있습니다.

모범 예상 답변

펼치기

쿼리에 대한 실행 계획을 보면서 인덱스가 어떻게 걸리는지 확인하고 실제로 쿼리를 날려봐서 조회 속도를 측정해 봅니다. 또는 select * from sys.schema_unused_indexes 쿼리를 날려서 사용되지 않는 인덱스가 있는지 확인합니다.

15. 인덱스 사용시 주의해야 할 점에 대해서 알려주세요.

인덱스로 사용된 컬럼 값 그대로 사용해야만 인덱스가 사용됩니다. 아래와 같은 상황에서는 인덱스가 사용되지 않습니다.

  1. 전체 테이블 값을 반환하는 경우

  2. OR 절을 사용하는 경우: OR 절을 사용하므로 전체 테이블을 동시에 스캔

  3. NULL 값을 사용하는 경우: 인덱스에 NULL 값이 저장되지 않음

  4. 함수나 연산자를 사용하는 경우: 인덱스와 함수&연산자의 동작 방식이 다름

  5. 컬럼의 자료형이 다른 검색: 인덱스 스캔 전 자료형을 변환하므로 정렬 순서가 변경됨

  6. LIKE 문 검색에서 와일드카드 위치: %가 앞이나 중간에 있으면 정렬을 활용 못함

  7. IN 연산자를 사용한 검색에서 IN 목록의 개수가 많은 경우: N개의 인덱스 수행보다 풀스캔이 효율적

    • JOIN을 사용해서 성능 개선 가능

모범 예상 답변

펼치기

인덱스 키 값이 너무 길어지게 하지 않습니다. 인덱스 키 값이 길어지면 인덱스 정보들을 담고 있는 페이지 개수가 많아집니다. 이는 디스크로부터 페이지를 읽어야 하는 횟수가 늘어나는 걸 뜻하고, 랜덤 엑세스 횟수가 증가하는 것을 뜻하기 때문에 조회 속도가 느려지는 결과를 가져올 수 있습니다.

16. GROUP BY 사용시 인덱스가 걸리는 조건에 대해 설명해주세요.

GROUP BY 절에서 인덱스를 걸기 위해선 인덱스 컬럼의 순서와 위치가 같아야 합니다. 인덱스를에서 뒤에 있는 컬럼은 GROUP BY 절에 명시되지 않아도 인덱스를 사용할 수 있습니다. 그러나 앞에 있는 컬럼이 GROUP BY 절에 명시되지 않으면 인덱스를 사용할 수 없습니다. GROUP BY 절에 명시된 컬럼이 하나라도 인덱스에 없으면 GROUP BY 절은 인덱스를 사용하지 못합니다.

ORDER BY?

ORDER BY 구문에 인덱스를 거는 건 GROUP BY와 비슷합니다. 그러나 ORDER BY는 각 컬럼의 ASC/DESC 옵션이 인덱스와 같거나 정반대인 경우 사용할 수 있습니다.

모범 예상 답변

펼치기

GROUP BY 절에 명시된 컬럼이 인덱스 컬럼의 순서와 같아야합니다. 예를들어 복합 인덱스 (a,b,c)는 group by (a,b,c) 했을때 걸립니다. 또는 group by (a) 또는 group by (a,b) 했을때 걸립니다.

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

카디널리티를 보면 성별 > 국가 > 이름 순으로 컬럼의 중복 정도가 낮습니다. 중복도가 낮은 컬럼이 인덱스의 효과를 볼 수 있으므로 이름으로 인덱스를 거는 게 좋습니다. 동명이인을 방지하고 싶다면 이름과 국가 복합 인덱스를 설정할 수 있습니다.

참고 자료

Previous2주차 - SQLNext4주차 - 이상현상, 정규화

Last updated