[Mysql] index 설정

왜 공부를 하는가?

프로젝트를 진행을 하다보니, 비교적 테이블의 데이터를 조회하는 비즈니스 로직을 많이 사용하는 것을 확인했다. 그래서 어떻게 하면 조금 더 빠르게 조회를 할 수 있을까? 라는 생각을 하였다.

DB에서 테이블을 조회를 할 때, 데이터가 많아지게 되면 조회를 하는데 시간이 오래 걸릴 것이다. DB 설계를 할 때, 조금 더 성능을 높일 수 있게끔 할 수 있을 것 같아 공부를 해보려고 한다.

우선 Index를 알아보기 전에 풀 스캔과 레인지 스캔을 이해해야 한다.

풀 스캔 & 레인지 스캔

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

연습할 테이블을 만들었다.

CREATE TABLE `user` (
  `id` 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 (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Mysql에서 엔진들을 선택할 수 있다. 기본적으로 InnoDB, MyISAM, Archive 엔진이 사용된다.

실행 계획(explain)을 통해 스캔 방식에 대해 확인을 할 수 있다. 아래의 명령어를 통해 알아볼 수 있다.

여기서 type을 주목을 해야 한다. type이 ALL이라고 되어 있는데 이는, 모든 데이터를 풀 스캔 한다는 것이다.

만약 where 절을 사용해서 범위를 지정해 준다면 어떻게 될까?

type이 range로 바뀌었다. 이는 id가 1 ~ 10 범위에서 레인지 스캔을 한다는 뜻이다.

Index 란?

위에서 알아본 풀 스캔과 레인지 스캔 처럼, 특정 데이터를 찾을 때 풀 스캔을 하는 것 보다 레인지 스캔을 하는 것이 더욱 빠를 것이다. 이를 도와 주는 것이 인덱스이다.

인덱스는 테이블의 동작속도(조회)를 높여주는 자료구조이다. 인덱스로 데이터의 위치를 빠르게 찾아주는 역할이다. 색인과 비슷한 개념이다.

우리가 책에대 포스트잇을 붙여넣거나 목차를 달아두어서 찾고자 하는 내용이 있으면 해당 내용이 속한 포스트잇이나 목차를 바로 찾아서 둘러보는 것과 같다.

인덱스는 MYI(MySQL Index)파일에 저장되며, 인덱스가 설정되지 않았다면 Table Full Scan이 일어나 성능이 저하되거나 치명적인 장애가 발생한다고 한다.

인덱스를 설정을 하게 되면 조회속도는 빨라지지만 UPDATE, INSERT, DELETE의 속도는 저하된다는 단점이 있다고 한다.

때문에 효율적인 인덱스 설계가 필요하다.

Index의 특징

인덱스는 하나 혹은 여러 개의 컬럼에 대해 설정할 수 있다. (단일 여러개 또는 여러 칼럼을 묶어 복합 인덱스)

WHERE절을 사용하지 않고 인덱스가 걸린 컬럼을 조회하는 것은 성능에 아무런 영향이 없다.

ORDER BY와 GROUP BY에 대한 Index

인덱스는 ORDER BY와 GROUP BY에도 영향을 끼치는데 다음과 같은 경우에는 인덱스를 타지 않는다.

• ORDER BY 인덱스컬럼1, 컬럼2 : 복수의 키에 대해서 ORDER BY를 사용한 경우
• WHERE 컬럼1=’값’ ORDER BY 인덱스 컬럼 : 연속하지 않은 컬럼에 대해 ORDER BY를 실행한 경우
• ORDER BY 인덱스컬럼1 DESC, 인덱스컬럼2 ASC : DESC와 ASC를 혼합해서 사용한 경우
• GROUP BY 컬럼1 ORDER BY 컬럼2 : GROUP BY와 ORDER BY의 컬럼이 다른 경우
• ORDER BY ABS(컬럼) : ORDER BY 절에 다른 표현을 사용한 경우

Index의 구조

인덱스를 설정하지 않았을 때의 user 테이블의 인덱스 구조이다.

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

다음에 B-Tree 구조 알아보자. (업데이트 필요)

Index 생성

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

이렇게 인덱스를 설정할 수 있다.

마치며

다음 시간엔 앞서 만든 테이블에 인덱스를 설정을 하고 얼마나 속도가 빨라지는지에 대해 테스트를 해볼 것이다.