DB ) 인덱스

 

디스크 읽기 방식

DB 의 성능 튜닝은 어떻게 디스크 I/O 를 줄이느냐가 관건

SSD

기존 하드 디스크 드라이브에서 데이터 저장용 플래터(원판)을 제거하고 플래시 메모리를 장착.

• 디스크 원한 회전 불필요
  • 아주 빨리 데이터를 읽음
• 플래시 메모리는
  • 전원 공급 안 돼도 데이터 삭제 X
  • 컴퓨터 메모리(D-Ram) 보다는 느리지만 기계식 하드 디스크 드라이브보다는 훨씬 빠르다!

장점

기존 하드 디스크 드라이브와 순차 I/O 는 거의 비슷하지만, 랜덤 I/O 가 훨씬 빠르다.

랜덤 I/O 와 순차 I/O

랜덤, 순차 I/O 모두 파일에 쓰기를 실행하면 반드시 동기화가 필요한데, 순차I/O 의 경우에도 파일 동기화 작업이 빈번히 발생하면 랜덤 I/O와 같이 비효율적으로 처리될 때가 있다.

랜덤 I/O

하드 디스크 드라이브의 플래터(원판)을 돌려서 읽어야 할 데이터가 저장된 위치로 디스크 헤더를 이동시킨 다음 데이터를 읽는 것.

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

그래서 쿼리 튜닝이란?

랜덤 I/O 자체를 줄여주는 것

랜덤 I/O를 줄인다는 건?

쿼리를 처리하는데 꼭 필요한 데이터만 읽도록 쿼리를 개선하는 것.

인덱스

컬럼(들)값과 해당 레코드가 저장된 주소를 키와 값의 쌍으로 삼아 만든 것. 컬럼 값을 주어진 순서로 미리 정렬해서 보관한다. → DB 쿼리 성능을 언급하며 빼놓을 수 없는 부분, 쿼리 튜닝의 기본 !

 

	특징	단점	장점
SortedList (인덱스)	저장되는 값을 항상 정렬된 상태로 유지	저장 과정이 복잡하고 느리다. (INSERT, UPDATE, DELETE)	이미 정렬되어 있어 원하는 값을 빨리 찾을 수 있다.(SELECT)
ArrayList (데이터 파일)	저장한 순서 그대로 유지

 

 

 

✅ DBMS 에서 인덱스는 데이터의 저장 성능을 희생하고 데이터 읽기 속도를 높이는 기능이다.

 

 

 

 

B-Tree (Balanced Tree)

가장 일반적으로 사용하는 인덱스 알고리즘. 컬럼 값을 변형하지 않고 원래의 값을 이용해 항상 정렬된 상태로 유지한다.

구조

트리 구조를 띈다.

• 최상위 : 루트 노드
• 중간 : 브랜치 노드
• 최하위 : 리프 노드
• DBMS는 레코드가 삭제되어 빈 공간이 생겨도 그 다음 insert 는 가능한 삭제된 공간을 재활용하도록 설계되어 있다.
  • 항상 insert 된 순서로 저장되는 건 아니다.
• 테이블의 키 컬럼만 가지고 있다.
  • 나머지 컬럼을 읽으려면 데이터 파일에서 해당 레코드(위치)를 찾아야한다.

추가

1. 저장될 키 값을 이용해 B-Tree 상의 적절한 위치 검색
2. 레코드 키 값과 대상 레코드의 주소 정보를 리프 노드에 저장.
3. 리프 노드가 꽉 차서 더 저장할 수 없다면 리프 노드를 분리
   1. 상위 브랜치 노드까지 처리 범위가 넓어진다.

→ 상대적으로 쓰기 작업에 비용이 많이 든다.

삭제

해당 키 값이 저장된 B-Tree의 리프 노드를 찾아서 삭제 마크만 하면 끝. 삭제 마킹된 키 공간은 계속 그대로 방치하거나 재사용 가능.

변경

먼저 키 값을 삭제한 후, 다시 새로운 키 값을 추가하는 형태

검색

빠른 검색을 위해서 인덱스를 사용하는 것 아니겠는가!

트리 탐색

B-Tree 의 루트 노드부터 시작해 브랜치 노드를 거쳐 최종 리프 노드까지 이동하며 비교 작업 수행

주의

• 인덱스 키 값에 변형이 가해진 후 비교하면 절대 B-Tree의 빠른 검색 기능을 사용할 수 없다.
• 함수나 연산을 수행한 결과로 정렬하거나 검색하면 B-Tree의 장점을 사용할 수 없다.

인덱스 사용에 영향을 미치는 요소

키 값의 크기

인덱스는 *페이지 단위로 관리된다. *페이지: 디스크에 데이터를 저장하는 가장 기본 단위, 디스크의 모든 읽기 및 쓰기 작업의 최소 작업 단위

DBMS 의 B-Tree 는 자식 노드의 개수가 가변적인 구조. → 자식 노드는 인덱스의 페이지 크기와 키 값의 크기에 따라 결정된다.

• 인덱스를 구성하는 키 값의 크기가 커지면 디스크로부터 읽어야 하는 횟수가 늘어난다.
  • 그만큼 느려진다.
• 키 값의 길이가 길어지면 전체적인 인덱스 크기가 커진다.
  • 자연히 메모리 효율이 떨어진다.

B-Tree 깊이(Depth)

중요하지만, 직접 제어할 방법은 없다. 예를 들어, 인덱스 키 값의 크기가 커지면 커질수록 하나의 인덱스 페이지가 담을 수 있는 인덱스 키 값의 개수가 적어지고 그러면 같은 레코드 건수라도 B-Tree의 깊이가 깊어져 디스크 읽기가 더 많이 필요하다.

선택도(기수성)

모든 인덱스 키 값 가운데 유니크한 값의 수, 인덱스나 쿼리 효율성에 큰 영향을 미친다.

 

 

 

✅ 선택도가 좋지 않다고 정렬이나 그루핑 같은 작업을 위해 인덱스를 만드는 것이 훨씬 나은 경우도 많다. 인덱스가 항상 검색에만 사용되는 것은 아니니 여러가지 용도를 고려해 인덱스를 설계하자!

 

 

 

 

읽어야하는 레코드의 건수

인덱스를 통해 테이블의 레코드를 읽는 건 인덱스를 거치지 않고 바로 테이블의 레코드를 읽는 것보다 높은 비용이 드는 작업이다.

인덱스를 통해 레코드 1건을 읽는 것이 테이블에서 직접 레코드 1건을 읽는 것보다 4~5배 정도 비용이 많이 든다.

• 읽어야 할 레코드가 전체 테이블 레코드의 20~25%를 넘으면
  • 테이블을 모두 직접 읽어서 필요한 레코드를 가려내는(필터링) 방식으로 처리하는 게 효율적이다.

B-Tree 인덱스를 통한 데이터 읽기

인덱스 레인지 스캔

인덱스 접근 방법 가운데 가장 대표적인 방법.

검색해야 할 인덱스의 범위가 결정됐을 때 사용하는 방식이며, 나머지 접근 방식에 비해 빠르다.

 

인덱스 리프 노드에서 검색 조건에 일치하는 건들은 데이터 파일에서 레코드를 읽어오는 과정이 필요하다. 그러면 레코드 한 건 한 건 단위로 랜덤 I/O가 한 번씩 일어난다. → 그래서 인덱스를 통해 데이터 레코드를 읽는 작업은 비용이 많이 든다.

인덱스 레인지 스캔의 단계

1. 인덱스에서 조건을 만족하는 값이 저장된 위치를 찾는다. 인덱스 탐색 Index Seek
2. 1번에서 탐색된 위치부터 필요한 만큼 인덱스를 차례대로 쭉 읽는다. 인덱스 스캔 Index Scan
3. 2번에서 읽어 들인 인덱스 키와 레코드 주소를 이용해 레코드가 저장된 페이지를 가져오고, 최종 레코드를 읽어온다.

커버링 인덱스

위의 단계에서 3번 과정이 빠진 것. 디스크의 레코드를 읽지 않아도 되어 랜덤 읽기가 상당히 줄어들고 성능은 그만큼 빨라진다.

인덱스 풀 스캔

인덱스의 처음부터 끝까지 모두 읽는 방식.

• 쿼리의 조건절에 사용된 컬럼이 인덱스의 첫 번째 컬럼이 아닌 경우 사용
• 쿼리가 인덱스에 명시된 컬럼만으로 조건을 처리할 수 있는 경우 사용
  • 데이터 레코드까지 모두 읽어야 한다면 절대 이 방식으로 처리하지 않는다.

단계

1. 인덱스 리프 노드의 제일 앞 또는 뒤로 이동
2. 인덱스의 리프 노드를 연결하는 링크드 리스트를 따라서 처음부터 끝까지 스캔

 

 

 

 ✅ 일반적으로 인덱스를 사용한다는 의미는 ”인덱스 레인지 스캔” 이나 “인덱스 스킵 스캔” 방식으로 사용한다는 의미다. → 인덱스 풀 스캔은 효율적인 방식이 아니다…😭

→ 인덱스 루스 스캔은 MySQL 에만 있는 기능이니 나중에 소개…

인덱스 스킵 스캔

핵심은 값이 정렬돼 있다는 것. 인덱스를 구성하는 컬럼의 순서는 매우 중요하다.

방식

1. 컬럼에서 유니크한 값을 모두 조회
2. 주어진 쿼리에 해당 컬럼의 조건을 추가
3. 쿼리를 다시 실행

예시

# 인덱스 추가
ADD INDEX IX_GENDER_BIRTH (GENDER, BIRTH_DATE); 

# 인덱스 스킵 스캔으로 읽기
SELECT GENDER, BIRTH_DATE
FROM EMPLOYEES
WHERE BIRTH_DATE >= '1925-02-01';

# 실제 쿼리 실행 (인덱스 유니크 값 조회해서 조건 추가 후 쿼리 다시 실행)
mysql> SELECT GENDER, BIRTH_DATE FROM EMPLOYEES WHERE GENDER = 'M' AND BIRTH_DATE >= '1925-02-01';
mysql> SELECT GENDER, BIRTH_DATE FROM EMPLOYEES WHERE GENDER = 'F' AND BIRTH_DATE >= '1925-02-01';

단점

• WHERE 조건절에 조건이 없는 인덱스의 선행 컬럼의 유니크한 값의 개수가 적어야 함.
• 쿼리가 인덱스에 존재하는 컬럼만으로 처리 가능해야 함.

 

 

 

✅ 인덱스 스킵 스캔은 인덱스의 선행 컬럼이 가진 유니크 값의 개수가 소량일 때만 적용 가능한 최적화

 

 

 

 

다중 컬럼 인덱스

두 개 이상의 컬럼으로 구성된 인덱스. 복합 컬럼 인덱스나 Cancatnated Index 라고도 한다.

• 인덱스의 두 번째 컬럼은 첫 번째 컬럼에 의존해서 정렬되어 있다.
• 즉, 두 번째 컬럼의 정렬은 첫 번째 컬럼이 똑같은 레코드에서만 의미가 있다.

→ 인덱스 내에서 각 컬럼의 위치(순서)가 상당히 중요하다.

인덱스 정렬 및 스캔 방향

쿼리가 인덱스를 사용하는 시점에 인덱스를 읽는 방향에 따라 오름차순 또는 내림차순 정렬 효과를 얻을 수 있다.

스캔 방향

• ASC : 오름차순
  • 작은 값 → 큰 값
• DESC : 내림차순
  • 큰 값 → 작은 값
• Forward index scan : 인덱스 정순 스캔
  • 왼쪽 → 오른쪽
• Backward index scan : 인덱스 역순 스캔
  • 왼쪽 ← 오른쪽

→ 역순 정렬 쿼리가 정순 정렬 쿼리보다 28.9% 더 시간이 걸린다.

정순 정렬 쿼리가 더 빠른 이유

• 페이지 잠금이 인덱스 정순 스캔에 적합한 구조
• 페이지 내에서 인덱스 레코드가 단방향으로 연결된 구조

결론

1. 내림차순 인덱스가 더 효율적이다.
2. 쿼리에서 자주 사용되는 정렬 순서대로 인덱스를 생성하자.
   1. 잠금 병목 현상을 완화한다.

인덱스 가용성 및 효율성

어떤 조건에서 인덱스를 사용할 수 있고 어떨 때 사용할 수 없을까? 인덱스를 100% 활용할 수 있을까, 일부만 이용하게 될까?

비교 조건의 종류와 효율성

조건절이 동등 비교(”=”) 인지 아니면 크다 (”>”) 작다 (”<”) 같은 범위 조건인지에 따라 활용이 다르다.

예시

# case A
SELECT * FROM DEPT_EMP
WHERE DEPT_NO = 'D002' AND EMP_NO >= 10114 ;

# case B
SELECT * FROM DEPT_EMP
WHERE EMP_NO >= 10114 AND DEPT_NO = 'D002' ;

• 케이스 A : index (DEPT_NO, EMP_NO)
  1. “DEPT_NO = 'D002' AND EMP_NO >= 10114” 인 레코드를 찾고
  2. DEPT_NO 가 ‘D002’ 가 아닐 때까지 인덱스를 쭉 읽기만 하면 된다.
  → 조건을 만족하는 레코드가 5건이라면, 꼭 필요한 5번의 비교 작업만 수행한다.
• 케이스 B : index (EMP_NO, DEPT_NO)
  1. “EMP_NO >= 10114 AND DEPT_NO = 'D002'”인 레코드를 찾고
  2. 그 이후 모든 레코드에 대해 DEPT_NO 가 D002 인지 비교하는 과정을 거쳐야 한다.
  → 필요없는 작업이 굉장히 많이 이뤄질 수 있다.

필터링

위처럼 인덱스를 통해 읽은 레코드가 나머지 조건에 맞는지 비교하며 취사선택하는 작업

작업 범위 결정 조건

작업의 범위를 결정하는 조건 → 케이스 A 의 두 조건 “DEPT_NO = 'D002'” 와 “EMP_NO >= 10114”

체크 조건 (필터링 조건)

비교 작업의 범위를 줄이지 못하고 단순히 거름종이 역할만 하는 조건 → 케이스 B 의 DEPT_NO = ‘D002’

결론

• 작업 범위를 결정하는 조건은 많으면 많을수록 쿼리의 처리 성능을 높인다.
• 체크 조건은 많다고 해서 쿼리의 처리 성능을 높이지는 못한다.
  • 최종적으로 가져오는 레코드만 적을 수 있다.
  • 오히려 성능을 더 느리게 만들 때가 많다.

인덱스 가용성

왼쪽 값에 기준해서 오른쪽 값이 정렬되어 있다.

• 하나의 컬럼이어도 값의 왼쪽 부분이 없으면
• 다중 컬럼 인덱스에서도 왼쪽 컬럼의 값을 모르면
• 왼쪽 값 규칙은 GROUP BY, ORDER BY 에서도 똑같이 적용됨

→ 인덱스 레인지 스캔 사용 불가

가용성과 효율성 판단

작업 범위 결정 조건으로 사용할 수 없는 조건

1. Not equal 비교
2. LIKE ‘%??’ 뒷부분 일치 형태로 문자열 패턴 비교
3. 인덱스 컬럼이 변형된 후 비교된 경우
4. 스토어드 함수가 비교 조건에 사용된 경우
5. 인덱스 컬럼의 타입을 변환해야 비교가 가능한 경우
6. 문자열 데이터 타입의 콜레이션이 다른 경우

→ 일반 RDBMS 에는 NULL 값이 인덱스에 저장되지 않으나, MySQL 에는 NULL 도 인덱스에 저장된다.

클러스터링 인덱스

테이블의 레코드를 프라이머리 키를 기준으로 묶어서 저장되는 형태(테이블 레코드 저장 방식)

 

장점	PK 기반 검색이 매우 빠르다
단점	레코드 저장이나 PK 변경이 상대적으로 느리다.

 

 

✅ 테이블 레코드가 PK 값으로 정렬되어 저장되면 “클러스터링 인덱스” 또는 “클러스터링 테이블” 이라고 한다.

 

 

 

클러스터링 인덱스의 리프 노드에는 레코드의 모든 컬럼이 같이 저장되어 있다. → 클러스터링 테이블은 그 자체가 하나의 거대한 인덱스 구조로 관리된다.

PK 는 꼭 생성하자

어차피 PK 생성 안 해도 DB 가 알아서 PK 대체 컬럼을 임의로 만들고 클러스터링 키로 만든다. 이건 쿼리로 사용할 수도 없고… 그러니까 그냥 써먹을 수 있게 PK 를 생성하자.

세컨더리 인덱스에 미치는 영향

클러스터링 테이블의 모든 세컨더리 인덱스는 해당 레코드가 저장된 주소가 아니라 PK 값을 저장하도록 구현

장점

1. PK(클러스터링 키)로 검색할 때 처리 성능이 매우 빠르다.(PK 범위검색 굳)
2. 테이블의 모든 세컨더리 인덱스가 PK를 가지고 있어 인덱스만으로 처리되는 경우가 많음(커버링 인덱스)

단점

1. 테이블의 모든 세컨더리 인덱스가 클러스터링 키를 가져, 키 값의 크기가 크면 전체적인 인덱스 크기가 증가
2. 세컨더리 인덱스를 통해 검색하면 PK로 다시 한 번 검색해야 하므로 처리 성능 느림
3. INSERT 할 때 PK에 의해 레코드 저장 위치가 결정되어 처리 속도가 느림
4. PK를 변경할 때 레코드를 DELETE 하고 INSERT 하는 작업이 필요, 처리 성능 느림

결론

장점은 빠른 읽기, 단점은 느린 쓰기 웹 서비스는 조금 느린 쓰기를 감수하고 읽기를 빠르게 유지하는 것이 중요하니, 클러스터링 키 설정은 중요하다.

주의 사항

클러스터링 인덱스 키의 크기

클러스터링 테이블은 모든 세컨더리 인덱스가 PK(클러스터링 키) 값을 포함한다. PK 크기가 커지면 세컨더리 인덱스도 자동으로 크기가 커진다.

AUTO-INCREMENT 보다는 업무적인 컬럼으로 생성하자

PK로 검색하는 경우 클러스터링 되지 않은 테이블에 비해 매우 빠르게 처리된다.

PK 는 반드시 명시하자

어차피 안 만들어도 니가 못써먹을 임의 번호가 생성된다. 그럴 거면 명시해서 써먹자.

AUTO-INCREMENT 컬럼을 인조 식별자로 사용할 경우

PK 크기가 길어도 세컨더리 인덱스가 필요치 않다면 그대로 PK를 사용하는 것이 좋다. PK를 대체하기 위해 인위적으로 추가된 PK를 인조 식별자라고 부른다. → 조회보다 INSERT 위주의 테이블은 AUTO-INCREMENT 를 이용한 인조 식별자를 PK로 설정하면 성능 향상

유니크 인덱스

테이블이나 인덱스에 같은 값이 두 개 이상 저장될 수 없음. 제약 조건에 가깝다.

읽기

 

컬럼 읽기	하드디스크
컬럼 비교	CPU

유니크하지 않은 세컨더리 인덱스에서는 CPU 에서 컬럼 비교 작업 중복된 값이 허용되므로 읽어야 할 레코드가 많아서 느린 것이지, 인덱스 자체 특성 때문에 느린 게 아님 → 유니크 인덱스가 여부가 성능상 거의 영향 없음

쓰기

유니크하지 않은 세컨더리 인덱스 쓰기보다 느리다. → 중복된 값이 있는지 없는지 체크하는 과정이 한 단계 더 필요하다.

주의사항

쿼리 실행 계획이나 테이블 파티션에 미치는 영향이 있음.

결론

• 유일성이 꼭 보장되어야 하는 컬럼은 유니크 인덱스
• 꼭 필요하지 않다면 세컨더리 인덱스도 고려

외래 키

외래 키 제약이 설정되면 연관되는 테이블의 컬럼에 인덱스까지 생성된다.

1. 테이블 변경은 잠금 경함(잠금 대기)
2. 외래 키와 연관되지 않은 컬럼이 변경은 최대한 잠금 경합 발생시키지 않는다.

자식 테이블 변경

• 자식 테이블의 외래 키 컬럼의 변경은 부모 테이블 확인이 필요하다.
• 부모 테이블의 해당 레코드가 쓰기 잠금이 걸려있으면 해당 쓰기 잠금이 해제될 때까지 기다린다.

부모 테이블 변경

• 자식 테이블이 생성될 때 정의된 외래키의 특성 (ON DELETE CASCADE)이 있다.
  • 부모 레코드가 삭제되면 자식 레코드도 삭제됨

→ 잠금이 다른 테이블로 확장되면 그만큼 전체적으로 쿼리 동시 처리에 영향을 미친다.