SQL 쿼리문의 최적화에 대해서

[주정섭]

제목은 거창하게 쿼리 최적화라고 했으나, 그냥 간단한 쿼리를 두고 이를 좀더 단순 최적화하는 방법을 논해보려 한다. 이보다 더욱 단순하고 좋은 방법이 나올 수도 있을 것이다. 그런 방법을 알고 있다면 댓글을 달아주시면 무지 고맙겠다.

참고로 여기서의 쿼리는, SQL 서버 2000 기준이다.

내가 근무하는 회사의 직원 중 하나가 다음과 같은 SQL 쿼리 문제를 제기했다.

1. Customer이란 테이블에 cust_name라는 필드가 있다. 이 필드는 유니코드가 아닌 일반 문자열(varchar) 타입이다.

2. cust_name필드에는 거래처명이 입력되어 있는데, 이 거래처명이 ㄱ, ㄴ, ㄷ 등 각 초성으로 시작하는 거래처 수를 알고 싶다. 예를 들어 김말동은 'ㄱ' 범위이고 '홍길동'은 'ㅎ' 범위에 해당된다.

3. 각각의 한글 초성에 해당하는 거래처 이름 수를 표시하는, 다음과 같은 결과를 쿼리를 작성해야 한다.

  ㄱ   2324  -- 기역 범위 거래처 수
  ㄴ    213  -- 니은 범위 거래처 수
  ㄷ   1123
  ...
  ㅎ    423
  기타 3434

4. 순수 SQL 쿼리문으로 풀어야한다는 전제조건이 있다. 즉 일반 컴 언어(비베나 델파이같은)를 동원하여 파싱이나, 잘라내기를 하면 안된다는 것이다.

위 문제에 대한 비슷한 답들 중 하나로서,,,,

(해결법 1)

원하는 결과를 도출하는 가장 단순 무식한 쿼리는 다음과 같다. 여기서 단순 무식하다란 말은, 최적화를 전혀 고려하지 않고, 결과를 도출해 내는 것을 최우선 목적으로 작성된 쿼리라는 것이다.

select 'ㄱ', count(*) from customer where cust_name between 'ㄱ' and 'ㄴ'
select 'ㄱ', count(*) from customer where cust_name between 'ㄴ' and 'ㄷ'
...
-- 초성 수만큼 반복
...
select 'ㅎ', count(*) from customer where cust_name between 'ㅎ' and '힣'

(해결법 2)

다음 방법은, 주위 다른 사람이 제언한, 가장 최적화된 방법은 아니지만, 그래도 쓸만한 결과를 도출해내는 쿼리다.

SELECT 'ㄱ', COUNT(cust_name) FROM customer
WHERE cust_name >= '가' AND cust_name < '나'
union
SELECT 'ㄴ', COUNT(cust_name) FROM customer
WHERE cust_name >= '나' AND cust_name < '다'
union
SELECT 'ㄷ', COUNT(cust_name) FROM customer
WHERE cust_name >= '다' AND cust_name < '라'
union
SELECT 'ㄹ', COUNT(cust_name) FROM customer
WHERE cust_name >= '라' AND cust_name < '마'
union
SELECT 'ㅁ', COUNT(cust_name) FROM customer
WHERE cust_name >= '마' AND cust_name < '바'
union
SELECT 'ㅂ', COUNT(cust_name) FROM customer
WHERE cust_name >= '바' AND cust_name < '사'

이 방법의 문제는 실행 속도에 있다. 한글 초성 갯수 만큼 테이블 전체를 수차례 검색해야 한다. 거래처 테이블의 레코드 수가 많다면 엄청난 부하가 발생할 것이다. 이름 필드에 인덱스가 걸려 있던 말던 상관 없다. 이런 식의 select 문은 각 select 문마다 무조건 테이블 전체 스캔이 발생한다. 만일 이름 필드에 클러스터드 인덱스가 걸려 있다면, 이 검색은 좀더 빨라 질 수도 있겠지만, 이름 필드에 클러스터드 인덱스를 거는 것은, 클러스터 인덱스의 중요성에 비춰보면 매우 심한 리소스 낭비다. 클러스터 인덱스는 이보다 더 중요한 필드에 걸어야 할 것이다.

(해결법 3)

다음 쿼리는 이 문제를 들은 내가 근무하는 회사의 다른 직원이 작성했다.

select a1.aa, count(*)
from (
 select
  case   
   when substring(cust_name, 1, 1) >= 'ㄱ' and substring(cust_name, 1, 1) < 'ㄴ' then 'ㄱ'
   when substring(cust_name, 1, 1) >= 'ㄴ' and substring(cust_name, 1, 1) < 'ㄷ' then 'ㄴ'
   when substring(cust_name, 1, 1) >= 'ㄷ' and substring(cust_name, 1, 1) < 'ㄹ' then 'ㄷ'
   when substring(cust_name, 1, 1) >= 'ㄹ' and substring(cust_name, 1, 1) < 'ㅁ' then 'ㄹ'
   when substring(cust_name, 1, 1) >= 'ㅁ' and substring(cust_name, 1, 1) < 'ㅂ' then 'ㅁ'
   when substring(cust_name, 1, 1) >= 'ㅂ' and substring(cust_name, 1, 1) < 'ㅅ' then 'ㅂ'
   when substring(cust_name, 1, 1) >= 'ㅅ' and substring(cust_name, 1, 1) < 'ㅇ' then 'ㅅ'
   when substring(cust_name, 1, 1) >= 'ㅇ' and substring(cust_name, 1, 1) < 'ㅈ' then 'ㅇ'
   when substring(cust_name, 1, 1) >= 'ㅈ' and substring(cust_name, 1, 1) < 'ㅊ' then 'ㅈ'
   when substring(cust_name, 1, 1) >= 'ㅊ' and substring(cust_name, 1, 1) < 'ㅋ' then 'ㅊ'
   when substring(cust_name, 1, 1) >= 'ㅋ' and substring(cust_name, 1, 1) < 'ㅌ' then 'ㅋ'
   when substring(cust_name, 1, 1) >= 'ㅌ' and substring(cust_name, 1, 1) < 'ㅍ' then 'ㅌ'
   when substring(cust_name, 1, 1) >= 'ㅍ' and substring(cust_name, 1, 1) < 'ㅎ' then 'ㅍ'
   when substring(cust_name, 1, 1) >= 'ㅎ'  then 'ㅎ'   
   else '기타' end as aa            
 from customer ) as a1
group by a1.aa
order by a1.aa

그런데 이 쿼리는 정답에 접근했고, 테이블 스캔도 한번만 발생하므로, 최적화도 잘된 편이지만, substring 함수를 잘못 사용하는 실수를 저질렀다. 한글은 두바이트로 이뤄지는데, 그 첫바이트를 얻어낸다고 해서 초성값을 얻어낼 수는 없기 때문이다.

(해결법 4)

좀더 나은 방법은 위 쿼리를 다음과 같이 수정하는 것이다.

select a1.aa, count(*)
from (
 select
  case   
   when cust_name >= '가' and cust_name < '나' then 'ㄱ'
   when cust_name >= '나' and cust_name < '다' then 'ㄴ'
   when cust_name >= '다' and cust_name < '라' then 'ㄷ'
   when cust_name >= '라' and cust_name < '마' then 'ㄹ'
   when cust_name >= '마' and cust_name < '바' then 'ㅁ'
   when cust_name >= '바' and cust_name < '사' then 'ㅂ'
   when cust_name >= '사' and cust_name < '아' then 'ㅅ'
   when cust_name >= '아' and cust_name < '자' then 'ㅇ'
   when cust_name >= '자' and cust_name < '차' then 'ㅈ'
   when cust_name >= '차' and cust_name < '카' then 'ㅊ'
   when cust_name >= '카' and cust_name < '타' then 'ㅋ'
   when cust_name >= '타' and cust_name < '파' then 'ㅌ'
   when cust_name >= '파' and cust_name < '하' then 'ㅍ'
   when cust_name >= '하' then 'ㅎ'
   else '기타' end as aa            
 from customer ) as a1
group by a1.aa
order by a1.aa

(해결법 5)

그런데, (해결법 4) 쿼리는 case문의 성격을 잘 이해하면 아래와 같이 줄일 수 있다.
 
select a1.aa, count(*)
from (
  select
    case
      when cust_name < '가'  then '기타'
      when cust_name < '나'  then 'ㄱ'
      when cust_name < '다'  then 'ㄴ'
      when cust_name < '라'  then 'ㄷ'
      when cust_name < '마'  then 'ㄹ'
      when cust_name < '바'  then 'ㅁ'
      when cust_name < '사'  then 'ㅂ'
      when cust_name < '아'  then 'ㅅ'
      when cust_name < '자'  then 'ㅇ'
      when cust_name < '차'  then 'ㅈ'
      when cust_name < '카'  then 'ㅊ'
      when cust_name < '타'  then 'ㅋ'
      when cust_name < '파'  then 'ㅌ'
      when cust_name < '하'  then 'ㅍ'   
      when cust_name < '힣'  then 'ㅎ'   
      else '기타'
  end as aa            
  from customer ) as a1   
group by a1.aa
order by a1.aa

내가 판단하는 가장 잘된 쿼리는 (해법 5)마지막의 것이다. 여러분들은 어떠한가? 더 좋은 방법이 있다면 제언해 주기 바란다.

어떤 쿼리가 인덱스를 이용하지 못하는 경우라면, 즉 전체 테이블 스캔을 피할수가 없다면, 전체 테이블 스캔 횟수를 줄이는 방법이 최선일 것이다.

전체 테이블 스캔 수를 줄였다면, 쓸데없는 조건문, 함수 호출을 줄이는 것이 최적화의 다음 단계가 될 것이다.

[비젼]

CPU 이용률 이런것도 참고해야 하는데...

[꼬로미]

Inline view라고 속칭하는 From절의 Select절은 지양하는 것이 원칙입니다. 일반 Ansi 표준 SQL에서 허용할 수 있는 문장을 굳이 inline view 사용해서 표현할 이유도 없고요 상기 퀴리는 Inline view의 결과을 해싱하고 다시 그것을 그룹핑한다음 또 그것을 소트까지 했습니다. 상기 쿼리에서 수정할 것은

[꼬로미]

Order by 절입니다. 이미 Group by절에서 정렬이 이루어 졌기에 의미 없으며 오히려 엔진에 쓸데없는 Overload를 가중합니다. (실제 차이는 별로 없겠지만 데이터 건수가 많을 경우 정렬 한 문장이 어디입니까 ^^) 마지막으로 inline view를 쓰지 않는 것이 좋겠죠.. 물론 대안 없이 말씀 드리는 것도 아니고요 ^^

[꼬로미]

Select case when cust_name < '가' then '기타' when cust_name < '나' then 'ㄱ' when cust_name < '다' then 'ㄴ' when cust_name < '라' then 'ㄷ' when cust_name < '마' then 'ㄹ'

[꼬로미]

when cust_name < '바' then 'ㅁ' when cust_name < '사' then 'ㅂ' when cust_name < '아' then 'ㅅ' when cust_name < '자' then 'ㅇ' when cust_name < '차' then 'ㅈ' when cust_name < '카' then 'ㅊ'

[꼬로미]

when cust_name < '타' then 'ㅋ' when cust_name < '파' then 'ㅌ' when cust_name < '하' then 'ㅍ' when cust_name < '힣' then 'ㅎ' else '기타' end as chr, count(*) from customer group by 1;

[꼬로미]

상기 5항의 문장은 위와 같이 Ansi 표준 SQL 로 바뀌어 지겠죠 이경우 Mysql, firebird, Oracle, Sql-Server, MDB,.... 모두 통용이 가능합니다.

[꼬로미]

그리고 상기와 같이 이미 알고 있는 최대 레코드수가 적고 또한 한정적일때에는 Fech record를 쓸 이유가 없습니다. 또한 그렇게 사용해야 Client Porgram에서도 Looping하면서 비교할 필요도 없고요 그리고 또한, 쓸데없는 네트워크 트랙픽을 유발할 이유도 없고요 상기는 한건의 레코드로 유도하는 것이 정답입니다.

[꼬로미]

Select count(case when (cust_name < '가' or cust_name > '힣') then cust_name end) as f0,

[꼬로미]

count(case when (cust_name >= '가' and cust_name < '나') then cust_name end) as f1,

[꼬로미]

count(case when (cust_name >= '나' and cust_name < '다') then cust_name end) as f2,

[꼬로미]

count(case when (cust_name >= '다' and cust_name < '라') then cust_name end) as f3,

[꼬로미]

count(case when (cust_name >= '라' and cust_name < '마') then cust_name end) as f4,

[꼬로미]

count(case when (cust_name >= '마' and cust_name < '바') then cust_name end) as f5,

[꼬로미]

count(case when (cust_name >= '바' and cust_name < '사') then cust_name end) as f6,

[꼬로미]

count(case when (cust_name >= '사' and cust_name < '아') then cust_name end) as f7,

[꼬로미]

count(case when (cust_name >= '아' and cust_name < '자') then cust_name end) as f8,

[꼬로미]

count(case when (cust_name >= '자' and cust_name < '차') then cust_name end) as f9,

[꼬로미]

count(case when (cust_name >= '차' and cust_name < '카') then cust_name end) as f10,

[꼬로미]

count(case when (cust_name >= '카' and cust_name < '타') then cust_name end) as f11,

[꼬로미]

count(case when (cust_name >= '타' and cust_name < '파') then cust_name end) as f12,

[꼬로미]

count(case when (cust_name >= '파' and cust_name < '하') then cust_name end) as f13,

[꼬로미]

count(case when (cust_name >= '하' and cust_name < '힣') then cust_name end) as f14 from customer

[꼬로미]

여기서의 문제는 상기 5항을 수정해서 Ansi SQL로 최적화 한 제가올린 쿼리보다 최고 4배의 비교루틴을 엔진에서 감당해야 함인데 그러나 이것은 1회 패치에서 이루어진 메모리연산이므로 크게 성능의 차이는 없다고 봅니다. 쿼리상의 장점은 Grouping에 따른 레코드 정렬이 필요없고요

[꼬로미]

한건의 레코드의 이득은 오히려 그것을 상쇄시켜 줍니다. 그리고 쿼리문장이 길다고 가독성이 떨어지는 것도 물론 아닙니다. 오히려 더 직관적이죠.. 그럼이만...

[푸르름]

그런데 case문보다 decode문이 더 효율적이라 들었습니다만....