SQL의 성능은 처리범위에 좌우된다.

이를 인덱스를 잘 설정하면 처리범위를 최소화 할 수 있게 되고, 결국 성능을 향상시킬수 있다.

0. 인덱스를 거는 이유

1. 처리범위의 양을 줄이기위해

1.1. 결합인덱스를 구성하는 컬럼의 순서

2. 랜덤 액세스의 양을 줄이기위해

2-1. 랜덤 액세스의 종류

2.1.1. 확인 랜덤 액세스 

WHERE, HAVING조건의 컬럼이 인덱스에 존재하지 않아 발생하는 랜덤 액세스

- 점조건, 선분조건으로 인덱스를 걸어준다.

2.1.2 추출 랜덤 액세스 

2.1.3 정렬 랜덤 액세스

2.1.4 요약

3. 정렬횟수를 줄이기위해

ORDER BY를 사용하지 않더라도, 인덱스에 의해서 정렬이 되기 때문에, 인덱스를 잘 활용하면 정렬의 양을 줄일수 있다.

DBMS에서 SQL의 정렬을 제거할 수 있다면, SQL의 단순화와 성능 향상이라는 매우 큰 혜택을 얻을 것이다.

테이블 - 데이터가 INSERT되는 순서에 의해 저장되므로 어떤 컬럼에 의해 정렬된 데이터가 저장되지않는다.

인덱스 - 인덱스를 구성하는 컬럼에 의해 정렬된 데이터가 저장된다. 인덱스의 첫번째 컬럼에 의해 정렬되며, 첫번째 컬럼의 값이 동일한 데이터에 대해서는 인덱스의 두번째 컬럼에 의해 정렬된다.

출처 : http://www.gurubee.net/expert/kwontra

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

1. 구문 분석(Parsing)

해당 쿼리가 문법적으로 틀리지 않은지 확인. 해당 구문을 SQL 서버가 이해할 수 있는 단위들로 분해하는 과정.

만약 구문이 부정확하다면 여기서 처리를 중단. 이 문장이 일괄 처리(batch) 내에 있다면 일괄 처리 전체를 중단.(Batch abort : Batch 중 하나라도 syntax error가 있다면 전체 batch가 실행되지 않는다.)

2. 최적화(Optimization)

통계나 조각 정보 등을 바탕으로 실행 계획을 만들어 낸다. 쿼리처리에서 매우 중요한 단계

① 쿼리 분석 : 검색 제한자(SARG)인지 조인 조건인지 판단.

② 인덱스 선택 : 분포 통계 정보를 이용하여 인덱스검색이나 테이블 스캔 중의 하나를 선택. 여러 인덱스 중 가장 효율적인 인덱스 하나를 선택

③ 조인 처리 : JOIN, UNION, GROUP BY, ORDER BY 절을 가지고 있는지 확인하여 적절한 작업 순서를 선택

이 단계의 출력은 실행 계획(Execution Plan) 이다.

SQL 최적화단계(Optimizer)

3. Row Source Generator 

옵티마이저의 최적화 과정을 거치면 SQL처리과정을 표현한 실행계획이 만들어 진다.

이런 실행계획을 실행 가능한 코드 또는 프로시저 형태로 포맷팅(tree형식으로 만듦)하는 작업이 필요하며, 이 역할을 Row-Source Generator가 담당 한다. Row-Source는 레코드 집합을 반복 처리하면서 사용자가 요구한 최종 결과집합을 실제적으로 생성하는데 사용되는 제어 구조를 말한다.

Row Source Generator는 Row Source Tree(명령문에의해 참조되는 테이블의 순서, Access Method, Join Method, Filter / Sort / Aggregation과 같은 데이터 오퍼레이션을 포함)를 생산하며 Row Source는 테이블, 뷰,조인이나 그룹핑의 결과등이 해당된다.

4. 실행(Execute)

QL실행 엔진이 각각의 Row Source Tree의 각각 로우 소스를 실행하여 결과를 Client로 보낸다. (INSERT/UPDATE/DELETE 같은 DML인 경우 반드시 거쳐야 한다.)

실행기간동안 DB는 메모리에 데이터가 없는 경우 디스크로부터 데이터를 읽으며 SQL문의 실행에 따른 변화를 로깅하거나 데이터 무결성을 지키기 위해 DBLOCK을 걸거나 LATCH를 획득한다.

SELECT 실행 순서 

1. 문법 순서

2. 실행 순서

3.설명

별칭(Alias)

출처 : http://ojc.asia/bbs/board.php?bo_table=LecOrccleTun&wr_id=113

http://wiki.gurubee.net/pages/viewpage.action?pageId=3901322