[스터디] 데이터베이스 인덱싱

suhwani·2024년 8월 10일
study
0
post-thumbnail

1. 들어가며…

이전까지는 인덱싱을 써볼 생각을 안했다.
성능을 생각하기 보다는 기능 구현에 초점을 두고 개발을 했으니까…
애초에 데이터도 얼마 없으니 DB 인덱싱에 대해 생각이 들지 않고, 전공 수업에서 들었던
B tree 써서 해주는거라고만 알고 있었는데, 인턴을 하며 많은 데이터를 보게 되었다.
데이터가 많아지니, 당연히 시간도 오래 걸리는걸 체감하게 되어, 인덱싱을 하고 싶어졌다.

2. 인덱싱이란

인덱싱을 쓰는 이유

  • DB에 데이터를 저장할 땐 넣는 순서대로 쌓이게 됩니다. 그렇다면 내가 데이터를 찾으려고 하는데, 그 데이터가 중간쯤 넣은 데이터인 경우엔 모든 데이터를 순회하면서 내가 원하는 것과 맞는지 확인해야 합니다.
  • 데이터의 갯수가 적을 때는 크게 영향을 미치지 못하지만, 데이터가 백만, 천만, 억 단위를 넘어가는 서비스에선 그만큼 Read 요청이 수없이 많은데, 이 때마다 시간이 걸리게 되면 사용자 입장에선 불편할 수 있습니다.
  • 예를 들면 책의 목차와 같습니다.
    목차가 없을 때, 책의 내용 중 하나인 “사과에 대한 얘기”를 찾고 싶다면 책을 모두 읽어야합니다.
    하지만 목차가 있다면 비교적 빨리 해당 내용에 접근할 수 있습니다.
  • 따라서 특정 컬럼 값을 비교해서 미리 Tree구조로 데이터를 정렬해놓는 방법이 널리 쓰입니다.

  • 가장 일반적인 인덱싱 유형은 B+Tree/ B Tree 입니다. 둘은 다른 유형으로 각 장단점이 있지만,
    여기서는 자세히 다루지 않겠습니다.

인덱싱은 무조건 좋을까

  1. Read 요청(데이터를 읽는 요청) 성능 향상에 효율적입니다.
    1. 이외 UPDATE, INSERT, DELETE에선 성능 저하가 있을 수 있습니다.
    2. UPDATE와 DELETE에선 특정 컬럼에 접근한 후에 해당 데이터를 조작합니다.
      이 때 접근하는 과정에선 DB 내부적으로 SELECT가 실행되고, 인덱싱이 걸려있는 컬럼으로
      접근한다면 성능 저하를 줄일 수 있습니다.
    3. INSERT 에선 새로운 데이터를 추가할 때 INSERT가 진행됩니다. 이 때 기존 인덱싱이 걸린 컬럼 값을
      비교하여 새롭게 정렬을 진행합니다. 따라서 성능 저하가 발생합니다.
  2. 인덱스 설정을 하면 공간복잡도가 증가합니다.
    1. 인덱스를 관리하기 위해선 인덱스 테이블을 저장할 공간이 필요합니다.
    2. 테이블이 클수록, 레코드가 많은수록 인덱스가 차지하는 공간도 증가합니다.
    3. 인덱스 대상 컬럼의 데이터 타입과 크기에 따라 인덱스가 차지하는 공간도 달라집니다.
    4. 복합 인덱스(=컴포지트 인덱스, 뒤에서 다룰 예정)의 경우, 더 많은 저장 공간이 필요합니다.
    5. 인덱스 컬럼에 UNIQUE 속성을 추가하면, 데이터 무결성 유지를 위해 추가적인 오버헤드가 발생합니다.
    6. DB의 10%가 더 필요하다는 것은 추정치로, 실제와는 다를 수 있습니다.
  3. 인덱스를 잘못 사용할 경우 오히려 성능이 저하됩니다.
    1. 인덱스를 설정하면, 인덱스가 걸린 테이블과 원본 데이터 테이블이 생성됩니다.
    2. UPDATE와 DELETE를 하게 되면 원본 테이블과 인덱스 테이블을 모두 수정합니다. 따라서 READ가 많은 테이블이 아닌 곳에 인덱스를 설정하면, 성능이 저하될 수 있습니다.
    3. UPDATE와 DELETE의 경우, 기존 인덱스를 삭제하는 것이 아닌 “사용하지 않음” 처리를 하게 됩니다. 따라서 10만개 데이터가 있는 테이블이더라도, 인덱스는 100만개가 넘어갈 수 있습니다.

그럼 언제 인덱싱을 사용하면 좋을까

  • 규모가 작지 않은 테이블
  • READ 작업이 많은 테이블 (= INSERT, UPDATE, DELETE가 적은 테이블)
  • JOIN, WHERE, ORDER BY가 자주 사용되는 컬럼
  • 카디널리티(데이터의 중복도와 반대)가 높은 컬럼
    • 카디널리티가 낮으면, 중복도가 높고, 카디널리티가 높으면 중복도가 낮다.
    • 카디널리티가 낮을수록 내가 원하는 데이터를 선택하는 과정에서 최대한 많은 데이터가 걸러집니다.

인덱싱의 유형

  • 커버링 인덱스

    • 일반적으로 인덱스를 설계한다면 WHERE 절에 대한 인덱스를 생각하지만, 실제로는 쿼리 전체에 대한 인덱스 설계가 필요합니다.

    • 인덱스는 데이터를 효율적으로 찾는 방법이지만, 이를 잘 활용한다면 실제 데이터까지 접근하지 않고도 데이터를 찾아올 수 있다.

    • 쿼리를 충족시키는데 필요한 모든 데이터를 가지고 있는 인덱스를 커버링 인덱스(Covering Index)라 한다.

    • 실제 데이터에 접근하지 않기 때문에, 더 빠르게 실행됩니다.

      # 커버링 인덱스 X
EXPLAIN
SELECT *
FROM board.member m
WHERE m.member_id < 10;

# 커버링 인덱스 O
EXPLAIN
SELECT m.memeber_id
FROM board.member m
WHERE m.member_id < 10;
  • 컴포지트 인덱스

    • 컴포지트 인덱스(Composite Index)는 다중 컬럼 인덱스라고도 불립니다. 2개 이상의 컬럼으로 이루어진 인덱스 입니다.

    • 인덱스는 구성된 순서에 영향을 받으며, (a,b,c)로 인덱스를 구성한다면, a에 의해 b가 정렬되고, a, b에 의해 c가 정렬되기 때문에 순서에 영향을 받습니다.

    • where, group by 를 사용할 때 인덱스 구성 순서와 동일하게 사용해야 합니다.

      # 인덱스 작용 O
WHERE a = 10 AND c = 10
WHERE a = 10 AND b = 10
WHERE a = 10 AND b = 10 AND c = 10

# 인덱스 작용 X
WHERE b = 10
WHERE b = 10 AND c = 10
WHERE c = 10

실제 성능 확인하기

  • 데이터는 500만개 이상

인덱스가 없는 경우

  • INSERT
    • Execution Time: 1.235 ms


  • SELECT
    • Execution Time: 1723.324 ms


  • DELECT
    • Execution Time: 1270.992 ms

인덱스가 잡힌 경우

  • INSERT
    • Execution Time: 7.661 ms
    • 인덱스가 없는 경우: Execution Time: 1.235 ms
    • 약 6배 이상 성능 저하

  • SELECT
    • Execution Time: 2.570 ms
    • 인덱스가 없는 경우: Execution Time: 1723.324 ms
    • 약 700배 이상 성능 향상


  • DELETE
    • Execution Time: 1.302 ms
    • 인덱스가 없는 경우: Execution Time: 1270.992 ms
    • WHERE절에서 SELECT를 하기 때문에 큰 성능 저하가 일어나지 않음

인덱스를 많이 잡으면 진짜 비효율적일까

  • INSERT
    • Execution Time: 10.947 ms
    • 인덱스가 없는 경우: Execution Time: 1.235 ms
    • 인덱스가 1개 있는 경우: Execution Time: 7.661 ms
    • 성능 저하가 발생한다.


커버링 인덱스

  • 커버링 인덱스가 아닌 경우
    • SELECT
    • Execution Time: 0.848 ms


  • 커버링 인덱스인 경우
    • SELECT
    • Execution Time: 0.126 ms
    • 커버링 인덱스가 아닌 경우: Execution Time: 0.848 ms
    • 약 7배 이상 성능 향상


인덱스를 타지 않는 쿼리

쿼리문을 작성할 때도 인덱스가 적용이 되는지 확인해야 한다.
인덱스를 설정했어도, 인덱스를 타지 않는 쿼리문을 사용한다면 인덱스를 설정하지 않은 것과 다를 바 없다.

  1. 인덱스 컬럼의 변형
select * from table where LOWER(name)  ='word';
select * from table where idx - 1 = 5;

다음과 같이 인덱스가 잡힌 컬럼을 변형을 가하여 WHERE문을 작성한다면 데이터베이스는 인덱스를 이용하지 않는다.

  1. 인덱스 컬럼의 데이터 변형
select * from table where age = '30'

다음과 같이 age는 Integer 라고 가정했을 때, String 값을 넣어서 WHERE문을 작성한다면 인덱스를 이용하지 않는다. 따라서 정확한 데이터 값과 데이터 타입을 넣어줘야 한다.

  1. NOT 또는 IN 연산자 사용
SELECT * FROM users WHERE NOT age = 30;

NOT일 경우에도 인덱스를 타긴 타지만, 일반적으로, NOT에 사용된 값이 아닌 데이터의 비율이 높은 경우가 많기 때문에 인덱스를 타지 않는 경우가 많다. 이러한 경우 DBMS는 인덱스를 사용하는 것보다 전체 테이블을 스캔하는 것(Full Table Scan)이 더 효율적이라고 판단할 수 있다.

마찬가지로 IN일 경우에도, IN에 포함된 데이터들의 비율이 매우 높다면 FULL SCAN을 하는 것이 낫다고 DBMS가 판단하면 인덱스를 타지 않는다.

  1. 와일드 카드 LIKE문장 범위를 전체로 지정할 경우
select * from table where name like '%word';

문자열로 이루어진 값을 인덱스로 잡았을 때, %가 앞쪽에 사용되면 정렬 순서를 사용할 수 없으므로 테이블 FULL SCAN이 이루어진다. word 앞에 뭐가 올 지 모르기 떄문에 인덱스를 사용할 수 없다.

select * from table where name like 'word%';

이런 경우는 문자열 정렬 순서를 그대로 이용할 수 있기 때문에 인덱스를 탈 수 있다.

  1. 복합 컬럼 index에서 순서가 잘못되어 index 가 적용 되지 못하는경우
결합 인덱스 = 컬럼1,컬럼2
select * from table where 컬럼2

복합 인덱스는 인덱스를 지정하는 컬럼1과 컬럼2 값을 합쳐서 인덱스를 생성하기 때문에 순서가 바뀐다면 인덱스를 적용할 수 없다. 하지만 복합 인덱스 설정을 (컬럼1, 컬럼2)로 하고, WHERE절에 컬럼1을 지정한다면 인덱스를 사용할 수 있다.

  1. OR 조건을 사용할 경우
SELECT * FROM users WHERE age = 25 OR city = 'Seoul';

age와 city 각각 인덱스가 설정되었다고 해도, OR 조건이 있는 쿼리는 복잡도가 증가할 수 있기 때문에, DBMS는 전체 테이블을 스캔하는 것이 더 빠르다고 판단할 수 있다. 물론 인덱스를 이용할 수도 있지만, 안하는 경우가 대부분이다.

  1. NULL 값을 사용하는 경우
SELECT * FROM table_name WHERE name = NULL;     -- 인덱스 활용X
SELECT * FROM table_name WHERE age IS NULL;     -- 인덱스 활용X
SELECT * FROM table_name WHERE age > 0;         -- 인덱스 활용O

NULL 값은 인덱스에 저장되지 않기 때문에, NULL을 사용하면 인덱스를 이용할 수 없다.

  1. Optimizer 의 선택
select * from table where name ='word' and id ='elky';

인덱스가 name 과  id로 2개가 있을 경우 id나 name 인덱스 중 하나가 선택될 수도 있고, 둘 다 선택될 수도 있다. 어떤 방식으로 선택하는냐가 속도에 중요할 수도 있다. 즉 실행 계획을 추적해서 원하는 결과가 나오도록 관리가 필요하다. 따라서 Optimizer가 어떻게 계획을 세우고 실행하냐에 따라서 실행 시간이 달라질 수 있다.

profile
suhwani
Backend-Developer
이전 포스트

[스터디] gRPC에 대해서...with Python

다음 포스트

[스터디] OSI 7계층 & TCP/IP 4계층

0개의 댓글