[이렇게 사용하세요!] MySQL 8.0, 개발자를 위한 신규 기능 살펴보기! #2 SQL DML

​

안녕하세요, 네이버 클라우드 플랫폼입니다.

저번 게시글을 통해 12월 5일 네이버 클라우드 플랫폼이 ‘Cloud DB for MySQL 2.0 업데이트’가 완료되었다고 말씀드리면서 먼저 ‘InnoDB’과 관련된 내용을 전달해드렸습니다.

오늘은 MySQL 8.0, 개발자를 위한 신규 기능 그 두 번째 ‘SQL DML’을 소개해드리고자 합니다. 지금부터 상세히 설명해드리겠습니다.

# SQL DML

1. Windows Functions

2. CTE (Common Tables Expressions)

SQL DML은 Oracle 혹은 SQL Server를 사용해 보셨다면 익숙한 기능일 텐데요, 코드 복잡성을 줄여 개발 생산성 향상에 도움을 준다고 알려져 있는 Windows Functions와 CTE를 살펴보도록 하겠습니다.

​1. Windows Functions

먼저, 분석 함수 혹은 순위 함수로도 알려진 Windows Function입니다.

이해를 돕기 위해 집계 함수에서 설명을 시작하겠습니다. 왼쪽은 Sales 테이블의 전체 12개 행의 데이터를 출력한 결과입니다.

집계 함수인 SUM()을 사용해서 전체 profit의 합을 출력하면, 전체 12개 행에 대한 연산 결과가 단일 행으로 요약되는 것을 알 수 있습니다.

GROUP BY 절을 사용하면, 행의 서브셋에 집계 함수를 적용할 수 있는데요, 예를 들어 국가 별 profit의 합을 구한 결과를 보면 3개 국가에 대해 각 국가 별 profit의 합이 출력된 것을 볼 수 있습니다.

두 예를 통해 집계 함수는 쿼리에서 반환된 행의 수를 줄이는 것을 알 수 있습니다.

그런데 Sales 테이블 전체 결과를 출력하도록 유지하면서,

이전 예시에서 집계 함수를 통해 얻을 수 있었던 total_profit과 country_profit을 함께 출력하려면 어떻게 해야 할까요?

Windows Function을 사용하면 간단하게 해결할 수 있습니다.

Windows Function은 집계 함수와 유사하게 현재 행과 관련된 행 세트에서 계산을 수행하지만 집계 함수와 다르게 연산 결과를 단일 출력 행으로 그룹화하지 않고, 개별 행에 계속 액세스하면서 여러 행에 대한 집계 연산을 수행한다는 점에서 차이가 있습니다.

Windows Function은 반드시 키워드 OVER가 필수로 포함되어야 합니다.

그리고 이어지는 괄호 안에는 몇 개의 definition을 정의할 수 있는데요, 바로 이 OVER 절에 정의된 행 세트에서 앞선 예제와 같이 SUM()과 같은 집계 함수뿐만 아니라 다양한 함수들이 동작합니다.

↑ 이전의 요구 사항과 같은 결과를 얻기 위해

(1) total_profit을 얻기 위해 부모 쿼리와 관계없이 독립된 쿼리를 서브 쿼리 형태로 사용

(2) 서브셋의 집계 결과를 얻기 위해 부모 쿼리의 특정 컬럼과 조건을 비교하는 방식으로 서브 쿼리를 사용

하는 방식으로 쿼리를 작성할 수도 있지만

키워드 OVER를 통해 참조할 행 세트를 오픈하는 Windows Function을 사용하면 다음과 같이 간단하고 직관적인 형태로도 동일한 결과를 얻을 수 있습니다.

2. CTE

다음은 ‘WITH 쿼리’라고도 불리는 CTE(Common Tables Expressions)를 살펴보겠습니다.

CTE는 서브 쿼리로 쓰이는 파생 테이블(Derived Table)과 비슷한 개념으로 사용되며 하나의 쿼리 문이 끝날 때까지 지속되는 것으로 다른 SELECT, INSERT, UPDATE 또는 DELETE 문 내에서 여러 번 참조 가능한 결과 집합입니다.

MySQL에서는 8.0 버전부터 CTE를 사용해 복잡한 조인과 하위 쿼리를 단순화하고, 계층적으로 데이터를 처리할 수 있게 되었습니다.

CTE의 구문은 매우 간단합니다. CTE는 질의의 첫 번째 부분에 지정되는데, 키워드 WITH 뒤에 CTE Alias, 키워드 AS, 그리고 괄호 안에 참조할 구문을 정의하면 됩니다.

Alias와 AS 키워드만으로 쉼표로 구분된 복수 개의 CTE를 정의할 수 있고, 모든 CTE는 이전에 정의한 CTE를 참조하여 복잡한 SQL 구문을 단순화시킬 수 있습니다.

사용되는 용도는 파생 테이블과 비슷하지만 CTE를 잘 사용하면

(1) Better Readability : 쿼리를 단순화하여 가독성을 높일 수 있고,

(2) Easier Channing : 파생 테이블과 다르게 이전에 정의한 CTE도 참조할 수 있을 뿐만 아니라

(3) Can be referenced multiple times : 한 번의 정의로 여러 번 참조 가능하다는 부분에서

이점이 있습니다.

이어서 CTE을 잘 활용한 경우 얻을 수 있는 성능적 이점을 살펴보겠습니다.

양쪽은 동일한 쿼리입니다. 하단의 음영 표시된 부분에서 각각 VIEW와 CTE를 참조해 성능을 비교해 보도록 하겠습니다.

(왼쪽은 파생 테이블로 사용되는 쿼리를 VIEW라는 데이터베이스 객체에 저장한 형태로 사용했습니다)

먼저 VIEW를 사용한 쿼리의 실행계획을 보면, FROM 절에서 조인 테이블로 참조한 부분과, WHERE 조건의 서브 쿼리로 사용된 부분에서 각각 Materialised(구체화), 즉 VIEW를 실행한 결과를 임시 저장해 구체화한 것을 볼 수 있습니다.

CTE를 사용한 쿼리의 실행계획을 보면, 앞서 본 VIEW와 마찬가지로 FROM 절에서 조인 테이블로 참조된 곳에서만 한 번 구체화되었음을 볼 수 있습니다.

즉, MySQL에서는 CTE에 대한 여러 참조에 대해 단일 임시 테이블을 사용하기 때문에 WHERE 조건에서 서브 쿼리로 참조된 부분에서는 구체화의 과정이 생략되었습니다.

이 쿼리의 경우 이러한 구체화가 가장 실행 시간이 많이 걸리는 부분이었기 때문에 VIEW를 사용할 때 보다 실행 시간을 거의 절반으로 줄일 수 있게 되었습니다.

앞서 본 예와 같이 CTE가 여러 참조에 대해 단일 임시 테이블을 사용한다는 특성을 이용해 성능 향상을 기대해 볼 수 있기는 하지만, 오히려 이러한 특성으로 인해 무분별하게 사용되는 경우 쿼리 퍼포먼스가 떨어질 수 있다는 점을 양지하여 사용 전 충분한 테스트가 필요하다는 것은 꼭 기억하시면 좋겠습니다.

(참고: MySQL 8.0: Improved performance with CTE — MySQL Server Blog)

글을 마무리하며

이번 포스팅을 통해 이번에는 MySQL 8.0의 새로운 두 번째 기능 ‘SQL DML’과 관련해 ‘1. Windows Functions’ ‘2. CTE (Common Tables Expressions)’에 대해 상세히 설명해드렸습니다.

관련 정보는 네이버 클라우드 플랫폼 공식 홈페이지를 통해 확인해보실 수 있습니다. 다음 포스팅부터 차례대로 ‘SQL DDL’ ‘Indexes’ ‘JSON’ 기능을 만나보실 수 있습니다.

이상입니다. 더 유익한 글로 찾아뵙겠습니다.