SQL문을 병렬 수행할 시에 알아야할 8가지 대체 공식

SQL문을 병렬 수행할 시에 알아야할 8가지 대체 공식

2010 11월 10일 PM 1:32

Flexviews와 Shard-Query와 같은 수많은 개인 프로젝트들을 진행하며, 쿼리문을 조작하여 같은 결과를 도출하지만 다른 형태를 갖게끔 해왔습니다. Flexviews는 여러시간에 걸쳐 일을 수행하게끔 해주며, 요약 테이블을 증분 관리할 수 있게 해줍니다. Shard-Query를 통해서는 Gearman 어플리케이션의 worker 프로세스들에 쿼리를 분산시켜, 하나 이상의 MySQL서버에서 병렬로 수행될 수 있게끔 해주었습니다.  

두개의 툴은 모두 비슷한 대체 조건을 적용하여 구성되었습니다. 수많은 수학 분야중에 제가 제일 좋아했던 것은, 하나의 수학 공식을 그와는 다른 종류의 식으로 표현해 내는 방법이었으며, 이를 통해 해결이 불가능해보였던 문제도 쉽게 해결할 수 있었습니다. 간단한 치환 작업을 통하여, 병렬 또는 다중 샤드에서의  쿼리 수행과 같은 어려운 SQL문제 또한 쉽게 해결할 수 있습니다. 

제가 사용했던 툴에 적용되었던 공식들을 모아봤습니다. 이중 대부분은 일반적인 SQL 어플리케이션에서는 그다지 유용하지 않을 것입니다. 이것들은 Shard-Query와 같은 병렬 쿼리 수행 툴을 사용할 때에 의미가 있을 것입니다.

공식 #1 - 대수 법칙을 SQL문에 적용시키기

이 공식은 쿼리 최적화에 대하여 매우 중요한 내용이 되겠습니다. MySQL 옵티마이저는 그렇게 똑똑하지 못하여 인덱스를 제대로 이용하지 못할 수 있습니다.

예제:

SELECT * FROM t WHERE some_col + interval 10 minute >= now()

 

이와 같이 바꿀 수 있습니다:

SELECT * FROM t WHERE some_col >= now() - interval 10 minute;

이것은 아주 일반적으로 적용할 수 있는 훌륭한 법칙입니다. 하지만 다른 모든 내용들은 병렬 쿼리와 관련된 내용입니다.

공식 #2 - IN LIST문은 UNION ALL문으로 바뀔 수 있습니다. (IN list 조건이 겹치지 않는한)

예제:

SELECT * FROM t WHERE some_col IN (1,2,3)

이와 같이 바꿀 수 있습니다:

SELECT * FROM t WHERE some_col = 1

UNION ALL

SELECT * FROM t WHERE some_col = 2

UNION ALL

SELECT * FROM t WHERE some_col = 3

공식 #3 - COUNT(*) = SUM(1)

예제:

SELECT COUNT(*) FROM t 

이와 같이 바꿀 수 있습니다:

SELECT SUM(1) as `COUNT(*)` FROM t

공식 #3 은 Flexviews와 같은 매우 제한적인 부분에서만 사용 가능합니다. 

공식 #4 - AVG(expr) = SUM(expr)/COUNT(expr) 

예제:

SELECT AVG(some_col) FROM t 

이와 같이 바꿀 수 있습니다:

SELECT SUM(some_col)/COUNT(some_col) as `AVG(some_col)` FROM t

물론 여러분도 평균= 합계/개 수 라는 것을 알고 계실것입니다. 하지만 이렇게 구문을 나누어 줌으로써 일을 세분화 시킬 수 있다는 것을 기억해두시기 바랍니다.

공식 #5 - In some cases, BETWEEN can be expressed as an IN LIST (for DATE and INTEGER type columns) This conversion to IN makes 레인지 룩업이 가능하게 됩니다. 

예제:

SELECT * FROM t  where some_col between 1 and 3

이와 같이 바꿀 수 있습니다:

SELECT * FROM t where some_col IN (1,2,3)

다시 한번 말씀 드리지만, IN list 형태로 변환이 되었다면, IN 을 다시 UNION ALL형태로 변현하는 것도 가능하게 될 수 있습니다. (공식 #2와 #6을 보십시오)

공식 #6 - 만약 트랜잭션 고립화 단계가 read-committed로 설정되어 있고 쿼리에 집합 함수가 사용되지 않았다면, UNION ALL의 각 파트들은 병렬로 수행시킬 수 있습니다. 공식 #2의 예제 구문이 이 문제에 대한 훌륭한 예시가 될 것입니다. UNION ALL의 개별 요소들은 모두 독립족이며, 추가적인 변경없이도 병렬 수행이 가능합니다.  

공식 #7 - MIN/MAX/AVG/SUM/COUNT와 같은 집합 함수가 사용된 쿼리를 재작성하여 병렬 수행이 가능하게끔 해줄 수 있습니다. 다만, 아래와 같은 법칙을 통해 UNION ALL형태로 다시 래핑해주셔야 합니다:

COUNT(expr) := SUM(`COUNT(expr)`) 

AVG(expr) := SUM(`SUM(expr)`) / SUM(`COUNT(expr)`) # see below and 공식 #4

예제:

SELECT AVG(some_col) as expr FROM t WHERE col2 in (1,2,3)

 

이와 같이 바꿀 수 있습니다:

SELECT SUM(the_union.expr_s) / SUM(the_union.expr_c) as expr

FROM (

SELECT SUM(some_col) as expr_s, COUNT(some_col) as expr_c FROM t WHERE col2 = 1

UNION ALL

SELECT SUM(some_col) as expr_s, COUNT(some_col) as expr_c FROM t WHERE col2 = 2

UNION ALL

SELECT SUM(some_col) as expr_s, COUNT(some_col) as expr_c FROM t WHERE col2 = 3

) as the_union

공식 #8 - STDDEV,VARIANCE 등등과 같은 집합 함수가 사용된 쿼리를 재작성하여 병렬 수행이 가능하게끔 해줄 수 있습니다. 다만, 아래와 같은 법칙을 통해 최종 단계까지 계속 진행해 나가야 합니다.

STDDEV(expr) := SELECT ... ,expr, ... GROUP BY expr

예제:

SELECT STDDEV(some_col) as expr FROM t WHERE col2 in (1,2,3)

이와 같이 바꿀 수 있습니다:

SELECT STDDEV(the_union.expr) as expr

FROM (

SELECT some_col as expr FROM t WHERE col2 = 1 GROUP BY some_col

UNION ALL

SELECT some_col as expr FROM t WHERE col2 = 2 GROUP BY some_col

UNION ALL

SELECT some_col as expr FROM t WHERE col2 = 3 GROUP BY some_col

) as the_union