이 글은 '친절한 SQL 튜닝(저자 조시형, 출판사 (주)DBian)을 읽고 정리한 글입니다.
1.SQL 튜닝
1. SQL이 느린 이유
SQL이 느린 이유는 대부분의 경우 I/O, 특히 디스크 I/O 때문인데 이 I/O는 프로세스의 일시적 중지(sleep)를 일으킵니다. 특히나 디스크 I/O는 메인 메모리 I/O에 비하면 매우 오래 걸립니다. 만약 동시 다발적으로 디스크 I/O 요청이 들어오면 I/O를 수행하는 시간에 대기시간 까지 추가되어 더 오래 걸리게 됩니다. 물론 I/O 요청이 발생하면 DBMS가 우선적으로 메인 메모리에 있는 캐시를 탐색하고 여기서 같은 내용에 대한 I/O를 찾지 못한 경우에만 실제 디스크 I/O 요청을 수행하기 때문에 실제 디스크 I/O로 이어지는 것을 많이 줄일 수 있지만, 캐시 hit ratio가 100일 수는 없기 때문에 SQL 튜닝을 통해 디스크 I/O를 줄이는 것이 여전히 중요합니다.
2. 디스크 I/O를 줄이려면?
디스크 I/O를 줄이는 방법을 설명하기에 앞서 논리적 I/O와 물리적 I/O에 대해서 알아보겠습니다. 논리적 I/O는 매모리 버퍼 캐시에 요청된 모든 I/O 개수이며(엄밀하게는 다르지만 거의 같은 개념으로 봐도 무방합니다) 물리적 I/O는 논리적 I/O 중에서 메모리 캐시에서 원하는 block(데이터 I/O 최소 단위이며 page라고 하기도 합니다.)을 찾지 못하고 실제 디스크 액세스로 이어진 것들을 말합니다. 실제 디스크 액세스 단계로 이어지면 디스크 암이 동작하며 물리적 움직임이 일어나기 때문에 메모리 캐시에서 읽어오는 경우에 비해 매우 느립니다.( 저장장치로 SSD를 사용하면 디스크암의 물리적인 움직임보다는 빠르겠지만 여전히 메인 메모리에보다는 매우 느립니다.) 요약하면, 전체 논리적 I/O 중 캐시 miss인 경우에 한해 실제 물리적 I/O가 일어납니다.
- 물리적 I/O = 논리적 I/O * cache miss ratio
위의 식을보면 결국 물리적 I/O 줄이려면 논리적 I/O를 줄이거나 캐시 miss를 줄여야한다는 결론이 나옵니다. 그런데 캐시 hit이나 miss는 실행 환경에 따라 달라지는 외부 요소이고 온라인 서비스 트랙잭션인 경우에는 대부분의 경우 hit ratio 99%를 달성하기 때문에, 전체 물리적 I/O를 줄이기 위해 SQL 작성자가 해야할 일은 결국 '논리적 I/O'를 줄여야하는 것입니다. 그래서 많은 SQL 튜닝 방법들은 이 논리적 I/O를 어떻게 줄이느냐가 핵심이고 그 방법에 따라 몇 가지로 나뉘게 됩니다. 논리적 I/O를 줄일 수 있는 여러 방법 중에서 이 글에서는 인덱스를 이용하는 방법에 대해 알아보겠습니다.
2.인덱스 튜닝
1. 인덱스란
인덱스란, 테이블에서 지정한 몇 개의 칼럼만을 사용하여 미리 테이블 레코드를 정렬시켜 놓은 정보입니다. 마치 책 마지막 페이지에 가나다 혹은 abc순으로 단어 목록을 만들어 놓은 색인(index)과 비슷합니다. 만약에 색인이 없으면 내가 찾고싶은 단어가 나오는 부분을 책에서 찾을 때 시작부터 끝까지 순차적으로 찾아봐야 합니다. DBMS에서는 이러한 방식을 '테이블 풀 스캔(table full scan)'이라고 합니다. 원하는 데이터의 위치를 알고 찾아가는 것과 모르고 처음부터 순차적으로 찾는 것에는 속도의 차이가 있겠죠?
인덱스는 특정 컬럼 기준으로 정렬돼있다고 했는데요, 이 정렬된 속성을 얼마나 잘 이용하는 가가 인덱스 튜닝의 핵심입니다. 이를 잘 활용해 전체 테이블에서 필요한 부분만 스캔을 하고 거기에 더해 레코드들의 정렬된 순서 그대로를 잘 활용하여 추가적인 정렬 연산을 줄일 수 있도록 인덱스와 조건절을 잘 구성하는 것이 인덱스를 잘 활용한 SQL 튜닝이라고 할 수 있겠습니다.
2. 인덱스 구조와 인덱스 스캔 원리
인덱스는 B+tree 구조로 돼있고(B+트리에 대해서는 여기서 설명하지는 않겠습니다.) 이 트리 구조에서 실제 원하는 테이블 레코드로 찾아갈 수 있는 주소 정보는 트리 맨 아랫단에 있는 리프 노드에 저장돼있습니다. 따라서 인덱스를 통해 원하는 레코드를 찾으려면 먼저 그 레코드의 주소 정보를 가진 리프 노드를 찾아야 합니다. 이를 '수직적 탐색'이라고 부릅니다. 인덱스 칼럼들이 정렬돼있다는 성질을 이용하면 트리 상에서 불필요한 노드들을 순회할 필요 없이 빠르게 원하는 리프 노드로 수직적 탐색을 수행할 수 있습니다.
만약 조건절이 하나의 레코드를 특정하는게 아니라 between 이나 like 같이 범위에 해당한다면, 일단 범위 맨 끝에 해당하는 레코드 정보를 가진 리프 노드를 찾고나서 그 리프 노드 내의 레코드들을 순차적으로 읽으면 됩니다. 이 역시 인덱스가 정렬돼있기 때문에 가능합니다. 이를 '수평적 탐색'이라고 부릅니다. 만약 조건절 범위가 한 리프 노드내에서 끝나지 않고 넘어간다 하더라도 B+tree에서는 인접한 리프 노드들 끼리는 양방향으로 연결돼있기 때문에 다음 리프 노드로 바로 접근할 수 있습니다. 아래 그림은 인덱스 구조를 개략적으로 나타낸 것입니다.
3. 무조건 인덱스를 사용하는 것이 낫다?
인덱스 튜닝 방법에 대해서 알아보기 전에 먼저 한 가지 짚고 넘어가야할 것이 있습니다. 위에서 설명한 내용을 보면, 인덱스를 사용하면 스캔할 대상들을 대폭 줄일 수 있으니 무조건 인덱스를 사용하는 것이 table full scan에 비하면 빠를 것 처럼 보이지만 사실 항상 그런 것은 아닌데 그 이유는 데이터를 읽어오는 방식 차이 때문입니다. 간단하게 설명드리면 tabel full scan은 sequential access 와 multi block I/O 방식으로, 인덱스를 활용하면 random access와 single block I/O 방식을 사용합니다. 그러면 잠깐 sequential access, random access, multi block I/O와 single block I/O에 대해서 알아보고 가겠습니다.
앞서 설명한 것 처럼 읽고자 하는 block이 메모리 캐시에 없으면 I/O call을 통해서 디스크에서 원하는 block을 읽어 캐시에 적재하게 됩니다. 이 때 한 번에 한 block 을 요청하면 single block I/O, 여러 블럭을 요청하면 multi block I/O 입니다. 그리고 데이터가 저장된 물리적 순서와 상관 없이 원하는 레코드의 주소 정보를 가지고 바로 접근하는 것은 random access라고 하고 데이터의 저장 순서대로 순차적으로 읽는 것은 sequential access라고 합니다.
인덱스를 사용하면 수직적 탐색을 통해 인덱스 리프 노드를 찾고 여기서 찾아낸 각 레코드 접근 정보를 활용하여 random access를 합니다.(여기서 예외적인 경우가 있는데, 만약 조건절의 모든 칼럼이 인덱스 칼럼이고 select 절의 모든 칼럼 또한 인덱스 칼럼이라면 굳이 레코드에 까지 접근해서 더 확인해야할 값이 없기 때문에 random access를 하지 않고 인덱스 트리 탐색만으로 끝날 수도 있습니다.) 불행한 것은 여기서 연속된 레코드라도 각각 random access를 따로 수행한다는 것입니다. 만약 캐시에 원하는 레코드를 포함한 block이 있으면 그나마 낫지만 없으면 하나의 레코드를 위해 그 레코드를 포함한 block에 대한 디스크 I/O 요청을 해야합니다. 또한 만약 원하는 레코드 여러 개가 하나의 block 안에 있다고 하더라도 각각 random access를 수행하기 때문에 같은 block 을 여러 번 읽어야합니다.(물론 최초에만 디스크 I/O를 수행하고 그 뒤론 메모리 캐시에서 읽습니다.) 예를 들어 테이블 전체 데이터를 읽는 데 이 방식을 사용하면 한 block에 500개의 레코드가 있는 경우에는 테이블을 구성하는 모든 블럭을 500번씩 읽게 됩니다.
반면 많은 block을 읽는 table full scan에서는 데이터의 물리적 순서에 따라 sequential access를 수행하는데 이때에는 내가 찾는 레코드가 포함된 block을 개별적으로 읽어오지 않고 multi block I/O 방식을 사용해 한 번에 많은 block을 읽어옵니다. 한 번에 많이 읽는 만큼 I/O 요청 수를 줄일 수 있기 때문입니다. 원하는 레코드의 위치 정보를 인덱스에서 먼저 찾고, 그 정보를 통해 레코드가 저장된 block에 random access를 하는 구조가 아니기 때문에 인덱스를 사용할 때처럼 수평, 수직 탐색 시간도 없고 같은 block을 여러 번 읽는 비효율도 발생하지 않습니다.
위에서 설명한 것 처럼 인덱스를 활용할 때에는 어느정도의 비효율이 따라옵니다. 그래서 읽어야하는 데이터 양이 많은 배치 프로그램이나 집계 SQL 같은 것들은 인덱스를 사용하면 오히려 느리기 때문에 select 절의 SQL 힌트를 활용해 table full scan을 사용하도록 유도하는 방식으로 튜닝을 진행하기도 합니다.(튜닝에 사용할 수 있는 SQL 힌트들은 그 외에도 많이 있으나 모두 설명하기에는 너무 많아서 이 글에서는 설명하지 않겠습니다.) 반면에 수백~수천만 건의 데이터에서 수십, 수백 건의 소량 데이터를 찾는 경우에는 table full scan을 쓰면 읽은 데이터의 절대 다수가 버려지기 때문에 이런 경우에는 인덱스를 잘 활용해서 원하는 테이블 레코드들만 빠르게 찾는 것이 효율적입니다. 온라인 서비스에서 사용되는 트랜잭션들 대부분이 여기에 해당합니다.
4. 인덱스를 활용한 튜닝 1 - 인덱스가 활용되지 않는 경우
인덱스를 잘 활용한다는 것은 앞서 말씀드린 인덱스의 정렬된 성질을 이용하여 테이블 전체를 스캔하지 않고 원하는 시작점, 끝점을 찾아 그 범위(range) 만큼만 스캔한다는 것입니다. 이러한 스캔 방식을 index range scan이라고 부릅니다. 인덱스를 통해 값을 탐색한다 해도 인덱스를 제대로 활용하지 못하면 매번 인덱스 리프 노드 전체를 탐색하게 되는데요, 이것은 index full scan이라고 합니다. 어차피 인덱스를 활용하기만 하면 그게 그거 아니냐고 하실 수도 있는데요, 만약 조건절에 있는 칼럼들이 모두 인덱스 칼럼들이면 그나마 낫지만 조건절에 있는 칼럼 중에 인덱스 칼럼이 아닌 칼럼이 있다면 전혀 다른 얘기가 됩니다. 해당 칼럼에 걸린 조건에 맞는 레코드인지 아닌지 확인하기 위해 random access가 일어나야하고 이 random access는 생각보다 오버헤드가 크기 때문입니다. random access를 통해 테이블에서 인덱스가 아닌 칼럼 값까지 확인했는데 조건에 맞지 않아 필터링되는 레코드가 대부분이라면 탐색 비효율이 심각해집니다. 경우에 따라서는 select 절의 모든 칼럼을 인덱스에 포함시켜 table access를 막고 인덱스만 읽고 처리하게 할 수도 있습니다.(이를 covered 쿼리라 하고 여기에 사용된 인덱스를 covered 인덱스라고 합니다.)
인덱스의 정렬된 성질을 활용하지 못하게 되는 경우는 어떤 경우가 있을까요? 예를 들어 보겠습니다. 어느 학교 전교생의 성명, 생년월일로 구성된 테이블에 생년월일 컬럼만으로 구성된 인덱스가 있습니다. 생년월일은 '20120413' 과 같은 형태로 저장됩니다. 여기서 2013년 3월 1일 이후로 태어난 모든 학생 레코드를 찾는 다면 index range scan을 할 수 있습니다. 생년월일 순으로 정렬돼있으니 range 의 시작점과 종료 점을 알 수 있으니까요. 여기서 종료 점은 마지막 리프 노드에 있을 것입니다. 그런데 만약 5월에 태어난 학생 레코드를 찾기 위해서,
where substr(생년월일, 5, 2) = '05'
와 같은 조건을 사용한다면 어떻게 될까요? 여기서는 range scan 사용이 불가능합니다. 왜냐하면 인덱스는 생년월일순으로 정렬돼있지 'substr(생년월일, 5, 2)'을 기준으로 정렬돼있지 않기 때문에 인덱스에서 스캔 시작점과 끝점을 찾을 수 가 없고 리프 노드 처음부터 끝까지 모두 스캔해야합니다. 5월생들은 같은 년도 레코드 내에서는 모여있겠지만 전체적으로 봤을 때는 듬성듬성 존재하기 때문에 정렬된 성질을 제대로 사용할 수 없게 되는겁니다. 이렇게 조건절에서 인덱스 칼럼을 사용했다고 해도 그 칼럼을 그대로 사용하지 않고 가공해서 사용하면 index range scan을 사용할 수 없게 됩니다.
다른 예를 볼까요? 주문 정보를 담은 테이블에서 인덱스 칼럼이 주문 수량 칼럼일 때,
where nvl(주문 수량, 0) < 100
과 같은 조건을 사용해도 마찬가지입니다. 인덱스는 주문 수량 기준으로 정렬돼있지 nvl(주문 수량, 0)을 해석한 'null 이면 값을 0으로 치환한 주문 수량'을 기준으로 정렬돼있지 않기 때문입니다. 그 외 몇 가지 예를 더 들자면, '칼럼 like %키워드%' 와 같이 like 로 중간 값을 검색했을 때, OR 조건으로 검색할 때, 또는 IN 조건(사실상 or 조건의 다른 형태입니다.)으로 검색할 때 등이 있습니다.
앞에서 말씀 드린 index range scan이 사용되지 않는 조건들 중에서 IN 조건의 경우 SQL 옵티마이저(DBMS 내에서 SQL 최적화를 담당하는 부분)가 IN-List Iterator 로 불리는 방식을 사용하여 IN List 내 값의 개수 만큼 index range scan을 수행한 후 union all 하도록 최적화한다고 합니다. 또한 OR 조건도 select 절에 use concat 이라는 힌트를 추가하면 각 OR 조건 값(A OR B OR C 에서 A, B, C)에 대한 index range scan 후 union all 하여 결합하는 방식으로 유도할 수 있다고 합니다. 이를 OR Expansion이라고 합니다.
인덱스 칼럼을 직접적으로 가공하진 않았지만 의도치 않게 가공되는 경우도 있는데요, 자동 형변환이 되는 경우가 그렇습니다. 조건절 좌, 우변 값의 타입이 다르면 DBMS에 따라 에러를 발생시키기도 하고 자동으로 형변환을해서 비교하는 경우가 있는데요(오라클), 후자의 경우에서 만약 아래와 같은 조건절이 사용되면
SELECT *
(생년월일 칼럼의 타입은 varchar)
자동으로 형변환이 되어 SQL이
SELECT *
WHERE TO_NUMBER(생년월일) = 19920511
과 같이 바뀔 수 있습니다. (DBMS의 타입 우선순위에 따라 다를 수 있습니다.)
가공된 인덱스 칼럼이 조건 절이 아니라 ORDER BY 절에서 발생하면 어떻게 될까요? 이런 경우도 문제가 생기는데 기껏 인덱스를 통해 정렬된 순서대로 잘 가져온 데이터를 '가공된 인덱스 칼럼 값' 기준으로 다시 또 정렬하게 됩니다. 예를 들어 어떤 테이블의 인덱스를 A, B, C 순으로 구성했을 때 SQL을 아래와 같이 작성하면 index range scan을 통해 정렬된 상태로 가져온 데이터들을 다시 정렬하게 됩니다.
SELECT *
FROM 어떤 테이블
WHERE A = 'X'
ORDER BY B || C 정렬된 성질을 잘 활용하여 정렬 연산을 생략하기 위해서는 ORDER BY 절을 생략하거나
ORDER BY 변경일자, 변경순번
과 같이 ORDER BY 절을 인덱스 칼럼 순서로 변경하면 정렬을 추가적으로 수행하지 않습니다.
5. 인덱스를 활용한 튜닝 2 - 인덱스 튜닝/설계 원칙
4절에서 인덱스가 활용되지 않는 경우를 설명 드렸는데요, 좋은 인덱스를 설계하기 위해서는 4절에서 나온 경우들을 피해서 인덱스를 설계하면 됩니다. 물론 그 외에도 잘 활용되는 인덱스를 만들기 위해 유의해야할 점은 여러가지가 있겠지만, 큰 틀에서 두 가지로 압축할 수 있습니다.
- 조건절에서 항상 혹은 매우 자주 사용되는 칼럼을 찾는다.
- 그 칼럼들 중에서 between이나 like, 부등호 연산(<, <= 등)과 같은 범위 조건이 아니라 '=' 조건으로 자주 사용되는 칼럼을 인덱스 선행 칼럼에 두는 것이 좋다.
insert나 delete과 같은 DML 연산이 수행되면 인덱스 역시 테이블의 레코드의 추가, 삭제 상황에 따라 업데이트를 해줘야합니다. 그래서 인덱스가 너무 많아지면 insert나 delete 연산 수행 시간이 늘어나기 때문에 인덱스를 무한정 많이 만들 수는 없고 조건절에서 사용되는 빈도가 높은 칼럼들로 적절히 인덱스를 구성해야합니다.
쉬운 이해를 위해 예를 들어 설명 드리면 A, B 가 인덱스 칼럼인 테이블에서 데이터를 조회할 때
where A = 1 and B >= 1
와 같은 조건절을 자주 사용한다면, 인덱스 칼럼의 순서는 (B, A)가 아니라 (A, B)가 돼야한다는 것입니다.
인덱스 칼럼의 순서는 왜 중요할까요? 그 이유는 인덱스 내 선행하는 칼럼이 범위 조건일 경우, 후행하는 칼럼들은 인덱스 스캔 범위를 거의 줄이지 못하기 때문입니다. 인덱스는 인덱스 칼럼의 순서대로 정렬 우선 순위를 갖습니다. 그래서 후행 칼럼의 정렬은 선행 칼럼 값이 같을 때만 보장됩니다. 즉 선행 칼럼이 범위 조건이 돼버리면 스캔이 진행되면서 선행 칼럼 값이 계속 변하기(A: 1 -> 2) 때문에 후행 칼럼은 정렬이 보장되지 않습니다. 정렬이 보장되지 않으면 index range scan의 range를 줄일 수가 없습니다. 후행 칼럼 조건에 상관 없이 선행 칼럼 조건 범위 만큼은 다 스캔해야 합니다. 선행 칼럼 범위만큼 range scan하면서 마주치는 인덱스 레코드를 필터링하는 조건으로는 사용되나, 전체 스캔 range를 줄이지는 못합니다. 예를 들어 위의 예시에서 (A, B)로 인덱스를 구성한다면 레코드 순서는 아래 표와 같을 것입니다.
| A | B |
| 1 | 2 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 2 |
| 2 | 4 |
| 2 | 4 |
위와 같은 정렬 상태에서 where A = 1 and B >= 1 조건절을 만족하는 레코드를 찾으려면 맨 위 세 레코드만 읽으면 됩니다. 그런데 만약에 아래와 같이 인덱스를 (B, A)로 구성하면 어떻게 될까요?
| B | A |
| 1 | 2 |
| 2 | 1 |
| 2 | 1 |
| 2 | 2 |
| 3 | 1 |
| 4 | 2 |
| 4 | 2 |
이 경우 맨 위에서 맨 아래까지 모두 스캔해야합니다. B가 가질 수 있는 값 모두(1, 2, 3, 4)에 대해서 각각 A = 1인 레코드를 찾아야하니까요. 이렇게 조건절에서 특정 인덱스 칼럼이 범위 조건으로 사용되면 그 칼럼보다 후행하는 인덱스 칼럼들은 scan range를 줄이지 못합니다.
덧붙여, 앞서 4절에서 인덱스 칼럼이 가공되면 index range scan이 수행되지 않는다고 말씀드렸는데요, 사실 더 정확하게 얘기하면 가공된 칼럼은 위에서 설명한 것과 같이 "scan range를 줄이지 못한다" 가 맞습니다. 그래서 4절에서 처럼 조건절에서 단독으로 사용된 인덱스 컬럼이 가공돼버리면 scan range를 줄일 수 있는 조건이 하나도 없기 때문에 index range scan이 제대로 수행되지 않는 것입니다. 그런데 만약에 4절의 첫 번째 예시에서 인덱스가 (성명, 생년월일)이고 조건절이
where substr(생년월일, 5, 2) = '05'
and 성명 like '김%'
이면 어떨까요? 네, index range scan이 사용됩니다. 선행 칼럼인 성명 칼럼으로 인덱스 리프 노드 상의 scan range를 정할 수 있으니까요. 하지만 성명이 '김'으로 시작하는 학생들은 모두 다 뒤져봐야 합니다. 왜냐하면 가공된 인덱스 칼럼(여기서는 substr(생년월일, 5, 2))은 성명이 '김'으로 시작하는 학생 레코드 안에서 scan range를 거의 줄일 수 없기 때문입니다.
1), 2)에서 설명한 내용은 일반적으로 사용할 수 있는 기본 원칙인 것은 맞지만 항상 적용할 수 있는 것은 아닙니다. 이미 운영 중인 환경에 인덱스 칼럼을 변경하는 것도 쉽지 않고, 앞서 말한 것과 같이 insert나 delete 성능에 영향을 주기 때문에 추가적으로 인덱스를 만드는 것도 쉽지 않습니다. 또한 경우에 따라 scan range 효율 외에도 아래와 같은 고려할 사항들이 있을 수도 있습니다.
- 쿼리 수행 빈도
- 업무상 중요도
- 클러스터링 팩터
- DML 부하
- 그 외...
설계자의 성향, 스타일과 운용 상황에 따라 우선 고려해야할 요소들이 달라질 수 있기 때문에 같은 테이블에도 인덱스 설계가 달라질 수 있습니다. 또한 이미 운용중인 테이블의 인덱스를 바꾸는 것은 이미 사용중인 다른 쿼리에 주는 영향도를 생각해야하기 때문에 말처럼 쉽지는 않다고 합니다. 그래서 사실 설계 단계에서 여러 요소들을 잘 고려해서 인덱스를 설계해야 유지, 보수에 들어가는 비용을 줄일 수 있습니다.