데이터 마이그레이션을 위한 Update 쿼리 성능 개선

실행 시간이 1시간 이상 소요되는 악성 SQL...

오늘은 실무 초기에 SQL 을 작성하면서 실수했었던 부분에 대해 다루고자 한다. 🐥 실무에서 데이터 마이그레이션을 위해 Update 쿼리를 작성해본 경험이 있었는데, 테스트 환경에서 실행해본 결과 실행 시간이 무려 1시간을 훌쩍 넘긴 일이 있었다...👀 뭔가 잘못됐음을 직감하고 일단 SQL 실행계획부터 확인해보기로 했다.

 

SQL 의 형태는 대략 아래와 같은 구조였다. (참고로, 이름은 임의로 명명했다.)

 

UPDATE a_table a
  JOIN b_table b ON CAST(a.index_id AS CHAR) = CAST(b.id AS CHAR)
  JOIN c_table c ds ON b.c_id = c.id
  SET a.text = ...
  WHERE a.`type_id` = 3;

 

 

치명적인 테이블 풀 스캔..

해당 Update 쿼리는 3개의 테이블이 join 되게 되는데, 업데이트의 대상이 되는 테이블(a 테이블)의 데이터 개수는 80만개에 달했고, 이 테이블과 직접 join 되는 테이블(b 테이블)의 데이터 크기 역시 20만개에 달했다. 이때, 실행계획을 보니 이 두 테이블이 조인될 때 a 테이블의 경우 인덱스를 타지 않고 테이블 풀 스캔이 발생했고, (type = ALL) b 테이블의 경우 인덱스 풀 스캔이 발생했다. (type = index)

 

하지만, 이 두 테이블이 조인될 때 사용되는 칼럼은 모두 인덱스로 설정되있는 칼럼이었다. 일단, a 테이블의 경우 index_id 칼럼은 세컨더리 인덱스(b 테이블의 외래키는 아님)로 지정되있는 칼럼이었으며, b 테이블의 id 칼럼의 경우 프라이머리 키(클러스터드 인덱스)로 지정된 칼럼이었다. 충분히 이러한 인덱스를 활용하여 조인 시 성능 최적화를 기대할 수 있을 것 같은데, 왜 인덱스를 제대로 활용하지 못했을까? (이미 저 SQL 만 보고도 눈치챈 사람들이 많이 있으리라 생각되지만...🤣)

 

 

인덱스를 제대로 활용할 수 없었던 이유

해당 SQL 에서 a 테이블과 b 테이블 조인 시 인덱스를 제대로 활용하지 못했었던 이유는 조인 조건 상 명시적으로 형변환 처리를 해주었기 때문이다. 여기서 명시적으로 형변환 처리를 해주었던 이유는 a 테이블의 index_id 칼럼 문자열 타입이었고, b 테이블의 id 칼럼은 정수 타입이기 때문이었다.

 

참고로, MySQL 공식 문서를 보면 CAST 연산으로 인해 MySQL 은 해당 인덱스를 효율적으로 사용하지 못한다는 것을 확인할 수 있다.

 

https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html

 

이때, 인덱스를 제대로 활용할 수 없다면 아울러, MySQL 버전이 8.0.20 미만이라면 블록 네스티드 루프 조인을 쓸 수도 있지 않았을까 생각할 수도 있겠다. 다만, 실행계획 상 블록 네스티드 루프 조인을 사용하는 것을 볼수는 없었는데, 이는 옵티마이저의 최소 비용을 추구하는 알고리즘과 연관이 있는 것이기 때문에 왜 블록 네스티드 루프 조인이 사용되지는 정확하게는 알 수가 없었다.

 

 

어떻게 튜닝할 수 있을까?

이제 1시간 이상 소요되는 이 SQL 을 어떻게 최적화할 수 있을까? 일단 조인되는 두 칼럼의 데이터 분포도를 살펴보기로 했다. 일단, a 테이블의 index_id 칼럼 데이터들은 문자열로 지정되긴 했지만 type_id 가 3인 경우(해당 SQL 문의 WHERE 절 참고)에는 모두 정수형 값을 지니고 있음을 확인할 수 있었다. 즉, 기존 SQL 에서는 조인되는 두 개의 칼럼을 모두 문자열로 형변환 처리해주었지만 사실 그럴 필요 없이 하나의 칼럼만 형변환 해주어도 되는 부분이었다. 아래와 같이 SQL 문을 변경해보았다.

 

UPDATE a_table a
  JOIN b_table b ON a.index_id = b.id
  JOIN c_table c ds ON b.c_id = c.id
  SET a.text = ...
  WHERE a.`type_id` = 3;

 

이전과 달라진 점은 a 테이블과 b 테이블간 조인 시 단순히 명시적으로 형변환하기 위해 설정해주었던 CAST 연산자가 빠졌을 뿐이다. 참고로, 명시적으로 형변환해주지 않아도 MySQL 은 동등 연산(=) 비교 시 묵시적으로 형변환 처리를 해주는데, 경우에 따라 문자열을 숫자로 바꾸기도 하고 숫자를 문자열로 바꾸기도 한다.

 

https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html

 

 

놀라울 정도로 빨라진 SQL...

변경된 SQL 에 대해 다시 한 번 실행계획을 살펴봤다.

 

개선 전에는 a 테이블과 b 테이블을 조인할 경우 a 테이블의 세컨더리 인덱스가 적용된 칼럼과 b 테이블의 프라이머리 키가 적용된 칼럼이 제대로 활용되지 못하고 있었다. a 테이블 검색 시 테이블 풀 스캔이 발생했고,  (type = ALL) b 테이블 검색 시에는 인덱스 풀 스캔이 발생했었다. (type = index)

 

하지만, 위와 같이 불필요한 형변환 연산만을 제거해주었을 뿐인데, a 테이블 검색 시 인덱스를 활용할 수 있었고 (type = ref) 아울러, b 테이블의 경우 프라이머리 키를 활용할 수 있게 되었다. (type = eq_ref)

 

다시 한 번 테스트 환경에서 실행해보니 5초만에 실행이 완료되는 것을 확인할 수 있었다. 🚀 80만개 데이터와 20만 개 데이터를 조인함에 있어 인덱스를 제대로 활용하냐 못하냐의 차이는 어마어마한 시간 차이가 발생하는 것을 확인할 수 있었다. 📚

 

(서비스 운영 시 실시간으로 호출되는 API 에서 쿼리 실행 시간이 5초라는 것은 거의 말도 안되는 일이지만, 해당 쿼리는 데이터 마이그레이션을 위해 일회성으로 수행되는 쿼리이다. ☕)