쿼리 실행 계획(TIL)

1. SQL 옵티마이저

 

 개발자가 작성한 SQL은 적절하게 파싱이 되어 SQL 옵티마이저로 넘어가게 된다. SQL 옵티마이저의 역할은 사용자가 원하는 작업을 수행할 수 있는 방법을 찾고 최적의 경로를 선택해주는 역할이다. 안타깝게도, 옵티마이저가 선택하는 내용은 완벽하지 않을 수 있다.

 

 옵티마이저가 완벽하지 않은 첫 번째 이유는 사용하는 정보 때문이다. 옵티마이저가 비용을 산출할 때에는 DBMS 내부에 테이블 통계 정보를 활용한다. 이를 카탈로그라고 하는데, 카탈로그 내부에는 테이블 레코드 수, 필드 수와 크기, 필드 카디너리티, 필드값 히스토그램 정보가 들어있다. DBMS 종류에 따라 다르겠지만 해당 내용을 갱신 명령어로 수동으로 갱신해주어야하거나, 일정 비율 이상으로 데이터가 변경되어야만 카탈로그가 자동 갱신이 된다. 따라서, 과거의 정보로 비용을 산출하는 경우 잘못된 경로를 선택하게 된다.

 

 옵티마이저가 완벽하지 않은 두 번째 이유는 실행 계획이 너무 많아서 일수도 있다. 인덱스 혹은 값 범위 등에 따라 실행 비용을 산출해야하는데, 종류가 너무 많은 경우 각각의 비용을 대략적으로 산출하는 비용이 실제 실행 비용보다 커버리면 안되니 모든 경우를 계산하지 않기 때문이다.

 

 따라서, SQL 옵티마이저가 완벽하지 않다는 걸 DBMS 벤더들도 알기 때문에, HINT를 제공할 수 있게 DB별 HINT 구문 작성 방법이 있다. 아닌 DB도 있지만 Oracle과 MySQL에는 있으며, 찾기 어렵지 않으니 직접 찾아보길 바란다. 해당 글에서는 HINT에 관해서는 다루지 않을 예정이다.

---

2. 메모리와 디스크

 

  당연히 메모리에서 읽는 것보다 디스크에서 데이터를 읽어오는 것이 느리다. 그 이유는 부가적인 OS 내 작업이 일어나고 디스크 내에서 데이터를 읽는 것은 물리적인 이동이 수반되므로 느릴 수 밖에 없다. 그러니 메모리에 올린 버퍼 캐시의 HIT RATIO가 높으면 디스크에서 데이터를 읽을 일이 적어지니 좋다.

 

 여기서는 모든 데이터를 메모리에서 가져올 때도 더 빠르게 가져오는 방법과, 디스크에서 데이터를 가져올 때도 더 빠르게 가져오는 방법이 있지 않을 지를 고민해봤으면 좋겠다.

 

○ 인덱스로 메모리 데이터를 접근할 때 지연이 발생하는 이유

 메모리에서 모든 데이터를 가져오더라도, 시스템 상황에 따라서 속도는 다를 수 있다. 그 이유는 메모리로 설정해놓은 캐시는 멀티 쓰레드 환경이기 때문이다. 데이터를 읽기만 하더라도 버퍼 내부에선 대부분 LRU 알고리즘으로 작동을 하며, 최신으로 읽은 데이터를 메모리 버퍼로 올려놓고 오래된 데이터를 뒤로 미룬다. 결과적으로 무슨 행동을 하건 SQL이 날아왔다는 것은 메모리 버퍼의 변경이 일어남을 의미한다.

 

 조금만 더 자세하게 알아보면 인덱스 내의 ROWID를 통해 캐시를 읽을 때 일어나는 일은 메모리 버퍼 내에 있는 데이터는 내부 해싱 알고리즘을 통해 해시체인에 접근해서 가져오는 방식이다.

 

 참고로 이 과정에 사족을 더하면 읽고자하는 내용을 메모리에서 바로 찾는 게 아닌 해시 함수에 입력해 해시 체인을 찾아 버퍼 헤더를 찾아 거기서 얻은 포인터로 데이터 블록을 찾아간다. 그래서 인메모리 DB랑 비교했을 때 같은 메모리 내부에 캐싱을 해 데이터를 가져오더라도 속도 차이가 난다. 인메모리는 하드 데이터를 캐시에 올려놓고 캐시에 올라간 내용 포인터를 저장해서 가져오기 떄문이다.

 

 다시 돌아와보자. 이 때, 접근하려는 프로세스가 여러 개인 경우, 여러 프로세스가 동시에 체인 구조를 변경하면 안된다. 이는 해시 체인 래치라는 것을 통해 동시에 변경이 일어나는 것을 막고 직렬화한다.

 

 

 그런데, 전략을 잘못 선택해서 읽어야되는 블록의 개수가 늘어나면 결과적으로 메모리에서 가져오더라도 버퍼 변동이 커지고, 해시 체인 래치를 한 프로세스가 오랫동안 잡고 있기 때문에 실행속도가 느려지게 된다. 따라서 동일한 데이터를 메모리에서 가져오더라도 읽는 블록의 개수를 줄이는 선택이 더 좋은 결과를 가져온다.

 

○ 디스크에서 지연이 발생하는 경우

 부가적인 정렬과 관련된 내용을 빼놓고 생각을 해보자. 앞의 인덱스 기초 편에서 클러스터링 인덱스와 논-클러스터링 인덱스에서 클러스터링 인덱스는 링크드리스트로 수평탐색을 할 때, 실제 데이터 저장과 순서가 일치한다고 했었다. 그런데 논-클러스터링 인덱스는 색인처럼 인덱스로 설정된 특정 컬럼들에 대한 순서 정보를 담은 논리적인 정보이기 때문에 이야기가 다르다.

 

 여기서 알아야 되는 정보는 하나가 더 있다. 테이블에 있는 모든 정보는 연속된 파일 내에 있지 않는다. 그 이유는 테이블 정보는 데이터가 추가됨에 따라 확장이 되어야 하기 때문이다. 아래는 친절한 SQL 튜닝 내부에 있는 이미지인데, 데이터를 읽고 쓰는 최소 단위인 블록과 테이블에서 공간을 확장하는 단위인 익스텐트까지는 데이터가 연속된다. 하지만, 그 이후에는 일수도 아닐 수도 있다. 

 

 

 위의 두 가지 내용을 종합해보면 다음과 같은 사실을 알 수 있다. 인덱스의 순서 정보는 논리적인 정보이다. 그러니, 실제 테이블과 일치하지 않는다. 링크드리스트로 된 연속된 정보를 읽을 때, 앞의 내용과 뒤의 내용은 디스크 내의 다른 블록에 위치할 가능성이 높다.

 

 따라서, 디스크에서 읽더라도 같은 익스텐트, 블록 단위에 있어 해당 데이터를 한 번 가져올 때 더 크게 가져올 수 있는가 하나씩 가져오는가에 따라서도 달라진다.

 

○ 인덱스 클러스터링 팩터

 앞의 내용을 종합해보면 DB 버퍼 내부에 있는 Pinning에 관해서도 이해할 수 있다. 인덱스를 통해 데이터를 찾는 과정이 여러 블럭에 흩어져있을 수 있기 때문에 어렵다고 했었다. DB에서는 데이터를 가져오기 전에 항상 버퍼에 있는지를 확인한다. 이 때, 해싱 알고리즘을 통해 적절한 버켓에 접근하여, 체인을 통해 접근하는 방식을 채택한다고 했었다. 이를 기억하고 아래를 읽어보자.

 

 클러스터링 팩터는 '군집성 계수'에 관한 이야기이다. 얼마나 데이터가 몰려있는지에 관한 데이터이다. 데이터가 몰려있다는 것은 같은 데이터 파일 내 같은 익스텐트 내 같은 데이터 블록에 데이터가 있을 수 있다는 것을 의미한다. DB에서 버퍼에서 적절한 데이터를 찾기 위해 해싱하고 래치(락)을 획득하고 해시 체인을 스캔하는 과정은 부가적인 비용이 발생한다. 하지만, 클러스터링 팩터가 높아 같은 블록에 있다면 이를 무시해도 된다. 따라서 클러스터링 팩터가 높은 데이터일수록 범위스캔을 했을 때 불필요한 블럭에 접근하는 횟수가 줄어들어 성능이 좋다.

---

3. DBMS의 쿼리 실행 계획 종류

 

○ 풀테이블 스캔

 가장 기초적인 스캔 방식으로 테이블 전체를 스캔해서 읽는 방식을 의미한다. 테이블에 속한 블록 '전체'를 읽어 사용자가 원하는 데이터를 찾는다. 이런 방식의 특징은 전체 테이블을 놓고 봤을 때 선택해야 하는 레코드의 비율이 클 때 유리하다. 특히나 캐시에서 데이터를 못 찾아왔을 때, 중구난방으로 데이터가 흩어진 인덱스를 스캔하는 것보다 디스크 내부에서 테이블 내의 블록을 한 번에 땡겨와 순서대로 읽을 수 있기 때문이다.

 

 

 하지만, 테이블 내에 PK나 클러스터링 인덱스를 통해 정렬된 내용과 찾고자 하는 내용이 다르다면, 정렬이 안되어있다. 따라서, 정렬에 관한 추가 비용이 발생할 수 있다.

 

○ 인덱스 범위 스캔

 인덱스 범위 스캔은 풀테이블 스캔과 다르게 인덱스 내의 시작지점을 찾고 링크드 리스트로 연결된 인덱스를 순차적으로 탐색하며 종료지점을 만날 때까지 쭉 도는 방식을 사용한다. 그림으로 나타내면 아래와 같다.

 

 

 앞에서 설명한 내용을 써먹을 때가 왔다. 인덱스 내부의 논리적인 정렬 순서와 실제 데이터 정렬 순서는 다르다. 아래 그림처럼 인덱스의 리프 노드의 순서와 실제 데이터 순서가 일치하면 테이블 스캔처럼 데이터를 한번에 가져올 수 있을 것이고 당연히 범위를 줄이는 선택이 옳을 것이다.

 

 하지만 인덱스와 테이블은 아래의 그림처럼 순서가 일치하지 않는 랜덤한 상황이라 가정을 하기 때문에 디스크에서 읽어올 때도 단일 블록을 획득하기 위해 계속 이동을 해야 한다. 따라서, 디스크에서 가져오는 블록 개수만큼 추가 비용이 크게 발생한다.

 

 

 대신 인덱스 범위 스캔을 통해 가져온 데이터는 설정한 인덱스에 따라 다르겠지만, 정렬이 된 채로 나온다는 특징이 있다.

 

 결과적으로 인덱스로 범위가 줄어든 정도에 따라 절약한 비용 + 정렬 연산에서 절약한 비용을 합한 값보다 개별 디스크에서 가져오는 번거로움의 비용이 크다면 풀 테이블 스캔을 아니라면 인덱스 범위 스캔을 선택하는 게 좋다.

 

○ 인덱스 풀 스캔

 인덱스 풀 스캔은 인덱스를 이용해서 테이블 전체를 검색하는 방법이다. 그런데 앞에서 테이블 전체를 스캔하는 게 인덱스를 사용하는 것보다 랜덤 접근을 최소화하기 때문에 풀 테이블 스캔을 하는게 낫다고 설명해놓고 여기서는 왜 인덱스로 테이블을 스캔할까?

 

 

 여기에는 조건이 달린다. 인덱스 가장 선두 컬럼이 포함되지 않아 범위 스캔이 불가능한데, 풀 테이블 스캔하기에는 용량이 너무 큰 경우에 이 전략을 선택한다는 것이다. 인덱스를 설정하면 실제 테이블보다는 작게 컬럼 X 레코드로 작은 저장 공간을 만들 수 있다. 이 때, 아주 일부만 접근해야 한다면, 이편이 낫다는 것이다.

 

 이런 상황이 아닌데 선택하는 경우는 엄청 큰 데이터를 가져오는데, 정렬이 필요한 경우이다. 전체 데이터를 한꺼번에 가져와서 보여주는 게 아닌 일부 앞부분만 보여주고 후에 데이터를 가져오는 상황에서 빠른 응답을 위해 사용한다. 풀 테이블 스캔으로 데이터를 가져와서 정렬된 순서로 보여주려면, 엄청나게 큰 값을 끝까지 읽어 모두 불러서 메모리에서 정렬까지해서 줘야 한다. 사용자가 다 볼 것 같지 않다면 모든 데이터를 메모리에 올려 정렬하지 않는게 비용적으로 저렴하다. 특히나 DBMS는 서버처럼 메모리가 Out Of Memory가 나더라도 프로그램이 종료돼서 멈춰버리지 않는다. 어떻게든 디스크까지 활용해 느리게 처리하려고 하니 이 비용도 아낄 수 있다.

 

○ 인덱스 유니크 스캔

 논-클러스터링 인덱스를 설정할 때는 두 가지 방법이 있다. Unique 제약조건을 거는 방법과 Index를 거는 방법인데, 인덱스 유니크 스캔은 전자로 생성된 인덱스에 대해 사용된다. 이 경우 '=' 조건으로 단일 값만 찾는 경우 발생하며, 처리도 매우 빠르다. 꼭 단일 Unique가 아니라 여러 컬럼에 대한 Unique 제약 조건이 걸려있는 경우에도 선두 컬럼이 포함되어있다면 적용된다. 어렵지 않으니 이 이상은 생략하겠다.

 

○ 인덱스 스킵 스캔

  인덱스 컬럼이 여러 개인 경우, 선두 컬럼을 조건절에 사용하지 않으면 인덱스의 범위를 고르기가 어렵다. 만약에 성별 + 연봉으로 인덱스를 만든 경우, 연봉에 대한 범위는 매우 넓고 성별은 단 2개만 있다. 이 때, 연봉만으로 검색을 하는 경우, 성별의 개수가 별로 없으니 건너뛰기 쉽다. 특히 인덱스에서는 블럭을 보면 가장 왼쪽에 있는 값을 알 수 있다.

 

 친절한 SQL 튜닝 책의 예시를 보면, 쿼리를 다음과 같이 짰다고 해보자. 인덱스는 성별 + 연봉이다.

select * from 사원 where 연봉 between 2000 and 4000;

 

 우리는 사람이라 남자랑 여자만 있으니 사실 성별 '남' + 연봉 2000-4000이랑 '여' + 연봉 2000-4000을 Union all로 결합하면 될 것이라고 생각할 것이다. 그런데, 데이터베이스는 성별 조건을 빼버렸을때 이 내용을 모른다. 그러니 아래처럼 탐색을 하게 된다. 남자 800보다 낮은 값이 있는 블럭엔 제 3의 성이 있고 그 값이 2000-4000까지 있는 값이 있을 수 있으니 탐색한다. 2번 블럭을 탐색하지 않는 이유는 3번 블럭의 시작점이 1500부터이기에 2000보다 높은 값이 없을 것이기 때문이다. 이런 식으로 쭉 탐색한다. 남자도 8000이상은 탐색 안하는 이유는 그 다음 블럭이 남자가 아직 안 끝났기 때문이며, 남자 10000은 다음에 여자 3000이라는 범위 포함되는 값이 시작하는 지점까지 이어지기 때문이다. 마지막 블럭은 맨 처음 블럭을 탐색하는 이유와 같다. 

 

 

 

 이런 식으로 탐색하면 선두 컬럼이 조건에 포함되어있지 않지만 인덱스 풀 스캔을 하지 않더라도 효율적으로 엑세스할 수 있다. 또한 꼭 선두 컬럼이 없을 때만 하는 게 아닌 인덱스로 성별 + 부서 + 연봉일 떄에 성별과 연봉은 검색 조건에 있고 부서는 없는 경우에도 스킵 스캔을 사용하여 인덱스 범위 스캔보다 효율을 높이려고 한다.

 

○ 인덱스 패스트 풀 스캔

 사실 책에서 가장 이해가 안됐다. 인덱스를 사용해서 풀 테이블 스캔처럼 Multiblock I/O을 하는데, 데이터를 인덱스 논리 구조를 싹다 무시하고 디스크로 읽어서 인덱스 키 순서대로 정렬이 안되는 방식이란다. 그리고 병렬 처리와 파티션에 대해서 공부를 하지 않아서 정확한 내용을 모르겠다.

 

 검색해서 이해한 바에 따르면, 인덱스 패스트 풀 스캔을 사용할 떄에는 쿼리에 사용된 컬럼이 모두 인덱스에 함되어 있을 때만 사용이 가능하다. 이를 사용하면 테이블 전체가 아닌 인덱스 컬럼만 읽어 처리가 가능해 데이터 처리 양과 CPU 자원을 아낄 수 있다고 한다.

 

*** 파티션을 하지 않아도 병렬 쿼리가 가능하다는 말은 학습하고 이후에 다시 변경하도록 하겠다. ***

[ 참고 자료 ]

http://adminschool.net/doku.php?id=study:oracle:adv_owi_10g:oracle_internal_owi:buffer_cache_owi 

친절한 SQL 튜닝 - 조시형

SQL 레벨업 - 미크