데이터베이스를 사용하면서 성능 문제로 어려움을 겪고 있는 사이트가 지금도 존재한다. 그리고 과거에 이러한 성능 문제를 경험해본 사이트도 적지 않을 것이다. 과거와 달리 지금은 많은 데이터베이스들이 TB 이상의 대용량 데이터베이스로 변화하고 있으므로 이러한 성능 저하 현상은 더욱 심화되고 있다. 이에 대한 해결 방법은 다양하지만 변함없이 그 중심에는 데이터베이스를 엑세스하는 SQL이 있다. 지금은 성능을 고려한 SQL 최적화에 시간과 관심을 아끼지 말아야 할 때이다.
악성 프로젝트를 주로 담당해온 필자는 다수의 악성 프로젝트를 지원하면서 악성 프로젝트의 몇 가지 공통점을 발견하게 되었다. 악성 프로젝트의 공통점 중 하나는 시스템 오픈시 항상 성능 저하의 문제를 동반하는 경우가 많다는 것이다. 성능 저하의 문제는 고객과의 신뢰를 무너뜨리게 되며 이와 같은 성능 저하는 수일 만에 해결할 수 있는 문제가 아니다. 많은 시간과 비용이 투자되어야만 비로소 성능 문제를 해결할 수 있다.
그렇다면 이와 같은 성능 문제는 왜 발생하는 것일까? 아마도 여러 가지 요소에 의해 성능 문제가 발생할 것이다. 그 중에서도 데이터베이스를 액세스하는 SQL이 최적화되지 못해 발생하는 경우가 다수를 차지한다. 최적화되지 않은 SQL에 의해 성능이 저하될 수 있음은 누구나 공감하는 부분일 것이다. 그렇다면 왜 SQL은 데이터베이스 시스템의 성능을 저하시키는 주범이 되는 것일까? 이제부터 SQL이 데이터베이스 시스템의 성능을 저하시키는 원인을 확인해 보고 이에 대한 최적화 방법을 확인해 보자.
최적화되지 않은 SQL은 왜 성능을 저하시키는가?
최적화되지 않은 SQL은 분명히 성능을 저하시키게 된다. 그렇다면 왜 최적화되지 않은 SQL은 성능을 저하시키는 것일까?
- 불필요한 데이터 블록의 액세스 증가
- 자원 사용 증가
위와 같은 두 가지 이유에 의해 해당 SQL은 성능 저하를 발생시키게 된다. 그럼 앞서 언급한 각각의 항목에 대해 확인해 보자.
첫 번째로 불필요한 데이터 블록의 액세스 증가에 대해 확인해 보자. SQL의 성능은 해당 SQL이 액세스하는 데이터 블록의 양에 의해 좌우된다. 예를 들어 우리가 인터넷을 통해 파일을 다운로드한다고 할 때, 1MB 크기의 파일을 다운로드하는 경우와 100MB 크기의 파일을 다운로드하는 경우의 속도 차이는 어떠한가? 누가 보더라도 1MB 파일을 다운로드하는 것이 비교할 수 없을 만큼 빠르다는 것을 알 수 있다. 그럼 동일한 내용을 담고 있는 1MB 크기의 파일과 100MB 크기의 파일 가운데 하나를 골라 다운로드한다면 어떤 파일을 다운로드하겠는가? 당연히 속도가 빠른 1MB 크기의 파일을 다운로드할 것이다. 혹자는 같은 내용을 저장하고 있는 1MB 크기의 파일이 과연 100MB 크기의 파일이 될 수 있겠느냐고 따질 수 있지만 데이터베이스에서 이는 흔하게 발생할 수 있는 현상이다.
이와 같은 이유에서 우리는 SQL을 최적화해야 한다. 대용량 데이터베이스에서는 1MB와 100MB의 차이가 아니라, 1MB와 10GB 이상의 차이가 발생하는 경우도 흔하다. 결국 SQL을 최적화하는가 아닌가는 1MB를 액세스하는가 아니면 10GB를 액세스하는가의 차이를 발생시킬 수도 있다. 10GB를 액세스한다면 과연 1초 만에 결과를 데이터베이스에서 추출할 수 있겠는가? 이는 디스크 성능이 매우 좋고 CPU가 많은 최신의 시스템이라 할지라도 온라인으로 사용할 수 없는 SQL이 된다. 또한 이와 같은 SQL이 수행된다면 해당 애플리케이션뿐만 아니라 해당 시스템에서 수행되는 모든 애플리케이션에 영향을 미치게 될 것이다.
두 번째로 자원 사용 증가에 대해 확인해 보자. 자원 사용의 증가는 여러 가지 현상으로 나타나게 된다. 이 중 최적화되지 않은 SQL에 의해 소비되는 자원은 어떤 것이 있을까? 다음과 같은 자원들은 최적화되지 않은 SQL에 의해 낭비될 수 있는 자원이다.
- CPU 사용
- 메모리 사용
- 디스크 I/O 및 공간
해당 항목들을 확인해 보면 시스템을 구성하는 대부분의 중요 자원이 낭비될 수 있다는 의미가 된다. 최적화되지 않은 SQL에 의해 CPU를 더 많이 사용하게 되며 이는 CPU 평균 사용률을 증가시키는 역할을 한다. CPU 평균 사용률이 높아진다는 것은 무엇을 의미하는가? CPU 사용률은 대부분의 회사에서 용량 증설의 기준이 되는 수치로 사용하는 경우가 많다. 용량 증설이라는 것은 해당 시스템에 CPU, 메모리 등을 증설하는 것을 의미한다.
그렇다면 최적화되지 않은 SQL과 용량 증설에는 어떤 관계가 있을까? 최적화되지 않은 SQL이 많다면 해당 시스템의 CPU 사용률은 높아지며 이러한 현상은 CPU 평균 사용률을 증가시키게 된다. 따라서 이는 자연스럽게 시스템 용량 증설을 계획하게 만든다. 과연 이와 같이 용량을 증설하는 것이 올바른 처리일까? 최적화되지 않은 SQL을 최적화한다면 용량 증설을 계획 중인 많은 시스템은 용량 증설을 수행하지 않아도 될 것이다. 용량 증설을 하지 않는다면 용량 증설에 사용할 회사의 비용이 절감되며 해당 비용은 회사의 발전을 위해 다른 부분에 사용될 수 있을 것이다. 최적화되지 않은 SQL에 의해 회사의 비용을 잘못된 곳에 사용하게 만드는 경우도 종종 발생하게 된다.
아직도 많은 곳에서는 이러한 사실을 인지하지 못하고 데이터베이스 시스템에 대해 무조건 용량 증설을 시도하고 있다. 이제라도 해당 시스템에서 수행되는 모든 SQL이 최적화되어 있는 상황임에도 용량 증설을 해야 하는 상황인지 아니면 최적화되지 않은 SQL에 의해 용량 증설을 고민해야 하는 상황인지를 정확하게 판단하길 바란다.
메모리 사용량은 어떠한가? 메모리는 많은 양의 데이터를 정렬하는 SQL 또는 데이터 블록을 많이 액세스하는 최적화되지 않은 SQL에 의해 과다하게 사용될 수 있다. 이와 같은 현상이 발생한다면 메모리 자원에 대한 경합에 의해 성능 저하가 발생하는 것은 당연한 일이다.
디스크 I/O 및 공간 사용량 또한 최적화되지 않은 SQL에 의해 낭비되기 쉽다. 디스크 I/O의 경우 SQL이 최적화되어 100개의 데이터 블록만 액세스하면 결과를 추출할 수 있는 경우임에도 SQL이 최적화되지 않아 10,000개의 데이터 블록을 액세스하는 경우가 있다. 이때 해당 SQL만 최적화해도 디스크 I/O가 무려 1% 수준으로 줄어들게 될 것이다.
만일 이와 같은 현상이 왜 발생하는지를 묻는 이가 있다면 그는 분명 SQL에 대해 충분히 이해하지 못하는 사람일 것이다. SQL은 많은 데이터 중에서 원하는 데이터만을 추출하기 위해 많은 방법을 이용할 수 있게 한다. 동일한 결과를 추출하는 많은 방법 중에서 어떤 방법은 100개의 데이터 블록을 액세스하는가 하면 또 다른 방법은 동일한 결과를 추출하기 위해 10,000개의 데이터 블록을 액세스할 수도 있다. 이와 같으므로 최적화되지 않은 SQL에 의해 디스크 I/O는 증가하게 된다. 그렇다면 디스크 공간 사용은 어떻게 되는가? SQL 최적화는 인덱스와 분리해 생각할 수 없다. SQL의 최적화 과정에는 반드시 인덱스의 최적화도 수반된다. 인덱스가 최적화되지 않으면 어떤 현상이 발생하는가? 아마도 테이블에 불필요한 인덱스가 증가하게 될 것이다. 인덱스는 세그먼트이므로 인덱스를 구성하는 데이터가 실제 디스크에 저장된다. 따라서 불필요한 인덱스가 많으면 디스크 공간 사용률이 증가하게 되고, 그에 따라 디스크 공간은 불필요한 인덱스에 의해 낭비된다.
최적화되지 않은 SQL은 불필요한 데이터 블록의 액세스와 불필요한 자원의 사용에 의해 성능 저하를 발생시키게 된다. 그렇다면 과연 어떻게 SQL을 작성해야 이와 같은 불필요한 블록 액세스를 제거하고 자원을 효율적으로 사용하도록 SQL을 최적화할 수 있을까?
인덱스의 사용 유무를 결정하라
SQL을 작성하기 전에 해당 SQL이 인덱스를 사용해야 할지 아니면 사용해서는 안 되는지를 결정해야 한다. 우리가 쉽게 범하는 오류 가운데 하나가 바로 인덱스의 사용이다. SQL은 반드시 인덱스를 이용해 데이터를 액세스해야 하며 인덱스를 이용하지 않은 SQL은 최적화되지 않은 SQL이라고 단정하는 경우가 많다. 하지만 이와 같은 생각은 SQL을 더욱 최적화하기 힘들게 만드는 우리의 고정관념일 것이다.
그렇다면 어떤 기준을 가지고 해당 SQL이 인덱스를 사용해야 할지 아니면 인덱스를 사용하지 말아야 할지 구분할 수 있을까? 여기서 하나의 예를 들어보자. 필자가 어느 사이트의 지원 업무를 맡았을 때의 일이다. 해당 사이트에서는 <리스트 1>과 같은 SQL에 의해 CPU 사용률이 높아지면서 성능 저하가 발생하고 있었다.
<리스트 1>의 SQL은 매우 간단한 SQL일 수 있다. 이와 같은 SQL을 보는 순간 우리는 DT 컬럼에 인덱스를 생성한 후 DT 인덱스를 이용해 TEST 테이블을 액세스하고자 할 것이다. 해당 SQL에 대해 인덱스를 이용해 액세스하는 것이 가장 최적의 데이터 액세스 방법인가?
그렇다면 위의 SQL은 성능 저하를 발생시키지 않을 것이다. 물론 해당 사이트에서도 DT 컬럼에 인덱스를 생성하고 해당 인덱스를 이용해 SQL을 수행하고 있었다.
그렇다면 무엇이 문제이기에 <리스트 1>의 SQL은 성능 저하를 발생시킨 것일까? 이 예제를 통해 SQL이 인덱스를 이용해야 할지 아니면 인덱스를 이용하지 말아야 할지를 판단하는 기준을 제공할 수 있다. TEST 테이블은 대용량 테이블이며 2개월간의 데이터를 저장하고 있었다. 그렇다면 <리스트 1>의 SQL은 어떤 현상이 발생하겠는가? DT 인덱스를 액세스하는 순간 전체 데이터 중 50%를 액세스해야 할 것이다. 인덱스에서 전체 데이터 중 50%를 액세스하는 것이 어려운 게 아니라 이후에 발생할 작업에 의해 성능 저하를 발생시키게 된다. 예로 든 SQL은 인덱스 액세스 후에 테이블을 액세스해야 할 것이다. 이는 SELECT 절의 COL1, COL2, COL3, COL4, COL5 컬럼은 인덱스에 없기 때문에 테이블을 액세스해서 해당 컬럼 값을 추출해야 결과 데이터를 추출할 수 있게 된다.
이와 같이 인덱스를 액세스한 후 테이블을 액세스하는 현상을 랜덤 액세스라고 하며 랜덤 액세스는 데이터 한건 한건에 대해 I/O를 발생시키게 된다. 결국 해당 테이블의 데이터 중 50%에 대해 인덱스 액세스를 한 후에 테이블을 액세스하는 I/O가 발생해 해당 SQL은 성능 저하가 발생하게 된다.
사전의 예를 들어보자. 사전에 100만 개의 단어가 저장되어 있고 우리가 찾고자 하는 단어는 그 중 70만 개의 단어라고 가정하자. 사전의 옆에 있는 인덱스를 이용한다면 70만 번 인덱스를 이용해야 한다. 만약 사전의 인덱스를 이용하지 않는다면 어떻게 원하는 단어를 찾을 수 있을까? 사전의 처음부터 읽기 시작해서 맨 끝까지 읽고 나서야 원하는 모든 단어를 찾을 수 있을 것이다. 과연 70만 번의 인덱스를 이용해 70만 개의 단어를 확인하는 것과 사전을 처음부터 읽어 100만 개의 단어에서 70만 개의 단어를 확인하는 것 중 어느 것이 더 효과적이겠는가? 당연히 처음부터 사전을 읽어 100만 개의 단어 중 70만 개의 단어를 추출하는 방법이 더 효과적이고 빠른 속도를 보장할 수 있다.
데이터베이스의 인덱스도 사전의 인덱스와 별반 다르지 않다. 해당 테이블에서 많은 데이터를 추출하는 과정에서 인덱스를 액세스한 후에 테이블을 액세스하는 것은 앞서 언급한 100만 건의 단어에서 70만 건의 단어를 사전에서 찾는 데 사전의 인덱스를 이용하는 것과 마찬가지이다. 결국 인덱스는 해당 테이블의 적은 데이터를 액세스하는 경우에만 이용해야 하며 테이블이 많은 데이터를 액세스하는 경우에는 테이블 액세스 없이 인덱스만을 이용하거나 아니면 테이블만을 액세스하는 것이 더 빠른 성능을 기대할 수 있다.
왜 이와 같은 현상이 발생하는 것일까? 모든 데이터는 DB의 블록에 저장되며 하나의 블록에는 여러 개의 데이터가 동시에 저장될 수 있으므로 100만 개의 데이터가 20만 개의 블록에 저장되어 있다고 가정하자. 또한 인덱스를 이용해 하나의 데이터를 테이블로부터 액세스하기 위해서는 인덱스 블록 하나와 데이터 블록 하나를 액세스하게 된다고 가정하자. 이와 같이 가정했을 경우에 인덱스를 액세스한 후에 테이블을 액세스하는 방법은 추출하고자 하는 데이터 한 건 한 건마다 인덱스 블록과 데이터 블록을 순서대로 읽어야 한다. 이는 추출하고자 하는 70만 개의 데이터에 대해 먼저 필요한 인덱스 블록을 모두 액세스한 후 필요한 데이터 블록을 한 번에 모두 액세스할 수는 없기 때문이다.
따라서 70만 개의 데이터를 인덱스를 이용해 추출하기 위해서는 70만 개의 인덱스 데이터가 저장되어 있는 인덱스 블록과 70만 개의 데이터 블록을 읽어야만 원하는 결과를 추출할 수 있다. 전체적으로 봐서는 70만 개 이상의 블록을 액세스하게 되는 셈이다. 또한 인덱스를 통한 데이터 액세스는 하나의 블록 액세스에 한 번의 I/O가 발생하는 단일 블록 I/O를 수행하므로 I/O로 계산한다면 70만 번 이상의 I/O가 발생하게 된다.
반면에 100만 개의 데이터를 인덱스를 이용하지 않고 읽는다면 100만 개의 데이터가 20만 개의 블록에 저장되어 있으므로 70만 개의 원하는 데이터를 추출하기 위해서는 단지 20만 개의 블록만 액세스하면 된다. 이와 같이 테이블의 처음부터 끝까지 액세스하는 경우에는 단일 블록 I/O가 아니라 한번 I/O에 여러 블록을 액세스하는 다중 블록 I/O를 수행하게 된다. 다중 블록 I/O를 통해 한 번의 I/O에 64개의 블록을 동시에 읽는다면 20만 개의 블록을 모두 읽기 위해서는 3,130번의 I/O를 수행해야 한다. 결국 20만 개의 블록을 액세스해서 100만 개의 데이터를 확인하게 되고 필요 없는 30만 개의 데이터를 버린다면 원하는 70만 개의 데이터를 추출할 수 있을 것이다.
우리는 SQL을 최적화한다고 하면서 항상 인덱스를 만드는 것에만 열을 올리는 경향이 강하다. 하지만 인덱스를 이용하는 방법이 더 큰 문제를 발생시킬 수 있으므로 SQL을 최적화하고자 한다면 가장 먼저 인덱스를 사용해야 할지 말아야 할지를 판단해야 한다.
많은 데이터를 액세스하는 경우에는 인덱스를 이용하지 않는 것이 더 유리하다는 것을 이해했을 것이다. 그렇다면 여기서 말하는 많은 데이터의 기준은 무엇일까? 이에 대한 수치는 절대적일 수 없다. 하지만 대략적으로 해당 테이블의 1~3% 이상의 데이터를 액세스하는 경우를 우리는 많은 데이터를 액세스한다고 말할 수 있다. 테이블에 데이터가 많으면 많을수록 해당 수치는 낮아지게 된다. 그렇기 때문에 어떤 테이블은 테이블의 1%만 인덱스를 이용해 액세스해도 성능을 보장받지 못하는 경우가 자주 발생하며 이와 같은 경우는 대용량 테이블에서는 흔히 볼 수 있다.
우리는 이미 테이블의 많은 데이터를 액세스하는 경우를 확인해 봤다. 테이블의 데이터 중 매우 적은 양의 데이터를 액세스하는 경우에는 앞서 언급한대로 인덱스를 이용하는 것이 유리한가? 이와 관련해 사전의 예를 들어 확인해 보자. 사전에서 ‘SCHOOL’이라는 단어를 찾는다고 가정하자. 그렇다면 우리는 많은 영어 단어 가운데 ‘SCHOOL’이라는 단어를 어떻게 찾을 수 있을까? 어느 누구나 사전의 인덱스에서 먼저 ‘S’를 찾은 후 그 다음 ‘C’를 찾는 방식으로 ‘SCHOOL’이라는 단어를 찾게 된다.
그럼 우리가 10개의 단어를 찾을 때는 어떻게 할까? 이런 경우에도 우리는 사전의 인덱스를 이용해 단어를 하나하나 찾을 것이다. 데이터베이스도 이와 동일하다. 해당 테이블에서 적은 데이터를 액세스하고자 한다면 성능을 보장받기 위해 반드시 인덱스를 이용해야 한다. 테이블의 데이터 중에서 적은 데이터를 추출하는 데 인덱스를 이용하지 않는다면, 이는 마치 ‘SCHOOL’이라는 단어를 사전의 인덱스를 이용하지 않고 1페이지부터 찾는 셈이 될 것이다.
<리스트 2>의 SQL에서 TEST 테이블은 50만 건의 데이터를 저장하고 있으며 DT 컬럼이 ‘20080701’을 만족하는 데이터는 1,000건이라고 가정하자. 또한 2008년 데이터는 10만 건이라고 가정하자. 그렇다면 위의 SQL은 어떻게 수행되어야 하는가? 앞의 예제에서 첫 번째 SQL은 해당 테이블에서 적은 데이터를 추출하므로 인덱스를 이용해 원하는 데이터를 추출해야 할 것이다. 하지만 두 번째 SQL은 테이블의 많은 데이터를 액세스하게 되므로 인덱스를 이용한다면 인덱스 액세스 후에 테이블을 액세스하게 되어 랜덤 액세스의 증가로 성능은 저하된다. 이처럼 유사한 SQL임에도 불구하고 어느 정도의 데이터를 추출하는가에 따라 인덱스를 이용할 수 있는 경우와 그렇지 못한 경우로 나뉘게 된다. 이 얼마나 놀라운 사실인가? 이와 같이 SQL과 인덱스의 관계를 이해하지 못한다면 우리는 더 이상 SQL을 최적화할 수 없을 것이다.
데이터 연결을 이해해라
데이터베이스의 테이블에서 데이터를 액세스하기 위해서는 SQL을 이용해야 한다. 데이터베이스에 하나의 테이블만 존재하는 경우는 없을 것이다. 논리적 모델링에 의해 하나의 데이터베이스 안에는 많은 테이블이 존재하게 된다. 그렇기 때문에 여러 테이블에 분산 및 저장된 연관 관계가 있는 데이터를 한 번에 추출하기 위해서는 여러 테이블을 한 번에 조회하는 데이터 연결을 사용해야 할 것이다. 보통의 경우 하나의 테이블을 조회하는 것보다는 여러 테이블의 데이터를 연결해 원하는 데이터를 추출하는 경우가 더 많다. 이와 같은 데이터 연결은 흔히 ‘조인’이라고 한다. 그렇다면 데이터 연결에는 어떤 방법들이 존재하는가?
- 일반 조인
- 서브쿼리
- 스칼라 서브쿼리
- 집합 연산자
위와 같은 방법을 통해 여러 개의 테이블에서 우리는 데이터를 연결할 수 있다. 그럼 데이터 연결 SQL의 예제를 살펴보자.
FROM 절을 이용해 TEST1 테이블과 TEST2 테이블을 연결할 수 있다. 이와 같이 FROM 절을 이용한 데이터 연결을 일반 조인이라고 한다. 일반 조인은 우리가 추출하고자 하는 데이터를 저장하고 있는 테이블들을 FROM 절에 설정하고 WHERE 절에 두 테이블 사이의 데이터를 연결할 수 있는 조건을 설정하는 것으로 모두 마무리될 것이다. 조인을 위한 SQL을 작성하는 것이 어렵지만은 않다. 어떻게 보면 누구나 작성할 수 있는 매우 간단한 SQL이다. 그렇다면 앞의 SQL은 무조건 성능을 보장받을 수 있는 것일까?
<리스트 3>의 SQL이 성능을 보장받기 위해서는 내부적으로 어떻게 수행되는지가 가장 중요하다. 그렇다면 앞의 일반 조인 SQL은 어떻게 수행될 수 있을까? 해당 SQL이 수행될 수 있는 모든 경우의 수를 확인해 보자.
- A 테이블 액세스 후 B 테이블 액세스
- B 테이블 액세스 후 A 테이블 액세스
해당 SQL은 위와 같이 두 가지 방법에 의해 수행될 수 있으며 이를 조인 순서라고 한다. 또한 각각의 테이블을 액세스해서 데이터 연결을 수행하기 위해서는 데이터 연결을 위한 조인 방식을 사용하게 되며 조인 방식에는 일반적으로 다음과 같이 세 가지가 있다.
- 중첩 루프 조인(Nested Loop Join)
- 해쉬 조인(Hash Join)
- 소트 머지 조인(Sort Merge Join)
앞서 언급한 조인 순서와 조인 방식에 의해 만들어질 수 있는 모든 경우의 수가 바로 앞서 언급한 SQL이 수행될 수 있는 전체 경우의 수가 될 것이다. 따라서 조인 순서의 두 가지 경우와 조인 방식의 세 가지 경우를 합해 여섯 가지 경우의 수가 생성될 것이다. 예를 들면 A 테이블을 액세스한 후 B 테이블을 액세스하는 방식이 중첩 루프 조인을 사용할 수도 있고 해쉬 조인을 사용할 수도 있다.
여섯 가지 경우의 수 가운데 과연 어떤 경우가 최적의 실행인가? 여섯 가지 경우의 수가 모두 동일한 성능을 보장한다면 이와 같은 경우의 수는 아무런 의미가 없을 것이다. 결국 해당 SQL의 여섯 가지 경우의 수 중에서 단 한두 가지만이 성능을 보장할 수 있는 것이다. 이는 각각의 테이블에 존재하는 인덱스와도 매우 밀접한 관계가 있다.
예를 들어 <리스트 3>의 SQL은 100건의 데이터가 결과로 추출되며 TEST1 테이블과 TEST2 테이블에는 인덱스가 존재하지 않는다고 가정하자. 여기서 TEST1 테이블이 먼저 액세스되고 TEST2 테이블이 이후에 액세스되며 조인 방식은 중첩 루프 조인을 사용했다고 가정하자. 그렇다면 어떤 현상이 발생하겠는가?
먼저 TEST1 테이블을 액세스하게 될 것이다. TEST1 테이블에 대해 해당 SQL의 WHERE 조건을 확인해 보자. WHERE 조건에는 DT 컬럼에 대한 조건이 존재하지만 DT 컬럼에는 인덱스가 존재하지 않으므로 TEST1 테이블을 처음부터 끝까지 액세스해야 조건을 만족하는 데이터를 모두 액세스할 수 있다. 이와 같이 수행한 후 TEST1 테이블로부터 1,000건의 데이터가 추출되었다고 가정하자. 1,000건의 데이터가 TEST1 테이블로부터 추출되었기 때문에 TEST2 테이블과의 조인 또한 1,000번이 수행될 것이다. 중첩 루프 조인 방식을 이용하게 되므로 TEST2 테이블은 조인 조건인 TEST1 테이블의 KEY1 컬럼의 값이 상수화되면서 KEY2 컬럼이 WHERE 조건으로 사용될 수 있다.
하지만 TEST2 테이블에는 인덱스가 존재하지 않으므로 TEST2 테이블을 전체 스캔(FULL SCAN)해야 할 것이다. TEST1 테이블의 한 건의 데이터에 대해 이와 같이 수행되며 TEST1 테이블에서는 1,000건의 데이터가 추출되므로 TEST2 테이블은 1,000번 전체 스캔(FULL SCAN)이 발생한다. TEST2 테이블의 크기가 100MB라면 1,000번을 전체 스캔하게 되므로 100GB의 데이터를 액세스하게 된다. 물론 이중에는 디스크에서 액세스하는 부분도 있지만 많은 부분은 메모리에서 데이터를 액세스하게 될 것이다. 그렇다고 100GB를 액세스하는 SQL이 과연 1초만에 결과를 추출할 수 있을까? 이는 불가능한 일일 것이다.
이번에는 TEST1 테이블의 DT 컬럼에 인덱스가 존재하고 TEST2 테이블의 KEY2 컬럼에 인덱스가 존재한다고 가정하자. 앞서 언급한 것과 동일하게 TEST1 테이블이 먼저 액세스되고 TEST2 테이블이 후에 액세스된다면 어떻게 수행되겠는가? TEST1 테이블이 사용할 수 있는 WHERE 조건은 DT 컬럼이면 해당 컬럼에 인덱스가 존재하므로 TEST1 테이블에서 1,000건의 데이터는 인덱스를 이용해 바로 추출할 수 있다. 이 부분에서도 앞서 언급한 TEST1 테이블의 전체 스캔에 비해 더 빠른 성능을 보장받을 수 있다.
그럼 이후 액세스되는 TEST2 테이블은 어떠한가? 중첩 루프 조인으로 수행하게 되므로 TEST2 테이블은 TEST1 테이블로부터 KEY2 컬럼의 값을 상수로 제공받게 된다. 그러므로 TEST2 테이블이 사용할 수 있는 WHERE 조건은 KEY2 컬럼이 된다. 또한 TEST2 테이블에는 KEY2 컬럼에 인덱스가 존재하므로 해당 인덱스를 이용해 TEST2 테이블을 액세스하게 되므로 불필요하게 TEST2 테이블을 전체 스캔할 필요가 사라진다. KEY2 컬럼의 값으로 원하는 모든 값을 인덱스를 써서 결과를 추출할 수 있게 된다. 아울러 앞서 언급한 실행과 비교하면 성능적인 면에서는 비교가 되지 않을 만큼 빠른 속도를 보장할 수 있다.
여기서는 한 가지 실행 방법만을 확인해 봤다. 인덱스 이용 여부까지 고려한다면 전체 경우의 수는 여섯 가지에서 인덱스가 존재하는 경우와 존재하지 않는 경우를 모두 포함하는 12가지로 늘어난다. 이중 앞에서 확인한 두 가지 경우는 성능적인 면에서는 엄청난 차이가 발생한다. 이 얼마나 놀라운 사실인가? 이처럼 동일한 결과를 추출하는 조인 SQL이 어떻게 수행되는가에 따라 그 성능이 하늘과 땅 차이로 달라진다. 이와 같은 상황에서 우리는 조인이 성능을 저하시킨다고 말할 수 있을까? 물론 조인은 성능 저하를 발생시키는 요소를 가지고 있다. 하지만 SQL의 성능 최적화를 위해 노력한다면 이와 같은 데이터 연결을 매우 효과적으로 사용할 수 있다.
SQL 최적화는 멀리 있지 않다
SQL 최적화는 먼 나라의 이야기가 아니다. 이를 전제로 SQL 최적화에서 우리가 고려해야 할 사항이 무엇인지를 살펴보자.
- 인덱스의 이용 유무
- 인덱스를 고려한 데이터 연결의 최적화
- 집합적인 SQL 작성
위의 세 가지 사항을 항상 염두에 두고 우리가 SQL을 작성한다면 최적화된 SQL에 근접한 SQL을 작성할 수 있다. 인덱스 이용 유무와 인덱스를 고려한 데이터 연결의 최적화는 이미 언급했다. 그렇다면 집합적인 SQL 작성은 무엇을 의미하는 것일까?
집합적인 SQL의 작성은 우리가 일반적으로 많이 사용하는 프로그램 언어와 같은 절차형 언어와는 반대되는 개념이다. 우리가 프로그램을 작성하다 보면 처리를 위해 순서대로 프로그램을 작성하는 경우가 많다. A라는 과정을 거치고 B라는 과정을 거쳐야만 C라는 결과를 추출할 수 있는 것이 일반 프로그램 언어이다. 그렇기 때문에 프로그램 작성 시에도 그 순서대로 프로그램을 작성하게 될 것이다. SQL은 이와 같은 절차형 언어가 아니다. SQL을 이용하면 A와 B를 한 번에 처리해 바로 C로 결과를 만들 수 있다. 따라서 이런 차이를 항상 명심한다면 우리는 좀더 쉽게 SQL을 최적화할 수 있다.
SQL 최적화는 대용량 데이터가 늘어나는 시대에는 반드시 필요한 항목이다. SQL 최적화를 통해서만 우리는 최적의 시스템을 구축할 수 있고 비로소 안정적인 시스템 운영을 할 수 있다. 아직도 많은 곳에서 SQL 최적화를 간과하고 있는 것이 사실이지만 지금도 늦지 않았다. 이제라도 많은 관심을 가지고 SQL 최적화에 노력해야 할 것이다.
====================================================================================================================
SQL 튜닝시 주의사항 (RDBMS의 특징 및 버전별로 다름)
1.튜닝 주의사항
1.절대로!! 성능보다 유지보수성이 우선된다는 진리를 잊어서는 안된다.
2.반드시 업무 특징과 WAS와 DB를 동시에 고려해서 튜닝해야 한다.
간단히 캐싱해면 끝날것을 SQL튜닝에 시간을 낭비하지 말라.!
2.대용량?
2.분 류 (대부분 분리형 데이터베이스인듯)
2.랜덤 억세스가 자주 일어나며 다양한 억세스 형태를 갖지 않는 경우
3.데이터 가 지속적으로 증가하며 다양한 억세스 형태를 가짐
2.파티션을 적용하고 SQL의 실행계획과 적절한 인덱스 전략이 필요함. => 쉽게 해결
3.테 이블 구조개선이 필요함 => 해결이 어려움.
3.매우 큰 대용량 처리를 관계형 데이터로 하는것은 미련한 짓일 수 있다. 관계형 데이터베이스의 기능이 정말로 필요한가?
2.대용량 처리가 가능한 파일데이터베이스 + 하둡으로의 전환을 염두해야 한다. (ex) 구글, 페이스북, 아마존, 이베이 등등)
3.그래도 관계형 데이터베이스를 써야겠다면?
2.데이터가 중요하지 않거나 DBMS가 오라클이 아닐 경우에 사용하자.
3.속도가 느리다면 => 개선방법
2.SQL이 엉터리이지 않는가 확인한다. (이런 경우가 상당히 존재한다.)
3.예상된 인덱스를 타는가? 플랜을 떠본다. 기본중의 기본이다.
2.인덱스와 조인 타입이 예상된 대로 인지?
3.무식하게 직접 하지 말고 이클립스 플러그인이 && 오렌지나 토드 등의 툴을 이용하자.
2.order by 구문에 페이징 처리까지 해야 한다면 근본적으로 클러스터 외에는 방법이 없다.
4.2달에 한번 정도 대청소의 개념으로 인덱스와 테이블을 리빌드 해주자.
5.테이블의 비정규화 등 구조적인 문제가 있는가? => 어렵지만 구조개선을 하는것이 좋다.
6.스토리지 등의 하드웨어 문제점을 진단해 본다. => 약 2배 가까운 성능향상을 볼수도 있다고 한다.
7.그래도 안되는, 이 외의 구조에 문제가 없다면 클러스터링밖에 답이 없다.
8.하지 말아야할 안티 패턴 => 당장은 문제를 빠르게 해결할 수 있지만 또다른 문제를 일으킨다.
2.집계용 컬럼 / 테이블의 추가.
3.다른 테이블의 컬럼을 복사시켜두는 컬럼의 중복화
4.랜덤 억세스 제거하기 기본
5.select / where에 있는 모든 컬럼을 전부 인덱스에 넣는다.
6.인 덱스에서 ROWID만을 읽어온 후 페이징처리를 한 후 실제 테이블에 억세스 한다.
7.기본적인 상식
2.업무에 따라 or 구문은 union을 가진 2개의, 인덱스를 타는 쿼리로 나눌 수 있다.
3.!= 이나 not in 등의 부정형 구문은 어플리케이션에서 긍정형 구문으로 바꾸어 주자.
4.any, all 구문을 min,max로 바꾸어 준다.
5.Min / Max 구문 : GROUP BY 없이 인덱스 + ROWNUM = 1 구문으로 최적화 가능하다.
6.적 당한 조인 테이블을 유지한다. (절대적이지 않다.)
2.반 대로 성능에 문제가 된다면 조인되는 테이블을 최소화 하라. SQL을 나누어 실행하도록 join을 쪼개자.
7.exist 구문
즉 해당하는것이 1:N일경우 속도증가가 있다.
2.not in 구문 대신 exists 키워드 사용 가능 (not in과는 달리 선행 함수에서 상수값을 제공받아 실행된다.)
2.이를 First row semi join이라 한다.
8.count
2.ex) 100개 이상일 경우인지 아닌지만 알고 싶다면 주요 인덱스만 일단 먼저 태워서 100개 이상/이하를 선판단 할 수 있다.
3.A 집합을 알고 싶을때 A에 인덱스가 없다면? => 전체집합에서 인덱스가 있는 B집합을 구해서 (전체-B)로 구하는 방법도 있다.
9.부정형
2.not in : 수행 후 서브쿼리라면 not exist와 비슷. 선수행 서브쿼리라면 minus와 비슷.
3.not exist : 후수행 서브쿼리로 동작. N개 리턴시 N번 수행됨으로 N이 크다면 성능저하 발생.
8.대사(2개 이상의 테이블에 대한 동기화) 작업
2.서로 없는 컬럼 insert : 원쿼리(insert all + union all + group by) + 힌트로 최적화된 동기화 작업이 가능하다.
개별비교 하지 말것!
3.서로 다른 컬럼 update : 역시 원쿼리(인라인뷰) + 힌트로 가능.
9.동적 쿼리 최적화
2.이 인덱스별 분류를 키값을 받아 분리(where조건에 '1'=key & union all + 인덱스힌트)해서 최적화 시킬 수 있다.
3.하지만 그리 좋은 생각은 아니다. 성능이 유지보수보다 월등히 중요한 경우에만 사용.
2.동적 order by구문 : 역시 위와같은조건 또는 동적 인덱스힌트 삽입으로 order by를 제거할 수 있다.
3.둘다 동적인 경우 : 둘의 경우를 적절히 조합.
10.배치 최적화
2.해시조인 + 병렬처리로 성능 개선 => 반드시 뷰의 로우수와 테이블의 로우수가 일치(FK로 보장)해야 한다.
2.대규모 update / delete를 insert로 대체.
2.대규모 처리할거면 성능향상을 위해 아에 신규 테이블을 만들고 insert로 나머지 로직을 처리할수도 있다.
출처 : egloos blog 청춘의 선택(http://dryang.egloos.com/3440561)
'04번. IT 힌트얻기 > ▶ DB/ SQL ' 카테고리의 다른 글
| [SQL고수되기] 첫번째 이야기 (0) | 2011.09.29 |
|---|---|
| [SQL Function] INSTR (0) | 2011.09.29 |
| HASH JOIN (0) | 2011.09.27 |
| SORT MERGE JOIN (0) | 2011.09.27 |
| ORACLE NESTED LOOP JOIN (0) | 2011.09.27 |