- 순차I/O vs 랜덤I/O
- 인덱스의 의미
- 인덱스를 효율적으로 사용하는 방법
- 인덱스를 효율적으로 사용하는 쿼리
- 인덱스 레인지 스캔
- 클러스터 인덱스
- NOMALIZATION
- 파티셔닝을 이용한 데이터 분산
- 파티셔닝 vs 샤딩
- 캐시
- 프로시져
- 트리거
순차 I/O vs 랜덤 I/O
순차 I/O(sequence)는 연속적인 블록이나 행을 읽거나 쓰는 작업을 말한다.
데이터가 연속돼 있기 때문에 속도가 빠르다.
또 이전에 읽은 블록이 캐시에 남아 있을 가능성이 높아
더 빠른 처리가 가능해진다.
랜덤 I/O(random)는 임의의 블록이나 행을 읽거나 쓰는 작업을 말한다.
여러 위치로 이동해 처리해야 하기 때문에 속도가 상대적으로 느릴 수밖에 없다.
순차 I/O와 랜덤 I/O의 공통점은
HDD의 플래터(원판)을 돌려서 읽어야 할 데이터의 위치로 디스크 헤더를 이동시킨다는 부분이다.
여기서 둘의 속도 차이가 발생하게 된다.
순차 I/O의 경우 데이터가 연속적으로 존재하기 때문에
디스크 헤더의 이동이 필요 없지만,
랜덤 I/O의 경우엔 데이터 위치가 분산돼 있기 때문에
디스크 헤더가 이동하게 된다.
MySQL의 경우 InnoDB 기반의 스토리지 엔진을 사용하는데,
이 스토리지 엔진은 특정 작업 시 순차 I/O보다 랜덤 I/O 기반으로
작업을 처리하는 경향이 있다.
MySQL이 트랜잭션 처리, 복제, 동시성 제어 및 안전성을 제공하는 데
더 좋은 성능을 낼 수 있도록 설계되어 있기 때문에 그렇다.
우리가 흔히 말하는 쿼리 튜닝이란 랜덤 I/O를 최대한 줄이는 방향으로
개선하는 작업을 말한다.
랜덤 I/O를 줄인다는 건, 꼭 필요한 데이터만 읽도록 쿼리를 작성하는 걸 말한다.
인덱스의 의미
인덱스는 데이터베이스의 검색 속도를 향상하기 위해 사용하는 데이터 구조를 말한다.
흔히 드는 예시로 책갈피, 혹은 목차가 있다.
데이터 전체를 뒤지지 않고 특정 데이터의 위치를 기반으로 처리되기 때문에
빠른 속도로 검색을 할 수 있다.
인덱스 또한 저장 공간을 차지하므로 오히려 너무 많은 인덱스는 성능 저하를 일으킬 수 있고,
특히 insert, update, delete 작업 등 값이 자주 변화되는 컬럼에 인덱스를 걸게 되면 추가 오버헤드가 발생해 성능 저하가 생길 수 있어 이런 곳에 인덱스의 사용은 지양해야 한다.
보통은 select 시 where절에 사용되는 컬럼에 인덱스를 사용하는 것이 좋다.
인덱스를 효과적으로 사용하는 방법
-
Cardinality
카디널리티는 인덱스 된 컬럼에서 고유한 값을 가지는 정도를 나타내는데,
이 값이 높을수록 중복이 적다는 의미이므로 카디널리티가 높을수록 인덱스 효과가 좋다고 볼 수 있다.
고유한 값이 많고 자주 조회되는 컬럼에 인덱스를 걸어야 한다. -
Update Frequency
위에 인덱스의 의미 부분에서 설명했듯 갱신 빈도가 높은 컬럼에는
인덱스 사용을 지양해야 한다.
변경이 가해지는 컬럼은 인덱스의 재구성도 자주 일어나게 된다.
이는 추가 오버헤드로 이어지게 된다. -
size
마찬가지로 위에서 설명했듯 인덱스 역시 저장공간을 차지하기 때문에
인덱스의 크기도 고려해야 할 대상이다.
사이즈가 큰 인덱스는 읽기 작업에도 시간이 더 걸리게 되고
메모리에 맞지 않을 경우 디스크 I/O가 증가할 수 있다.
인덱스를 효율적으로 사용하는 쿼리
- LIKE
- BETWEEN
- IN
- SUBSTRING()
- LOWER()
like를 사용할 땐 %aaa 이런 형태로 사용하면 좋지 않다.
인덱스는 좌측부터 읽는 특징이 있는데 위와 같은 형태로 작성 시
인덱스를 활용할 수 없게 된다.
그래서 모든 레코드를 순차적으로 스캔하게 될 수 있다.
between
멀티인덱스 상황에서는 between을 쓸 때 효율이 떨어진다고 한다.
멀티인덱스는 여러 개의 컬럼을 조합한 인덱스를 의미하는데
between을 사용할 때 범위 지정을 명확하게 하지 않으면
뒤에 오는 내용을 full scan 해야 하므로 효율이 떨어진다고 한다.
between은 연속된 범위를 검색하는 데 효과적이고
in은 OR 조건으로 연속적이지 않은 대상을 검색할 때 효과적이다.
멀티인덱스의 경우 연속적이지 않은 여러 컬럼들의 조합을 사용하기 때문에
between보다는 in을 사용하는 게 더 효율적이라는 얘기라고 생각된다.
인덱스 된 컬럼은 조작하면 안 된다.
substring이나 lower의 경우 값을 변경하게 되는데,
이 경우 인덱스를 활용하지 못하고 full table scan을 수행할 수 있다.
인덱스는 원래 데이터의 일부로 정렬된 구조인데
변형된 값은 이 순서를 깨뜨릴 수 있기 때문이다.
그래서 가능하면 인덱스 된 컬럼 그대로를 사용하고
조회 성능을 개선하기 위해 필요한 경우만 인덱스를 생성하는 게 좋다.
인덱스 레인지 스캔
검색해야 할 인덱스의 '범위'가 결정됐을 때 사용하는 방식을 말한다.
일반적인 DBMS의 인덱스는 대부분 B-Tree 구조로 되어 있다.
리프 노드가 모두 같은 레벨에 존재하는 밸런스 트리이다.
루트 노드 -> 브랜치 노드 -> 리프 노드 방향으로 비교를 해 시작점을 찾는다.
시작점을 찾았다면 그때부터는 리프 노드의 레코드만 순서대로 쭉 읽게 된다.
인덱스는 정렬되어 있기 때문에 순서대로 읽을 수가 있다.
만약 리프 노드의 끝까지 다 읽었으면 리프 노드 간 링크를 통해
다음 리프 노드로 이동해 검색을 다시 수행하게 되고
끝점을 찾았다면 해당 범위 내의 데이터를 반환하게 된다.
데이터 반환의 절차를 살펴보자면,
리프 노드에 담긴 포인터를 통해 해당 주소의 데이터 파일을 읽어온 후
데이터 파일에서 정보를 가져와 그 값을 반환하게 되는데
이때 디스크 랜덤 I/O가 발생하게 된다.
쿼리 최적화는 이 랜덤 I/O의 발생 빈도는 줄이는 게 목적인데
인덱스를 통해 찾으려는 레코드가 20~25%를 넘으면(검색 범위가 넓을 경우)
랜덤 I/O 때문에 느려질 수 있어 오히려 풀 스캔이 더 나은 선택이 될 수 있다.
추가적으로 인덱스 스캔의 종류에는 위에 설명한 인덱스 레인지 스캔 외에도
인덱스 풀 스캔, 인덱스 유니크 스캔, 인덱스 루스 스캔, 인덱스 병합 스캔 등이 존재한다.
모두 인덱스를 활용한 스캔 방법들인데 간단하게 살펴보자면,
인덱스 풀 스캔은 인덱스의 모든 데이터를 읽어오며
인덱스의 크기가 작은 경우 빠른 검색이 가능하지만
테이블이 큰 경우 테이블 풀 스캔보다 느릴 수 있다.
인덱스 유니크 스캔은 인덱스 유일값을 검색하는 방법인데
인덱스 풀 스캔과 달리 인덱스나 테이블의 크기와는 관계 없이
빠른 검색이 가능하다.
다만 인덱스 컬럼이 유일값을 가지고 있어야만 사용이 가능하다.
인덱스 루스 스캔은 말 그대로 타이트하지 않게 듬성듬성
일부 데이터만 읽어오는 방식으로 범위 지정 없이 검색을 수행한다.
루스 스캔도 인덱스나 테이블의 크기와 관계 없이 빠른 검색이 가능하지만
정확도가 떨어질 수 있다.
인덱스 병합 스캔은 여러 인덱스를 합쳐서 검색하는 방법인데
복잡한 검색 조건을 처리할 수 있다는 장점이 있지만
인덱스의 크기가 작은 경우에만 유용하고 병합하는 데 시간이 걸릴 수 있다.
클러스터 인덱스
클러스터 인덱스는 테이블에서 특정 컬럼값에 따라
물리적으로 데이터를 정렬하는 방식의 인덱스를 말한다.
클러스터 인덱스의 주요 특징을 살펴보자.
-
데이터 정렬
데이터 저장 시 지정된 컬럼의 순서대로 레코드를 저장한다.
이는 해당 컬럼을 기준으로 한 검색을 매우 빠르게 만들어준다. -
검색 성능
범위 검색 성능을 크게 개선시킨다.
한 번의 디스크 접근으로 연속된 범위 내의 모든 값을 가져올 수 있기 때문이다. -
유일성
물리적인 순서대로 저장되기 때문에
하나의 테이블에는 하나의 클러스터 인덱스만 존재할 수 있다.
보통 PK 컬럼에 사용된다.
클러스터 인덱스는 보통 PK 컬럼에 사용되며,
PK 컬럼값의 순서에 따라 물리적으로 정렬된다.
만약 PK가 변경된다면 해당 레코드의 물리적 위치도 변경돼야 한다.
인덱스가 걸려 있다면 포인터도 변경되어야 한다.
이처럼 클러스터 인덱스는 검색 성능을 대폭 개선할 수 있지만
PK에 대한 값을 바꾸는 등의 대상 컬럼에 대한 수정 작업이 발생했을 시
비용이 높아질 수 있는 단점이 있다.
Normalization(정규화)
정규화는 데이터베이스 설계 과정에서 중복을 최소화하고
데이터 구조를 효율적으로 만들기 위해 사용되는 기술이다.
이는 데이터의 무결성과 일관성을 유지하는 데 중요한 역할을 한다.
-
정합성
정규화를 통해 각각의 정보가 한 곳에서 관리된다.
정보 변경이 일어났을 때 여러 위치를 찾아 수정할 필요 없이
한 번의 업데이트로 관리가 가능해진다. -
중복 데이터
중복 데이터를 제거하면 저장 공간을 절약할 수 있고
중복으로 인한 오류의 가능성도 줄일 수 있다.
정규화는 여러 단계가 존재하는데 각 단계와 그에 따른 설명은 아래와 같다.
- 제1정규화(1NF)
컬럼이 원자값을 갖도록 테이블을 분해해야 한다.
-
각 컬럼은 하나의 속성만을 가져야 함.
-
각 컬럼은 하나의 타입만을 가져야 함.
-
각 컬럼은 유일한 이름을 가져야 함.
-
컬럼의 순서는 상관 없어야 함.
이름 나이 주력언어 김코드 24 python, java 최코드 29 java 이코드 35 html 이런 구조를 아래처럼 해야 한다는 것이다.
이름 나이 주력언어 김코드 24 python 김코드 24 java 최코드 29 java 이코드 35 html
- 제2정규화(2NF)
완전 함수 종속을 만족하도록 테이블을 분해해야 한다.
-
제1정규화를 만족해야 함.
-
모든 컬럼에 부분적 종속이 없어야 하고
완전 함수 종속을 만족해야 함.이름 나이 주력언어 회사 김코드 24 python 카카오 김코드 24 java 카카오 최코드 29 java 네이버 이코드 35 html 배민 이름과 나이가 복합키로 기본키라고 할 때
이름만 알아도 주력언어를 찾을 수 있는 게 부분 함수 종속이다.
제2정규화는 이걸 막고 1:1 매칭이 되도록 해야 한다는 것이다.
제2정규화를 만족하는 형태는 아래와 같다.이름 나이 회사 김코드 24 카카오 김코드 24 카카오 최코드 29 네이버 이코드 35 배민 이름 주력언어 김코드 python 김코드 java 최코드 java 이코드 html
- 제3정규화(3NF)
이행 함수 종속을 제거해야 한다.
-
제2정규형을 만족해야 한다.
-
기본키를 제외한 속성들간에 이행 종속성이 없어야 한다.
이게 무슨 말이냐면,
A -> B
B -> C일 때
A -> C를 만족하면 이행 함수 종속성이 있다는 것이다.이름 주력언어 회사 김코드 python 카카오 김코드 java 카카오 최코드 java 네이버 이코드 html 배민 이름으로 주력언어를 찾을 수 있고,
주력언어로 회사를 찾을 수 있을 때,
이름으로 회사를 찾을 수 있는 상태를 이행 함수 종속성이 있다고 판단하며,
이것을 제거하는 것이 제3정규화의 목적이다.이름 주력언어 김코드 python 김코드 java 최코드 java 이코드 html 주력언어 회사 python 카카오 java 카카오 java 네이버 html 배민 이렇게 분해하는 것이 제3정규화이다.
- 보이스 코드 정규화(BCNF)
결정자가 후보키가 아닌 함수 종속을 제거한다.
-
제3정규형을 만족해야 함.
-
모든 결정자가 후보키 집합에 속해야 함.
함수적 종속성이란 무엇이냐면,
A와 B 두 속성이 있을 때 A의 값이 B의 값을 결정할 수 있다면
B는 A에 함수적으로 종속되어 있다고 말할 수 있다.위 표의 예시로 보자면,
이름이 고유값이라면 이름은 주력언어나 회사 등
다른 정보를 결정하는 데 사용할 수 있다.
즉 이름은
이때 주력언어나 회사 정보 등은 이름에 종속되어 있는 것이다.💡 잠깐 용어 개념 이해
결정자:
어떤 속성의 값이 다른 속성들의 값을 결정하는 역할을 할 때 결정자라고 한다.후보키:
각 레코드(row)를 유일하게 식별할 수 있는 속성이나 속성들의 집합을 말한다.기본키:
후보키 중 데이터베이스 설계 시 고유하게 식별 가능한 컬럼을 말한다.
보통 PK 걸리는 컬럼이 기본키이다.
모든 기본키는 후보키가 된다. (기본키 in 후보키)복합키:
꼭 기본키를 PK 컬럼만 사용하지는 않는다.
2개 이상의 컬럼을 조합해서 기본키로 설정할 수도 있는데
이런 경우를 복합키라고 한다.돌아와서,
결정자가 후보키가 아닌 함수 종속을 제거한다라는 말은
모든 결정자가 후보키 집합에 속해야 한다는 말인데,이름과 주력언어가 기본키(복합키)라고 했을 때
이름과 주력언어를 통해 회사 정보를 가져올 수 있다면
기본키는 결정자이고 회사 정보는 기본키에 대해 종속된다고 볼 수 있다.그런데 거꾸로 회사 정보를 통해 주력언어를 가져올 수 있다면
이 경우 회사가 결정자가 되고 주력언어는 회사에 종속 상태가 된다.
하지만 현재 회사라는 결정자는 기본키(이름, 주력언어)에 포함되어 있지 않으므로
이런 경우 정규화를 해야 한다는 의미이다.이름 회사 김코드 카카오 김코드 카카오 최코드 네이버 이코드 배민 회사 주력언어 카카오 python 카카오 java 네이버 java 배민 html
- 제4정규화(4NF)
다치 종속을 제거한다.
-
BCNF를 만족해야 함.
-
다치 종속이 없어야 함.
여기서 다치 종속이란 다중값 종속을 말한다.
같은 테이블 내의 독립적인 2개 이상의 컬럼이
또 다른 컬럼에 종속되는 것을 말한다.A가 결정자, B가 종속적 상태라고 할 때
A는 단일값이지만 B가 다중값으로 존재한다면
이것을 다치 종속이라고 할 수 있다.
표기법은 A ↠ B 이렇게 이중 화살표를 사용한다.이름 주력언어 자격증 김코드 python sqld 김코드 java 정보처리기사 최코드 java 워드1급 이코드 html 컴활1급 위와 같은 테이블 구조일 때는
김코드에게 주력언어와 자격증의 두 가지 의존성이 생긴다.
이걸 분해해야 하는 게 제4정규화의 내용이다.
제4정규화를 적용한 결과는 아래와 같다.이름 주력언어 김코드 python 김코드 java 최코드 java 이코드 html 이름 자격증 김코드 sqld 김코드 정보처리기사 최코드 워드1급 이코드 컴활1급
- 제5정규화(5NF)
조인 종속을 제거한다.
-
4NF를 만족해야 함.
-
조인 종속성에 의해 발생할 수 있는 이상 현상을 제거함.
-
조인 종속이 없는 릴레이션을 말함.
💡여기서 조인 종속이란?
주어진 관계를 A라고 하고
이 A를 A1, A2 이렇게 2개 이상의 부분 관계로 분리했을 때
이걸 다시 원래대로 결합해 올바르게 복원할 수 있다면
A는 A1와 A2에 대해 조인 종속성을 가진다고 말할 수 있다.다만 복원 시 불필요한 데이터가 발생하게 된다면
조인 종속에 대한 이상 현상이 발생한 것으로 볼 수 있다.이름 주력언어 자격증 김코드 python sqld 김코드 java 정보처리기사 최코드 java 워드1급 이코드 html 컴활1급 이 테이블을 2개 이상의 부분 관계로 분리했다가
다시 결합했을 때 아래와 같이 된다면
조인 종속에 대한 이상 현상이 발생한 것이다.이름 주력언어 자격증 김코드 python sqld 김코드 java sqld 김코드 python 정보처리기사 김코드 java 정보처리기사 최코드 java 워드1급 이코드 html 컴활1급 이 경우 조인 종속을 제거해 제5정규화를 적용하면
아래와 같이 된다.이름 주력언어 김코드 python 김코드 java 최코드 java 이코드 html 주력언어 자격증 python sqld java 정보처리기사 java 워드1급 html 컴활1급 이름 자격증 김코드 sqld 김코드 정보처리기사 최코드 워드1급 이코드 컴활1급
실무에서는 보통 제3정규형까지 적용하는 경우가 많다.
그리고 정규화가 필수인 것도 아니다.
상황에 따라 중복을 허용할 수도 있고
더 큰 단위의 객체를 유지하는 비정규화 과정이 필요할 수도 있다.
partitioning을 사용한 데이터 분산
파티셔닝은 대용량 테이블이나 인덱스를 관리하기 위한 기법 중 하나이다.
데이터를 여러 부분으로 나누어 저장하게 되면
쿼리 성능이 향상되고 데이터 관리도 용이해지며 백업/복구 시간을 줄일 수 있게 된다.
파티셔닝의 종류는 다음과 같다.
-
Range Partitioning
범위 파티셔닝은 지정된 컬럼 값의 범위에 따라 데이터를 분할한다.
날짜 컬럼을 기준으로 할 경우 각 연도나 각 월에 해당하는 데이터를
별도 파티션으로 만들 수 있다. -
List Partitioning
목록 파티셔닝은 미리 정의된 목록에 따라 데이터를 분할한다.
만약 국가명이 들어가는 컬럼이 있고 국가명에 따라 파티셔닝을 한다면
KR 값끼리, US 값끼리 각 파티션에 저장되는 형태이다. -
Hash Partitioning
해시 파티셔닝은 해시 함수 결과값에 따라 데이터를 분할한다.
이 방식은 균등한 크기로 나눌 때 유용하며 특정 범위나 목록에 의존하지 않아서
접근 패턴이 일정하지 않은 경우 좋은 성능을 제공한다. -
Composite Partitioning
복합 파티셔닝은 위에 언급한 내용들을 조합해서 사용한다.
범위-해시, 범위-목록 이런 식으로 사용하게 되는데,
좀더 세밀한 제어와 최적화가 가능해진다.
파티션 구성 후 쿼리 실행 시 DBMS는
필요한 모든 데이터가 1개 이상의 특정 파티션 내부에만 존재한다면
그 외 다른 파티션엔 접근하지 않는다.
즉 필요한 데이터가 포함된 파티션만 탐색하는 과정,
이걸 '파티션 프루닝(partition pruning)'이라고 부르는데
이로 인해 쿼리 성능이 크게 향상된다.
예를 들면 국가 컬럼 기준의 목록 파티셔닝에서
SELECT * FROM table WHERE country = 'KR';이와 같은 쿼리를 실행한다면 DBMS는 'KR' 파티션만 검색한다.
다른 'US', 'UK' 등의 파티션은 완전히 무시되므로
I/O 비용을 줄여주고 성능 최적화에 중요한 역할을 한다.
파티셔닝 vs 샤딩
파티셔닝은 하나의 데이터베이스 내에서 테이블을 논리적으로 나눈다.
같은 데이터베이스 서버 내에 위치한다.
특정 데이터에 빠르게 접근하거나 백업/복구를 용이하게 하기 위해 사용된다.
샤딩은 데이터베이스 자체를 여러 부분으로 나눈다.
각 샤드는 다른 서버에 위치할 수 있고
독립적인 하드웨어 리소스와 처리 능력을 가진다.
시스템의 확장성을 개선하기 위해 사용되며
대량의 트래픽과 데이터를 분산처리하는 환경에서 사용된다.
정리하면,
파티셔닝: 단일 DB 내부에서 테이블을 분리하여 성능 및 용이성 개선
샤딩: 여러 DB 서버로 분산해서 시스템 전체 성능 및 확장성 개선
캐시, 프로시저, 트리거
캐시는 시스템에서 자주 사용되는 데이터나 결과에 빠르게 접근하기 위해
임시로 저장하는 공간이다.
데이터베이스 쿼리 결과, 웹페이지, 이미지 등 다양한 정보를 캐싱할 수 있다.
캐시를 통하면 시스템의 성능을 향상시킬 수 있고 I/O 작업을 줄일 수 있다.
프로시저는 SQL 문장들을 하나의 함수처럼 묶어서 데이터베이스에 저장한 후
필요할 때 호출해 사용하는 것이다.
트리거는 데이터베이스에서 특정 이벤트(INSERT, UPDATE, DELETE)가 발생하면
자동으로 실행되는 일련의 SQL문이다.
