MySQL 인덱스에 대해서

인덱스

- 인덱스란 select의 성능을 향상하기 위해 지정한 컬럼들을 기준으로 메모리 영역에 일종의 목차를 생성하는 것이다.

책의 찾아보기를 보면 내용을 쉽고 빠르게 찾아갈 수 있도록 "ㄱ", "ㄴ", "ㄷ"과 같이 순서대로 정렬되어 있는데, DBMS의 인덱스도 마찬가지로 컬럼의 값을 주어진 순서로 미리 정렬해서 보관합니다.

insert, update, delete의 성능이 저하되는 대신 select의 성능을 향상시키는데

조회를 통한 update, delete를 진행할 경우에는 인덱스가 있으면 빠르게 조회를 할 수 있다.

그러므로 많은 양의 데이터를 삭제 및 업데이트 하는 경우에는 인덱스로 지정된 컬럼을 기준으로 진행하는 것을 추천한다.

 

B-Tree 인덱스

- 이름 그래도 데이터를 트리 구조로 저장하는 형태의 인덱스이다.

앞의 B는 Balanced를 의미하며, 뜻하는 바와 같이 균형 잡힌 뛰어난 범용성을 인정받아 가정 널리 사용된다.

최상위에 Root 노드가 존재하고, 최하위에는 Leaf 노드가 존재한다.

그리고 Root 노드와 Leaf 노드 사이에 존재하는 중간 노드를 internal 또는 Branch 노드라고 한다.

인덱스와 실제 데이터는 따로 관리되는데, 인덱스의 Leaf 노드는 항상 실제 데이터를 찾아가기 위한 주소값을 가지고 있다.

 

탐색 대상이 E라고 가정하면 다음과 같은 순서로 진행된다.

1. 루트 노드 값이 P이므로 E보다 값이 크다. 따라서 왼쪽 자식 노드로 탐색한다.

2. E는 C와 G 사이이므로 두번째 노드로 이동한다.

3. 마지막인 Leaf 노드에 도달했고, Leaf노드인 E에는 실제 데이터가 저장되어 있는 주소를 알고 있으므로 데이터를 조회할 수 있게 된다.

 

인덱스는 테이블의 키 컬럼만 가지고 있으므로 나머지 컬럼을 읽으려면 데이터 파일에서 해당 레코드를 읽어야 합니다.

데이터 파일은 디스크에 저장되는데, 디스크에서 읽는 것은 메모리에서 읽는 것보다 성능이 떨어진다.

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

 

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

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

또한, 인덱스 역시 공간을 차지하므로 많은 인덱스는 그만큼 많은 공간을 차지하게 된다.

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

옵티마이저(Optimizer)란?
- SQL 개발자가 SQL을 작성하여 실행할 때, 옵티마이저는 SQL을 어떻게 실행할 것인지를 계획하게 된다.
- SQL 실행 계획(Execution Plan)을 수립하고 SQL을 실행한다.
- 옵티마이저는 SQL의 실행 계획을 수립하고 SQL을 실행하는 데이터베이스 관리 시스템의 소프트웨어이다.
- 동일한 결과가 나오는 SQL도 어떻게 실행하느냐에 따라서 성능이 달라지기에, SQL 성능에 옵티마이저는 아주 중요한 역할을 한다.

인덱스 키 값의 크기

- MySQL에서 디스크에 데이터를 저장하는 가장 기본 단위를 페이지라고 하며, 인덱스 역시 페이지 단위로 관리 된다.

페이지는 16KB로 고정되어 있다.

설정한 인덱스 키의 크기가 16 Byte 라고 하고, 자식노드(Branch, Leaf)의 주소가 담긴 크기가 12 Byte 정도로 잡으면,

16 * 1024 / (16 + 12) = 585 로 인해 하나의 페이지에는 585개가 저장될 수 있다.

여기서 인덱스 키가 32 Byte로 커지면 6 * 1024 / (32 + 12) = 372가 되어 한 페이지에 372개가 저장된다.

즉, 조회 결과로 500개의 row를 읽을 때, 인덱스 크기가 16 Byte이면 1개의 페이지에서 모두 조회가 가능하지만 인덱스의 크기가 32 Byte이면 2개의 페이지를 읽어야 하므로 성능 저하가 발생하게 된다. 따라서 인덱스의 키는 길면 길수록 성능상 이슈가 발생하게 된다.

 

인덱스 컬럼 기준

- 1개의 컬럼만 인덱스를 걸어야 한다면, 해당 컬럼은 카디널리티(Cardinality)가 가장 높은 것을 잡아야 한다.

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

인덱스로 최대한 효율을 높이려면, 해당 인덱스로 많은 부분을 걸러내야 한다.

만약 설병을 인덱스로 잡는다면, 남/녀 중 하나를 선택하기 때문에 인덱스를 통해 50%밖에 걸러내지 못한다.

하지만 주민등록번호 같은 경우 인덱스를 통해 데이터의 대부분을 걸러내기 때문에 빠르게 검색이 가능하다.

여러 컬럼으로 인덱스를 구성할 겨웅에는 카디널리티가 높은 컬럼부터 낮은 순으로 구성하는게 성능이 더 뛰어나다.

 

인덱스 컬럼 구성

- 여러 컬럼으로 인덱스 시, 반드시 인덱스의 모든 컬럼을 모두 사용해야 하는 것은 아니다.

그렇다면 인덱스의 컬럼 중 누락되면 안되는 것과 되는 것에는 무엇이 있을까?

 

만약 인덱스를 아래와 같은 컬럼으로 생성할 경우에

group_no, from_date, is_bonus

조회 쿼리 사용 시 인덱스를 사용하려면 최소한 첫 번째 인덱스 컬럼은 조회 조건에 포함되어야 한다.

첫 번째 인덱스 컬럼이 조회 쿼리에 없으면 인덱스를 사용하지 않는다.

 

인덱스 조회 시 주의 사항

- between, like, <, > 등 범위 조건은 해당 컬럼은 인덱스를 타지만, 그 뒤 인덱스 컬럼들은 인덱스가 사용되지 않습니다. 즉, group_no, from_date, is_bonus 컬럼으로 인덱스가 구성되어 있는데 조회 쿼리를 아래와 같이 사용하면 is_bonus는 인덱스가 사용되지 않습니다.

where group_no= XX and is_bonus = YY and from_date > ZZ

반대로 =, in은 다음 컬럼도 인덱스를 사용합니다. in은 결국 =을 여러 번 실행시킨 것이기 때문입니다. 단, in은 인자값으로 상수가 포함되면 문제없지만, 서브 쿼리를 넣게 되면 성능상 이슈가 발생합니다. in의 인자로 서브쿼리가 들어가면 서브 쿼리의 외부가 먼저 실행되고, in은 체크 조건으로 실행되기 때문입니다.

 

and 연산자는 각 조건들이 읽어와야할 row 수를 줄이는 역할을 하지만, or 연산자는 비교해야할 row가 더 늘어나기 때문에 풀 테이블 스캔이 발생활 확률이 높습니다. 따라서 where 절에서 or 연산자를 사용할 때는 주의가 필요합니다.

 

인덱스가 사용된 컬럼 값은 그대로 사용해야만 인덱스가 사용됩니다. 인덱스는 가공된 데이터를 저장하고 있지 않기 때문입니다. 예를 들어, where salary * 10 > 150000; 은 인덱스를 사용하지 못하지만, where salary > 150000 / 10; 은 인덱스를 사용합니다.

 

 null 값의 경우, is null 조건으로 인덱스 레인지 스캔이 가능합니다.

 

참고

- jojoldu.tistory.com/243

- icarus8050.tistory.com/42