[MySQL] 인덱스(Index) 정리

인덱스(Index) 정리

인덱스를 알아보기 전에 풀 스캔(Full Scan)과 레인지 스캔(Range Scan)을 이해해야 한다.

 

풀 스캔(Full Scan) & 레인지 스캔(Range Scan) 

풀 스캔 : 테이블에 포함된 레코드를 처음부터 끝까지 읽어들인다. 

레인지 스캔: 테이블의 일부 레코드에만 엑세스한다. 

앞으로 예시로 들 테이블 구조 CREATE문

CREATE TABLE `user` (
    `idx` bigint(20) NOT NULL AUTO_INCREMENT,
  `created_date` datetime DEFAULT NULL,
  `email` varchar(255) NOT NULL DEFAULT '',
  `name` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `principal` varchar(255) DEFAULT NULL,
  `social_type` varchar(255) DEFAULT NULL,
  `updated_date` datetime DEFAULT NULL,
  PRIMARY KEY (`idx`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

실행 계획을 통해 풀 스캔을 하는지 레인지 스캔을 하는지 확인할 수 있는데, 실행 계획을 확인하는 방법은 아래의 명령어를 입력해보면 된다. 실행하고자하는 쿼리 앞에 explain을 붙여주면 된다 .

$ explain select * from user;

 

type을 보면 ALL 이라고 표시되어있는데, 모든 데이터를 스캔하는 풀 스캔을 하는 것이다. 

 

 

아래처럼 범위를 정하면 어떻게 될까? 

$ explain select * from user where idx between 1 and 10;

 

type이 range로 바뀌었다. 해당 쿼리문으로 인해 idx가 1~10까지 범위내에서 레인지 스캔을 하는 것이다. 

---

그럼 인덱스(Index)란?

내가 찾고 싶은 데이터를 찾을 때, 모든 레코드에서 찾는 것보다 특정한 범위 내로 한정해서 데이터를 찾는게 빠를 것이다.

 

우리가 책에다 포스트잇을 붙여넣거나 목차를 달아두어서 찾고자 하는 내용이 있으면 해당 내용이 속한 포스트잇이나 목차를 바로 찾아서 둘러보는 것과 같은 것이다. DB에서 레인지 스캔을 하려면 책처럼 색인(또는 인덱스)이 필요하다. 

 

용어를 모를 때 가장 친절하게 알려주는 위키에서 '인덱스' 에 대한 정의를 한 번 보자.

인덱스(영어: index)는 데이터베이스 분야에 있어서 테이블에 대한 동작의 속도를 높여주는 자료 구조를 일컫는다.

인덱스는 테이블 내의 1개의 컬럼, 혹은 여러 개의 컬럼을 이용하여 생성될 수 있다.

고속의 검색 동작뿐만 아니라 레코드 접근과 관련 효율적인 순서 매김 동작에 대한 기초를 제공한다.

인덱스를 저장하는 데 필요한 디스크 공간은 보통 테이블을 저장하는 데 필요한 디스크 공간보다 작다.
(왜냐하면 보통 인덱스는 키-필드만 갖고 있고, 테이블의 다른 세부 항목들은 갖고 있지 않기 때문이다.) 

관계형 데이터베이스에서는 인덱스는 테이블 부분에 대한 하나의 사본이다.

출처: 위키피디아

 

 

인덱스 구조

Index_type을 보면 BTREE라고 적혀있는 것을 확인할 수 있다.

 

인덱스 구조는 B-Tree(실제로는 B+Tree)로 구성되어 있다. 이 B-Tree 구조를 통해 찾고자하는 데이터를 빠르게 찾을 수 있는 것이다. 

 

B-Tree에 대한 건 다음 포스팅으로.

 

인덱스 생성

$ create index [인덱스 명] on [테이블 명]([컬럼 명]);
$ create index ind_email on user(email);

더미데이터 생성 시, email 컬럼에 대해 중복이 없도록 생성했다.

 

 

생성된 인덱스를 사용했을 때, 어떤 실행계획으로 동작하는지 한 번 보자. 

$ explain select * from user where email = 'haribo19@kakao.com';

type과 Extra를 주목해보자. 

 

type에는 ALL이 아닌 ref가 표시되어 있는데, 이는 풀 스캔을 회피하고 테이블의 일부만 대상으로 한다는 걸 의미한다. (ref는 reference의 축약어라고 짐작해본다.)

 

Extra에는 'using index condition' 즉, 인덱스 조건을 사용하고 있다는 의미이다.

인덱스 확인

아래의 명령어를 입력하면 해당 테이블이 가지고 있는 인덱스를 확인할 수 있다.

$ show index from [테이블 명];

Key_name이 'PRIMARY'라고 적혀있는데, 해당 인덱스는 테이블의 PK를 이용해서 만들어지는데, 테이블 생성 당시 PK를 설정해주면 자동으로 인덱스가 잡힌다.

 

프로시저를 통해 user 테이블에 더미 데이터를 약 4만건 정도를 만들었다. 실무에 사용되는 데이터 양에 비하면 턱없이 부족하긴하다. 하지만 이건 예시다!

 

인덱스가 잡힌 컬럼 Vs 아닌 컬럼간의 테스트 

간단하게 동일한 데이터를 찾는데  1. 인덱스가 걸린 PK를 조건으로 걸어보고 2. 인덱스가 걸리지 않은 이름으로 조건을 걸어서 검색해보겠다. 

$ select * from user where idx = 1522;

 

$ select * from user where name = '하리보19';

 

PK인 idx로 검색했을 때는 0.5 ms(밀리세컨드) / 인덱스가 잡히지 않은 name 컬럼으로 검색했을 때는 31.ms

 

4만건의 데이터에서도 이정도면 데이터가 커질수록 인덱스를 걸어두고 안 걸어두고의 차이가 극명해질 것이다. 

 

인덱스가 인기 있는 이유는?

1. SQL문을 변경하지 않아도 성능 개선을 할 수 있다. 
2. 테이블의 데이터에 영향을 주지 않는다. 
3. 일정한(때로는 극적인) 효과를 기대할 수 있다. 

 

인덱스를 만들 때 어떤 기준으로 만드는게 좋을까?

1. 크기가 큰 테이블만 만든다. 

크기가 작은 테이블에는 인덱스나 풀 스캔이나 큰 차이가 없다. (테이블 크기 때문에 성능이 떨어진다 싶을 때가 인덱스가 필요하다는 신호)

 

2. 기본키 제약이나 유일성 제약이 부여된 열에는 불필요하다. 

PK가 부여된 열에는 자동으로 인덱스가 작성되어 있고, 유일성 제약이 붙어있는 컬럼 또한 같다. 

이 2가지 제약이 붙은 열에 암묵적으로 인덱스가 작성된 이유는 값의 중복 체크를 하려면 데이터를 정렬해야 하는데 인덱스를 작성해 정렬하는 것이 편리하기 때문이다.

 

3. Cardinality(카디널리티)가 높은 열에 만든다. 

인덱스를 만드는 열을 결정하는 지침으로써 가장 중요한 것이 카디널리티이다. 'Cardinality'란 '값의 분산도'를 뜻한다. 특정 열에 대해 많은 종류의 값을 가지고 있다면 Cardinality가 높다. 하지만, 값의 종류가 적으면 Cardinality가 낮다는 의미이다. 

 

'주민등록번호'는 한국 국민에게 중복되지 않도록 고유의 번호를 지니고 있고, 대한민국 국민 수만큼 종류의 값을 지니고 있는데, 이는 Cardinality가 높다고 할 수 있다. 하지만, 성별처럼 남자, 여자, 미상의 3종류 밖에 얻을 수 없어 Cardinality가 매우 낮다고 할 수 있다.  

 

Cardinality가 낮은 열에 인덱스 효과를 기대하기 어려운 이유는 인덱스 트리를 따라가는데 조작이 증가(중복된 값이 많기 때문에 걸러야함)해서 오버헤드도 증가해 인덱스의 혜택을 받기 어렵다.

 

인덱스의 역효과 

1. 인덱스 갱신의 오버헤드로 갱신 처리의 성능이 떨어진다.

인덱스는 테이블에 새로운 데이터가 추가(insert)되거나 기존 데이터에 갱신(update) 또는 삭제(delete)되면 자동으로 인덱스 자신도 갱신하는 기능을 갖추고 있다. 

 

인덱스가 없었을 때를 생각하면 추가, 갱신, 삭제 시 해당 rows만 처리하면 됐지만, 인덱스가 추가되면서 인덱스 또한 갱신하기 때문에 오버헤드가 발생한다. 

 

인덱스는 SELECT 문을 고속화한 대신 INSERT나 UPDATE, DELETE처럼 갱신을 늦춘 댓가라고 볼 수 있다. 

 

2. 의도한 것과 다른 인덱스가 사용된다.

한 개의 테이블에 복수의 인덱스를 작성한 경우 발생하는 문제다. 한 테이블에 다양한 SQL문이 이용되고, 거기에 대응해 작성된 인덱스가 많아질 수 있다. 

 

느린 SQL문에 대한 실행계획을 보면 '왜 이 인덱스를 쓰고 있지'라는 의문이 드는 경우가 있다. 더 빠른 인덱스가 있음에도 의도한 것과는 다른 인덱스를 사용해 오히려 느려지는 케이스가 발생할 수 있다.

 

이런 경우가 발생하는 이유는 옵티마이저가 만능은 아니기 때문이다. 옵티마이저가 고도의 실행계획을 만들도록 설계되어 있긴 해도 인덱스 후보가 많으면 옵티마이저도 인간처럼 헤매게 된다. 

---

그 밖에...

index를 정리하다 clustered index와 non-clustered index라는 걸 보게 되었는데, 이건 다음번에 공부해봐야겠다. 

 

 

 

 

참고: 데이터베이스 첫걸음 - 미크, 기무라 메이지 지음(한빛미디어)