실행계획과 옵티마이저
데이터베이스에게 sql을 통해 질의를 수행한다고 가정해보자. 이 때 데이터베이스의 결과 통지 과정은 다음과 같다.
- 데이터베이스가 sql을 받고 구문 오류가 없는지를 보는 파스 작업을 한다. 오류가 나면 신택스 오류 메세지를 날린다.
- 파스를 통과하면 sql문에 필요한 데이터에 어떤 경로로 접근할지 계획을 세운다. 이를 실행계획 또는 액세스 플랜이라 하며, 실행계획을 결정하는 내부 프로그램을 옵티마이저라 한다. 옵티마이저는 최적화라는 동사에서 파생됨. 사람이 아닌 옵티마이저에 실행 계획을 위임하는 이유는 데이터베이스가 더 효율적인 실행계획을 만들 수 있다고 판단하기 때문이다.
- 옵티마이저는 작성한 실행계획을 평가한다.
- 실행계획을 통해 데이터에 액세스한다.
옵티마이저는 테이블의 열,행수, 열의 길이와 데이터형, 테이블 크기, 기본키나 not null 제약정보, 열 값의 분산과 편향 등의 통계정보를 고려하여 실행계획을 세운다. 그러나 통계정보 수집이 오래 걸리는 경우 오히려 성능이 떨어지게 된다. 통계정보는 이미 집약된 작은 크기의 데이터기에, 다소 부정확함은 눈감아줘도 충분한 이익인 속도를 손에 넣을 수 있다.
풀 스캔과 레인지 스캔
옵티마이저가 테이블의 데이터를 조회하는 방법에는 두 가지가 있다.
풀 스캔은 테이블에 포함된 레코드 전체를, 레인지 스캔은 테이블에 포함된 특정 레코드 범위를 지정해 읽는 방법이다.
레인지 스캔을 실행할 때 필요한 것이 바로 인덱스다. 적절한 인덱스가 없다면 데이터베이스는 어쩔 수 없이 풀 스캔을 해야 한다.
인덱스의 생성
선택된 행수가 전체의 극히 일부라 해도 인덱스가 없으면 풀 스캔을 선택할 수밖에 없다. 인덱스는 아래와 같은 sql문으로 만들 수 있다
create index 인덱스명 on 테이블명 (열명) ;
예를 들어 city 테이블명의 district 열에 “ind_districe”라는 인덱스를 건다면, create index ind_districe on city (district) ; 와 같이 작성할 수 있다.
그러나 인덱스가 걸려 있더라도, 인덱스가 아닌 풀 스캔이 적용될 수도 있다. 사용자는 인덱스를 만들기만 할 뿐, 인덱스를 사용하는 지시는 dbms가 이를 풀 스캔보다 낫다고 인식 후 자동으로 이를 실행계획에 넣는다. 이것이 옵티마이저의 일이다.
인덱스의 성능과 구조
응답 시간이 늦은 sql이 발견되면, 일단 인덱스로 해결할 수 없는지를 검사하는 것이 옵티마이저의 제 1선택이다. 인덱스는 sql문을 변경하지 않아도 성능 개선이 가능하며, 테이블의 데이터에 영향을 주지 않고, 일정한 효과가 기대되기 때문이다. 즉 비용 대비 성능이 높다.
B-Tree
인덱스의 구조는 트리 형태의 b-tree 구조를 가진다. 트리 구조는 어떤 특정 데이터를 찾는 것을 매우 효율적이고 단시간 내에 실행할 수 있다. b-tree는 반드시 데이터를 정렬된 상태로 유지한다. 데이터가 순서를 유지하는 것은 b-tree의 핵심이다.
b-tree 구조의 장점은 어떤 값에 대해서도 같은 시간에 결과를 얻을 수 있는 균일성이다. 값의 대소를 비교해 분기에 들어가는 이진 탐색 방법을 사용해 데이터를 찾는다. 이는 정렬을 마친 데이터 구조를 탐색할 때 효율적이다. 가장자리에서부터 찾아들어가는 선형 탐색보다 훨씬 빠르다.
b-tree는 값에 따른 성능 불균형이 작은 균형 트리이다. 균형 트리는 루트부터 리프까지의 거리가 일정한 구조이다. 하지만 데이터 갱신이 반복될 때 균형이 깨지고 성능이 약화되므로, 갱신 빈도가 잦은 테이블은 인덱스 재구성을 해서 균형을 찾아야 한다.
b-tree는 데이터양이 증가할 수록 효과가 오른다. 풀 스캔이 테이블 크기에 비례해 실행시간이 늘어나는데, 인덱스를 사용할 때는 데이터양이 계속 늘어나도 시간 증가가 거의 평평한 증가 곡선을 그린다
인덱스 작성 시 주의점
인덱스는 좋다고 마구 사용하다가 역효과가 날 수 있다.
먼저 인덱스 갱신의 오버헤드로 갱신 처리의 성능이 떨어질 수 있다. 테이블에 데이터가 추가되거나 기존 데이터가 갱신 및 제거되면 자동으로 인덱스가 갱신된다. 몇천만 행이나 몇억 행 갱신을 수행하면 인덱스 갱신 시간이 걸릴 수 밖에 없다. 인덱스를 통해 select를 고속화하는 것은 insert나 update 같은 갱신 처리를 늦추는 트레이드 오프이다.
또 의도한 것과 다른 인덱스가 사용될 수 있다. 예를 들어 한 개의 테이블에 복수의 인덱스를 작성한 경우 문제가 될 수 있다. 옵티마이저의 실행계획 선택은 고도화되어 있지만, 사용할 수 있는 인덱스가 많으면 옵티마이저도 헤매면서 최적의 예측을 하지 못할 수 있다.
인덱스를 만드는 기준
- 데이터의 크기가 큰 테이블만 만든다. 크기가 작으면 풀스캔이랑 큰 차이가 없음
- 기본키 제약이나 유일성 제약이 부여된 열에는 불필요하다. 이미 자동으로 인덱스가 작성되어 있기 때문이다. 자동으로 걸리는 이유는 값의 중복 체크를 위해서는 데이터 정렬이 필요한데, 인덱스를 작성해 정렬하는 것이 편리하기 때문이다.
- 카니널리티, 즉 값의 분산도가 높은 열에 만든다. 특정 열에 많은 종류를 가지는 것을 카디널리티가 높다고 표현할 수 있다. 예를 들면 중복되지 않는 운전면허증 번호나 이메일 등이 해당한다. 또 시도 행정구역, 성별같이 분산이 적은 데이터는 카디널리티가 낮다. 카디널리티가 낮은 열은 인덱스 트리를 따라가는 조작이 증가할수록 오버헤드가 증가해 인덱스를 작성한 혜택을 받지 못하기 때문이다.
