데이터베이스 튜닝 규칙
데이터베이스가 가능한 빨리 쿼리를 처리하는 동시에 현실적으로 가능한 많은 동시 사용자를 지원해야 한다. 이는 공간과 자원 관리를 최적화하는 동시에 잠금, 입출력, 네트워크 트래픽을 최소화해야 한다는 것을 의미한다.
가능하면 커서(Cursor)를 피하자.
커서는 일련의 데이터에 순차적으로 액세스할 때 검색 및 현재 위치를 포함하는 데이터 요소를 말한다. 커서를 피하는 것은 아주 쉬운 결정이다. 커서는 속도 문제를 겪을 뿐 아니라, 다른 작업을 필요 이상 지연시킬 정도로 하나의 작업을 블록(Block) 시킬 수도 있다. 이는 시스템 동시성을 크게 저하시킨다.
커서를 피할 수 없다면, 임시 테이블을 사용하자.
커서를 사용해야만 할 때가 있다. 그런 경우, 라이브 테이블보다는 임시 테이블에 대한 커서 작업을 수행하는 것이 더 낫다. 훨씬 더 작은 라이브 테이블에 대한 하나의 UPDATE 문이 있을 수 있다. 짧은 시간 동안에만 Lock을 유지하게 되어 동시성을 크게 증진시켜 준다.
임시 테이블을 현명하게 사용하자.
다른 여러 가지 상황에서도 임시 테이블을 사용할 수 있다. 예를 들어, 어떤 테이블을 더 큰 테이블에 조인 시켜야만 한다면, 더 큰 테이블에서 필요한 일부 데이터만 임시 테이블로 끌어와서 조인시킴으로써 성능을 개선할 수 있다. 이는 필요한 처리 능력을 크게 줄여주며, 프로시저에 같은 테이블에 대해 유사한 조인을 해야만 하는 여러 개의 쿼리가 있는 경우 유용하다.
데이터를 미리 준비하자.
흔히 간과되는 예전 기법이다. 커다란 테이블에 대해 비슷한 조인 작업을 할 보고서(Report)나 프로시저가 있다면, 미리 테이블을 조인시키고 테이블들을 하나의 테이블에 영속화시킴으로써 데이터를 사전 준비하자. 그렇게 하면, 사전 준비된 해당 테이블에 대한 보고서 작업을 실행할 수 있어서, 대규모 조인 작업을 피할 수 있다.
항상 이 기법을 사용할 수는 없지만, 대부분의 환경에는 늘 조인되는 인기 테이블이 있다. 서버 자원을 절약하기 위한 좋은 방법이다.
복합 뷰(Nested View)를 최소화하자.
뷰는 엄청난 쿼리를 사용자들로부터 가리는데 훌륭하지만, 하나의 뷰 안에 또 다른 뷰와 내부에 있는 다른 뷰를 중첩시키다 보면 심각한 성능 저하를 유발할 수 있다. 너무 많은 수의 복합 뷰는 모든 쿼리에 대해 엄청난 양의 데이터가 반환 되는 결과를 초래한다. 혹은, 쿼리 최적화기(Optimizer)가 포기해서 아무것도 반환되지 않을 수도 있다.
복합 뷰를 풀어내는 것으로 쿼리 응답 시간을 획기적으로 줄일 수 있다.
UPDATE 문 대신 CASE 문을 사용하자.
임시 테이블에 데이터를 삽입하고 있으며 다른 값이 존재할 경우 해당 데이터가 특정 값을 표시하도록 해야 한다. 예를 들어, Customer 테이블에서 데이터를 끌어오고 있으며 주문 액수가 일정 금액 이상인 고객에 대해서 “VIP”라는 라벨을 붙여야 한다. 그래서 일정 금액 이상의 주문 금액을 보유하고 있는 모든 고객에 대해서 CustomerRank 컬럼에 “VIP”라고 설정하기 위해 테이블에 데이터를 삽입하고 UPDATE문을 실행한다.
문제는 UPDATE문이 로그된다는 것이다. 즉, 테이블에 대한 모든 한 번의 쓰기 작업 당 두 번의 쓰기 작업이 일어난다. CASE 문을 사용한다면 모든 행에 대해 주문량 조건을 확인하고 테이블에 쓰기 전에 “VIP” 라벨을 설정한다. 성능 증가는 엄청나다.
SQL 서버에서 분할(Partition)을 활용하자.
SQL 서버 엔터프라이즈 사용자들은 성능을 가속화하기 위해 데이터 엔진의 자동 분할 기능을 활용할 수 있다. SQL 서버에서는 간단한 테이블조차도 하나의 분할로 생성되며, 사용자는 나중에 필요에 따라 여러 개의 분할로 쪼갤 수 있다. 테이블 간에 많은 양의 데이터를 옮겨야 할 경우, INSERT와 DELETE 문 대신에 SWITCH 명령을 사용할 수 있다. 테이블 간에 많은 양의 데이터를 삭제하고 삽입하는 대신, 단일 테이블에 대한 메타데이터만 변경하는 것이기 때문에 실행하는데 몇 초 밖에 걸리지 않는다.
배치 모드로 DELETE와 UPDATE 작업을 수행하자.
거대한 테이블에서 많은 양의 데이터를 DELETE, UPDATE하는 작업에서 문제가 있다. 이 두 가지 명령문 모두 하나의 트랜잭션으로 실행되는 것이며, 프로세스를 중지시켜야 한다거나 작업 도중에 어떤 문제가 발생한다면, 시스템은 전체 트랜잭션을 ROLL_BACK 시켜야만 한다. 이 작업은 진행 중인 다른 트랜잭션들을 블록 시킬 뿐 아니라, 많은 시간이 걸릴 수 있어서, 기본적으로 시스템 병목을 일으킨다.
해결책은 작은 배치 단위로 DELETE나 UPDATE 작업을 하는 것이다. 트랜잭션이 중지돼도 소수의 행만 복원하면 되므로, 데이터베이스는 훨씬 빨리 온라인으로 돌아온다. 그리고 더 작은 배치작업들이 디스크에 커밋하는 동안 다른 작업들이 끼어들어서 어느 정도의 작업을 할 수 있어서 동시성이 크게 개선된다.
ORM을 피하자.
ORM은 개발자가 직면할 가능성이 있는 대부분의 성능 문제에 책임이 있다. ORM을 피할 수 없다면, 스스로 자체적인 저장 프로시저를 작성하고 ORM이 자체 쿼리를 작성하는 대신 사용자가 작성한 쿼리를 호출하게 함으로써 부정적인 측면을 최소화할 수 있다.
가능한 경우, 저장 프로시저(Stored Procedure)를 사용하자.
저장 프로시저는 호출이 더 짧을 것이기 때문에, 트래픽을 크게 줄여준다. 프로파일러(Profiler) 같은 도구를 사용해서 추적하기가 더 쉬워서 사용자가 성능 통계치를 확보하고 잠재적인 문제를 더 빨리 규명할 수 있게 해준다. 더욱 일관성 있는 방식으로 정의할 수 있으며, 이는 실행 계획(Execution Plan)을 재사용할 가능성이 더 높으며, 임의 쿼리에 비해 엣지 케이스(Edge Case)와 감사용으로 사용하기가 더 쉽다.
Edge Case : 극단적인 작동 매개변수에서만 발생하는 문제 또는 상황.
중복 처리(Double Dipping)을 피하자.
저장 프로시저 사용은 때로 중복 처리로 이어질 수 있다. 대규모 테이블에 대해 별개의 쿼리를 여러 개 실행하고, 그것들을 임시 테이블에 넣은 다음에, 테이블들을 다시 조인하는 것이다. 이는 성능에 커다란 방해물이 될 수 있다. 가능한한 대규모 테이블을 한 번만 쿼리하는 것이 낫다.
한 프로세스의 몇 가지 단계에서 커다란 테이블의 일부가 필요한 경우라면, 매 번 커다란 테이블에 대한 쿼리를 유발한다. 이 때 일부에 대한 쿼리를 실행하고 그것을 다른 곳에 영속화 시킨 다음에, 후속 단계를 영속화된 작은 데이터 셋으로 유도하자.
커다란 트랜잭션은 작은 트랜잭션 여러 개로 쪼개자.
단일 트랜잭션에서 여러 개의 테이블을 처리하는 작업은 해당 트랜잭션이 끝날 때까지 모든 테이블을 잠글 수 있기 때문에, 다수의 블로킹으로 이어진다. 해결책은 이 트랜잭션을 각각의 개별적으로 단일 테이블에 대한 작업을 하는 여러 루틴으로 쪼개는 것이다. 이는 블로킹 횟수를 줄여주고 다른 작업들이 계속해서 이루어질 수 있도록 다른 테이블들을 풀어준다.
트리거(Trigger) 사용을 피하자.
트리거를 사용하면 원래 작업의 동일한 트랜잭션에서 수행될 것이기 때문에 트리거가 완료될 때까지 여러 개의 테이블을 잠그는 결과를 초래할 수 있다. 이런 트리거를 별개의 트랜잭션들로 쪼개면 더 적은 수의 자원을 잠그게 돼서 필요한 경우 변경사항 복원을 쉽게 만들어준다.
GUID에 대한 클러스터링을 피하자.
테이블 데이터 정렬을 위해 GUID(Globally Unique Identifier : 전역 고유 식별자), GUID(Universally Unique Identifier : 범용 고유 식별자)를 사용하지 말자. 임의로 생성되는 이런 16비트 숫자는 사용자의 테이블을 훨씬 더 빨리 단편화(Fragmentation)한다. DATE나 IDENTITY 같은 값을 점진적으로 증가시켜서 데이터를 정렬하는 것이 훨씬 낫다. 휘발성 있는 모든 컬럼에 대해서도 같은 규칙이 적용된다.
데이터베이스에서의 단편화란, 데이터 파일이 저장 매체 상에서 하나의 연속된 위치에 저장되는 것이 아니라, 여러 조각으로 나뉘어 저장되는 것을 말한다.
테이블에 있는 모든 것을 카운트하지 말자.
PostgreSQL
-- 수정 전
SELECT CASE WHEN (SELECT COUNT(*) FROM TB_MEMBER) > 0
THEN 'Y'
ELSE 'N'
END
-- 수정 후
SELECT CASE WHEN EXISTS(SELECT 1 FROM TB_MEMBER)
THEN 'Y'
ELSE 'N'
END
테이블이 크면 클수록, 더 많은 차이를 낸다.
행을 카운트하려면 시스템 테이블을 사용하자.
커다란 테이블의 행을 카운트할 필요가 있다면, 시스템 테이블에서 끌어올 수 있다.
PostgreSQL
SELECT *
FROM pg_stat_all_tables
WHERE relname = 'tb_member'
필요한 컬럼만 조회하자.
불필요한 데이터를 조회하면, 개발자가 필요한 것보다 훨씬 더 많은 데이터를 처리할 뿐 아니라, 다른 프로세스들로부터 자원을 뺏어가기도 한다.
가능한 HAVING절 사용을 피하자.
HAVING절은 GROUP BY에 의한 결과를 제한할 때 사용한다. GROUP BY에 HAVING 절을 사용하였을 경우 GROUP BY에 의해서 결과들을 모두 집계한 다음 HAVING절에 명시한 조건으로 맞지 않는 결과를 버리게 된다. 대부분의 경우 HAVING절 없이 GROUP BY와 WHERE절 만으로 원하는 결과를 얻을 수 있다.
가능한 DISTINCT문 사용을 피하자.
SORT에 따른 성능 저하가 있기 때문에 꼭 필요한 경우에만 사용하자.
Index를 타는지 항상 체크하자.
Index를 활용하지 않은 검색은 데이터가 많으면 많을수록 성능은 급격히 떨어진다. WHERE 조건절에는 Clustered Index Seek를 타기 위한 컬럼이 우선 존재해야 하고 그 후에 원하는 데이터를 얻기 위한 조건절이 존재해야 한다.
Clustered Index Seek를 항상 체크하자.
Clustered index scan을 타는 것만으로도 속도는 향상 되지만 완전하지 않다. clustered index column의 일정 구간을 타는 seek여야 대량으로 증가하는 data에 대한 부하를 감당할 수 있다. 이를 위해 index의 구간 체크를 해야 한다. 만약 검색하는 column이 clustered index column인 경우는 단방향 WHERE 조건문으로도 index scan이 성립된다. 자신의 column에서 그대로 찾아서 시작지점부터 끝까지 index를 타면 되기 때문이다. 하지만 일반 non-clustered index의 경우는 clustered를 찾기 위해 해당 column의 clustered index 정보를 호출해야하는 부담이 생긴다. 왜냐하면 결국 호출을 하기 위해서는 해당 데이터의 위치를 찾아야하고 이 위치를 가장 밀도있게 알고 있는 clustered index에서 해당 데이터의 위치를 찾아 가져오기 때문이다. 결국 구간 체크가 아닌 non-clustered index의 단방향 WHERE 조건문은 clustered의 전체 스캔을 하게 되는 결과를 가져온다.
Reference : https://www.itworld.co.kr/tags/2665/SQL/105792
Comments