MS-SQL
Clustered vs NonClustered
by kcj3054
2022. 4. 5.
Clustered . NonClustered
- Clustered Index는 동일한 값에도 인덱스를 걸 수 있다 (pk와의 차이점) 그런데 동일한 값에서도 데이터를 식별해야해서 Uniquifier가 존재한다.
- Clusterd (영한 사전)이고 Non - Clusterd(색인)으로 본다 왜? Clustered index를 보면 바로 데이터를 찾아 갈 수 있는데 Non clustered는 데이터를 색인처럼 중복해서 가지고 있을 수 있기 때문이다.
- Clusterd는 leaf Page가 Dage Page이다. 데이터는 ClusterdIndex 키 순서로 정렬된다..
- Non - Clusterd는 Clusterd Index에 따라서 다르게 결정된다..
- 1) Clusterd Index가 존재하는 경우 HEAP Table이 없다 leaf table에 실제 테이블에 실제 데이터가 존재한다.Clustered index의 실제 키를 들고 있다.
- 2) Clusterd Index가 존재하지 않는 경우 데이터는 HEAP Table이라는 곳에 저장된다 , 페이지 정보를 살펴보면 Heap RID라는 것이이는데 이것을 살펴보면서 PAGE DATE를 찾아가면서 HEAP Table을 찾아 갈 수 있다..
- Clusterd Index가 존재하지 않는 경우 => Heap RID를 통해 Heap table
Clustered index가 테이블당 하나만 존재하는 이유 ?
- Clustered index는 물리적으로 데이터를 정렬한다. 그래서 테이블당 Clustered index는 하나만 존재할 수 있다.
- 물리적으로 정렬되어 있어서 순차적으로 데이터를 접근할 때 속도가 빠르다.
Non - clustered Index
- 물리적이지 않아서 데이터 페이지를 인덱스에 의해 찾아가야한다. Heap RID를 통해서 찾아간다.
Clustered . NonClustered 실습
-인덱스 번호 찾기
SELECT index_id, name
FROM sys.indexes
WHERE object_id = object_id('TestOrderDetails')
-- 조회 PageType이 2이다 Nonclustered
DBCC IND('Northwind', 'TestOrderDetails', 2)
-- Index Level이 높을 수록 root이다.
-- 944
-- 856 888 889 890 891....
--Heap RID ([페이지주소][파일ID][슬록])
-- Heap Table [{page}{page}{}..]
--
DBCC PAGE('Northwind', 1/*페이지 ID*/,888, 3)
-- HEAP RID가 존재... HEAP RID를 이용해서 데이터가 있는 Heap Table에서 데이터를 꺼내서 사용한다..
clustered 추가
--clusterd 추가
CREATE CLUSTERED INDEX Index_OrderDetails_Clustered
ON TestOrderDetails(OrderID)
-- 조회 PageType이 1이다 clustered
DBCC IND('Northwind', 'TestOrderDetails', 1)
-- 968
-- 920 928 929 931.... (실제 데이터를 물고 있는 페이지..)
DBCC PAGE('Northwind', 1/*페이지 ID*/,931, 3)
임시...
clustered Index :
-> pk or Unique index // pk가 없을 때는 Unique index가 clustered index가 된다.
-> 물리적인 데이터 파일과 연관이 있다.
->
-> index는 읽기속도만 항샹된다.
->
-> 1,2 ,3, 4 5처럼 나열되어있는 것이 cardinality가 높다고 말한다.
-> 남, 여로 그룹화되어있는 것은 cardinality가 낮다고말한다.
-> cardinality가 높아야지 인덱스 속도향상에 도움이된다.
-> clustered Index는 리프자체가 데이터, Nonclusterd Index는 RID이다.
-> 인덱스를 걸어줄 때 optimize table emp 와, analyze table emp를 해주는 것이 좋다.
-> 인덱스의 크기는 Data Size의 10%를 넘는 것은 좋지 않다고한다 인덱스의 크기는 (clustered index size + non-clustered index size), Data size는 (rows * avg_row_length)
-> show index from 테이블 // 인덱스 잡혀있는 것을 볼 수 있따.
-> 인덱스는 꼭 필요한 것들만 걸어야한다 왜 ? //
-> 클러스터 인덱스는 쓰기는 느리다 왜냐? // 데이터 파일까지 분할이 일어나기때문이다., pk를 autoincrement로 잡은 것은 중간에 끼어드는 것이 없어서 페이지분할이 적기때문이다.
- like로 동일 테이블도 만들 수 있다 => ex : create table Dept2 lite Dept;
- AS를 이용하면 테이블뿐만 아니라 테이터까지 다 동일하게 사용할 수 있다 => create table Dept3 AS select * from Dept; // as로 하면 테이블과 데이터는 오지만, 인덱스는 오지않는다.
- 테이블이 어떻게 만들어졌는지 볼 수 있다 => show create table [테이블 명]
-
[nonclustered Index]
- 정렬이 되어있지않아서 탐색은 느리다, 인덱스 페이지 리프노드에 실제 데이터가 있는 것이 아니라, 데이터 페이지에 관한 포인터가 있다
- 정렬이 되어있지 않아서 탐색은 느리나, 삽입 삭제 수정이 빠르다, 왜? 삽입 삭제할 때 순서에 상관없이 그냥 지우면되니..
-
[트랜잭션 격리성과 격리수준에 따른 현상]
- 팬턴리드: 한 트랜잭션 내에서 동일한 쿼리를 보냈을 때 해당 조회 결과가 다른 경우를 말한다,
사용자 A가 회원 테이블에서 age가 10 이상인 회원들을 조회하는 쿼리를 보낸다고 해보자. 그결과가 2라고 할 때
그다음 사용자 B가 age가 20인 회원 레코드를 삽입 그렇게 된다면 후에 사용자 A가 조회할 때는 2가 아닌 3개의 테이블이 조회되는 것이다.
- 반복 가능하지 않은 조회 : 한 트랙잭션 내의 같은 행에 두 번 이상 조회가 발생했는데, 그 값이 다른 경우를 말한다.
EX : 사용자 A가 보석 개수가 10개라는 값을 가진 데이터였는데, 그후 사용자 B가 그 값을 0으로 변경해서 커밋을 하면 사용자 A는 10이 아닌 0을
읽게 된다 팬텀리드와 차이점은 팬텀리드는 값이 변경되는 것은 아닌데 반복 가능하지 않은 조회는 값이 변경된다.
- 더티리드 : A트랜잭션이 실행 중일 때 B트랜잭션에 의해 수정되어었지만, '아직 커밋되지 않은 행의 데이터를 읽을 수 있을 때 발생한다
EX : 사용자 A가 아이템 개수 200개를 0으로 변경한 내용이 '커밋되지 않은' 상태라도 이후 B가 조회한 결과가 0인 경우
[격리 수준]
- Serializable : 트랜잭션을 순차적으로 진행하는 것이다. 여러 트랜잭션이 동시에 같은 row에 접근을 못한다 안전하지만 동시성이 떨어져서 성능이 좋지
못한 격리 수준이다.
- repeatable_read : 하나의 트랜잭션이 수정한 행을 다른 트랜잭션이 수정할 수 없도록 막아주지만 새로운 행을 추가하는 것을 막지 않습니다,
그래서 이후 추가된 행이 발견될 수도있다.
- read_committed : 커밋 완료된 데이터에 대해서만 조회가능, 그렇지만 A트랜잭션이 수정한 행을 B트랜잭션이 수정할 수도있다 그 결과 A가 디사 같은
위치를 조회할 때는 다른 내용이 발견될 수 있다.
-
=====================================================================================
DTO : DATA TRANSFER OBJECT 데이터 전달 개체, // 어떤 데이터를 넘기고 받을 것이냐에 따라서 DTO를 설계하면된다.
DAO : 인터페이스로만 구성..
