mysql 쿼리 실행 계획 #2

이전 글에 이어 인덱스를 이용해 성능 튜닝을 해보자

 

쿼리에 앞서 show index 쿼리를 실행했을 때 출력되는 요소에 대해 이해해보자

 

Table: 인덱스가 생성된 테이블의 이름을 나타냅니다.

Non_unique: 이 컬럼은 인덱스가 중복 허용 여부를 나타낸다. 만약 값이 0이면, 인덱스에 중복된 값이 없음을 나타낸다. 즉, 유니크 인덱스를 나타낸다. 반면 값이 1이면, 인덱스가 중복된 값을 가질 수 있음을 의미한다.

Key_name: 인덱스의 이름을 나타낸다. "PRIMARY"는 기본 키를 의미한다.

Seq_in_index: 복합 인덱스의 경우 각 컬럼이 인덱스 내에서 몇 번째 위치인지를 나타내는 순서 번호이다.

Column_name: 인덱스에 포함된 컬럼의 이름을 나타낸다.

Collation: 이 컬럼은 인덱스가 문자열 값을 어떻게 정렬하는지를 나타낸다. "A"는 오름차순, "NULL"은 정렬이 적용되지 않음을 나타낸다.

Cardinality: 인덱스의 고유한 값의 개수를 나타낸다. 이 값은 인덱스의 선택도를 나타내는 데 사용되며, 쿼리 최적화기가 인덱스를 얼마나 효과적으로 사용할 수 있을지를 나타낸다.

Sub_part: 인덱스가 컬럼의 일부만 참조하고 있다면 그 길이를 나타낸다. NULL 값은 전체 컬럼이 인덱스에 포함되어 있음을 나타낸다.

Packed: 인덱스가 압축되어 있는지를 나타낸다. 압축된 인덱스는 저장 공간을 절약하지만, 쿼리 성능에 영향을 미칠 수 있다.

Null: 해당 컬럼이 NULL 값을 허용하는지를 나타낸다.

Index_type: 사용된 인덱스의 유형을 나타낸다. 일반적으로 "BTREE", "HASH", "RTREE", "FULLTEXT" 등의 값이 가능하다.

Comment: 인덱스에 대한 추가 정보를 제공한다.

Index_comment: 인덱스에 대한 주석을 보여준다. 사용자가 추가한 주석 정보이다.

인덱스 사용이 적절하지 않은 경우

위의 쿼리문을 분석해보자.

사원출입기록이라는 테이블에는 총 66만건의 데이터가 들어있다.

그 중에서 30만건의 데이터가 출입문 = b인 데이터이다.

그리고 사원출입기록의 인덱스는 위의 그림과 같다.

여기서 알 수 있듯이 '출입문'은 인덱스로 사용하기에 적절하지 않다.

인덱스를 사용할 경우 다음과 같이 0.81초가 걸린다.

하지만 인덱스를 사용하지 않을 경우 0.3초나 단축이 된 것을 확인할 수 있다.

실행 계획을 통해 알 수 있듯이 중복된 값이 많은 컬럼을 인덱스로 사용할 경우 인덱스 스캔 보다는 FULL 스캔이 성능이 더 좋는것을 볼 수 있다.

인덱스 사용이 적절한 경우

이와 반대로 중복되는 값이 별로 되지 않는 컬럼에 대해서 index를 생성한 경우에는 쿼리 성능이 훨씬 좋아진 것을 확인할 수 있다.

다음의 쿼리문을 보자

해당 테이블의 인덱스는 다음과 같이 이루어져 있다.

이러한 경우 이름과 성에 대해서 index를 만들어 조회를 한다면 훨씬 좋은 성능을 보일 수 있다. 다음의 쿼리를 보자.

성, 이름 컬럼으로 인덱스를 만든 후 같은 쿼리를 조회했다. 무려 위에 쿼리보다 41배 빨라졌다.

쿼리 실행 계획을 통해 차이를 확인해 보자.

인덱스를 만들어 조회하는 경우 훨씬 적은 row를 탐색하고 속도도 빨라지는것을 확인할 수 있다.

마치며

이렇듯 인덱스를 제대로 사용한다면 쿼리의 성능을 훨씬 향상 시킬 수 있지만 제대로 이해하지 않고 쓴다면 오히려 쿼리의 성능을 더 악화시킬 수 있다. 위의 상황 이외에도 성능을 향상 시킬 수 있는 상황((성별, 성)을 인덱스로 만들기 보다는 (성, 성별)을 인덱스로 만들어 겹치는 데이터가 많은 컬럼일수록 뒤에 보내어 검색 최적화 하기)은 존재하니 인덱스에 대해 잘 이해하고 사용하도록 하자.