[DB] 더 빠른 SQL 쿼리를 위한 데이터베이스 튜닝 규칙

- IT WORLD 매거진 참고.

- 튜닝하기 위한 정답이 존재하지는 않는다. 

- SQL Server(MS-SQL), 오라클, DB2, 사이베이스, MySQL, MariaDB

- 최고의 성형은 다이어트? 최고의 튜닝은 네트워크 트래픽 최소화.

​

1. 가능하면 커서(Cursor)를 피하라.

커서는 속도 문제를 겪을 뿐 아니라, 다른 작업을 필요 이상 지연시킬 정도로 하나의 작업을 블록(Block)시킬 수도 있다.

우리는 BM 특성상 커서를 사용하는 부분이 있다. BM이해 없이 그대로 복사해서 작업시 문제가 될 수 있다. 

​

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. ORM을 피하라

ORM(Object-relational Mapper: 객체 관계형 매퍼)는 지구상에서 최악의 코드를 만들어 내고 있으며, 개발자가 직면할 가능성이 있는 대부분의 성능 문제에 책임이 있다. 그렇지만, ORM을 피할 수 없다면, 스스로 자체적인 저장 프로시저를 작성하고 ORM이 자체 쿼리를 작성하는 대신 사용자가 작성한 쿼리를 호출하게 함으로써 부정적인 측면을 최소화할 수 있다.

​

11. 가능한 경우, 저장 프로시저(Stored Procedure)를 사용하라

더 훌륭한 코드로 이끄는 것 외에, 저장 프로시저는 다른 많은 장점도 가지고 있다. 저장 프로시저는 호출이 더 짧을 것이기 때문에, 트래픽을 크게 줄여준다. 프로파일러(Profiler) 같은 도구를 사용해서 추적하기가 더 쉬워서 사용자가 성능 통계치를 확보하고 잠재적인 문제를 더 빨리 규명할 수 있게 해준다. 더욱 일관성 있는 방식으로 정의할 수 있으며, 이는 실행 계획(Execution Plan)을 재사용할 가능성이 더 높으며, 임의 쿼리에 비해 엣지 케이스(Edge Case)와 감사용으로 사용하기가 더 쉽다는 의미이다.

​

많은 닷넷 코더들은 비즈니스 로직이 데이터베이스가 아닌 애플리케이션의 프론트 엔드에 속한다고 믿고 있다. 그렇지만, 그들은 틀렸다(대부분의 경우).

​

12. 더블 디핑(Double-Dipping: 중복 처리)을 피하라

저장 프로시저 사용은 때로 “더블 디핑”으로 이어질 수 있다. 대규모 테이블에 대해 별개의 쿼리를 여러 개 실행하고, 그것들을 임시 테이블에 넣은 다음에, 테이블들을 다시 조인하는 것이다. 이는 성능에 커다란 방해물이 될 수 있다. 가능한한 대규모 테이블을 한 번만 쿼리 하는 것이 훨씬 더 낫다.

​

조금 다른 시나리오는 한 프로세스의 몇 가지 단계에서 커다란 테이블의 일부가 필요한 경우로, 이는 매 번 커다란 테이블에 대한 쿼리를 유발한다. 일부에 대한 쿼리를 실행하고 그것을 다른 곳에 영속화 시킨 다음에, 후속 단계를 영속화된 더 작은 데이터 세트로 유도하라.

​

13. 커다란 트랜잭션은 작은 트랜잭션 여러 개로 쪼개라

단일 트랜잭션에서 여러 개의 테이블을 처리하는 작업은 해당 트랜잭션이 끝날 때까지 모든 테이블을 잠글 수 있기 때문에, 다수의 블로킹으로 이어진다. 해결책은 이 트랜잭션을 각각이 개별적으로 단일 테이블에 대한 작업을 하는 여러 개의 루틴(Routines)으로 쪼개는 것이다. 이는 블로킹 횟수를 줄여주고 다른 작업들이 계속해서 이루어질 수 있도록 다른 테이블들을 풀어준다.

​

14. 트리거(Trigger) 사용을 자제하라

하려고 하는 작업이 무엇이든, 원래 작업의 동일한 트랜잭션에서 수행될 것이기 때문에 트리거 사용도 비슷한 문제로 이어질 수 있다. 이는 트리거가 완료될 때까지 여러 개의 테이블을 잠그는 결과를 초래할 수 있다는 의미이다. 이런 트리거를 별개의 트랜잭션들로 쪼개면 더 적은 수의 자원을 잠그게 돼서 필요한 경우 변경사항 복원을 쉽게 만들어준다. 가능하면 트리거를 피하라.

​

15. GUID에 대한 클러스터링을 피하라

테이블 데이터 정렬을 위해 GUID(Globally Unique Identifier: 범용 고유 식별자)를 사용하지 말라. 임의로 생성되는 이런 16비트 숫자는 사용자의 테이블을 훨씬 더 빨리 파편화한다. DATE나 IDENTIFY 같은 값을 점진적으로 증가시켜서 데이터를 정렬하는 것이 훨씬 낫다. 휘발성 있는 모든 열에 대해서도 갖은 규칙이 적용된다. 단 몇 분 만에 극적으로 테이블들이 파편화될 수도 있다.

​

16. 테이블에 있는 모든 것을 카운트(Count)하지 말라

테이블에 데이터가 존재하거나 어떤 고객에 대한 데이터가 존재하는 지를 확인할 필요가 있으며, 확인 결과에 따라, 어떤 조치를 취해야 한다고 가정하자.

​

17. 행을 카운트하려면 시스템 테이블(System Table)을 사용하라

커다란 테이블의 행을 정말로 카운트할 필요가 있다면, 시스템 테이블에서 끌어 올 수 있다. ‘SELECT rows from sysindex’ 명령문은 모든 인덱스에 대한 열의 수를 알려줄 것이다.

​

그리고 클러스터된 인덱스가 데이터 자체를 나타내기 때문에, ‘WHERE indid = 1’을 추가하면 테이블 행을 얻을 수 있다. 그 다음에는 그냥 테이블 이름을 추가하기만 하면 만사형통이다. 이렇게 하면, 최종 쿼리는 다음과 같다

​

SELECT rows FROM sysindexes WHERE object_name(id) = ‘T1’ AND indexid = 1

​

18. 필요한 수의 열만 끌어오라

열을 개별적으로 나열하는 대신 모든 쿼리를 SELECT * 명령문으로만 코딩한다면 너무 쉬울 것이다. 또 다시 문제는 필요한 것보다 더 많은 데이터를 끌어 온다는 것이다. 개발자가 120개의 열과 수 백만 개의 행을 가지고 있는 테이블을 대상으로 SELECT *를 실행하고는, 겨우 3~5개만 사용하고 말았다. 그 시점에, 개발자는 필요한 것보다 훨씬 더 많은 데이터를 처리시켰을 뿐만 아니라 다른 프로세스들로부터 자원을 뺏어가기도 한 것이다.

​

19. 네거티브 검색(Negative Search)를 피하기 위해 쿼리를 재 작성하라

인덱스를 사용할 수 없는 쿼리를 사용해서 데이터를 행 별로 비교할 필요가 있을 때, 예를 들어 FROM Customers WHERE RegionID <> 3 같은 경우는 인덱스를 사용할 수 있도록 쿼리를 재작성하는 것이 더 낫다.

​

SELECT * FROM Customers WHERE RegionID < 3 UNION ALL SELECT * FROM Customers WHERE RegionID

​

데이터 세트가 큰 경우, 인덱스를 사용하는 것이 테이블 스캔 버전을 크게 능가하는 결과를 내 놓을 수도 있다. 물론, 더 열악한 결과를 낼 수도 있으니 구현에 앞서 시험해보라.

​

필자는 이 쿼리가 팁 13번(중복 처리를 피하라)을 어긴다는 것을 알았지만, 융통성 없는 규칙은 없다는 것을 보여주는 것이기도 하다. 여기서는 중복 처리를 했지만, 대가가 큰 테이블 스캔을 피하기 위해서이다.

​

20. 맹목적으로 코드를 재사용하지 말라

필요한 데이터를 끌어온다는 것을 알기 때문에 다른 누군가의 코드를 복사하기가 십상이다. 문제는 종종 필요한 것보다 훨씬 더 많은 데이터를 끌어오고 있으며, 개발자들이 양을 줄이려 하는 경우는 거의 없어서, 거대한 데이터 상위 집합에 이르고 만다. 이는 대개 추가적인 외부 조인(Outer Join)이나 WHERE 문에서 추가 조건 형태로 나타난다. 재사용된 코드를 꼭 필요한 수준으로 줄일 수 있다면 커다란 성능 이득을 볼 수 있다.