티스토리 뷰
. T-SQL 순위를 구하는 문제의 해결책
가장 쉬우면서도 가장 평범한 문제를 다루고자 한다. 간단하게 생각하면 이 순위 패턴은 오라클 에서는 필요하지 않다라고 생각 할 수 있다. 왜냐면 오라클에서는 ROWNUM을 제공하므로 데이터를 ORDER BY해주고 ROWNUM만 적어 주면, 순위를 매겨 번호를 붙여 줄 수 있다. 하지만, 순위가 동률인 데이터에 대해서 어떻게 처리할 것인가? 이런 문제에 대해서 순위 패턴을 적용할 수 있을 것이다. SQL Server의 T-SQL은 어떠한가? ROWNUM같은 기능의 함수는 존재하지 않는다. 그러므로 이 순위 패턴을 절대적으로 피할 수 없을 것이다.(순위에 대한 문제를 클라이언트에서 처리하지 않는다면)
예전에 T-SQL을 공부하기 시작했을 때는 왜 ROWNUM 같은 것이 없을까 하고, 찾아 헤맨 적이 있었다. 하지만, SQL에 대해 알면 알아 갈수록 ROWNUM이 없어도 모든 처리가 가능하다는 것을 알 수 있었다.
SQL을 하면서 항상 느끼는 것은 연구하고 생각할수록 더 좋은 문장을 만들 수 있다는 것이다. 이 데이터베이스의SQL은 기능상으로 어떤 함수가 제공되지 않는다고 못한다는 것은 연구하고 생각하고자 하는 의지가 부족한 것이다. 그런 개발자들은, 생각부터 고쳐야 할 것이다.
가장 간단한 데이터를 가지고 생각을 해보도록 하자.
우리가 만들 테이블은 점수 테이블이다. 간단하게 학번과 점수로만 구성되어 있다.
이렇게 간단한 데이터를 가지고 시작하는 것은 우리가 알아야 할 순위를 구하는 원리에 대해 집중하기 위한 것이다.
학번과 점수로만 구성이 된 간단한 테이블이므로 ERD는 생략하도록 하겠다.
<SQL 1-1>
CREATE TABLE SCORE ( StudentID INT IDENTITY PRIMARY KEY ,Score NUMERIC(3,0) ) go |
테이블을 생성했으면 샘플 데이터를 입력하도록 하자.
<SQL 1-2>
--점수 입력 INSERT INTO INSERT INTO INSERT INTO INSERT INTO INSERT INTO INSERT INTO go |
샘플 데이터 입력 후에 점수(Score) 순으로 데이터를 관찰 해보도록 하자. “갑자기 무슨 관찰인가” 란 생각이 들 수 있겠지만 데이터를 관찰 하는 것은 매우 중요하다. 데이터를 관찰하고 자신이 원하는 결과가 무엇인지 상상하다 보면 풀 수 있는 해법을 찾을 수 있기 때문이다.
<SQL 1-3>
--순위대로 데이터 보기 SELECT * FROM SCORE ORDER By Score DESC |
위의 SQL을 통해서 다음과 같은 결과를 얻을 수 있다.
StudentID | Score | Ranking |
5 | 95 | 1 |
1 | 90 | 2 |
4 | 85 | 3 |
3 | 80 | 4 or 5 |
2 | 80 | 4 or 5 |
6 | 70 | 6 |
이 결과에서 점선으로 표시된 Ranking 부분은 아직 얻어내지 않은 결과이다. 우리가 원하는 것은 위의 결과처럼 점선의 Ranking까지 같이 표시하는 결과이다.
이 결과를 얻기 위해 먼저 정해야 하는 것은 같은 점수를 가진 학생에 대해서 어떻게 등수를 부여할 것이냐의 문제이다. 여기서는 일단, 동률 점수를 가진 학생에 대해 같은 낮은 순위의 등수를 부여하기로 하겠다. 그러므로StudentID 3번과 2번은 같은 4등을 가지게 할 것이다.
이 순위 문제를 해결하기 위해서 SQL문을 만들기 전에, 종이와 펜을 들고, 또는 머릿속으로 생각해 보도록 하자.생각으로 SQL문을 만들어 보자는 것이 아니라, 왜, StudentID 5번은 1등이고, 1번은 2등이 되는지 생각해 보자는 것이다. 5번은 점수가 가장 높으니까, 1등이고, 1번은 2번째로 점수가 좋으니 2등이다. 그렇다면 이 당연한 것을StudentID와 Score만 가지고 어떻게 만들어 낼 수 있는가를 깊이 생각해 보자.
이것은 당연하고 쉬운 문제이다. “5번 학생의 점수 95점보다 높은 점수를 가진 학생이 몇 명일까?”란 질문으로 이 해답은 풀어지게 된다. 5번 학생 점수보다 높은 학생은 아무도 없다. 그러므로 1등이다. 1번 학생의 점수보다 높은 학생은 몇 명인가? 5번 학생 한 명이다. 그러므로 1번 학생은 2등이다. 4번 학생보다 점수가 좋은 학생은 5번과 1번 학생 두 명이다. 그러므로 4번은 3등이다. 이렇게, 자기 자신의 점수보다 점수가 좋은 학생의 수를 센다면, 쉽게 순위를 구할 수 있다.
자, 그럼, 생각한 대로 SQL문을 만들어 보도록 하자. 여기서는 이해가 쉽도록 SELECT절의 서브쿼리를 사용할 것이다. 간혹, 개발자들의 특성에 따라, SELECT절에 서브쿼리가 오는 것은 최악이라고 생각하는 경우가 있다. 하지만,필자는 꼭 그렇다라고 생각하지는 않는다. SQL문의 가독성 적인 측면을 생각한다면, SELECT절의 서브쿼리가 훨씬 유용할 때가 많기 때문이다.
우리가 생각한 논리는 다음과 같이 SQL문으로 표현 할 수 있다.
<SQL 1-4>
--순위 구하기 SELECT StudentID ,Score ,( SELECT COUNT(*) FROM SCORE T2 WHERE T2.Score > T1.Score) Ranking FROM SCORE T1 ORDER BY Ranking |
위의 SQL문에서 실제 순위를 구하는 곳은 SELECT절의 서브쿼리란 것을 쉽게 알 수 있을 것이다. FROM절의 SCORE테이블(T1)의 Score보다 큰 Score를 가진 학생들의 COUNT를 구해서 Ranking으로 표현하는 것이다.
실행 결과, 원하지 않는 결과가 나온 것을 알 수 있다.
StudentID | Score | Ranking |
5 | 95 | 0 |
1 | 90 | 1 |
4 | 85 | 2 |
3 | 80 | 3 |
2 | 80 | 3 |
6 | 70 | 5 |
실제 1등으로 표현되어야 할 StudentID 5번은 Rangking이 0으로 표시되고, 실제, 2등인 1번의 Ranking이 1로 표시된 것을 알 수 있다. 이것을 해결할 수 있는 방법은 무엇인가? 아주 쉬울 것이다. 결과에 1씩만 더해 주면 될 것이다. 다음과 같은 SQL이 될 것이다.
<SQL 1-5>
SELECT StudentID ,Score ,( SELECT COUNT(*) FROM SCORE T2 WHERE T2.Score > T1.Score) + 1 Ranking FROM SCORE T1 ORDER BY Ranking |
원하는 결과가 나온 것을 알 수 있다.
이런 순위의 문제에 대해서 항상, 고려해야 하는 것은 동률을 이룬 데이터에 대한 처리이다.
동률의 데이터에 대해 동일하게 낮은 등수를 적용하는 경우도 있을 것이다. 3번과 2번 StudentID에 대해 같은 4등을 적용하는 것이 아닌, 같은 5등을 적용하는 것이다. 이 경우는 어떻게 하면 되겠는가?
0을 1로 바꾸기 위해서 서브쿼리의 결과에 + 1 을 한 것과 같이 + 2를 해주면 되는 것인가? 아니다. + 2를 해주면, 1등은 2등이 되고, 2등은 3등이 될 것이다. 단지, StudentID, 3번과 2번만이 우리가 원하는 5등이 될 것이다.같은 낮은 등수를 적요하기 위해서는 서브쿼리에 + 1을 하는 것이 아닌, 서브쿼리의 T2.Score > T1.Score 부분을>= 조건으로 변경하면 된다. 자신보다 높은 점수를 가진 사람을 세는 것이 아니라, 자신보다 높거나 같은 점수를 가진 사람들을 세게 된다면, 동률 데이터에 대해서는 저절로 동일한 낮은 순위가 매겨지게 될 것이다.
<SQL 1-6>
SELECT StudentID ,Score ,( SELECT COUNT(*) FROM SCORE T2 WHERE T2.Score >= T1.Score) Ranking FROM SCORE T1 ORDER BY Ranking |
만족스러운 결과를 얻을 수 있을 것이다.
우리는 비록 원하는 결과를 일차적으로 얻었지만, 또 다른 방법이 없는지 연구 해봐야 한다. 해당 시스템에서 가장 최적이고 좋은 방법을 생각해 내야 하는 것이다.
이런 순위를 구하는 가장 간단한 방법은 클라이언트에서 순위를 비교해서 뿌려 주는 것이다. SQL로는 ORDER BY만 해주고, 데이터를 화면에 출력할 때, 기존에 출력했던 데이터보다, 점수가 높으면 1증가시키고, 아닌 경우는 순위를 이전과 동일하게 유지하면서 출력해주는 방법이다. 가장 쉬운 방법이면서도, 가장 좋은 성능을 낼 것이라 생각 된다. 문제는, 다양하게 변하는 사용자의 요구사항을 적용하기 위해서 클라이언트 프로그램을 변경하는 일은 만만한 작업이 아니라는 것이다. 이미 운영되고 있는 시스템이라면, 배포에 대한 문제도 있을 수 있으면, 어떤 코딩을 건드렸을 때, 다른 코딩에 영향을 주지 않는지를 알아내기란 쉽지 않은 문제이다.
모든 것에는 장 단점이 있는 것이므로 적절하게 사용을 하면 될 것이다. 하지만, 우리는 좀더 SQL쪽에 접근해서 문제를 푸는 방법을 생각하도록 하자.
순위를 구할 수 있는 또 다른 SQL문에는 어떤 것이 있을까? 서브쿼리를 사용한 것은 가장 쉬운 방법이라고 필자는 생각한다. 서브쿼리를 사용하지 않고 해결하는 방법에는 셀프 조인(Self Join)이 있다.
자기 자신과 테이블을 조인하는 방법이다. 사실, 순위를 구하기 위해 사용한 서브쿼리 역시 셀프 조인이다. 자기 자신의 테이블을 관련되어서 순위를 찾아내니까 말이다. 하지만, 문법적으로 셀프 조인을 작성 할 때는 FROM절에 두 개의 같은 테이블이 모두 있어야 한다.
순위를 구하기 위한 셀프조인 SQL문은 다음과 같다.
<SQL 1-7>
SELECT T1.StudentID ,MIN(T1.Score) Score ,COUNT(*) Ranking FROM SCORE T1 JOIN SCORE T2 ON T1.Score <= T2.Score GROUP BY T1.StudentID ORDER BY Ranking |
확실히 셀프 조인은 서브쿼리보다 읽기가 쉽지는 않을 것이다. 하지만, 이 어려워 보이는 문장도 하나씩 각개격파를 해보면 별 것 아니다. 그리고, 이 문장을 이해하는 것은 SQL의 JOIN이 얼마나 강력한지 이해하게 되는 것이다.위의 결과에 대해 결과는 다음과 같다.
StudentID | Score | Ranking |
5 | 95 | 1 |
1 | 90 | 2 |
4 | 85 | 3 |
3 | 80 | 5 |
2 | 80 | 5 |
6 | 70 | 6 |
실행 결과를 보면, StudentID 3번과 2번이 4등이 아닌, 5등으로 나타난 것을 알 수 있다. 이것을 4등으로 표현하기 위한 문제는 좀 더 후에 생각하도록 하고, 지금은, 이 결과가 어떻게 해서 나왔는지 생각을 해보도록 하자. 약간씩 복잡한 SQL문을 만들거나, 복잡한 SQL을 파악할 때는 하나씩 작은 단위로 나누어서 SQL을 파악하는 것이다.이 정도 SQL가지고 복잡하다고 말 할 수 없음을 많은 개발자들은 알고 있을 것이다. 하지만, 이 짧은 SQL을 파악하는 과정을 통해 더 복잡한 문장도 파악할 수 있는 능력이 생길 것이다.
먼저 순수하게 조인만 수행하고 결과를 살펴보도록 하자.
<SQL 1-8>
SELECT T1.StudentID ,T1.Score ,T2.StudentID ,T2.Score FROM SCORE T1 JOIN SCORE T2 ON T1.Score <= T2.Score ORDER BY T1.Score DESC |
결과를 보면 다음과 같다.
T1.StudentID | T1.Score | T2.StudentID | T2.Score |
5 | 95 | 5 | 95 |
1 | 90 | 1 | 90 |
1 | 90 | 5 | 95 |
4 | 85 | 1 | 90 |
4 | 85 | 4 | 85 |
4 | 85 | 5 | 95 |
3 | 80 | 1 | 90 |
3 | 80 | 2 | 80 |
이 결과에서 눈여겨 볼 것은 같은 T1.StudentID를 가진 데이터가 몇 개씩 있느냐 이다.
거듭 강조하지만, T2로 나온 결과는 신경쓰지 말도록 하자. T1.StudentID가 5번인 데이터는 한 건이다. 1번인 데이터는 2건, 4번인 데이터는 3건, 3번과 2번은 각각 5건이다. 이 T1.StudentID별로 데이터 건수가 등수로 연결되는 것은 너무도 쉽게 알 수 있을 것이다. 그럼, 왜 이렇게 데이터가 나올 수 있을까를 고민해 보도록 하자. 이것에 대한 답은 조인을 통해 변하는 데이터의 건수이다. SQL문을 보면 조인 조건이 T1.Score = T2.Score가 아닌, T1.Score <= T2.Score인 것을 알 수 있다. 이 것이 어떻게 조인이 이루어 지는가를 알기 위해서 간단한 테이블이 있다고 생각하고 하나씩 짚어 보도록 하자.
no란 컬럼을 가진 A테이블과 B테이블이 있다고 생각해 보자.
두 테이블에서 같은 no를 가진 데이터만 연결(JOIN)해서 조인 결과(A.no = B.no)를 만들어 보자.
A.no | B.no | A.no = B.no | |||
1 | 1 | 1 | 1 | ||
2 | 2 | 2 | 2 | ||
4 | 2 | 2 | 2 | ||
| 3 |
|
| ||
조인의 결과는 총 3 건이 된다. 그림을 보고 A테이블부터 첫 번째 데이터부터 시작을 해보도록 하자.
A테이블의 첫 번째 데이터는 no가 1이다. 이 1이란 숫자를 가지고 B테이블로 가져가 보도록 하자.
B테이블에서 지금 가져온 1이란 값과 일치되는 데이터는 B테이블의 가장 첫 데이터이다. 그러므로 이것은 1건의 조인 결과를 만들어 내게 된다.
이번에는 A테이블의 두 번째 데이터인 2번을 가지고 B테이블에서 찾아보도록 하자. B테이블은 2번을 두 건 가지고 있다. 그러므로 결과는 조인 결과는 2건이다.
마지막으로 A테이블의 4번 데이터를 B테이블에서 찾아보자. 알다시피 한 건도 없으므로 조인 결과에는 참여하지 않게 된다. 이것이, 같다 조건을 사용한 조인이 된다. 여기서 정확히 알고 있어야 하는 것은, A테이블과 B테이블의 연결 순서에 상관없이 결과는 동일하다는 것이다. 위와는 반대로 B테이블로 시작해서 A테이블을 거쳐서 조인을 해보도록 하자.
B.no | A.no | B.no = A.no | |||
1 | 1 | 1 | 1 | ||
2 | 2 | 2 | 2 | ||
2 | 4 | 2 | 2 | ||
3 |
|
|
| ||
B테이블의 처음 데이터는 no가 1이다. 이 데이터를 가지고 A테이블을 뒤져 보면, no가 1인 데이터는 1건이다. 그러므로 결과에는 1건이 나오게 된다. B테이블의 두 번째 데이터는 no가 2이다. 이 데이터와 동일한, no를 가진 데이터를 A테이블에서 찾아보면 1건이다. 그러므로 B.no가 2이고, A.no가 2인 데이터가 결과에 한 건 만들어 진다. B테이블의 세 번째 데이터의 no도 역시 B테이블의 두 번째 데이터와 마찬가지로 2이다. 이 데이터를 가지고 다시A테이블에서 찾아보면, 이전에 B테이블의 두 번째 데이터와 짝을 맞춘 no가 2인 데이터가 한 건 있다. 이 데이터와 조인이 되어서 역시 결과로 한 건이 보내 진다. 마지막으로 B테이블의 no가 3인 데이터는 A테이블에서 찾아볼 수 없으므로 결과에 참여하지 못한다.
이와 같이, 조인의 순서는 결과 내용에는 영향을 미치지 않는다. 하지만, 이러한 조인의 순서는 때로는 성능에 영향을 미치게 되며, 내부 조인(일반적인 조인)이 아닌, 외부조인을 사용할 경우에는 조인의 순서가 조인결과에 영향을 미치기도 한다. 하지만, 내부 조인에서는 절대적으로 조인의 순서에 따라 결과가 틀리게 나오는 경우는 없다.
그러면 이번에는 같다 조건이 아닌 크기 비교 조건으로 조인을 수행해 보도록 하자.
대부분의 개발자들은 같다 조건 이외의 조건으로 조인을 해본 적이 거의 없을 것이다. 실제로, 이런 조건을 잘못 사용하게 되면, SQL의 실행 성능에 악영향을 끼치기도 하며, 실제로 사용되는 일이 드물기 때문이다. 하지만, 원리를 알고 정확히 사용한다면, 아주 유용하게 사용할 수 있을 것이다.
A테이블과 B테이블을 A.no >= B.no 의조건으로 조인을 해보도록 하자.
총 몇 건의 결과가 나올 거 같은가? 아마, 머리 좋은 개발자들은 이 정도의 데이터 건수라면, 쉽게 암산 할 수도 있을 것이다. 하지만, 데이터가 많아 지면 이를 암산하기는 쉽지 않다. 그림을 통해서 알아보도록 하자.
A.no | B.no | A.no >= B.no | |||
1 | 1 | 1 | 1 | ||
2 | 2 | 2 | 1 | ||
4 | 2 | 2 | 2 | ||
| 3 | 2 | 2 | ||
| 4 | 1 | |||
4 | 2 | ||||
4 | 2 | ||||
4 | 3 | ||||
같다 조건으로 결합한 경우보다 좀 더 보기 복잡해 졌을 것이다. 그래도 인내를 갖고 하나씩 생각하면서 보도록 하자. 결합(조인) 조건은 A의 no가 B의 no보다 크거나 같은 경우이다.
A의 첫 번째 데이터는 no가 1이다. 그럼 B테이블에서 1보다 작거나 같은 no를 가진 데이터는 첫 번째 데이터 한 건이다. 그러므로 먼저 한 건이 결과로 만들어 진다.
A의 두 번째 데이터는 no가 2이다. B테이블에서 2보다 작거나 같은 no를 가진 데이터는 무엇이 있는지 찾아보자. B 테이블의 첫 번째 데이터, 두 번째, 세 번째 데이터가 각각, 1, 2, 2의 값을 가지고 있으므로 A테이블의 두 번째 데이터와 결합 할 수 있다. 그러므로 3건이 결과 집합에 만들어 지게 된다.
마지막으로 A의 세 번째 데이터는 4에 대해서 살펴보도록 하자. B테이블에서 4보다 작거나 같은 no를 가진 데이터는 B테이블의 전부이다. 총 4건이므로 총 4개의 결과가 추가 된다. 그 결과, 총 8건의 결과가 만들어 진 것을 볼 수 있다. 반대로 B테이블을 시작점으로 해서 조인을 해보아도 = 조건의 조인과 결과는 동일하다.
우리는 이 A.no >= B.no 같은 조건을 순위를 구하는 SQL문에 응용했던 것이다.
그럼, 실제 순위를 구했던 SQL을 통해 크기비교 조건으로 조인이 어떻게 되는지 살펴보자.
아마도, 관계형 DB를 할 때는 집합개념이 중요하다는 말을 많이 들었을 것이다. 필자는 수학이고 산수고 모두 자신 없다.(실제로 구구단을 외자 게임을 하게 된다면, 두, 세번 답변 하다가 지고 말 것이다.) 그러므로 집합에 대한 얘기는 하지 않겠다. 집합을 몰라도 관찰력과 상상력을 가지고 있다면 SQL을 능숙히 만들어 낼 수 있을 것이다. 이런, 관찰력이 우리에게는 필요하다는 것을 상기하고 SQL문과 결과를 관찰해 보도록 하자.
먼저 SQL문장에서는 셀프 조인(SELF JOIN)을 사용했다. 셀프 조인이란 것은 특별히 어려운 것이 없다. FROM절에 같은 테이블이 두 개 이상 온다면 셀프 조인인 것이다. 우리는 이전의 크기비교 조인을 살펴보기 위해서 간단한 A테이블과 B테이블을 사용했지만, 실제 순위를 구하는 SQL은 크기비교 조인을 자기 자신과 수행하게 된다. <SQL 1-8>을 수행해서 결과를 살펴보자. 어떤 특징이 있는가? 위에서도 설명했듯이, 이 결과에서는, T1.StudentID가 5번인 데이터는 한 건, 1번인 데이터는 2건, 4번인 데이터는 3건, 3번과 2번은 각각 5건라는 것이다. 이러한 사항을 좀 더 쉽게 관찰 할 수 있는 방법은 무엇일 까? 그것은 바로 하나씩 실행을 해보는 것이다. 실제 개발 시에도 하나의 데이터에 대해 관찰해서 얻은 결론으로 전체 데이터에 적요하는 SQL문을 만들면 생각보다 쉽게 SQL을 만들 수 있다.
다음과 같은 SQL을 실행해 보자.
<SQL 1-9>
SELECT T1.StudentID ,T1.Score ,T2.StudentID ,T2.Score FROM SCORE T1 JOIN SCORE T2 ON T1.Score <= T2.Score WHERE T1.StudentID = 1 |
T1테이블의 StudentID가 1번인 학생 보다 높거나 같은 점수를 가진 데이터를 T2에서 찾는 것이다. 결과는 총 두 건이 나온 것을 알 수 있다. 그러므로 이 1번 학생은 2등이다. WHERE조건의 StudentID를 하나씩 바꿔가면서 실행해 보도록 하자. 해당 번호의 학생이 몇 등인지를 쉽게 알 수 있을 것이다.
<SQL 1-8>의 결과를 T1.StudentID별로 집계를 해서, 카운트를 센다면, 등수가 나올 것이다. 그리고, Score는T1.StudentID에 대해 종속적(T1.Score는 T1.StudentID에 대해 결정되어 진다.)이므로 MAX나, MIN을 사용해서Score를 표시하도록 하자. 그래서 나온 결과가 <SQL 1-7>이다. GROUP BY, COUNT, MAX, MIN등에 대해서는 설명하지 않아도 모두 알고 있으리라 생각하고 생략하도록 하겠다.
이번에는, 셀프 조인을 사용해서 동률인 데이터에 대해서 낮은 등수를 출력할 수 있도록 구성해 보자. 기존의<SQL 1-7>의 결과는 다음과 같았다.
StudentID | Score | Ranking |
5 | 95 | 1 |
1 | 90 | 2 |
4 | 85 | 3 |
3 | 80 | 5 |
2 | 80 | 5 |
6 | 70 | 6 |
이 결과에서 3번과, 2번 StudentID가 5등이 아닌, 4등이 되어야 하는 것이다.
스스로 한번, 시도를 해보기 바란다. 아마도, 다양한 방법이 있을 것이다. 하지만, 결코 만만한 문제는 아닐 것이다.
먼저, 단순하게, Ranking에 -1을 하게 되면 어떻게 되겠는가? 이것은, 3번과, 2번 학생에 대해서는 우리가 원하는4를 돌려주겠지만, 나머지, 데이터들의 등수가 모두 내려가게 될 것이다.
이것 역시 관찰과 상상력을 통해서 결과를 얻을 수 있다.
먼저, 3등과, 2등이, 4등이 되려면 어떻게 되어야 하는가? 기존의 서브쿼리로 순위를 구했던 <SQL 1-5>를 참고 할 수 있다. <SQL 1-5>의 서브 쿼리에서 조건은 크거나 같은 데이터가 아닌, 외부 테이블보다 큰 점수를 가진 데이터만 찾았다. 그러므로 다음과 같은 조건의 셀프 조인이 될 수 있다.
<SQL 1-10>
SELECT T1.StudentID ,MIN(T1.Score) Score ,COUNT(*) Ranking FROM SCORE T1 JOIN SCORE T2 ON T1.Score < T2.Score GROUP BY T1.StudentID ORDER BY Ranking |
결과는 다음과 같다.
StudentID | Score | Ranking |
1 | 90 | 1 |
4 | 85 | 2 |
3 | 80 | 3 |
2 | 80 | 3 |
6 | 70 | 4 |
제대로 된 결과 같은가? 자세히 보도록 하자. 데이터가 한 건 사라진 것을 알 수 있다.
이것이 바로 내부 조인의 핵심이다. 조인 조건에 참인 데이터만 내보낸다는 것이다.
거듭 강조하는 것은 조인은 같은 데이터를 연결 하는 것이 아닌, 조인 조건을 만족하는 데이터를 연결하는 것이다. 그러므로 한 건의 데이터가 사라진 것이다. 어떤 데이터가 사라졌는가? T1.StudentID가 5번인 1등 데이터가 사라진 것이다. 그럼, 왜 사라졌는가? 조인 조건을 만족하는 데이터는 연결이 되어서 결과에 나타나지만, 조인 조건을 만족하지 못하는 데이터는 연결되지 못하기 때문이다. 5번인 데이터의 Score는 95이다. 우리는 T1.Score < T2.Score조건으로 조인을 했다. 그러므로 T2에서, 95점 보다 큰 데이터는 없으므로 사라진 것이다. 같다 조인을 사용하든, 크기 비교 조인을 사용하든, 이것은 조심해야 할 사항이다. 기존의 <= 조건에서 5번 데이터는 사라지지 않았다. 왜냐면, T2에는 같은 95점을 가지고 있는 T2.StudentID가 5번인 데이터가 있기 때문이다.
그렇다면 어떻게 해야 하는가? 여기서 조건을 반대로 준다거나, 크거나 같다. 조건 등으로 바꿔 보는 것은 아주 좋은 시도다. 조건을 바꿔서 결과가 어떻게 나왔는지 살펴보고 왜 그렇게 되었는가를 고민해 본다면, 정답을 찾든 안 찾든, 큰 발전이 있을 것이다. 스스로 많은 방법을 연구 해보고 다음을 보도록 하자.
필자가 생각하는 방법은 외부조인을 사용하는 것이다. 외부조인의 특징이 무엇인지 확실히 알고 있어야 한다. 외부 조인은, 어느 한쪽의 기준 테이블의 모든 데이터를 조인 조건이 참이 아니더라도 결과에 내보내는 것이다. 단, WHERE조건을 통해 걸러 진 데이터는 역시 결과에 참여할 수 없는 것이다.
LEFT OUTER JOIN을 했다면, JOIN문장의 왼편에 있는 테이블이 기준 테이블이 되어서 내부적으로 먼저 접근 되어지게 되며, 왼편에 있는 데이터는 모두 결과에 나오게 된다. 왼편에 테이블과 결합되는 데이터가 오른편의 테이블에 존재하지 않는다면, 오른쪽의 데이터는 모두 NULL이 되어서 결합되어 진다.
이전의 A테이블과 B테이블에 대해서 A LEFT OUTER JOIN B ON A.no = B.no 에 대해서 수행해 보도록 하자.
A.no | B.no | A.no = B.no | |||
1 | 1 | 1 | 1 | ||
2 | 2 | 2 | 2 | ||
4 | 2 | 2 | 2 | ||
| 3 | 4 | NULL | ||
이전의 내부 조인처럼 조인 조건을 만족하는 데이터를 결과에 내보내 준다. 하지만, A테이블의 마지막 데이터만은 다른 데이터와 틀리다. A.no가 4인 데이터는 B테이블에서 전혀 찾을 수 없다. 그러므로, A.no가 4인 데이터는 B테이블에 해당하는 값들 대신에 NULL값을 가지고, 결과로 내보내 진다. 그러므로 결과에는 A테이블의 모든 데이터들이 포함되어 있다.
B테이블을 기준으로 LEFT OUTER JOIN을 한다면 어떻게 될 것인가? 조인 조건은 B LEFT OUTER JOIN A ON B.no = A.no이다. 결과는 B테이블에 있는 모든 데이터가 나오게 된다. 하지만, A테이블의 A.no가 4인 데이터는 결과에 나오지 않는다. 이처럼 외부 조인에서는 조인의 순서가 있게 된다. 그러므로 어떤 테이블의 데이터가 기준이 되어서 모두 나와야 하는지를 유심히 생각을 해서 정해야 하며, 이런 순서는 성능에 영향을 미치게 되므로 특히 조심을 해야 한다. 계속해서 강조하게 되는 것은 조인은 같다란 조건을 만족하는 데이터를 결과로 내보내는 것이 아니라,조인 조건이 참인 데이터를 내보내게 된다. 그러므로 우리는 외부조인이라 해도 크기 비교의 조건을 사용할 수 있는 것이다.
우리는 순위를 구하기 위해 어느 한쪽의 테이블의 데이터가 모두 나와야 하는지 결정해야 한다.
조건은 T1.Score < T2.Score 이다. T2테이블은 순위를 카운트하기 위해 참여하는 테이블이다. 그러므로 T1테이블의 데이터들이 모두 나와야 한다.
그러므로 다음처럼 외부 조인을 구사해야 할 것이다.
<SQL 1-11>
SELECT T1.StudentID ,MIN(T1.Score) Score ,COUNT(*) Ranking FROM SCORE T1 LEFT OUTER JOIN SCORE T2 ON T1.Score < T2.Score GROUP BY T1.StudentID ORDER BY Ranking |
결과를 살펴보도록 하자. 결과를 보면, StudentID가 1번인 데이터와 5번인 데이터가 동일하게 1등을 가지고 있는 것을 볼 수 있다. 무엇이 잘 못되었는지 알겠는가? 그것은 바로 COUNT이다.
COUNT의 특성을 제대로 알고 있다면, 쉽게 고칠 수 있을 것이다. COUNT(*)를 하는 경우에는 해당 레코드(모든 컬럼들이 포함된 한 줄의 데이터)의 모든 컬럼 값이 NULL을 가지고 있어도 카운트에 추가시킨다.
하지만, COUNT(컬럼)을 사용해서 특정 컬럼에 대해 카운트를 하게 되면, NULL값은 카운트에서 제외시키게 된다.다음의 간단한 두개의 SQL을 통해 정확히 알 수 있을 것이다.
<SQL 1-12>
SELECT COUNT(*) FROM (SELECT cast(NULL as int) a, cast(NULL as int) b) T1 go SELECT COUNT(a) FROM (SELECT cast(NULL as int) a, cast(NULL as int) b) T1 Go |
두 개의 결과가 틀리다는 것을 알 수 있다. 첫 번째 SQL문은 1을 두 번째 SQL은 0을 결과값으로 보여준다.
두 번째 SQL문은 특정 컬럼에 대해 카운트를 세었기 때문에 NULL값은 제외되었기 때문이다.
그러므로 우리는 제대로 된 등수를 구하기 위해서 COUNT를 변경해야 한다. <SQL 1-11>에서, T1.StudentID가 5인 데이터와 결합될 T2의 데이터는 없다. 그리고, T1.StudentID가 1인 데이터는 T2의 StudentID가 5인 데이터와 결합된다.(1번은 90점, 5번은 95점이므로) 그러므로, COUNT(T2.StudentID)를 한 후에 COUNT에 +1만 해주게 되면 원하는 결과가 정확히 나오게 된다.
<SQL 1-13>
SELECT T1.StudentID ,MIN(T1.Score) Score ,COUNT(T2.StudentID) +1 Ranking FROM SCORE T1 LEFT OUTER JOIN SCORE T2 ON T1.Score < T2.Score GROUP BY T1.StudentID ORDER BY Ranking |
여기까지 원하는 순위를 구하기 위한 SQL문을 모두 살펴 보았다.
우리는 서브쿼리와 셀프조인을 사용해서 순위를 각각 구해 보았다. 개발자들이 SQL문을 작성할 때 가장 중요하게 고민해야 하는 것은 바로 성능이다. 우리가 작업했던 SQL문들에 대한 성능을 측정해 보도록 하는 시간을 가져 보도록 하자. 본격적으로 성능을 살펴보기 전에 알아야 할 것은, 각 SQL Server의 버전마다. 테스트 환경의 PC성능에 따라, SQL의 환경 설정에 따라 다른 실행 계획을 나타날 것이다. 하지만, 우리는 여기서 기본적으로 어떤 요소들이 성능에 영향을 주는지는 알 수 있을 것이다.
먼저, 서브쿼리와, 셀프 조인을 했을 경우 어떻게 성능의 차이가 있는지 알아보도록 하자.
먼저 <SQL 1-5>와 <SQL 1-13>을 비교해 보도록 하겠다. 두 SQL문은 정확히 같은 결과를 돌려주도록 만들 어진 문장이다. 이 두 문장을 수행하기 전에 쿼리 분석기에서 Ctrl + K(또는 메뉴의 쿼리의 실행계획 표시를 선택한다.)를 눌러서 그림으로 실행계획을 표시하도록 설정해 놓고 수행해 보도록 하자. 우리가 수행할 두 문장이 같이 있는SQL은 <SQL 1-14>이다.
<SQL 1-14>
SELECT StudentID ,Score ,( SELECT COUNT(*) FROM SCORE T2 WHERE T2.Score > T1.Score) + 1 Ranking FROM SCORE T1 ORDER BY Ranking go SELECT T1.StudentID ,MIN(T1.Score) Score ,COUNT(T2.StudentID) +1 Ranking FROM SCORE T1 LEFT OUTER JOIN SCORE T2 ON T1.Score < T2.Score GROUP BY T1.StudentID ORDER BY Ranking go |
수행한 후 쿼리분석기의 왼쪽아래의 탭에서 실행계획을 클릭해서 실행계획을 확인 할 수 있다. 필자의 PC에서는 두 개의 SQL에 대해 거의 동일한 비용이 소모된 것으로 나타났다. 서브쿼리를 사용한 SQL은 49.99%이고, 셀프조인을 사용한 SQL은 50.01%이다. 이 것은 두 개의 비용이 별 반 차이 없음을 나타낸다. 그것은 곧, 둘 중에 어느 문장을 사용해도 좋다는 것을 나타낸다. 이 비용에 대한 %가 SQL 성능을 나타내는 절대적인 수치가 될 수 없음을 알도록 하자. 그럼에도 불구하고 실행계획의 그림에 표시되는 수치들은 큰 도움을 준다. 여기서는 두 문장이 수치적으로 어떤 차이가 있는지 알게 해준 것과 더불어, 각 SQL에서 어떤 부분에서 가장 많은 비용이 소모되었는지 알 수 있는 것이다. 두 SQL의 실행계획의 그림을 보고 어떤 부분이 가장 비용이 많이 소모되었는지 알아보자. 먼저,첫 번째 서브쿼리를 사용한 SQL의 경우에는 SCORE테이블의 PK인덱스를 스캔하는 부분이다. 이 인덱스 스캔이 두 번 일어난 것을 알 수 있다. 하나는 FROM절의 테이블에 대한 스캔이고, 또 하는, 서브쿼리에 있는 스캔이다. 두 번째 SQL 역시, 두 개의 SCORE 테이블의 PK 인덱스가 가장 많은 비용을 차지하고 있다. 그럼 우리가 튜닝할 것의 목표가 정해진 것이다. 이 SCAN에 대한 비용을 줄이는 것이다.
이 중에는 줄일 수 있는 것과 없는 것이 있다. 어떤 것일까? 바로, 한 테이블에 대한 PK SCAN이다. 테이블의 모든 데이터에 대해 등수를 구하는 것이기 때문에, 어느 한 테이블에 대한 SCAN은 피할 수 없는 운명인 것이다.
설명을 좀더 편하게 하기 위해서, 다시 한번 Ctrl + K 를 눌러서 실행계획 그림표시를 제거하고 다음을 수행한다.
<SQL 1-15>
SET STATISTICS PROFILE ON go |
<SQL 1-15>를 수행 한 다음, <SQL 1-14>를 다시 수행해 보도록 하자. 우리는 실행 결과와 더불어 다음과 같은 실행 계획들을 얻을 수 있다. 먼저 <PLAN 1-1>은 서브쿼리에 대한 실행 계획이다.
<PLAN 1-1>
No Rows Exec StmtText -- ----- ------ ----------------------------------------------------------------------------------- 1 6 1 SELECT StudentID ,Score ,( SELECT COUNT(*) FROM SCORE T2 WHERE T2.Score > 2 6 1 |--Sort(ORDER BY:([Expr1004] ASC)) 3 6 1 |--Compute Scalar(DEFINE:([Expr1004]=[Expr1002]+1)) 4 6 1 |--Nested Loops(Inner Join, OUTER REFERENCES:([T1].[Score])) 5 6 1 |--Clustered Index Scan(OBJECT:([SQL_PATTERN].[dbo].[SCORE].[PK__SCORE__79A81403] AS [T1])) 6 6 6 |--Hash Match(Cache, HASH:([T1].[Score]), RESIDUAL:([T1].[Score]=[T1].[Score])) 7 5 5 |--Compute Scalar(DEFINE:([Expr1002]=Convert([Expr1007]))) 8 5 5 |--Stream Aggregate(DEFINE:([Expr1007]=Count(*))) 9 11 5 |--Clustered Index Scan(OBJECT:([SQL_PATTERN].[dbo].[SCORE].[PK…] AS [T2]), WHERE:([T2].[Score]>[T1].[Score])) |
실행 계획을 보는 순서는, 처음 문장을 쫓아서 내려가면서, 분기점(두 개의 노드를 가지고 있는 계획)을 만나면 위의 노드부터 다시 쫓아 내려간다. 더 이상 내려갈 경우가 없을 때 해당 노드의 연산이 수행되고, 수행된 연산의 부모 노드의 연산이 시작된다. 기존의 분기되었던, 부모 노드일 경우, 다시 아래쪽 노드를 쫓아 내려가야 한다.이렇게 설명할 수 있는데, 말로는 설명이 만만하지가 않다.
일단, 필자가 실행계획에 번호(No)를 붙여 났기 때문에, 이 번호를 통해 순서를 파악해 보도록 하자.
먼저, 1번 실행 계획은 자식 노드 2번이 있기 때문에, 2번보다 1번이 먼저 수행된다. 2번도 자식 노드 3번을 가지고 있으므로 2번보다 3번이 먼저 수행되고, 3번도 자식 노드 4번이 있으므로 3번보다 4번이 먼저 수행된다. 그리고, 4번 노드는 두 개의 자식 노드 5번과 6번을 가지고 있다. 자식 노드중에 위에께 먼저 수행되므로, 5번이 6번보다 먼저 수행된다. 그런데, 5번은 자식 노드가 없다. 그러므로 5번 노드가 가장 먼저 수행된다. 다시, 6번 노드부터 쫓아가면, 6번 이후로 9번까지 하나 만의 자식 노드를 가지고 연결되어 있으므로, 이 부분에서는 9-8-7-6순으로 실행계획의 연산이 수행될 것이다. 종합해 보면, 실행계획이 실행 되는 순서는 이렇다. 5-9-8-7-6-4-3-2-1이 되는 것이다. 제일 처음 수행되는 5번 연산부터 알아보자. 5번 연산은 T1테이블에 대해 클러스터드 인덱스 스캔을 하고 있다. 이것은, T1테이블에 있는, 모든 데이터에 대해 순위를 구해야 하는 것이므로 피할 수 없는 운명이다. 그렇다면, 5번 연산은 무시하고 넘어가도록 하자. 5번 다음의 연산인 9번 연산을 살펴 보도록 하자. 9번 연산에서는 T2.Score가 T1.Score보다 큰 데이터를 찾고 있다. 실제적으로 순위를 구하기 위해 대상 데이터를 찾는 과정이다. 그런데, 9번 연산도, 클러스터드 인덱스 스캔을 하고 있다. 이것은, T1테이블의 각각의 데이터에 대해 스캔을 계속해서 수행한다는 것을 나타낸다. 즉, SCORE테이블의 데이터 건수만큼, SCORE테이블을 스캔하는 것이다. SCORE에는 현재 총 6건의 데이터가 있다. 그러나, 9번 문장의 수행 횟수(Execute)는 6이 아닌 5이다. 이것은,영리한 SQL Server가 캐쉬를 사용하기 때문이다. 서브쿼리에서 사용 되어진 내용을 메모리 캐쉬에 올려 났다가,똑 같은 조건의 매개변수 값(T1.Score가 된다.)이 들어오면 메모리 캐쉬에 있는 부분을 돌려주므로 9번 연산의 수행 횟수가 6이 아닌 5이다. SCORE테이블에는 85점의 점수를 가진 사람이 2명이 존재한다. 그러므로 첫 번째 85점에 대해서는 실제 테이블에 접근해서 서브쿼리의 결과를 찾았겠지만, 두 번째 85점에 대해서는 기존에 캐쉬에 저장된 결과를 참조하게 되는 것이다. 이것은 메모리에 접근해서 연산하는 수행 횟수를 줄여 주므로 성능의 이득을 주게 된다.
* 메모리 캐쉬에 대한 내용은 Windows Magazine 2004년 9월호 page67의 정재우님의 멀티 캐시의 효과를 통해서 더 자세히 알 수 있다.
9번 과정을 거쳐 8번과 7번을 수행하게 된다. 8번과 7번 역시 실행 횟수가 5번 이라는 것은, 9->8->7의 과정의 결과가 메모리 캐쉬에 저장되었다는 것을 의미한다. 8번 과정은 서브쿼리의 결과를 COUNT하는 과정이다. 이 과정을 통해 등수를 구할 수 있다는 것은 잘 알 것이다. 7번 과정은 스칼라 계산을 하는 과정이다. 정확히 7번 과정이 무슨 연산을 수행하는지는 필자도 모르겠다. 아무튼 7번 과정 자체가 성능에 영향을 미치는 요소는 아니므로 무시하고 넘어가도록 하겠다. 그 다음에는 6번 과정을 거치게 된다. 6번 과정은 해시 매치라는 과정인데, 이 과정은 해시 테이블을 만드는 과정이다. 해시 매치는 해시 함수를 사용해서 입력되는 값을 계산해서 테이블을 만들어 낸다.이 테이블은 조인을 위해 사용 된다. 이 해시 테이블은 T1.Score의 값에 순위를 저장하게 된다. 그러므로T1.Score가 95가 들어 왔을 때는 1이란 숫자를 돌려주고, 90이란 숫자가 들어왔을 때는 2라는 숫자를 돌려주게 된다. 이 해시 테이블은 무엇을 통해서 만들어 졌는가? 바로 9, 8, 7 과정을 통해서 만들어 진 것이다. 이 해시 매치는 9, 8, 7과는 다르게 수행횟수가 6번 인 것을 알 수 있다. 그것은, FROM절의 SCORE테이블에서 같은 등수의 데이터가 두 번째 들어온 경우라도 이 연산은 수행하게 된다는 것이다.
4번 과정은 실제적으로 조인을 수행하는 연산이다. 5번의 내용과 6번의 내용을 실제 INNER JOIN을 하는 과정이다. 6번의 내용은 해시 테이블이고 5번의 연산 결과는 실제 SCORE테이블이다. 6번의 해시 테이블에는 이미 각 점수 별 등수가 있으므로 이 두 데이터를 연결하는 과정을 수행하는 것이다.
지금까지의 과정을 종합해 보면, 5번의 과정에서 데이터를 입력 받아서, 9->8->7과정을 수행하게 된다.
이 결과를 메모리 캐쉬(해시 테이블)에 쌓아 놓게 되고, 6번과 4번 과정을 통해 해시 테이블과, 5번의 실제 테이블 사이에 데이터 조인을 수행하게 한다. 5번 연산의 모든 데이터에 대해서 동일한 과정을 반복 수행한다. 여기서 만약 5번 테이블의 데이터에 대해 조인될 결과 값이 이미 해시 테이블에 있는 경우는 9, 8, 7 과정을 생략하고 바로 6과 4를 거쳐 조인을 하게 된다.
마지막으로, 3번과 2번 과정은 설명을 하지 않아도 모두 알 수 있으리라 생각한다.
지금까지 서브쿼리의 실행계획을 살펴보았다. 무엇이 성능을 향상시킬 수 있는 요소라고 생각 되는가? SQL문에 대해서 여러 가지 해법이 있는 것처럼 성능 향상의 방법에도 여러 가지 해법이 있다.
필자가 생각하는 부분은 9번 과정이라고 생각된다. 실제로 그래픽으로 실행 계획을 보게 되면 9번 과정이 43%로 큰 부분을 차지하고 있다. 그러므로 9번 과정을 향상시킬 방법이 필요하다. 무엇이 있을까? 아마도 대부분의 성능 향상의 답은 인덱스 설정이다. 실제로 시스템의 성능을 향상시키는 것은 제대로 설정된 인덱스와 제대로 작성된SQL문이라고 생각 할 수 있다. 이 외에도 여러 가지 요소가 많이 있겠지만, 개발자가 할 수 있는 요소는 이 두 가지 일것이다. 사실, 인덱스도 개발자 스스로 만드는 것에는 무리가 있다. 하지만, 개발자가 적절한 인덱스가 무엇인지 알고, DBA나, 팀장을 통해 인덱스를 생성하도록 하는 것은 꼭 필요한 일이다.
9번 과정의 성능 향상을 위해서는 9번 과정의 WHERE절의 컬럼에 대해 성능을 향상시켜 주면 된다.
현재 SCORE테이블에는 StudentID에만 클러스터드 인덱스가 설정되어 있다.
*클러스터드 인덱스와 넌클러스터드의 인덱스의 차이점은 매우 중요하다. 이에 대해서는 다른 문서를 통해 알고 있기 바란다.
여러 가지 인덱스 전략이 있을 수 있다. StudentID + Score의 결합된 클러스터드 인덱스, 또는 Score로 만든, 넌클러스터드 인덱스. 이것에 대해 어떤 것이 최적일까라는 것을 알아내기란 쉽지 않다. 하지만, 어느 정도의 추측은 가능하다. 그 추측은 경험이 더해 질수록 더욱 정확해 질 것이다.
먼저, SCORE테이블에 제약사항이 무엇인가를 살펴보자. 그것은 바로 PK인 StudentID이다. 데이터베이스는 PK를 유지하기 위해서 UNIQUE 인덱스를 사용해야 한다. SQL Server는 PK에 대해서 기본적으로 클러스터드 인덱스를 만들어 버린다. 그리고, 클러스터드 인덱스 스캔과 테이블 스캔은 동일한 것이다. 클러스터드 인덱스는 성능 향상을 위한 아주 중요한 요소이다. 이 클러스터드 인덱스를 무조건 PK에 사용할 수는 없다. 필자의 생각은, StudentID에는 UNIQUE 넌클러스터드 인덱스를 만들어서 PK를 유지시켜 주고, Score에 대해서는 클러스터드 인덱스를 만들어 주는 것이다. 아마도, 이 SQL에 대해서는 최적을 발휘 할 수 있지 않을까 싶다.
먼저, 기존의 SCORE테이블을 보호하기 위해서, SCORE2라는 테이블을 만들고, 인덱스를 설정 해 주는 과정을 거치도록 하자.
<SQL 1-16>
SELECT * INTO SCORE2 FROM SCORE go ALTER TABLE SCORE2 ADD CONSTRAINT PK_SCORE2 PRIMARY KEY NONCLUSTERED (StudentID) go CREATE CLUSTERED INDEX SCORE2_CLU_IDX ON SCORE2(Score) go |
자 그럼 <SQL 1-5>의 서브쿼리를 다음처럼 SCORE와 SCORE2에 대해 실행해서 실행 비용을 비교해 보도록 하자. 우리는 실행 비용을 쉽게 판단 할 수 있도록 그래픽으로 실행계획을 볼 것이다. 그래픽으로 실행 계획을 보기 위해서 먼저 SET STATISTICS PROFILE OFF 를 실행해서 기존의 문자열로 출력되던 실행 프로필을 OFF 시켜야 한다.
<SQL 1-17>
SET STATISTICS PROFILE OFF |
문자열 실행 프로필을 OFF시킨 후에는 다시 Ctrl + K 를 눌러서 그래픽 실행 계획을 활성화한다.
그 다음, 다음의 두 개의 SQL을 동시에 실행한다.
<SQL 1-18>
SELECT StudentID ,Score ,( SELECT COUNT(*) FROM SCORE T2 WHERE T2.Score > T1.Score) + 1 Ranking FROM SCORE T1 ORDER BY Ranking go SELECT StudentID ,Score ,( SELECT COUNT(*) FROM SCORE2 T2 WHERE T2.Score > T1.Score) + 1 Ranking FROM SCORE2 T1 ORDER BY Ranking |
첫 번째 SQL은 기존의 SCORE테이블이고, 두 번째 SQL은 새롭게 인덱스를 설정해 준 SCORE2 테이블이다.
그래픽 실행계획을 보면, 첫 번째 SQL이 60%의 비용을, 두 번째 SQL이 40%의 비용을 사용하는 것을 알 수 있다.이것은 기존의 <PLAN 1-1>에서 9번 과정이 인덱스 스캔에서 인덱스 씨크(Seek)로 변경되었기 때문이다. 새로 만들어진 Score에 대한 클러스터드 인덱스는 9번 과정을 인덱스 씨크로 변경하는데 도움을 준 것이다.
이 외에 다른 방법들을 실제, 만들어 보고 테스트해 보기 바란다. 이런, 고민과 테스트는 자신의 발전에 큰 도움을 줄 것이다.
계속해서 셀프조인으로 해결했던 SQL의 실행 계획을 보도록 하자.
<PLAN 1-2>
No Rows Exec StmtText -- ----- ------ ----------------------------------------------------------------------------------- 1 6 1 SELECT T1.StudentID ,MIN(T1.Score) Score ,COUNT(T2.StudentID) +1 Ranking FROM SCORE T1 LEFT OUTER 2 6 1 |--Sort(ORDER BY:([Expr1004] ASC)) 3 6 1 |--Compute Scalar(DEFINE:([Expr1004]=[Expr1003]+1)) 4 6 1 |--Compute Scalar(DEFINE:([Expr1003]=Convert([Expr1008]))) 5 6 1 |--Stream Aggregate(GROUP BY:([T1].[StudentID]) DEFINE:([Expr1002]=MIN([T1].[Score]), [Expr1008]=COUNT_BIG([T2].[StudentID]))) 6 15 1 |--Nested Loops(Left Outer Join, WHERE:([T1].[Score]<[T2].[Score])) 7 6 1 |--Clustered Index Scan(OBJECT:([SQL_PATTERN].[dbo].[SCORE].[PK…] AS [T1] 8 36 6 |--Clustered Index Scan(OBJECT:([SQL_PATTERN].[dbo].[SCORE].[PK…] AS [T2] |
셀프조인으로 사용한 순위 SQL은 기존의 서브쿼리로 해결한 방법의 실행 계획과는 틀리다. 제일 눈에 뛰는 것은 조인이 Left Outer Join이며 해시매치를 사용하지 않는다는 것이다.
먼저, 이 실행계획이 실행되는 순서를 찾아보자. 한 번 해보았으므로 쉽게 알 수 있을 것이다. 가장 먼저 수행되는 것은 7번이다. 그 다음 8번, 6번부터 2번 까지 역순으로 수행된다. 7->8->6->5->4->3->2->1 순으로 수행되는 것이다. 이번 실행 계획은 이전의 서브쿼리 실행계획보다 확실히 보기 쉬운 것 같다.
먼저 7번 연산은 클러스터드 인덱스 스캔을 하고 있다. 8번 연산도 클러스터드 인덱스 스캔을 하고 있으며 실행 횟수가 7번 연산은 1번인데 비해 8번 연산은 6번 이다. 이것은 7번 연산의 데이터를 스캔 하면서 각 데이터에 대해 8번 연산을 한 번씩 수행한 다는 것이다. 이것이 실제적으로 6번 과정을 위한 과정이다. 6번 과정은 외부 조인을 수행하고 있다. T1.Score < T2.Score의 조건으로 조인을 수행한다. 6번 과정을 통해 총 15건의 데이터가 만들어 진다. 그 후 5번 과정을 거쳐서 GROUP BY를 수행하게 된다. GROUP BY와 MIN과 COUNT를 통해 해당 StudentID별로 점수와 등수를 구해낸다. 4번 연산은 스칼라 연산이고, 3번 연산도 스칼라 연산이다. 4번 연산은 결과를 보여주기 위해 형 변환을 수행하는 과정이고,(아마도, 등수를 센 것을 숫자형으로 변환시크는 과정일 것이다.) 3번 연산은 등수에 + 1을 해줌으로서 우리가 원하는 등수를 정확히 표현해 주도록 하는 과정이다. 마지막으로 2번은 결과를 보기 좋도록 정렬을 하는 과정이다.
이 실행 계획에서 성능을 향상 시킬 수 있는 부분은 무엇일까? 이것 역시, 서브쿼리와 마찬가지의 인덱스를 설정해 주면 좀 더 나은 성능을 낼 것 같다. 그러므로 SCORE2를 사용해서 똑 같은 셀프조인 쿼리를 만들어 비교를 해보도록 하자. 그래픽 실행 계획을 보는 방법에 대해서는 더 이상 설명하지 않겠다. 아울러, 실행 프로필을 설정하고 보는 방법에 대해서도 더 이상 설명하지는 않겠다.
다음과 같은 SQL을 동시에 날려보도록 하자.
<SQL 1-19>
SELECT T1.StudentID ,MIN(T1.Score) Score ,COUNT(T2.StudentID) +1 Ranking FROM SCORE T1 LEFT OUTER JOIN SCORE T2 ON T1.Score < T2.Score GROUP BY T1.StudentID ORDER BY Ranking go SELECT T1.StudentID ,MIN(T1.Score) Score ,COUNT(T2.StudentID) +1 Ranking FROM SCORE2 T1 LEFT OUTER JOIN SCORE2 T2 ON T1.Score < T2.Score GROUP BY T1.StudentID ORDER BY Ranking go |
순위를 구하기 위한 서브쿼리 방법으로 SCORE와 SCORE2를 실행해서 비교했던 것과 동일하게 SCORE를 사용한 첫 번째 셀프조인 SQL은 60%의 비용을 사용하고, SCORE2를 사용한 두 번째 SQL은 40%의 비용이 소모되는 것을 알 수 있었다.
자, 이제, 순위 구하는 SQL에서 좋은 성능을 내기 위한 방법은 무엇인가? 조인 조건이 되는 컬럼 부분에 인덱스를 만들어 주는 것이다. 그리고 넌클러스터드 인덱스보다 클러스터드 인덱스가 좋은 성능을 내는 것은 당연한 일일 것이다. 우리가 사용했던 예제 SQL만을 볼 때는 이와 같은 결론을 내릴 수 있다.
하지만, 실제업무에서는 WHERE절의 조건을 충분히 줄여 주는 대상에 인덱스가 있는 것이 아주 중요하다. 6건의 데이터를 가지고 성능을 얘기한다는 건 우스운 얘기이다. 6건의 데이터를 가지고, 아무리 복잡한 연산을 수행하는SQL을 만든다 해도, 데이터베이스는 빛의 속도로 이것을 해결할 것이기 때문이다. 하지만 6건의 데이터를 가지고 성능을 테스트한 우리는 훨씬 더 많은 수의 데이터가 들어 있는 테이블에 대한 SQL도 테스트 할 수 있는 능력을 배운 것이다.
지금 우리가 했던 SQL에 대해 최적의 인덱스가 무엇인지가 중요하지 않다. 제일 중요한 건, 어느 연산을 변경해서 성능을 향상시킬 수 있는 것을 찾아내는 부분이다. 필자 역시 계속해서 노력해야 하는 부분이고, 여러 개발자들도 끊임없이 관찰하고 연구해야 하는 부분이다.
순위 구하는 문제에 대해서 마무리 한다면, 순위를 구하기 위해 어떤 방법을 사용했는가 보다는 데이터를 어떻게 관찰하고 연구해서 결과를 만들어 냈는지의 과정이 더 중요하며, 그 과정에 대해 많은 개발자들이 끊임없이 노력하기를 바란다는 말로 마무리를 하겠다.
[출처] [본문스크랩] SQL에서 순위구하기 - 기본|작성자 야쿠
'웹개발 > DataBase' 카테고리의 다른 글
| MYSQL / FULLTEXT 검색 (0) | 2014.12.24 |
|---|---|
| mysql grant all (0) | 2014.06.13 |
| mysql 디비 업데이트 (0) | 2014.06.13 |
| MYSQL 또는 Maria 테이블에 날짜 데이터 자동 넣기 (0) | 2014.03.24 |
| pg_dump 뜨기 (0) | 2013.11.02 |