나도 한때 개발자 일때가 있었다.
개발을 하면서 나름 오랬동안 MS-SQL 을 사용해 왔다. 그로 인해 나름 데이터 베이스에
대해서 자신감 나도 나름대로 DB 좀 한다^^라는 생각을 가지고 있었다. 하지만 정원혁 강사님의
강의를 들은후 자심감이 아니라 자만감 이었구나 느끼게 되었다. 현재 많은 개발자들이
SQL 의 기본만 배우고 개발에 뛰어 들고 있다. 그래서 DBA 가 존재 하는지도 모른다.
개발자 들이라면 한번 읽어 봤으면 하는 내용이 웹서핑중에 있어 옮겨 보았다.
1. DB 생성시 주의사항
1) DB 명칭은 해당 서비스를 파악할 수 있도록 명명한다.
2. USER 생성시 주의사항
1) USER ID는 유관 서비스를 파악할 수 있도록 명명한다.
2) Password는 운용팀 DBA의 생성규칙을 따른다.
3. 테이블 생성시 주의사항
1) Table Column 길이의 합이 8K를 넘지 않도록 할 것
2) Table 및 Column 이름은 일관성 있게 줄 것 (예: TB_, str_)
3) PK / FK Column은 고정길이 형식을 사용할 것 (예: CHAR Type)
4) Trigger의 사용을 자제
5) Table의 소유자는 항상 ‘DBO’가 되도록 한다
4. 인덱스 생성시 주의사항
1) Where 절에서 많이 사용하는 경우 생성
2) Covered Index인 경우 선택도가 좋은 조건(10%이하) 부터 순서대로 생성
3) 구간별 선택이 많은 컬럼인 경우 클러스터 인덱스 추전
4) PK정의시 넌클러스터 인덱스로 정의하되 3)항 조건이 만족하면 클러스터 인덱스로 생성
5. 쿼리
1) SELECT 시 주의 사항들
- 꼭 필요한 컬럼만 선택하여 정의되었는지
- Count(컬럼명)대신 Count(*)를 사용하는가
- 각 종 연산문(+,-,*,/) 자제요망
- 원하는 결과 값을 찾는 적절한 WHERE절을 사용 하는가?
- WHERE절에서 인덱스를 사용 할 수 있게 했는가?
- 단순 SELECT면 with NOLOCK옵션을 주었는가?
(예: SELECT au_lname FROM authors WITH (NOLOCK))
2) WHERE 절 작성시 주의 사항들
- 테이블 컬럼의 DATA가 가공되는 함수 및 연산자 사용금지
(EX: LIKE ‘L%’ 이것을 사용하지 않고 LIKE ‘%L%’를 사용하는 것은 아닌가?)
(SELECT * FROM ST WHERE Qty + 1 > 20
권장
SELECT * FROM ST WHERE Qty > 20 -1 )
- 조건식의 순서는 선택도가 좋은 컬럼부터 기술한다.
3) Index Seek을 하는지 반드시 점검
- Index scan, table scan은 서버 및 서비스에 악영향을 미침
6. 프로시져 생성시 주의사항
항상 주석처리할 것 (최초만든자,수정일,수정자,사용예등..)_차후 SP 수정시에도 주석수정
7. 커서 및 임시 테이블의 내용을 최대한 자제 하는가?
8. view의 총 사용을 줄였는가?
9. 저장프로시져를 사용하는가?
- 저장프로시져를 적절하게 리컴파일 하면서 사용하는가?
- 프로시저의명칭에 적당한 접두어 규칙을 사용하는가?
(EX:사용자 프로시져는 up_로 시작하는가? 테이블은 tb_로 시작하는가 등)
- 프로시져나 뷰등을 생성 할 때 소유자를 DBO로 지정해 주고 있는가?
- 모든 소유자는 dbo로 통일하는 것을 권장합니다.(소유권 체인 문제 발생 예방)
- SP를 만들 때도 항상 CREATE PROC DBO.SP이름 형식으로 만드는가?
10. 잠금관련 권고사항
- 트랜잭션은 가능한 짧게 만들었는가?
- 데드락을 피하기 위해 같은 방향으로 트랜잭션을 진행하는가?
- 잠금수준을 내려서 불필요한 잠금을 없애고 있는가?.(read uncommitted)
- 트리거를 사용하지 않습니다.
- 대규모 데이터 변경시에만 커서를 사용합니다.
12. SET NOCOUNT ON 을 사용하는가?
- 프로시저를 작성할 때 SET NOCOUNT ON과 같은 환경설정은 먼저 실행해 두고
프로시저를 작성하는가
13. 임시 테이블 대신 테이블 변수 사용하는가?
14. ANSI SQL문법을 사용하고 있는가?
- 다음과 같이 T-SQL로 구성 된 것을 ANSI-SQL구문으로 변경
SELECT t.title_id, qty, title
FROM titles t , sales s
WHERE t.title_id *=s.title_id
이 구문은 T-SQL구문을 사용 해서 표현한 방식인데 추후 변경 가능 성이나 가독성을 위해
서 ANSI-SQL문법을 사용 할 것을 권장
SELECT t.title_id, qty, title
FROM titles t LEFT OUTER JOIN sales s
ON t.title_id = s.title_id
15. 만든 구문을 SET statistics time, SET statistics IO를 ON으로 하고 각 쿼리들이
얼마의 시간이 소용되는지 체크 해 보았는가?
16. 쿼리가 인덱스를 사용하고 있는지 확인 하였는가?
- 많이 사용 하는 쿼리가 밑의 방식대로 확인을 했는데 Index Scan라고 인덱스를 생성 해야
한다.
-> 확인 방법 : 쿼리를 작성 후 CTRL + L를 클릭 하여 실행 계획을 인덱스를 사용 하고
있는지를 확인 한다. Clustered Index Seek이나 index Seek은 인덱스를 정상적으로
사용 하는 것이지만 Scan으로 되어 있다면 인덱스를 사용 하지 않는 것이다.
17. 사용량이 많은 쿼리에서 사용하는 컬럼에 인덱스가 없다면 신청 하였는가?
18. 현재 프로그램에 하드코딩된 쿼리를 SP로 만들어 사용 할 수는 없는 것들인가?
- 생각 중? à 사용 할 수 없다면 그 이유는 무엇인가? -> DBA와 협의
19. 동적 쿼리를 사용시 EXEC (@str)이 아니고 EXEC sp_executesql을
사용 하고 있는가?
- EXEC (@str)이 것은 한번 사용한 실행계획을 계속 사용 할 수가 없고 매번 다시 만들게
되므로 성능 저하의 원인이 된다. 그러므로 실행 계획을 재 사용 할 수 있는
EXEC sp_executesql을 사용 할 것을 권장 한다.
20. 변수 사용해서 값을 비교 시 WHERE 절에서 문자열 함수를 사용하고 있는 것은
아닌가?
(예: convert(varchar(10),date1,120) = @층, Left(date1,1,8))
21. 다른 서버에서 정보를 가져 올 때 연결된 서버를 이용할 때 4Part name방식 말고
OPENQUERY를 사용하고 있는가?
(예 : SELECT * FROM OPENDATASOURCE('SQLOLEDB','Data Source=192.168.168.1;User ID=sa;Password=password').PUBS.DBO.titles )
22. 임시 테이블 보다는 인라인 뷰를 사용하자.
23. 서브쿼리 사용시 주의 사항들
- 서브쿼리보다는 조인을 사용하자
24. cursor사용시 주의 사항들
- 클라이언트 측 커서를 사용하자 (커서를 자제 해야 하지만 사용해야 한다면)
: 서버의 커서를 사용할 때에는 가능한 작은 결과 셋을 가져오도록 한다
: 커서를 다 사용한 후에는 그냥 닫지만 말고(close) 말고 반드시 해제(deallocate) 시켜
야 한다.
25. select 성능향상지침
- Distinct, Order by 를 사용 할 때는 주의 해야 한다.
- 중복 값을 제거하지 않아도 된다면 UNION대신 UNION ALL을 사용하자.
26. insert 성능향상지침
- SELECT … INTO 는 각 로우들의 입력 과정이 로그로 기록 되지 않으므로 로그로 기록
되는 INSERT보다는 가끔 몇 배나 빨리 처리 된다. 그렇지만 이 명령은 시스템 테이블에
락을 걸어 둘 수 있으므로 주의 하여 사용해야 한다.
27. DATA Type의 정의
- 컬럼에 필요한 데이터를 저장할 수 있는 데이터 타입 가운데 항상 가장 작은 데이터 타입
을 선택한다.
- 컬럼에 저장되는 텍스트 데이터의 길이가 매우 가변적이라면 CHAR 대신 VARCHAR 데이
터 타입을 사용하는 것이 좋다.
- 16비트 문자(유니코드) 데이터를 저장할 계획이 아니라면 NVARCHAR 또는 NCHAR 데이터
타입을 사용하지 않는 것이 좋다.
- 긴 문자열을 저장할 때, 문자열의 길이가 8000자 이하라면 TEXT 대신 VARCHAR 데이터 타입을 사용하는 편이 좋다.
- 숫자만을 저장하는 컬럼은 VARCHAR 또는 CHAR 대신 INTEGER와 같은 숫자 데이터 타입을 사용하는 것이 좋다.
