쿼리튜닝팁

SQL 쿼리 튜닝 팁

1. SELECT * ... 문장은 피하세요. 

쿼리의 결과로 모든 필드의 결과가 필요한 경우가 아니라면 SELECT리스트에 필요 한 필드들을 일일이 적어 주어야 합니다. 즉, "SELECT * From Table" 과 같은 쿼 리는 "SELECT Field1, Field2 From Table ..."과 같이 필요한 필드를 밝힌 쿼리로 작성하기 바랍니다. 

2. 알맞은 인덱스를 구성해서 쿼리가 인덱스를 타게 하세요. 

(효율적으로 인덱스를 구성하고 사용한다면 쿼리의 속도를, 그리고 궁극적으로는 DB 서버의 퍼포먼스를 놀라울 정도로 향상시킬 수 있습니다.)

3. 쿼리가 여러 번 DB서버에 들어가지 않게 하세요. 

만일 웹 서버가 DB 서버에 데이터를 요청할 때 저장 프로시저를 사용하지 않고 웹 서버에서 작성한 쿼리를 DB서버에 보내는 방식이라면, 되도록 쿼리가 DB서버에 자주 들어가지 않도록 작성하기 바랍니다. ex. 루프 내에서 단순한 SELECT쿼리를 계속 부르는 실수입니다. 이러한 경우에는 필요한 데이터를 모두 SELECT해서 가져온 다음에 레코드셋을 가지고 루프 연산을 수행하는 것이 좋습니다. ex. UPDATE 쿼리를 사용할 때 종종 실수를 하는 일이 있습니다. 어떤 값을 하나 증가시키기 위해 해당 값을 일단 SELECT 해 와서 그 값을 하나 증가시킨 뒤에 데 이터를 업데이트하는 경우가 있습니다. 이런 경우에는 당연히 "UPDATE Table SET Field = Field + 1 WHERE ..."와 같 은 쿼리로 해당 값을 SELECT하지 말고 한번의 UPDATE쿼리로 처리하는 것이 좋 습니다. ex. 서브쿼리가 있다는 것을 인식하지 못하는 데서 일어나는 실수도 있습니다. (즉, SELECT쿼리를 통해서 데이터를 반환 받고 반환 받은 값을 변환한 다음, INSERT쿼리를 통해서 변환한 값을 다른 테이블에 넣는 경우가 이러한 예에 해당합 니다.)

4. JOIN쿼리에서 사용 메모리를 줄이는 방법을 생각하세요. 

기본적으로 JOIN (여기에서는 Inner Join)을 사용하면 ON 절에 밝힌 조건에 의해 서 데이터를 추린 다음 WHERE 절 조건을 적용합니다. 따라서 ON 절 조건에 의해 추려지는 데이터는 메모리에 설정됩니다. 이때 아예 JOIN을 피할 수 있도록 테이블을 구성할 수 있다면 그렇게 하는 것이 가 장 좋은 방법이겠지만, 그렇게 할 수 없다면 되도록 JOIN 할 때 설정되는 임시 메 모리의 크기가 작게 만들어지도록 두 테이블에서 값은 같지만 메모리는 더 작게 차 지하는 필드를 찾아 그것을 ON조건에 사용하는 것이 유리합니다. 

5. SELECT가 많다면 NOLOCK 힌트를 줄 수도 있어요. 

( NOLOCK이란 DB서버 내부적으로 해당 데이터를 읽고 있음을 표시해 주는 LOCK을 설정하지 않는 것을 의미합니다.) 서비스 내용 구성상 SELECT 쿼리는 자주 일어나는데 INSERT나 UPDATE쿼리는 거의 일어나지 않는 환경이라면 "SELECT Field1 FROM Table (NOLOCK) ..."과 같이 NOLOCK 힌트를 주면 좋습 니다. 참고로 NOLOCK힌트의 단점은 NOLOCK을 쓰게 되면 변화된 데이터 내용을 반영 하지 않거나, 데이터가 변경 중이라도 데이터를 읽어 오는 현상이 일어날 수 있기 때문에 사용상의 주의가 요구됩니다. 다이내믹 타입의 레코드셋을 사용하는 것과 같이 변경된 값이 바로 반영되어야 하는 환경이 아니라면 크게 문제 되지는 않을 것 이지만요.

6. TOP을 이용해서 필요한 만큼만 부르세요. 

"SELECT * ..."가 불필요한 데이터까지 다 반환하는 좋지 못한 예였다면, TOP을 사용하지 않아서 필요도 없는 데이터 행(Row)까지 반환하는 것도 비슷한 맥락에서 퍼포먼스를 저하시키는 예라고 할 수 있습니다. 예를 들어 서비스의 한 웹 페이지에 게시판 목록을 보여주는 기능, 즉 어떤 자료에 대한 리스트를 10개 보여 주고 다음 페이지로 넘어가면 다음 리스트 10개를 보여 주는 페이지를 작성한다고 합시다. 그러면 처음 웹 페이지를 구성하는 데에 필요한 데이터는 10개인데도 불구하고 모든 데이터 행을 다 SELECT해 온 다음, 웹 스크 립트에서 루프를 10번 돌리면서 페이지를 꾸미는 경우를 자주 보았습니다. 이렇게 하면 리스트 페이지를 하나씩 넘어갈 때마다 전체 데이터가 반환되므로 네트워크 부하는 물론이고 웹 서버와 DB 서버의 메모리 부하도 커지게 되므로 결코 좋은 방 법이라고 할 수 없습니다. 이런 경우에는 "SELECT TOP n ..."과 같이 TOP을 써서 필요한 수만큼 데이터를 반환하도록 해 줍니다. 

7. 빠른 데이터 개수 반환 팁을 쓰세요. 

테이블에 저장되어 있는 데이터의 총 개수를 알아내기 위해서 "SELECT COUNT (PromaryKeyField) ..." 혹은 "SELECT COUNT(*) ..."와 같이 COUNT함수를 사용하는 경우가 있습니다. 이것이 잘못되 었다는 말이 아니고 만일 테이블에 있는 데이터 행의 총 개수를 알아내고자 하는 경 우라면 "SELECT rows FROM sysindexes WHERE id = OBJECT_ID('Table이름') AND indid <2" 의 쿼리를 사용하는 편이 속도가 훨씬 빠르다는 것입니다. (총 개수를 구하고자 할 때 사용하며, 일부 개수를 구할 수는 없습니다.)

8. 뷰나 스토어드 프로시져를 사용하세요. 

긴 쿼리문을 네트웍으로 전송하는것에 비해 뷰나 스토어드 프로시져는 그 이름만 전송하기 때문에(파라미터가 있다면 이것도 포함) 네트웍 트래픽을 감소시킬 수 있 습니다.. 게다가 보안관리까지 할 수 있기 때문에 여러분이 사용자에게 숨겨야하는 컬럼의 액세스 제한을 할 수 있습니다. 

9. 가능한 한 SQL Server 커서의 사용을 피하세요. 

SQL Server 커서는 select문에 비해 성능상에 좋지 않습니다.. 행 단위의 처리가 필요하다면 상관질의나 유도된 테이블을 사용하도록 노력해보세요. 

10. 트리거 대신에 제약조건을 사용하세요. 

제약조건은 트리거보다 성능면에서 훨씬 효율적입니다. 따라서 가능한 한 제약조건 을 사용하세요. 

11. 임시테이블 대신 테이블 변수를 사용하세요. 

테이블 변수는 임시테이블에 비해 잠금과 로깅 작업에 적은 리소스가 소모됩니다.. 따라서 가능한 한 테이블 변수를 사용하세요.

12. HAVING절의 사용은 피하세요. 

Having절은 GROUP BY에 의한 결과를 제한할 때 사용합니다.. GROUP BY에 Having절을 사용하였을 경우 GROUP BY에 의해서 결과들을 모두 집계한 다음 Having절에 명시한 조건으로 맞지 않는 결과를 버리게 됩니다.. 대부분의 경우 Having절의 필요 없이 GROUP BY와 Where절만으로 원하는 결과를 얻을 수 있습 니다.. 

13. 가능한 한 DISTINCT 문의 사용을 피하세요. 

DISTINCT문을 사용할 경우 소트에 따른 성능 하락이 있기 때문에 꼭 필요한 경우 에만 사용하세요. 

14. 결과에 적용된 행의 갯수를 표시하지 않아도 된다면 프로시져에 SET NOCOUNT ON을 추가해보세요. 

몇개의 행이 적용되었는지가 전달되지 않기 때문에 네트웍 트래픽이 감소합니다. 

15. 가능한 한 UNION 대신에 UNION ALL을 사용하세요. 

UNION ALL이 UNION보다 훨씬 빠릅니다.. 왜냐하면 UNION ALL은 로우의 중복 검사를 하지않는 반면에 UNION은 중복행이 있건 없건간에 중복검사를 수행하기 때문입니다.

16. SELECT * FROM TBL WHERE IDX LIKE '%A%'를하면 인덱스가 깨집니다.

17. INNER JOIN과 LEFT OUTER JOIN의 성능이 비슷하다고 생각될경우 INNER JOIN이 성능이 더 우수합니다.

출처: https://www.slideshare.net/ETRIBE_STG/sql-37344670