DB INDEX

개발을 하면서 생각보다 조회 자체로도 서버 부하에 큰 영향을 준다는 점을 알았습니다.

테스트 서버와 달리 실서버 수십만건의 결제 데이터 조회 시 서버에 부하를 일으켰습니다.

기존 서비스들이 무분별한 확장만 추구하였을 뿐 어느 누구도 서비스의 질을 생각하지 않아 속도가 느려지며 부하가 발생하여 데드락이 발생하여 점점 서비스에 영향이 갔습니다.

 

PAGE란?

---

• 데이터 파일을 구성하는 논리 단위
• SQL Server의 기본 데이터 저장 단위(8KB)
• 데이터를 쓸 때 행을 페이지에 기록됨
• 데이터를 읽을 때 페이지 내의 모든 행이 읽어짐
• 페이지 내의 행이 많을 수록 I/O 효율 증가

 

INDEX란?

---

추가적인 쓰기 작업과 저장 공간을 활용하여 DB 테이블의 검색 속도를 향상시키기 위한 자료구조

 

장점

• 빠른 데이터를 검색 ( Disk I/O를 줄일 수 있다 )
• 데이터의 중복을 방지할 수 있습니다. (Primary Key, Unique)
• 잠금을 최소화 시켜줍니다. (동시성을 높여줍니다.)

 

단점

• 물리적인 공간을 차지하게 됩니다. (인덱스도 테이블처럼 데이터를 가지므로 물리적인 공간을 차지하게 됩니다.)
• 페이지를 가지고 있는 존재는 데이터와 인덱스 두가지입니다. (프로시져/뷰는 사이즈가 없습니다.)
• INSET , DELETE , UPDATE 의 성능을 떨어뜨립니다.

 

 

Table 구조

• MSSQL은 2가지의 TABLE 구조로 존재합니다.

 

1. HEAP

---

정렬의 기준이 없이 저장 된 테이블의 형태

• Queue에서 나온 개념으로써, 우선 순위를 도입하기 위해 사용 ( E.G 시뮬레이션 , NETWORK TRAFFIC , OS SCHEDULING )
• 완전 이진 트리로 우선 순위 QUEUE를 위하여 만들어진 자료 구조
• 여러 값들 중 최소 , 최대 값을 빠르게 찾을 수 있도록 구현
• 중복된 값이 허용된다. (완전 이진 트리는 중복 X )

 

장점

• INSERT 새로운 행을 기존 페이지의 빈 곳에 추가하면 되고, 빈 공간이 없으면 새로운 페이지에 추가

단점

• SELECT 모든 테이블을 스캔해야하기 때문에 데이터를 찾기가 어렵습니다.

 

2. CLUSTER INDEX TABLE

---

2-1) B TREE

• BTREE자료 구조 형태 ( ROOT / INTERMEDIATE / LEAF ) 3개의 NODE로 구성되며, 노드에 페이지 생성
• 이진트리에서 발전되어 모든 리프 노드들이 같은 level을 가질 수 있도록 BALANCE를 맞추는 TREE
• 정렬된 순서를 보장 및 MULTI LEVEL INDEXING 이 가능합니다. ( 실제 DB에서는 B+트리 사용 )
• 이진트리와 다르게 하나의 노드에 많은 양의 ROW DATA 저장 가능 -> M차 B트리

 

 

2-2 ) CLUSTER INDEX

• TABLE마다 하나만 생성 가능 ( 정렬이 필요하기 때문 )
• 성능 최적화를 위해 자주 사용하는 쿼리 칼럼 , 고유한 값을 많이 가지는 칼럼에 생성
• JOIN 조건 / ORDER BY / GROUP BY 에 사용되는 경우 CLUSTER INDEX KEY로 설정
• 자주 변경되는 칼럼은 클러스터형 인덱스 키로 사용하지 않는 것이 좋습니다.
• 다수의 Key 칼럼을 사용하는 것은 좋지 않으므로 최대한 적은 수의 칼럼을 클러스터형 인덱스 키 칼럼으로 지정해서 사용

 

 

2-3) NONCLUSTER INDEX

• INDEX가 생성되더라도 실제 데이터 정렬 X
• HEAP인 경우 = LOCATOR라는 행포인터 -> RID -> 데이터 찾기
• CLUSTSER INDEX = RID 가 아닌 클러스터형 인덱스 KEY를 통해 데이터

 

*RID = 파일식별자 + 페이지번호 + 페이지 내 행의 번호

• 업데이트가 자주 발생하지 않는 테이블에 인덱스를 생성하는 것이 좋다( INDEX가 다수 설정되면, 데이터가 변경 시 인덱스도 전부 변경이 되어야하여 성능 저하)
• 조회하려고 하는 모든 칼럼이 인덱스에 포함되어 있는 경우에는 데이터 페이지까지 내려가지 않고 모든 데이터를 조회할 수 있기 때문에 성능 향상이 가능하므로 Included column을 사용하는 것도 고려해보는 것이 좋다.

* 클러스터형 인덱스에는 Included column을 설정할 수 없다.

 

 

INDEX SEEK / INDEX SCAN / TABLE SCAN

---

• CLUSTER 및 NONCLUSTER로 조회 시 INDEX를 타게 되면 INDEX SEEK
• CLUSTER INDEX가 잡혀있음에도 KEY로 조회를 하지 않게 되면 결국 모든 TABLE을 전부 봐야하여 INDEX SCAN으로 처리가 된다. ( 최악의 경우 INDEX + 모든 TABLE을 봐야 함으로 시간이 더 소요 )
• TABLE SCAN 은 앞서 HEAP 구조의 TABLE에서 모든 DATA 조회 시

 

use test

IF OBJECT_ID('card') IS NOT NULL
  DROP TABLE card
GO 

CREATE TABLE card
(
     id   INT          NOT NULL                                          
   , name NVARCHAR(20) NOT NULL                                           
   , amount INT        NOT NULL DEFAULT 0                               
   , itemname NVARCHAR(30)     NULL                                           
   , transdate VARCHAR(8)   NOT NULL DEFAULT CONVERT(CHAR(8), GETDATE(), 112) 
   , indate  DATETIME              DEFAULT GETDATE()                       
);

-- CLUSTER INDEX 설정
CREATE UNIQUE CLUSTERED INDEX ix_tmp_id ON card(id, transdate);  
-- NONCLUSTER INDEX 설정
CREATE INDEX ix_non_card ON card(amount);

INSERT INTO card(id, name, amount, itemname) VALUES(143, '김욱성', 9000000, '룰렉스시계');
INSERT INTO card(id, name, amount, itemname) VALUES(154, '이나현', 93000000, '스포츠카');
INSERT INTO card(id, name, amount, itemname) VALUES(111, '윤명근', 5000, 'NOTEBOOK');
INSERT INTO card(id, name, amount, itemname) VALUES(122, '허강무', 400, '껌');
INSERT INTO card(id, name, amount, itemname) VALUES(110, '이하연', 160000, '목걸이');
INSERT INTO card(id, name, amount, itemname) VALUES(125, '김예지', 88000, '나이키신발');

-- 어떤 방식으로 찾는지 보여줌 1. SEEK  -> 2. WHERE
SET SHOWPLAN_ALL ON
SELECT AMOUNT FROM CARD WHERE AMOUNT > 100000 AND ID = 110;
SET SHOWPLAN_ALL OFF

-- INDEX SEEK ( CLUSTER )
SELECT ID , NAME FROM CARD WHERE AMOUNT > 100000 AND ID = 110;
-- INDEX SEEK (NONCLUSTER)
SELECT id FROM card WHERE amount > 95;
-- INDEX SCAN ( CLUSTER )
SELECT NAME FROM CARD WHERE AMOUNT > 100000;
-- INDEX SCAN ( CLUSTER )
SELECT NAME FROM CARD WHERE NAME = '김욱성';

 

 

인용

https://datalibrary.tistory.com/129

[SQL Server] 클러스터형 인덱스(Clustered Index)와 비클러스터형 인덱스(Non Clustered Index) 비교 - 1: 클러

https://velog.io/@emplam27/%EC%9E%90%EB%A3%8C%EA%B5%AC%EC%A1%B0-%EA%B7%B8%EB%A6%BC%EC%9C%BC%EB%A1%9C-%EC%95%8C%EC%95%84%EB%B3%B4%EB%8A%94-B-Tree

[자료구조] 그림으로 알아보는 B-Tree

https://co-no.tistory.com/10