MYSQL 인덱스 튜닝

안녕하세요. 호갱노노 BE팀 폴 입니다.

백엔드 개발 업무를 하다보면 데이터베이스에 쿼리하는 일이 잦은데요. 그러다보면 종종 성능을 고려한 SQL을 작성해야하는 고민에 마주하게 됩니다.

필요한 데이터를 어떻게 테이블에서 조회하고 갱신할지에 대해 최적의 방법을 생각하게 되는데요.

이번 글에서는 인덱스 성능이 안 나오는 몇몇 사례를 중심으로 SQL의 비효율을 제거하는 과정을 (특히 인덱스 중심으로) 정리해 보도록 하겠습니다.

TOC

1. SQL 튜닝 개요
2. 인덱스 튜닝 세부 분류
3. 인덱스가 있는데도 인덱스를 안타는 케이스
4. 인덱스를 타긴 타는데 성능이 안나오는 케이스
5. 인덱스 스캔 비효율은 없지만 성능이 안나오는 케이스
6. 인덱스가 너무 많이 있는 케이스
7. 마무리

SQL 튜닝 개요

SQL 튜닝은 동일한 쿼리 결과를 더 빠르고 더 가볍게 개선하는 모든 행위를 말합니다.

SQL 튜닝의 성능지표는 cpu 시간이나 block IO 등을 통해 평가합니다. 동일한 결과를 조회하는데 DBMS가 얼마나 더 적은 자원을 사용했느냐가 성능 좋은 쿼리의 기준이 됩니다.

SQL의 튜닝 포인트들은 인덱스 튜닝, 조인 튜닝, 소트 튜닝, DML 튜닝 등이 있는데요. 이번 글에서는 인덱스 튜닝에 대해서 다뤄보도록 하겠습니다.

※인덱스 튜닝 관련 배경설명들이 매우 방대한 관계로 아래 항목들에 대한 자세한 설명은 각 항목에 연결해드린 외부 자료 참고 부탁드리겠습니다.

• explain, explain analyze
  - https://dev.mysql.com/doc/refman/8.0/en/explain-output.html
  - https://dev.mysql.com/blog-archive/mysql-explain-analyze/
• storage architecture
  - https://blog.ex-em.com/1699
• block io
  - http://www.gurubee.net/lecture/3117
• clustered index / nonclustered index
• single block io / multi block io
• table random access / table sequential access
• buffer pool access, latch

인덱스 튜닝 세부 분류

인덱스 튜닝은 아래와 같이 세부적으로 나누어 볼 수 있습니다.

1. 인덱스를 타지 않는 경우
   1.1. 인덱스가 없을때
   — 인덱스 생성
   1.2. 인덱스가 있을때
   — 인덱스 컬럼 가공으로 인한 성능저하 여부 확인
   — 인덱스 손익분기점 확인 (=테이블 풀스캔이 더 효율적인지 확인)
2. 인덱스를 타는 경우
   2.1. 인덱스 스캔 비효율 (인덱스 스캔량이 최종rows 보다 훨씬 많을때)
   — 인덱스 필터링으로 동작하는 인덱스 스캔 개선
   2.2. 인덱스 스캔 비효율은 없지만, 테이블 엑세스가 많을때
   — 인덱스만 읽고 멈추도록 개선
3. 불필요한/중복인 인덱스 제거or통합
   — DML 성능 향상
   — 인덱스 다이브 성능 개선

이 중에서 아래 4가지 항목에 대해 사례와 함께 살펴보도록 하겠습니다.

• 인덱스가 있는데도 인덱스를 안타는 상황 → 인덱스 손익 분기점
• 인덱스를 타긴 타는데 성능이 안나오는 상황 → 인덱스 스캔 비효율
• 인덱스 스캔 비효율은 없지만 성능이 안나오는 상황 → 테이블 랜덤엑세스 최소화
• 인덱스가 너무 많이 있는 상황 → 중복 인덱스 최적화

※ 테스트 환경은 아래와 같이 구성했습니다.

• mysql 8.0.34 (https://registry.hub.docker.com/_/mysql/)
• default my.cnf (zero config)

인덱스가 있는데도 인덱스를 안타는 상황 → 인덱스 손익 분기점

인덱스 스캔으로 잘 동작하던 SQL이 어느날 갑자기 테이블 풀스캔으로 동작하는 상황이 있습니다.

옵티마이저가 인덱스 레인지 스캔보다 테이블 풀스캔을 선택했기 때문인데요. 실제로 io 가 어떻게 발생하길래 옵티마이저가 그런 선택을 했는지 살펴보도록 하겠습니다.

실험을 위한 데이터는 국토부 실거래 데이터(http://rtdown.molit.go.kr)를 사용했습니다.

<거래금액>      82,500
</거래금액><거래유형>중개거래
</거래유형><건축년도>2002
</건축년도><년>2023
</년><등기일자>23.04.11
</등기일자><법정동> 영등포동
</법정동><아파트>영등포푸르지오
</아파트><월>3
</월><일>11
</일><전용면적>59.912
</전용면적><중개사소재지>서울 영등포구
</중개사소재지><지번>647
</지번><지역코드>11560
</지역코드><층>10
</층><해제사유발생일> 
</해제사유발생일><해제여부> 
</해제여부>

데이터의 스키마는 대략 아래와 같습니다.

• 지역코드
• 매물구분 (아파트,오피스텔,연립다세대)
• 거래구분 (매매,전월세)
• 거래일자
• 매매금액
• 보증금
• 월세
• 기타
  - 갱신요구권
  - 해제사유발생일
  - 등기일자
  - …

위 데이터를 기반으로 2020년도 이후 분량만 뽑아서 아래와 같이 아파트 실거래 테이블을 구축했습니다.

create table T
(
	id int unsigned auto_increment
		primary key,
	지역코드 varchar(5) not null,
	거래년월 int unsigned not null,
  	...
);

create index IDX_01 on T (거래년월);
...

이 테이블을 특정 거래일자 전후로 실행계획이 다르게 동작하는데요. 아래와 같이 인덱스가 있는데도 인덱스를 타지않고 풀스캔으로 동작하는 경우가 등장했습니다.

select count(*) from T
# 2034128건

select * from T where 거래년월 >= 202307
# 96977건 (range scan)

select * from T where 거래년월 >= 202306
# 134176건 (range scan)

select * from T where 거래년월 >= 202305
# 173069건 (full scan) <<< 갑자기 인덱스를 안탄다..?

위 상황과 관련하여 mysql 공식문서(table-scan-avoidance) 에서는 아래와 같이 4가지 상황을 가이드하고 있습니다.

• The table is so small that it is faster to perform a table scan than to bother with a key lookup. This is common for tables with fewer than 10 rows and a short row length.
• There are no usable restrictions in the ON or WHERE clause for indexed columns.
• You are comparing indexed columns with constant values and MySQL has calculated (based on the index tree) that the constants cover too large a part of the table and that a table scan would be faster. See Section 8.2.1.1, “WHERE Clause Optimization”.
• You are using a key with low cardinality (many rows match the key value) through another column. In this case, MySQL assumes that by using the key probably requires many key lookups and that a table scan would be faster.

3,4 항목을 보시면 옵티마이저가 “인덱스 키 조회 비용이 테이블 스캔 비용보다 더 크다고 판단” 하면, 인덱스가 있는데도 불구하고 테이블 풀 스캔을 사용한다고 설명합니다.

인덱스 스캔보다 테이블 풀스캔 비용이 더 낮아지는 지점을 “인덱스 손익 분기점” 이라고 하는데요.

(이미지 출처 : https://richardfoote.wordpress.com/2012/06/08/indexes-vs-full-table-scan-picture-vs-1000-words-pictures-of-lily/)

위 그래프처럼 테이블 풀스캔의 비용은 일정한데 비해, 인덱스 스캔은 조회 건수에 비례하고 있습니다.

또한 쿼리 구조나 시스템 상황 등에 따라 기울기가 조금씩 달라지게 되는데요. 인덱스 스캔의 비용 직선이 테이블 풀스캔과 만나는 지점 어딘가에서 옵티마이저는 인덱스를 사용하지 않는 실행계획을 세우게 됩니다.

실제로 그렇게 동작하는지 확인해보겠습니다.

옵티마이저 테이플 풀스캔 vs 힌트 인덱스 레인지 스캔 : io 비교 (상세)

• https://gist.github.com/paulan-zb/51a7c8af8e9161faf129fa3154b93ffb

옵티마이저 테이플 풀스캔 vs 힌트 인덱스 레인지 스캔 : io 비교 (요약)

/**
 * 옵티마이저 풀스캔 vs 힌트 인덱스스캔 성능비교 정리
 */
## 옵티마이저 풀스캔
# => 논리IO: 145788-83717 = 62071
# => 물리IO: 36651-21307 = 15344

## 힌트 인덱스스캔
# => 논리IO: 275930-16207 = 259723
# => 물리IO: 3483-1205 = 2278

## 논리IO 비교 : 
# 259723 / 62071 = 4.18428896
# 259723 - 62071 = 197652
# => 인덱스스캔이 테이블풀스캔보다 논리IO가 훨씬 많이 발생했음 (풀스캔 대비 400% 수준)
# => 인덱스스캔에서 인덱스트리의 root-branch-leaf노드(block)를 각각 탐색하는 비용
#    + 최종 leaf노드에 담겨있는 clustered index key(여기에서는 pk id) 로 테이블엑세스를 single block io 로 접근함 (1요청 1블록)
# => 테이블스캔에서는 데이터block 을 곧바로 접근 + multi block io 방식으로 접근함 (1요청 N블록 → 서버/os 설정에 따라 달라질 수 있음)

## 물리IO 비교 : 
# 2278 / 15344 = 0.14846194
# 15344 - 2278 = 13066
# => 힌트인덱스스캔이 옵티마이저풀스캔보다 더 적은 물리IO가 발생함 (풀스캔 대비 14% 수준)
# => 인덱스스캔에서는 많은 block io 비용을 사용해서 최종row의 key를 선정한 다음에 테이블엑세스를 진행한 반면,
# => 테이블스캔에서는 일단 필요한 블록을 다 읽은 다음에 최종row 를 필터링했기 때문.

위 비교 자료를 통해 아래와 같이 결론을 정리할 수 있습니다.

• 스캔량이 인덱스 손익 분기점을 지나면, 테이블 풀스캔보다 인덱스 스캔에서 논리IO 가 더 많이 발생함
• 옵티마이저는 이를 알고 풀스캔을 실행계획으로 선택함

인덱스 스캔은 결국 소량의 데이터를 찾는 목적에서 잘 동작합니다. 많은 양의 데이터를 조회하는 쿼리에서는 적합하지 않으므로 다른 조회 전략을 찾는게 유리할 수 있습니다.

즉, 인덱스 스캔 에서는

1. 인덱스트리를 탐색하는 비용과
   버퍼캐시키체인 -> 래치 -> 버퍼풀에서 인덱스엑세스(없으면 버퍼풀 로딩) -> 인덱스필터링 -> 최종 대상 리프노드에 저장되어있는 클러스터드인덱스키(PK)로 테이블엑세스(이 역시 버퍼풀에서 먼저 찾고 없으면 로딩)
2. 최종 인덱스 리프노드에서 뽑은 clustered index key 로 테이블 로우를 읽는 비용(random access) 을 합한 비용이 발생하고,

테이블 풀스캔 에서는

1. 전체 테이블 로우를 다 읽는 비용(sequential access) 만 발생합니다.

너무 많은 대상을 인덱스로 찾으면, 전체 테이블을 읽는 비용보다 인덱스를 찾는 비용이 훨씬 더 많은 비용이 발생할 수도 있습니다. 인덱스를 안탄다고 강제로 힌트를 지정하더라도 사실은 그게 DBMS에 더 많은 io 비용을 만들어버리게 되는 것입니다.

다시 말해서, 테이블 풀스캔이 항상 나쁜 것은 아니고, 바꿔말해 인덱스 스캔이 항상 좋은 것도 아닙니다. 각 상황에 맞는 최적의 스캔방법을 선정하는 것이 중요합니다.

인덱스를 타긴 타는데 성능이 안나오는 상황 → 인덱스 스캔 비효율

인덱스필터링조건 vs 인덱스엑세스조건 : block io 비교 (인덱스 100개 읽어서 타겟팅완료 vs 인덱스 10개 읽어서 타겟팅 완료)

튜닝포인트 : 인덱스 필터링조건 → 엑세스조건 으로 변경

인덱스를 잘 탔는데도 성능이 떨어지는 상황이 있을 수 있습니다.

인덱스 스캔 자체에서 비효율이 발생하는 경우인데요. 복합인덱스에서는 인덱스 구성 컬럼에서 범위조건이 사용되면, 해당 컬럼 이후의 컬럼들은 인덱스 스캔이 “엑세스” 가 아니라 “필터링” 으로 동작합니다.

예를 들면, 동일테이블 동일인덱스 에서

• 상황1 : 100건 스캔 → 5건 필터링 → 5건 테이블엑세스
• 상황2 : 20건 스캔 → 5건 필터링 → 5건 테이블엑세스

로 동작할수도 있다는 의미 입니다.

위 상황을 만들어서 한번 살펴보도록 하겠습니다.

아래와 같은 테이블을 준비했습니다.

create table T
(
	id int unsigned not null
		primary key,
	지역코드 varchar(5) not null,
	거래년월 int unsigned not null,
  ...
);

create index IDX_02 on T (지역코드, 거래년월);
...

위 테이블에서 서울지역의 2023년도 매매건수를 확인해보겠습니다.

select count(*)
from T
where 지역코드 like '11%'
and 거래년월 between 202301 and 202309
-- 27498건

explain
select count(*)
from T
where 지역코드 like '11%'
and 거래년월 between 202301 and 202309
# +--+-----------+-----+----------+-----+-------------+------+-------+----+------+--------+--------------------------------+
# |id|select_type|table|partitions|type |possible_keys|key   |key_len|ref |rows  |filtered|Extra                           |
# +--+-----------+-----+----------+-----+-------------+------+-------+----+------+--------+--------------------------------+
# |1 |SIMPLE     |T    |null      |range|IDX_02       |IDX_02|206    |null|360246|11.11   |Using index condition; Using MRR|
# +--+-----------+-----+----------+-----+-------------+------+-------+----+------+--------+--------------------------------+

위에서 만든 IDX_02 인덱스를 레인지 스캔으로 잘 타는 것처럼 보입니다.

하지만 실제로는 어떻게 스캔이 동작하는지 좀 더 상세하게 확인해보겠습니다.

explain analyze
select count(*)
from T
where 지역코드 like '11%'
and 거래년월 between 202301 and 202309
# -> Aggregate: count(0)  (cost=84718 rows=1) (actual time=132..132 rows=1 loops=1)
#     -> Filter: ((T.`지역코드` like '11%') and (T.`거래년월` between 202301 and 202308))  (cost=80715 rows=40023) (actual time=1.19..130 rows=25620 loops=1)
#         -> Covering index range scan on t using IDX_02 over ('11￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿' <= 지역코드 <= '11￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿' AND 202301 <= 거래년월 <= 202308)  (cost=80715 rows=360246) (actual time=0.0297..96.3 rows=167787 loops=1)

explain analyze 에서 분석한 내용을 확인해보면,
1) 인덱스 레인지 스캔을 통해 167787건 읽은 다음
2) 140289건이 필터링되어 25620건이 출력으로 나와
3) 최종적으로 count함수로 1건이 Aggregate되었다고 설명하고 있습니다.
27498/167787 = 0.16388636 (약 16%) 즉 16% 를 얻기위해 84% 를 읽고 그냥 버려버리는 매우 비효율적인 처리로 동작했습니다.

사실 이 167787건은 지역코드 11(서울지역) 에 해당되는 데이터 건수이고, 25620건은 지역코드 11(서울지역) + 거래년월 2023–01 ~ 2023–09에 해당되는 데이터 건수인데요.

위에서 만든 IDX_02 인덱스는 (지역코드, 거래년월) 컬럼으로 만들어졌고, 위 쿼리는 지역코드 거래년월 만 사용했는데, 왜 두 컬럼중 앞쪽 지역코드 컬럼만 사용되었을까요?

그 이유는 복합인덱스의 경우, 인덱스 선행컬럼이 조건절에 없거나 =조건이 아니면, 인덱스 스캔 과정에 비효율이 발생하기 때문입니다.

인덱스 스캔은 액세스 조건과 필터 조건으로 구분되어 동작하는데요.

• 인덱스 엑세스 조건 : 인덱스 스캔 범위 결정
• 인덱스 필터 조건 : 테이블 엑세스 여부 결정
• 테이블 필터 조건 : 최종 결과집합 포함 여부 결정

위 문제의 쿼리에서는 인덱스 선행컬럼인 지역코드를 지역코드 like '11%' 와 같이 범위검색으로 접근했기 때문에, 인덱스 후행컬럼인 거래년월 컬럼조건 거래년월 between 202301 and 202309 이 스캔범위를 결정하는데 사용되지 못했기 때문입니다.

이런 유형의 쿼리는 선행컬럼의 범위검색 조건을 IN-List 조건으로 변경하는 것으로 개선할 수 있습니다.

explain
select count(*)
from T
where 지역코드 in (
    '11000', '11110', '11140', '11170', '11200', '11215', '11230', '11260', '11290', '11305', '11320', '11350', '11380', '11410', '11440', '11470', '11500', '11530', '11545', '11560', '11590', '11620', '11650', '11680', '11710', '11740'
)
and 거래년월 between 202301 and 202309
# +--+-----------+-----+----------+-----+-------------+------+-------+----+-----+--------+------------------------+
# |id|select_type|table|partitions|type |possible_keys|key   |key_len|ref |rows |filtered|Extra                   |
# +--+-----------+-----+----------+-----+-------------+------+-------+----+-----+--------+------------------------+
# |1 |SIMPLE     |T    |null      |range|IDX_02       |IDX_02|206    |null|27499|100     |Using where; Using index|
# +--+-----------+-----+----------+-----+-------------+------+-------+----+-----+--------+------------------------+
# => 인덱스 레인지스캔 탐

explain analyze
select count(*)
from T
where 지역코드 in (
    '11000', '11110', '11140', '11170', '11200', '11215', '11230', '11260', '11290', '11305', '11320', '11350', '11380', '11410', '11440', '11470', '11500', '11530', '11545', '11560', '11590', '11620', '11650', '11680', '11710', '11740'
)
and 거래년월 between 202301 and 202309
# -> Aggregate: count(0)  (cost=8912 rows=1) (actual time=34.6..34.6 rows=1 loops=1)
#     -> Filter: ((T.`지역코드` in ('11000','11110','11140','11170','11200','11215','11230','11260','11290','11305','11320','11350','11380','11410','11440','11470','11500','11530','11545','11560','11590','11620','11650','11680','11710','11740')) and (T.거래년월 between 202301 and 202309))  (cost=6162 rows=27499) (actual time=0.0413..32.8 rows=27498 loops=1)
#         -> Covering index range scan on t using IDX_02 over (지역코드 = '11000' AND 202301 <= 거래년월 <= 202309) OR (지역코드 = '11110' AND 202301 <= 거래년월 <= 202309) OR (24 more)  (cost=6162 rows=27499) (actual time=0.0368..16.8 rows=27498 loops=1)

27498건 읽어서 최종 27498건을 사용했고, 27498/27498 = 1.0 (100%) 스캔 비효율이 없는 쿼리로 개선되었습니다.

IN-List 조건은 내부적으로 IN절을 N번 반복하는 형태로 동작하는데요. 이는 explain analyze 에서 레인지스캔 분석 부분을 살펴보면 (지역코드 = '11000' AND 202301 <= 거래년월 <= 202309) OR (지역코드 = '11110' AND 202301 <= 거래년월 <= 202309) OR (24 more) 로 표현되어 있는 것으로 확인할 수 있습니다.

즉, 인덱스 선행컬럼인 지역코드 조건이 = 조건으로 총 26회 펼쳐져서 동작하게 되어, 지역코드 + 거래년월 조건으로 정확하게 필요한 영역(27498건) 만 스캔하도록 동작하게 되었습니다.

※ 인덱스 탐색 동작에 대한 내용은 다음 기회에 좀 더 자세히 설명해보도록 하겠습니다.

※ IN-LIST 변환에 너무 많은 값이 들어가게 되면 오히려 성능이 나빠질 수도 있습니다. (ex. eq_range_index_dive_limit)

이처럼 explain 결과에서 인덱스 스캔 사용여부만 확인하고 넘어간다면, 비효율적인 인덱스 스캔을 놓치게 될 수도 있습니다. 인덱스 스캔으로 동작하지만 실제 비효율적인 동작이 있는지 explain analyze 를 통해 한번 더 확인해본다면 예상하지 못한 성능저하를 예방할 수 있습니다.

인덱스 스캔 비효율은 없지만 성능이 안나오는 상황 → 테이블 랜덤 엑세스 최소화

테이블엑세스 vs 인덱스만읽기 : block io 비교 (인덱스 10개 테이블 5개 읽어서 타겟팅완료 vs 인덱스 10개-5개필터링 해서 타겟팅완료)

튜닝포인트 : 인덱스 컬럼 추가

인덱스 스캔 비효율은 없지만 그래도 성능이 안나오는 경우도 있습니다.

이런 케이스는 실제 데이터를 스캔하는 일량 자체가 많은 상황이기 때문에, 조회 조건이나 순서를 조정하는 등의 SQL 외적인 방법을 고려하는게 바람직하지만,

그럼에도 불구하고 특정 상황에서는 SQL 레벨에서 성능을 개선하는 방법도 있습니다.

아래 데이터를 통해 확인해보겠습니다.

create table T
(
	id int unsigned not null
		primary key,
	지역코드 varchar(5) not null,
	거래년월 int unsigned not null,
	거래금액 int unsigned not null,
  ...
);

create index IDX_02 on T (지역코드, 거래년월);
...

## 서울지역 2020~2022년 사이 시군구별 최고매매가 계산
select 지역코드, max(거래금액)
from T
where 지역코드 in ('11000', '11110', '11140', '11170', '11200', '11215', '11230', '11260', '11290', '11305', '11320', '11350', '11380', '11410', '11440', '11470', '11500', '11530', '11545', '11560', '11590', '11620', '11650', '11680', '11710', '11740')
and 거래년월 between 202001 and 202212
group by 지역코드;

explain
select 지역코드, max(거래금액)
from T
where 지역코드 in ('11000', '11110', '11140', '11170', '11200', '11215', '11230', '11260', '11290', '11305', '11320', '11350', '11380', '11410', '11440', '11470', '11500', '11530', '11545', '11560', '11590', '11620', '11650', '11680', '11710', '11740')
and 거래년월 between 202001 and 202212
group by 지역코드;
# +--+-----------+-----+----------+-----+-------------+------+-------+----+------+--------+---------------------+
# |id|select_type|table|partitions|type |possible_keys|key   |key_len|ref |rows  |filtered|Extra                |
# +--+-----------+-----+----------+-----+-------------+------+-------+----+------+--------+---------------------+
# |1 |SIMPLE     |t    |null      |range|IDX_02       |IDX_02|206    |null|195035|100     |Using index condition|
# +--+-----------+-----+----------+-----+-------------+------+-------+----+------+--------+---------------------+
# => 레인지스캔 잘 탐

explain analyze
select 지역코드, max(거래금액)
from T
where 지역코드 in ('11000', '11110', '11140', '11170', '11200', '11215', '11230', '11260', '11290', '11305', '11320', '11350', '11380', '11410', '11440', '11470', '11500', '11530', '11545', '11560', '11590', '11620', '11650', '11680', '11710', '11740')
and 거래년월 between 202001 and 202212
group by 지역코드
# -> Group aggregate: max(T.`거래금액`)  (cost=212457 rows=238) (actual time=10.8..892 rows=25 loops=1)
#     -> Index range scan on t using IDX_02 over (지역코드 = '11000' AND 202001 <= DEAL_YM <= 202212) OR (지역코드 = '11110' AND 202001 <= DEAL_YM <= 202212) OR (24 more), with index condition: ((T.`지역코드` in ('11000','11110','11140','11170','11200','11215','11230','11260','11290','11305','11320','11350','11380','11410','11440','11470','11500','11530','11545','11560','11590','11620','11650','11680','11710','11740')) and (T.거래년월 between 202001 and 202212))  (cost=192953 rows=195035) (actual time=0.061..857 rows=140289 loops=1)
# => 인덱스스캔 140289건 → 테이블스캔 140289건 (100%, 비효율없음)
# => 140289건 → group by 25건

IDX_02 인덱스를 레인지 스캔으로 비효율없이 잘 쿼리하는 것 같습니다.

show status like 'Innodb_buffer_pool_read%'
# +-------------------------------------+-----+
# |Variable_name                        |Value|
# +-------------------------------------+-----+
# |Innodb_buffer_pool_read_ahead_rnd    |0    |
# |Innodb_buffer_pool_read_ahead        |0    |
# |Innodb_buffer_pool_read_ahead_evicted|0    |
# |Innodb_buffer_pool_read_requests     |16453|
# |Innodb_buffer_pool_reads             |1432 |
# +-------------------------------------+-----+

select 지역코드, max(거래금액)
from T
where 지역코드 in ('11000', '11110', '11140', '11170', '11200', '11215', '11230', '11260', '11290', '11305', '11320', '11350', '11380', '11410', '11440', '11470', '11500', '11530', '11545', '11560', '11590', '11620', '11650', '11680', '11710', '11740')
and 거래년월 between 202001 and 202212
group by 지역코드;

show status like 'Innodb_buffer_pool_read%'
# +-------------------------------------+------+
# |Variable_name                        |Value |
# +-------------------------------------+------+
# |Innodb_buffer_pool_read_ahead_rnd    |0     |
# |Innodb_buffer_pool_read_ahead        |0     |
# |Innodb_buffer_pool_read_ahead_evicted|0     |
# |Innodb_buffer_pool_read_requests     |474380|
# |Innodb_buffer_pool_reads             |4003  |
# +-------------------------------------+------+
# => 474380-16453 = 457927 (논리read io)

위 쿼리는 스캔 비효율은 없지만 read io 일량(인덱스 스캔 + 테이블 랜덤 엑세스) 자체가 많은 케이스 입니다. 140289건을 읽기위해 457927만큼의 read io 가 발생했습니다.

이런 경우에는 아래와 같이 인덱스에 컬럼을 추가하는 것으로 인덱스까지만 읽고 테이블 랜덤 엑세스가 발생하지 않도록 유도할 수 있습니다.

-- create index IDX_02 on T (지역코드, 거래년월);
create index IDX_03 on T (지역코드, 거래년월, 거래금액);

## 서울지역 2020~2022년 사이 시군구별 최고매매가 계산
select /*+ index(T IDX_03) */ 지역코드, max(거래금액)
from T
where 지역코드 in ('11000', '11110', '11140', '11170', '11200', '11215', '11230', '11260', '11290', '11305', '11320', '11350', '11380', '11410', '11440', '11470', '11500', '11530', '11545', '11560', '11590', '11620', '11650', '11680', '11710', '11740')
and 거래년월 between 202001 and 202212
group by 지역코드;

explain
select /*+ index(T IDX_03) */ 지역코드, max(거래금액)
from T
where 지역코드 in ('11000', '11110', '11140', '11170', '11200', '11215', '11230', '11260', '11290', '11305', '11320', '11350', '11380', '11410', '11440', '11470', '11500', '11530', '11545', '11560', '11590', '11620', '11650', '11680', '11710', '11740')
and 거래년월 between 202001 and 202212
group by 지역코드
# +--+-----------+-----+----------+-----+-------------+------+-------+----+------+--------+------------------------+
# |id|select_type|table|partitions|type |possible_keys|key   |key_len|ref |rows  |filtered|Extra                   |
# +--+-----------+-----+----------+-----+-------------+------+-------+----+------+--------+------------------------+
# |1 |SIMPLE     |t    |null      |range|IDX_03       |IDX_03|206    |null|217038|100     |Using where; Using index|
# +--+-----------+-----+----------+-----+-------------+------+-------+----+------+--------+------------------------+
# => 레인지스캔 잘 탐

explain analyze
select /*+ index(T IDX_03) */ 지역코드, max(거래금액)
from T
where 지역코드 in ('11000', '11110', '11140', '11170', '11200', '11215', '11230', '11260', '11290', '11305', '11320', '11350', '11380', '11410', '11440', '11470', '11500', '11530', '11545', '11560', '11590', '11620', '11650', '11680', '11710', '11740')
and 거래년월 between 202001 and 202212
group by 지역코드
# -> Group aggregate: max(T.`거래금액`)  (cost=70364 rows=238) (actual time=2.25..218 rows=25 loops=1)
#     -> Filter: ((T.`지역코드` in ('11000','11110','11140','11170','11200','11215','11230','11260','11290','11305','11320','11350','11380','11410','11440','11470','11500','11530','11545','11560','11590','11620','11650','11680','11710','11740')) and (T.거래년월 between 202001 and 202212))  (cost=48660 rows=217038) (actual time=0.0412..187 rows=140289 loops=1)
#         -> Covering index range scan on t using IDX_03 over (지역코드 = '11000' AND 202001 <= 거래년월 <= 202212) OR (지역코드 = '11110' AND 202001 <= 거래년월 <= 202212) OR (24 more)  (cost=48660 rows=217038) (actual time=0.0369..104 rows=140289 loops=1)
# => 140289건 읽어서 140289건 필터링 (100%, 비효율없음)
# => 인덱스스캔 140289건 → group by 25건 (커버링인덱스에서 필터링 동작, 테이블스캔이 사라짐)

show status like 'Innodb_buffer_pool_read%';
# +-------------------------------------+-----+
# |Variable_name                        |Value|
# +-------------------------------------+-----+
# |Innodb_buffer_pool_read_ahead_rnd    |0    |
# |Innodb_buffer_pool_read_ahead        |0    |
# |Innodb_buffer_pool_read_ahead_evicted|0    |
# |Innodb_buffer_pool_read_requests     |16320|
# |Innodb_buffer_pool_reads             |1289 |
# +-------------------------------------+-----+

select /*+ index(T IDX_03) */ 지역코드, max(거래금액)
from T
where 지역코드 in ('11000', '11110', '11140', '11170', '11200', '11215', '11230', '11260', '11290', '11305', '11320', '11350', '11380', '11410', '11440', '11470', '11500', '11530', '11545', '11560', '11590', '11620', '11650', '11680', '11710', '11740')
and 거래년월 between 202001 and 202212
group by 지역코드;

show status like 'Innodb_buffer_pool_read%';
# +-------------------------------------+-----+
# |Variable_name                        |Value|
# +-------------------------------------+-----+
# |Innodb_buffer_pool_read_ahead_rnd    |0    |
# |Innodb_buffer_pool_read_ahead        |0    |
# |Innodb_buffer_pool_read_ahead_evicted|0    |
# |Innodb_buffer_pool_read_requests     |33105|
# |Innodb_buffer_pool_reads             |1490 |
# +-------------------------------------+-----+
# => 33105-16320 = 16785 (read io)

IDX_02 에 비해 IDX_03 에서 read io 가 큰 폭으로(asis: 457927 → tobe: 16785) 줄어들었습니다.

/**
 * 결론
 */
## 비효율은 없지만 테이블랜덤엑세스(read io 실제 일량)가 많은 상황
# => 474380-16453 = 457927 (read io)
# => 인덱스스캔 140289건 → 테이블스캔 140289건 (100%, 비효율없음) → group by 25건

## 인덱스 컬럼추가로 테이블랜덤엑세스 최소화
# => 33105-16320 = 16785 (read io)
# => 인덱스스캔 140289건 → group by 25건 (테이블랜덤엑세스 사라짐)

기존 대비 3% (16785 / 457927 = 0.03665431) 수준의 io 만 사용하는 쿼리로 개선되었습니다. 쿼리가 인덱스만 읽고 처리를 완료할 수 있도록 변경되어서 테이블 랜덤 엑세스가 사라졌고, 이에 관련 비용들이 줄어들게 된 것입니다.

커버링인덱스의 실제 io 프로파일링을 확인해보니, 생각했던 것 보다 더 많은 io 를 줄일수 있다는 것을 알 수 있었습니다. (테이블 랜덤 엑세스 비용이 그 만큼 비싸다는 반증으로 볼 수 있겠습니다.)

커버링인덱스는 유용한 인덱스 튜닝 방법이긴 하지만, 복잡한 인덱스를 여기저기 과도하게 생성하게 되면 또 다른 문제가 발생할 수 있습니다.

이어지는 주제에서 과도한 인덱스가 어떤 문제를 발생하는 지에 대해 자세히 다뤄보도록 하겠습니다.

인덱스가 너무 많이 있는 상황 → 중복 인덱스 최적화

중복인덱스 테이블 vs 인덱스없는 테이블 vs 인덱스 최적화 테이블 : query profile 비교

튜닝포인트 : 인덱스 통폐합

인덱스를 줄이는 것도 인덱스 튜닝이 될 수 있습니다.

너무 많은 인덱스, 불필요한 인덱스는 DML 성능을 떨어뜨립니다. 또한 저장공간의 낭비, 의도하지 않은 인덱스 선정 등과 같은 부작용도 함께 발생할 수 있습니다.

중복인덱스 테이블과 인덱스를 최적화한 테이블 간의 query profile 을 비교해보겠습니다.

실험을 위한 테이블을 아래와 같이 준비했습니다.

create table 아파트_실거래
(
	id int unsigned auto_increment
		primary key,
	거래일자 datetime not null,
	아파트코드 int unsigned not null,
	전용면적 decimal unsigned default 0 not null,
	...
);
create index IDX_01 on 아파트_실거래 (전용면적);
create index IDX_02 on 아파트_실거래 (거래일자);
create index IDX_03 on 아파트_실거래 (아파트코드, 전용면적, 거래일자);
create index IDX_04 on 아파트_실거래 (아파트코드, 전용면적);
create index IDX_05 on 아파트_실거래 (아파트코드);

## 인덱스 없는 테이블
create table 아파트_실거래_noindex like 아파트_실거래;
insert into 아파트_실거래_noindex select * from 아파트_실거래;

## 인덱스 통폐합 테이블
create table 아파트_실거래_compact_index like 아파트_실거래;
insert into 아파트_실거래_compact_index select * from 아파트_실거래;
create index IDX_02 on 아파트_실거래_compact_index (거래일자);
create index IDX_03 on 아파트_실거래_compact_index (아파트코드, 전용면적, 거래일자);

중복인덱스 테이블 vs 인덱스없는 테이블 vs 인덱스 최적화 테이블 : query profile 비교 (상세)

• https://gist.github.com/paulan-zb/6245874265333577523746084d2de9f5

중복인덱스 테이블 vs 인덱스없는 테이블 vs 인덱스 최적화 테이블 : query profile 비교 (요약)

/**
 * 중복인덱스 vs 인덱스없음 vs 인덱스통폐합 DML 성능비교 정리
 */
## 중복인덱스 테이블
# block io :
# => read io : 15876 → 450088 => 434212
# => write io :  1857 → 201534 => 199677

## 인덱스 없는 테이블
# block io :
# => read io : 41120 → 82548 => 41428
# => write io :  3412 → 23887 => 20475

## 인덱스통폐합 테이블
# block io :
# => read io : 16932 → 176005 => 159073
# => write io :  1856 → 82184 => 80328


## 결론1
# 동일 스키마 테이블에 10000건 insert 에 대해서
# write io 는 중복인덱스(199677) > 인덱스통폐합(80328) > 인덱스없음(20475) 의 형상을 띔
# => 중복인덱스(199677) / 인덱스통폐합(80328) = 2.48577084 (약 250%)
# => 중복인덱스(199677) / 인덱스없음(20475) = 9.75223443 (약 970%)


## 결론2
# DML 쿼리에서는 테이블 CUD 뿐만아니라, 인덱스 갱신/정렬 도 함께 발생하기때문에
# read io 역시 중복인덱스에서 훨씬 많이 발생함
# => 중복인덱스(434212) / 인덱스통폐합(159073) = 2.72963985 (약 270%)
# => 중복인덱스(434212) / 인덱스없음(41428) = 10.48112388 (약 1050%)


## 결론3
select table_schema, table_name,
       round(sum(data_length)/1024/1024,2) as 'table Size(Mb)',
       round(sum(index_length)/1024/1024,2) as 'index Size(Mb)',
       max(table_rows) as rows_number,
       max(auto_increment) as auto_increment_value
from information_schema.tables
where table_schema = 'test' and table_name like '아파트_실거래%'
group by table_schema, table_name;
# +------------+---------------------+--------------+--------------+-----------+--------------------+
# |TABLE_SCHEMA|TABLE_NAME           |table Size(Mb)|index Size(Mb)|rows_number|auto_increment_value|
# +------------+---------------------+--------------+--------------+-----------+--------------------+
# |test        |아파트_실거래              |82.61         |176.34        |1495158    |31963385            |
# |test        |아파트_실거래_noindex      |82.61         |0.00          |1495158    |31963385            |
# |test        |아파트_실거래_compact_index|82.61         |30.56         |1495158    |31963385            |
# +------------+---------------------+--------------+--------------+-----------+--------------------+
# => 불필요한 인덱스 최적화를 통해, 테이블보다 인덱스 사이즈가 더 큰 비효율을 개선할 수 있음
# => 중복인덱스 : 82.61(테이블) / 176.34(인덱스)
# => 인덱스통폐합 : 82.61(테이블) / 30.56(인덱스)
#     => 176.34 → 30.56 으로 asis 대비 17% 수준으로 인덱스 용량 최적화
#     => 176.34/82.61(약 210%) → 30.56/82.61(약 36%) 으로 테이블 대비 인덱스 용량 최적화
# => 인덱스없음 : 82.61(테이블) / 0(인덱스)

위 비교 자료를 통해 아래와 같이 결론을 정리할 수 있습니다.

• 불필요한 인덱스로 인해 DML 쿼리에서 상당한 비효율이 발생할 수 있음
• DML 성능 뿐만아니라, 저장공간 낭비도 발생할 수 있음

인덱스는 꼭 필요한 조회경로에 대해서만 생성하는 것이 좋습니다.

특히 중복 인덱스의 경우에는 조회/갱신 양쪽 모두에서 불필요한 성능저하가 발생할 수 있는데요. 가능하다면 중복 인덱스가 보인다면 인덱스 정리를 고려해볼 수 있겠습니다.

※중복 인덱스 정리는 해당 컬럼을 사용하는 모든 쿼리의 실행계획에 영향을 줄 수 있으므로 신중한 진행이 필요합니다.

마무리

지금까지 인덱스를 타더라도 성능이 안나올 수 있는 상황들을 살펴봤습니다.

• 인덱스가 있는데도 인덱스를 안타는 상황 → 인덱스 손익 분기점
  — 옵티마이저 테이플풀스캔 vs 힌트 인덱스레인지스캔 : block io 비교
• 인덱스를 타긴 타는데 성능이 안나오는 상황 → 인덱스 스캔 비효율
  — 인덱스필터링조건 vs 인덱스엑세스조건 : block io 비교 (인덱스 100개 읽어서 타겟팅완료 vs 인덱스 10개 읽어서 타겟팅 완료)
  — 튜닝포인트 : 인덱스 필터링조건 → 엑세스조건 으로 변경
• 인덱스 스캔 비효율은 없지만 성능이 안나오는 상황 → 테이블 랜덤엑세스 최소화
  — 테이블엑세스 vs 인덱스만읽기 : block io 비교 (인덱스 10개 테이블 5개 읽어서 타겟팅완료 vs 인덱스 10개-5개필터링 해서 타겟팅완료)
  — 튜닝포인트 : 인덱스 컬럼 추가
• 인덱스가 너무 많이 있는 상황 → 중복 인덱스 최적화
  — 중복인덱스 테이블 vs 인덱스없는 테이블 vs 인덱스 최적화 테이블 : query profile 비교
  — 튜닝포인트 : 인덱스 통폐합

이 밖에도 인덱스 튜닝 포인트는 많이 있을 수 있습니다.

• 인덱스 스킵 스캔
• 인덱스 다이브 최적화
• 인덱스를 이용한 sort 연산 생략
• …

튜닝의 핵심은 이슈 상황에 따라 어떤 지점에서 어떤 비용이 많이 발생하는지를 파악하는게 가장 중요합니다.

1) explain, explain analyze 등을 통해 쿼리가 어떻게 동작하는지 확인하고,
2) 필요하다면 쿼리 프로파일링을 통해 실제 시스템에서 cpu, block io 등이 얼마나 발생하고 있는지 검토한 다음,
3) 비효율을 제거할 수 있는 포인트를 찾고,
4) 해당 지점을 효율적으로 동작하도록 개선해 나가는게 중요하겠습니다.

감사합니다.