Index 란 무엇이고 어떤 기준을 잡아야할까

# Index(색인)이란 무엇일까? 

- Index란 주로 책의 첫 장에 있는 "목차"에 비유된다. 

 

# 이걸 왜 사용하는걸까? 

자, 사전을 펼쳤다고 생각해보자.

"한글" 이라는 단어에 관한 내용을 찾아보려고 한다.  어떤 방법으로 찾는게 빠를까?

 

1. 목차를 보고 'ㅎ' 으로 시작하는 단어의 페이지를 펼친 후 찾는다.

2. 책의 처음부터 끝까지 "한글"이라는 단어를 찾는다.

 

정답은 당연히 1번이다. 

 

이와 같이 방대한 양의 데이터 중 내가 원하는 데이터를 빠르게 찾기 위해서 

Index를 사용한다고 생각하면 된다. 

 

# 그럼 우리는 Index를 어떻게 설정하고 사용할까? 

table을 설계하다보면 index를 지정하기 위해 고민이 필요하다. 

index로 최대한 효율을 뽑아내려면 내가 지정한 index로 최대한 많은 데이터를 걸러낼 수 있어야한다. 

 

그러기 위해 우리는 카디널리티(Cardinality)가 가장 높은 컬럼을 index로 지정해주어야한다. 

 

*카디널리티(Cardinality) - 컬럼의 중복된 수치를 나타낸다. ( 상대적인 개념으로 이해하면 된다.)

회원관리에서 성별은 주민등록번호에 비해 중복된 데이터가 많다 -> 이 경우 카디널리티가 "낮다"

반면, 주민등록번호, 휴대폰 번호는 성별에 비해 중복된 데이터가 적다 -> 카디널리티가 "높다" 

 

 

회원정보 테이블에서 index로 성별을 지정하게 된다면 데이터의 50% 밖에 걸러내지 못함. ( 성별비율 5:5 가정 ) 

하지만 중복된 데이터가 없는 주민번호로 걸러낸다면 훨씬 빠른 검색이 가능할것이다. 

 

그러니 index를 설정할땐 가장 중복되지 않는 값을 인덱스로 정하는게 좋다. (= 카디널리티가 높은)

( 단, index의 길이가 길면 길수록 성능에 이슈가 있으니 짧은걸로 잡는게 좋다. )

 

 

# 여러개의 Index를 설정하려고 하는데 어떤 순서로 구성해야할까? 

여러개의 index를 설정하면 어떤 순서로 구성하는게 좋을까

 

1. 카디널리티가 낮은 -> 높은 

2. 카디널리티가 높은 -> 낮은

 

간단한 예를 들어 생각해보자. 

예) 전국민의 정보가 들어있는 DB에서 서울시에 거주중인 100세 여성 인구수를 조사한다. 

 

1. 카디널리티가 낮은 -> 높은 

- 여성으로 거른다. --> 2500만명

- 서울시에 거주중인 사람으로 거른다. --> 800만명

- 나이가 100인 사람으로 거른다 --> 1만명

( SELECT * FROM 전국민 WHERE 성별 ='여성' AND 거주지 = '서울' AND  나이 = 100; )

 

2. 카디널리티가 높은 -> 낮은

- 나이가 100인 사람으로 거른다. --> 10만명 

- 서울시에 거주중인 사람으로 거른다.  --> 2만명

- 성별을 여성으로 거른다. --> 1만명 

( SELECT * FROM 전국민 WHERE 나이 = 100 AND 거주지 = '서울' AND 성별 ='여성'; )

 

위 조건을 보면 어떤식으로 걸러내는게 빠를지 알 수 있다. 

실제 쿼리의 동작에서도 예시와 같이 카디널리티가 높은순에서 낮은순으로 구성하는것이 성능이 좋다. 

 

 

# 주의 사항 

- Index를 지정했다고해서 모두 사용해야만 하는 것은 아니다. 

그렇다면 지정한 Index 중 누락되어도 되는것과 누락되면 안되는 것은 어떤 걸까

 

# 누락되면 안되는 index 

- index 설정을 아래의 순서로 지정했다고 한다면 

 (인덱스 ::::   나이 > 거주지 > 성별 )

 

SELECT * FROM 전국민 WHERE 거주지 = '서울' AND 성별 ='남성'; 

-> 이렇게 조회하는 경우 인덱스가 적용되지 않는다.

내가 설정한 첫번째 인덱스인 "나이"가 조건에 포함 되어 있는 경우만 다른 인덱스의 적용이 가능하다. 

 

SELECT * FROM 전국민 WHERE  나이 = 50 AND 성별 ='남성';  ==> index 적용 O. 

 

 

# 인덱스에 범위 조건을 지정하는 경우 어떤일이 생길까 

SELECT * FROM 전국민 WHERE 나이 > 99 AND 거주지 = '서울' AND 성별 = '남성'; 

 

위 쿼리를 조회를 한다면 나이까지는 적용이 되지만 거주자와 성별은 Index가 적용되지 않는다. 

- 즉  between, like, <, > 등 범위 조건을 사용하면 뒤에 오는 인덱스가 사용되지 않는다고 생각하면 된다. 

 

반면에 

 =, in은 다음 컬럼도 index가 적용된다. ( in 은 결국 = 를 여러번 실행한 것이기 때문)

 단, in 인자값으로 서브쿼리를 넣게되면 성능상 이슈가 발생할 수 있는데

 이유는 서브쿼리의 외부가 먼저 실행되고 in 은 체크 조건으로 실행되기 때문이다.

 

 

# AND 와 OR  연산시 주의사항 

AND는 조회할 ROW 수를 줄이는 역할을 하지만 OR 연산자는 비교해야할 ROW가 늘어나기 때문에 

풀 테이블 스캔이 발생할 확률이 높다. 그러니 사용할 때 주의하기. 

 

 

# Index로 설정한 컬럼값 그대로 사용해야 인덱스가 적용된다. 

- WHERE 나이 +10 > 10 ==>  X 

- WHERE 나이 > 10 * 2  ==>  O

 

 

# Index 조회 순서 

- 요즘은 예전처럼 꼭 index의 순서와 조회 순서를 지킬 필요는 없다고한다. 

  중요한건 Index 컬럼들이 조회 조건에 포함되있는것이다.

 

 

 

 

끝===

공부를 위해 찾아본 내용이라 오류가 있을 수 있습니다. 

틀린 부분이 있다면 댓글로 남겨주시면 감사하겠습니다 ^^. 

 

 

 

 

참고

http://www.mysqlkorea.com/sub.html?mcode=manual&scode=01&m_no=21436&cat1=7&cat2=0&cat3=0&lang=k

https://webmonkeyuk.wordpress.com/2010/09/27/what-makes-a-good-mysql-index-part-2-cardinality/