sql 튜닝 / 튜닝에 도움 되는 21가지 규칙 / 튜닝 가이드 / 더 빠른 데이터베이스 쿼리(1/3)

데이터 베이스의 데이터들을 효율적으로 관리하려면 최적화된 쿼리가 필수적일 것이다.

데이터 베이스의 효율성을 위해 쿼리 속도가 빨라지는 SQL 쿼리 튜닝을 고려해 볼수 있다.

 

SQL 개발자와 DBA 모두 이런 목표를 달성하기 위해 오랜 사용으로 효과가 입증된 여러 가지 방법에 의지한다.

하지만 그 어떤 방법이라도 하나만으로는 완벽하거나 확실하지 않다.

 

이처럼 모든 쿼리를 튜닝하기 위한 딱딱 정답이 정해져있는 것은 아니지만 , "SQL 튜닝"을 하나도 모르는 상태보다는 훨씬 나을 것이다. 이러한 규칙들이 존재한다는 사실을 알고 있다면 나중에 필요할 때 키워드를 검색하여 찾아서 사용할 수 있다.

 

데이터 베이스 쿼리 튜닝

SQL 서버(MS-SQL), 오라클(Oracle), DB2, 사이베이스, MySQL, 아니면 다른 관계형 데이터베이스 플랫폼 중 어디에서 SQL을 짜고 있더라도, 같은 결과를 조금이라도 효율적으로, 빨리 돌아가게 한다는 목표는 같다.

 

데이터베이스는 가능한 빨리 쿼리를 처리하는 동시에 많은 동시 접속자를 지원하는 것이다.

이는 공간과 자원 관리를 최적화하는 동시에 잠금(Locking), 입출력, 그리고 네트워크 트래픽은 최소화해야 한다는 것을 의미한다.

때문에 데이터베이스 튜닝은 기술인 동시에 과학이다.

다음은 사용 중인 데이터베이스를 더 빠르고 더욱 효과적으로 만들어주는 21가지 규칙이다.

 

 

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

다시말해 커서를 사용하지 않고 똑같은 결과값을 얻을수 있다면, 커서는 사용하지 않는 것이 좋다.

커서는 일련의 데이터에 순차적으로 액세스할 때 검색 및 현재 위치를 포함하는 데이터 요소를 말한다.

여기서 '순차적으로'라는 단어가 핵심이다.

한꺼번에 할수 있는 작업을 순차적으로 한다면 비효율적인 일이 되어버릴수 있다. 

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

 

- 커서 개념 / MS-SQL 커서 사용법

https://taewooblog.tistory.com/41

2. 커서를 피할 수 없다면, 임시 테이블(temp table)을 사용하라
커서를 사용하지 않는 것을 지향하지만, 커서를 반드시 사용해야만 할 경우가 있다.

그런 경우, 라이브 테이블(Live Table)보다는 임시 테이블에 대한 커서 작업을 수행하는 것이 더 낫다.

훨씬 더 작은 라이브 테이블에 대한 하나의 UPDATE 문이 있을 수 있다. 짧은 시간 동안에만 잠금(Lock)을 유지하게 되어 동시성을 크게 증진시켜 준다.

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

한마디로 필요한 데이터만 모아서 임시테이블을 형성하고 그것을 활용하라는 말이다.

 

- 임시테이블/테이블변수 개념,사용법 

https://taewooblog.tistory.com/49?category=327968

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

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

 

서버와 통신을 자주한다는 것 자체가 서버에겐 큰 부담이다.

 

반대로 자주 사용하는 데이터를 필요할 때마다 디비 접속하고 조인걸어 가져오게되면 성능저하가 발생할 수 밖에 없을 것이다. 자주 쓰이는 데이터는 캐시나 쿠키에 일시적으로 저장해놓고 쓰는 것도 좋은 방법이다. 

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

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

 

- View (가상테이블) 개념 및 사용법 

https://taewooblog.tistory.com/66

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

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

 

당연히 update문을 사용하지 않고 case 문으로 같은 효과를 낼 수 있다면, case문이 나을 것이다. update문이 case문 보다 훨씬 가볍다. 

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

 

 

 

 

이해가 잘되지 않는 부분도 있지만 찬찬히 읽어 보면 나중에 큰힘이 될 것이다.

 

 

 

 

reference:
http://www.itworld.co.kr/tags/2665/SQL/105792?page=0,0#csidxa2d9ebeaa795fec8061a380e4a59328