[MySQL] INDEX

http://www.tcpschool.com/mysql/mysql_index_create

코딩교육 티씨피스쿨

 

---

INDEX

인덱스란?

MySQL에서 SELECT를 하면 가장 처음 데이터부터 마지막 데이터까지 순차적으로 검색을 한다.

데이터가 적은 테이블에서는 문제없이 빠른 속도로 검색이 되지만 데이터가 매우 많은 테이블에서  SELECT를 한다면... 데이터를 일일히 검색하려면 시간이 꽤 많이 걸릴 것이다. 이 말인 즉슨, 성능저하가 필연적으로 일어난다는 말이다.

 

데이터가 많은 테이블에서 유용하게 사용되는 녀석이 바로 인덱스이다. 

인덱스는 마치 책에서의 목차와 같은 역할을 수행해 어떻게 사용하느냐에 따라 쿼리 수행 속도를 빠르게 할 수 있다.

 

하지만 그렇다고 인덱스를 모든 곳에 사용하면 오히려 악효과를 낼 수 있다.

인덱스는 테이블을 '정렬'해서 조회하기 때문에 SELECT문에서는 효과적이지만 SELECT가 아닌 테이블을 변경하는 쿼리(INSERT, DELETE, UPDATE 등)가 빈번하게 발생하는 쿼리에서는 테이블을 정렬한 후에 변경을 해야하기 때문에 성능저하로 이어질 수 있다고 한다.

또 다른 단점으로는 인덱스는 DB 저장공간의 약 10%정도를 차지하기 때문에 저장공간의 낭비가 일어날 수 있다. 이러한 이유 때문에 더더욱 인덱스의 남발은 피해야한다.

 

그럼 효율적으로 인덱스 사용 방식에는 무엇이 있을까?

 

• WHERE 절에 자주 등장하는 컬럼을 인덱스로 설정한다.
• ORDER BY 절에 자주 등장하는 컬럼을 인덱스로 설정한다.
• SELECT 절에 자주 등장하는 컬럼들을 잘 조합해서 인덱스로 구성한다.
• JOIN이 자주 사용되는 열에 인덱스를 생성해주는 것이 좋다.

 

종합해보면, 인덱스를 사용하는 이유는 CUD를 희생하여 R(Read) 즉, SELECT의 기능을 향상시키기 위함이다.

 

 

인덱스의 구조

B-TREE 인덱스

제일 어려운 자료구조에 대한 이야기이다.

 

대부분의 RDBMS 구조체(관계형 데이터베이스)는 기본적으로 B-TREE 혹은 B+TREE 인덱스를 사용한다고 하는데... B-TREE가 뭐지?

 

인덱스 컬럼(키)을 관리하기 위해서 트리(자료구조)를 이용하는데 최상위에 루트(Root) 노드가 있고 가장 끝단에 실제 레코드의 주소가 저장되어있는 리프(Leaf) 노드가 있다.
루트 노드와 리프 노드 사이에는 브랜치(Branch) 노드가 있다.
루트 노드와 브랜치 노드에는 실제 레코드의 주소는 알 수 없지만 실제 레코드의 주소를 알고 있는 리프 노드에 대한 매핑 정보를 갖고 있다. (같은 트리 레벨(깊이)의 페이지 사이에도 링크가 되어있어 다음 페이지를 확인할 수 있다.)

출처: https://jeong-pro.tistory.com/242 [기본기를 쌓는 정아마추어 코딩블로그]

 

예시와 함께 이해해보기 위해 다음 글도 가져와보았다.

https://jojoldu.tistory.com/243

[mysql] 인덱스 정리 및 팁

• 인덱스 탐색은 Root -> Branch -> Leaf -> 디스크 저장소 순으로 진행된다.

예를 들어, Branch (페이지번호 2) 는 dept_no가 d001이면서 emp_no가 10017 ~ 10024까지인 Leaf의 부모로 있다.

즉, dept_no=d001 and emp_no=10018로 조회하면 페이지 번호 4인 Leaf를 찾아 데이터파일의 주소를 불러와 반환하는 과정을 지나게 된다.

 

• 인덱스의 두번째 컬럼은 첫 번째 컬럼에 의존해서 정렬되어 있다.

즉, 두번째 컬럼의 정렬은 첫번째 컬럼이 똑같은 열에서만 의미가 있다는 말이다.

만약 3번째, 4번째 인덱스 컬럼도 있다면 두번째 컬럼과 마찬가지로 3번째 컬럼은 2번째 컬럼에 의존하고, 4번째 컬럼은 3번째 컬럼에 의존하는 관계가 된다.

 

• 디스크에서 읽는 것은 메모리에서 읽는것보다 성능이 훨씬 떨어지게 된다.

결국 인덱스 성능을 향상시킨다는 것은 디스크 저장소에 얼마나 덜 접근하게 만드느냐, 인덱스 Root에서 Leaf까지 오고가는 횟수를 얼마나 줄이느냐에 달려있다.

 

• 인덱스의 갯수는 3~4개 정도가 적당하다고 한다.

너무 많은 인덱스는 새로운 Row를 등록할때마다 인덱스를 추가해야하고, 수정/삭제시마다 인덱스 수정이 필요하여 성능상 이슈가 있다.

또, 인덱스는 일부 공간을 차지하므로 많은 인덱스들은 그만큼 많은 공간을 차지한다.

특히 많은 인덱스들로 인해 옵티마이저가 잘못된 인덱스를 선택할 확률이 높다.

 

 

조금은 쉽게 B-TREE를 설명해둔 글이 있어 하나 더 첨부한다.

https://matice.tistory.com/8

[OS] 책 설명이 x같아서 내가 쉽게 쓴 B 트리

 

 

어렵지만 차근차근 공부해나가면 언젠가는 이해하겠지... 우선 다시 MySQL의 인덱스로 돌아가보자.

 

인덱스 종류

primary key

중복되지 않는 유일한 키

테이블 전체를 통틀어서 중복되지 않는 값을 지정해야 한다.

where 문을 이용해서 데이터를 조회할 때 가장 고속으로 데이터를 가져올 수 있다.

테이블마다 딱 하나의 primary key를 가질 수 있다.

 

normal key

중복을 허용하는 인덱스

테이블 전체를 통틀어서 중복되지 않는 값을 지정해야 한다. (== primary key)

고속으로 데이터를 가져올 수 있다.

여러개의 unique key를 지정할 수 있다.

 

unique key

중복을 허용하지 않는 유일한 키

 

foreign key

다른 테이블과의 관계성을 부여하는 키

 

복합키

하나의 키에 여러개의 컬럼을 포함

즉, 여러개의 키를 하나로 묶어서 정의해놓는 것을 포괄해서 복합키라고 한다. 

 

 

인덱스 생성

CREATE문으로 생성

CREATE INDEX 인덱스명
ON 테이블명(컬럼명1, 컬럼명2, ...);

CREATE UNIQUE INDEX 인덱스명
ON 테이블명(컬럼명1, 컬럼명2, ...);

 

테이블 생성과 함께  인덱스 생성

예제 (출처: 생활코딩 )

CREATE TABLE `student` (
  `id` tinyint(4) NOT NULL AUTO_INCREMENT,
  `name` char(4) NOT NULL,
  `address` varchar(50) NOT NULL,
  `department` enum('국문과','영문과','컴퓨터공학과','전자공학과','물리학과') NOT NULL,
  `introduction` text NOT NULL,
  `number` char(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_number` (`number`) USING BTREE,
  KEY `idx_department` (`department`),
  KEY `idx_department_name` (`department`,`address`),
  FULLTEXT KEY `idx_introduction` (`introduction`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;​

위 테이블 생성 쿼리에서

`id`를 PRIMARY KEY,

`number`를 UNIQUE키,

`department`를 일반키

`department`와 `address`를 복합키로 생성하였다.

 

 

ALTER문으로 인덱스 추가

ALTER TABLE 테이블명
ADD INDEX 인덱스명 (컬럼명);

ALTER TABLE 테이블명
ADD UNIQUE INDEX 인덱스명 (컬럼명);

 

인덱스 생성 시 기준으로 삼을 컬럼은?

인덱스가 1개일 때

카디널리티(Cardinality)가 가장 높은 것을 기준으로 잡아야한다.

카디널리티(Cardinality)란 해당 컬럼의 중복된 수치를 말하는데, 중복이 많이 될 수록 카디널리티가 낮다고 얘기하며 중복되는 값이 적으면 카디널리티가 높다고 말한다.

예를들어 성별, 학년 등은 카디널리티가 낮고 주민등록번호, 계좌번호 등은 카디널리가 높다.

 

인덱스가 1개일 때 카디널리티를 기준으로 잡아야하는 이유는 인덱스로 최대한 효율을 뽑아내려면 해당 인덱스로 많은 부분을 걸러내야하기 때문이다.

만약 성별을 인덱스로 잡는다면, 남/녀 중 하나를 선택하기 때문에 인덱스를 통해 걸러낼 수 있는 건 50%밖에 되지 못한다.

하지만 주민등록번호나 계좌번호로 인덱스를 잡으면 거의 대부분을 걸러낼 수 있기 때문에 빠른 검색이 가능하다.

 

 

인덱스가 여러개일 때

그럼 여러 컬럼으로 인덱스를 잡는다면 어떤 순서로 인덱스를 구성하는게 좋을까?

 

참고하고 있는 글 (https://jojoldu.tistory.com/243)에서 직접 실험으로 증명하고 있는데 실험 내용까지 그대로 가져올 수 없기에 결론을 말하자면 카디널리티가 높은 순(중복이 많이 되지 않는 값)에서 낮은 순(중복이 많이 되는 값)으로 구성하는게 더 성능이 뛰어나다고 한다.

 

이걸 내 방식으로 생각해보면 10000명의 사람이 있을 때, 성별 > 전화번호 > 주민등록번호 순으로 조회하는 것보다 주민등록번호 > 전화번호 > 성별로 조회하는게 훨씬 빠르니 당연한 결과이다.

 

 

인덱스 성능 확인하기

인덱스를 생성해놓고 그냥 SELECT문을 실행해보면 이게 정말 속도가 빠른지 어떻게 조회하고있는지 바로 알기는 어렵다.

인덱스의 성능(실행 계획)을 보는 방법은 간단하다. 

실행하려는 쿼리 앞에 EXPLAIN을 붙이면 된다.

 

EXPLAIN SELECT * FROM `student`
WHERE `id` = 3;

위의 쿼리를 실행시켜보면 다음과 같은 결과가 나온다.

여기서 주목해야하는 부분은 type부분인데, 실행 성능에 따라 아래 표와 같이 나타나진다.

 

type	설명
all	테이블 Full Scan(테이블 크기가 크다면 성능 개선 필수)
index	인덱스 Full Scan(인덱스 전체 조회. 테이블 크기가 크다면 성능 개선 필수)
range	인덱스를 사용한 범위 검색
ref	key = value 형태의 조회
eq_ref	테이블 간의 JOIN에서 PK 또는 Unique Key가 이용된 경우
const	PK 또는 Unique Key로 조회된 경우. 최고의 성능.

 

 

 

인덱스 조회 시 주의사항

• between, like, <, > 등 범위 조건은 해당 컬럼은 인덱스를 타지만, 그 뒤 인덱스 컬럼들은 인덱스가 사용되지 않는다
  범위조건으로 인덱스를 사용하면 안된다고 기억하자.

• 반대로 =, in 은 다음 컬럼도 인덱스를 사용한다.
  in은 결국 =를 여러번 실행시킨 것과 같기 때문이다. 

 

• AND연산자는 각 조건들이 읽어와야할 ROW수를 줄이는 역할을 하지만, or 연산자는 비교해야할 ROW가 더 늘어나기 때문에 풀 테이블 스캔이 발생할 확률이 높다.

 

• 인덱스로 사용된 컬럼값 그대로 사용해야만 인덱스가 사용된다.
  컬럼이 문자열인데 숫자로 조회하면 타입이 달라 인덱스가 사용되지 않습니다. 정확한 타입을 사용해야만 합니다.

 

• null 값의 경우 is null 조건으로 인덱스 레인지 스캔이 가능하다

 

---

 

MySQL에서 제일 어렵게 느껴졌던 INDEX... 사실 아직 직접 커다란 데이터에서 써보지 않고는 잘 와닿지 않는다. 

성능과 가장 밀접한 관계성을 가지고 있는 녀석이니 확실히 알아두고 넘어가도록 하자.

 

---

 

<참고>

https://velog.io/@ansrjsdn/MySQL-INDEX-%EB%9E%80

 

https://jojoldu.tistory.com/243

 

https://jeong-pro.tistory.com/242

 

https://opentutorials.org/module/98/1538