인덱스

다음의 글을 그대로 가져온 글입니다.

jojoldu.tistory.com/243

[mysql] 인덱스 정리 및 팁

 

1. 인덱스란?

---

인덱스 == 정렬

 

인덱스는 결국 지정한 칼럼들을 기준으로 메모리 영역에 일종의 목차를 생성하는 것입니다.

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

 

2. 인덱스 키 값의 크기

---

InnoDB(MySQL)은 디스크에 데이터를 저장하는 가장 기본 단위를 페이지라고 하며, 인덱스 역시 페이지 단위로 관리됩니다. 

(B-Tree 인덱스 구조에서 Root,Branch, Leaf 참고)

페이지는 16KB로 크기가 고정되어 있습니다.(MS-SQL 은 8KB로 기본 설정되어 있습니다.)

 

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

 

여기서 인덱스 키가 32Byte로 커지면 어떻게 될까요?

6*1024 / (32+12) = 372로 되어 372개만 한 페이지에 저장할 수 있습니다. 

 

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

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

3.인덱스 칼럼 기준

---

먼저 얘기할 것은 1개의 칼럼만 인덱스를 걸어야 한다면, 해당 칼럼은 카디널 리티(Cardinality)가 가장 높은 것 을 잡아야 한다는 점.

 

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

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

 

3-1. 여러 컬럼으로 인덱스 구성시 기준

여러 칼럼으로 인덱스를 잡는다면 어떤 순서로 인덱스를 구성해야 할까요?

카디널리티가 낮은 -> 높은순으로 구성하는게 좋은지?

카디널리티가 높은 -> 낮은순으로 구성하는게 좋은지?

 

실제 블로그에서는 

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;

전체 Row는 약 1700만건으로 생성됬습니다.

각 컬럼의 카디널리티는 다음과 같습니다.

 

자 그럼 인덱스를 2가지 형태로 생성해보겠습니다.

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);

첫번째 인덱스는 is_bonud, from_date, group_no 순으로 카디널리티가 낮은순에서 높은순 (중복도가 높은 수에서 낮은 순으로) 으로,

 

두번째 인덱스는 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;

옵티마이저가 인덱스를 자동 선택해버리니 Index Hint(use index (IDX_SALARIES_INCREASE)) 로 강제로 인덱스를 사용하도록 하였습니다.

 

이 인덱스 2개를 총 10회로 테스트 하였습니다.

결과가 어떻게 될까요?

 

IDX_SALARIES_INCREASEIDX_SALARIES_DECREASE

1	110ms	46.9ms
2	89.5ms	24.6ms
3	95.4ms	38.1ms
4	85.6ms	29.3ms
5	83.6ms	29.3ms
6	85.2ms	38.2ms
7	59.4ms	26.1ms
8	64.2ms	29.4ms
9	93.7ms	25.7ms
10	102ms	35.4ms
평균	86.86ms	32.3ms

월등한 차이가 나지 않지만 10회만으로 비교는 가능한것 같습니다.

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

 

정확한 성능비교를 위해서는 MySql 캐시 이외에 OS 캐시도 비워야만 했습니다. 그래서 쿼리의 조건 (group_no in()) 에 포함되는 값들을 하나씩 추가하면서 쿼리가 캐시 안되게 하여 비교하였습니다.

 

3-2 여러 컬럼으로 인덱스시 조건 누락

꼭 인덱스의 컬럼을 모두 사용해야만 인덱스가 사용되는 것은 아닙니다. 그렇다면 인덱스 컬럼중 어떤 것들은 누락되어도 되고, 누락되면 안되는 것은 어떤 것일까요?

 

예를 들어 아래와 같이 인덱스가 잡혀있습니다.

 

인덱스 : group_no, from_date, is_bonus

 

여기서 중간에 있는 from_date 를 제외한 조회 쿼리와 가장 앞에 있는 group_no를 제외한 조회 쿼리를 사용해 봅니다. 

 

첫 번째 조회쿼리의 실행계획을 보면

 

이렇게 정상적으로 인덱스를 사용했음을 확인할 수 있습니다.

filtered가 10% 인만큼 효율적으로 사용하지는 못했지만, 인덱스를 태울 수 있는 쿼리입니다.

 

그럼 두번째 조회 쿼리의 실행계획은 어떻게 될까요?

전혀 인덱스를 사용하지 못했음을 확인할 수 있습니다.

 

조회 쿼리 사용시 인덱스를 태우려면 최소한 첫번째 인덱스 조건은 조회조건에 포함되어야만 합니다.

첫번째 인덱스 컬럼이 조회 쿼리에 없으면 인덱스를 타지 않는다는 점을 기억하시면 됩니다.

 

4. 인덱스 조회 시 주의 사항

---

 

• between, like, <, > 등 범위 조건은 해당 컬럼은 인덱스를 타지만, 그 뒤 인덱스 컬럼들은 인덱스가 사용되지 않습니다.
  • 즉, group_no, from_date, is_bonus으로 인덱스가 잡혀있는대 조회 쿼리를 where group_no=xx and is_bonus=YY and from_date > zz 등으로 잡으면 is_bonus는 인덱스가 사용되지 않습니다.
  • 범위조건으로 사용하면 안된다고 기억하시면 좀 더 쉽습니다.
• 반대로 =,in 은 다음 컬럼도 인덱스를 사용합니다.
  • in은 결국 = 를 여러번 실행시킨 것이기 때문입니다.
  • 단, in은 인자값으로 상수가 포함되면 문제 없지만,서브쿼리를 넣게되면 성능상 이슈가 발생합니다.
  • in의 인자로 서브쿼리가 들어가면 서브쿼리의 외부가 먼저 실행되고, in은 체크조건으로 실행되기 때문입니다.
• AND 연산자는 각 조건들이 읽어와야할 ROW수를 줄이는 역활을 하지만, OR 연산자는 비교해야할 ROW가 더 늘어나기 때문에 풀 테이블 스캔이 발생할 확률 이 높습니다.
  • WHERE 에서 OR을 사용할 때는 주의가 필요 합니다.
• 인덱스로 사용된 컬럼값 그대로 사용해야만 인덱스가 사용됩니다.
  • 인덱스는 가공된 데이터를 저장하고 있지 않습니다.
  • where salary * 10 > 150000; 인덱스를 못타지만, where salary > 150000 / 10;은 인덱스를 사용합니다.
  • 컬럼이 문자열인데 숫자로 조회하면 타입이 달라 인덱스가 사용되지 않습니다. 정확한 타입을 사용해야만 합니다.
• null 값의 경우 is null 조건으로 인덱스 레인지 스캔 가능

5.인덱스 컬럼 순서화 조회 컬럼순서

최근엔 이전과 같이 꼭 인덱스 순서와 조회 순서를 지킬 필요는 없습니다.

인덱스 컬럼들이 조회조건에 포함되어 있는지가 중요합니다.

(3-1 실험과 동일한 인덱스에 조회 순서만 변경해서 실행한 결과)

 

보시는 것처럼 조회 컬럼의 순서는 인덱스에 큰 영향을 기치지 못합니다.

단, 옵티마이저가 조회 조건의 컬럼을 인덱스 컬럼 순서에 맞춰 재배열하는 과정이추가되지만 거의 차이가 없긴합니다. (그래도 이왕이면 맞추는게 성능에 조금이나마 나을것이다.)

6.페이지 성능 개선 팁

---

jojoldu.tistory.com/528

1. 페이징 성능 개선하기 - No Offset 사용하기

jojoldu.tistory.com/529?category=637935

2. 페이징 성능 개선하기 - 커버링 인덱스 사용하기

jojoldu.tistory.com/530?category=637935

3-1. 페이징 성능 개선하기 - 검색 버튼 사용시 페이지 건수 고정하기

jojoldu.tistory.com/531?category=637935

3-2. 첫 페이지 조회 결과 cache 하기