비전공자가 알아두면 좋은 SQL 인덱스 성능 팁 2가지

요즘 많은 분들이 SQL을 통해서 데이터를 조회하고 추출합니다.  예전에는 DBA 분들이 주로 SQL을 통해서 데이터를 조회하고 추출하였습니다. 하지만 이제는 데이터 분석가, 사업, 기획, 개발 많은 분야에서 다양한 분들이 SQL을 통해서 데이터를 조회 및 추출을 합니다.

 

이렇게 많은 분들이 사용하다 보니 비전문가가 많아서 성능을 고려하지 못하고 SQL을 쓰는 사례가 많아졌습니다. 이는 자칫 실서버 또는 분석용 서버에 무리를 주는 경우가 있어서 이러한 부분을 조금이라도 줄이기 위해서 글을 작성합니다.

 

이 글의 경우 mysql , google big query에서 테스트하였습니다. (2020 기준으로 회사에서 2개를 사용)

 

---

첫 번째, WHERE절의 좌변을 가공하지 마세요.

이게 무슨 말일까요? 

SQL의 기본적인 형태는 다음과 같습니다.

SELECT *
FROM TABLE
WHERE A = 1

 

여기에서 SELECT , FROM 절을 제외하고 WHERE 절은 조건절이라고 합니다. 이는 조건을 넣어서 데이터의 검색 범위를 줄입니다. 쉽게 설명하면 내가 100개의 방을 보고 찾아야 하는데, 호수를 알고 있기 때문에 그 방만 제외하면 된다는 뜻입니다.

 

여기서 좌변을 WHERE A = 1에서 A 부분을 뜻 합니다.

A는 내가 찾고자 하는 테이블에 속한 컬럼을 뜻하며, 이를 가공할 경우 인덱스를 이용하여 빠른 검색이 불가능합니다. 

 

위의 예시를 토대로 쓰자면 내가 100개의 방에서 1개의 호수를 보고 찾을 수 있는데, 누군가 그 호수의 내용이 있는 쪽지를 상자에 담아서 볼 수가 없게 되었습니다. 이 경우 당연히 100개의 방을 전부 봐야 합니다.

(물론 상자를 열면 되겠으나...)

 

이게 DB의 깊은 부분까지 전부 나열하면 복잡하지만 우리는 여기까지만 알면 됩니다. 

그럼 어떤 사람이 이렇게 생각할 수 있습니다. 

그렇다면 WHERE 1 = A 이렇게 하고 좌변을 바꾸면요? 이것은 괜찮습니다.

하지만... 약간의 약속(?) 같이 SQL을 만들 때 컬럼을 좌측에 씁니다.

여기서 핵심은 위치가 아니고 컬럼을 가공하지 않아야 한다는 것입니다.

 

실제로 데이터로 테스트해보겠습니다. 성능 테스트는 explain을 사용하였습니다. ( explain )

약 1억 건의 데이터가 있는 테이블에서 가장 많이들 사용하는 날짜를 사용해서 조회를 해보겠습니다.

 

• 좌변을 가공하지 않았을 때

좌변을 가공하지 않은 조회

 

위의 Query를 보시면 rows가 59,522건 검색되었습니다. "possible_keys" 컬럼을 보시면 IX_log_data_1 인덱스를 잘 활용하였죠. 위의 내용을 자세히 보지는 않으셔도 됩니다. 수치만 보시고 판단하시면 됩니다.

 

• 좌변을 가공하였을 때

그럼 이제 가공해서 조회해 보겠습니다. 많은 분들이 이렇게 활용하실 겁니다. UTC 타임존으로 되어 있는 날짜 조건을 KST로 변경하여 조회하려고 이렇게 흔히들 사용합니다.

좌변을 가공 하였을 때

 

위의 Query를 보시면 rows가 10,231,319건 검색되었습니다. 이 테이블의 건수가 11,111,989건인데, 거의 모두 검색하였네요. "possible_keys"가 null입니다. 테이블을 모두 읽어서 가져왔다는 뜻이죠. 

 

체감이 잘 안되시죠? 그럼 실제로 실행해서 소요시간을 볼까요?

아래에서 위와 아래의 차이를 보시면 놀라실 겁니다. 

위 가공 X / 아래 가공 O

 

위의 부분은 1초도 안 걸립니다. 312ms입니다. 그런데 아래의 내용은 35초가 걸렸습니다.  쿼리의 내용은 log_data_kst를 kst로 조회 log_data_utc를 kst로 가공하여 조회이므로, 결과를 똑같습니다. (스크린숏은 order 안되어 있음)

 

체감부터가 엄청나죠. 기간이 하루인데, 이렇게 차이가 심한데 한 달이면? 아주 길겠네요.

 

그런데 이게 big query에서도 똑같이 작용합니다. 

big query에서 성능 차이

 

이 작은 부분이 아주 큰 차이를 만듭니다.

앞으로 이 부분만 아셔도 큰 도움이 될 겁니다.

 

---

두 번째,인덱스의 순서를 지키세요.

이게 무슨 말이야? 생각할 수 있습니다. 우선 이 부분은 mysql 만 해당합니다. big query의 경우 파티션(인덱스)이 한 개 밖에 생성이 안되므로 애초에 복합 인덱스가 안됩니다. 그래서 mysql 쪽에서 사용 가능합니다.

 

우선 이 내용을 보려면 인덱스의 구조를 조금 아시면 좋습니다. 

다음의 글을 보고 이 글을 보시면 도움이 됩니다. ( 인덱스 순서가 성능에 미치는 영향 )

 

인덱스가 1개  1 컬럼이면 별로 상관이 없는데 1개의 인덱스에 2개 이상의 컬럼이 있는 복합 인덱스의 경우가 있습니다.

 

예시를 보면서 확인하면 쉽게 이해가 됩니다. 제가 생성한 log_data_test 테이블에는 log_type, log_date_KST으로 복합 인덱스가 걸려 있습니다. log_type가 먼저 앞에 있습니다. 이럴 경우 위와 같이 가공하지 않고 log_date_KST를 이용해서 데이터를 조회해보겠습니다.

인덱스 검색

이상합니다. 분명히 좌변을 가공하지 않았는데 매우 성능이 좋지 못하고 인덱스도 활용하지 못하였습니다.

 

실제로 조회를 하였을 때도 시간이 오래 걸립니다.

실제 조회시 소요시간

 

36초가 걸렸습니다. 아까 좌변을 가공했을 때와 거의 비슷합니다.

이는 인덱스를 전혀 활용하지 못한다는 뜻입니다. 

 

이 경우 해결 방법이 2가지가 있습니다. 

• 첫 번째 log_date_KST로 인덱스를 새롭게 생성하는 것입니다.
• 두 번째 강제로 log_type 컬럼을 WHERE 조건에 넣습니다.

첫 번째 방법의 경우 인덱스 생성을 DB 담당자에게 부탁하면 되겠지만 인덱스를 실시간으로 생성할 경우 많은 이슈가 있습니다. 그래서 보통은 그렇게 하지 않고 두 번째 방법을 사용합니다.

 

두 번째 방법을 사용하려면 log_type이 무엇이 있는지 봐야 합니다.

group by 를 통해서 log_type 확인

 

보시면 2가지 타입밖에 없습니다. 

이를 어떻게 활용하면 될까요? 

강제로 인덱스 사용하게 하기

 

인덱스를 강제로 태우기 위해서 WHERE 조건에 현재 있는 타입을 모두 넣고 그다음에 log_date_KST 조건을 넣습니다.

이렇게 하면 결과는 똑같습니다.

 

그렇다면 소요시간은 얼마나 될까요?

강제로 인덱스 활용

보시면 1.6초입니다. 물론 가장 좋은 방법은 log_date_KST로 인덱스를 만드는 것이지만, 그 방법이 안될 경우 위와 같이 하면 쉽게 해결 가능합니다. 

 

---

마치며...

 

위의 2가지만 알아도 DB 담당자에게 많이 사랑받을 것이고, (제가 실제로 사랑했습니다.) 많은 성능 향상 , 속도 향상 , 비용 감소(빅쿼리)를 체감할 수 있습니다. 아주 간단하지만 아주 막강한 내용이므로 꼭 알고 계시면 큰 도움이 될 것이라고 생각됩니다.

 

실제 사용하는 데이터베이스에서는 이러한 부분이 매우 중요하게 작용하며 아마도 개발 하실 때도 이를 활용하면 빠르게 원하는 결과를 확인 할 수 있을 것 같습니다.

 

감사합니다.