우재남님의 "혼자 공부하는 SQL"을 기반으로 학습한 게시물입니다.
📕 목차

1. What is Index?
2. 내부 작동
3. Practice

1. What is Index?

 

📌 Concept
  • 데이터 저장(INSERT, UPDATE, DELETE) 성능을 희생하고 조회(SELECT) 성능을 높여주는 자료구조
  • 책 뒷편의 '찾아보기'와 비슷하다
    • 효율적인 검색을 위해 자주 찾는 column을 Index로 생성해 놓는다.
      • Index는 Table의 Column 단위로 생성된다.
      • 하나의 column에 여러 개의 Index를 생성하거나, 여러 개의 column을 묶어 하나의 Index로 만들 수도 있으나 드문 경우다.
    • 알파벳 순서 또는 한글 순서 등의 형태로 정렬되어 있다.
    • 용어를 요약해두지 않고, 어느 페이지에 있는지 간략한 페이지 번호만 나타낸다.
    • 색인 정보가 있는 페이지는 몇 페이지 안 된다.
    • 너무 여러 곳에 등장하는 용어는 색인에 나타나지 않는다.
  • Index가 없어도 데이터베이스가 동작하는 데 문제는 없다
  • 장점
    • 어떤 항목을 SELECT하는 데 있어 몇 분 단위의 소요 시간을 0. 초 단위로 줄일 수 있다.
    • 매우 빠른 응답 속도와 Query 부하를 압도적으로 감소시킬 수 있다.
    • 데이터베이스 크기가 억대 단위로 클 수록 필수적으로 요구된다.
  • 단점
    • Index 또한 하나의 데이터 덩어리이므로, 데이터베이스 전체 크기의 10%를 할당해주어야 한다.
    • 너무 자주 등장하거나, 변경 작업(INSERT, UPDATE, DELETE)이 빈번한 Index를 등록하면 성능이 크게 감소할 수도 있다.
      • MySQL은 Index와 전체 테이블 검색 중 더 빠른 경로를 알아서 판단하므로, 불필요한 Index가 적어도 시간 낭비를 불러오는 것은 방지할 수 있다.
    • Index를 생성하는 데 시간이 소요된다. (때론 몇분 씩 소요)

 

📌 Type
  • Clustered Index : 처음부터 정렬되어 저장된 형태 (Table 당 한 개만 생성 가능)
  • Secondary Index : 찾아보기가 따로 있는 형태 (Non-clustered Index와 동일하다.)

 

🟡 자동으로 생성되는 Index

SHOW INDEX FROM [Table 이름];

  • Key_name에 PRIMARY라고 써 있다면 Clusted Index라 봐도 무방하다.
  • Column_name에 명시된 column에 Index가 만들어져 있다.
  • Non_unique가 0이면 고유하고, 1이면 고유하지 않다는 의미다.
  • Index_type으로 BTREE 방식을 사용한다.

 

ALTER TABLE member
	MODIFY COLUMN [필드이름] VARCHAR(255) UNIQUE;
    
SHOW INDEX FROM member;

  • Column을 Unique로 지정해도 자동으로 Index가 생성된다.
  • Key name에 Column name이 쓰여있으면 Secondary Index라 보면 된다.

 

🟡 자동으로 정렬되는 Clustered Index

💡 Clustered Index는 Table당 하나만 만들 수 있다.
CREATE TABLE user (
    user_id     CHAR(8),
    user_name   CHAR(10),
    user_number INT,
    addr        CHAR(2)
);

  • Primary Key를 지정하지 않으면 데이터를 저장한 순서대로 저장한다.

 

ALTER TABLE user
    ADD CONSTRAINT
    PRIMARY KEY(user_id);

  • user_id를 pk값으로 지정하면, 해당 column을 기준으로 Table이 정렬된다.

 

🟡 정렬되지 않는 Secondary Index

ALTER TABLE user
    ADD CONSTRAINT
    UNIQUE(user_id);
    
ALTER TABLE user
    ADD CONSTRAINT
    UNIQUE(user_name);

  • 여러 개의 Column에 Unique를 설정할 수 있다.
  • Table이 자동으로 정렬되지는 않는다.
  • Secondary Index를 만들 때마다 Database의 공간을 차지하므로 꼭 필요한 column에만 생성하는 것이 좋다.

 

2. 내부 작동

 

  • 가장 보편적으로 균형 트리(B-Tree, Balanced Tree) 데이터 저장 방식을 사용한다.
    • 데이터가 정렬된 상태를 유지한다.
    • 어떤 값에 대해서도 같은 시간에 대한 결과를 얻을 수 있다. O(lgN)
  • Hash Index와 Fractal-Tree Index 같은 Algorithm도 있다.

 

📌 Mechanism

🟡 B-Tree 개념

출처 : Real MySQL 위키북스 백은빈, 이성욱 지음

  • 용어
    • 루트 노드(root node) : 가장 상위 노드
    • 브랜치 노드(branch node) : 루트 노드와 리프 노드가 아닌 중간 노드
    • 리프 노드(leaf node) : 가장 하위 노드
    • 페이지(Page) : MySQL에서 Node를 호칭하는 방법. 최소한의 저장 단위(16Kbyte)
  • 반드시 root page부터 탐색을 시작한다.
  • 레코드 탐색 순서
    1. Root Page에서 "Gad"라는 Index key를 찾는다.
    2. Page1에 없으므로 'A'와 'J' 사이에 'G'가 있으므로 "Aamer"가 가리키는 Page(2)로 향한다. (정렬되어 있으므로 판단 가능)
    3. Page(2)에서 "Gad"를 발견한다. (없으면 위의 과정과 동일한 논리로 다음 Page로 이동ㅎㄴ다.)
  • B-Tree를 사용하지 않으면 전체를 처음부터 검색하는 방법밖에 없다.
  • 어떤 데이터를 조회하든지, 사용하는 조회 과정의 길이 및 비용이 균등하다.

 

🟡 B-Tree 페이지 분할

  • Index를 구성하면 변경작업 성능이 나빠지는 이유가 페이지 분할 과정이 수반되기 때문이다.
  • 하나의 Page에 값을 저장하다고 빈 공간이 없을 경우 "새 페이지 준비 - 데이터 분리 - 새로운 페이지 등록"과 같은 일련의 과정들이 수반된다.
  • 값 하나를 추가하기 위해 다수의 Page를 추가하고, 여러 분할 과정을 거쳐야할 수도 있다.

 

 

[MYSQL] 📚 인덱스(index) 핵심 설계 & 사용 문법 💯 총정리

인덱스의 개념 인덱스란 데이터의 저장(INSERT, UPDATE, DELETE) 의 성능을 희생하고 그 대신에 데이터의 읽기 속도를 높이는 테이블의 동작속도(조회)를 높여주는 자료구조이다. 쉽게 예를 들어보면

inpa.tistory.com

 

 

MySQL 인덱스 구조와 원리의 이해

인덱스란? 어떤 문제를 해결하기 위한 기술인가 관계형 데이터베이스는 기본적으로 데이터 영속화라는 목적이 있는 기술이다. 쉽게 말하면 어떤 애플리케이션에 필요한 데이터를 유지(저장)하

jeong-pro.tistory.com

 

🌱 (추가) B-Tree Index 사용에 영향을 미치는 요소
  1. Index key 값의 크기
    • Index key 값이 클 수록 하나의 Page가 가질 수 있는 키의 개수(16KB)가 줄어들어 효율이 감소한다.
  2. B-Tree의 깊이
    • Index Tree의 깊이가 깊어질 수록 찾아가는 단계가 많아져서 오버헤드가 발생한다.
    • Index key 값의 크기를 줄여도 깊이를 최소화하는 것이 최선의 방법이다.
  3. 선택도(Selectivity)
    • Index로 정한 key 값이 얼마나 Unique한가, 골고루 퍼져있는가의 척도
    • 걸러지는 양이 많을 수록 쓸 데 없는 데이터를 읽지 않아도 되므로 성능상 이점을 얻는다.
  4. 읽어야 하는 Record 수
    • 읽어야 하는 record 수가 절반이 넘는다면 굳이 Index를 읽는 것은 비효율적이다.
    • 통계적으로 전체 record의 20% 미만의 record를 읽을 때 효율적으로 동작한다.
    • Optimizer가 알아서 최적화 해준다.
  5. Unique 속성
    • Unique 하지 않은 Index라면 1개를 찾아내도 끝까지 찾아봐야 하므로 최적화가 힘들다.

책에서는 WHERE 절에서 사용되는 column에 Index를 만드는 것이 좋다고 한다.

 

📌 Structure

🟡 Clustered Index

  • 처음부터 정렬되어 저장된 형태
  • Table 당 한 개만 생성 가능
  • Index Page의 Leaf Page는 데이터 그 자체가 된다.
  • 대용량 데이터가 이미 입력된 상태에서 Clustered Index 생성은 Table 전체 정렬 과정으로 인한 심각한 시스템 부하를 줄 수 있다.
  • 저장하는 정보 공간을 적게 사용하면서 Table 공간 자체를 활용한다.
  • Secondary Index보다 검색속도는 빠르지만, 입력/수정/삭제는 더 느리다.
  • Primary Key나 UNIQUE & NOT NULL인 Column도 없으면, 임의의 보이지 않은 column을 자동으로 만들어 생성한다.

 

🟡 Secondary Index

  • 데이터 Page를 그냥 둔 상태에서 별도의 Page에 Index를 구성한다. (Table 자동 정렬이 안 되는 이유)
  • 데이터 Page에 바로 연결시키지 않고 Reaf Page를 만들어 Mapping을 하고 정렬시킨다.
  • Secondary Index 자체는 정렬된 형태로 저장된다.
  • RID인 "페이지 번호 +#위치" 형태로 데이터의 위치가 저장되어 있다.

 

🌱 (추가) 혼합형 Index

https://inpa.tistory.com/entry/MYSQL-%F0%9F%93%9A-%EC%9D%B8%EB%8D%B1%EC%8A%A4index-%ED%95%B5%EC%8B%AC-%EC%84%A4%EA%B3%84-%EC%82%AC%EC%9A%A9-%EB%AC%B8%EB%B2%95-%F0%9F%92%AF-%EC%B4%9D%EC%A0%95%EB%A6%AC

어우 머리 아파 🙄

이미지 출처를 타고 들어가면 조회 과정까지 친절하게 설명해주고 계신다.

재밌는 점은 둘을 혼합해서 쓸 때는 Secondary Index가 RID가 아닌 PK를 저장한다는 점이다.

설명을 보니 알 거 같긴 한데 너무 깊게 들어가는 거 같아서 나는 멈췄다.

 

내가 데이터 관리자를 할 건 아니니까..

 

3. Practice

 

📌 생성과 제거 문법
-- Index 생성
CREATE [UNIQUE] INDEX [인덱스_이름]
    ON [테이블_이름] [열_이름] [ASC | DESC]

-- Index 제거
DROP INDEX [인덱스_이름] ON [테이블_이름]
  • 생성
    • UNIQUE를 생략하면 중복이 허용된다.
    • ASC(오름차순)와 DESC(내림차순)을 정할 수 있는데, 기본값으로 ASC고 대부분 그렇게 한다.
    • Secondary Index를 만들고 적용시키려면 ANALYZE TABLE 문으로 Table을 분석/처리해야 한다.
  • 삭제
    • Primary key, Unique Key로 자동 생성된 Index는 DROP INDEX로 지울 수 없다.
    • ALTER TABLE로 Primary Key나 Unique Key를 제거해야 삭제가 가능하다.
    • 하나의 Table에 Clustered Index와 Secondary Index가 모두 있다면 Secondary Index를 먼저 제거하라
      • Clustered Index를 제거하면 Table 내부가 재구성된다.

 

✒️ Table에 생성된 Index 크기 확인

SHOW TABLE STATUS를 이용하면 된다.
실제론 16KB도 안 되는 데이터밖에 안 들어가 있는데 불구하고 Page 단위인 16KB가 할당되어져 있다.
심지어 Secondary Index는 2개 만들었다고 32KB나 차지하고 있다.

• Data_length : Clustered Index 크기
• Index_length : Secondary Index 크기

 

📌 Index 사용 분류

1️⃣ 인덱스 풀 스캔(Index Full Scan)

  • Index를 처음부터 끝까지 전부 다 읽는 것이다.
  • "Index를 탔다"라는 범위에 속하지 않는다.
  • WHERE 절을 사용하지 않은 경우에 사용된다.
  • WHERE 절에서 column에 연산이 가해져도 Index를 사용하지 않는다.
    • "WHERE column >= 5"나 "WHERE column >= 5*2"는 가능하다.
    • "WHERE column*2 >= 5"는 Index를 사용하지 못한다.
  • Index를 사용하는 것보다 Full Table Scan이 효율적인 경우에도 MySQL이 자동이로 이 방식을 택한다.

 

2️⃣ 인덱스 레인지 스캔(Index Range Scan)

  • 구체적으로는 Record를 한 건만 읽는 것과 여러 건을 읽는 것은 다른 이름으로 분리한다. 
  • 특정 범위 내에서 "일부"만 읽어 검색했을 때를 말한다.
  • "인덱스를 탔다!"라고 하면 보통 Index range scan이 수행되었을 때를 말한다.

 

이외에도 Loose Index Scan, Index Skip Scan이 있는데, 별로 내가 알 필요 없다고 생각해서 추가적으로 파보진 않았다.

 

저작자표시 비영리

티스토리툴바