'이것이 MySQL이다'로 정리해보는 인덱스 개념

'이것이 MySQL이다' https://book.naver.com/bookdb/book_detail.nhn?bid=10652258

이것이 MySQL이다

Chapter 9 인덱스 부분 요약 정리한 내용입니다.

 

---

 

전반 개념

1. 인덱스

• 데이터를 좀 더 빠르게 찾을 수 있도록 해 주는 도구
• 속성 값을 기준으로 이미 정렬되어 있고, 그에 해당하는 레코드 주소가 있어 바로 데이터 접근이 가능
• 인덱스 검색을 위한 조건은 WHERE 절에 인덱스로 설정된 칼럼명이 나와야 함
• 인덱스로 설정된 칼럼명이 나와도 인덱스가 사용되지 않는 경우도 존재함

    SELECT * FROM tbl_name [IGNORE INDEX | USE INDEX (idx_name)] WHERE col_name;

  • 전체 데이터의 대략 20%이상을 스캔해야 한다면 MySQL이 알아서 인덱스를 사용하지 않고 전체 테이블 스캔을 수행함
    e.g. 찾으려는 범위의 데이터가 너무 많은 페이지에 분포되어 있어 너무 많은 페이지를 읽어야 할 경우
  • WHERE col_name * 1 = 1000 인덱스가 생성된 열에 함수, 연산을 가하면 인덱스를 사용할 수 없음
    • 연산을 하고 싶다면 = 부호 뒤에 연산을 하면 된다

2. 장점

• 검색 속도가 빨라짐 (단, 항상 그런 것은 아님)
  • 인덱싱보다 전체 테이블 검색 Full Table Scan이 더 나은 경우 (테이블 전체에서 자주 등장하는 데이터일 경우)
• 적은 처리량으로 결과를 얻을 수 있어 쿼리 부하가 줄어듦 → 다른 요청 처리 가능 → 전체 시스템 성능이 좋아짐

3. 단점

• DB공간을 차지함, DB의 대략 10%정도의 추가 공간이 필요함
• 처음 생성할 때 시간이 많이 소요될 수 있음
• 데이터 변경작업이 자주 일어나게 되면 오히려 성능이 나빠짐

4. 튜닝

• SQL 서버가 기존보다 더 좋은 성능을 내도록 하는 전반적인 방법론

1. 응답시간 Response Time을 줄이는 것
   • 쿼리문에 대한 응답을 얼마나 빨리 얻는가
   • 하지만 서버 입장에서 기존에는 1만 하던 것을 100을 해야 하게 되면 전체 시스템 성능은 나빠질 수도 있음을 주의해야 함
2. 서버 부하량을 최소화
   • 서버가 처리하는 총 작업량을 줄임: 시스템의 전반 성능이 향상 → 더 많은 일 처리 가능

5. 데이터 중복도

• 데이터의 종류가 얼마나 분포되어 있는가
• 중복도가 높다 = 분포도가 낮다 = Cardinality가 낮다 = 나타나는 데이터의 종류가 별로 없다
• 중복도가 낮다 = 분포도가 높다 = Cardinality가 높다 = 나타나는 데이터의 종류가 많다
• 중복도가 높은 경우, 인덱스를 사용하는 것이 효율이 없지는 않지만
  어차피 데이터를 읽기 위해 많은 페이지를 읽어야 하는 것은 마찬가지.
  인덱스 관리 비용이나 INSERT구문으로 인한 성능 저하 등을 고려하면 반드시 필요하지는 않음

 

 

Clustered Index

테이블 레코드들이, 인덱스 칼럼의 정렬 순서대로 적재되어 있는 것:
인덱스 자체가 테이블 레코드 순서와 같다

1. Clustered Index가 생성되는 경우

1. Primary Key로 지정한 열
2. UNIQUE NOT NULL로 지정한 열
   • UNIQUE (NULL)로 지정한 열은 보조 인덱스가 생성됨
3. 1, 2가 함께 있으면 Primary Key에만 생성됨
   (테이블당 1개의 primary key, clustered index가 허용됨)

2. 클러스터 인덱스 생성 순서

1. 행 데이터를 해당 열로 정렬한 후
2. 루트 페이지를 만든다

3. 구조적 특징

• 찾아보기의 끝, 즉 리프 페이지에는 실제 데이터가 저장되어 있음
• 데이터 검색 속도가 클러스터형이 더 빠를 수 밖에 없음
• 기본적으로 인덱스 칼럼 기준으로 레코드들이 오름차순 정렬됨
• 인덱스 자체의 리프 페이지가 데이터 → 인덱스 자체에 데이터가 포함딤
• 보조 인덱스보다 검색 속도는 빠르지만, 데이터 CUD는 더 느리다
• 성능은 좋지만 테이블에 딱 하나만 생성: 어떤 열에 생성하느냐에 따라 성능이 달라짐

4. 주의할 점

• 생성 시, 데이터 페이지가 전체 재정렬됨
• 많은 데이터가 이미 입력된 상태에서 인덱스를 생성하면, 데이터 양에 따라 몇시간 + α 소요 가능
  → 업무시간에 기존에 운영되던 테이블을 함부로 건드리면 안되는 것이져

 

Secondary Index 보조 인덱스

1. 생성 과정

1. 리프 페이지에는 인덱스로 선택된 칼럼의 값이 정렬되어 있고
2. 해당 칼럼 값마다 실제 데이터가 저장된 위치의 고유 값(포인터)가 생성된다
   • 데이터 위치 포인터는 주소 값(페이지 번호와 #오프셋이 기록됨)
     e.g. 1002번 페이지의 #2 (두번째 위치)에 데이터가 있다는 뜻

2. 구조 특징

• 테이블 당 여러 개 생성 가능
• 생성 직후 데이터 저장 순서에는 변화가 없음
  • Heap 영역에 있는 데이터 페이지를 건드리지 않음
  • 별도로 저장되는 페이지에 인덱스를 구성함
• 인덱스의 리프 페이지는 데이터 자체가 아니고 데이터가 위치하는 주소의 고유값 (RID)이다
• 클러스터 인덱스보다 검색 속도는 느리지만 데이터의 CUD는 덜 느리다
• 여러 개 생성할 수 있지만 남용하면 시스템 성능 저하: 꼭 필요한 열에만 생성하자

 

Clustered, Secondary Index가 동시에 존재하는 경우

1. Clustered & Secondary Index의 결합 구조

1. 보조 인덱스의 루트 페이지에서 (페이지 번호 10) 찾으려는 값이 있을 페이지(20)로 이동함
2. 페이지 20번을 앞에서부터 차례대로 읽어, 찾으려는 값의 클러스터 인덱스 키 값을 확인하고,
   클러스터의 인덱스 루트 페이지(30)로 이동함 (무조건 클러스터 인덱스의 루트 페이지)
3. 클러스터 인덱스 루트 페이지 (페이지 번호 30)를 읽어, 원하는 데이터가 있는 리프 페이지(데이터 페이지)를 확인함
4. 해당 데이터가 있는 리프 페이지(페이지 번호 40)를 차례대로 읽어 목표 데이터를 얻어냄

2. 혼합 구성하는 이유

별도 구성되어 있다면, 데이터 삽입 시 클러스터와 보조 인덱스 모두 다 재구성 해야 함

1. INSERTION으로 클러스터형 인덱스의 리프 페이지(데이터 페이지)가 재구성되면
2. 데이터 페이지의 번호 및 각 데이터의 페이지 #오프셋이 대폭 변경됨
3. 보조 인덱스의 데이터 주솟값도 대폭 변경되어야 함
4. 시스템 부하가 일어날 가능성이 높아짐

3. 혼합 구성시 주의할 점

보조 인덱스가 클러스터 인덱스의 칼럼 값을 저장하게 됨:
= 클러스터 인덱스의 속성값들이 클 수록 보조 인덱스가 차지하는 공간도 커지게 됨 → 클러스터 인덱스로 설정할 열의 속성값이 자리수가 적은 것이 바람직하다

 

Unique Index / Non-Unique Index

• Unique: 인덱스 값이 중복되지 않는 인덱스
• Non-Unique: 인덱스 데이터가 중복되는 인덱스
  → Primary or Unique Key가 아닌 칼럼에 인덱스를 생성

 

인덱스 내부 작동에 연관되는 개념

1. B-Tree(Balanced Tree, 균형 트리)

균형된 트리 구조 (자료구조)

• Node: 트리 구조에서 데이터가 존재하는 공간. 갈라지는 부분의 마디.
  • Root Node: 가장 상위 노드
  • Leaf Node, 말단 노드: 가장 마지막 노드
• MySQL에서는 Node = Page
  • 한 개 페이지가 기본 16Kbyte (최소한의 저장 단위)
  • 레코드 하나만 저장해도 한 개 페이지를 차지하게 됨

MySQL에서 인덱스 구성 시 기본적으로 B-Tree 구조를 사용함

• 데이터 검색 시 성능이 좋음 → 데이터를 찾기 위해 읽어야 하는 페이지가 줄어듦

1. 루트 페이지를 검색
2. 해당 범위의 리프 페이지로 이동
3. 원하는 데이터를 찾음

2. 페이지 분할

INSERT 작업 시 인덱스에서 페이지 분할이 일어나면서 성능이 저하될 수 있음

1. 데이터 INSERT 발생
2. 리프 페이지에 삽입할 공간이 없으면, 비어있는 페이지를 하나 더 확보함
3. 꽉 차있는 리프 페이지와 새로 생성된 페이지에 데이터를 공평하게 나눈다
4. 루트 노드에 새로운 페이지를 등록한다:
   1. 루트 페이지에 공간이 없으면, 비어있는 페이지 하나 더 확보하고
   2. 꽉 찬 루트 페이지와 새로 생성된 페이지로 페이지에 데이터를 공평하게 나누고
   3. 새로운 루트 노드를 생성하여 중간 노드가 된 페이지 데이터를 등록한다

• MySQL의 페이지 크기는
  SHOW VARIABLES LIKE 'innodb_page_size';로 확인 가능하고,
  필요하다면 4k, 8k, 16k, 32k, 64k로 변경할 수 있다

 

클러스터형 인덱스와 보조 인덱스의 비교

1. 범위로 검색하는 경우 성능차이가 나게 됨

• 클러스터형 인덱스
  애초에 정렬이 되어 있어서 해당 범위의 리프 페이지만 읽으면 데이터를 얻을 수 있다
• 보조 인덱스
  범위에 해당하는 데이터가 서로 다른 데이터 페이지에 존재함
  원하는 데이터를 찾기 위해 더 많은 데이터 페이지를 읽어야 데이터를 얻을 수 있음

2. 데이터를 INSERT할 경우

• 클러스터형 인덱스
  데이터 삽입을 위해 페이지 분할이 일어남
• 보조 인덱스
  데이터 페이지를 정렬할 필요는 없으므로 데이터 페이지의 뒤쪽 빈 부분에 삽입됨

 

인덱스 생성/삭제

1. 생성

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (index_col_name, ...)
    [index_option]
    [algorithm_option | lock_option]

• index_col_name : col_name [(length)] [ASC | DESC]
• index_type: USING {BTREE | HASH} → 생략시 기본 값은 B-Tree
• UNIQUE : 중복 허용 여부 (default: 없음- 단순 보조 인덱스) | 고유 보조 인덱스
  • 현재 중복된 값이 없다고 무조건 설정은 안됨 → 나중에 중복되는 값이 생길 가능성 있음
  • 업무 절차상 절대로 중복되지 않을 경우 (주민번호, 학번 등) 고유 보조 인덱스를 생성하는 것
• MySQL에서 생성한 인덱스를 실제 적용시키려면 먼저 분석 처리해줘야 함

    ANALYZE TABLE tbl_name;

2. 삭제

DROP INDEX idx_name ON tbl_name;

ALTER TABLE tbl_name DROP INDEX idx_name

• PRIMARY KEY에 설정되는 클러스터 인덱스의 이름은 항상 'PRIMARY'
  • PRIMARY KEY를 제거하면 클러스터 인덱스도 제거됨
• 인덱스 제거 시, 보조 인덱스부터 삭제 (클러스터 먼저 삭제 시, 보조 인덱스 재구성 후 삭제하는 격)
• 한달에 한번, 일년에 한번 등 인덱스 활용도가 떨어지면 삭제가 필요함. 유지할 필요 없다

 

 

총정리

1. 인덱스는 열 단위로 생성된다
2. WHERE 절에서 사용되는 열에 인덱스를 만들어야 한다 그 중에서도 SELECT를 자주 사용해야 생성 가치가 없다
   e.g. INSERT를 주로 하는 테이블인데 클러스터 인덱스를 생성한다면?
   +) 인덱스 생성 시점에 따라 생성 소요시간이 달라지므로 생성 시점을 잘 설계해야 함
3. 데이터 중복도가 높은 열은 인덱스를 생성해도 별 효과가 없다:
   오히려 인덱스 관리 비용때문에 인덱스가 없는 것이 나을 수도 있다
4. 외래 키 지정한 열에는 자동으로 외래 키 인덱스가 생성된다
5. JOIN에 자주 사용되는 칼럼이라면 인덱스 생성이 좋다
6. CUD(INSERT/UPDATE/DELETE)가 얼마나 발생하는지를 고려해야 한다
   인덱스는 단지 SELECT에서만 성능을 향상시킴, 변경 시 오히려 부담이 된다
   OLAP DB는 조회 위주로 변경이 잘 일어나지 않으므로 도움이 되지만
   OLTP DB는 입력&갱신 자주 발생하므로 인덱스로 성능 저하 가능성이 높다 → 신중히 결정
7. 클러스터형 인덱스는 테이블당 하나만 생성이 가능하다 클러스터 인덱스로 아주 적절한 칼럼은: (데이터 페이지가 이미 정렬되어 있으므로)
   • 범위(BETWEEN, >, < 등)로 조회되거나
   • 집계함수를 사용하는 경우
   • ORDER BY에 자주 사용되는 칼럼
8. 클러스터형 인덱스가 테이블에 아예 없는 것이 좋을 때:
   대용량의 데이터가 계속해서 입력되는 시스템에서는 페이지 분할과 데이터 정렬이 끊임없이 일어나게 된다
   → UNIQUE로 보조 인덱스를 생성하는 것이 낫다
   • 만약 NOT NULL 조건을 써야 한다면 App의 입력단에서 처리하는 방식을 사용
     e.g. 사례: 평소 가입 수가 적당했지만 이벤트로 갑자기 많은 회원이 동시에 가입하면서 시스템이 마비됨
9. 사용하지 않는 인덱스는 제거해야 함
   쿼리 분석으로 WHERE 조건에서 잘 사용되지 않는 칼럼의 인덱스는 제거해야 함
   +) 주기적인 OPTIMIZE TABLE, ANALYZE TABLE 등으로 인덱스 재구성 → 조각화 최소화