print('yongineer')

반복문

January 11, 2021

반복문 의존증

모든 엔지니어 또는 프로그래머는 공통적인 병을 가지고 있다 조금은 과장된 표현이지만 모두 최소한 한 번은 반복문 의존증이라는 병을 경험한 적이 있다. 하지만 SQL에는 반복문이 없다. SQL은 일부러 반복문을 언어 설계에서 제외 하였다. 왜냐하면 “반복문은 필요 없다”라는 생각 때문이다.

RDB를 처음 생각해냈던 Edgar F. Codd는 저서 Relational Database : a practical foundation for productivity (1989)에서 다음과 같이 말했다.

💡 관계 조작은 관계 전체를 모두 조작의 대상으로 삼는다. 이러한 것의 목적은 반복을 제외하는 것이다. 최종 사용자의 생산성을 생각하면 이러한 조건을 만족해야 한다. 그래야만 응용 프로그래머의 생산성에도 기여할 수 있을 것이다.

‘관계 조작’이라는 것은 SQL이라고 생각하면 된다. 따라서 SQL은 처음부터 ‘반복문을 제외’하고 만들어진 언어라는 것이다. 그리고 Codd는 이러한 이유를 ‘그게 편하니까’라고 설명한다.

1. 내부적으로는 반복문 사용

업계에 종사하고 있다면 다음과 같은 코드를 가진 시스템을 보았을 것이다.

  • 온라인 처리에서 화면에 명세를 출력하고자 레코드 하나씩 접근하는 SELECT구문을 반복 사용
  • 배치 처리에서 대량의 데이터를 처리할 때 레코드를 하나씩 호스트 언어에서 처리하고 테이블에 갱신

적재적소라는 말처럼 SQL을 적용하기 힘든 작업에 무리하게 SQL을 사용할 필요는 없다. 또한 미들웨어 또는 ORM등의 프레임워크가 내부적으로 반복계 코드를 사용해서, 사용자에게 따로 선택의 여지가 없는 경우도 있다.

그런데 사실 반복이 없으면 프로그램의 생산성 향상 외에도 Codd가 언급하지 않은 큰 장점이 생긴다. 반대로 말하면 반복계 코드에서 발생하는 큰 단점이다.

반복계의 공포

먼저 다음과 같은 2개의 테이블이 있다고 가정해 보자

Sales

https://yongineer.duckdns.org/sql/092.png

Sales2

https://yongineer.duckdns.org/sql/093.png

Sales 테이블은 각 기업의 회계연도별 매출을 기록한다. 다만 연도가 연속되지는 않는다. 어쨌거나 이러한 데이터를 사용해 특정 기업의 매출 변화를 조사할것이다. 그리고 결과는 var필드를 추가한 Sales2 테이블에 등록한다. 이때 var필드는 다음과 같은 규칙에 따라 결정된다.

  • 이전 데이터가 없을 경우 : NULL
  • 이전 데이터보다 매출이 올랐을 경우 : +
  • 이전 데이터보다 매출이 내렸을 경우 : -
  • 이전 데이터와 매출이 동일한 경우 : =

따라서 최종적으로 Sales2 테이블에는 다음과 같은 데이터가 들어간다.

https://yongineer.duckdns.org/sql/094.png

아래 코드는 이러한 문제를 해결하는 전형적인 방법 중 하나로 Oracle의 PL/SQL코드이다.

CREATE OR REPLACE PROCEDURE PROC_INSERT_VAR
IS
	/* 커서 선언 */
	CURSOR c_sales IS
		SELECT company, year, sale FROM Sales ORDER BY company, year;
	
	/* 레코드 타입 선언 */
	rec_sales c_sales %ROWTYPE;

	/* 카운터 */
	i_pre_sale INTEGER :=0;
	c_company CHAR(1) :='*';
	c_var CHAR(1) :='*';

BEGIN

OPEN c_sales;
	LOOP
	/* 레코드를 패치해서 변수에 대입 */
	fetch c_sales into rec_sales;
	/* 레코드가 없다면 반복을 종료 */
	exit when c_sales%notfound;

	IF (c_company = rec_sales.company) THEN
		/* 직전 레코드가 같은 회사의 레코드 일 때 */
		/* 직전 레코드와 매상을 비교 */
		IF (i_pre_sale < rec_sales.sale) THEN
			c_var :='+';
		ELSEIF(i_pre_sale > rec_sales.sale) THEN
			c_var :='-';
		ELSE
			c_var :='=';
		END IF;
	
	ELSE
		c_var :=NULL;
	
	END IF;
	
	/* 등록 대상이 테이블에 테이블을 등록 */
	INSERT INTO Sales2 (company, year, sale, var)
		VALUES (rec_sales.company, rec_sales.year, rec_sales.sale, c_var);
	
	c_company := rec_sales.company;
	i_pre_sale := rec_sales.sale;
	
	END LOOP;
	
	CLOSE c_sales;
	commit;

END;

특정 연도의 레코드와 직전 연도의 레코드를 비교하는 로직을 반복하는 것은 전형적인 ‘record at a time (한 번에 한 레코드)‘적 사고방식이다.

반면 여러 행을 한꺼번에 처리하는 SQL을 포장계라고 부른다. 포장계 SQL은 비즈니스 로직을 SQL에 넣으려다 보니 구문이 복잡해져서 유지 보수성이 떨어지는 SQL 구문이 만들어지기도 한다.

1. 반복계의 단점

한 마디로 설명한다면 ‘성능’이다. 반복계로 구현한 코드는 포장계로 구현한 코드에 성능적으로 이길 수 없다. 처리하는 레코드 수가 적을 때는 반복계와 포장계에 큰 차이가 없다. 게다가 오히려 반복계가 빠른 경우도 있다. 하지만 처리하는 레코드 수가 많아지면 많아질수록 차이는 점점 더 벌어진다.

https://yongineer.duckdns.org/sql/095.png

반복계의 처리 시간이 처리 대상 레코드 수에 대해 선형으로 증가하는 이유는 간단하다. 반복계의 처리 시간은 <처리 횟수> * <한 회에 걸리는 처리 시간>이므로 <한 회에 걸리는 시간>이 일정하다고 가정하면 처리 횟수 (처리 대상 레코드 수)에 비례할 것이다.

반대로 포장계의 경우, SQL 패턴이 다양하므로 완전히 이러한 대수 함수의 곡선이 된다고 단언할 수는 없다. 다만 인덱스를 사용한 접근이고, 실행 계획 변동이 없다고 한다면 대부분 이렇게 완만한 커브를 그리게 된다.

SQL실행의 오버 헤드

SQL을 실행 할 때는 데이터를 검색하거나 연산하는 실제의 SQL 처리 이외에도 다양한 처리가 이루어 진다.

  • 전처리

    1️⃣ SQL 구문을 네트워크로 전송

    2️⃣ 데이터베이스 연결

    3️⃣ SQL 구문 파스

    4️⃣ SQL 구문의 실행 계획 생성 또는 평가

  • 후처리

    5️⃣ 결과 집합을 네트워크로 전송

1️⃣ 과 5️⃣ 는 SQL을 실행하는 애플리케이션과 데이터베이스가 물리적으로 같은 본체에 있다면 발생하지 않을 것이다. 하지만 일정 규모 이상의 시스템에서는 보통 애플리케이션 서버와 데이터베이스 서버를 물리적으로 분리해서 사용하므로 SQL 구문 또는 결과 집합을 네트워크로 전송해야 한다. 그렇다고 해도 일반적으로 두 가지는 같은 데이터센터 내부의 동일 LAN위에 있으므로 전송 속도 자체는 고속(거의 ms) 인 만큼 오버헤드가 딱히 일어나지 않는다.

2️⃣ 는 데이터베이스에 SQL 구문을 실행하기 위한 작업이다. 일단 데이터베이스에 연결해서 세션을 설정해야 하므로 발생하는 처리이다. 하지만 최근에는 애플리케이션에서 미리 연결을 일정 수 확보해서 이런 오버헤드를 감소시키는 커넥션 풀(Connection Pool)이라는 기술을 사용한다. 따라서 2️⃣도 거의 문제되지 않는다.

오버헤드 중에서도 가장 영향이 큰 것은 3️⃣또는4️⃣이다. 특히 조금 성가신것을 고른다면 3️⃣, SQL 파스(구문 분석)이다. 파스는 DBMS마다 하는 방법도 미묘하게 다르고 종류도 굉장히 많다. 특히 종류에 따라 느린 부분은 0.1초 ~ 1초 정도 걸린다. 이는 다른 오버헤드가 밀리 초로 영향을 미치는 것에 비해 굉장히 크다. 그리고 파스는 데이터베이스가 SQL을 받을 때마다 실행되므로 작은 SQL을 여러 번 반복하는 반복계에서는 오버헤드가 높아질 수 밖에 없다.

병렬 분산이 힘들다

반복계는 반복 1회 마다의 처리를 굉장히 단순화 한다. 따라서 리소스를 분산해서 병렬 처리하는 최적화가 안된다. CPU의 멀티 코어로 분산 처리를 할 수 없는 것은 물론 저장소의 분산 효율이 낮다. 데이터베이스 서버 저장소는 대부분 RAID 디스크로 구성되어 I/O 부하를 분산 할 수 있게 되어 있다. 하지만 반복계에서 실행하는 SQL구문은 대부분 단순해서 1회의 SQL구문이 접근하는 데이터양이 적다. 따라서 I/O를 병렬화 하기 힘들다는 단점이 있다.

데이터베이스의 진화로 인한 혜택을 받을 수 없다.

DBMS는 단순한 SQL 구문과 같은 ‘가벼운’처리를 빠르게 만드는 것은 사실 안중에도 없다. 따라서 반복계는 미들웨어 또는 하드웨어의 진화에 따른 혜택을 거의 받을 수 없다. 실제로 반복계의 처리가 느려서 문제가 되는 경우 그냥 대충 스케일업을 하는 경우도 있다. 하지만 물리 리소스가 bottle neck이 걸리는 경우가 아니라면 스케일업을 해도 속도가 빨라지지 않는 경우도 많다.

위 세가지 이유로 반복계는 포장계에 비해 성능적 관점에서 비교가 불가능하다. 물론 이러한 비교가 성립되려면 포장계의 SQL이 충분히 튜닝되어 있다는 것이 전제되어야 한다. 일반적으로 포장계의 SQL은 반복계에 비해 굉장히 복잡하다. 따라서 튜닝되지 않은 상태에서는 반복계에게 질 수도 있다. 하지만 포장계의 SQL구문은 튜닝 가능성이 굉장히 높으므로 제대로 튜닝한다면 처음과 비교해서 현격한 성능 차이가 있을 것이다.

이러한 포장계의 장점은 반대로 반복계의 단점이라고 할 수 있다. 따라서 반복계는 단지 느리기만 한 것이 아니라 느린 구문을 튜닝할 수 있는 가능성도 거의 없다고 할 수 있다. 반복계가 정말 무서운 것은 바로 이 단점이다.

2. 반복계를 빠르게 만드는 방법은 없을까?

반복계를 포장계로 다시 작성

이는 애플리케이션 수정을 의미한다. 컷오버직전의 성능 검증에서 문제가 발견되어 야근과 철야에 시달리고 있는 현장의 우리들 앞에 이러한 막돼먹은 ‘제안’으로 빈축을 사는 것이 일반적인 ‘컨설턴트’이다. 하지만 실제 상황에서는 이러한 선택지를 사용할 수 없는 경우가 많다.

각각의 SQL을 빠르게 수정

반복계에서 사용하는 SQL구문은 너무 단순하다. 실행 계획을 보아도 unique scan또는 index range scan정도 뿐이다. 이런 간단한 구문의 어디를 어떻게 튜닝해야 할까?

게다가 INSERT구문을 반복하는 경우도 있다. INSERT구문은 SELECT구문보다 고속화가 더 어렵다. 따라서 튜닝 가능성이 더욱 제한된다.

다중화 처리

앞선 선택지 보단 가장 희망적인 선택지이다. CPU 또는 디스크와 같은 리소스에 여유가 있고 처리를 나눌 수 있는 키가 명확하게 정해져 있다면, 처리를 다중화해서 성능을 선형에 가깝게 스케일할 수 있다. 물론 애플리케이션 수정이 필요하지만, 처음부터 다중도를 설정할 수 있게 애플리케이션을 구성했다면 코드를 변경하지 않고도 확장 가능하다. 하지만 반대로 데이터를 분할할 수 있는 명확한 키가 없거나, 순서가 중요한 처리, 병렬화했을 때 물리 리소스가 부족하다면 이러한 방법은 사용할 수 없다.

이렇게 반복계라는 것은 튜닝의 선택지가 굉장히 한정적이다. 따라서 반복계로 만든 애플리케이션이 느리다면 대대적인 애플리케이션 수정을 각오해야 한다. 다만 수백 개 정도만 반복한다면, 반복계라도 성능이 충분히 괜찮게 나온다. 따라서 무조건 반복계를 적대시할 필요는 없다. 하지만 수백 또는 수천만 번의 반복을 기본이라 생각하는 일괄 처리에서는 반드시 주의가 필요하다. 또한 프레임워크 또는 업무 패키지 내부에서 반복계를 사용하는 경우도 꽤 있는데, 이런 경우에는 애플리케이션 수정이 더욱 힘들어진다.

3. 반복계의 장점

이것은 모두 반복계의 SQL구문이 지나치게 단순해서 생기는 장점이다.

SELECT sale FROM sales2 WHERE company = 'A';

이런 단순한 쿼리는 실행 계획도 엄청나게 단순하다.

https://yongineer.duckdns.org/sql/096.png

실행 계획의 안정성

실행 계획이 단순하다는 것은 해당 실행 계획에 변동 위험이 거의 없다라는것을 나타낸다. 변동이 일어난다고 해 봤자 겨우 옵티마이저에서 사용하는 인덱스를 바꾸는 정도이다. 따라서 실제 운용 중에 갑자기 실행 계획이 바뀌어 느려지는 현상을 일어나지 않는다. 이런 현상은 비용 기반(cost base)의 옵티마이저에서는 숙명적인 것인데, 그로부터 조금은 자유로워질 수 있다는 것이다. 특히 SQL구문 내부에서 결합을 사용하지 않아도 된다는 것이 굉장히 크게 작용한다. 실행 계획 변동에서 가장 골칫거리가 되는 것이 바로 결합 알고리즘의 변경이 때문이다.

이는 어떤 의미에서 규칙 기반(rule base)에서 비용 바탕으로 변화하는 DBMS의 빈화를 거스르는 것이다. 옵티마이저가 완벽하지 않은 현재 시점에서 안정적인 성능을 확보할 수 있다는 것은 정말 어마어마한 장점이다.

반대로 말하면, 이는 포장계의 단점이다. 포장계는 SQL구문이 복잡한 만큼 실행 계획의 변동 가능성이 굉장히 크다. 물론 옵티마이저가 잘 할 것으로 생각하면 장점이고, 위험하다 생각하면 단점이 되는 미묘한 부분이다. 하지만 현 시점에서, 실행 계획 변동이 쉬운 SQL구문에 대해서는 부분적으로 힌트 구문을 사용해 실행 계획을 사용하거나, 조금 단순한 구문을 사용하는 것이 좋다.

예상 처리 시간의 정밀도

실행 계획이 단순하고 성능이 안정적이라는 것은 추가적인 장점을 가져온다. 바로 예상 처리 시간의 정밀도가 높다는 것이다. 반복계의 처리 시간은 다음과 같이 표현 할 수 있다.

  • <처리 시간> = <한 번의 실행 시간> * <실행 횟수>

실행 횟수는 기능 요건으로 알 수 있다. 한편 한 번의 실행 시간은 대충 0.1밀리 초 ~ 0.5초 정도 사이이다. 0.1밀리 초와 0.5초는 5000배 차이가 있다 생각할 수 있지만 절대치로 보면 그렇지만 SQL 구문은 미세한 조건의 차이로 수배 ~ 수백 배의 차이가 나오는 것이므로, 이 정도만 해도 예상을 위한 정밀도가 높다고 말할 수 있다. 포장계는 실행 계획에 따라 성능이 전혀 달라지므로 프로그램의 사양을 사전에 예상하기 조차 힘들다. 그에 비하면 굉장히 괜찮다.

정밀한 예상을 하려면, 어느 정도 규모가 있는 데이터를 넣어 모델 검증을 한 뒤에, 몇 개에서 어느 정도의 실행 시간이 나오는지 측정하고 (10만 건, 100만 건, 1000만 건 등), 실행 시간이 선형으로 증가하는지 여부와 기울기를 확인해서 계산해야 한다.

트랜잭션 제어가 편리

반복계의 또 하나의 장점은 트랜잭션의 정밀도를 미세하게 제어할 수 있다는 것이다. 예를 들어 갱신 처리를 반복계에서, 특정 반복 횟수마다 커밋한다고 가정해보자. 만약 중간에 오류가 발생했다고 해도, 중간에 커밋을 했으므로 해당 지점 근처에서 다시 처리를 실행하면 된다. 또한 특정 이유로 배치를 잠시 중단해야 할 때도 해당 지점 근천에서 다시 처리를 실행할 수 있다. 이러한 미세한 제어는 포장계의 SQL구문에서는 할 수 없는 것이다. 포장계에서는 갱신 처리 중간에 오류가 발생하면, 처리를 처음부터 다시 실행해야 한다.

SQL에서는 반복을 어떻게 표현할까?

1. 포인트는 CASE식과 윈도우 함수

SQL에서 반복을 대신하는 수단은 바로 CASE식과 윈도우 함수이다. 정확하게 CASE식은 절차 지향형 언어에서 말하는 IF-THEN-ELSE구문에 대응하는 기능이다.

SQL에서도 CASE식과 윈도우 함수를 함께 사용하는 세트라고 기억하면된다. 반복계의 코드를 포장계의 SQL로 작성하면 다음과 같다.

INSERT INTO sales2
	SELECT company, year, sale, CASE SIGN(sale - MAX(sale)
																					OVER(PARTITION BY company ORDER BY year
																									ROWS BETWEEN 1 PRECEDING
																												AND 1 PRECEDING))
	WHEN 0 THEN '='
	WHEN 1 THEN '+'
	WHEN -1 THEN '-'
	ELSE NULL END AS var
FROM sales

이 코드의 포인트는 바로 SIGN함수이다. SIGN함수는 숫자 자료형을 매개변수로 받아 음수라면 -1, 양수라면 1, 0이라면 0을 리턴하는 함수이다. 여기서는 직전 연도와의 판매 변화를 알고자 사용했다. CASE식의 조건 부분에 윈도우 함수를 몇 번씩 사용하지 않도록 해주는 기술이기도 하다.

그럼 이제 INSERT구문을 제외한 SELECT구문의 실행 계획을 살펴보자

https://yongineer.duckdns.org/sql/097.png

일단 sales 테이블을 풀스캔하고 (WHERE구를 사용한 조건 지정이 없으므로 당연함), 윈도우 함수를 정렬로 실행하는 것을 확인 할 수 있다. (Extra : Using filesort) 현재 SELECT 구문은 결합을 사용하지 않는다. 따라서 테이블의 레코드 수가 증가해도 실행 계획에 별다른 영향을 주지 않으므로 안정적이라 말할 수 있다.

위 코드에서 중요한 포인트는 윈도우 함수에 ROWS BETWEEN옵션을 사용한 것이다. 이는 대상 범위의 레코드를 직전의 1개로 제한하는 것이다. ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING은 ‘현재 레코드에서 1개 이전 부터 1개 이전까지의 레코드 범위’를 나타낸다. 따라서 직전의 1개로 레코드를 제한하게 된다.

https://yongineer.duckdns.org/sql/098.png

따라서 현재 윈도우 함수는 ‘같은 회사의 직전 매상’을 리턴하고 다음과 같이 결과를 출력한다.

SELECT company, year, sale, 
				MAX(company) 
						OVER (PARTITION BY company ORDER BY year
										ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS pre_company,
				MAX(sale)
						OVER (PARTITION BY company ORDER BY year
										ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS pre_sale,
FROM sales;

https://yongineer.duckdns.org/sql/099.png

이때 만약 비교 대상 레코드를 ‘1개 전 레코드’가 아닌 ‘2개 전 레코드’로 하고 싶다면 ROWS BETWEEN 2 PRECEDING AND 2 PRECEDING 으로 범위를 변경한다. 이러한 유연함은 위도우 함수가 보급되기 이전에 사용하던 상관 서브쿼리로는 하기 힘든 것이다.

💡 상관 서브뭐리를 사용한 대상 레코드 제한

상관 서브쿼리는 서브쿼리 내부에서 외부 쿼리와의 결합 조건을 사용하고, 해당 결합 키로 잘라진 부분 집합을 조작하는 기술이다. 이렇나 점에서 윈도우 함수의 PARTITION BY구와 ORDER BY구와 같은 기능을 갖는다. 앞서 작성한 코드와 같은 결합을 검색하는 코드를 상관 서브쿼리로 작성하면 다음과 같이 된다.

SELECT company, year, sale,
				(SELECT company FROM sales S2 
						WHERE S1.company = S2.company AND 
								year = (SELECT MAX(yaer) FROM sales S3
													WHERE S1.company = S3.company # 상관 서브 쿼리의 결합 조건
														AND S1.year > S3.year)) AS pre_company
				(SELECT sale FROM sales S2 
						WHERE S1.company = S2.company AND 
								year = (SELECT MAX(yaer) FROM sales S3
													WHERE S1.company = S3.company # 상관 서브 쿼리의 결합 조건
														AND S1.year > S3.year)) AS pre_sale
FROM sales S1

상관 서브쿼리를 사용한 코드에서는 ‘직전’ 또는 ‘직후’의 데이터를 구할 때 MAX/MIN함수를 사용한다. 따라서 두 번째, 세 번째의 데이터를 구하는 것은 조금 어렵다. 또한 실행 계획이 굉장히 복잡해지므로 성능적인 리스크도 발생한다.

2. 최대 반복 횟수가 정해진 경우

인접한 우편 번호 찾기

일본에서는 413-0033처럼 하이픈(-)으로 구분된 7자리 숫자를 우편번호로 사용한다. 앞의 세 자리는 지역을 나타내고, 오른쪽 네 자리는 해당 지역을 조금 더 자세하게 나누어 일련 번호를 붙인 것이다. 유일성 관점에서 보면, 하이픈을 제외한 4130033와 같은 일곱 자리 숫자가 유일하다. 이때 하위 자릿수까지 일치할수록 가까운 지역임을 나타낸다. 예를 들어 4130033은 시즈오카현 아타미시 아타미를 나태내고 4130002는 시즈오카현 아타미시 이즈산을 나타내므로 인접한 지역이라는 것을 알 수 있다.

이러한 우편번호의 성질을 사용해서 간단한 문제를 풀어보자. 일단 우편번호를 관리하는 테이블을 만든다. 이 테이블에 저장하는 우편번호는 집합에서 입력받은 우편번호와 가장 가까운 우편번호를 검색해본다 가정할때 가까운 지역일 수록 하위 자리 숫자까지 일치할 것이다. 7자리가 모두 일치한다면 그것이 답이지만, 정확하기 일치하는 숫자가 없을 때는 왼쪽부터의 자릿수가 많이 일치하는 우편번호를 답으로 한다.

먼저 다음과 같은 테이블이 존재한다고 가정해보자.

https://yongineer.duckdns.org/sql/100.png

이 테이블에서 만약 우편번호 4130033을 입력시 인접한 우편번호를 출력한다면 다음과 같을 것이다.

https://yongineer.duckdns.org/sql/101.png

이 문제의 해결하는데 있어 기본적인 방법은 다음과 같다. 일단 우편번호 4130033이 테이블에 있는지를 찾는다 만약 없다면 이어서 413003* (여기서 *은 임의의 숫자)가 있는지를 찾는다. 마찬가지로 없다면 이어서 41300**가 있는지 찾는다. 만약 일치하는 결과가 있다면 이것을 출력하면 된다.

하지만 이런 방법은 테이블의 레코드 수가 많아질수록 성능 측면에서 점점 악화되는 결과를 가져온다.

결국, 순위 붙이기 문제

이 문제의 포인트는 순위이다. 가장 가까운 우편번호 순위를 0, 가장 먼 우편번호 순위를 6으로 나타낸다면 다음과 같을 것이다.

https://yongineer.duckdns.org/sql/102.png

이러한 rank_pcode필드를 만드려면 CASE식을 사용합니다.

SELECT pcode, district_name,
	CASE WHEN pcode = '4130033' THEN 0
			 WHEN pcode LIKE '413003%' THEN 1
			 WHEN pcode LIKE '41300%' THEN 2
			 WHEN pcode LIKE '4130%' THEN 3
			 WHEN pcode LIKE '413%' THEN 4
			 WHEN pcode LIKE '41%' THEN 5
			 WHEN pcode LIKE '4%' THEN 6
			 ELSE NULL END AS rank_pcode
FROM PostalCode ORDER BY rank_pcode

CASE식의 WHEN구는 차례대로 조건을 검사하다가. 조건이 맞으면 이후의 WHEN구를 평가하지 않는다. 따라서 순위를 계산할 수 있는 것이다. 그렇면 이 문제는 다음과 같이 바꿀 수 있을것이다.

  • 순위가 가장 높은 우편번호를 선택

순위가 가장 높다는 것은 rank_pcode필드의 값이 가장 작다는 의미이므로 MIN함수를 사용하면 구할 수 있다.

SELECT pcode, district_name FROM PostalCode
	WHERE CASE WHEN pcode = '4130044' THEN 0
						 WHEN pcode LIKE '413003%' THEN 1
						 WHEN pcode LIKE '41300%' THEN 2
						 WHEN pcode LIKE '4130%' THEN 3
						 WHEN pcode LIKE '413%' THEN 4
						 WHEN pcode LIKE '41%' THEN 5
						 WHEN pcode LIKE '4%' THEN 6
						 ELSE NULL END =
									(SELECT MIN(CASE WHEN pcode = '4130033' THEN 0 
																	 WHEN pcode LIKE '413003%' THEN 1
																	 WHEN pcode LIKE '41300%' THEN 2
																	 WHEN pcode LIKE '4130%' THEN 3
																	 WHEN pcode LIKE '413%' THEN 4
																	 WHEN pcode LIKE '41%' THEN 5
																	 WHEN pcode LIKE '4%' THEN 6
																	 ELSE NULL END)
										FROM PostalCode);

https://yongineer.duckdns.org/sql/103.png

이러한 방법의 포인트는 7회 반복을 CASE식의 분기로 변환했다는 것이다. 실제 애플리케이션에서는 우편번호를 매개변수로 사용해 SQL 구문을 동적으로 생성할 것이다.

하지만 이 쿼리는 성능적인 관점에서 가장 좋은 답이라 하기에는 아직 이르다. 실행 계획을 살펴보면 테이블에 접근이 2회 발생하는 것을 알 수 있다.

https://yongineer.duckdns.org/sql/104.png

만약 테이블의 수가 수백 만에서 수천 만으로 늘어나면 시간이 꽤 걸릴것이다. 따라서 이러한 스캔 횟수를 줄일 수 있는 방법을 연구해야 한다.

윈도우 함수를 사용한 스캔 횟수 감소

그런데 왜 테이블 스캔이 2회 발생하는지 생각해봐야 한다. 바로 순위의 최솟값을 서브쿼리에서 찾기 때문이다. 고전적인 방법이지만 다음과 같이 윈도우 함수를 사용하면 스캔 횟수를 줄일 수 있다.

SELECT pcode, district_name 
	FROM (SELECT pcode, district_name,
										CASE WHEN pcode = '4130044' THEN 0
												 WHEN pcode LIKE '413003%' THEN 1
												 WHEN pcode LIKE '41300%' THEN 2
												 WHEN pcode LIKE '4130%' THEN 3
												 WHEN pcode LIKE '413%' THEN 4
												 WHEN pcode LIKE '41%' THEN 5
												 WHEN pcode LIKE '4%' THEN 6
												 ELSE NULL END AS hit_code,
										MIN(CASE WHEN pcode = '4130033' THEN 0 
														 WHEN pcode LIKE '413003%' THEN 1
														 WHEN pcode LIKE '41300%' THEN 2
														 WHEN pcode LIKE '4130%' THEN 3
														 WHEN pcode LIKE '413%' THEN 4
														 WHEN pcode LIKE '41%' THEN 5
														 WHEN pcode LIKE '4%' THEN 6
														 ELSE NULL END)
										OVER(ORDER BY CASE WHEN pcode = '4130033' THEN 0 
																			 WHEN pcode LIKE '413003%' THEN 1
																			 WHEN pcode LIKE '41300%' THEN 2
																			 WHEN pcode LIKE '4130%' THEN 3
																			 WHEN pcode LIKE '413%' THEN 4
																			 WHEN pcode LIKE '41%' THEN 5
																			 WHEN pcode LIKE '4%' THEN 6
																			 ELSE NULL END) AS min_code
				FROM PostalCode)Foo
WHERE hit_code = min_code;

https://yongineer.duckdns.org/sql/105.png

실행계획을 보면 테이블 접근이 1회로 감소한 것을 확인 할 수 있다. 그런데 윈도우 함수를 사용함으로 인해 정렬 (Using filesort)가 추가로 사용되었다. 따라서 여기에 비용이 추가되는데 테이블 크기가 크다면 테이블 풀 스캔을 줄이는 것의 효과가 더 크다.

💡select_type의 DERIVED

MySQL의 실행계획에서 DERIVED란 다음과 같다.

  • FROM절에 사용된 서브 쿼리로 부터 발생한 임시테이블
  • 이 임시테이블은 메모리에 저장될 수 도 있고 디스크에 저장될 수도 있다.
  • 일반적으로 메모리에 저장하는 경우 성능저하가 없지만 데이터의 크기가 커서 임시 테이블을 디스크에 저장하면 성능이 떨어지게 된다.

3. 반복 횟수가 정해지지 않은 경우

인접 리스트 모델과 재귀 쿼리

다음과 같이 현재 주소(pcode)뿐만 아니라 과거에 살던 주소(new_pcode)까지 관리하는 테이블이 있다고 가정해 보자

https://yongineer.duckdns.org/sql/106.png

이 테이블에 현재 주소를 등록할 때는 다음과 같이 현재 주소의 우편번호만 등록하고 ‘이사하는 곳의 우편번호’는 NULL로 한다.

('A', '4130001', NULL)

이후 A가 이사를 하는 시점에 다음과 같이 ‘이사하는 곳의 우편번호’를 변경 한다.

('A', '4130001', NULL) -> ('A', '4130001', '4130002')

이번에는 다시, 이사한 곳의 주소를 다음과 같이 새로운 레코드로 등록한다.

('A', '4130002', NULL)

이후 A가 이사를 반복할 때마다 이러한 처리를 반복한다. 이렇게 이력을 저장하면 A가 다음과 같이 이사를 두번 했다는 것을 알 수 있다.

4130001 -> 4130002 -> 4130103(현재 주소)

실제 우체국에서도 오랜된 주소로 보내진 우편물을 새로운 주소로 전달하기 위해 이런 방식으로 이력을 관리한다. 이처럼 우편번호를 키로 삼아 데이터를 줄줄이 연결하는 것을 포인터 체인이라고 부른다. 계층 구조를 표현하는 고전적인 방법이다. 포인터 체인을 사용하는 PostalHistory 같은 테이블 형식을 인접 리스트 모델이라고 부른다.

만약 A가 가장 오래전에 살았던 주소를 검색한다면 답은 4130001일것이다. 이것을 찾으려면 현재 주소에서 출발해서 차근차근 이전 주소를 찾아야 할 것이다. 문제는 몇 번을 따라 올라가야만 가장 오래도니 주소를 찾을 수 있을 것인지 사전에는 알 수 없다는 점이다.

이사를 한 번 정도밖에 하지 않은 사람도 있겠지만, 이사를 100번 넘게 하는 이사 매니아가 있을 수도 있다. 이사 횟수의 상한이 정해져 있다면 그만큼 자기 결합을 반복할 수 있겠지만, 상한이 정해지지 않았다면 그런 방법을 사용할 수 없다.

이때 절차 지향형 언어에서 반복문을 사용한다면 문제를 쉽게 풀 수 있다. 파일을 name으로 정렬하고, 현재 주소의 레코드부터 출발해서 이전 주소가 없을 때 까지 처리를 반복하면 가장 오래된 주소를 찾을 수 있다.

다음은 SQL에서 계층 구조를 찾는 방법 중 하나는 재귀 공통 테이블 식(recursion common table expression)을 사용하는 방법이다.

WITH RECURSIVE Explosion (name, pcode, new_pcode, depth)
AS
(SELECT name, pcode, new_pcode, 1 FROM PostalHistory 
	WHERE name = 'A' AND new_pcode IS NULL /* 검색 시작 */
UNION
SELECT Child.name, Child.pcode, Child.new_pcode, depth + 1
	FROM Explosion AS Parent, PostalHistory AS Child
		WHERE Parent.pcode = Child.new_pcode AND Parent.name = Child.name)
/* 메인 SELECT 구문 */
SELECT name, pcode, new_pcode FROM Explosion
	WHERE depth = (SELECT MAX(depth) FROM Explosion);

https://yongineer.duckdns.org/sql/107.png

재귀 공통 테이블 식 Explosion은, A에 대한 현재 주소(new_pcode필드가 NULL)부터 출발해서 포인터 체인을 타고 올라가 과거의 주소를 모두 찾는다. 이때 가장 오래된 주소는 재귀 수준이 가장 깊은 레코드이므로, 이를 depth필드로 찾는다. depth필드는 한 번 반복할 때마다 1씩 증가하므로, depth필드가 가장 큰 것이 가장 재귀 수준이 깊다는 것을 의미한다.

그럼 실행 계획은 어떻게 될까?

https://yongineer.duckdns.org/sql/108.png

id3의 Parent테이블 영역의 Extra를 보면 Recorsive; Using where이라고 나와있는데 이것이 재귀 연산을 의미한다. 이 쿼리는 몇 번을 이사해도 대응할 수 있다는 점에서 굉장히 유연하다. 또한 마지막 실행 계획을 보면 Using temporary이라는 설명이 보이는데 이틑 Explosion뷰에 여러 번 접근하므로 임시 테이블로 만들었다는 것을 나타낸다. 이렇게 만들어진 임시 테이블과 원래 PostalHistory테이블은, 인덱스 idx_new_pcode를 사용해 Nested Loops를 수행하므로 꽤 효율적인 계획인다.

다만, 재귀 공통 테이블은 비교적 최근에 만들어진 기능이므로 아직 구현되지 않았거나, 실행 계획이 최적화되지 않은 DBMS가 있다. 이러한 경우 사용할 수 있는 대체 수단을 살펴보자

중첩 집합 모델

SQL에서 계층 구조를 나타내는 방법은 크게 3가지가 있다.

  1. 인접 리스트 모델
  2. 중첩 집합 모델
  3. 경로 열거 모델

1️⃣ 은 앞에서 살펴본 방법으로, RDB가 탄생하기 이전부터 계층 구조를 표현하는 전통적인 방법으로 사용되었다. 3️⃣ 은 갱신이 거의 발생하지 않은 경우에 힘을 발휘한다. 중요한 것은 2️⃣ 의 중첩 집합 모델이다. 이 방법의 포인트는 각 레코드의 데이터를 집합(원)으로 보고, 계층 구조를 집합의 중첩 관계로 나타낸다는 것이다.

https://yongineer.duckdns.org/sql/109.png

먼저 위와 같은 테이블을 만들어 준다. 이 테이블은 우편 번호의 데이터를 수치선 상에 존재하는 원으로 생각한다. lft와 rgt는 원의 왼쪽 끝과 오른쪽 끝에 위치하는 좌표를 나타낸다. 좌표값은 대소 관계만 적절하다면 임의의 값을 사용할 수 있다. 그리고 이사 할 때마다 새로운 우편번호가 이전의 우편번호 ‘안에’ 포함되는 현태로 추가된다. 이렇게 하면 A의 우편번호 3개의 포함 관계가 다음과 같이 나타날 것이다.

https://yongineer.duckdns.org/sql/110.png

이때 새로 삽입하는 우편번호의 좌표는 외측 원의 왼쪽 끝과 오른쪽 끝의 좌표를 사용해 결정된다. 예를 들어 외측의 우편번호의 왼쪽 끝 좌표를 plft, 오른쪽 끝 좌표를 prgt라고 한다면, 다음과 같은 수식에 따라 자동적으로 노드의 좌표를 연산한다.

  • 추가되는 노드의 왼쪽 끝 좌표 = (plft∗2+prgt)/3(plft * 2 + prgt) / 3
  • 추가되는 노드의 오른쪽 끝 좌표 = (plft+prgt∗2)/3(plft + prgt * 2) / 3

따라서 plft와 prgt로 3개의 구간을 분할할 수 있는 2개의 좌표를 찾는 것이다. lft와 rgt의 자료형은 실수형(REAL)인데 DBMS의 정밀 번위 내에서는 낮은 비용으로도 중첩을 얼마든지 할 수 있다.

이러한 중첩 모델의 테이블에서는 A의 가장 오래된 주소를 굉장히 간단한 SQL 구문으로 찾을 수 있다. 가장 바깥 쪽에 있는 원을 찾기만 하면 된다. 따라서 NOT EXISTS를 사용하면 쉽게 구할 수 있다. ‘왼쪽 끝의 좌표가 다른 모든 원의 왼쪽 끝 위치보다 작은’이라는 조건을 사용했다.

SELECT name, pcode FROM PostalHistory2 PH1
	WHERE name = 'A' 
		AND NOT EXISTS (
					SELECT * FROM PostalHistory2 PH2
						WHERE PH2.name = 'A' AND PH1.lft > PH2.lft);

https://yongineer.duckdns.org/sql/111.png

이제 실행 계획을 살펴보자.

https://yongineer.duckdns.org/sql/112.png

외측 테이블 (PH1)과 내측 테이블(PH2)을 한 번만 Nested Loops로 결합하는 실행 계획이다. 여기서 재귀 연산을 사용하지 않았다는 것에 주목해야 한다. PostgreSQL에서는 테이블의 풀 스캔이 수행 되는데 이는 테이블의 레코드 수가 적기 때문이다 반면 MySQL에서는 인덱스를 활용하는 계획을 세우는 것을 알 수 있다. 이러한 중첩 집합의 코드가 재귀보다 빠를지 단순하게 판단할 수는 없지만, 일반적인 코딩에서는 없는 모델(엔티티 구조)의 관점으로 문제를 해결할 수도 있다는 것을 일단 알고 있으면 된다.

정리

  • 우리는 모두 반복문 의존증에 걸려 있다.
  • SQL은 의도적으로 반복문을 설계에서 제외했음
  • 반복계는 성능적으로 큰 결점을 가지고 있지만, 몇가지 장점도 있음
  • 하지만 반복계는 성능 튜닝 가능성이 거의 없으므로 사용시 주의가 필요
  • 여기에서도 트레이드오프를 고려해서, 반복계와 포장계 중에 어떤 것을 채용할지 판단할 필요가 있다.
Written by@Yongineer
Backend Developer

GitHubInstagram