[DB] 인덱스 종합 정리

강민승·2023년 12월 25일
0

DB

목록 보기
12/12

📌 인덱스란

쉽게 찾아볼 수 있도록 일정한 순서에 따라 놓은 목록
-> 즉, 원하는 값을 빠르게 찾을 수 있도록 하는 것!
Database에서 Select에 해당하는 항목!

📍 인덱스 예시

인덱스가 이메일일 경우
100만건 이상의 데이터에서, 먼저 이메일로 정렬이 되어야한다.
이메일이 index01@gmail.com 인 회원을 조회하면 빠르게 값을 찾을 수 있다.
SQL문으로 보면

select * from member
where email = 'index01@gmail.com'

이 될 것이다. 즉, 인덱스가 적용이 되고, where 절을 통해 검색을 한다.

하지만, 

select * from member

은 where 절을 통한 검색이 아니기 때문에, 인덱스가 사용되지 않는다.

즉, 인덱스는 데이터베이스 테이블에 대한 검색 성능을 향상시키는 자료 구조이며 where 절 등을 통해 활용이 된다.

📍 인덱스

인덱스는 항상 최신의 정렬상태를 유지
인덱스도 하나의 데이터베이스 객체 -> 저장공간을 사용한다.
데이터베이스 크기의 약 10% 정도의 저장공간이 필요하다.

인덱스는 MYI(MySQL Index)파일에 저장되며, 인덱스가 설정되지 않았다면 Table Full Scan이 일어나 성능이 저하되거나 치명적인 장애가 발생한다.

먼저 알고가자

페이지: 데이터가 저장되는 단위

📌 인덱스 알고리즘

🔧 Full Table Scan:

찾는 과정: 1 페이지 부터 끝 번까지 모두 순회하여 찾는 것을 Full Table Scan이라고 한다.

특징 :
- 순차적으로 접근
- 접근 비용 감소
사용
- 적용 가능한 인덱스가 없는 경우
- 인덱스 처리 범위가 너무 넓은 경우
- 크기가 작은 테이블에 엑세스하는 경우 <- 너무 작기 때문에 필요없는 경우

🔧 B-Tree (Balanced-Tree)

🔨 특징

  • 트리 높이가 같음
  • 자식 노드를 2개 이상 가질 수 있음
  • 기본 데이터베이스 인덱스 구조 (MySQL, MariaDB 등..)

🔨 구성

  • 루트 페이지 (자식 페이지의 정보)
  • 브랜치 페이지 (자식 페이지의 정보)
  • 리프 페이지
    • 실제 데이터의 페이지 (클러스터링 인덱스)
    • 실제 데이터의 주소 페이지 (논-클러스터링 인덱스)

📍 인덱스의 INSERT, DELETE, UPDATE

INSERT

INSERT를 한다면 페이지가 가득 찰 때까지 INSERT를 한다.
이것은 페이지 내부에서 작업되었기 때문에 큰 부담은 없지만 페이지를 초과해서 저장하려고 한다면, 페이지 분할이 발생하는데 이때 추가 페이지를 확보하고 문제가 있는 데이터 페이지를 공평하게 나누어 저장한다.
이때 페이지 분할이 DB에 부담을 주게 된다.

페이지 분할

페이지 분할은 페이지에 새로운 데이터를 추가할 여유공간이 없어, 페이지에 변화가 발생할 때 나타난다.

DELETE

DELETE 인덱스의 데이터를 실제로 지우지 않고 사용안함 표시를 한다.

UPDATE

UPDATE는 1. DELETE 후 2. INSERT 함으로써 실행된다.

📍 UPDATE, DELETE에서 WHERE 조건으로 처리한다면?

UPDATE 또는 DELETE를 WHERE 절로 처리할 대상을 찾기 위한 조회 성능은 향상된다.
하지만, 사용하지 않는 인덱스가 적용되었다면, 불필요한 처리량이 증가하고, 사용안함 표시로 인해 페이지 낭비 및 인덱스 조각화가 심해질 수 있다.

📍 인덱스의 종류

🔧 클러스터형 인덱스 (Clustered Index)

  • 테이블당 1개만 존재할 수 있음
  • 기본 키로 지정된 컬럼은 자동으로 클러스터링 인덱스가 생성됨
  • 실제 저장된 데이터와 같은 무리의 페이지 구조를 가짐
  • 실제 데이터가 정렬된 사전
  • 클러스터링 인덱스를 기준으로 데이터가 자동 정렬됨
  • 기본 키를 변경하면 클러스터링 인덱스가 변경되기 때문에 변경된 기본 키를 기준으로 자동 정렬

🔧 보조 인덱스 (Secondary Index / non- Clustered Index)

  • 한 테이블에 여러개 설정 가능
  • UNIQUE 키워드로 고유 컬럼 지정시 자동으로 보조 인덱스가 생성됨
  • 실제 저장된 데이터와 다른 무리의 별도의 페이지 구조를 가짐
  • 클러스터링 인덱스와 달리 데이터를 정렬하지 않음
  • CREATE INDEX 문으로 직접 보조 인덱스 생성 가능
  • 실제 데이터 탐색에 도움을 주는 별도의 찾아보기 페이지

🔨 클러스터링 인덱스

적용 하는 방법
1. 

alter table member
add constraint pk_id primary key (id);
  1. NOT NULL 과 UNIQUE를 동시에 걸면 PRIMARY KEY와 같은 효과
alter table member modify column id int not null;
alter table member add constraint nuq_id unique (id);

이렇게 하면 가장 먼저 클러스터링 인덱스를 적용한 id 컬럼을 기준으로 데이터가 정렬이 된다.
정렬된 데이터를 기준으로 루트 페이지가 구성이 된다. B-Tree구조.

특징

  1. 실제 데이터 자체가 정렬
  2. 테이블당 1개만 존재 가능
  3. 리프 페이지가 데이터 페이지
  4. 아래의 제약조건 시 자동 생성
    -primary key (우선순위)
    -unique + not null

🔨 논 클러스터링 인덱스

방법 1.

alter table member
add constraint unq_name unique (name);

방법 2.

create unique index unq_idx_name
on member (name);

방법 3.

create index idx_name
on member (name);

이렇게 한다면 데이터 페이지(실제 데이터가 저장된 곳)는 어떠한 데이터의 변화도 일어나지 않음

클러스터링 인덱스와는 다르게 별도의 인덱스 페이지가 추가가 된다.

특징

  1. 실제 데이터 페이지는 그대로
  2. 별도의 인덱스 페이지 생성 -> 추가 공간 필요
  3. 테이블당 여러 개 존재
  4. 리프 페이지에 실제 데이터 페이지 주소를 담고 있음
  5. unique 제약조건 적용시 자동 생성
  6. 직접 Index 생성시 논-클러스터링 인덱스 생성

클러스터링과 논 클러스터링 인덱스를 함께

-> 논 클러스터링 인덱스의 리프 페이지에 클러스터링 인덱스가 적용된 컬럼의 실제 값이 들어가 있음.

📌 정리해보는 인덱스 특징

📍 인덱스의 특징

인덱스는 하나 혹은 여러 개의 컬럼에 대해 설정할 수 있다. (단일 여러개 또는 여러컬럼을 묶어 복합인덱스)

WHERE절을 사용하지 않고 인덱스가 걸린 컬럼을 조회하는 것은 성능에 아무런 영향이 없다.

ORDER BY 와 GROUP BY에 대한 INDEX
INDEX는 ORDER BY와 GROUP BY에도 영향을 끼치는데 다음과 같은 경우에는 INDEX를 타지 않는다.

ORDER BY 인덱스컬럼1, 컬럼2 : 복수의 키에 대해서 ORDER BY를 사용한 경우
WHERE 컬럼1='값' ORDER BY 인덱스 컬럼 : 연속하지 않은 컬럼에 대해 ORDER BY를 실행한 경우
ORDER BY 인덱스컬럼1 DESC, 인덱스컬럼2 ASC : DESC와 ASC를 혼합해서 사용한 경우
GROUP BY 컬럼1 ORDER BY 컬럼2 : GROUP BY와 ORDER BY의 컬럼이 다른 경우
ORDER BY ABS(컬럼) : ORDER BY 절에 다른 표현을 사용한 경우

다중 컬럼 인덱스
다중 컬럼 인덱스는 두개 이상의 필드를 조합해서 생성한 INDEX이다. 1번째 조건과 이를 만족하는 2번째 조건을 함께 INDEX해서 사용한다. (MySQL은 INDEX에 최대 15개 컬럼으로 구성 가능)

다중 컬럼 인덱스는 단일 컬럼 인덱스 보다 더 비효율적으로 INDEX/UPDATE/DELETE를 수행하기 때문에 신중해야한다.

때문에 가급적 UPDATE가 안되는 값을 선정해야한다.

인덱스 적용 기준

카디널리티 -> 그룹 내 요소의 갯수
즉, 카디널리티( 그룹 내 요소의 갯수)가 높은 것 == 중복 수치가 낮은 것.

만약 id, 이름, 그룹, 이메일, 성별, 주민번호, 나이가 있다면 주민번호 혹은 이메일이 될 수 있다.

또한,
Where, join, order by 절에 자주 사용되는 컬럼 (인덱스는 추가 공간이 필요로 된다. 조건 절이 없다면 인덱스가 사용되지 않는다)

insert, update, delete가 자주 발생하지 않는 컬럼

규모가 작지 않은 테이블이 해당이 된다.

인덱스 사용시 주의사항

  1. 잘 활용되지 않는 인덱스는 과감히 제거하자

    • where 절에 사용되더라도 자주 상요해야 가치가 있다.
    • 불필요한 인덱스로 성능저하가 발생할 수 있다.

  2. 데이터 중복도가 높은 컬럼은 인덱스 효과가 적다.

  3. 자주 사용되더라도, Insert/ update/ delete가 자주 일어나는지 고려해야 한다.

    • 일반적인 웹 서비스와 같은 온라인 트랜잭션 환경에서 쓰기와 읽기 비율은 2:8 또는 1:9이다.
    • 조금 느린 쓰기를 감수하고 빠른 읽기를 선택하는 것도 하나의 방법이다.

단일 인덱스와 다중 컬럼 인덱스의 차이점

Table1 (단일 인덱스)

CREATE TABLE books_single_index(
    book_id INT NOT NULL auto_increment,
    title VARCHAR(100) NOT NULL,
    author VARCHAR(50) NOT NULL,
    genre VARCHAR(30) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY(book_id),
    KEY idx_title(title),
    KEY idx_author(author),
    KEY idx_genre(genre)
);

Table2 (다중 컬럼 인덱스)

CREATE TABLE books_multi_index(
    book_id INT NOT NULL auto_increment,
    title VARCHAR(100) NOT NULL,
    author VARCHAR(50) NOT NULL,
    genre VARCHAR(30) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY(book_id),
    KEY idx_title_author(title, author)
);

쿼리 비교
Table1에서의 쿼리:


SELECT * FROM books_single_index WHERE title='해리포터' AND author='J.K. Rowling';

Table1에서는 'title'과 'author' 각각에 대한 단일 인덱스가 있음. MySQL은 두 인덱스 중 어떤 것이 더 효율적인지 판단하여 먼저 검색하고, 그 다음 다른 컬럼을 검색한다.

Table2에서의 쿼리:


SELECT * FROM books_multi_index WHERE title='해리포터' AND author='J.K. Rowling';

Table2에서는 'title'과 'author'에 대한 다중 컬럼 인덱스가 있어서, 이 두 조건을 조합하여 검색할 경우 이 경우 Table1보다 빠르게 원하는 결과를 찾을 수 있다.

다중 컬럼 인덱스의 한계:

 
SELECT * FROM books_multi_index WHERE author='J.K. Rowling';

이 쿼리에서는 'author'만 사용되므로, 다중 컬럼 인덱스의 첫 번째 컬럼('title')이 사용되지 않았기 때문에 인덱스의 이점을 얻지 못한다.

다중 컬럼 인덱스를 사용할 때는 항상 인덱스가 생성된 컬럼의 순서를 고려해야 한다.

다중 컬럼 인덱스의 적용 순서 예제
Table (다중 컬럼 인덱스)
이 테이블은 '제목', '저자', '장르'에 대해 다중 컬럼 인덱스를 가지고 있습니다.

CREATE TABLE books(
    book_id INT NOT NULL auto_increment,
    title VARCHAR(100) NOT NULL,
    author VARCHAR(50) NOT NULL,
    genre VARCHAR(30) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY(book_id),
    KEY idx_title_author_genre(title, author, genre)
);

쿼리 비교
인덱스가 적용되는 경우:

SELECT * FROM books WHERE title='해리포터' AND author='J.K. Rowling';

이 쿼리는 인덱스에 있는 'title'과 'author' 컬럼을 순서대로 사용하므로, 인덱스가 효과적으로 작동합니다.

인덱스가 부분적으로 적용되는 경우:

SELECT * FROM books WHERE title='해리포터' AND genre='판타지';

여기서는 'title' 컬럼은 인덱스에 포함되지만, 'genre'는 'author'가 누락되어 있기 때문에, 'genre'에 대한 인덱스는 적용되지 않습니다. 따라서 이 쿼리는 'title'에 대해서만 인덱스를 활용할 수 있습니다.

인덱스가 적용되지 않는 경우:

SELECT * FROM books WHERE author='J.K. Rowling';

이 쿼리는 인덱스의 첫 번째 컬럼인 'title'이 빠져있으므로, 인덱스의 효과를 얻지 못합니다.

이 예제를 통해, 다중 컬럼 인덱스를 사용할 때는 쿼리에서 인덱스 컬럼들이 선언된 순서대로 사용되어야 함을 알 수 있습니다. 인덱스 컬럼들의 순서를 무시하면 인덱스의 효율성이 크게 감소할 수 있습니다.

profile
강민승
Step by Step goes a long way. 꾸준하게 성장하는 개발자 강민승입니다.

0개의 댓글