커버링 인덱스와 ICP(index condition pushdown)에 대한 고민

Alex96 2023. 9. 30. 02:16

사이드 프로젝트를 할때 특정 검색 기능의 성능이 느려 개선 백로그로 잡아두었었다. 다만, 회사와 기타 일정에 이리저리 치이고 치이다 보니 결국 그 사이드 프젝이 끝날 때까지 건드리지를 못했었는데, 추석 연휴를 맞이해서 갑자기 생각이 났고.. 쉬는 김에 한번 개선해보기로 했다.

개선 시작

검색 성능 개선이 목적이다 보니 해당 쿼리에 인덱스를 태울 생각을 먼저 했고, 기존엔 그 어떤 인덱스도 타고 있지 않던걸 확인했다.

테스트용 DB를 생성해서 다음과 같이 지저분한(..?) 프로시저를 태워 데이터를 적재했다.

테스트용 더미 데이터 insert

create table place( -- 테스트 대상 테이블
    id bigint auto_increment primary key,
    name varchar(150) not null,
    address varchar(250),
    latitude double,
    longitude double,
    thumbnail_image_url varchar(300) default null,
    image_ids varchar(300),
    phone varchar(20),
    created_at timestamp default now(),
    updated_at timestamp default now(),
    is_deleted boolean default false
);

DELIMITER $$
CREATE PROCEDURE myFunction()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE (i <= 50000) DO
        INSERT INTO `place`(name, address, latitude, longitude, image_ids, phone)
        VALUES (concat('테스트', cast(i as char)), concat('테스트시 테스트구 테스트로', cast(i as char), '길'), i % 120, i % 90, concat('1,2,3,4,5,', cast(i as char)), cast(i as char)),
               (concat('테스트', cast((i + 1) as char )) ,concat('테스트시 테스트구 테스트로', cast((i + 1) as char ),'길'), (i + 1) % 120, (i + 1) % 90, concat('1,2,3,4,5,', cast((i + 1) as char )), cast((i + 1) as char )),
               (concat('테스트', cast((i + 2) as char )) ,concat('테스트시 테스트구 테스트로', cast((i + 2) as char ),'길'), (i + 2) % 120, (i + 2) % 90, concat('1,2,3,4,5,', cast((i + 2) as char )), cast((i + 2) as char )),
               (concat('테스트', cast((i + 3) as char )) ,concat('테스트시 테스트구 테스트로', cast((i + 3) as char ),'길'), (i + 3) % 120, (i + 3) % 90, concat('1,2,3,4,5,', cast((i + 3) as char )), cast((i + 3) as char )),
               (concat('테스트', cast((i + 4) as char )) ,concat('테스트시 테스트구 테스트로', cast((i + 4) as char ),'길'), (i + 4) % 120, (i + 4) % 90, concat('1,2,3,4,5,', cast((i + 4) as char )), cast((i + 4) as char )),
               (concat('테스트', cast((i + 5) as char )) ,concat('테스트시 테스트구 테스트로', cast((i + 5) as char ),'길'), (i + 5) % 120, (i + 5) % 90, concat('1,2,3,4,5,', cast((i + 5) as char )), cast((i + 5) as char )),
               (concat('테스트', cast((i + 6) as char )) ,concat('테스트시 테스트구 테스트로', cast((i + 6) as char ),'길'), (i + 6) % 120, (i + 6) % 90, concat('1,2,3,4,5,', cast((i + 6) as char )), cast((i + 6) as char )),
               (concat('테스트', cast((i + 7) as char )) ,concat('테스트시 테스트구 테스트로', cast((i + 7) as char ),'길'), (i + 7) % 120, (i + 7) % 90, concat('1,2,3,4,5,', cast((i + 7) as char )), cast((i + 7) as char )),
               (concat('테스트', cast((i + 8) as char )) ,concat('테스트시 테스트구 테스트로', cast((i + 8) as char ),'길'), (i + 8) % 120, (i + 8) % 90, concat('1,2,3,4,5,', cast((i + 8) as char )), cast((i + 8) as char )),
               (concat('테스트', cast((i + 9) as char )) ,concat('테스트시 테스트구 테스트로', cast((i + 9) as char ),'길'), (i + 9) % 120, (i + 9) % 90, concat('1,2,3,4,5,', cast((i + 9) as char )), cast((i + 9) as char )),
               (concat('테스트', cast((i + 10) as char )), concat('테스트시 테스트구 테스트로', cast((i + 10) as char ),'길'), (i + 10) % 120, (i + 10) % 90, concat('1,2,3,4,5,', cast((i + 10) as char )), cast((i + 10) as char )),
               (concat('테스트', cast((i + 11) as char )), concat('테스트시 테스트구 테스트로', cast((i + 11) as char ),'길'), (i + 11) % 120, (i + 11) % 90, concat('1,2,3,4,5,', cast((i + 11) as char )), cast((i + 11) as char )),
               (concat('테스트', cast((i + 12) as char )), concat('테스트시 테스트구 테스트로', cast((i + 12) as char ),'길'), (i + 12) % 120, (i + 12) % 90, concat('1,2,3,4,5,', cast((i + 12) as char )), cast((i + 12) as char )),
               (concat('테스트', cast((i + 13) as char )), concat('테스트시 테스트구 테스트로', cast((i + 13) as char ),'길'), (i + 13) % 120, (i + 13) % 90, concat('1,2,3,4,5,', cast((i + 13) as char )), cast((i + 13) as char )),
               (concat('테스트', cast((i + 14) as char )), concat('테스트시 테스트구 테스트로', cast((i + 14) as char ),'길'), (i + 14) % 120, (i + 14) % 90, concat('1,2,3,4,5,', cast((i + 14) as char )), cast((i + 14) as char )),
               (concat('테스트', cast((i + 15) as char )), concat('테스트시 테스트구 테스트로', cast((i + 15) as char ),'길'), (i + 15) % 120, (i + 15) % 90, concat('1,2,3,4,5,', cast((i + 15) as char )), cast((i + 15) as char )),
               (concat('테스트', cast((i + 16) as char )), concat('테스트시 테스트구 테스트로', cast((i + 16) as char ),'길'), (i + 16) % 120, (i + 16) % 90, concat('1,2,3,4,5,', cast((i + 16) as char )), cast((i + 16) as char )),
               (concat('테스트', cast((i + 17) as char )), concat('테스트시 테스트구 테스트로', cast((i + 17) as char ),'길'), (i + 17) % 120, (i + 17) % 90, concat('1,2,3,4,5,', cast((i + 17) as char )), cast((i + 17) as char )),
               (concat('테스트', cast((i + 18) as char )), concat('테스트시 테스트구 테스트로', cast((i + 18) as char ),'길'), (i + 18) % 120, (i + 18) % 90, concat('1,2,3,4,5,', cast((i + 18) as char )), cast((i + 18) as char )),
               (concat('테스트', cast((i + 19) as char )), concat('테스트시 테스트구 테스트로', cast((i + 19) as char ),'길'), (i + 19) % 120, (i + 19) % 90, concat('1,2,3,4,5,', cast((i + 19) as char )), cast((i + 19) as char )),
               (concat('테스트', cast((i + 20) as char )), concat('테스트시 테스트구 테스트로', cast((i + 20) as char ),'길'), (i + 20) % 120, (i + 20) % 90, concat('1,2,3,4,5,', cast((i + 20) as char )), cast((i + 20) as char ));-- ⓓ 테이블에 i값 넣어주기
        SET i = i + 1;
    END WHILE;
END$$
DELIMITER ;
CALL myFunction();

100만건 넣으려고 했는데 105만건 들어갔다..ㅎ `i <=50000` 부분을 `<` 로 했어야 했나보다. 데이터는 많으면 많을 수록 좋지 뭐..ㅎ

이 프로시저는 5분정도 걸렸다.

기존 쿼리 테스트

테스트할 쿼리는 다음과 같다.

select
    id, name, address, latitude, longitude, phone, thumbnail_image_url
from 
    place
where 
    (latitude between ? and ?)
    and (longitude between ? and ?)
    and name like ? -- % 양쪽에 붙는 라이크문
limit 500;

일단 깡 실행

실행 계획좀 볼까?

뭐... 그 어떤 최적화도 되지 않는 순수한 자연의 상태다.

개선 방안 고민

당연히 인덱스 태울건데 두 가지가 고민되었다.

최소한의 조건절 인덱스만 걸고 ICP(index condition pushdown) 사용
커버링 인덱스 사용

일단 성능을 비교해보고 이유를 고민해보기로 했다. 성능차이가 너무 많이 날 수도 있으니...

인덱스 생성

일단 두 인덱스를 모두 생성했다.

create index ix__place__for_search on place(latitude, longitude, name, phone, address, thumbnail_image_url); # 커버링 인덱스
create index ix__place__for_search2 on place(latitude, longitude, name); # ICP 활용

인덱스 잡을때 고려한 점

1. ICP를 잘 써먹으려면 like % 붙는 컬럼을 반드시 포함시켜야한다. 그래야 인덱스에서 해당 값을 가지고 있을 수 있고, 거기서 먼저 조건절 태워서 랜덤 I/O할 row를 잘 걸러내서 랜덤 I/O의 횟수를 효과적으로 줄일 수 있다.

2. 인덱스 대상 컬럼 순서. 범위를 많이 줄일 수 있는게 앞에 오는게 좋다. 당연히 where절에 포함되는 컬럼 우선으로 넣는게 좋고 그중 name은 like % 조건이니 범위를 줄일 수 없는 컬럼이라 후순위로 했다. 그리고 나머지는 조건절에도 포함 안되고, 커버링 인덱스에서 그냥 랜덤 I/O 안하게 하려고 잡은 컬럼들이라 크게 의미를 두지 않았지만, 최대한 카디널리티가 높은거 먼저로 했다.

대망의 테스트

use index절을 이용해서 바꿔가며 테스트 했다.

최소한의 조건절 인덱스만 걸고 ICP(index condition pushdown) 사용

쿼리

select
    id, name, address, latitude, longitude, phone, thumbnail_image_url
from place use index(ix__place__for_search2)
where (latitude between 90 and 90)
and (longitude between 60 and 60)
and name like '%테스트1113%'
limit 500;

결과 속도

실행 계획(테이블로 보기 힘들어서 json으로 뽑았다.)

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "2936.34"
    },
    "table": {
      "table_name": "place",
      "access_type": "ref",
      "possible_keys": [
        "ix__place__for_search2"
      ],
      "key": "ix__place__for_search2",
      "used_key_parts": [
        "latitude",
        "longitude"
      ],
      "key_length": "18",
      "ref": [
        "const",
        "const"
      ],
      "rows_examined_per_scan": 2909,
      "rows_produced_per_join": 323,
      "filtered": "11.11",
      "index_condition": "((`chess`.`place`.`latitude` between 90 and 90) and (`chess`.`place`.`longitude` between 60 and 60) and (`chess`.`place`.`name` like '%테스트1113%'))",
      "cost_info": {
        "read_cost": "2645.45",
        "eval_cost": "32.32",
        "prefix_cost": "2936.35",
        "data_read_per_join": "1M"
      },
      "used_columns": [
        "id",
        "name",
        "address",
        "latitude",
        "longitude",
        "phone",
        "thumbnail_image_url"
      ]
    }
  }
}

성능도 만족할만큼 나왔고, 실행계획도 딱 생각한 대로다. 인덱스도 잘 타고 ICP도 효율적으로 잘 먹는다.

커버링 인덱스 사용

쿼리

select
    id, name, address, latitude, longitude, phone, thumbnail_image_url
from place use index(ix__place__for_search)
where (latitude between 90 and 90)
and (longitude between 60 and 60)
and name like '%테스트1113%'
limit 500;

결과 속도

실행 계획

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "2465.47"
    },
    "table": {
      "table_name": "place",
      "access_type": "ref",
      "possible_keys": [
        "ix__place__for_search"
      ],
      "key": "ix__place__for_search",
      "used_key_parts": [
        "latitude",
        "longitude"
      ],
      "key_length": "18",
      "ref": [
        "const",
        "const"
      ],
      "rows_examined_per_scan": 5688,
      "rows_produced_per_join": 631,
      "filtered": "11.11",
      "using_index": true,
      "cost_info": {
        "read_cost": "1896.67",
        "eval_cost": "63.19",
        "prefix_cost": "2465.47",
        "data_read_per_join": "2M"
      },
      "used_columns": [
        "id",
        "name",
        "address",
        "latitude",
        "longitude",
        "phone",
        "thumbnail_image_url"
      ],
      "attached_condition": "((`chess`.`place`.`latitude` between 90 and 90) and (`chess`.`place`.`longitude` between 60 and 60) and (`chess`.`place`.`name` like '%테스트1113%'))"
    }
  }
}

using_index가 true인걸 보니 커버링 인덱스가 잘 먹었다. 근데 속도는 사실 그냥 적당히 인덱스 잡은거랑 별 차이가 안난다.

어떤 차이가 있었을까?

일단 같은 쿼리를 날렸는데, 속도 차이가 별로 없었다. 그래서 두 방식의 원리를 좀 고민해보기로 했다.

일단 두 방식의 공통점은 랜덤 I/O를 줄이는걸 목표로 한다는 것이 있다.

그럼 차이점은? 커버링 인덱스 방식은 모든 컬럼을 인덱스에서 다 읽어서 랜덤 I/O를 발생시키지 않는 것이고, ICP를 활용하는 방식은 조건을 인덱스에서 먼저 검사해서 랜덤 I/O하는 row를 줄이는 것이다. 즉, 랜덤 I/O를 완전히 하지 않느냐 아니면 줄여서 하느냐로 차이를 둘 수 있어보인다.

검색 범위 늘려서 테스트

그럼 검색 결과가 많이 나오도록 검색 범위를 넓혀서 검색하여서 ICP를 활용하는 방식의 랜덤 I/O 갯수를 늘리면 차이가 벌어질까?

바로 실험해보기로 했다.

최소한의 조건절 인덱스만 걸고 ICP(index condition pushdown) 사용

쿼리

select
    id, name, address, latitude, longitude, phone, thumbnail_image_url
from place use index(ix__place__for_search2)
where (latitude between 90 and 92)
and (longitude between 60 and 62)
and name like '%테스트1%'
limit 500;

실행 속도

실행 계획

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "37033.34"
    },
    "table": {
      "table_name": "place",
      "access_type": "range",
      "possible_keys": [
        "ix__place__for_search2"
      ],
      "key": "ix__place__for_search2",
      "used_key_parts": [
        "latitude"
      ],
      "key_length": "18",
      "rows_examined_per_scan": 39792,
      "rows_produced_per_join": 491,
      "filtered": "1.23",
      "index_condition": "((`chess`.`place`.`latitude` between 90 and 92) and (`chess`.`place`.`longitude` between 60 and 62) and (`chess`.`place`.`name` like '%테스트1%'))",
      "using_MRR": true,
      "cost_info": {
        "read_cost": "36984.23",
        "eval_cost": "49.12",
        "prefix_cost": "37033.34",
        "data_read_per_join": "1M"
      },
      "used_columns": [
        "id",
        "name",
        "address",
        "latitude",
        "longitude",
        "phone",
        "thumbnail_image_url"
      ]
    }
  }
}

약간 느려지긴 했다.

커버링 인덱스 사용

쿼리

select
    id, name, address, latitude, longitude, phone, thumbnail_image_url
from place use index(ix__place__for_search)
where (latitude between 90 and 92)
and (longitude between 60 and 62)
and name like '%테스트1%'
limit 500;

결과 속도

실행 계획

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "21616.68"
    },
    "table": {
      "table_name": "place",
      "access_type": "range",
      "possible_keys": [
        "ix__place__for_search"
      ],
      "key": "ix__place__for_search",
      "used_key_parts": [
        "latitude"
      ],
      "key_length": "18",
      "rows_examined_per_scan": 40530,
      "rows_produced_per_join": 500,
      "filtered": "1.23",
      "using_index": true,
      "cost_info": {
        "read_cost": "21566.65",
        "eval_cost": "50.03",
        "prefix_cost": "21616.68",
        "data_read_per_join": "1M"
      },
      "used_columns": [
        "id",
        "name",
        "address",
        "latitude",
        "longitude",
        "phone",
        "thumbnail_image_url"
      ],
      "attached_condition": "((`chess`.`place`.`latitude` between 90 and 92) and (`chess`.`place`.`longitude` between 60 and 62) and (`chess`.`place`.`name` like '%테스트1%'))"
    }
  }
}

커버링 인덱스의 경우 검색 범위를 넓혀도 의미없는 차이만 존재했다.

고민과 선택

일단, 커버링 인덱스를 적용시키는 방안을 선택했다.

고민한 포인트는 다음과 같다.

1. 검색 범위나 결과 갯수에 따라 실행 시간이 들쭉날쭉하지 않는가?

해당 기능은 이 쿼리만 딱 나가는게 아니라 다른 부가적인 로직도 같이 존재한다. 이 쿼리가 크게 문제되지 않는다고 해서 기능이 통째로 빠른건 아니다. 글에 전부 담지는 않았지만, ICP만 믿는 방식은 쿼리 파라미터에 따라 100ms대에서 400ms대까지 실행시간이 들쭉날쭉한 반면, 커버링 인덱스는 계속 크게 유의미한 차이를 두지 않았다. 이유는 예상한 랜덤 I/O 갯수의 이유가 맞는 것 같다. 범위가 좁아서 결과 갯수가 적을 경우 빠르고 범위가 넓고 갯수가 많을 경우 느려진다.

2. 쓰기는 얼마나 발생할까?

해당 테이블에 쓰기가 얼마나 발생하느냐를 고민했다. 당연하지만, 인덱스에 걸린 컬럼이 많으면 많을 수록 해당 테이블에 대한 쓰기 성능은 떨어진다. 그리고 커버링 인덱스 방식은 꽤 많은 컬럼을 인덱스에 포함시켜야 한다. 만약 이 테이블에 쓰기를 하는 주체가 유저여서 쓰기속도도 중요하게 생각해야 한다면 쓰기에 대한 기능도 같이 테스트해서 선택을 했을 것이다. 하지만, 이 테이블에 대한 쓰기 기능은 어드민에서 굉장히 낮은 빈도로만 실행된다. 거의 읽기에 치중된 테이블이고 읽기 성능을 위해 쓰기 성능을 조금 포기해도 된다 싶었다.

3. 결과 row는 몇개 정도를 기대할까?

일단 비즈니스적으로 limit 500을 걸어 관리하고 있으니 ICP 방식을 썼을 때 최대 500번의 랜덤 I/O가 발생한다고 보면 된다. 500보다 적은 결과를 리턴할 경우도 있겠지만, max치로 잡고 생각하는게 마음 편할 것 같았고 그 경우에 커버링 인덱스가 확실히 조금 더 빠른 성능을 보여줬다.

느낀점

사실 두 방식은 크게 성능차이가 나지 않긴 했다. 만약 쓰기 성능도 포함해서 고려해야 한다면, 충분히 ICP를 활용하는 방식도 채택할만한 속도라고 생각한다. 다만, 위에 기술했듯 쓰기는 어차피 어드민에서 관리자가 하는 거다 보니 해당 기능에선 배제하고 생각했다.

추석을 맞아 오랜만에 포스팅을 하게 되었는데, 재미있는 주제를 잡고 한 것 같아서 기분이 좋다. 😁

업무에 치일 때는 쳐내도 쳐내도 일이 들어오고... 진짜 물리적으로 시간이 모자라다 보니 개발은 원래 이렇게 힘든거구나... 싶어서 개발자가 안맞나? 싶었었는데, 연휴에 느긋하게 공부하고 성능개선하고 이러니 다시 여전히 개발이 재밌다는 생각이 든다. 그냥 체력적으로 힘들어서 그랬었나보다. 🤪

끝

'DB' 카테고리의 다른 글

Real MySQL 5장 트랜잭션과 잠금 정리 (0)	2023.05.07
Real MySQL 4장 아키텍처 정리 (0)	2023.05.07
Serializable로 동시성을 잡으려 하면 데드락을 만납니다 (0)	2022.12.05
DB의 락 기능을 이용해서 동시성 이슈 해결하기 (feat. 우테코 체스미션) (0)	2022.05.24

현재글커버링 인덱스와 ICP(index condition pushdown)에 대한 고민

개발하며 느낀 것들을 나만의 언어로 기록하는 곳 https://github.com/yxxnghwan

Redis, 우아한테크코스, 인가, 캐시, Spring, web, 로깅, MySQL, Kotlin, Java, db, refresh token, JPA, 동시성, 쿠버네티스, 멀티모듈, 욕망의 인프라, Effective Java, 인증, 트랜잭션,

Today :
Yesterday :

알렉산더 코딩 정복기