트러블 슈팅 1
어떤 상황이냐면, 서로 외래키가 서로의 주키를 참조하고 있는 상황
- 어떤 곳에도 INSERT 문을 넣으면 제약과 충돌한다.
- 어떻게 해결?
처음에 NULL값을 허용한다.
별로 권장하지 않다고 한다.
주의해야 한다고 한다.
아니 근데 어떻게 주키가 NULL 이 가능함..
걍 정규화를 잘못한거
NULL 허용
애초에 처음부터 NULL을 허용하도록 변경한다
..? 이게 맞나
데이터 모델 재설계
애초에 이렇게 양방향 종속성이 생기는 것이 안 좋다.
정규화는 연습이 피료해....
내가 미쳤나봄
애초에 말이 안되게 생김. 테이블 ERD 짤때도 정규화가 필요한 것 같다.
(졸면서 짜면 이런 실수 하는거)
데이터를 가장 빨리 찾는 방법은
- 계층적으로 찾는거다.
- 예를 들어서 김민수을 찾을때 서울에서 브루트 포스 하는 것보다 양천구에서 찾는 것
더 나아가서 목동에서 찾는게 더 빠르단 것. - 빠른 속도(성능)만을 위해서 만드는 건 아니다.
PRIMARY KEY , UNIQUE 제약이 설정되면 고유한 인덱스가 자동으로 만들어진다.
여기서 의도하는 것은 빠른 성능이 아니라 중복되는 데이터가 입력되지 못하게 강제하는 것이다.
데이터가 많아야 인덱스가 의미가 있다.
데이터가 별로 없으면 애초에 인덱스를 만드는 것이 소모적인 행동일 수 있다.
인덱스
- root level
- intermediate level
왠만하면 추가될 일은 별로 없다.
한 intermediate level이 추가되려면 엄청난 데이터 추가가 필요하다. - leaf level
firstpage - 테이블의 존재형태
뭔가 헷깔리는게, 비 클러스터형 테이블이 힙형 테이블인가를 따져보면 아니라고 한다.
힙형 테이블은 클러스터형 인덱스가 없는 테이블이며, 비클러스터형 인덱스는 테이블의 데이터 정렬 방식과는 별개로 존재하는 검색 최적화 도구이다.
힙형 테이블은 비클러스터형 인덱스를 가질 수 있지만, 그 자체로 비클러스터형 인덱스는 아니다.
힙형 테이블을 기준열로 정렬하면 클러스터형 테이블이 된다.
힙
INSERT 친화적, SELECT에겐 쥐약
테이블 스캔
데이터가 특별한 순서없이 저장된다.
새로운 데이터는 중간의 여유 공간에 저장된다.
공간이 부족하면 맨 뒤에 저장한다.
기본적으로 MSSQL에서 테이블을 생성하면 처음에는 힙형 테이블로 생성된다.
클러스터
클러스터형
클러스터 형 인덱스는 테이블이다.
힙형 테이블을 기준열로 정렬하면 클러스터형 테이블이 된다.
클러스터형 테이블에서 데이터는 클러스터형 인덱스에 의해 정렬되어 저장된다.
클러스터형 인덱스는 테이블의 데이터를 물리적으로 순서대로 저장한다.
이 인덱스는 테이블의 데이터 행 자체를 포함한다.
클러스터형 인덱스는 테이블 당 하나만 생성할 수 있다.
이 인덱스에 정의된 열(들)을 기준으로 저장된다.
따라서 힙형 테이블에 클러스터형 인덱스를 추가하면, MSQL은 데이터를 인덱스의 키 값에 따라 물리적으로 재정렬 한다.
이후에 테이블은 클러스터형 테이블이 되며, 클러스터형 인덱스의 주요 이점은 데이터 검색과 정렬이 보다 효율적이고
특히 범위 검색이나 순차 접근이 많은 경우에 유리하다.
클러스터형 인덱스 스캔
무조건적으로 나쁘다고 할 수는 없으나 성능저하의 원인이 되기도 한다.
원하는 열로 정렬된 클러스터형 인덱스에서 찾으려고 하는데 내가 찾으려고 하는 것이 다른 열을 기준으로 된 것이라면 테이블 전체를 스캔할 수 밖에 없다.
이것을 클러스터형 인덱스 스캔이라고 한다.
데이터를 전부 가져오는 제일 빠른 방법이 테이블 스캔이기도 하다.
비클러스터형
데이터를 정렬하지 않고 테이블 스캔을 피하려면?
-
INDEX SEEK (비클러스터형 인덱스)
root – intermediate – leaf 구조로 찾는건데 어떤식으로 찾느냐면
맨 위의 루트 레벨에서 다음과 같은 식으로 저장된다. ( 맨 끝 페이지 )
(~~~, 200 )
( 26 , 201 )
그렇다면 여기서 알 수 있는 것이 15는 26보다 작으니까 200번 페이지에 있음을 확인할 수 있다.
이후에 200번 페이지로 가게 되면, 고객번호 (기준열)와 함께 RID 가 저장되어있는데
RID는 다음과 같이 생겼다. ~~ : 페이지번호 : 몇번째 줄 ?
그럼 그 다음엔 그거 보고 그 페이지 가서 찾으면 된다..!
이걸 index seek이라고 한다. -
RID LOOKUP
일단 데이터 페이지랑 인덱스 페이지를 구분해서 생각하자.
요약하면, 비클러스터형 인덱스에서 힙으로 데이터 행을 찾아가는 과정이다. -
PAGE SPLIT
데이터가 추가되었는데 리프 페이지가 꽉 차 있다면 넣을 수가 없다.
때문에, 절반 정도를 새로운 페이지를 만들어서 그 페이지로 넘기고
그 다음에 새로운 데이터의 인덱스를 추가한다.
힙은 페이지 분할이 일어날 일이 없다.
그냥 빈 곳에 쑤셔 넣고, 빈 곳이 없으면 뒤에 낑겨 넣는다. -
맨 위의 루트레벨의 인덱스에서는
어떤 리프페이지의 첫 데이터가 기준열의 몇번째 데이터부터 시작하는지를 다룬다. -
복잡하지?
그래서 비 클러스터형 인덱스에서는 SELECT문은 큰 효과를 볼 수 있지만, INSERT문은 인덱스에 등록하는 과정이 꽤 복잡하다. -
클러스터형 인덱스의 리프페이지는 데이터페이지다.
비클러스터형 인덱스는 리프페이지와 데이터페이지가 따로 있었으나
클러스터형은 리프페이지와 데이터페이지가 같다. 실제로 리프페이지 안에 물리적으로 정렬되어서 저장되어있다.
내용 정리해보자
-
클러스터형 인덱스와 비클러스터 인덱스를 좀 팠는데,
-
크게는
힙
그냥 막 섞인..
중간에 빈 곳이 있으면 낑겨서 넣는데 빈 곳이 없다면 페이지를 새로 생성해서 뒤에다가 낑겨서 넣는다.
클러스터형 인덱스
데이터페이지는 정렬되어있다.
정렬되어있어서 찾기는 좋은데, 어떤 페이지를 먼저보아야 할지를 모른다.
때문에 인덱스 키와 페이지 번호가 적혀있는 인덱스가 필요하다!
RID는 필요가 없다.
비클러스터형보다 단계가 하나가 없다보니까, 더 나은 것 같지만 insert 하기 힘든 것은 솔직히 비슷하다.
비클러스터형 인덱스
비클러스터형 인덱스 + 클러스터형 인덱스 -
클러스터형의 데이터페이지는 물리적으로 정렬된 건 아니다.
페이지의 슬롯인덱서가 논리적 순서를 매핑한다 -
클러스터형 + 비클러스터형
클러스터형 인덱스라도 결국에 인덱스테이블이 많아지게 되면, 그것또한 누가 어디에 있는지 알려줘야 한다.
(정렬해놓긴했지만.. 일단 어딘지 알아야 .. ㅇㅇ )
그래서 비클러스터형 인덱스를 위에다가 따로 구성한다 -
페이지 분할을 방지하려면
일단 힙에서는 페이지 분할이라는게 있을 수 없음
청소 안하는 사람이 책장정리할 필요가 없음. 그냥 던져놓으면 됌
페이지 분할을 방지하기 위해서 원인을 원천 차단한다!
페이지의 전체를 다 채우지 않고 50%만 채워놓는다고 쳐보자
그러면 페이지가 2배로 더 필요하다.
어떻게 보면 SELECT 문이 읽어야할 페이지가 늘어나는 거긴한데,
그리고 페이지 분할이 안일어나잖아..ㅇㅇ
밀당인거임.. -
주키(PRIMARY KEY)는 기본적으로 클러스터형 인덱스로 생성된다.
이후 SQL SERVER는 해당 열을 기준으로 데이터를 물리적으로 정렬하는 클러스터형 인덱스를 자동으로 생성하고
그러면 검색 속도가 빨라짐. -
주키를 비클러스터형으로도 만들 수 있다.
-
뷰
데이터베이스 복잡성을 숨길 수 있음
성능향상
인덱스된 뷰를 만들지 않는 이상은 성능상 이점이 없음
보통은 단지 권한을 효과적으로 관리하고
원하는 데이터에 쉽게 접근하기 위해서 씀
필요한 데이터만 보여주니, 정보유출 위험을 줄일 수 있다. -
뷰를 만들때 원칙이 몇가지 있다.
32중 이상으로 중첩은 불가능
열은 1024개부터 불가능
COMPUTE, COMPUTE BY, COMPUTE INTO는 사용할 수 없음
TOP 없이 ORDER BY를 사용할 수 없음
뷰 임시테이블정의 하나 이상의 테이블에 대한 쿼리의 결과로 생성되는 가상 테이블이다.
실제로 데이터를 저장하지 않고, 정의된 쿼리에 따라서 데이터를 동적으로 표시한다. 세션 또는 트랜잭션 동안에만 존재하는 테이블이다.
실제로 데이터를 저장하며, 작업 중간 결과를 저장하는데 사용된다.
저장 데이터베이스 내에 정의가 저장되며,
뷰를 참조할 때마다 기본 테이블의 데이터를 쿼리한다. 메모리나 디스크에 데이터를 실제로 저장한다.
데이터는 세션이나 트랜잭션이 종료될 때까지 유지된다.
용도 데이터의 재사용성과 보안, 복잡한 쿼리의 간소화를 위해서 사용된다.
또한 사용자에게 특정 데이터만 보여주고자 할 때 유용하다. 복잡한 처리를 위한 중간단계
대량의 데이터를 임시로 처리하거나, 변환하는 경우
반복적인 데이터베이스 작업을 최적화하기위해 사용된다.
수명 사용자가 삭제하거나 데이터베이스를 삭제할 때까지 데이터베이스 세션이나 트랜잭션이 종료되면 자동으로 사라진다.
-
뷰 관리
ALTER VIEW 문으로 뷰에 포함된 SELECT 문을 변경할 수 있다.
뷰를 DROP문으로 제거한 다음 CREATE VIEW로 다시 만들면, 같은 결과가 아니다.
DROP VIEW 문으로 뷰를 제거하면 뷰에 대한 데이터베이스 사용자 권한도 같이 제거된다.
VIEW문으로 뷰를 다시 만들어도 제거된 권한은 복원되지 않는다.
그러니까 꼭 뷰 변경은 DROP하고 다시 만들지 말고
ALTER VIEW를 쓰자. -
뷰
유효기간
뷰는 특별한 조건이나 시간 제한 없이 유효하다.
DROP VIEW 문으로 삭제 가능하나 만약에 그게 변경을 하려는 경우라면 추천하지 않는다.
권한까지 같이 삭제되기 때문
뷰의 기반이 되는 테이블이 변경되면
오류가 발생할 수 있고, 더이상 유효하지 않을 수 있다.
뷰의 권한 변경
뷰에 대한 액세스 권한이 변경될 경우에 뷰 권한은 여전히 존재하지만
특정 사용자가 뷰를 사용할 수 없게 될 수 있다. -
프로시저
리스트 몇개를 외워야 할듯?
sp_helptext : 구문확인 -
구문 암호화
WITH ENCRYPTION
근데 암호화는 왠만하면 하지말라..
하게 된다면 구문을 따로 저장해야 한다.
나중에 뷰 수정하려면..
구문을 나중에 따로 볼 수 없으 -
뷰가 참조하는 개체를 보호
WITH SCHEMABINDING
주로 뷰나 사용자 정의함수의 정의에 사용된다.
해당 객체가 참조하는 테이블이나 뷰의 스키마가 변경되는 것을 방지한다.
데이터 무결성을 유지하는 데에 도움이 된다.
성능 최적화
WITH SCHEMABINDING을 사용하여 생성된 뷰는 인덱싱될 수 있어서, 성능이 향상될 수 있다.
이것은 뷰가 더 효율적인 실행 계획을 가질 수 있도록 도와준다.
변경제한
WITH SCHEMABINDING을 사용한 객체는 참조하는 테이블이나 다른 뷰가 변경되지 않는 한 수정이 제한된다.
스키마 변경 시 실수로 인한 오류를 방지할 수 있다. -
저장 프로시저
SQL SERVER를 사용하면서 저장프로시저를 사용하지 않는 서비스는 없다
그만큼 중요
이점
캡슐화
성능
실행계획을 재사용하면 CPU를 절약할 수 있다.
SQL 자체가 CPU 병렬 알고리즘을 사용하기 때문에 CPU랑 이런식으로 연관이 되는 것 같다.
네트워크 트래픽 최소화
쿼리문의 문자열이 줄면 네트워크 트래픽도 감소하게 된다.
쿼리문은 긴 편인데, 저장 프로시저 이름과 매개변수만 사용하기 때문
보안계층으로 사용
객체에 접근할 권한을 직접 주지 않고, 저장 프로시저 실행권한만 부여하면 된다.
객체에 대한 불필요한 접근을 제한할 수 있다. -
SQL의 실행을 한번 보면
SQL 같은 경우는 띄어쓰기 등 조금만 변경되어도 다른 쿼리로 인식하고 그렇기 때문에 다른 쿼리로 인식될 경우의 수가 커서 결국에 CPU를 많이 갈구게 되어있다.
결국 실행계획이 재사용되는 일은 거의 없어지고, 쿼리 컴파일이 빈번하게 일어난다.
CPU와 메모리 사용량이 올라가서 성능에 악영향을 끼친다.
그냥 저장 프로시저 쓰라는 말이다. -
- 구문분석
키워드 분석, 분리
- 구문분석
-
- 표준화
각 객체의 이름을 확인
불필요한 구문 제거
- 표준화
-
- 보안점검
구문을 실행한 사람의 권한을 체크
- 보안점검
-
- 최적화
최적화된 실행 계획을 수립한다.
성능과 관련이 크다.
- 최적화
-
- 컴파일
PLAN CACHE 라고도 하고
실행계획을 컴파일하고 캐시에 등록한다.
- 컴파일
-
- 실행
컴파일된 구문을 실행한다.
- 실행
-
SET NOCOUNT ON
개의 행이 영향을 받았습니다 같은 메시지 안뜨게 한다
엥 근데 이것도 이점이 많네..
이점
메시지 억제
클라이언트에 ~~개의 행이 영향을 받았다라는 메시지를 반환하지 않음
대량의 데이터를 처리하는 쿼리나 프로시저에서 유용
네트워크 트래픽 감소
이런 메시지들을 억제하면 클라이언트와 서버간의 네트워크 트래픽을 줄일 수 있다.
그러면 네트워크 부하가 감소하고 이것은 전체적으로 전반적인 성능을 향상시키는 것에 도움을 준다.
성능향상
일부 어플리케이션은 그 메시지의 행수에 대한 처리에 부담을 느낄 수 있다고 한다
그거 없애면 성능에 도움이 될 수도 있다는..
간결성
쓸데 없는 메시지가 없으니 결과가 더 간결하다. -
프로시저 디테일
매개변수에 DEFAULT로 전달하면 기본값으로 구문이 실행됨
물론 기본값을 사전에 설정해놔야 ( 프로시저 만들때나.. )
매개변수를 생략할 수도 있다.
단축형
EXECUTE 프로시저 명,매개변수 순서대로 나열
근데 중간에 매개변수 1, ,매개변수 3
이런식으로 맘대로 하면 안돼
매개변수 1, DEFAULT, 매개변수 3으로 이런식으로 설정해야해..
프로시저에서 리턴값을 따로 설정하지 않았다면 default는 0이다.
프로시저는 리턴값을 받던 안받던 일단 리턴값을 무조건 넘긴다. -
BEGIN
코드 블록의 시작
T-SQL에서 복잡한 실행 논리를 포함하는 코드 블록의 시작을 나타냄
특히 조건문이나 반복문 같은 제어문에서 유용
절차적 논리의 구성
저장 프로시저는 데이터베이스 작업을 수행하기 위해서 여러 SQL문장을 순차적으로 실행한다
그래서 BEGIN과 END를 잘 써서 이러한 문장들을 하나의 논리적 단위로 잘 구성해야한다
그래야 보기 깔끔하겠지?
가독성 및 유지관리
BEGIN을 사용함으로써 코드의 가독성이 향상되며 복잡한 프로시저 내에서 어떤 코드가 특정 논리적 단위에 속하는지 명확하고 빠르게 할 수 있다.
오류 처리 및 트랜잭션 관리
BEGIN을 통해서 정의된 블록 내에서 오류 처리 및 트랜잭션 관리를 보다 효과적으로 수행할 수 있다.
BEGIN TRANSACTION
BEGIN TRY
BEGIN CATCH
로 사용할 수 있다. -
RAISEERROR X / RAISERROR O
-
시스템 함수 ( 시스템 전역변수 )
보통 시스템 전역 변수를 사용하는 사유는 SQL SERVER의 내부 상태를 모니터링하고, 스크립트 또는 저장 프로시저 내에서 조건부 논리를 구현하는데 유용하게 사용하기 위해서이다.
- @@ROWCOUNT
A. 마지막으로 실행된 문STATEMENT에 의해서 영향을 받은 행의 수 반환
i. 예시로 SELECT의 @@ROWCOUNT값은 반환된 행의 총 수를 이야기 하는 것 - @@ERROR
A. 마지막 T-SQL문이 실행된 후의 오류 번호를 반환한다.
B. 문이 성공적으로 실행되면 0을 반환하며, 오류가 발생하면 해당 오류의 번호를 반환한다. - @@IDENTITY
A. 마지막으로 삽입된 행의 IDENTITY값을 반환한다
B. 주로 새로운 레코드를 삽입한 이후에 그 레코드의 식별번호가 몇인지 알고 싶을 때 사용한다. - @@SPID
A. 현재 세션의 SQL SERVER 프로세스 ID를 반환한다.
B. 이를 통해서 현재 연결이나 세션을 식별할 수 있음
- RAISERROR
RAISERROR(‘에러 발생’, 16, 1 ) 를 예시로 두면
두번째 매개변수
심각도를 의미
0(정보메시지) ~ 25(시스템 오류)
일반적으로 사용자 정의 오류는 1~18을 사용하고 19이상의 심각도는 시스템 관리자만 발생시킬 수 있다.
16은 일반적으로 사용되는 심각도로서, 심각한 오류는 맞지만, 데이터베이스는 계속 작동할 수 있게 하는 수준이다.
세번째 매개변수
상태를 의미
오류의 원인 또는 위치를 나타내는 사용자 정의값이다.
이것은 0에서 255 사이의 정수로 지정된다.
1은 가장 기본적인 상태 값으로 사용된다. 보통..
