DBMS 2
DA, SQL, DB보안 등 실무자를 위한 위한 DB기술 바이블!
스티브 맥코넬이 이런 말을 했습니다. 다음의 표를 참조합시다. 주요 단축키 사용 안내 입니다. Select 하는 내용도 필요한 항목만을 가지고 오도록 되어 있어서 리소스가 전혀 낭비되지 않고 있습니다. 필요한 칼럼을 가져오기는 하지만 불필요한 전체 행(Row)들을 가져오고 있습니다. 불필요한 칼럼정보, 행(Row) 데이터를 가져오고 있습니다. 따라 하기 - 3개의 쿼리를 한번에 실행하기 인덱스란 데이터를 빨리 찾기 위해서 사용됩니다. 인덱스가 없다면 특정데이터를 찾기 위해서 모든 데이터페이지를 검색(Table Scan)해야만 합니다 그에 비해 인덱스가 존재하고 그 인덱스가 사용되는 것이 효과적이라면 SQL서버는 해당 인덱스 페이지를 사용하여 쉽게 데이터를 가져올 수 있는데 이를 인덱스 검색(Index Seek)이라 합니다. 옵티마이저가 쓸모 있게 변환하는 것은 CTRL+K 실행 계획 상부 표시에서 관찰할 수 있습니다. 따라 하기 - 3개의 쿼리를 한번에 실행하기 COUNT(*) 와 COUNT(컬럼명)의 차이는 중요합니다. COUNT 하는 해당 테이블 컬럼에 NULL 값을 포함하고 있다면 이 두 예제는 서로 다른 결과를 반환합니다. COUNT(컬럼명)은 그룹에 포함된 각 행을 평가하여 NULL이 아닌 값의 개수를 반환합니다. COUNT(*)는 NULL 값과 중복된 값을 포함한 그룹의 항목 개수를 반환합니다. 결론부터 말씀 드리자면 커서보다는 임시테이블이 임시테이블보다는 테이블 변수를 사용하는 것이 성능에 보탬이 됩니다. 단 SQL2000에서만 테이블 변수가 가능합니다. 따라 하기 - 다음은 테이블 변수를 사용하여 기존 커서를 대체하는 것을 구현했습니다 VIEW는 보안과 편리성에 관련된 이슈를 다루는 데 있어 최고입니다, select lastname,firstname from employees VS select * from EmployeesView 중간 단계가 있는 쪽이 효율이 떨어집니다. 저장 프로시저는 복잡한 SQL문을 단순화 시켜주고, 보안 문제를 해결해주며 더 나아가 빠른 성능에 매개변수,출력매개변수,리턴 값을 사용할 수 있습니다. 저장 프로시저의 역할 7가지 쿼리는 한번만 실행할 때는 일반 SQL이 훨씬 간단합니다. 그러나 반복적으로 실행되면 저장 프로시저가 월등히 빠르고 편리합니다. 데이터가 변화하면(인덱스를 추가하거나 인덱스된 열의 데이터를 변경하는 등의 작업 수행 시) 그에 걸맞게 실행계획도 변화해 갑니다. 그에 대처하기 위해서 다음과 같은 리컴파일 방법를 제공합니다. 는 SQL SERVER가 이 저장 프로시저의 계획을 캐시하지 않기 때문에 이 저장 프로시저가 실행 할 때 마다 다시 컴파일 됩니다(실행 속도가 느려짐). 는 지금 이순간만 리컴파일 하고 다시 저장 프로시저 실행하면 예전 실행 계획대로 작동하는 것입니다. 제공하는 매개 변수가 불규칙하거나 저장 프로시저를 만든 다음 데이터가 많이 변경되었을 경우 이 옵션을 사용합니다. 는 저장 프로시저가 다음에 실행될 때 첫 실행처럼 컴파일되고 실행되도록 하는 것입니다. 시스템 저장 프로시저는 master 데이터베이스내에서 sp_라는 접두어로 시작하는 것이 좋으며 모든 데이터베이스에서 실행될 수 있습니다. 각 사용자 데이터베이스에서는 다른 접두어를 사용하는 것이 보기에도 좋고 알아보기에도 수월합니다. 따라 하기 일반sp_ 저장 프로시저를 시스템sp_ 저장 프로시저로 만들어 봅니다. 소유자가 다르면 복잡한 소유권 체인문제가 발생합니다. 따라 하기 - 소유자를 dbo로 바꿔보자 데드락이란 라이브락과 반대되는 개념입니다. 둘 이상의 트랜잭션이 서로가 실행해야 될 내용을 이미 잠그고 있어 마치 교차로에서 서로 엉켜 꼼짝할 수 없는 상황을 의미 합니다. 이를 해결하기 위한 SQL서버의 노력은 한쪽을 일방적으로 취??납니다. 이를 해결하기 위한 가장 좋은 방법은 일방통행 방식으로 변경하는 것입니다. 이것이 라이브락 입니다. 따라 하기 불필요한 메시지가 네트워크 트래픽을 낭비하고 있습니다. 특히'몇 개 행이 적용 되었습니다' 같은 메시지가 그런 대표적인 예입니다. 따라 하기 프로시저를 작성할 때?고 프로시저를 작성하면 환경이 저장된 채로 프로시저가 제작되므로 편리합니다.쿼리디자인
쿼리디자인
쿼리 디자인
번호
수칙
체크
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
선택 내용을 대문자로
선택 내용을 대문자로
V
붙여넣기
W
X
자르기
Y
다시하기
Z
취소
F1
도움말
선택 내용을 도움말로 보기
F8
객체 브라우저보이기감추기
수칙 1. SELECT는 필요한 결과값만을 요구하는가?
select title , price from titles
where title_id = 'BU1032'
select title , price from titles
select * from titles
수칙 2. 적절한 WHERE 조건을 사용하는가?
그러나 이렇게 인덱스가 있더라도 이를 사용 불가능하게 하는 나쁜 쿼리가 있으니 이는 검색조건에서 불필요하게 칼럼이 변형된 경우입니다. 다음의 여러 나쁜 예를 좋은 예와 비교해 봅시다.
SARG(Search Argument)란 쿼리가 반환하는 결과를 제한하기 위하여 옵티마이저가 인덱스와 결합해서 사용할 수 있는 쿼리 내의 조건절을 말하는데 다음의 형태를 가집니다.
컬럼 연산자/변수
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
[참고]?쿼리 계획 은 다음의 몇 가지 단계로 이뤄집니다.
것 같은 작업을 수행합니다
계획을 실행하도록 내놓는다
수칙 3. COUNT(컬럼명) 대신 COUNT(*)을 사용하는가?
일반적으로, 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. 커서 및 임시 테이블의 내용을 최대한 자제하는가?
커 서는 내부적으로 임시 테이블을 사용하기 때문에 임시테이블을 쓴다고 부하가 더 발생하진 않습니다. 오히려 커서의 부가적 기능 때문에 서버 자원을 더 낭비하게 됩니다. (커서로 할 수 있는 건 임시테이블이나 테이블 변수로도 모두 처리가 가능합니다.)
CTRL + K로 확인하면 테이블 변수로 사용할 경우 실제 테이블에 잠금을 전혀 걸지않는 것을 알 수 있습니다(중요) 그와 반대로 커서를 사용할 경우 프로시저 시작부터 끝까지 지속적으로 사용 부분을 계속해서 잠그고 있어서 다른 작업들이 대기해야 되는 문제가 생깁니다.수칙 5. VIEW의 총사용을 줄였는가?
그러나, 일반적으로 보안상에 이슈를 제외한 경우에는 불필요한 부하가 가중될 수 있고 많은 경우에 더 불필요한 데이터를 반환합니다 예를 들면 VIEW에서 10개를 가져오고 거기에 WHERE 조건을 붙여서 7개만 가져오는 경우가 그렇습니다.
수칙 6. 저장 프로시저를 사용하는가?
저장 프로시저의 생성과 반복사용 시 발생하는 일
제작
첫 번째 실행 시
반복해서 실행 시
수칙 7. 저장 프로시저를 적절하게 리컴 파일 하는가?
저장 프로시저 리컴 파일 모드에는 다음의 3가지가 있습니다.
CREATE PROCEDURE [WITH RECOMPILE]
EXECUTE [WITH RECOMPILE]
sp_recompile CREATE PROCEDURE [WITH RECOMPILE]
CREATE PROCEDURE [WITH RECOMPILE]
EXECUTE [WITH RECOMPILE]
sp_recompile
[문서화되지 않은 DBCC 명령어]
-- pubs 데이터베이스의 모든 저장 프로시저를 재컴파일 해보자
select db_id('pubs')
dbcc flushprocindb(5)
-- 모든 인덱스를 재구축한다
-- 관리자가 사용할 경우 엄청난 시간이 소요될 수 있습니다
dbcc dbreindexall('pubs')
수칙 8. 저장 프로시저 작명 시 SP외의 접두어를 사용한다
또한 시스템 저장 프로시저는 어느 데이터베이스에서 수행하건 해당 데이터베이스의 내용을 참조합니다.
수칙 9. 모든 개체의 소유자는 DBO 이다
수칙 10. 데드락이 발생하는 부분을 라이브락 형태로 변경했는가?
수칙 11. SET NOCOUNT ON을 사용하는가?
수칙 12. 실무 사례: 저장 프로시저 관리 방법
저장 프로시저 관리방법
---------------------------
-- 객체이름 :
-- 파라미터 :
-- 제작자 :
-- 이메일 :
-- 버젼 :
-- 제작일 :
-- 변경일 :
-- 그외 :
---------------------------
use 데이터베이스명
-- 저장 프로시저는 use 데이터베이스명 문과 같이 써두어야 어디 소속인지 명확히 파악이 가능합니다.
go
-- 소스
create proc dbo.저장 프로시저명
-- 소유자가 명확하게 dbo로 지정 되 있어야 성능 향상이 이뤄집니다. 컴파일 잠금 시간 대폭 감소
as
begin
-- 가장 바깥쪽의 begin end 및 불필요한 begin end문은 과감히 생략합니다.(소스만 길어짐)
end
-- 실행예제
exec 데이터베이스명.dbo.저장 프로시저명
-- 데이터베이스 이름까지 명시해야 오브젝트 참조에서 발생할 수 있는 불 명확성을 줄여줌으로 바람직합니다.