한 사이트 지원을 나갔을 때의 일이다. 해당 사이트의 PM은 데이터베이스 벤더의 직원이었다. 이와 같은 상황이라면 누구나 데이터베이스의 벤더 직원이니 DBA의 역할이나 SQL 최적화의 중요성을 인지할거라고 생각할 것이다. 생각과는 달리 데이터베이스 벤더 직원이 SQL 최적화에 대해 너무 모르고 있었다. SQL 최적화가 인덱스 몇 개 만들고 하루에 수십 개 이상의 SQL을 최적화할 수 있다는 생각을 하고 있었다. 과연 이것이 맞는 것인가?
SQL 최적화를 해본 사람이라면 알겠지만 하나 하나의 SQL을 최적화하는 것은 그다지 쉬운 작업이 아니다. 그런데 PM이 이와 같은 생각을 가지고 있으니 해당 프로젝트가 제대로 진행될 리 없다. 그래서인지 그 프로젝트에서 SQL 최적화를 담당하는 사람들은 SQL 최적화의 역할 뿐만 아니라 데이터 이행, DBA의 일부 역할 및 성능 테스트 등을 책임지고 있었다. 과연 이것이 가능한 것인가? 이는 IT에서 몇 십 년을 일했다는 사람들조차도 SQL 최적화의 중요성을 전혀 인지하지 못해서 발생하는 좋은 예다. 이제는 우리가 SQL 최적화에 눈을 돌릴 시기다. 데이터의 증가와 성능의 중요성이 부각되면서 SQL 최적화를 중요하지 않고 쉬운 일로 생각하는 일부 관리자들의 생각이 변해야 될 것이다. 이번 호에서는 SQL 최적화가 무엇인지에 대해 같이 이야기해 보자.
SQL의 성능은 블록의 액세스 양과 비례한다
SQL의 성능은 우리가 듣지도 보지도 못한 곳에 있는 것이 아니다. SQL의 성능은 아주 쉬운 곳에 있다. 바로 블록 액세스의 양이다. 블록 액세스는 무엇인가? 우리가 인터넷을 통해 파일을 전송받고자 한다면 파일을 전송받는 속도는 어떤 요소에 좌우되는가? 물론, 네트워크의 속도에 의해서도 좌우된다. 사용하는 네트워크의 속도가 10Mbps인지 100Mbps인지는 많은 차이를 발생시킬 것이다. 이와 같은 네트워크의 속도를 배제한다면 우리는 어떤 요소로 해당 파일을 전송받는 데 더 오랜 시간이 소요될지 더 적은 시간이 소요될지를 판단하겠는가? 바로 파일의 크기를 보고 판단할 것이다. 1MB 크기의 파일과 100MB의 파일을 전송받는다고 한다면 1MB 크기의 파일을 전송받는 데 더 많은 시간이 소요되겠는가? 당연히 이상이 없다면 100MB 크기의 파일을 전송받는 데 더 많은 시간이 소요될 것이다. 데이터베이스도 이와 별반 다른 것이 없다. 많은 데이터를 액세스한다면 수행 시간은 많이 소요되며 적은 데이터를 액세스한다면 빠른 시간에 결과를 추출할 수 있을 것이다. 이것이 어찌 일반적으로 파일을 전송하는 것과 다르다고 할 수 있겠는가?
여기서 우리는 하나의 오류에 빠지기 쉽다. 그것은 바로 보이는 것이 다가 아니라는 사실이다. 그럼 무엇이 추가로 존재한다는 것인가? 영화를 인터넷에서 전송받는다면 보이는 크기가 내려받는 파일의 전체 크기가 될 것이다. 따라서, 1GB 크기의 영화를 전송받으면 1GB의 데이터만을 액세스하게 된다. 하지만, 데이터베이스에서는 이와는 전혀 다른 현상이 발생하게 된다. 해당 SQL을 수행하여 추출되는 결과가 10건이라고 가정하자. 그렇다면 눈에 보이는 10건에 대해서만 데이터를 액세스한다면 결과가 추출될까?
데이터베이스는 눈에 보이는 것이 다가 아니다. 10건을 액세스하기 위해 많은 방법이 존재하며 이중 한 가지 방법을 이용하여 해당 SQL을 수행하게 된다. 따라서, 어떤 방법을 선택하는가에 따라 10건의 데이터만을 액세스할 수도 있지만 잘못 선택한다면 1,000만 건의 데이터를 액세스한 후 10건의 데이터를 결과로 추출하는 경우도 많다. 이렇기 때문에 눈에 보이는 10건을 보고 10건의 데이터만을 액세스한다고 생각하면 큰 오류에 빠지게 되는 것이다. 10건의 데이터를 추출하기 위해 데이터베이스 내부에서는 더 많은 데이터를 액세스할 수 있게 되고 그렇다면 액세스하는 데이터의 양이 증가하기 때문에 성능은 저하되게 된다. 이를 이해하지 못하기 때문에 일부 관리자들은 튜닝의 중요성을 인식하지 못하는 것 같다. 이와 같은 보이지 않는 데이터의 액세스 양의 변화는 아래와 같은 요소들에 의해 큰 변화를 발생시키게 된다.
● 인덱스 사용 유무
● 조인 방식
● 클러스터 팩터
● SQL 문
이와 같은 요소에 의해 10건을 추출하는 SQL은 100만 건의 데이터를 액세스한 후 10건의 데이터만을 결과로 추출할 수도 있고 진정으로 10건의 데이터만을 액세스하여 결과를 추출할 수도 있다. 따라서 우리에게 보여지는 데이터의 건수는 SQL이 ‘빠르다’, ‘느리다’를 판단할 수 있게 하는 절대 근거가 되지 못한다. 내부적으로 보이지 않는 데이터를 얼마나 액세스하는가에 의해 SQL의 성능을 좌우하게 된다.
결국, SQL의 최적화는 보이지는 않지만 결과를 추출하기 위해 불필요한 데이터를 액세스하는 과정을 제거하는 일련의 활동이라고 할 수 있다. 어떻게 보면 쉬울 수 있지만 이와 같은 과정을 최적으로 수행하기 위해서는 많은 노력과 많은 지식이 있어야만 가능하다. 이는 SQL 최적화를 수행해본 사람이라면 이해할 수 있을 것이다.
SQL의 최적화는 숨어 있는 I 인치를 찾는 것이다
어느 광고에서인가 숨은 1인치를 찾으라고 했던 기억이 난다. 이것이 진정한 SQL의 최적화이다. SQL에서 보이지는 않지만 분석을 통해 숨어 있는 비효율을 찾아서 해당 비효율을 최적화하는 것이 바로 SQL 최적화이다.
그렇다면 이와 같은 비효율은 왜 발생하는 것일까? 이 이유를 정확히 안다면 SQL 최적화가 얼마나 어려운 길인지를 짐작할 수 있을 것이다. 하나의 SQL이 수행되는 경우의 수는 셀 수 없을 정도로 많이 존재한다. 우리가 아침마다 출근하는 길을 생각해보자. 집에서 회사까지 출근하는 길이 한 가지인가? 회사에서 집까지 출근하는 길은 매우 다양할 것이다. 그렇다면 과연 본인이 선택한 길이 집에서 회사까지 가장 빠른 길일까? 반드시 그렇지만은 않을 것이다. 출근을 위한 길을 선택하는 경우 최단 거리도 확인하지만 교통 수단을 갈아타는 횟수 및 차비 등의 비용 등도 계산하게 된다. 물론, 이와 같은 계산을 수행했어도 잘못된 길을 선택하는 경우도 많다.
SQL도 마찬가지다. 하나의 SQL을 수행하기 위해서는 해당 SQL을 수행하는 방법에는 많은 종류가 존재한다. 그 많은 종류 중에 오직 하나만을 선택할 수 있다.
이 SQL은 그렇게 복잡한 SQL은 아닐 것이다. 위의 SQL이 수행될 수 있는 방법은 몇 가지인가? 위의 SQL이 수행될 수 있는 방식의 경우의 수를 확인해 보자.
● 테이블의 액세스 순서 - 5*4*3*2*1 = 120가지
● 조인 방식 - 3*3*3*3 = 729 가지
● 인덱스 사용 유무 - 2*2*2*2*2 = 32 가지
위와 같이 해당 SQL이 수행될 수 있는 경우의 수는 어느 테이블을 먼저 액세스하냐에 따라 120가지가 존재하며 조인 단계에서 해쉬 조인, 중첩 루프 조인 및 소트 머지 조인 중 어떤 조인을 사용하는가에 따라 두 테이블을 조인하는 방식이 세 가지씩 존재한다. 또한, 테이블이 5개이므로 이와 같은 조인 방식은 네 번이 선택된다. 그러므로 조인 방식에서 729가지의 경우의 수가 도출된다. 각각의 경우의 수는 동시에 발생하므로 120*729*32 = 2,799,360만큼의 경우의 수가 존재하며 이중 단 한 가지 방법으로만 해당 SQL을 수행하게 된다. 이와 같이 SQL을 수행하기 위한 경우의 수를 실행 계획이라고 한다.
결국, SQL의 최적화는 수없이 많은 실행 계획 중 최적인 하나의 실행 계획을 찾는 일련의 과정이라고 봐도 될 것이다. 물론, SQL을 최적화하기 위해 SQL을 변경해야 하는 경우도 많이 발생하게 된다. SQL을 변경하더라도 결국 변경한 SQL에 대한 최적의 실행 계획을 찾아야 SQL 최적화를 수행할 수 있게 될 것이다.
필자소개
권순용 kwontra@hanmail.net｜Data Consulting 업무를 수행하는 ㈜엑시엄의 대표이사이며 DBA로 시작하여 SQL 튜닝, 데이터베이스 아키텍처 및 모델링 업무를 주로 수행했다. 데이터베이스 교육에도 많은 관심을 가지고 있으며 저서로는 『Perfect! 오라클 실전 튜닝, 『초보자를 위한 오라클 10g』 및 『INSIDE SQL』이 있다. 또한, 데이터 액세스 최적화에 대한 특허를 출원했다.
출처 : 한국 마이크로 소프트웨어 [2009년 3월호]
그 필요성을 느끼고, 조금만 노력을 해봐도 처음에는 그 실력이 일취월장할수 있다는...
물론 고수가 되기위해서는 보통이상의 엄청난 노력이 필요하겠지만...