[무언가에 대한 리뷰][테크영상리뷰][10분 테코톡] 라라, 제로의 데이터베이스 인덱스

passionfruit200 2023. 10. 7. 15:34

2023. 10. 7. 15:34

https://www.youtube.com/watch?v=edpYzFgHbqs

- B-Tree(Balanced-Tree) 자료구조

- 트리 높이가 같다.

- 자식 노드를 2개 이상 가질 수 있음 ( 이진탐색트리가 아닌이유, Child Node개수가 Binary 가 아니기에 )

- 기본 데이터베이스의 자료구조이다.

- 이렇게 아래 루트페이지, 브랜치 페이지, 리프페이지 3가지 페이지로 구분된다.

- 루트페이지 (최상단)

- 자식페이지의 정보를 가지고 있다. (브랜치페이지의 부모노드이다.)

- 브랜치 페이지( 자식페이지)

- 자식페이지의 정보를 가지고 있다. (리프페이지의 부모노드이다.)

- 리프페이지

- 실제 데이터 페이지 (클러스터링 인덱스)

- 실제 데이터의 주소 페이지 (논-클러스터링 인덱스)

- 루트페이지가 리프페이지의 시작 위치를 알고있다.

- 'ppp'를 찾는다면, 총 2개 페이지를, 7번의 검색으로 찾을 수 있다.

- 인덱스를 통해 SELECT 성능이 향상됨을 알 수 있다.

- Insert, Update, Delete의 경우는 어떻게 될까?

- INSERT인 경우

- 'ooo' 를 삽입한다.

- 'o'는 NNN과 PPP 사이에있다. 현재는 Page가 비어있었기에 쉬웠지만, 이제 페이지3이 다 찻다.

- 'zzz'를 삽입한다. 페이지가 다찼고, 문제가 있는 데이터의 페이지를 공평하게 나눈다.

- 페이지 분할을 한다. 페이지에 새로운 데이터를 추가할 여유공간이 없어 페이지에 변화가 발생한다. DB가 느려지 고 성능에 영향을 준다.

DELETE인경우
- 인덱스의 데이터를 실제로 지우지 않고 사용안함 표시를 한다.
- DELETE(기존 값 사용안함 표시), INSERT ( 변경된 값 삽입 )
UPDATE인경우
- - DELETE(기존 값 사용안함 표시), INSERT ( 변경된 값 삽입
UPDATE, DELETE인경우도 WHERE 절을 사용하면 빨라지지 않을까?
- WHERE 절로 처리할 대상을 찾기 위한 조회 성능은 향상된다.
- 사용하지 않는 인덱스가 적용되었다면 불필요한 처리량이 증가한다
- 사용안함표시로 페이지 낭비 및 인덱스 조각화가 심해진다.
SELECT는 성능이 향상되고, INSERT, UPDATE, DELETE는 페이지 분할과 사용안함표시로 인덱스의 조각화가 심해져 성능이 저하된다.

3. 인덱스 종류

클러스터링 인덱스, 논 클러스터링 인덱스(보조인덱스, 세컨더리 인덱스) 2가지로 나뉜다.
클러스터링 인덱스
- 무리, 군집, 무리를 이루다 라는 의미다. 즉, 실제데이터와 무리를 이룬다
- 실제데이터와 같은 무리의 인덱스를 의미한다.
- 실제데이터가 정렬된 사전으로 볼 수 있다.
논 클러스터링 인덱스
- 실제데이터와 다른 무리의 별도의 인덱스를 의미한다.
- 책 앞에 존재하는 책 내용을 보여주는 목차와 같은 페이지와 같은 역할이라고 볼 수 있다.
우리는 모르는 새에 데이터베이스 인덱스를 사용하고 있었다.

create table member(
	id int primary key,
    name varchar(255),
    email varchar(255) unique
);

자동으로 Create table만으로도 인덱스가 2개가 생성되었다.
- 이유는 primary key와 unique 제안조건으로 생겼다.
- 하나의 컬럼에 primary key를 입력하면 클러스터링 인덱스가 자동으로 생성된다.
- unique 키워드를 사용하면 논-클러스터링 인덱스가 자동으로 생성된다.

- 클러스터링 인덱스에 대한 더 자세한 내용

create table member(
	id int,
    name varchar(255),
    email varchar(255)
);

위와 같이 어떠한 제약조건 없이 생성해본다. 즉, 어떠한 인덱스도 생기지 않는다.
Member 테이블에 id, name, email이라는 컬럼이 존재한다.
아래 처럼 insert 해보자. 순차적으로 데이터가 쌓일것이다.

insert into member values(1, '안녕', 'hello@hello.com");

clustering index를 적용해보자.

첫번쨰 방법은 Primary key를 COlumn 에 적용한다.

ALTER TABLE member
ADD CONSTRAINT pk_id PRIMARY KEY(id);

두번쨰 방법은 한 컬럼에 NOT NULL과 UNIQUE 제약조건을 설정한다.

ALTER TABLE member MODIFY COLUMN id int not null;
ALTER TABLE member ADD CONSTRAINT unq_id UNIQUE(id)

위의 2가지 방법으로 CLustering Index를 구성할 수 있다.

Indx를 적용한 후에는 어떤 작업이 일어날지 알아보자.

클러스터링 인덱스를 적용한 id 컬럼을 기준으로 데이터가 정렬된다.
여기서는, id가 1, 2, 3,4, .. 이니 id 컬럼을 기준으로 정렬될것이다.
정렬된 데이터를 기준으로 루트페이지와 리프 페이지가 생성된다. 이 구조는 B-TREE 구조로 되어있다.
루프페이지에는 id값과 데이터페이지 로 저장된다. 데이터페이지란 리프페이지에서 실제데이터가 저장된 데이터페이지이다.
어떤 데이터가 추가되거나 삭제되더라도 이 정렬을 최신상태로 유지하면서 데이터가 저장되어잇다.
실제 데이터가 정렬된 사전이라고 생각하자.
아래와 같이 id가 7인 데이터를 찾아보자.

SELECT * FROM MEMBER
WHERE id = 7

id 7은 id 5와 9 사이에 위치한다. 그러니 데이터페이지 값인 1001 을 찾아서 해당 데이터페이지인 리프페이지로 간뒤 데이터를 찾을 수 있다.
클러스터링 인덱스의 특징을 정리해보면,
- 1. 실제 데이터 자체가 정렬되어 잇다.
- 2. 테이블 당 1개만 존재가 가능하다. ( 여러개의 인덱스를 사용할떄는 클러스터링 인덱스가 사용안되는것이다. )
- 3. 리프페이지가 데이터 페이지와 동일하다.
- 아래의 제약조건 시 자동 생성된다.
  - primary key(우선순위)
  - unique + not null

- 논-클러스터링 인덱스에 대한 자세한내용

create table member(
	id int,
    name varchar(255),
    email varchar(255)
);

위와 같이 어떠한 제약조건 없이 생성해본다. 즉, 어떠한 인덱스도 생기지 않는다.
Member 테이블에 id, name, email이라는 컬럼이 존재한다.
아래 처럼 insert 해보자. 순차적으로 데이터가 쌓일것이다.

insert into member values(1, '안녕', 'hello@hello.com");

이번에는 name column에 non-clustering index로 구성해보겠다.
첫번쨰 방법은, 한 컬럼에 UNIQUE 제약조건을 건다.

ALTER TABLE member
ADD CONSTRAINT unq_name UNIQUE(name);

두번쨰 방법은, Index 자체를 생성한다. UNIQUE_INDEX 를 생성하면, 중복을 허용하지 않으며 인덱스를 생성한다.

CREATE UNIQUE INDEX unq_idx_name
ON member(name);

세번쨰 방법은, Index 자체를 생성한다. INDEX 를 생성하면, 중복을 허용하는 INdex가 생성된다.

CREATE INDEX idx_name
ON member (name);

논 클러스터링 인덱스 구성후를 살펴봅니다.

루트페이지
리프페이지
- 클러스터링 인덱스와 다르게 리프페이지가 한개 생깁니다.
- 즉, 루트페이지에서 바로 데이터페이지로 가는것이 아닌 별도의 인덱스 페이지가 추가됩니다.
- 이 리프페이지는 카테고리로써 제목이 존재하고, 페이지에는 데이터페이지의 위치(참조값)이 존재한다.
- 이 리프페이지는 실제 데이터 탐색에 도움을 주는 별도의 찾아보기 페이지입니다. ( 클러스터링 인덱스 같은경우에는 실제 데이터가 정렬된 사전이라면, 논-클러스터링은 카테고리 목록이라고 보면된다.)
데이터페이지
- 실제 데이터가 저장된 데이터페이지는 변경되지 않는다.

아래의 과정을 진행해보자.

SELECT * FROM member
WHERE name = '철수'

1. 루트페이지에서 '철수' 라는 이름을 찾기 위해 name 인덱스 페이지에서 '철수'를 찾는다.
2. 데이터 페이지 주소를 통해 실제 데이터를 탐색한다.

논 클러스터링 인덱스의 특징

1. 실제 데이터 페이지는 그대로 존재한다.
2. 별도의 인덱스 페이지가 생성되어서 추가공간이 필요하다.
3. 테이블 당 여러개가 존재한다. ( 각 인덱스마다를 의미하는것 같다.)
4. 리프 페이지에 실제 데이터 페이지 주소를 담고잇다.(루트 페이지가 가리키고있는, 리프페이지가 카테고리다)
5. unique 제약조건 적용시 자동생성한다. ( 인덱스를 생성하기 위해서는 한개씩만 있어야하기 떄문인듯)
6. 직접 index를 생성시에도 논-클러스터링 인덱스 생성한다.

클러스터링 인덱스와 논-클러스터링 인덱스를 함꼐 적용하면 어떻게 될까?

create table member(
	id int,
    name varchar(255),
    email varchar(255)
);

id 컬럼에는 클러스터링 인덱스를 적용하고 name 컬럼에는 논-클러스터링 인덱스를 적용합니다.
예상으로는 논-클러스터링의 리프페이지에는 데이터페이지의 주소가 들어있을것이라고 생각했지만(즉, 클러스터링 인덱스 페이지의 id 인덱스페이지는 생략할줄알앗다.), 예상과 다르게 id값의 실제 값이 들어가있었다.
name 컬럼에 non-clustering index가 적용된 경우,
- 1. name 인덱스 페이지에서 '철수' 탐색, 이를 통해 해당 id값을 가져온다. (메모리 위치값이 아니다.)
- 2. 이떄 id값을 id 인덱스 페이지에서 탐색한다.
- 3. 해당 데이터페이지를 찾아서 들어간다.
왜 이와 같이 name 컬럼의 논-클러스터링 인덱스페이지에 데이터페이지의 주소가 아닌, id값이 들어가도록 설계되었을까?
- 1. 만약 id가 3인 '하영'이가 추가되었다고하자.
- 2. 그렇다면, 데이터페이지에 새로운 값이 들어온다.
- 3. 페이지 분할이 발생한다.
- 4. 그렇게 되면 name index page의 리프페이지의 주소가 변경되어야한다.
- 5. 페이지가 분할될떄마다 계속해서 리프페이지의 위치가 바뀌어야한다.
- 6. 그렇기에 id가 변경되지 않는 이상 리프페이지는 변경되지 않고, 데이터페이지만 분할된다.

클러스터링 + 논-클러스터링 인덱스 구성의 특징

클러스터링+논-클러스터링 조합에서 특징은 이전에 배웠던 것과 다 같고, 딱 한가지가 유의한다.
우선, 바뀌는 특징은 논-클러스터링 인덱스는 원래
- 리프페이지에 실제 데이터 페이지 주소가 존재했다.
- 하지만, 같이 사용될경우 리프페이지에는 클러스터링 인덱스가 적용된 컬럼의 실제값이 적용된다.

4. 인덱스 적용 기준 (

어떤 컬럼에 인덱스를 적용해야할까라는 의문에서 나온다.
카디널리티(Cardinality) : 그룹 내 요소의 개수, the number of elements in a set or group
- id, 이름, 그룹, 이메일, 성별, 주민번호, 나이 이 중에서 어떤 컬럼에 인덱스를 적용해야할까?
- 카디널리티(그룹 내 요소의 개수)가 높은것 = 중복수치가 낮은 것.
- 즉, 해당 값들의 variation이 다양한 것을 고른다.
추가적인 적용기준
- 1. 카디널리티가 높은(중복도가 낮은) 컬럼
- 2. WHERE, JOIN, ORDER BY 절에 자주 사용되는 컬럼
  - 인덱스는 추가 공간이 필요로 된다.
  - 조건절이 없다면 인덱스가 사용되지 않는다.
- 3. INSERT / UPDATE / DELETE 가 자주 발생하지않는 컬럼
- 4. 규모가 작지 않은 테이블

5. 실습

- 인덱스 조회

CREATE TABLE member (
	id int primary key,
    email varchar(255) unique
);

위의 테이블의 인덱스를 적용한다.
- Non_unique id : 0, email : 0
- key_name id: primary key, email : email
- Cardinality (현재 아무런 데이터가없기에) id : 0, email : 0
- index_type : BTREE

- 성능 비교

인덱스가 적용되지 않은경우 10만건 데이터 기준

select * from member where email='hello@hello.com'  0.25초 (10만건)
select * from member where email='hello@hello.com'  0.67초 (100만건)

email 인덱스를 적용한 경우 10

select * from member where email='hello@hello.com'  0.00초 (10만건)
select * from member where email='hello@hello.com'  0.001초 (100만건)

6. 인덱스 사용시 주의사항

1. 잘 활용되지 않는 인덱스는 과감히 제거하자.
- WHERE 절에 사용되더라도 자주 사용해야 가치가 있다.
- 불필요한 인덱스로 성능저하가 발생가능
2. 데이터 중복도가 높은 컬럼은 인덱스 효과가 적다.
3.자주 사용되더라도 INSERT / UPDATE / DELETE 가 자주 일어나는지 고려한다.
- 일반적인 웹서비스와 같은 온라인 트랜잭션 환경에서 쓰기와 읽기 비율은 2:8 또는 1:8 이다.
- 조금 느린 쓰기를 감수하고 빠른 읽기를 선택하는것도 하나의 방법이다.

'무언가에 대한 리뷰 > 테크영상리뷰' 카테고리의 다른 글

[무언가에 대한 리뷰][테크영상리뷰][10분 테코톡] 저문, 라온의 Cache & Redis (0)	2023.10.11
[무언가에 대한 리뷰][테크영상리뷰][쉬운코드]DB 인덱스(DB index) !! (0)	2023.10.09
[무언가에 대한 리뷰][우아콘2020] 수십억건에서 QUERYDSL 사용하기 (0)	2023.10.04
[무언가에 대한 리뷰][10분 테코톡] 🤔 조엘의 GC (0)	2023.10.04
[무언가에 대한 리뷰][10분 테코톡] 조조그린의 Thread Pool (0)	2023.10.04

passionfruit200