언제까지 인덱스를 탈 것인가?

 

 

 

오늘은 StartUP Tuning 강의 중 일부인 "언제까지 인덱스를 탈 것만 같아?" 부분을 소개해봅니다.

MySQL로 데이터를 조회할 때 언제까지 인덱스를 사용하는지, 언제나 인덱스가 좋은 것인지에 대한 내용입니다.

---

SQL의 성능을 위해서는 기본적으로 세 개의 요소가 필요합니다. StartUP Tuning에서는 아래와 같이 세 개의 요소를 정의합니다.

• INDEX
• 비효율 SQL 변경(제거)
• 힌트 사용

이 외에도 SQL 성능을 위해 다양한 요소가 필요하겠지만 기본적으로 가장 신경써야 할 부분은 위의 세 가지입니다.

 

SQL 성능을 위해 인덱스를 잘 구성해야 합니다. 기본적인 인덱스 구성 없이 SQL 성능을 끌어 올리는 것은 불가능합니다.

그러나 인덱스가 잘 구성되어 있어도 SQL을 잘 작성하지 않는다면 아무 필요가 없습니다.

(StartUP Tuning 강의에서는 인덱스를 하루에 걸쳐 설명하지만, 항상 SQL의 중요성을 강조합니다.)

SQL이 실행되면 MySQL의 옵티마이져는 상황에 따라서 인덱스로 처리할지 테이블 FULL SCAN으로 처리할지 선택하게 됩니다. 이는 인덱스를 사용하는 것이 항상 성능에 유리하지는 않다는 뜻이기도 합니다.

 

아래와 같은 SQL의 조건을 변경해가면서 실행하면, 어느 지점부터 인덱스가 아닌 FULL SCAN을 선택하는지 간단히 살펴볼 수 있습니다. T_ORD_BIG에는 약 6백만건의 데이터가 존재하며 ORD_YMD 컬럼에 싱글 컬럼 인덱스가 존재합니다. SELECT 절에 일부러 인덱스에 없는 PAY_DT 컬럼을 사용해 인덱스를 사용한 후 테이블 접근이 발생하도록 유도했습니다. 

SELECT  MAX(T1.PAY_DT) MAX_PAY_DT, COUNT(*) CNT
FROM    T_ORD_BIG T1 
WHERE   T1.ORD_YMD >= '20170104'
AND     T1.ORD_YMD <  '20170110';

 

조회 조건을 변경해가면서 테스트를 해보면 아래와 같은 결과를 얻을 수 있습니다. 조회 건수가 450,000 건 정도가 되자 인덱스가 아닌 Table Scan(TABLE FULL SCAN)을 선택했습니다. (이는 인덱스로 데이터를 탐색 후 테이블 접근이 발생하는 것이 전제된 경우입니다. 만약에 테이블 접근 없이 인덱스만 사용한다면 다른 결과가 나올 것입니다.) 실행시간 역시 342,000건 까지는 1.75초 정도였으나 450,000건인 Table Scan부터는 7.05 초로 매우 느려진 것을 알 수 있습니다.

 

MySQL의 옵티마이져는 통계를 참고해 450,000건 정도부터는 인덱스보다는 Table Scan이 더 빠르다 판단한 것입니다. 

항상 옵티마이져가 최선의 선택을 하는 것은 아닙니다. 최선의 선택을 위해 주어진 환경에서 최대한 노력하지만, 최선의 선택이 아닐 수도 있습니다. 실제로 현재 환경에서 450,000건 정도는 Table Scan보다 인덱스를 활용한 처리가 더 빠릅니다. 위 결과를 시각화 해보면 다음과 같습니다. Table Scan(FULL SCAN)에서 Buffers(IO) 수치는 꺽이지만 실행 시간은 오히려 7초대까지 올라간 것을 알 수 있습니다.

 

여기서 주의할 점이 있습니다. 450,000이 Table Scan을 선택하는 일반화된 숫자가 절대 아니라는 점입니다. 이러한 숫자는 서버와 디스크 성능, 서버 설정, 테이블의 크기(로우x컬럼), 인덱스 구성, 통계, SQL문 등에 의해 다른 결과가 나올 수 있습니다. 제 개인 PC에 설치된 MySQL에서 이와 같은 수치가 나온 것이므로 각자 환경에서 큰 테이블을 기준으로 테스트 해보시기 바랍니다.

 

추가로 하나의 테스트를 더 해 봅니다. 조회 건수를 변경해가면서 INDEX 또는 NO_INDEX 힌트를 적용해 성능 비교를 하는 것입니다. 아래와 같은 결과를 얻을 수 있습니다.

 

450,000건을 처리할 때 인덱스를 사용한 경우에는 3.28초 정도의 시간이 걸린 것을 알 수 있습니다. 해당 건수의 FULL SCAN 시간은 6.64초 정도가 나왔습니다. (이전 테스트에서 7.05초 정도였습니다. 약간 시간이 줄어들었으나 크게 의미는 없습니다.)

위 내용을 시각화 해보면 다음과 같습니다.

 

Table Scan(FULL SCAN)은 몇 건의 데이터를 조회하든 거의 일정한 시간과 거의 일정한 IO를 유지합니다. 반면에 인덱스를 이용한 데이터 접근은 어느 순간(450,000 건을 넘어서는 순간)부터 IO와 실행시간이 급등하기 시작합니다.

이처럼 조회할 데이터가 많아질 수록 인덱스보다 Table Scan(FULL SCAN)이 SQL 성능을 위해 더 좋은 선택일 수 있습니다. (한번 더 강조하면, 이와 같은 수치는 테스트 환경에 따라 상이합니다.)

 

조회할 데이터가 많으면 Table Scan이 더 좋다! 그러나, SQL 튜닝에 많은 도움을 주시는 변동구 이사님께서 이런 말씀을 하셨습니다. "지금도 빠르고 앞으로도 빠를 것인가?"

현재 테스트한 T_ORD_BIG에는 6백만건 정도의 데이터가 있습니다.

데이터가 지금의 열 배가 된다면, 여전히 Table Scan(FULL SCAN)이 인덱스를 활용한 검색보다 빠를 수 있을지는 생각을 해볼 필요가 있습니다.

 

StartUP Tuning(MySQL을 활용한 SQL 튜닝 입문 교육)에서는 지금까지의 내용을 다음과 같은 슬라이드로 소개합니다.

 

 

 

오늘 준비한 내용은 여기까지입니다. 정리해보면 다음과 같습니다.

• 옵티마이져는 데이터 처리량에 따라 인데스와 Table Scan 중에 선택을 한다.
• 옵티마이져의 선택이 완벽하지 않을 수도 있다.
• 실제로 처리할 건수가 많아질 수록 인덱스보다는 Table Scan이 더 효과적이다.
• 그러나 "지금도 빠르고 앞으로도 빠를것인가?" 에 대해 고민해보자.

감사합니다.

---

[StartUP Tuning]

  - SQL 입문을 넘어선 MySQL을 활용한 SQL 튜닝 입문 교육
  - 2024년 5월 오프라인 교육 과정을 모집중입니다.

  - https://cafe.naver.com/dbian/7717

StartUP Tuning - MySQL/MariaDB를 활용한 SQL 튜닝 입문

 

[StartUP SQL]

  - 데이터 분석가, 개발자, 기획자를 위한 SQL 입문 교육

  - 교육용 슬라이드 전체 무료 오픈: https://sweetquant.tistory.com/497

StartUP SQL 소개 및 목차