Query 튜닝

쿼리 튜닝의 경우 여러가지 경우의 수를 따져야한다.

아무리 조건을 잘짠다고 해도 데이터가 많을 경우 속도는 현저히 느려진다. 반대로 데이터가 많이 없다고 가정을 해도 조건을 이상하게 짠다면 테이블 전체를 불러와 쓸모없이 데이터를 Scan 하게 된다.

이러한 경우를 실행계획이라 하며, 데이터가 많든 적든 필요한 데이터를 최적의 실행계획으로 가져와야한다.

실행 계획을 알아보기에 앞서 간단하게 인덱스를 설명해보려 한다.

Index란?

위 사진을 보면 쿼리가 실행되면 우선적으로 옵티마이저에서 INDEX를 스캔하게 된다. INDEX를 스캔할때 있을 경우 TABLE에서 해당 인덱스를 찾아 해당 데이터를 가져온다.

실행계획?

• DBMS가 쿼리를 어떻게 실행할지에 대한 구체적인 절차와 전략을 보여주는 설계도이다.

실행 계획에서 해석할때 자주 사용하는 단어

알다시피 그림 상 데이터와 인덱스가 비슷해보이지만 웬만하면 Table 데이터가 매우매우 많다.

• Table Scan(Full Scan)
  • 테이블 전체를 다 읽는다
  • 데이터가 많으면 느림

    

• Index Seek
  • 인덱스를 타고 원하는 데이터만 원하는 데이터만 딱 가져온다.

    

• Index Scan
  • 인덱스 모든 키 값을 처음부터 끝까지 스캔한다.

    

• Nested Loop Join
  • 한쪽 테이블을 돌면서 다른 테이블을 반복 조회한다.
  • 소량 데이터에 유리
• Hash Join
  • 해시 테이블을 만들어 Join한다
  • 대량 데이터에 유리
• Merge Join
  • 정렬된 두 집합을 병합
  • 정렬된 데이터일 때 효율적

튜닝

이러한 실행계획은 개발자가 직접적으로 관여를 할 필요가 없다. 왜냐하면 SQL 문을 실행할 때 옵티마이저가 가장 효율적이라고 판단한 실행 방법을 나타내기 때문이다. 하지만 이런 옵티마이저가 꼭 최적의 통계를 찾아 내는 것은 아니다, 통계가 부정확하거나 오래 되면 잘못된 실행 계획을 선택하기도 하기에 이러한 경우 개발자나 DBA가 옵티마이저의 판단을 보정해주는게 필요하다.

이제 튜닝에 대해서 얘기해보려 한다.

“옵티마이저가 다 해주니깐 상관 없는거 아니야”라고 얘기할 수 있다. 하지만 어디까지나 내가 짠 쿼리를 최적의 경로로 데이터를 추출해주는 도구에 불과하다. 실제로 쿼리를 짜는 것은 개발자/DBA 이다. 옵티마이저에게 인덱스를 탈 수 있도록 조건문을 짜야하며 하나의 조건을 위해 불필요한 Table Full Scan을 탈필요 없다.

인덱스 튜닝

Cost에 대해서 간단하게 알아보자면 디스크 블록 읽기 횟수로 많이 읽을 수록 당연히 많은 리소스 비용이 발생하게 된다. 이때 발생하는 비용을 cost라고 부른다. cost가 많다면? 평균적으로 느리다(실행 환경에 따라 차이 발생)

이제 본격적으로 실행계획을 통한 튜닝을 알아보자.

예를 들어 10만건의 데이터에서 1개의 데이터를 추출하려 하는데 위와 같이 index 설정이 안되어있어 Full Scan이 났다고 가정해보자. 그렇게 되면 1건을 위해 10만건의 데이터에서 찾게 될것이다. INNER JOIN일 경우는 그나마 양반이지만 LEFT일 경우는 O(N * M) 이 될 가능성이 있다.

하지만 쿼리 하나를 생성하고 DB에 INDEX를 생성해 INDEX 부자가 될 경우 유지보수가 매우 어려워 진다.

왜? 어려워질까? 우선 DB 테이블 하나에 10개 이상의 인덱스가 있다고 가정해보자. 일단 그렇게 될경우 내가 의도하는 인덱스를 태우고 싶으나 옆 동네 인덱스를 태울수도 있다. 이 부분에 대해 힌트를 사용 할 수 있지만 남발하게 된다면 인덱스가 변경되거나 혹은 변경 되어야할 때 운영 유지보수는 배로 들어갈 것이다.

최대한 설계를 통해 옵티마이저가 실행계획을 정확한 방향으로 가도록 설계하고 힌트의 경우 매우 급할 경우에만 사용해야 한다.

문법을 이용한 쿼리 튜닝

튜닝의 기본이라 생각하는 부분이다.(mssql 기준)

1. 조건문에 인덱스를 흐리는 경우
   1. 기준이 되는 조건 a에 b를 찾을것이다 하지만 여기서 기준이 되는 값을 형변환을 하는 사례의 경우
   2. 컬럼을 변환하거나 가공하는 함수나 연산을 사용할 경우!(+-*/,isnull)

-- 문제 코드
SELECT 
	*
FROM
	USER_INFO A 
WHERE 
	ISNULL(A.PHONE,0) = '2020'
	
-- 해결 코드
SELECT 
	*
FROM
	USER_INFO A
WHERE 
	A.PHONE = '2020'
OR A.PHONE IS NULL

1. 스칼라쿼리 혹은 함수를 두는 경우
   1. 스칼라를 쓰는 경우는 흔하지만 데이터가 많은 곳에서 사용하게 된다면 n * 2가 될 가능성이 있다.

-- 문제 코드
SELECT 
	(
		SELECT
			B.COMMENT
		FROM
			USER_HIST B 
		WHERE
			B.USER_ID = A.USER_ID
			조건......
		),
		A.*
FROM
	USER_INFO A

-- 해결 코드
SELECT 
	B.COMMENT,
	A.*
FROM
	USER_INFO A
JOIN
	USER_HIST B
ON 
	A.USER_ID = B.USER_ID
-- JOIN으로 대체하자

1. LIKE 앞에 와일드카드 넣을 경우
   1. 와일드카드를 넣을 경우 인덱스 사용이 불가하자

SELECT 
		A.*
FROM
	USER_INFO A
WHERE
	A.USER_NM LIKE '%민수' -- 인덱스 X
	A.USER_NM LIKE '민수%' -- 인덱스 O

1. 부정 연산자를 사용할 경우

SELECT 
		A.*
FROM
	USER_INFO A
WHERE
	A.USER_NM != '2020' -- 이경우 TABLE FULL SCAN

위와 같이 적절한 연산자 혹은 조건을 활용하여 최대한 데이터를 추리는게 튜닝의 핵심이라 할 수 있다. 문법적으로 접근했을때 위와 같은 조건문 하나에 인덱스를 탈수 있는지 없는지가 판단이 된다.

※ 어떤 DB를 사용하냐에 따라 다르며 해당 문법에 맞게 사용하자.