[SQL] 인덱스를 안탔다??! 암시적 형변환, 풀테이블 스캔 문제

안녕하세요! 😄 오늘은 실제 실무에서 발생했던 슬로우쿼리 로그를 보면서 (다르게 재구성한 시나리오 입니다.)
함께 문제를 풀어나가는 시간을 갖도록 해보겠습니다. 바로 시작할게용~

 

암시적 형변환 때문에 발생한 풀테이블 스캔 문제

# Time: 240731 11:00:00
# Query_time: 1310.440900  Lock_time: 0.000067  Rows_sent: 0  Rows_examined: 351718

UPDATE history_table SET STATUS = 7
WHERE INSERT_DATE < DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 12 HOUR), '%Y%m%d%H%i%s') 
AND STATUS = 1;


# Time: 2400801 10:00:00
# Query_time: 1490.498300  Lock_time: 0.000097  Rows_sent: 0  Rows_examined: 371718

UPDATE history_table SET STATUS = 7
WHERE INSERT_DATE < DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 12 HOUR), '%Y%m%d%H%i%s') 
AND STATUS = 1;

 

💡 해당 쿼리를 천천히 읽어보면, 이 쿼리는 특정 기록 테이블에서 INSERT_DATE(해당 레코드가 히스토리에 남은 시간)가 현재 시간으로부터 12시간 이상 지난 레코드 중 STATUS가 1인 레코드를 찾아 그 상태를 7로 업데이트하는 것입니다.

 

배경

• 현재 조건절에 해당하는 STATUS는 인덱스로 선언되어 있는 상태입니다.
• 여기서 STATUS 1은 미완료 상태를 의미하며, STATUS 7은 실패 상태를 뜻합니다.
• 즉, 해당 쿼리는 12시간이 지나도 완료되지 않은 요청의 기록을 실패로 처리하겠다는 의미입니다.

 

눈에 보이는 문제점

• INSERT_DATE는 테이블에 행이 들어간 시간을 나타내므로, 이 UPDATE 문은 과거부터 12시간이 지난 레코드들 즉,거의 모든 행을 바라보게 됩니다.
• 이 업데이트 문이 이전에 실행되었는데도 Rows_examined(탐색하는 행의 갯수)가 35만건 -> 37만건으로 계속 증가하고, 쿼리 실행 시간도 늘어나고 있습니다.
• 이는 해당 쿼리가 업데이트되지 않았거나 인덱싱이 이루어지지 않아 풀 테이블 스캔을 수행하고 있음을 나타냅니다.

✅ 눈에 보이는 문제로는 바로 해결하기 어려웠습니다. 하지만 CREATE TABLE 문을 살펴보고 해당 컬럼의 데이터 타입을 확인한 후 문제를 해결할 수 있었습니다😅.

 

해답

• CREATE TABLE에는 STAUTS의 데이터 타입이 VARCHAR로 선언되어있었습니다.
• 하지만 UPDATE 문에는 SET STATUS = 7 , "정수형(INT)"으로 쿼리가 동작하고 있었습니다.
• SET STATUS = '7' 로 변경후에 INDEX는 정상 동작하였고 해소되었습니다.

 

데이터 타입이 다른게 무슨 문제냐?

💡 SQL 테이블에서 데이터의 타입가 다르면 암시적인 형변환이 일어나게 됩니다.

 

✅ DBMS는 VARCHAR로 선언된 STATUS와 SET 동작을 수행하게될 1 이라는 값을 '1'로 변환한 후에 AND연산을 마무리 지을 수 있습니다. 그렇기에 설령 이전 UPDATE문에서 STATUS == 1이 모두 7로 SET되었다 하더라도, 모든 행에 대하여 검사를 할 수 밖에 없는 상태가 됩니다.

 

그렇기 때문에 UPDATE문이 이전에 실행되었어도 암시적 형변환으로 인한 강제적 연산과정 때문에 Rows_examined(탐색하는 행의 갯수)가 계속 늘어났던 것이지요..😋

 

결론

문제를 해결하고 나니 매우 간단해 보이지만, 쿼리와 DB LOG에서 주어진 힌트를 종합해서 생각의 틀을 넓힐 수 있는 예제였다고 생각합니다. 사실 뭘 배워도 새롭긴 하지만 ㅎㅎㅋㅋㅋ 그래서 결론은~!

 

 먼저 발생빈도, 서칭하는 행 갯수, 지연된 쿼리 시간을 통해 슬로우 쿼리를 파악하고 쿼리가 발생한 로그를 보고 추려낸 힌트를 종합하여 해결책을 생각해본다! 그리고 "해당 쿼리의 인덱스가 타당한지 생각하는 습관을 들인다" 정도가 결론이겠습니다!

 

슬로우 쿼리 이슈는 뭔가 탐정놀이 하는것 같이 재밌네요 ㅎㅎ😄

오늘도 읽어주셔서 감사합니다.