DB

[MySQL]MySQL 벼락치기(12) - 쿼리최적화(3)

강씨아저씨 2018. 11. 4. 18:06

이번 포스팅은 사내에서 MySQL 관련 내용 발표를 위해 Real MySQL(http://wikibook.co.kr/real-mysql/) 서적을 기반으로 학습하고 이해한 내용을 정리하는 포스팅이다. 포스팅에서는 주로 InnoDB 스토리지 엔진을 기준으로 설명할 예정이다. 

MySQL 역시 내용이 많기 때문에 시리즈로 나눠서 정리할 예정이다.

이번에는 쿼리최적화에 대해서 알아볼 예정이고 오늘은 그중에서도 JOIN을 사용하려면 알아야 하는 정보에 대해서 알아볼 예정이다. 

JOIN 의 순서와 인덱스

조인을 살펴보기 전에 인덱스 레인지 스캔으로 레코드를 읽는 작업을 다시 한번 살펴보자.

  1. 인덱스에서 조건을 만족하는 값이 지정된 위치를 찾는다. 이 과정을 인덱스탐색(index seek) 이라고 한다. 
  2. 1번에서 탐색된 위치부터 필요한 만큼 인덱스를 쭉 읽는다. 이 과정을 인덱스스캔(index scan) 이라고 한다. 
  3. 2번에서 읽어들인 인덱스 키와 레코드 주소를 이용해 레코드가 저장된 페이지를 가져오고 최종 레코드를 읽어온다.

조인 작업에서 드라이빙 테이블을 읽을 때는 인덱스탐색 작업을 단 한번만 수행하고 그 이후부터는 스캔만 실행하면 된다. 하지만 드리븐 테이블에서는 인덱스탐색 작업과 스캔 작업을 드라이빙 테이블에서 읽은 레코드 건수만큼 반복한다. 드라이빙 테이블과 드리븐 테이블이 1:1 로 조인되더라도 드리븐 테이블을 읽는 것이 훨씬 큰 부하를 차지하는 것이다. 그래서 옵타이마저는 항상 드라이빙 테이블이 아닌 드리븐 테이블을 최적으로 읽을 수 있게 실행 계획을 수립한다. 
다음과 같은 쿼리를 살펴보자.

SELECT * 
FROM employees e, dept_emp de
WHERE e.emp_no = de.emp_no;

이 두 테이블의 조인 쿼리에서 employees 테이블의 emp_no 컬럼과 dept_emp 테이블의 emp_no 컬럼에 각각 인덱스가 있을때와 없을때 조인순서가 어떻게 달라지는지 살펴보자. 

  • 두 컬럼 모두 각각 인덱스가 있는경우
    • 어느 테이블을 드라이빙으로 선택하든 인덱스를 이용해 드리븐 테이블의 검색 작업을 빠르게 처리할 수 있다. 이럴때는 옵티마이저가 통계정보를 이용해 적절히 드라이빙 테이블을 선택하게 된다.
  • employees.emp_no 에만 인덱스가 있는 경우
    • employees.emp_no 에만 인덱스가 있을 때 dept_emp 테이블이 드리븐 테이블로 선택된다면 employees 테이블의 레코드 건수만큼 dept_emp 테이블을 풀 스캔해야만 e.emp_no = de.emp_no 조건에 일치하는 레코드를 찾을 수 있다. 그래서 옵티마이저는 항상 dept_emp 테이블을 드라이빙 테이블로 선택하고 employees 테이블을 드리븐 테이블로 선택하게 된다. 
  • dept_emp.emp_no 에만 인덱스가 있는경우
    • 위의 "employees.emp_no 에만 인덱스가 있는 경우"와 반대로 처리된다. 이때는 employees 테이블의 반복된 풀 스캔을 막기 위해 employees 테이블을 드라이빙 테이블로 선택하고 dept_emp 테이블을 드리븐 테이블로 조인을 행하도록 실행계획을 수립한다. 
  • 두 컬럼 모두 인덱스가 없는경우
    • "두 컬럼 모두 각각 인덱스가 있는 경우"와 마찬가지로 어느 테이블을 드라이빙으로 선택하더라도 드리븐 테이블의 풀 스캔은 발생하기 때문에 옵티마이저가 적절히 드라이빙 테이블을 선택한다. 단 레코드 건수가 적은 테이블을 드리븐 테이블로 선택하는것이 효율적이다. 또한 드리븐 테이블을 읽을 때 조인 버퍼가 사용되기 때문에 실행계획의 Extra 컬럼에 "Using join buffer" 가 표시된다.

결국 조인이 수행될때 조인되는 양쪽 테이블의 컬럼에 모두 인덱스가 없을때만 드리븐 테이블을 풀 스캔한다. 나머지 드라이빙 테이블은 풀 테이블 스캔을 사용할 수는 있어도 드리븐 테이블을 풀 테이블 스캔으로 접근하는 실행계획은 옵티마이저가 거의 만들어내지 않는다. 

JOIN 컬럼의 데이터 타입

WHERE 절에 사용하는 조건 중에서 비교 대상 컬럼과 표현식의 타입을 동일하게 사용해야 하는 이유는 이미 자세히 살펴봤다.(https://idea-sketch.tistory.com/51) 이것은 테이블 조인을 위한 조인조건에서도 동일하다. 조인컬럼 간의 비교에서 각 컬럼의 데이터 타입이 일치하지 않으면 인덱스를 효율적으로 이용할 수 없다.
대표적으로 다음의 비교 패턴은 문제가 될 가능성이 높다. 

  • CHAR 타입과 INT 타입의 비교와 같이 데이터타입의 종류가 완전히 다른 경우
  • 같은 CHAR 타입이더라도 문자집합이나 콜레이션이 다른경우
  • 같은 INT 타입이더라도 부호(Sign)가 있는지 여부가 다른경우

OUTER JOIN 과 COUNT(*)

페이징 처리를 위해 조건에 일치하는 레코드의 건수를 가져오는 쿼리에서 OUTER JOIN 과 COUNT(*)를 자주 함께 사용하곤 한다. 일반적으로 페이징 처리는 테이블의 레코드를 가져오는 쿼리와 단순히 레코드 건수만 가져오는 쿼리가 쌍으로 사용된다. 그런데 주로 테이블의 레코드를 가져오는 쿼리에서 SELECT 절의 내용만 COUNT(*) 로 바꿔서 일치하는 레코드 건수를 조회하는 쿼리를 만들기때문에 불필요하게 OUTER JOIN 으로 연결되는 테이블이 자주 있다. 만약 건수를 조회하는 쿼리에서 OUTER JOIN 으로 조인된 테이블의 다음 2가지 조건이 만족한다면 해당 테이블은 불필요하게 조인에 포함된 것이다. 조인에 불필요한 테이블을 제거하면 같은 결과를 더 빠르게 가져올 수 있다. 

  • 드라이빙 테이블과 드리븐 테이블의 관계가 1:1 또는 M:1 인경우
  • 드리븐 테이블에 조인조건 이외의 별도 조건이 없는 경우

위의 두가지 조건 가운데 첫번째는 OUTER JOIN으로 연결되는 테이블에 의해 레코드 건수가 더 늘어나지 않아야 한다는 것을 의미하고 두번째 조건은 OUTER JOIN으로 연결되는 테이블에 의헤 레코드 건수가 더 줄어들지 않아야 한다는 것을 의미한다. 

INNER JOIN 과 OUTER JOIN 의 선택

쿼리나 테이블의 구조를 살펴보면 OUTER JOIN을 사용하지 않아도 될 것을 OUTER JOIN으로 사용할 때가 상당히 많다. 때로는 그 반대로 OUTER JOIN으로 실행하면 쿼리의 처리가 느려진다고 생각하고 억지로 INNER JOIN으로 쿼리를 작성하려는 경우도 있다. 사실 OUTER JOIN과 INNER JOIN은 실제 가져와야 하는 레코드가 같다면 쿼리의 성능이 거의 차이가 없다고 한다. INNER JOIN과 OUTER JOIN 은 성능을 고려해서 선택할 것이 아니라 업무 요건에 따라 선택하는것이 옳다. 

지연된 조인(Delayed Join)

지연된 조인이란 조인이 실행되기 이전에 GROUP BY 나 ORDER BY를 처리하는 방식을 의미한다.
인덱스를 사용하지 못하는 GROUP BY 와 ORDER BY 쿼리를 지연된 조인으로 처리하는 방법을 한번 살펴보자. 

SELECT *
FROM employees e
  LEFT JOIN dept_emp de ON de.emp_no = e.emp_no
    AND NOW() BETWEEN de.from_date AND de.to_date
LIMIT 0, 10;

이 쿼리는 사원의 정보와 그 사원이 현재 소속된 부서의 정보를 보여주는 쿼리다. dept_emp 테이블에는 특정 사원이 지금까지 소속됐던 부서의 이력이 저장돼 있기 때문에 현재 소속된 부서만 가져오기 위해 NOW() BETWEEN de.from_date AND de.to_date 조건이 필요하다. 그리고 마지막 LIMIT 절은 10건씩만 끊어서 화면에 보여주기 위해 사용한 것이다. 

이 쿼리의 문제점을 한번 살펴보자. 우선 첫번째 페이지에서는 아무런 문제도 없다. 하지만 두번째 페이지가 호출되면 쿼리는 다음과 같이 변경되어 실행될 것이다. 

SELECT *
FROM employees e
  LEFT JOIN dept_emp de ON de.emp_no = e.emp_no
    AND NOW() BETWEEN de.from_date AND de.to_date
LIMIT 10, 10;

이 쿼리는 employees 테이블이 드라이빙 테이블이 되고 dept_emp 테이블이 드리븐 테이블이 되어 조인이 실행될 것이다. 우선 employees 테이블을 풀 테이블 스캔으로 한 건씩 읽으면서 dept_emp 테이블과 조인해서 조건에 일치하는 레코드를 가져오다가 LIMIT 조건에 필요로 하는 20건이 채워지면 쿼리를 종료한다. 하지만 여기서 20건의 레코드를 가져오지만 앞의 10건은 버리게 된다. LIMIT 절에서는 결과 레코드의 11번째 레코드부터 필요하기 때문이다. 

이 쿼리를 지연된 조인으로 처리하면 꼭 필요한 10건에 대해서만 dept_emp 테이블과 조인하게 만들수 있다. 

SELECT * 
FROM ( SELECT * FROM employees e LIMIT 990, 10) e
  LEFT JOIN dept_emp de ON de.emp_no = e.emp_no
    AND NOW() BETWEEN de.from_date AND de.to_date

employees 테이블에서 990 번째부터 10개의 레코드만 가져오는 서브 쿼리로 FROM 절의 employees 테이블을 대체했다. 그리고 이 서브 쿼리의 결과가 저장된 임시테이블의 레코드 10건과 dept_emp 테이블을 아우터로 조인했다. 즉 꼭 필요한 레코드만 조인을 수행한 것이다. 

일반적으로 지연된 조인으로 쿼리를 개선했을때 FROM 절의 서브 쿼리 결과가 저장되는 임시테이블이 드라이빙 테이블이 되어 나머지 테이블과 조인을 수행하므로 임시테이블에 저장되는 레코드 건수가 작업량에 커다란 영향을 미치게 된다. 그래서 파생 테이블에 저장돼야 할 레코드 건수가 적으면 적을수록 지연된 조인의 효과가 커진다. 따라서 쿼리에 GROUP BY 나 DISTINCT 등과 LIMIT 절이 함께 사용된 쿼리에서 상당히 효과적이다. 

지연된 조인은 경우에 따라서 상당항 성능 향상을 가져올 수 있지만 모든 쿼리를 지연된 조인 형태로 개선할 수 있는 것은 아니다. OUTER JOIN 과 INNER JOIN 에 대해서 다음과 같은 조건이 갖춰져야만 지연된 쿼리로 변경해서 사용할 수 있다. 

  • LEFT OUTER JOIN 의 경우 드라이빙 테이블과 드리븐 테이블은 1:1 또는 M:1 관계여야 한다. 
  • INNER JOIN 인 경우 드라이빙 테이블과 드리븐 테이블은 1:1 또는 M:1 관계임과 동시에 드라이빙 테이블에 있는 레코드는 드리븐 테이블에 모두 존재해야 한다. 두번째 조건은 드라이빙 테이블을 서브쿼리로 만들고 이 서브쿼리에 LIMIT 를 추가해도 최종결과의 건수가 변하지 않는다는 것을 보증해주는 조건이기 때문에 반드시 정확히 확인후 적용해야 한다. 

오늘은 여기까지~

누군가에게 도움이 되었길 바라면서 오늘의 포스팅 끝~