SQL 튜닝
SQL 튜닝은 SQL문을 최적화하여 빠른 시간내에 원하는 결과값을 얻기 위한 작업이다.
SQL 튜닝 접근 방법
- 부하의 감소
보통 일반적으로 접근하는 튜닝 방식으로, 동일한 부하를 보다 효율적인 방법으로 수행해야 한다.
- 부하의 조정
부하 정도에 따라 업무를 조정하는 접근 방법으로, 일반 업무(OLTP)를 분리한다.
- 부하의 병렬 수행
부하가 많이 걸리는 부분에 병렬 서비스를 실행하여 응답 시간을 크게 단축하는 접근 방법이다.
SQL 튜닝 규칙
아래는 ITWORLD에서 발간한 더 빠른 SQL 쿼리를 위한 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 문을 사용하면 성능을 개선할 수 있다. 이는 쿼리 시간을 절반으로 대폭 줄여줄 수 있다.
8. SQL 서버에서 분할(Partition)을 활용하라
SQL 서버 엔터프라이즈 사용자들은 성능을 가속화하기 위해 데이터 엔진의 자동 분할 기능을 활용할 수 있다.
SQL 서버에서는 간단한 테이블조차도 하나의 분할로 생성되며, 사용자는 나중에 그것을 필요에 따라 여러 개의 분할로 쪼갤 수 있다.
테이블 간에 많은 양의 데이터를 옮겨야 할 경우, INSERT와 DELETE 문 대신에 SWITCH 명령을 사용할 수 있다. 테이블 간에 많은 양의 데이터를 삭제하고 삽입하는 대신, 단일 테이블에 대한 메타데이터만 변경하는 것이기 때문에, 실행하는데 몇 초 밖에 걸리지 않는다.
9. 배치 모드로 삭제(Delete)와 갱신(Update) 작업을 하라
거대한 테이블에서 많은 양의 데이터를 삭제하거나 업데이트하는 작업은 악몽일 수 있다.
문제는 이 두 가지 명령문 모두가 하나의 트랜잭션으로 실행되는 것이며, 프로세스를 중지시켜야 한다거나 작업 도중에 어떤 일이 일어난다면, 시스템은 전체 트랜잭션을 복원(Roll Back)시켜야만 한다.
이 작업은 진행 중인 다른 트랜잭션들을 블록 시킬 뿐 아니라, 많은 시간이 걸릴 수 있어서, 기본적으로 시스템 병목을 일으킨다.
해결책은 작은 배치 단위로 삭제나 업데이트 작업을 하는 것이다.
트랜잭션이 중지돼도, 소수의 행만 복원하면 되므로, 데이터베이스는 훨씬 더 빨리 온라인으로 돌아온다. 그리고 더 작은 배치작업들이 디스크에 커밋(Commit)하는 동안, 다른 작업들이 끼어들어서 어느 정도의 작업을 할 수 있어서 동시성이 크게 개선된다.
10. 서두르지 말고 천천히 하라
삭제와 업데이트 작업이 같은 날 완료되어야만 하는것은 아니다. 특히, 아카이빙 작업은 더욱 그렇지 않다.
이 작업은 필요한 만큼 늘일 수 있으며, 이 작업을 완료하는 데는 더 작은 배치작업들이 도움이 된다.
이런 집약적인 작업을 더 천천히 할 수 있다면, 여분의 시간을 시스템이 다운되지 않도록 하는 데 투여하기 바란다.
11. ORM을 피하라
ORM(Object-relational Mapper: 객체 관계형 매퍼)는 지구상에서 최악의 코드를 만들어 내고 있으며, 개발자가 직면할 가능성이 있는 대부분의 성능 문제에 책임이 있다.
그렇지만, ORM을 피할 수 없다면, 스스로 자체적인 저장 프로시저를 작성하고 ORM이 자체 쿼리를 작성하는 대신 사용자가 작성한 쿼리를 호출하게 함으로써 부정적인 측면을 최소화할 수 있다.
12. 가능한 경우, 저장 프로시저(Stored Procedure)를 사용하라
더 훌륭한 코드로 이끄는 것 외에, 저장 프로시저는 다른 많은 장점도 가지고 있다.
저장 프로시저는 호출이 더 짧을 것이기 때문에, 트래픽을 크게 줄여준다.
프로파일러(Profiler) 같은 도구를 사용해서 추적하기가 더 쉬워서 사용자가 성능 통계치를 확보하고 잠재적인 문제를 더 빨리 규명할 수 있게 해준다.
더욱 일관성 있는 방식으로 정의할 수 있으며, 이는 실행 계획(Execution Plan)을 재사용할 가능성이 더 높으며, 임의 쿼리에 비해 엣지 케이스(Edge Case)와 감사용으로 사용하기가 더 쉽다는 의미이다.
13. 더블 디핑(Double-Dipping: 중복 처리)을 피하라
저장 프로시저 사용은 때로 “더블 디핑”으로 이어질 수 있다.
대규모 테이블에 대해 별개의 쿼리를 여러 개 실행하고, 그것들을 임시 테이블에 넣은 다음에, 테이블들을 다시 조인하는 것이다.
이는 성능에 커다란 방해물이 될 수 있다. 가능한한 대규모 테이블을 한 번만 쿼리 하는 것이 훨씬 더 낫다.
조금 다른 시나리오는 한 프로세스의 몇 가지 단계에서 커다란 테이블의 일부가 필요한 경우로, 이는 매 번 커다란 테이블에 대한 쿼리를 유발한다.
일부에 대한 쿼리를 실행하고 그것을 다른 곳에 영속화 시킨 다음에, 후속 단계를 영속화된 더 작은 데이터 세트로 유도하라.
14. 커다란 트랜잭션은 작은 트랜잭션 여러 개로 쪼개라
단일 트랜잭션에서 여러 개의 테이블을 처리하는 작업은 해당 트랜잭션이 끝날 때까지 모든 테이블을 잠글 수 있기 때문에, 다수의 블로킹으로 이어진다.
해결책은 이 트랜잭션을 각각이 개별적으로 단일 테이블에 대한 작업을 하는 여러 개의 루틴(Routines)으로 쪼개는 것이다.
이는 블로킹 횟수를 줄여주고 다른 작업들이 계속해서 이루어질 수 있도록 다른 테이블들을 풀어준다.
15. 트리거(Trigger) 사용을 자제하라
하려고 하는 작업이 무엇이든, 원래 작업의 동일한 트랜잭션에서 수행될 것이기 때문에 트리거 사용도 비슷한 문제로 이어질 수 있다.
이는 트리거가 완료될 때까지 여러 개의 테이블을 잠그는 결과를 초래할 수 있다는 의미이다.
이런 트리거를 별개의 트랜잭션들로 쪼개면 더 적은 수의 자원을 잠그게 돼서 필요한 경우 변경사항 복원을 쉽게 만들어준다.
가능하면 트리거를 피하라.
16. GUID에 대한 클러스터링을 피하라
테이블 데이터 정렬을 위해 GUID(Globally Unique Identifier: 범용 고유 식별자)를 사용하지 말라.
임의로 생성되는 이런 16비트 숫자는 사용자의 테이블을 훨씬 더 빨리 파편화한다.
DATE나 IDENTIFY 같은 값을 점진적으로 증가시켜서 데이터를 정렬하는 것이 훨씬 낫다.
휘발성 있는 모든 열에 대해서도 갖은 규칙이 적용된다.
단 몇 분 만에 극적으로 테이블들이 파편화될 수도 있다.
17. 테이블에 있는 모든 것을 카운트(Count)하지 말라
테이블에 데이터가 존재하거나 어떤 고객에 대한 데이터가 존재하는 지를 확인할 필요가 있으며, 확인 결과에 따라, 어떤 조치를 취해야 한다고 가정하자.
그런 데이터의 존재를 확인하기 위해 누군가가 SELECT COUNT(*) FROM dbo.T1 명령을 실행하는 것을 자주 보았다.
SET @CT = (SELECT COUNT(*) FROM dbo.T1);
If @CT > 0
BEGIN <Do something>
END
전혀 불필요한 명령이다. 존재 여부를 확인하고 싶다면, 다음과 같이 하라:
If EXISTS (SELECT 1 FROM dbo.T1)
BEGIN
<Do something>
END다른 말로 하면, 테이블에 있는 모든 것을 카운트하지 말라는 것이다.
첫 번째 행으로 돌아가면 찾을 수 있다. SQL 서버는 EXIST 문을 제대로 사용할 수 있을 정도로 똑똑하며, 두 번째 블록의 코드는 아주 빠르게 결과를 돌려준다. 테이블이 크면 클수록, 더 많은 차이를 낼 것이다.
18. 행을 카운트하려면 시스템 테이블(System Table)을 사용하라
커다란 테이블의 행을 정말로 카운트할 필요가 있다면, 시스템 테이블에서 끌어 올 수 있다. SELECT rows from sysindex 명령문은 모든 인덱스에 대한 열의 수를 알려줄 것이다.
그리고 클러스터된 인덱스가 데이터 자체를 나타내기 때문에, ‘WHERE indexid = 1’을 추가하면 테이블 행을 얻을 수 있다. 그 다음에는 그냥 테이블 이름을 추가하기만 하면 만사형통이다. 이렇게 하면, 최종 쿼리는 다음과 같다
SELECT rows FROM sysindexes WHERE object_name(id) = ‘T1’ AND indexid = 119. 필요한 수의 열만 끌어오라
열을 개별적으로 나열하는 대신 모든 쿼리를 SELECT * 명령문으로만 코딩한다면 너무 쉬울 것이다.
또 다시 문제는 필요한 것보다 더 많은 데이터를 끌어 온다는 것이다.
개발자가 120개의 열과 수 백만 개의 행을 가지고 있는 테이블을 대상으로 SELECT *를 실행하고는, 겨우 3~5개만 사용하고 말았다.
그 시점에, 개발자는 필요한 것보다 훨씬 더 많은 데이터를 처리시켰을 뿐만 아니라 다른 프로세스들로부터 자원을 뺏어가기도 한 것이다.
20. 네거티브 검색(Negative Search)를 피하기 위해 쿼리를 재 작성하라
인덱스를 사용할 수 없는 쿼리를 사용해서 데이터를 행 별로 비교할 필요가 있을 때, 예를 들어 FROM Customers WHERE RegionID <> 3같은 경우는 인덱스를 사용할 수 있도록 쿼리를 재작성하는 것이 더 낫다.
SELECT * FROM Customers WHERE RegionID < 3 UNION ALL SELECT * FROM Customers WHERE RegionID데이터 세트가 큰 경우, 인덱스를 사용하는 것이 테이블 스캔 버전을 크게 능가하는 결과를 내 놓을 수도 있다.
물론, 더 열악한 결과를 낼 수도 있으니 구현에 앞서 시험해보라.
필자는 이 쿼리가 팁 13번(중복 처리를 피하라)을 어긴다는 것을 알았지만, 융통성 없는 규칙은 없다는 것을 보여주는 것이기도 하다.
여기서는 중복 처리를 했지만, 대가가 큰 테이블 스캔을 피하기 위해서이다.
21. 맹목적으로 코드를 재사용하지 말라
필요한 데이터를 끌어온다는 것을 알기 때문에 다른 누군가의 코드를 복사하기가 십상이다.
문제는 종종 필요한 것보다 훨씬 더 많은 데이터를 끌어오고 있으며, 개발자들이 양을 줄이려 하는 경우는 거의 없어서, 거대한 데이터 상위 집합에 이르고 만다.
이는 대개 추가적인 외부 조인(Outer Join)이나 WHERE 문에서 추가 조건 형태로 나타난다.
재사용된 코드를 꼭 필요한 수준으로 줄일 수 있다면 커다란 성능 이득을 볼 수 있다.
추가적인 튜닝 규칙
가급적 WHERE 조건에서는 인덱스 컬럼을 모두 사용한다.
인덱스를 만들어 놓더라도 WHERE 조건을 어떻게 명시하느냐에 따라 옵티마이저가 인덱스를 사용할 수도 있고 사용하지 않을 수도 있다.
예를 들어 A와 B라는 컬럼의 인덱스를 만들었는데, WHERE 조건에서 A 컬럼만 사용한다면 인덱스를 타지 않게 된다.
예) CONTRACT 테이블에서 CONTRACT_NO, CONTRACT_REV 컬럼이 CON_NO_IDX 인덱스로 만들어져 있을 때, 아래와 같은 쿼리는 인덱스를 사용하지 않고 FULL SCAN을 하게 된다.
SELECT *
FROM CONTRACT
WHERE CONTRACT_REV ='1'아래와 같이 사용하여야 한다.
SELECT *
FROM CONTRACT
WHERE CONTRACT_NO = '900000'
AND CONTRACT_REV = '1'인덱스 컬럼에 사용하는 연산자는 가급적 동등 연산자(=)를 사용하라.
인덱스 컬럼을 WHERE조건에 모두 명시하였더라도 LIKE와 같은 연산자를 사용하면 인덱스 효율이 떨어진다.
LIKE 외에도 IS NULL, IS NOT NULL, NOT IN 등이 사용되었을 경우에도 마찬가지 현상이 발생한다.
인덱스 컬럼은 변형하여 사용하지 않도록 한다.
WHERE 조건에 인덱스 컬럼을 사용했고, 동등 연산자를 사용했다 하더라도 인덱스 컬럼에 변형을 가하게 되면 인덱스를 사용하지 못한다.
SELECT *
FROM CONTRACT
WHERE SUBSTR(CONTRACT_NO, 1,1,) = '9'
AND CONTRACT_REV = '1'- 컬럼에 변형을 가하였을 때
SELECT *
FROM CONTRACT
WHERE CREATOR_ID LIKE 'KKK%'
SELECT *
FROM CONTRACT
WHERE SUBSTR(CREATOR_ID, 1, 3) = 'KKK'CREATOR_ID가 UNIQUE 인덱스일 경우, 첫 번째 문장은 LIKE를 사용하여 INDEX ROWID SCAN이 아닌 INDEX RANGE SCAN 방식을 사용하게 된다.
하지만 두 번째는 컬럼자체에 변형을 가했기 때문에 FULL SCAN을 하게 된다.
OR 보다는 AND를 사용해라.
옵티마이저의 OR-Expansion 처리는 OR 연산자로 연결된 쿼리를 UNION ALL로 변환하므로 OR 보다는 AND를 사용해야 성능 좋은 쿼리를 작성할 수 있다.
그룹핑 쿼리를 사용할 경우 가급적 HAVING 보다는 WHERE 절에서 데이터를 필터링하라.
그룹핑 쿼리 처리순서는 WHERE 조건이 먼저 처리되므로 가급적 필터링 할 대상은 WHERE 조건에서 처리할 수 있게 쿼리를 작성하도록 한다.
HAVING 절은 이미 WHERE 절에서 처리된 로우들을 대상으로 조건을 감시하기 때문에 좋은 성능을 발휘하기가 힘들다.
DISTINCT는 가급적 사용하지 않는다.
DISTINCT는 키워드 내부적으로 정렬 작업을 수반하기 때문에 꼭 필요한 경우가 아니라면 사용하지 않는다.
IN, NOT IN 대신 EXISTS 와 NOT EXISTS를 사용하라
IN 이나 NOT IN 연산자 보다는 EXISTS 나 NOT EXISTS를 사용하는 것이 더 좋은 성능을 발휘한다.
- IN 사용
SELECT A.*
FROM CONTRACT A, CONTRACTOR B
WHERE A.CONTRACT_NO = B.CONTRACT_NO
AND B.CONTRACT_NO ('1111', '2222', '3333');- EXISTS 사용
SELECT *
FROM CONTRACT A
WHERE EXISTS ( SELECT 1
FROM CONTRACTOR B
WHERE A.CONTRACT_NO = B.CONTRACT_NO
AND B.CONTRACT_NO IN ('1111', '2222', '3333') );
언뜻 보면 아래의 문장이 성능이 좋지 않을 것 같지만,
EXISTS를 사용한 두 번째 쿼리의 비용(2)이 IN을 사용한 첫 번째 쿼리의 비용(3) 보다 더 적다.
실제 테이블에 저장된 데이터 양이 많지 않아 성능 차이는 거의 없다고 볼 수 있지만, 수십, 수백만 건의 데이터가 저장되어 있다면 눈에 띄게 그차이가 드러난다.
SET 연산자 사용시 UNION 대신 UNION ALL을 사용하라.
UNION 연산자는 연결된 쿼리에서 동일한 로우는 제거하고 한 로우만 반환하게 된다.
따라서 추가적으로 필터링하는 로직이 숨어 있으므로 UNION ALL 보다는 비용이 높을 수 밖에 없다.
🙆♂️ 참고사이트 🙇♂️
