SQL/MySQL

[MySQL튜닝]인덱스를 만들어 보자

스윗보스 2022. 10. 20. 14:39

 

테스트를 위한 DB 구성은 아래 글을 참고해 구성할 수 있습니다.

 

RDBMS의 SQL 성능을 좌우하는 요소 세 가지를 뽑아보자면 아래와 같습니다.

  • SQL문 자체
  • 인덱스(INDEX)
  • 통계 정보

 

이번 글에서는 세 가지 요소 중에 인덱스에 대해 살짝 맛을 보도록 하겠습니다. 인덱스의 개념에 대해서는 아마도 다음 글에 설명이 될거 같습니다.

 

1. INDEX를 만들어보자.

인덱스는 RDBMS에서 데이터를 빠르게 찾을 수 있도록 해주는 객체입니다. 보통은 테이블별로 인덱스를 여러 개 만들 수 있으며, 데이터를 접근하는 주요 패턴을 분석해 인덱스를 설계합니다.

데이터를 접근하는 주요 패턴을 분석한다는 것을 "아주 간단히" 이야기 하자면, SQL의 WHERE 절이나, 조인절에서 사용된 조건 컬럼을 분석하는 것입니다. ("아주 간단히"를 강조한 이유는, 실전에서 인덱스 설계는 그렇게 간단한 문제가 아니기 때문입니다.)

 

아래와 같은 SQL이 있다고 해보죠.

SELECT  COUNT(*) ORD_CNT
FROM    MYTUNDB.T_ORD_BIG T1
WHERE   T1.ORD_DT >= STR_TO_DATE('20170201','%Y%m%d')
AND     T1.ORD_DT < STR_TO_DATE('20170202','%Y%m%d');

위 SQL을 EXPLAIN ANALYZE를 통해 실제 실행계획을 추출해보면 아래와 같습니다. 아래 실행계획에서 가장 윗 줄에 actual time이 해당 SQL의 총 실행시간입니다. 535.476 ~ 535.478 밀리세컨드(0.53초)가 걸렸습니다.

-> Aggregate: count(0)  (actual time=535.476..535.478 rows=1 loops=1)
     -> Filter: ((mytundb.t1.ORD_DT >= <cache>(str_to_date('20170201','%Y%m%d'))) and (mytundb.t1.ORD_DT < <cache>(str_to_date('20170202','%Y%m%d'))))  (cost=31559.80 rows=33455) (actual time=535.463..535.463 rows=0 loops=1)
         -> Table scan on T1  (cost=31559.80 rows=301158) (actual time=1.767..418.311 rows=304700 loops=1)

 

 

위 SQL에서 WHERE절에 사용된 조건은 T_ORD_BIG의  ORD_DT 컬럼입니다. 그러므로 T_ORD_BIG에 ORD_DT에 인덱스를 만들어 성능 개선을 고려할 수 있습니다. 실제로 인덱스를 만들어보고 성능이 개선되는지 확인해봅니다.

아래 구문을 통해 T_ORD_BIG_X1이라는 인덱스를 생성합니다. T_ORD_BIG_X1은 MYTUNDB.T_ORD_BIG 테이블의 ORD_DT로 구성된 인덱스입니다.

CREATE INDEX T_ORD_BIG_X1 ON MYTUNDB.T_ORD_BIG(ORD_DT);

 

인덱스를 생성했다면, 다시 위의 SELECT SQL을 EXPLAIN ANALYZE로 실행해봅니다. 아래와 같은 실행계획이 나오는 것을 알 수 있습니다. 전체 실행 시간인, 가장 위의 actual time이 0.045 밀리세컨드(0.000045초) 밖에 안되는 것을 알 수 있습니다.

-> Aggregate: count(0)  (actual time=0.045..0.046 rows=1 loops=1)
     -> Filter: ((mytundb.t1.ORD_DT >= <cache>(str_to_date('20170201','%Y%m%d'))) and (mytundb.t1.ORD_DT < <cache>(str_to_date('20170202','%Y%m%d'))))  (cost=1.21 rows=1) (actual time=0.041..0.041 rows=0 loops=1)
         -> Index range scan on T1 using T_ORD_BIG_X1  (cost=1.21 rows=1) (actual time=0.039..0.039 rows=0 loops=1)

 

이처럼, WHERE 절의 조건에 인덱스를 잡는 것만으로, 아래와 같이 성능 개선이 되었습니다.

  • 개선전후 시간 : 0.53초 -> 0.000045초

 

지금 살펴본 내용으로 아래 정도의 개념을 알 수 있습니다. 

  • 인덱스를 만들면 조회 속도가 빨라진다.
  • WHERE 절의 조건 컬럼에 인덱스를 고려한다.

 

하지만, 이 개념과 지식만으로 인덱스를 만들어대기 시작하면 매우 위험해집니다. (시스템이 오히려 문제가 생길 수 있습니다.) 실전에서 튜닝을 하고, 인덱스 설계를 위해서는 더 많은 경험과 공부가 필요합니다.

 

오늘은 여기까지입니다. 감사합니다.