[우아한테크코스] 9월 15일 TIL

[db] 인덱싱 적용하기

인덱스란 값이 많은 컬럼에 대해서 인덱싱을 적용해 조회가 더 빠르도록 하는 것이다.

인덱스는 지정한 컬럼을 기준으로 메모리 영역에 일종의 목차를 생성한다.

일반적으로 조회가 삽입, 수정, 삭제보다 빈번히 일어나므로 조회의 성능을 향상시키는 것이다.

상호 연관된 컬럼에서, 인덱스를 사용하는 것이 select 연산의 성능을 향상시키는 최선의 방법이다.

Update, delete, insert도 각 행위 자체가 느린거지 그 행위를 위해 조회하는 것은 더욱 빠르다. 따라서 조건으로 update, delete를 하는 경우에는 인덱싱을 하는 것이 빠르다.

인덱스는 특정 컬럼 값을 갖고 있는 열을 빨리 찾기 위해 사용된다. 인덱스가 없으면 첫째 열부터 전체 테이블에 걸쳐서 연관된 열을 찾아야만 한다.

** 만일 대부분의 열을 접속할 필요가 있는 경우라면, 순차적인 읽기 (sequential read)가 더 빠르다. 이유는 이 방법이 디스크 검색을 최소화 하기 때문이다. 참고

• WHERE 구문과 일치하는 열을 빨리 찾기 위해.
• 열을 고려 대상에서 빨리 없애 버리기 위해. 만일 여러 개의 인덱스 사이에서 선택을 해야 한다면, MySQL은 보통 최소의 열을 찾는 인덱스를 사용한다.
• 조인 (join)을 실행할 때 다른 테이블에서 열을 추출하기 위해.
• 특정하게 인덱스된 컬럼 key_col을 위한 MIN() 또는 MAX() 값을 찾기 위해

Mysql에서 인덱스(pk, unique, index, fulltext)는 B-Tree 구조로 저장되며 root -> branch -> leaf -> 디스크 저장소 순으로 탐색한다. 공간 데이터는 r-tree, 메모리 테이블은 해시 인덱스를 지원한다.

인덱스란?

• 인덱스

  인덱스는 페이지 단위로 관리되며, 페이지는 16KB로 고정된 크기를 갖는다. 따라서 인덱스의 키가 길수록 성능상의 이슈를 가진다.

  인덱스를 걸때는 카디널리티(컬럼의 중복 수치)가 높은 것을 우선해서 잡아야 한다. 중복이 많이 되면 그 안에서 또 찾아야 하니까

• 다중 컬럼 인덱스

  하나의 인덱스는 최대 15개의 컬럼으로 구성할 수 있다.

  하나의 테이블에서 인덱스를 추가할 수록 이 전의 인덱스에 의존해서 정렬되므로 이전의 컬럼이 같은 열에서만 인덱스가 유의미하게 작용한다.

  카디널리티가 높은 순으로(중복이 낮은 순으로) 인덱스를 잡는 것이 성능이 뛰어나다.

사용하는 알고리즘

B-Tree

Root -> branch -> leaf 노드를 가지면서 key 값을 이용해 찾고자 하는 데이터를 트리구조에서 찾는 것이다.

branch, leaf 노드에 모두 key와 data를 가질 수 있다.

이진 탐색과 유사하지만 자식 노드가 2개 이상 들어올 수 있다.

어떤 값에서도 같은 시간에 결과를 얻을 수 있다는 장점이 있다. 리프노드에 있는 값만 가져올 것이기 때문이다. 이는 루트부터 리프까지의 거리가 일정하다는 것을 의미한다.

B+Tree

B-Tree의 확장 개념으로 브랜치 노드에 key만 담고, data는 담지 않는다. 리프노드에 key와 data를 저장해 리프 노드끼리 linked list를 구성하도록 한다.

data를 담지 않아 메모리를 더 확보해 하나의 노드에 더 많은 키들을 담을 수 있어 트리의 높이는 더 낮아진다.

한번의 선형탐색만 하기 때문에 B-Tree에 비해 빠르다.

어디에 적용해야 하는가?

• 규모가 작지 않은 테이블

• INSERT, UPDATE, DELETE가 자주 발생하지 않는 컬럼

• JOIN이나 WHERE 또는 ORDER BY에 자주 사용되는 컬럼

• 데이터의 중복도가 낮은 컬럼

주의사항

• between, like과 같은 조건은 인덱스 적용 불가
• =, in은 인덱스 적용 가능
• Or 절에서는 row가 늘어나므로 주의
• 컬럼 값 그대로 사용(연산 불가)

사용방법

1. 인덱스 생성

   create index 인덱스명 on 테이블명(컬럼명);
   alter table 테이블명 add index 인덱스명(컬럼명);
   

2. 인덱스 확인

   show index from 테이블명;
   

3. 인덱스 삭제

   alter table 테이블명 drop index 인덱스명;
   

4. 인덱스 타는지 확인

   explain
   select * from 테이블명 where name = ‘철수’;
   

프로젝트에서의 적용

우리가 인덱싱을 사용하게 된 이유는 예약을 조회해 오는 부분에 대한 성능 우려가 있었기 때문이다.

우리는 당일의 예약만 가지고 오면 되는데 그 당일의 예약을 가져오기 위해 db에 존재하는 모든 예약을 조회하며 날짜가 맞는지 확인해야 하는 것이 가장 큰 문제였다.

그래서 우선적으로 예약 시간에 인덱스를 걸었다. 이를 걸기 위해서 컬럼값을 변경하기 보다는 시작 날짜와 끝 날짜가 같은 우리 프로젝트의 특성상 그 날짜를 가지는 컬럼을 하나 더 만들기로 하였다.

그렇게 날짜별로 인덱스를 갖도록 하자 성능이 확 좋아졌다.

이렇게 되자, 끝 날짜 기준으로도 인덱스를 걸어서 테스트 해보고, 이메일도 인덱스를 걸어 테스트 해보았지만 성능이 더 좋게 나오지 않았다.

무작정 적용하기 보다는, 어떤 요소에 적용할 수 있을지 생각해보고, 성능의 절충안을 찾는 것이 중요한 것 같다.

참고

mysql 인덱스에 대해서

btree+인덱스