인덱스 란? ***

목차

인덱스란?

insert, update, delete (Command)의 성능을 희생하고 대신 select (Query)의 성능을 향상시킵니다. 여기서 주의하실 것은 update, delete 행위가 느린것이지, update, delete를 하기 위해 해당 데이터를 조회하는것은 인덱스가 있으면 빠르게 조회가 됩니다.
인덱스가 없는 컬럼을 조건으로 update, delete를 하게 되면 굉장히 느려 많은 양의 데이터를 삭제 해야하는 상황에선 인덱스로 지정된 컬럼을 기준으로 진행하는것을 추천드립니다.

B-tree 인덱스

• 인덱스 탐색은 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를 등록할때마다 인덱스를 추가해야하고, 수정/삭제시마다 인덱스 수정이 필요하여 성능상 이슈가 있습니다.
  • 인덱스 역시 공간을 차지합니다. 많은 인덱스들은 그만큼 많은 공간을 차지합니다.
  • 특히 많은 인덱스들로 인해 옵티마이저가 잘못된 인덱스를 선택할 확률이 높습니다.

인덱스 키 값의 크기

InnoDB (MySQL)은 디스크에 데이터를 저장하는 가장 기본 단위를 페이지라고 하며, 인덱스 역시 페이지 단위로 관리 됩니다.
(B-Tree 인덱스 구조에서 Root, Branch, Leaf 참고)

페이지는 16KB 로 크기가 고정되어 있습니다.

만약 본인이 설정한 인덱스 키의 크기가 16 Byte 라고 하고, 자식노드(Branch, Leaf)의 주소(위 인덱스 구조 그림 참고)가 담긴 크기가 12 Byte 정도로 잡으면, 16*1024 / (16+12) = 585로 인해 하나의 페이지에는 585개가 저장될 수 있습니다.
여기서 인덱스 키가 32 Byte로 커지면 어떻게 될까요?
16*1024 / (32+12) = 372로 되어 372개만 한 페이지에 저장할 수 있게 됩니다.

조회 결과로 500개의 row를 읽을때 16byte일때는 1개의 페이지에서 다 조회가 되지만, 32byte일때는 2개의 페이지를 읽어야 하므로 이는 성능 저하가 발행하게 됩니다.

인덱스의 키는 길면 길수록 성능상 이슈가 있습니다.

 

인덱스 컬럼 기준

먼저 말씀드릴 것은 1개의 컬럼만 인덱스를 걸어야 한다면, 해당 컬럼은 카디널리티(Cardinality)가 가장 높은 것을 잡아야 한다는 점입니다.

카디널리티(Cardinality)란 해당 컬럼의 중복된 수치를 나타냅니다.
예를 들어 성별, 학년 등은 카디널리티가 낮다고 얘기합니다.
반대로 주민등록번호, 계좌번호 등은 카디널리티가 높다고 얘기합니다.

인덱스로 최대한 효율을 뽑아내려면, 해당 인덱스로 많은 부분을 걸러내야 하기 때문입니다.
만약 성별을 인덱스로 잡는다면, 남/녀 중 하나를 선택하기 때문에 인덱스를 통해 50%밖에 걸러내지 못합니다.
하지만 주민등록번호나 계좌번호 같은 경우엔 인덱스를 통해 데이터의 대부분을 걸러내기 때문에 빠르게 검색이 가능합니다.

 

여러 컬럼으로 인덱스 구성시 기준

자 그럼 여기서 궁금한 것이 있습니다.
여러 컬럼으로 인덱스를 잡는다면 어떤 순서로 인덱스를 구성해야 할까요?
카디널리티가 낮은->높은순으로 구성하는게 좋을까요?
카디널리티가 높은->낮은순으로 구성하는게 좋을까요?
실제 실험을 통해 확인해보겠습니다.

테스트 환경은 AWS EC2 Ubuntu 16.04를 사용했습니다.
최대한 극적인 비교를 위해 메모리는 1G, 디스크는 마그네틱(SSD X)을 사용했습니다.

테이블 형태는 아래와 같습니다.

CREATE TABLE `salaries` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `emp_no` int(11) NOT NULL,
  `salary` int(11) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  `is_bonus` tinyint(1) unsigned zerofill DEFAULT NULL,
  `group_no` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

첫번째 인덱스는 is_bonus, from_date, group_no순으로 카디널리티가 낮은순에서 높은순 (중복도가 높은 순에서 낮은순으로) 으로,
두번째 인덱스는 group_no, from_date, is_bonus순으로 카디널리티가 높은순에서 낮은순 (중복도가 낮은 순에서 높은순으로) 으로 생성했습니다.

CREATE INDEX IDX_SALARIES_INCREASE ON salaries 
(is_bonus, from_date, group_no);

CREATE INDEX IDX_SALARIES_DECREASE ON salaries 
(group_no, from_date, is_bonus);

사용한 쿼리

select SQL_NO_CACHE * 
from salaries 
use index (IDX_SALARIES_INCREASE)
where from_date = '1998-03-30' 
and group_no in ('abcdefghijklmn10494','abcdefghijklmn3968', 'abcdefghijklmn11322', 'abcdefghijklmn13902', 'abcdefghijklmn100', 'abcdefghijklmn10406') 
and is_bonus = true;

select SQL_NO_CACHE * 
from salaries 
use index (IDX_SALARIES_DECREASE)
where from_date = '1998-03-30' 
and group_no in ('abcdefghijklmn10494','abcdefghijklmn3968', 'abcdefghijklmn11322', 'abcdefghijklmn13902', 'abcdefghijklmn100', 'abcdefghijklmn10406') 
and is_bonus = true;

 

즉, 여러 컬럼으로 인덱스를 잡는다면 카디널리티가 높은순에서 낮은순으로 (group_no, from_date, is_bonus) 구성하는게 더 성능이 뛰어납니다

 

•  인덱스
  • 책의 제일 끝에 있는 찾아보기 (목차랑은 다른개념이다.)
  • 데이터 : 책의 내용 / 인덱스 : 찾아보기
  • 책의 찾아보기와 인덱스의 공통점 : 정렬
• 프로그래밍 언어의 자료구조로 인덱스와 데이터 파일을 비교
  • SortedList : 인덱스와 같은 자료구조 
    • 저장되는 값을 항상 정렬된 상태로 유지하는 자료구조
    • 장점 : 이미 정렬되어 있기 때문에 아주 빠르게 원하는 값을 찾을 수 있음(Select)
  • ArrayList : 데이터와 파일과 같은 자료구조
    • 값을 저장되는 순서대로 그대로 유지하는 자료구조
• DBMS의 인덱스는 데이터의 저장(Insert, update, delete) 성능을 희생하고 대신 데이터의 읽기 속도를 높이는 기능
  
  • Select 문장의 Where 조건절에 사용되는 모든 컬럼을 인덱스로 생성하면 저장성능이 떨어지고, 인덱스 크기가 커져 오히려 역효과
• 인덱스 데이터 저장 방식(알고리즘) 별로 구분
  • B-tree 인덱스
    • 컬럼의 값을 번형하지 않고, 원래의 값을 이용하여 인덱싱
  • Hash 인덱스
    • 컬럼의 값으로 해시 값을 계산해서 인덱싱
    • 매우 빠른 검색을 지원

---

실습으로 DB 인덱싱 알아보기

실습1 : 모든 동물 테이블을 검색하고, nocope를 검색한다. 그리고 인덱싱 처리해서 실행시간을 비교한다.

위와 같은 Animal 테이블이 있다고 가정하자.

그리고 천만 건의 데이터 중를 모두 select count(*) from Animals; 해주고 또 nocope를 검색하는 쿼리를 날려준다.

각각 3.6초, 4.5초가 걸렸다. 엄청 오래 걸린다...즉, 서비스에서는 못쓴다는 것이다. 

인덱스 추가하기

CREATE INDEX ANIMAL_NAME ON ANIMALS(NAME);

동물 이름에 index를 추가해보자

그러고 성능비교를 해보자 그랬더니 0.003초로 엄청나게 빨라졌다.

어떻게 동작하는 것인가?

B-Tree : Balanced Tree

위의 그림에서 5를 찾고 싶다라고 가정하면 7과 비교해서 작은 왼쪽트리로 가고 만약 9를 찾고 싶다하고 하면 가운데 트리로 이동하는 개념이다. 

위의 상황을 살펴보자. 동물이름은 비트리안에서 오름차순으로 정렬이 되어있다. 만약 nabi를 찾고 싶다면 meow와 비교해서 뒷편에 있을 테니 2번째 브랜치로 이동하여 탐색하는 개념이다.

 

실습2 : 동물 테이블에서 키가 30cm~50cm 사이인 동물의 평균 키는?

해당 쿼리를 실행 시켜보았다. 4.3초... 이것도 서비스에서는 못쓴다.

인덱스 적용하기

CREATE INDEX ANIMAL_WEIGHT ON ANIMALS(weight);

몸무게에 where절이 걸렸으므로 weght에 인덱스를 걸어준다.

하지만...

같은 쿼리를 실행하면 4.2초로 성능 차이가 없다. weight가 전혀 인덱스를 안탄것인데 왜 그런지 알아보자.

between 이므로 시작점 30과 끝점 50을 찾는다. 

만약 위처럼 평균 키(height)가 아닌 평균 몸무게(weight)로 검색했다면 인덱스를 잘 타고 0.6초 만에 검색이 되었을 것이다. 왜냐면 해당 쿼리문을 실행 시키기 위한 모든 정보가 인덱스 안에 있기 때문이다.

즉, 위의 쿼리는 키에 따른 몸무게 정보를 알기 위해서 full scan 과정을 또 겪었을 것이며 인덱스를 타지 않았다.

해결 방법

각 몸무게에 대한 키정보를 인덱싱한다. 

다음과 같이 키, 몸무게 정보를 담은 인덱싱을 한다.

CREATE INDEX animal_wh on Animal(weight,height)

결과

0.7초로 단축하며 인덱스를 타는 것을 알 수 있다.

마지막으로 인덱스를 잘 타는지 확인하고 싶다면 analyze 키워드를 맨 앞에 쓰면 된다.

key라는 컬럼에 animal_wh라는 인덱스를 이용했다고 명시한다.

인덱스 타는지 알아보는 방법

https://jsj0903.tistory.com/5

[CLOUD] Iass vs Pass vs Sass

 

 참고 사항

• https://siahn95.tistory.com/entry/DB-%EC%9D%B8%EB%8D%B1%EC%8A%A4%EB%9E%80-1-%EA%B0%9C%EB%85%90-%EC%9E%A5%EB%8B%A8%EC%A0%90-%EC%93%B0%EB%8A%94-%EC%9D%B4%EC%9C%A0

[DB] 인덱스란? - (1) 개념, 장단점, 쓰는 이유

 

https://jojoldu.tistory.com/243

[mysql] 인덱스 정리 및 팁

https://zorba91.tistory.com/292

[MySQL] 인덱스(Index) 정리