2017.07.28

더 빠른 SQL 쿼리를 위한 21가지 데이터베이스 튜닝 규칙

Sean McCown | InfoWorld

누구나 더 빠른 데이터베이스 쿼리를 원하며, SQL 개발자와 DBA 모두 이런 목표를 달성하기 위해 오랜 사용으로 효과가 입증된 여러 가지 방법에 의지한다. 하지만 안타깝게도 그 어떤 방법이라도 하나만으로는 완벽하거나 확실하지 않다. 이처럼 모든 쿼리를 미세조정, 즉 튜닝하기 위한 정답이 존재하지는 않지만, 길을 밝히는데 도움이 되는 풍부한 관례가 있다. 몇 가지는 RDMBS에 관련된 사항들이지만, 대부분의 팁은 모든 관계형 데이터베이스에 적용된다.

SQL 서버, 오라클, DB2, 사이베이스, MySQL, 아니면 다른 관계형 데이터베이스 플랫폼 중 어디에서 코딩을 하고 있더라도, 목표는 같다. 데이터베이스가 가능한 빨리 쿼리를 처리하는 동시에 현실적으로 가능한 많은 동시 사용자를 지원하는 것이다. 이는 공간과 자원 관리를 최적화하는 동시에 잠금(Locking), 입출력, 그리고 네트워크 트래픽은 최소화해야 한다는 것을 의미한다.

데이터베이스 튜닝은 기술인 동시에 과학이다. 다음은 사용 중인 데이터베이스를 더 빠르고 더욱 효과적으로 만들어주는 21가지 입증된 규칙이다.

1. 가능하면 커서(Cursor)를 피하라.
커서는 일련의 데이터에 순차적으로 액세스할 때 검색 및 현재 위치를 포함하는 데이터 요소를 말한다. 커서를 피하는 것은 아주 쉬운 결정이다. 커서는 속도 문제를 겪을 뿐 아니라, 다른 작업을 필요 이상 지연시킬 정도로 하나의 작업을 블록(Block)시킬 수도 있다. 이는 시스템의 동시성을 크게 저하시킨다.

2. 커서를 피할 수 없다면, 임시 테이블(temp table)을 사용하라
커서를 사용해야만 할 때가 있다. 그런 경우, 라이브 테이블(Live Table)보다는 임시 테이블에 대한 커서 작업을 수행하는 것이 더 낫다. 훨씬 더 작은 라이브 테이블에 대한 하나의 UPDATE 문이 있을 수 있다. 짧은 시간 동안에만 잠금(Lock)을 유지하게 되어 동시성을 크게 증진시켜 준다.

3. 임시 테이블을 현명하게 사용하라
다른 여러 가지 상황에서도 임시 테이블을 사용할 수 있다. 예를 들어, 어떤 테이블을 더 큰 테이블에 조인(Join) 시켜야만 한다면, 더 큰 테이블에서 필요한 일부 데이터만 임시 테이블로 끌어(Pull)와서 대신 그것과 조인시킴으로써 성능을 개선할 수 있다. 이는 필요한 처리 능력을 크게 줄여주며, 프로시저에 같은 테이블에 대해 유사한 조인을 해야만 하는 여러 개의 쿼리가 있는 경우 유용하다.

4. 데이터를 미리 준비하라
흔히 간과되는 예전의 기법이다. 커다란 테이블에 대해 비슷한 조인 작업을 할 보고서(Report)나 프로시저가 있다면, 미리 테이블을 조인시키고 테이블들을 하나의 테이블에 영속화(Persisted)시킴으로써 데이터를 사전 준비하라. 그렇게 하면, 사전 준비된 해당 테이블에 대한 보고서 작업을 실행할 수 있어서, 대규모 조인 작업을 피할 수 있다.

항상 이 기법을 사용할 수는 없지만, 대부분의 환경에는 늘 조인되는 인기 테이블이 있기 마련이다. 이런 테이블들을 사전에 준비하지 못할 이유가 전혀 없으며, 서버 자원을 절약하기 위한 훌륭한 방법이다.

5. 복합 뷰(Nested View)를 최소화하라
뷰는 엄청난 쿼리를 사용자들로부터 가리는데 훌륭하지만, 하나의 뷰 안에 또 다른 뷰와 내부에 있는 다른 뷰를 (계속해서) 중첩시키다 보면 심각한 성능 저하를 유발할 수 있다. 너무 많은 수의 복합 뷰는 모든 쿼리에 대해 엄청난 양의 데이터가 반환(Return) 되는 결과를 초래해서, 데이터베이스 성능을 말 그대로 기어 다니게 만들 수 있다. 혹은, 더 나가서, 쿼리 최적화기(Optimizer)가 포기해서 아무것도 반환되지 않을 수도 있다.

복합 뷰를 풀어내는 것으로 쿼리 응답 시간을 몇 분에서 몇 초로 줄일 수 있다.

6. UPDATE 문 대신 CASE 문을 사용하라
다음 시나리오를 살펴보자. 임시 테이블에 데이터를 삽입하고 있으며 다른 값이 존재할 경우 해당 데이터가 특정 값을 표시하도록 해야 한다. Customer 테이블에서 데이터를 끌어오고 있으며 주문 액수가 100,000달러 이상인 고객에 대해서 “우대”라는 라벨을 붙이고 싶어한다고 하자. 그래서, 100,000달러 이상의 주문 금액을 보유하고 있는 모든 고객에 대해서 CustomerRank 열에 “우대”라고 설정하기 위해 테이블에 데이터를 삽입하고 UPDATE 문을 실행한다.

논리적으로 보인다, 그렇지 않은가? 문제는 UPDATE 문이 로그된다는 것이다 즉, 테이블에 대한 모든 한 번의 쓰기 작업 당 두 번의 쓰기 작업이 일어난다는 의미이다. 물론, 이 문제를 피하는 방법은 SQL 쿼리 자체에서 인라인(Inline) CASE 문을 사용하는 것이다. 이는 모든 행에 대해 주문량 조건을 확인하고 테이블에 쓰기 전에 “우대” 라벨을 설정한다. 성능 증가는 깜짝 놀랄 정도이다.

7. 스칼라(Scalar) 대신 테이블 반환 함수(Table-Valued Functions)를 사용하라
전문가들이 사용하는 팁이 있다. 쿼리의 SELECT 목록에서 스칼라 함수를 사용할 경우, 그 대신에 쿼리에서 테이블 반환 함수를 사용하고 CROSS APPLY 문을 사용하면 성능을 개선할 수 있다. 이는 쿼리 시간을 절반으로 대폭 줄여줄 수 있다.


2017.07.28

더 빠른 SQL 쿼리를 위한 21가지 데이터베이스 튜닝 규칙

Sean McCown | InfoWorld

누구나 더 빠른 데이터베이스 쿼리를 원하며, SQL 개발자와 DBA 모두 이런 목표를 달성하기 위해 오랜 사용으로 효과가 입증된 여러 가지 방법에 의지한다. 하지만 안타깝게도 그 어떤 방법이라도 하나만으로는 완벽하거나 확실하지 않다. 이처럼 모든 쿼리를 미세조정, 즉 튜닝하기 위한 정답이 존재하지는 않지만, 길을 밝히는데 도움이 되는 풍부한 관례가 있다. 몇 가지는 RDMBS에 관련된 사항들이지만, 대부분의 팁은 모든 관계형 데이터베이스에 적용된다.

SQL 서버, 오라클, DB2, 사이베이스, MySQL, 아니면 다른 관계형 데이터베이스 플랫폼 중 어디에서 코딩을 하고 있더라도, 목표는 같다. 데이터베이스가 가능한 빨리 쿼리를 처리하는 동시에 현실적으로 가능한 많은 동시 사용자를 지원하는 것이다. 이는 공간과 자원 관리를 최적화하는 동시에 잠금(Locking), 입출력, 그리고 네트워크 트래픽은 최소화해야 한다는 것을 의미한다.

데이터베이스 튜닝은 기술인 동시에 과학이다. 다음은 사용 중인 데이터베이스를 더 빠르고 더욱 효과적으로 만들어주는 21가지 입증된 규칙이다.

1. 가능하면 커서(Cursor)를 피하라.
커서는 일련의 데이터에 순차적으로 액세스할 때 검색 및 현재 위치를 포함하는 데이터 요소를 말한다. 커서를 피하는 것은 아주 쉬운 결정이다. 커서는 속도 문제를 겪을 뿐 아니라, 다른 작업을 필요 이상 지연시킬 정도로 하나의 작업을 블록(Block)시킬 수도 있다. 이는 시스템의 동시성을 크게 저하시킨다.

2. 커서를 피할 수 없다면, 임시 테이블(temp table)을 사용하라
커서를 사용해야만 할 때가 있다. 그런 경우, 라이브 테이블(Live Table)보다는 임시 테이블에 대한 커서 작업을 수행하는 것이 더 낫다. 훨씬 더 작은 라이브 테이블에 대한 하나의 UPDATE 문이 있을 수 있다. 짧은 시간 동안에만 잠금(Lock)을 유지하게 되어 동시성을 크게 증진시켜 준다.

3. 임시 테이블을 현명하게 사용하라
다른 여러 가지 상황에서도 임시 테이블을 사용할 수 있다. 예를 들어, 어떤 테이블을 더 큰 테이블에 조인(Join) 시켜야만 한다면, 더 큰 테이블에서 필요한 일부 데이터만 임시 테이블로 끌어(Pull)와서 대신 그것과 조인시킴으로써 성능을 개선할 수 있다. 이는 필요한 처리 능력을 크게 줄여주며, 프로시저에 같은 테이블에 대해 유사한 조인을 해야만 하는 여러 개의 쿼리가 있는 경우 유용하다.

4. 데이터를 미리 준비하라
흔히 간과되는 예전의 기법이다. 커다란 테이블에 대해 비슷한 조인 작업을 할 보고서(Report)나 프로시저가 있다면, 미리 테이블을 조인시키고 테이블들을 하나의 테이블에 영속화(Persisted)시킴으로써 데이터를 사전 준비하라. 그렇게 하면, 사전 준비된 해당 테이블에 대한 보고서 작업을 실행할 수 있어서, 대규모 조인 작업을 피할 수 있다.

항상 이 기법을 사용할 수는 없지만, 대부분의 환경에는 늘 조인되는 인기 테이블이 있기 마련이다. 이런 테이블들을 사전에 준비하지 못할 이유가 전혀 없으며, 서버 자원을 절약하기 위한 훌륭한 방법이다.

5. 복합 뷰(Nested View)를 최소화하라
뷰는 엄청난 쿼리를 사용자들로부터 가리는데 훌륭하지만, 하나의 뷰 안에 또 다른 뷰와 내부에 있는 다른 뷰를 (계속해서) 중첩시키다 보면 심각한 성능 저하를 유발할 수 있다. 너무 많은 수의 복합 뷰는 모든 쿼리에 대해 엄청난 양의 데이터가 반환(Return) 되는 결과를 초래해서, 데이터베이스 성능을 말 그대로 기어 다니게 만들 수 있다. 혹은, 더 나가서, 쿼리 최적화기(Optimizer)가 포기해서 아무것도 반환되지 않을 수도 있다.

복합 뷰를 풀어내는 것으로 쿼리 응답 시간을 몇 분에서 몇 초로 줄일 수 있다.

6. UPDATE 문 대신 CASE 문을 사용하라
다음 시나리오를 살펴보자. 임시 테이블에 데이터를 삽입하고 있으며 다른 값이 존재할 경우 해당 데이터가 특정 값을 표시하도록 해야 한다. Customer 테이블에서 데이터를 끌어오고 있으며 주문 액수가 100,000달러 이상인 고객에 대해서 “우대”라는 라벨을 붙이고 싶어한다고 하자. 그래서, 100,000달러 이상의 주문 금액을 보유하고 있는 모든 고객에 대해서 CustomerRank 열에 “우대”라고 설정하기 위해 테이블에 데이터를 삽입하고 UPDATE 문을 실행한다.

논리적으로 보인다, 그렇지 않은가? 문제는 UPDATE 문이 로그된다는 것이다 즉, 테이블에 대한 모든 한 번의 쓰기 작업 당 두 번의 쓰기 작업이 일어난다는 의미이다. 물론, 이 문제를 피하는 방법은 SQL 쿼리 자체에서 인라인(Inline) CASE 문을 사용하는 것이다. 이는 모든 행에 대해 주문량 조건을 확인하고 테이블에 쓰기 전에 “우대” 라벨을 설정한다. 성능 증가는 깜짝 놀랄 정도이다.

7. 스칼라(Scalar) 대신 테이블 반환 함수(Table-Valued Functions)를 사용하라
전문가들이 사용하는 팁이 있다. 쿼리의 SELECT 목록에서 스칼라 함수를 사용할 경우, 그 대신에 쿼리에서 테이블 반환 함수를 사용하고 CROSS APPLY 문을 사용하면 성능을 개선할 수 있다. 이는 쿼리 시간을 절반으로 대폭 줄여줄 수 있다.


X