[DB] 정규화와 트랜잭션, 인덱스

서론

※ 아래 내용을 다룹니다.

• 용어
• 정규화
• 트랜잭션
• 인덱스

https://github.com/SangBeom-Hahn/boost-interview

GitHub - SangBeom-Hahn/boost-interview: Naver BoostCamp 6기, AI 엔지니어 기반을 다지는 공간 🎇

 

본론

- DB란?

일정한 규칙을 통해 구조화되어 저장되는 데이터의 모음

 

※ 용어

1. DBMS

DB를 관리하는 시스템으로 DB를 설치하면 자동으로 설치된다. 사용자 입장에선 DB의 데이터를 다루기 위한 인터페이스라고 볼 수 있으며, DBMS마다 정의된 쿼리 언어를 통해 CRUD를 수행할 수 있다.

응용 프로그램을 사용한다면, 응용 프로그램에서 DBMS를 통해 DB 안에 있는 데이터를 사용한다.

 

2. 엔터티

정의 : 여러 개의 속성을 지닌 명사
예시 : 회원 엔터티는 (이름, 아이디, 주소) 속성을 가진다.

혼자서는 존재하지 못하고 B의 존재 여부에 따라 종속적인 엔터티를 약한 엔터티라고 부르고, 혼자서도 존재할 수 있는 엔터티를 강한 엔터티라고 한다.

 

3. 릴레이션

정의 : 정보를 구분하여 저장하는 기본 단위
예시 : 회원 엔터티가 DB에 관리되면 회원 릴레이션, 회원 테이블이라고 한다.

 

4. 도메인

정의 : 속성이 가질 수 있는 값의 집합
예시 : 성별 속성의 [남, 여]

 

5. 필드

테이블의 속성을 의미한다.

 

-> 데이터 타입

1. 숫자 타입

저장 시, 바이트 수만큼의 용량을 차지한다.

 

2. 문자 타입

1) CHAR : 0 ~ 255 사이값을 지정할 수 있는 고정 길이 문자열로 CHAR(30)은 최대 30글자까지 입력할 수 있으며, n에 최대 몇 자를 명시한다. ( ex) 10글자를 입력해도 100글자로 선언하면 100 바이트로 저장 )

2) VARCHAR(n) : 0 ~ 65535 사이값을 지정할 수 있는 가변 길이 문자열로 n을 10000으로 선언해도 입력된 데이터에 따라 용량을 가변시켜 저장한다. ( ex) 10글자를 입력 받으면 10글자 용량만 사용 ) 유동적인 길이의 데이터를 저장할 때 사용하는 것이 좋다.

3) LOB : 대량의 데이터를 저장하는 타입으로 약 4GB의 파일을 하나의 데이터로 저장 가능하다.

 

4) ENUM : 테이블을 만들 때 정의한 속성 요소만을 다룰 수 있도록 집합화하는 타입

[참고] 블로그를 보시면 요일을 집합화하는 것처럼

 

테이블을 만들 때 속성의 요소 집합을 정의하고 요소에 있는 값만 삽입 가능하며, 없는 값은 null이 아닌 빈 문자열 ("") 공백으로 저장된다.

 

이처럼 삽입할 시

 

2 : 숫자를 입력하면 인덱스 접근하여 2번 인덱스인 1을 삽입
'2' : 요소 리스트에 허용된 값일 경우
'3' : 요소 리스트에 없는 값을 삽입 시 공백
'4' : 요소 리스트에 없는 값을 삽입 시 공백
Null : Null 이 그대로 삽입

 

5) SET : 여러 개의 데이터를 집합화하여 저장할 수 있는 데이터 타입

SET 타입으로 season 속성을 만들고

 

위처럼 저장한 결과는 여러가지 부분 집합 요소를 저장할 수 있다.(하나의 행 X) 그 외 비트 단위 연산, 최대 64 개의 요소 삽입 등 특징을 가진다.

 

3. 날짜 타입

바이트 수는 용량을 나타내고 보통 DATETIME을 사용한다.

 

- 관계

1. 1 대 1

1개의 테이블을 2개로 나눠, 테이블 구조를 더 이해하기 쉽게 만들어준다.

 

2. 1 대 다

하나의 유저가 여러 개의 상품을 가진다. 처럼 한 개체가 다른 많은 개체를 포함하는 관계이다.

 

3. 다 대 다

학생과 강의에서 1명의 학생도 여러개의 강의를 듣고, 1개의 강의도 여러 명의 학생이 듣는 관계로, 두 개를 직접 연결하지 않고 매핑 테이블을 둔다.

 

- 키

1) 유일성 : 중복되는 값 불가
2) 최소성 : 최소 필드만 써서 키를 형성할 수 있음

테이블 자체의 인덱스를 위해 설정된 장치로 위와 같은 특성을 가진다. 위 예시에서 학생이 여러개의 동아리에 가입할 수 있어서 sid만 사용하면 유일성을 만족할 수 없고 동아리를 함께 사용해야 유일성과 최소성을 만족하며 동아리장까지 사용하면 유일성은 만족하나 최소성을 만족하지 않음

 

✅ 종류

기본키 : 유일성과 최소성을 만족하는 테이블의 데이터 중 고유하게 존재하는 속성
자연키 : 주민등록번호, 이름 등의 속성
인조키 : UUID처럼 인위적으로 부여한 속성
외래키 : 1대(1..n) 관계에서 다른 테이블의 기본키를 그대로 참조한 값
후보키 : 기본키가 될 수 있는 후보군 전체 (선정된 기본 키 포함)
대체키 : 선정된 기본키 외 후보키
슈퍼키 : 각 레코드를 유일하게 식별할 수 있는 유일성을 갖춘 키

 

- 정규화

잘못된 ERD 설계를 해결하기 위해 여러 개로 분리하는 과정을 의미한다.

 

-> 제 1 정규형

속성 데이터는 단일 값을 가져야함을 의미한다. 이 경우 열이 아닌 행으로 늘려야 한다.

 

-> 제 2 정규형

쉽게 말해 분리할 수 있는 건 분리하라는 것으로 종속 관계를 제거해야 함을 의미한다.

 

학생 테이블에 학과 코드 학과명, 학과장 같은 분리해도 되어 보이는 속성이 보이고 이는 학과코드를 기준으로 분리 할 수 있다.

 

이렇게 학생 테이블과 학과 테이블을 별도로 만들고 학생이 학과코드를 참조하여 사용하도록 종속된 속성을 제거하는 것을 의미한다.

 

-> 제 3 정규형

종속되는 속성이 기본키가 아닐 때 종속하는 속성을 제거하는 것을 의미한다. 위 예제에서 할인률 속성이 기본키가 아닌 등급에 종속되어 이를 분리한다.

 

-> 보이스/코드 정규형

결정자 : 함수 종속 관계에서 X -> Y일 때 X가 결정자, Y가 종속자

결정자가 후보키가 아닌 경우는 전부 제거하는 것을 의미한다.

 

현재 [학번, 수강명], [학번, 강사]가 후보키이고 [강사 -> 수강명]라는 함수 종속 관계에서, 강사가 결정자지만 후보키가 아니라서 강사와 수강명을 삽입하면 학번이 NULL이 되는 문제가 발생할 수 있다.

 

이 경우 후보키가 아닌 함수 종속 관계를 제거하여 강사 속성을 분리해야 한다.

 

- 트랜잭션

하나의 기능을 묶는 작업 단위로, DB에서 작업은 SQL 쿼리이므로, 하나의 기능에 해당하는 1개 이상의 쿼리를 묶는 단위이다.

 

1. 원자성

하나의 기능이 성공하면 수행하고 실패하면 수행하지 않도록 보장하는 특징으로, 성공하면 커밋하고, 실패하면 롤백해서 All or Nothing을 만족하도록 한다.

 

커밋 : 하나의 기능이 성공하면, 모든 데이터의 변경을 영구 저장한다.
롤백 : 실패하면, 트랜잭션으로 처리한 하나의 묶음이 일어나기 전으로 모든 데이터를 복구한다.
트랜잭션 전파 : 여러 트랜잭션 메서드의 호출을 하나의 트랜잭션에 묶이도록 하는 것으로, 매 트랜잭션을 수행할 때마다 커넥션 객체를 생성해야 하는데, 이를 하나의 트랜잭션 내에서 호출하는 방식으로, 여러 트랜잭션을 관리할 수 있다.

커밋과 롤백 덕분에 데이터의 무결성이 보장된다.

 

2. 일관성

불가능한 데이터 변경은 발생하면 안된다는 특징으로, 예를들어 0원이 있을 때 500원을 출금할 수는 없다.

 

3. 격리성

어떤 트랜잭션이 동작하고 있을 때 다른 트랜잭션이 끼어들 수 없는 특징으로, 동시성 트랜잭션이 병렬적으로 동작하나, 순차적으로 동작하는 것처럼 만드는 특징이다. 위로 올라갈 수록 동시성이 커지고, 격리성이 낮아진다.

 

더티리드 : 커밋되지 않은 데이터 접근 가능
비반복 읽기 : 특정 행을 읽는 동안, 다른 트랜잭션이 수정 가능하여, 동일 행을 반복해서 읽었을 때, 데이터가 변경되는 현상
팬텀리드 : Range-Query를 하는 동안, 다른 트랜잭션이 범위 해당하는 데이터를 추가, 삭제하여 다시 Range-Query를 했을 때 데이터에 추가, 삭제가 있는 현상

 

1) READ_UNCOMMITTED

트랜잭션 1이 특정 행을 수정하는 동안, 트랜잭션 2가 커밋 전에 수정된 해당 행에 접근할 수 있다.

따라서, 유일하게 더티리드가 발생하는 격리 수준이고, 나머지 동시성 부작용도 발생한다. 데이터의 안전을 보장하지 않아, 사용하지 않아야 하지만, 가장 빠르므로 정확하지 않아도 되는 대규모 데이터의 집계 같은 상황에서 사용하면 좋다.

 

2) READ_COMMITTED

커밋된 데이터에만 접근을 보장하는 격리 수준으로, 더티리드를 해결한다. 나머지 2개의 동시성 부작용은 여전히 발생한다. 

 

트랜잭션 1 : 1행 수정 (쓰기 락 획득)
트랜잭션 2 : 1행 수정 불가 (쓰기 락 대기)
트랜잭션 2 : 1행 읽기 시 데이터 일치

수정이나 삭제 시에는 쓰기 락을 통해 비반복 읽기가 발생하지 않지만,

 

트랜잭션 1 : 1행 읽기
트랜잭션 2 : 1행 수정 (쓰기 락 획득)
트랜잭션 2 : 1행 읽기 시 데이터 불일치

읽기 시에는 다른 트랜잭션이 해당 행을 수정할 수 있어 비반복 읽기가 발생한다.

 

3) REPEATABLE_READ

트랜잭션 1 : 1행 읽기 (읽기 락 획득)
트랜잭션 2 : 1행 수정 불가 (쓰기 락 대기)
트랜잭션 2 : 1행 읽기 시 데이터 일치

하나의 트랜잭션의 읽기 락, 쓰기 락으로 다른 트랜잭션의 동시 접근을 전혀 허용하지 않는다. innoDB의 기본 격리성이다. 하지만 추가와 삭제가 가능하여 Range-Query 시 데이터 변경이 발생하여 팬텀 리드가 발생한다.

 

4) SERIALIZABLE

여러 트랜잭션을 순차적으로 수행하여, 가장 성능이 낮다.

 

4. 지속성

한 번 저장된 데이터(성공적으로 수행한 트랜잭션)는 영원히 반영되어야 하며, 문제 발생시 회복할 수 있는 기능을 제공해야 한다.

 

- 인덱스

인덱스란, 배열의 인덱스와 책갈피의 인덱스처럼 실제 데이터에 빠르게 접근하기 위해 사용한다. 인덱스의 특징은 정렬되어 있다는 것으로, 인덱스를 사용하지 않으면 풀 스캔을 해야하는데, 이처럼 원하는 데이터 대신에 정렬된 인덱스에 빠르게 접근하여 실제 데이터에 접근하기 위한 목적이다.

 

30만행의 데이터가 기준 없이 흩어진 상태에서 성이 토마스인 사람을 찾으려 했을 때 0.091초가 걸린 건 엄청 오래걸린 것으로 만약 데이터가 특정 기준으로 정렬되어 있다면 검색을 빠르게 할 수 있을 것이다.

 

이를 빨리 찾으려면 인덱스를 성으로 정하면 되고, 성으로 정렬을 하고 DB 어디에 있는지 인덱스를 DB 내부적으로 생성하고 가지게 된다.

 

인덱스를 사용하려면 WHERE 절을 반드시 사용해야 하며, 인덱스를 설정하더라도 WHERE을 쓰지 않으면 인덱스를 사용하지 않는다.

 

✅ 인덱스의 특징

1) 항상 최신의 정렬 상태를 유지한다.
2) 인덱스도 DB의 객체로 DB 크기의 약 10% 정도의 저장 공간을 요구한다.

데이터가 수정되면 인덱스도 수정되어야 하고 이때 B트리 높이를 균형있게 조절하는 비용과 효과적 검색을 위해 데이터를 분산하는 비용이 발생한다.

 

- 인덱스 알고리즘

1. 풀 테이블 스캔

순차적으로 처음부터 찾게 되며, 풀 스캔에서 PPP을 찾으려면 총 3개의 페이지에서 12번의 검색을 해야한다.

 

1) 비용은 적지만, 성능도 낮다.
2) 순차적 탐색

정렬을 하지 않고 저장된 데이터 그대로 검색을 하므로 접근 비용은 감소하지만 검색 성능이 안 좋음 (인덱스 없이 토마스를 검색한 경우 느림)

 

-> 풀 테이블 스캔을 사용하는 경우

1) 적용 가능한 인덱스가 없는 경우
2) 범위 검색으로 다수의 출력을 내는 경우
3) 인덱스를 사용하더라도 성능에 큰 이점이 없을 만큼 테이블이 작은 경우

WHERE 절을 사용한 범위 검색을 할 때 인덱스를 사용하면 성능이 더 안 좋아질 수 있다.

 

인덱스를 사용한 검색 : 인덱스 리스트를 탐색 -> 실제 데이터를 로드

인덱스를 사용한 검색은 위와 같은 절차를 거치기에 인덱스 구조를 탐색하는 과정에서 풀 스캔보다 추가적인 오버헤드가 발생한다. 만약 100만 개의 데이터 중에서 범위 검색으로 10만 개를 찾는다면 인덱스 리스트 탐색으로 해당 범위를 찾아낸 후 10만 개의 데이터를 가져와야 하는 과정에서 인덱스 탐색 및 데이터 로드에서 많은 시간이 소요될 수 있다. 반면, 풀 스캔을 하면 메모리에 적재된 데이터를 순차적으로 읽기만 하면 되어서 오히려 더 효율적일 수 있다.

 

2. B트리 자료구조

B 트리는 균형 있는 이진 탐색 트리의 경우 logn, 균형 없는 이진 탐색 트리의 경우 n의 시간 복잡도를 가지는 문제를 개선한 인덱스 자료구조로, 트리 높이가 같고 자식 노드를 2개 이상 가질 수 있다.

 

-> Select

B 트리는 루트, 브랜치, 리프 페이지로 이루어지며, 루트와 브랜치는 자식 테이블의 정보를 가지고, 리프 페이지는 실제 데이터 페이지이다. 루트 페이지는 최상단, 브랜치 페이지는 루트와 리프의 중간에 위치한다.

 

인덱스로 검색하는 과정을 알아보자. PPP를 찾기 위해서 루트 페이지를 탐색해서, P는 L 다음이니 L이 속한 리프 페이지로 이동 후 PPP를 찾는다. 인덱스를 사용해서 2개의 페이지, 7번의 검색으로 SELECT 성능을 높였다.

 

-> insert

인덱스를 사용할 때 삽입 과정을 알아보자. OOO를 추가할 때 O는 N과 P 사이이므로, P의 이동이 있지만 동일 페이지이므로 아직 큰 부담은 없다.

 

이때, ZZZ를 추가하면 페이지에 공간이 부족하여 DB는 비어있는 페이지를 확보하고 문제가 있는 페이지의 데이터를 공평하게 나누어 저장하는 페이지 분할이 발생한다. 인덱스는 항상 최신 정렬 상태를 유지하며, 데이터의 수정으로 인해 트리의 깊이를 균형있게 유지하고 데이터를 효율적으로 검색하기 위한 페이지 분할 비용이 발생한다.

 

-> delete + update

삭제 : 인덱스를 사용한 삭제는 실제 데이터를 삭제하지 않고 '사용 안함' 표시를 한다.
수정 : 인덱스에 수정 기능은 없고 데이터를 삭제하고 다시 INSERT 한다. (물리적으로 데이터 삭제)

 

인덱스를 사용하면 INSERT에서 페이지 분할 비용이 발생하고, 수정과 삭제에서 사용 안함 때문에 불필요한 처리량이 증가하고, 페이지 낭비로 성능이 저하된다. SELECT만이 성능이 향상된다.

 

- 인덱스의 종류

-> 클러스터란?

무리를 이룸을 의미하고, DB에서는 인덱스와 실제 데이터가 무리를 이룸을 의미하고, 논-클러스터는 인덱스와 실제 데이터가 무리를 이루지 않음을 의미한다.

 

1. 클러스터링 인덱스

클러스터링 인덱스는 사전의 인덱스를 보면 인덱스와 데이터가 함께 존재하고, 데이터도 인덱스에 따라 정렬된 곳에 위치가 정해진다.

 

2. 논 클러스터링 인덱스

논 클러스터링 인덱스는 인덱스와 실제 데이터가 무리를 이루지 않고 떨어져있다.

 

=> 인덱스의 종류

이렇게 테이블을 만들면 pk는 클러스터링, 이메일은 논-클러스터링 인덱스가 자동으로 생성된다.

 

1. 클러스터링 인덱스

기본키를 설정하거나, 하나의 컬럼에 UQ + NOT NULL을 함께 설정하면 클러스터링 인덱스가 생성된다. 클래스터링 인덱스는 테이블 당 하나만 생성할 수 있으며 PK가 있는 상태에서 다른 컬럼에 UQ + NOT NULL 설정을 하면 PK가 우선 순위를 가지고 PK로 인덱스가 생성된다.

 

하지만, UQ + NOT NULL 설정을 한 컬럼이 이미 존재하는 상황에서, 다른 컬럼에 UQ + NOT NULL 설정을 하면 DBMS가 시스템 오류를 발생시킨다.

 

클러스터링 인덱스의 동작 과정을 살펴보자. 인덱스를 생성하면 인덱스를 적용한 컬럼을 기준으로 데이터가 정렬되고, 인덱스와 데이터가 함께 있기 때문에 실제 데이터도 인덱스를 따라, 행 전체가 이동한다.

 

실제 데이터가 리프 페이지라고 했으니 인덱스와 데이터가 함께있는 정렬된 데이터 페이지가 리프 페이지가 되고 루트 페이지와 연결되어 비트리 구조를 가진다. 루트 페이지에는 리프 페이지의 첫 번째 데이터와 페이지 주소를 가지고 있고, WHERE 절로 7을 검색하면 7은 5와 9 사이에 있기에, 인덱스 5의 주소를 가진 리프 페이지로 접근하여 7을 찾는다.

 

✅ 클러스터링 인덱스의 특징

1) 실제 데이터 자체가 정렬
2) 리프 페이지 == 데이터 페이지
3) pk와 UQ+Not Null 제약 조건 시 pk가 우선
4) 테이블 당 1개만 존재

클러스터링 인덱스에서는 여러 개의 속성에 인덱스가 적용되어도 하나의 인덱스만 생긴다.

 

2. 논-클러스터링 인덱스

name 컬럼으로 별도의 인덱스를 만들어보자, Not Null 없이 UQ 제약조건 만으로 논-클러스터링 인덱스를 만들 수 있고, 중복을 허가한 인덱스와 중복을 불허한 인덱스도 만들 수 있다.

 

논-클러스터링 인덱스의 동작 과정을 보면, 리프 페이지가 실제 데이터 페이지가 아닌 별도의 인덱스 페이지가 추가되고 B트리에 연결된다. 도리, 라라 등 name 컬럼으로 생성한 인덱스 페이지가 사전 순으로 정렬되고 실제 데이퍼 페이지는 정렬되지 않는다. 루프 페이지가 리프 페이지의 주소를 가지고 있는 것은 동일하다.

 

별도의 인덱스로 인해, 데이터 페이지는 정렬과 변경이 일어나지 않는다. 검색 과정에서 인덱스 페이지에 실제 데이터 페이지의 주소와 찾으려고 하는 데이터의 행이 있다. 1002 #3은 주소 1002 데이터 페이지의 3번째 행을 의미한다.

 

✅ 특징

1) 실제 데이터 페이지는 변경 X
2) 별도의 인덱스 페이지 생성 = 추가 공간 필요
3) 테이블 당 여러개 존재(데이터 페이지는 변경이 안되어, 별도의 인덱스 페이지를 여러개 만들고 연결만 하면 O)
4) 리프 페이지에 실제 데이터 페이지 주소와 행 번호

클러스터 인덱스는 데이터와 인덱스가 함께 있기에 한 테이블 당 하나만 만들 수 있었지만, 논-클러스터 인덱스는 실제 데이터 페이지는 따로 있고, 별도의 인덱스 페이지를 새로 만들기 때문에, 테이블 당 여러 개 생성이 가능하다.

 

-> 클러스터링과 논 클러스터링 인덱스를 함께 적용

하나의 테이블에 id를 PK로 클러스터 인덱스, name을 논 클러스터 인덱스로 만들면 어떻게 동작이 이루어지는지 알아보자

 

✅ 예상되는 구조

id로 클러스터 인덱스가 생기고 논 클러스터 인덱스의 별도의 인덱스 페이지가 클러스터 인덱스의 실제 데이터 페이지의 주소를 가지고 접근 및 검색

 

실제 구조는 클러스터 인덱스는 동일하게 생성되지만, 논 클러스터 인덱스는 예상과는 다르게, 인덱스 페이지에 실제 데이터 페이지의 주소와 행 번호가 아닌 클러스터 인덱스가 적용된 컬럼(여기서는 ID 컬럼) 값이 존재한다. name 컬럼으로 검색을 하면 논 클러스터 인덱스에서는 클러스터 인덱스의 루트 페이지로 원하는 검색 데이터를(여기서는 ID) 전달하고, 실제 검색은 클러스터 인덱스에서 이루어진다.

 

🚨 예상 구조의 문제점

앞서 예상한 구조는 어떠한 문제점이 있을까?

 

컬러스터링 인덱스 : id가 3인 데이터 추가 -> id 4, 5의 페이지 분할
논 클러스터링 인덱스 : 페이지 분할되는 이름(호호와 스컬)에 해당하는 인덱스 페이지의 실제 페이지 주소 변경O [테이블 주소, 행 모두 변경]

만약 새로운 데이터가 추가될 때 실제 데이터 페이지에서 페이지 분할이 발생하면 별도의 인덱스 페이지의 실제 데이터 페이지 주소도 변경되어야 한다. 반면, 실제 구조를 살펴보면

 

컬러스터링 인덱스 : id가 3인 데이터 추가 -> 페이지 분할
논 클러스터링 인덱스 : 추가되는 데이터의 이름(파랑)을 사전식 정렬에 맞춰 추가, 그 외 변화 X

논-클러스터링 인덱스의 인덱스 페이지에 데이터 페이지의 주소가 아닌 데이터가 들어가기에 실제 데이터 페이지는 페이지 분할이 발생하더라도 인덱스 페이지에는 데이터 추가 외에 기존 데이터가 이동하는 변화가 없다. 따라서 예상 구조를 사용하면 안 된다.

 

-> 인덱스가 효율적인 이유 (대수 확장성)

깊이        각 노드의 최대 키 값 개수
1             (m-1) B트리의 차수 m
2             4 * (m-1)
3            16 * (m-1)

컬렉션 추가(리프 노드 수)에 비해 깊이가 쌓이는 속도가 느리다. 기본적으로 깊이가 1층 증가할 때 각 노드에 포함될 수 있는 최대 키 값 개수가 4배씩 증가한다. 깊이가 10인 B트리 구조에서 100만개의 데이터를 조회할 수 있다.

 

- 인덱스 최적화 방법

1. 인덱스는 비용이다.

1) 인덱스를 사용한 검색은 인덱스 리스트를 탐색하고 컬랙션을 조회하는 순으로 탐색하기 때문에 관련 읽기 비용이 든다.
2) 컬랙션이 수정될 때, 인덱스도 수정되어야 한다. 이때, B트리 높이를 균형있게 조절하는 비용도 들고, 데이터를 효율적으로 검색하기 위해 분산시키는 비용이 발생한다.

따라서, 무작정 인덱스를 만들면 안 된다.

 

2. 복합 인덱스(같음, 정렬, 다중값, 카디널리티)

여러 필드를 기준으로 조회를 할 때 복합 인덱스가 생성되며, 생성하는 순서에 따라 성능이 달라진다. 또한, 범위 검색과 같은 여러 값을 출력하는 필드는 데이터의 양이 많을수록 인덱스 성능이 떨어진다.

 

1) 같음 : == 비교를 하는 필드를 제일 먼저 설정
2) 정렬 : 정렬에 사용되는 필드를 그 다음 설정
3) 다중값 : 여러 개의 값을 출력하는 필드는 나중에 설정
4) 카디널리티 : 유니크 성을 나타내며, 유니크 성이 높은 필드를 먼저 설정하는 것이 좋음
5) where, join, order by 절에 자주 사용되는 컬럼
6) insert, update, delete가 자주 발생하지 않는 컬럼
7) 규모가 작지 않은 테이블

 

- MySQL 인덱스 실습 및 성능 비교

위처럼 인덱스를 설정하고 인덱스를 확인하면 다음과 같은 내용을 확인할 수 있다. 

 

중복 : PK와 UQ는 중복을 불허(0)하고 (1)은 중복이 허용되는 데이터 특성을 나타낸다.
key_name : 인덱스 이름으로 pk는 자동으로 PRIMARY로 설정된다.
카디널리티 : 데이터가 없는 상태에선 0을 나타내고, 데이터를 삽입하면 수치가 올라간다.

 

MySQL에서는 위와 같은 SQL로 인덱스를 설정할 수 있고 키를 삭제하거나 수정할 때 사용하기 위해 키 이름을 만들어야 한다. 인덱스를 만들 때 조합으로 여러 개 줄수도 있는데, 키 이름을 생략하면 조합 중 첫 번째 컬럼명이 키 이름으로 들어간다.

 

ADD KEY 명령어로 first name 컬럼 인덱스를 추가하면 중복을 불허하여 Non_unique가 1이고 카디널리티에서 PK는 모든 데이터가 고유하고, first name은 1236 종류를 가지고 있다고 볼 수 있다.

 

ALTER 명령어 외에도 CREATE 명령에서 인덱스를 생성할 수 있으며, 어떤 컬럼으로 인덱스가 생성되었는지 확인할 수 있다.

 

first name 인덱스로 검색을 해보면, 

 

1행이나 233행 출력이나 차이가 없을 정도로 빠르다.

 

인덱스를 제거하고 검색하면 0.2초나 걸리고 데이터의 크기가 클수록 이 차이는 더 커진다.

 

-> 복합 인덱스 성능 비교

first name, last name처럼 여러 개의 컬럼을 기준으로 검색하는 경우 인덱스 성능을 비교해보자

 

1. first name만 인덱스 설정

이 경우 인덱스가 설정된 토마스로 먼저 조회하고 찾은 233 중에서 호프만을 검색한다.

 

성으로 정렬된 별도의 인덱스 페이지 -> 인덱스 페이지의 실제 데이터 페이지 주소와 행으로 접근

선행 작업으로 인덱스를 사용하기에 빠르게 동작한다.

 

2) first name, last name 둘 다 인덱스 설정

그런데, last name까지 인덱스를 설정하면 더 빨라진다. 이는 마치 first name으로 먼저 정렬하고, last name으로 2차 정렬한 것처럼 둘 다 정렬된 상태에서 먼저 first name으로 정렬되어 있어서 토마스를 빠르게 찾고 last name으로 정렬되어 있으니 성능이 좋아진다. 따라서 보통 1개씩 조회하면 인덱스를 하나만 적용하고 둘 씩 묶어서 조회하면 인덱스도 묶는 것이 좋다. 시스템에 많이 쓰는 쿼리에 where 절에 조건에 맞춰서 인덱스를 주는 것이 좋다.

 

3) first name, last name 둘 다 인덱스 설정 후 last name으로만 검색

하지만, 인덱스 2개를 설정한 상태에서 last name으로만 검색하면 last name이 first name 다음에 정렬되어서 전체적으로 인덱스가 적용이 안 된다.

 

조회 결과 다시 0.2초가 걸리는 것을 볼 수 있다. 따라서 이 경우 키를 추가로 줘서 last name으로만 조회할 때는 last name 인덱스를 사용하고 first name과 조합으로 조회할 땐 복합 인덱스를 사용하도록 하는 것이 좋다.

 

✅ 인덱스 사용시 주의사항

1) 자주 사용되는 조건문을 잘 분석해서 인덱스 생성
2) 사용되지 않는 인덱스는 과감히 제거
3) 인덱스를 미적용했을 때 단건 조회에서는 성능에 큰 차이가 없겠지만, 어떤 작업을 위해 선행되어야 하는 작업이라면 성능에 큰 영향을 줄 수 있다.