index - mysql

INDEX란?

RDBMS에서 인덱스(INDEX)는 데이터베이스의 특정 테이블의 한 또는 여러 열에 대한 자료 구조로,
레코드(ROW)를 빠르게 찾는데 도움을 준다.
인덱스는 대형 데이터베이스에서 데이터 검색 속도를 크게 향상시킬 수 있다.

 

 

 

INDEX를 생성하는 방법

1. 이미 테이블이 존재한다면?

 - 데이터의 중복을 허용하는 컬럼의 인덱스를 거는 경우.

CREATE INDEX [index의 별칭] ON [table명] ([column명])
예> create index member_name_idx name on member (name);

- 데이터의 중복을 허용하지 않은 컬럼(들)의 인덱스를 거는 경우.

CREATE UNIQUE INDEX [index의 별칭] ON [table 명] ([column명], [column명], ...)
예> create unique index member_addr_name_idx on member (addr, name);>>주소와 이름의 조합은 unique하다는 가정

2. 테이블을 만드는 과정에서 인덱스를 건다면?

CREATE TABLE member(
id int primary key,   << ** 자동으로 index가 생성
... ,
INDEX [index의 별칭(생략가능)] (column명),
UNIQUE INDEX [index의 별칭(생략가능)] ([column명], [column명], ...) 
                ↑↑↑↑↑ multicolumn index, composite index 불림
);

3. 해당 테이블에 인덱스가 어떤 것들이 걸려 있는지 확인하고 싶다면?

SHOW INDEX FROM [table 명];

 

 

INDEX를 쓰는 이유?

• 조건을 만족하는 튜플(특정 데이터) 또는 튜플들(여러 데이터)를 빠르게 조회하기 위해서 사용
• 빠르게 정렬(order by)하거나 그룹핑(group by) 하기 위해서 사용

 

table : member

mysql > SELECT * FROM member WHERE name = 'hongs';

이렇게 되면, 테이블의 전체를 조회해서 데이터를 찾아오는 FULL SCAN 방식을 선택하게 된다.

이는 테이블의 데이터의 양만 큼의 시간이 소요되게 되는데  ( O(N) ),

만약 여기 테이블에서 name에 INDEX가 걸려 있었다면?

B-tree 기반의 인덱스의 경우, O(logN) 만큼의 시간이 걸리게 된다.

 

 

1. 순서대로 정렬된 인덱스를 기준으로 중앙 값을 조건과 비교를 한다.

2-1. 조건보다 순서상 작다면, 중앙 값을 기준으로 위의 row에서 다시 중앙 값을 찾아 들어감

2-2. 조건보다 순서상 크다면, 중앙값을 기준으로 아래의 row에서 다시 중앙 값을 찾아 들어감

 

3. 2번 과정을 반복하여 범위를 좁혀 나가면서 기존의 FULL SCAN보다 많은 양의 데이터가 존재하는 TABLE에서 원하는 데이터를 쉽게 찾아오는 것이 가능!!

 

여기서 POINTER란, 실제 데이터가 위치하고 있는 곳을 가리키는 것을 말한다.

 

 

 

주의 사항

• 만약 a, b 가 인덱스가 걸려 있는 경우, 조건에 a만 존재한다면 해당 multicolumn인덱스를 사용해도 성능적으로 괜찮다. 하지만, 조건에 b가 함께 걸려 있다면, 조건 b에 대해서는 full scan을 하므로 성능적으로 좋지 않다.
• 인덱스를 걸었다면, 그 순서도 중요하다. a, b에 인덱스가 걸려 있는 경우, 기본적으로 a를 기준으로 정렬이 되어 있다.하지만 b에 대해서는 a가 동일한 경우에만 순서가 정렬이 되어 있으므로, 내가 걸어준 index의 순서를 고려하여 인덱스를 적용해야 한다.
• 내가 의도한 대로 인덱스를 타는지 확인하고 싶다면? ->
  EXPLAIN 키워드를 사용하여 해당 쿼리를 날려보면 알수 있다.
  예> EXPLAIN SELECT * FROM member where name = 'hongs'
  이렇게 index를 잘 타고 있는지 확인을 해야하는 이유는, 기본적으로 RDBMS의 optimizer가 알아서 적절하게 index를 선택하기 때문인데 내가 의도한 것과 다르게 세팅이 되어 쿼리가 수행되고 있을 수 있기 때문이다.

만약 내가 의도한 index로 타게 하고 싶다면?
select * from member USE INDEX(index 별칭) where ... ;

그럼에도 optimizer에 의해 의도대로 되지 index를 사용하고 있지 않다면,
select * from member FORCE INDEX(index 별칭) where ... ;

 

 

지금까지 설명한 인덱스를 생각해보면, INDEX는 모든 컬럼에 전부 걸어주면 좋은게 아닐까라는 생각이 들 수 있다.

하지만, INDEX는 별도의 공간에 INDEX를 타게 하기위한 테이블이 생성되고, 원본 테이블의 변화가 있을 때마다 관련된 INDEX들도 변화를 적용해야한다. 정리하면,

1. INDEX는 필요한 경우에만 세팅하는 것이 중요!
2. TABLE의 변화가 있을 때마다, INDEX도 함께 변경됨! >> 속도 저하
3. 추가적인 저장 공간 차지 >> 별도의 INDEX 테이블 생성

결론 : 불필요한 INDEX를 만들지 말자!

 

 

** INDEX를 활용한 매우 빠른 조회 성능 올리기 - Covering index

Covering  index란,  내가 조회를 원하는 컬럼에 대해서 index가 되어 있을 경우, pointer를 통해 실제 테이블을 들르지 않아도 바로 조회가 가능한 것을 말한다.

 

 

** Full scan이 더 좋은 경우

인덱스를 타면 빠른 조회가 가능하다고 하지만, 몇몇 경우에는 full scan이 더 빠른 경우도 있다.

1. 조회하려는 table에 데이터가 조금 있을 경우,

2. 조회하려는 table의 데이터가 해당 table의 대다수를 차지하는 경우,

때문에 좋은 성능을 위해서는 mysql 실행계획을 확인하면서 조회 성능을 높히는 작업이 필요하다.

 

 

** 추가 사항

1. order by 나 group by 에도 index가 사용될 수 있다.

2. foreign key에는 index가 자동으로 생성되지 않을 수 있다.

3. 이미 데이터가 몇 백만건 있는 테이블에 인덱스를 생성하는 경우, 인덱스 생성 시간이 몇 분 소요 될 수 있고 DB성능에 안좋은 영향을 줄 수 있다.

 

 

 

 

 

느낀점

대용량 트래픽 처리 프로잭트를 진행하면서 index를 통해 1000만 건에 가까운 데이터를 조회하는 성능을 높히기 위한 작업을 진행중이다. 쉽진 않지만, 지속적으로 테스트를 해보고 성능을 확인하며 나의 프로젝트에 적합한 방법들을 찾아낸다면 좋은 프로젝트 성과가 있을 것이다!