[DB튜닝]개발자를 위한 튜닝 가이드 - 쿼리 디자인

개발자를 위한 튜닝 가이드 - 쿼리 디자인

 

쿼리 디자인

번호	수칙	체크
1	SELECT는 필요한 결과값만을 요구하는가?
2	적절한 WHERE조건을 사용하는가?
3	COUNT(컬럼명) 대신 COUNT(*)을 사용하는가?
4	커서 및 임시 테이블의 내용을 최대한 자제하는가?
5	VIEW의 총 사용을 줄였는가?
6	저장 프로시저를 사용하는가?
7	저장 프로시저를 적절하게 리컴 파일 하며 사용하는가 ?
8	작명 된 저장 프로시저 SP외의 접두어를 사용하는가 ?
9	모든 개체의 소유자는 DBO로 지정하며 생성했는가 ?
10	데드락이 발생하는 부분을 라이브락 형태로 변경했는가?
11	SET NOCOUNT ON을 사용하는가?
12	실무 사례: 저장 프로시저 관리 방법

 

스티브 맥코넬이 이런 말을 했습니다. 
뛰어난 디자이너는 습득한 지식을 사용하지 않는 것과 그 지식을 처음부터 확보하지 못한 것을 동일하게 봅니다. 
이말 뜻을 다음과 같이 해석하고 싶습니다. 여러분은 쿼리 분석기의 기능들이 어떤 것이 있고, 단축키가 메뉴우측에 작게 표시되어 있다는 것을 대부분 알고 있습니다. 하지만 잘 사용하지는 않고 있을 것입니다. 라고 말입니다. 그래서 먼저 단축키와 그 사용법에 대해 안내하는 시간을 우선 가지도록 하겠습니다. 
다음을 실습해보고 자세한 것은 표를 참조합시다

 

-- CTRL + E , F5 

-- 실행하기
----------------------------------------------------------------
use pubs 
go 

select * from titles 

-- CTRL + T => 결과 Text로 보기 

select * from titles 

-- CTRL + D => 결과 Text로 보기 

select * from titles 

-- CTRL + K => 실행계획 보기 

select * from titles 

-- F8 => 개체브라우저 보이기/감추기 

-- CTRL + R => 결과창 보이기/감추기 

-- 그외 CTRL + C , CTRL + V , CTRL + X 

-- CTRL + SHIFT + C => 주석달기 

select * from titles

 

다음의 표를 참조합시다.

	없음	Shift +	Ctrl+	Alt+	Shift+Ctrl+
A			전체 선택
B			중간 구분선 선택
C			복사		주석 달기
D			표 형태로 결과 표시	데이터베이스 선택
E			실행
F			찾기		파일로 결과 저장
G
H			교체
I			인덱스 튜닝마법사
J
K			실행 계획 보기
L			예상 실행 계획 보기		선택 내용을 소문자로
M
N			새 쿼리 윈도우
O			연결
P
Q
R			결과창 보이기/감추기		주석제거
S			저장
T			텍스트로 결과 표시
U					선택 내용을 대문자로
U			선택 내용을 대문자로
V			붙여넣기
W
X			자르기
Y			다시하기
Z			취소
F1	도움말	선택 내용을 도움말로 보기
F8	객체 브라우저보이기감추기

주요 단축키 사용 안내 입니다.

 

수칙1.SELECT는 필요한 결과값만을 요구하는가?

 

select title , price from titles where title_id = 'BU1032'

 

Select 하는 내용도 필요한 항목만을 가지고 오도록 되어 있어서 리소스가 전혀 낭비되지 않고 있습니다.

 

select title , price from titles

 

필요한 칼럼을 가져오기는 하지만 불필요한 전체 행(Row)들을 가져오고 있습니다.

 

select * from titles

 

불필요한 칼럼정보, 행(Row) 데이터를 가져오고 있습니다.

 

따라 하기 - 3개의 쿼리를 한번에 실행하기 

1.3개의 쿼리를 한 Session에서 실행하여 결과3개를 동시에 살펴봅니다.

2.결과 값으로 출력되는 데이터량의 차이를 확인합니다. 어느 쿼리가 가장 간결한 결과를 반환합니까? 
( 반드시 꼭 필요한 결과만 반환하게 하는 것이 좋습니다. 
select title , price from titles where title_id = 'BU1032' 가 적절합니다.)

 

수칙2.적절한 WHERE 조건을 사용하는가?

 

인덱스란 데이터를 빨리 찾기 위해서 사용됩니다. 인덱스가 없다면 특정데이터를 찾기 위해서 모든 데이터페이지를 검색(Table Scan)해야만 합니다 그에 비해 인덱스가 존재하고 그 인덱스가 사용되는 것이 효과적이라면 SQL서버는 해당 인덱스 페이지를 사용하여 쉽게 데이터를 가져올 수 있는데 이를 인덱스 검색(Index Seek)이라 합니다.

그러나 이렇게 인덱스가 있더라도 이를 사용 불가능하게 하는 나쁜 쿼리가 있으니 이는 검색조건에서 불필요하게 칼럼이 변형된 경우입니다. 다음의 여러 나쁜 예를 좋은 예와 비교해 봅시다.

SARG(Search Argument)란 쿼리가 반환하는 결과를 제한하기 위하여 옵티마이저가 인덱스와 결합해서 사용할 수 있는 쿼리 내의 조건절을 말하는데 다음의 형태를 가집니다.

 

컬럼 연산자/변수

 

옵티마이저가 쓸모 있게 변환하는 것은 CTRL+K 실행 계획 상부 표시에서 관찰할 수 있습니다.

 

set showplan_all on 

select * from authors 
where au_lname like 'Ma%' 

-- OBJECT:([pubs].[dbo].[authors].[aunmind]), SEEK:([authors].[au_lname] >= 'Ma' AND 
[authors].[au_lname] < 'MB'), WHERE:(like([authors].[au_lname], 'Ma%', NULL)) 
ORDERED FORWARD 

-- set showplan_all off

 

따라 하기 

1.인덱스 찾기(Index Seek)를 확인합니다.

2.다음과 같이 약간의 조건절(where) 변형만으로 인덱스페이지가 사용되지 않음을 확인합니다.

3.그렇다면 조건절(where) 변형하고 싶을 땐 어떻게 해야 할까요?

4.다음 예제도 복습해 봅시다.

5.항상 실행 계획을 참조하여 재차 쿼리를 확인해야 합니다.

 

[참고] 쿼리 계획 은 다음의 몇 가지 단계로 이뤄집니다. 

1. 평범한 계획을 식별 
2. 획을 단순화 - having를 where로 != @param을 < @param OR > @param으로 변환하는 것 같은 작업을 수행합니다 
3. 로드 한다 - 쿼리 옵티마이저가 인덱스와 컬럼 통계, 다른 지원정보를 로드한다 
4. 근거하여 계획들을 평가한다 - 실행하는 비용이 충분히 저렴하다고 생각될 때 그 계획을 실행하도록 내놓는다 
5. 병렬화를 위해 최적화한다 - SMP

 

수칙3.COUNT(컬럼명) 대신 COUNT(*)을 사용하는가?

 

COUNT(*) 와 COUNT(컬럼명)의 차이는 중요합니다. COUNT 하는 해당 테이블 컬럼에 NULL 값을 포함하고 있다면 이 두 예제는 서로 다른 결과를 반환합니다. COUNT(컬럼명)은 그룹에 포함된 각 행을 평가하여 NULL이 아닌 값의 개수를 반환합니다. COUNT(*)는 NULL 값과 중복된 값을 포함한 그룹의 항목 개수를 반환합니다.

일반적으로, COUNT(컬럼명)을 사용하여 특정한 컬럼의 행 개수를 세는 것보다 COUNT(*)을 사용하여 옵티마이저가 행의 개수를 반환하는 최상의 방법을 선택하도록 해주는 것을 더 선호하는 방식이다.

 

[참고] NULL을 처리하는 방법 

use pubs 
go 

-- 돈 받고 파는 책을 출력하세요 

select * from titles where price is not null 

-- 비매품인 책을 출력하세요 

select * from titles where price is null 

-- 비매품책을 제외한 모든 책의 평균가격? 

select avg(price) from titles 

-- 비매품책을 0원으로 두고 계산한 평균가격? 

select avg(isnull(price,0)) from titles 

 

[유용한 관용구]

칼럼의 중복 행의 수를 찾아봅시다 

use pubs 
go 

-- 중복 칼럼이 각각 몇 개 항목인지를 찾아보자 
-- type별로 몇 개의 책이 있을까? 

select type,count(*) as [중복 행의 수] 
from titles group by type having count(*)> 1

 

수칙4.커서 및 임시 테이블의 내용을 최대한 자제하는가?

 

결론부터 말씀 드리자면 커서보다는 임시테이블이 임시테이블보다는 테이블 변수를 사용하는 것이 성능에 보탬이 됩니다. 단 SQL2000에서만 테이블 변수가 가능합니다.

커서는 내부적으로 임시 테이블을 사용하기 때문에 임시테이블을 쓴다고 부하가 더 발생하진 않습니다. 오히려 커서의 부가적 기능 때문에 서버 자원을 더 낭비하게 됩니다. (커서로 할 수 있는 건 임시테이블이나 테이블 변수로도 모두 처리가 가능합니다.)

 

따라 하기 - 다음은 테이블 변수를 사용하여 기존 커서를 대체하는 것을 구현했습니다. 

1.훌륭하게 커서를 대신하는 문장입니다.

CTRL + K로 확인하면 테이블 변수로 사용할 경우 실제 테이블에 잠금을 전혀 걸지않는 것을 알 수 있습니다(중요) 그와 반대로 커서를 사용할 경우 프로시저 시작부터 끝까지 지속적으로 사용 부분을 계속해서 잠그고 있어서 다른 작업들이 대기해야 되는 문제가 생깁니다.

 

수칙5.VIEW의 총사용을 줄였는가?

 

VIEW는 보안과 편리성에 관련된 이슈를 다루는 데 있어 최고입니다, 
그러나, 일반적으로 보안상에 이슈를 제외한 경우에는 불필요한 부하가 가중될 수 있고 많은 경우에 더 불필요한 데이터를 반환합니다 예를 들면 VIEW에서 10개를 가져오고 거기에 WHERE 조건을 붙여서 7개만 가져오는 경우가 그렇습니다.

 

select lastname,firstname from employees VS select * from EmployeesView 중간 단계가 있는 쪽이 효율이 떨어집니다.

 

수칙6.저장 프로시저를 사용하는가?

 

저장 프로시저는 복잡한 SQL문을 단순화 시켜주고, 보안 문제를 해결해주며 더 나아가 빠른 성능에 매개변수,출력매개변수,리턴 값을 사용할 수 있습니다.

 

저장 프로시저의 역할 7가지 

1. 데이터 무결성의 시행 
2. 복잡한 비즈니스 규칙과 제약의 강화 
3. 캡슐형 설계 
4. 유지보수 
5. 네트워크 트래픽 감소(오고 가는 긴SQL 구문을 축소) 
6. 보다 빠른 실행(컴파일을 하지 않습니다) 
7. 보안강화

 

저장 프로시저의 생성과 반복사용 시 발생하는 일

 

제작

1. 구문분석  2. 표준화  3. 보안 점검(프로시저 생성권한)  4. 저장(syscomments)

첫 번째 실행 시

1. 보안 점검(프로시저 실행 권한)  2. 최적화  3. 컴파일과 이에 따른 실행계획을 캐쉬에 저장  4. 실행

반복해서 실행 시

1. 캐쉬에 실행 계획 있을 때는 그대로 실행  2. 캐쉬에 실행 계획이 없을 때는 첫 번째로 저장 프로시저 실행하는 것과 동일

 

쿼리는 한번만 실행할 때는 일반 SQL이 훨씬 간단합니다. 그러나 반복적으로 실행되면 저장 프로시저가 월등히 빠르고 편리합니다.

 

수칙7.저장 프로시저를 적절하게 리컴 파일 하는가?

 

데이터가 변화하면(인덱스를 추가하거나 인덱스된 열의 데이터를 변경하는 등의 작업 수행 시) 그에 걸맞게 실행계획도 변화해 갑니다. 그에 대처하기 위해서 다음과 같은 리컴파일 방법를 제공합니다. 

저장 프로시저 리컴 파일 모드에는 다음의 3가지가 있습니다.

 

CREATE PROCEDURE [WITH RECOMPILE] 
EXECUTE [WITH RECOMPILE] 
sp_recompile

 

CREATE PROCEDURE [WITH RECOMPILE]

는 SQL SERVER가 이 저장 프로시저의 계획을 캐시하지 않기 때문에 이 저장 프로시저가 실행 할 때 마다 다시 컴파일 됩니다(실행 속도가 느려짐).

 

EXECUTE [WITH RECOMPILE]

는 지금 이순간만 리컴파일 하고 다시 저장 프로시저 실행하면 예전 실행 계획대로 작동하는 것입니다. 제공하는 매개 변수가 불규칙하거나 저장 프로시저를 만든 다음 데이터가 많이 변경되었을 경우 이 옵션을 사용합니다.

 

sp_recompile

는 저장 프로시저가 다음에 실행될 때 첫 실행처럼 컴파일되고 실행되도록 하는 것입니다.

 

[문서화되지 않은 DBCC 명령어]

-- pubs 데이터베이스의 모든 저장 프로시저를 재컴파일 해보자 

select db_id('pubs') 

dbcc flushprocindb(5) 

-- 모든 인덱스를 재구축한다 
-- 관리자가 사용할 경우 엄청난 시간이 소요될 수 있습니다 

dbcc dbreindexall('pubs')

 

수칙8. 저장 프로시저 작명 시 SP외의 접두어를 사용한다.

 

시스템 저장 프로시저는 master 데이터베이스내에서 sp_라는 접두어로 시작하는 것이 좋으며 모든 데이터베이스에서 실행될 수 있습니다. 각 사용자 데이터베이스에서는 다른 접두어를 사용하는 것이 보기에도 좋고 알아보기에도 수월합니다.

또한 시스템 저장 프로시저는 어느 데이터베이스에서 수행하건 해당 데이터베이스의 내용을 참조합니다.

 

따라하기 
일반sp_ 저장 프로시저를 시스템sp_ 저장 프로시저로 만들어 봅니다. 

1. 사용자 정의 저장 프로시저는 master데이터베이스에 존재하더라도 master내용만 참고합니다.

2.그러나 다음과 같이 시스템 저장 프로시저화 한다면

3.부연하자면 모든 데이터베이스에서 사용하는 프로시저의 경우 sp_로 시작하게 작성한후 sp_MS_marksystemobject로 시스템 프로시저화 작업을 하는게 필요합니다. 이 내용은 엄격하게 구분되서 실행되는 것이 혼란을 줄일 수 있습니다.

 

수칙9. 모든 개체의 소유자는 DBO 이다

 

소유자가 다르면 복잡한 소유권 체인문제가 발생합니다.

lucia가 테이블의 소유자 입니다. lucia는 뷰를 만들었는데 maria에게 뷰를 볼 수 있게 했습니다. maria는 이를 Pierre가 볼 수 있게 했는데 Pierre는 Maria가 만든 뷰를 select권한을 받았음에도 불구하고 실행이 안됩니다. 이는 소유권 체인이 중간에 분실 됐기 때문입니다. 불필요한 이런 시스템은 시스템의 성능저하를 가져다 줍니다. 모든 소유자는 dbo로 통일하는 것을 권장합니다.

 

따라 하기 - 소유자를 dbo로 바꿔보자 

1.소유자를 dbo로 바꿀 때는 다음의 저장 프로시저를 사용하면 됩니다.

2.추가로 시스템테이블을 업데이트하는 방법을 통해 데이터베이스 차원에서 소유자를 바꾸는 방법도 있으며 커서를 사용하는 방법도 존재합니다.

 

[참고] 소유자가 dbo가 아닌 객체를 출력해봅시다. 

select name from sysobjects 
where uid <> user_id('dbo')

 

수칙10. 데드락이 발생하는 부분을 라이브락 형태로 변경했는가?

 

데드락이란 라이브락과 반대되는 개념입니다. 둘 이상의 트랜잭션이 서로가 실행해야 될 내용을 이미 잠그고 있어 마치 교차로에서 서로 엉켜 꼼짝할 수 없는 상황을 의미 합니다. 이를 해결하기 위한 SQL서버의 노력은 한쪽을 일방적으로 취소 시키는 것인데 이는 시스템의 성능저하로 나타납니다. 이를 해결하기 위한 가장 좋은 방법은 일방통행 방식으로 변경하는 것입니다. 이것이 라이브락 입니다.

 

따라 하기 

1.우선 준비를 위해 테이블을 만들고 데이터를 넣습니다.

2.창을 두 개 열어서 동시에 실행합니다. CTRL+TAB으로 잽싸게 창을 바꿔서 실행해 봅니다.

3.위의 데드락의 가장 바른 해결방법은 순차적인 라이브락 형태로 변경하는 것입니다.

수칙11. SET NOCOUNT ON을 사용하는가?

 

불필요한 메시지가 네트워크 트래픽을 낭비하고 있습니다. 특히'몇 개 행이 적용 되었습니다' 같은 메시지가 그런 대표적인 예입니다.

 

따라 하기 

1. set nocount on을 실행하고 쿼리를 실행합니다.

프로시저를 작성할 때도 set nocount on과 같은 환경설정은 먼저 실행해 두고 프로시저를 작성하면 환경이 저장된 채로 프로시저가 제작되므로 편리합니다.

 

수칙12.실무 사례: 저장 프로시저 관리 방법

 

저장 프로시저 관리방법

--------------------------- 
-- 객체이름 : 
-- 파라미터 : 
-- 제작자 : 
-- 이메일 : 
-- 버젼 : 
-- 제작일 : 
-- 변경일 : 
-- 그외 : 
---------------------------

use 데이터베이스명
-- 저장 프로시저는 use 데이터베이스명 문과 같이 써두어야 어디 소속인지 명확히 파악이 가능합니다. 
go 

-- 소스 

create proc dbo.저장 프로시저명 
-- 소유자가 명확하게 dbo로 지정 되 있어야 성능 향상이 이뤄집니다. 컴파일 잠금 시간 대폭 감소 
as 
begin 
-- 가장 바깥쪽의 begin end 및 불필요한 begin end문은 과감히 생략합니다.(소스만 길어짐) 

end 

-- 실행예제 

exec 데이터베이스명.dbo.저장 프로시저명 
-- 데이터베이스 이름까지 명시해야 오브젝트 참조에서 발생할 수 있는 불 명확성을 줄여줌으로 바람직합니다