[DataBase] 성능과 인덱스
성능을 측정하는 지표
시스템 세계에서의 성능은 2가지의 지표에 의해 측정된다.
한 가지는 처리 시간(또는 응답 시간)이라 불리는 지표이다. 이 지표는 '웹사이트에 결과를 표시하는데 5초가 걸린다' 처럼 특정 처리의 시작 ~ 종료까지 걸린 시간을 나타낸다.
다른 한가지는 처리율(Throughput)이다. 이는 특정 처리를 단위 시간에 몇 건 처리 가능한가를 의미하며, 흔히 사용하는 TPS(Transaction Per Second)가 50이라고 하면, 초당 트랜잭션을 50건 처리 가능하다는 의미이고 50TPS가 시스템의 처리율이 된다.
처리율이 성능 지표에서 중요한 이유
시스템의 자원 용량(Resource Capacity)은 무한하지 않다. 처리율이 높다는 것은 CPU나 메모리같은 하드웨어 자원이 많이 필요하다는 것을 의미하는데, 시스템은 하나라도 처리를 실행하면 시스템의 자원을 소비한다. 하여 동시에 실행되는 처리가 많다면 물리 자원이 많이 요구됨을 의미한다.
만약 한 가지 자원이라도 한계에 이르게 된다면 앞서 말한 응답 시간은 늘어나고 처리율은 줄어들기 시작한다. 이 때 한계에 최초로 도달한 자원을 '병목(Bottle neck)'이라고 부르며, 시스템의 처리가 많아지는 순간을 대비해서 자원을 준비하지 않으면 추후에 큰 지연을 일으킬 수밖에 없다.
(처리율과 응답 시간이 극단적으로 나빠지는 시간을 한계점(Breaking Point)라고 한다.)
그러므로, 자원을 확보해 두기 위한 것으로 '사이징' 이나 '캐퍼시티 플랜' 등의 대비가 필요하다.
데이터베이스가 병목되는 이유
취급하는 데이터 양이 많아서이다. '빅데이터'의 등장 배경을 생각하자. 말 그대로 데이터가 너무 많아 저장소 자원에서 문제가 발생하고, 이런 문제가 SQL 문의 응답 시간을 낮추게 된다.
또 다른 이유로 데이터베이스의 병목 지점은 CPU나 메모리가 아닌 저장소인데, 저장소를 스케일 아웃하는 것은 어려운 요소이다.
하여 데이터베이스에서는 튜닝 기술이 필요하다. 같은 양의 자원이라도 효율적으로 성능을 향상시키는 것이다. 이 지점이 SQL 튜닝을 하는 이유이며, 주어진 자원 범위 내에서 처리하는 것을 고민해야 한다.
데이터베이스의 결과 통지 과정
1. 작성한 SQL문을 보고, 문법적으로 잘못된 부분이 없는지 체크(Parse)한다 -> 내부적으로 Parser가 동작.
간단한 예시로 SELECK * FROM user; 라는 명령어를 치면, SELECT가 아니기 때문에 구문 오류를 뱉어낸다.
2. Parse단계가 끝나면 실행 계획을 짠다. 이는 SQL 문에 필요한 데이터를 어떻게 접근하는 지를 정하는 것이다. SQL문에서 요구하는 데이터를 얻는 방법은 여러 개일 수도 있기 때문에, 이를 결정해야 한다. 이러한 실행계획을 결정하는 내부 프로그램이 있는데, 옵티마이저(Optimizer)라고 부른다.
하여 결국 데이터를 어떻게 처리할지에 대해서는 이 옵티마이저가 결정하며, 데이터베이스의 핵심 부분이라고 할 수 있다.
그리고 이런 옵티마이저는 데이터베이스의 '통계 정보'를 활용하며, 이는 옵티마이저가 실행계획을 세울 때 참조하는 정보다. 통계정보는 show table 테이블명이나 show index from 테이블명 등, 해당 결과로 보이는 것들이며 대부분 자동으로 수집되어 구현된다고 한다.
정리하면,
파스 -> 실행 계획 작성(통계 정보 활용) -> 실행 계획 평가 -> 데이터 액세스 순으로 이루어진다.
실행 계획 보기
SQL문 앞에 EXPLAIN 키워드를 붙이면 된다.
다음은 explain select * from countires의 결과이다.
테이블의 액세스는 풀 스캔과 레인지 스캔 방식이 있다. 쉽게 말해 모두 보느냐, 일부만 보느냐이다.
type
- ALL : 풀 스캔(Full Scan) 방식으로 동작, 테이블의 모든 레코드를 끝까지 읽어들인다.
- range : 레인지 스캔으로, 일부만 본다.
만약 explain select * from countries where country_id BETWEEN 'A' and 'B'; 식으로 친다면
type이 range로 일부만 검색함을 알 수 있다.
인덱스
관계형 데이터베이스에서 레인지 스캔을 실행하려면 인덱스가 반드시 필요하다. 인덱스가 없으면 데이터베이스는 반드시 풀 스캔을 할 수 밖에 없다. DBMS에는 기본키를 구성하는 열에는 반드시 인덱스가 작성되어 있다. 위의 결과값에서 레인지 스캔을 이용할 수 있었던 이유는 내가 설정한 테이블의 country_id가 primary key이기 떄문이다.
바꿔 말해서 인덱스가 저장되어 있지 않은 키에 where 구문의 범위를 축소한다고 해도, 레인지 스캔은 작동되지 않고 풀 스캔이 작동된다.
index는 show index from 테이블명을 통해 확인할 수 있다.
중요한 것은 사용자는 SQL문으로 명령을 했을 뿐, 인덱스를 사용하라고 한 적은 없다. 이 부분이 바로 옵티마이저가 한 부분이며, 옵티마이저가 인덱스를 사용하는 것이 빠르다고 판단하고 인덱스를 사용한 것이다. 반대로 옵티마이저가 인덱스가 아닌 full scan이 빠르다고 판단하면 full scan을 사용할 수도 있다.
인덱스를 사용하면 풀 스캔 방식으로 작동하지 않기 때문에 일반적인 성능 향상 수단이다(반드시 그렇다는 건 아니다). 응답시간이 늦은 SQL이 발견되면 우선 인덱스로 해결할 수 있는지를 검사하는 것이 튜닝의 첫 번째 선택이다.
인덱스를 사용하면 다음과 같은 장점을 얻을 수 있다.
1. SQL문을 변경하지 않아도 성능 개선을 꾀할 수 있다
2. 테이블의 데이터에 영향을 주지 않는다
3. 일정한, 또는 큰 효과를 기대할 수 있다
인덱스의 구조
인덱스는 내부적으로 B-tree를 사용한다. B-tree는 트리 구조로 되어있고, '어떤 값에 대해서도 같은 시간에 결과를 얻을 수 있다'는 균일성을 가지고 있다. 이는 이진 탐색 형식으로 데이터를 탐색하기 때문이다. 또한 균형 트리의 성질을 가지고 있기 때문에 성능이 안정화되있는 편이다.
다만 B-tree는 처음 생성했을 때는 균형 트리이지만 데이터의 Command 작업 (C, U, D)가 진행되면서 균형이 점차 깨져간다. 하여 갱신 빈도가 높을 수록 균형을 되찾는 작업이 필요하거나, 인덱스의 도입을 검토해야 한다.
인덱스 사용 시 주의
인덱스를 마구잡이로 만드는 것이 반드시 성능의 개선을 가져오지 않는다.
1. 오버헤드
인덱스가 지정되있는 테이블에 CUD 등의 갱신 작업이 계속해서 일어나면 인덱스는 자기 자신도 갱신한다. 즉, 테이블이 변경되면 인덱스도 같이 변경고,기존에 1개의 작업이 2개의 작업으로 늘어나는 꼴이다.
2. 의도한 것과 다른 인덱스 사용
한 개의 테이블에 복수의 인덱스가 작성된 경우 발생할 수 있다. 옵티마이저도 만능이 아니기 때문에, 사용할 인덱스의 후보가 많아지게 되면 어떠한 것을 선택할지 예측하기 힘들다. 가령 쿼리를 작성해서 A 인덱스의 사용을 기대했는데, 전혀 예상하지 못한 F 인덱스를 사용하게 되는 것이다.
만들 때의 기준
1. 크기가 큰 테이블을 만든다.
인덱스로 유의미한 성능 개선을 꾀하기 위해서는 풀 스캔과의 차이가 있어야 한다. 이 차이가 적다면, 인덱스를 굳이 만들 이유가 없다. 하여 테이블의 크기가 큰 것을 위주로 만든다.
2. 기본 키나, 유일성 제약이 부여된 열에는 불필요하다
기본키에는 인덱스가 자동으로 작성되어 있고, 유일성 제약 열 또한 그렇다.
3. Cardinality가 높은 열에 만든다.
Cardinality는 '값의 분산도'를 표현한다. 특정 열에 대해 많은 종류의 값을 가지고 있다면 카디널리티를 높다고 표현하고, 그렇지 않으면 낮다고 표현한다.
예를 들어 주민등록번호는 중복되지 않게 발행되어 카디널리티가 높게 표현된다. 하지만 성별 같은 경우는 두 종류밖에 없다. 이는 카디널리티가 매우 낮은 예시이다.
참고 - 데이터베이스 첫걸음 (마크, 기무라 메이지 지음)