인덱스란?

  • DB에서 보다 더 빠른 조회를 하기 위해서 특정 컬럼에 대해서 물리적인 주소를 저장해 놓는 것
  • 인덱스는 주로 WHERE절의 최적화를 위해 설계되는 것이 주류이다.
  • 과도한 인덱스 생성은 오히려 성능을 떨어뜨리는 효과를 초래한다.
    • 인덱스 또한 DB의 테이블 객체이기 때문에 저장공간을 차지한다. 그래서 과도한 인덱스는 불필요한 공간을 차지한다.
    • 테이블에 쓰기 작업을 하게될 경우 인덱스 또한 동기화 시켜줘야하기 때문에
  • 인덱스는 독립적으로 존재할 수 없다. -> 다른 테이블에 의존적이다.

인덱스의 동작 방식

인덱스의 동작 방식을 알기 전에 먼저 클러스터링 인덱스(Clustering Index)와 세컨더리 인덱스(Secondary Index, Non Clustering Index) 를 알아볼 필요가 있다.

 

클러스터링 인덱스 ( Clustering Index )

  • PK(또는 히든 키, 유니크 키)와 실제 물리적 주소가 저장된 인덱스이다. 
  • 각 테이블 마다 하나씩만 존재한다.
  • 실제 물리적 순서와 PK의 순서대로 저장되어 있어 순차 IO의 경우 사용되기도 한다.
    • 데이터가 삽입, 삭제 시에 물리적 순서가 변경되기 때문에 논리적 순서가 적힌 테이블은 이를 항상 동기화 시켜줘야한다. 클러스터링 인덱스가 동기화 되면 클러스터링 인덱스의 위치를 가리키는 논 클러스터링 인덱스 또한 이를 동기화해야하기 때문에 과한 인덱스 사용을 지양하는 이유가 바로 이것이다.

세컨더리 인덱스 ( Secondary Index, Non Clustering Index)

  • 우리가 직접 설정하는 인덱스들을 세컨더리 인덱스라고 한다.
  • 보통 한 테이블당 3~5개의 인덱스를 사용한다.
  • 특정 칼럽의 값과 클러스터링 인덱스의 위치가 저장된다.

 

인덱스 동작 순서

  1. 먼저 인덱스를 통해 해당 클러스터링 인덱스 주소로 접근한다. (옵티마이저가 인덱스를 어떻게 탈지,풀 테이블 스캔을 할지를 정한다.)
  2. 클러스터링 인덱스를 타고 내려가 실제 데이터의 물리적 주소로 접근한다.
  3. 실제 데이터 접근

 

DB 자료구조

DB에서는 데이터들을 효율적으로 저장하기위해 여러가지의 자료구조들을 쓴다. 나는 그 중에서도 3개 정도의 DB의 자료구조를 알아보려고 한다.

 

Hash Map Index

  • Key - Value(해시 값) 로 이루어진 자료구조형
  • 단일 값에대한 접근이 매우 빠르다 -> 시간 복잡도 O(1)이다.
  • 값이 조금이라도 바뀌면 해시값이 완전히 바뀌어 버리기 때문에 범위 탐색에 제한이 있다.

B-Tree

  • 균형 트리의 한 종류료, 데이터 베이스에서 주로 사용되는 자료구조이다.
  • 데이터가 계층적으로 정렬되며, 각 노드는 여러 개의 키와 포인터를 갖는다.
  • 여기서 노드는 데이터가 저장되는 공간이다.
    • 노드는 루트 노드, 중간 노드, 리프 노드로 이루어져 있다.
    • 모든 노드에 데이터를 저장할 수 있다.
    • MySQL에서는 페이지라고 부른다.
  • 범위 검색에 최적화 되어있지만, 삽입과 삭제가 어려운 단점이 있다.
  • 시간 복잡도는 O(n)이다.

B+ Tree

  • 자식 노드가 2개 이상인 B-Tree를 개선한 구조이다.
  • 노드는 내부 노드와 리프 노드로 나뉜다.
  • 데이터를 리프 노드에만 저장하게끔 하여 내부노드는 더 많은 키들을 저장할 수 있게 되었다.
  • 데이터 노드들은 내부 도드와 크기가 같지 않아도 된다.
  • 리프 노드들은 Linked List로 연결되어 있다. 이로 인해 평소 부등호(>,<)를 이용한 순차 검색 연산이 많이 발생하는 DB에서 보다 용이하게 사용할 수 있다.
    • 다른 리프 노드로 이동하기 위해 상위 리프 노드를 타야하는 불필요한 과정을 지닌 B-Tree와 다르게 B+ Tree는 위와 같은 점을 이용하여 불필요한 이동을 줄일 수 있다.
  •  리프노드까지 가야해서 시간 복잡도가 O(log n)이라는 단점이 생기지만 해시 맵보다 인덱싱이 더욱 적합하게 되어있다.

인덱스 설정 기준

  • 카디널리티
    • 칼럼이 갖고 있는 중복도를 나타내는 수치이다.
    • 카디널리티가 높을 수록 중복도가 낮다 -> 인덱스 설정하기 좋다. (값들이 대부분 다른 값을 가진다.)
    • 카디널리티가 낮을 수록 중복도가 높아지는 것을 볼 수 있다. 
  • 선택도
    • 하나의 칼럼 값을 선택했을 때 많은 수의  Row를 가져오는 수치
    • 식 : 컬럼의 특정 값의 Row 수 / 전체 Row 수 * 100
    • 선택도가 높을수록 많은 Row들을 가져오는 것을 볼 수 있다.
    • 선택도가 낮을수록 적은 Row들을 가져오는 것을 볼 수 있다. -> 인덱스 설정라기 좋다.
  • 조회 활용도
    • 해당 컬럼이 얼마나 많이 사용되는지를 나타내는 척도이다. -> 조회 활용도가 높을수록 WHERE 의 대상 칼럼으로 많이 활용된다
  • 수정 빈도
    • 얼마나 수정을 많이하는지를 나타내는 척도이다 -> 수정 빈도가 낮을 수록 인덱스 선택 확률이 올라간다. (수정이 적을수록 좋기 때문)

커버링 인덱스

  • 일반적으로 인덱스 설계는 WHERE절에 대한 인덱스 설계를 이야기하지만, 사실 쿼리 전체에 대한 인덱스 설계 또한 필요한 경우가 있다.
  • 인덱스의 경우 데이터를 효율적으로 찾는 방법이지만, MySQL의 경우 인덱스 안에 포함된 데이터를 사용할 수 있다. 이를 잘 활용하면 실제 데이터까지 접근할 필요없이 응답할 수 있다.
    -> 이처럼 쿼리를 충족시키는데 필요한 모든 데이터를 갖고 있는 인덱스를 커버링 인덱스라고 한다.

그렇다면 실제 MySQL DB에서 커버링 인덱스는 어떻게 표기될까?

경우에 따라 Explain의 Extra 영역에 다양하게 인덱스 활용방법이 표기되는데 Explain을 이용하여 비교해 보자.

 

Using Index

  • 커버링 인덱스를 말하는 것이 바로 이 Using Index이다.
  • 오로지 인덱스만을 이용하여 커버링 인덱스를 수행한 것을 뜻한다.
  • 아래 질의문은 인덱스(idx_ages)만으로도 충분히 응답할 수 있어 Using Index라고 나오는 것을 볼 수 있다.
explain
select age from crew where age =50;

 

 

Using Where

  • 인덱스를 통해 접근했으나, 인덱스만의 데이터로는 부족하여 실제 데이터에 접근하는 것
  • 예를 들어 인덱스 설정 컬럼은 age로만 설정하였는데 select 한 것은 track, age 그리고 WHERE절에 age에 대한 조건이 있는 경우
explain
select * from crew where track = "FRONTEND" and age = 41;

Using Were

Using Index Condition 

  • Using Index Condition는 '인덱스 컨디션 푸시다운 인덱스'를 뜻한다.
  • MySQL에서는 아래와 같이 스토리지 엔진에서 실제 데이터를 가져와서 MySQL 엔진에서 정제가 더 필요한 경우 한 번 더 정제하는 과정을 거친다. 그렇다면 언제 더 정제하는 경우가 필요한 걸까?
    • MySQL 스토리지 엔진의 경우 인덱스 범위 조건을 수행하지 못하는 경우가 있다. 이를 MySQL에서 해주는 것이다.
    • MySQL 5.5 이전 버전의 경우에는 이렇게 스토리지 엔진이 인덱스 범위 조건을 수행하지 못하는 경우 조건 자체를 전달하지 못했었다. 그래서 해당 조건을 제외하고 가져오다 보니 결국 인덱스의 값을 이용하지 못하고 실제 테이블까지 가서 데이터를 조작했었다. -> 인덱스의 칼럼 값만을 조회했음에도 결국 실제 테이블로 접근하여 정제과정을 또 거쳤기에 Using Where 이었다.
    • 하지만 5.5버전 이후에는 인덱스 범위 조건을 수행하지 못하더라도 조건 자체를 넘길 수는 있어서 인덱스 내에서 해결할 수 있게 변경되었다. -> Using Index Condition
    • 아래의 질의문을 이용해 해당 기능을 껐다 킬 수 있다. (Using Index Condition이 디폴트이다)
create index idx_ages_name on crew(age,nickname);

explain
select * from crew where age = 41 and nickname like "F%";
-> 5.5 이전 Using Where / 5.5 이후 Using index Condition

set optimizer_switch = 'index_condition_pushdown=on';
set optimizer_switch = 'index_condition_pushdown=off';

 

Using Index Condition On!
Using Index Condition Off!

 

type = Index

  • type 란에 index가 있는 것을 볼 수 있는데 이는 '인덱스 풀 스캔'을 의미한다.
  • 이는 인덱스에 수직적인 접근이 아니라 수평적인 접근을 하는 것을 볼 수 있다.
  • 예시로 crew 라는 테이블에서 age 값들을 모두 가져오는 것을 예로 들 수 있다. (모든 인덱스들의 값을 가져온다)
explain 
select age from crew;

 

 

다중 컬럼 인덱스

  • 여러개의 칼럼으로 인덱스를 만드는 것을 뜻한다. (MySQL의 경우 최대 15개까지의 컬럼을 지정할 수 있다.)
  • INSERT,DELETE,UPDATE 시 많은 자원을 필요로 하기에 신중히 고려하여 결정해야 한다. -> 수정이 적은 것을 대상으로 설정해야한다.
  • 여러 개의 단일 인덱스 보다 다중 칼럼 인덱스를 고려해 보는 것이 좋다.

 

 

 

 

 

 

참고 자료 : https://rachel0115.tistory.com/entry/MySQL-%EC%9D%B8%EB%8D%B1%EC%8A%A4-INDEX-%EC%A0%95%EB%A6%AC-%EB%8F%99%EC%9E%91-%EB%B0%A9%EC%8B%9D-%EC%83%9D%EC%84%B1-%EC%82%AD%EC%A0%9C-%EC%84%A4%EA%B3%84, https://mangkyu.tistory.com/96,
https://velog.io/@jwpark06/%ED%9A%A8%EA%B3%BC%EC%A0%81%EC%9D%B8-DB-index-%EC%84%A4%EC%A0%95%ED%95%98%EA%B8%B0,
https://yurimkoo.github.io/db/2020/03/14/db-index.html, https://jojoldu.tistory.com/476,
https://jojoldu.tistory.com/474, https://velog.io/@tothek/%EC%9D%B8%EB%8D%B1%EC%8A%A4-%EA%B8%B0%EB%B3%B8-INDEX-SCAN%EC%A2%85%EB%A5%98

 

'DB' 카테고리의 다른 글

낙관적 락 과 비관적 락  (0) 2024.11.18
인덱스 (index) (2)  (1) 2024.11.15
순차 IO / 랜덤 IO  (0) 2024.11.14
구조적 질의어 (SQL, Structured Query Language) ( 3 )  (1) 2024.11.08
구조적 질의어 (SQL, Structured Query Language) ( 2 )  (0) 2024.11.06

티스토리툴바