CTE란?
CTE(Common Table Expression)는 서브 쿼리로 쓰이는 파생테이블(derived table)과 비슷한 개념으로 사용됩니다.
CTE는 ANSI SQL 99의 일부이며 MySQL 8.0.1에서 도입되었습니다.
CTE와 비교 대상으로는 VIEW가 있습니다. VIEW는 만들기 위해 권한이 필요하고 사전에 정의를 해야합니다. 반면, CTE는 권한이 필요 없고 하나의 쿼리문이 끝날때까지만 지속되는 일회성 테이블입니다.
CTE는 주로 복잡한 쿼리문에서 코드의 가독성과 재사용성을 위해 파생테이블 대신 사용하기에 유용합니다.
CTE를 사용하는 주요 이유는 다음과 같습니다.
- 쿼리의 가독성 향상
- 동일한 쿼리에서 여러 번 참조 가능
- 향상된 성능
- 사용자가 VIEW를 생성할 수 없는 경우 VIEW의 유효한 대안
- 재귀 쿼리 생성 가능: 계층적 데이터를 처리할 때 매우 유용할 수 있습니다.
SELECT , UPDATE 및 DELETE 문은 CTE를 참조할 수 있습니다.
CTE 생성 및 사용 방법
CTE는 하위 쿼리를 사용할 때 파생 테이블과 비슷하지만 기본 쿼리보다 먼저 선언합니다. 'WITH'라는 새로운 전용 절이 필요합니다.
유럽에서 가장 인구가 많은 국가를 찾기 위해 파생 테이블이 있는 서브 쿼리를 표현 합니다.
mysql> SELECT Name, Population
-> FROM (SELECT * FROM country WHERE continent='Europe') AS derived_t
-> ORDER BY Population DESC LIMIT 5;
+--------------------+------------+
| Name | Population |
+--------------------+------------+
| Russian Federation | 146934000 |
| Germany | 82164700 |
| United Kingdom | 59623400 |
| France | 59225700 |
| Italy | 57680000 |
+--------------------+------------+CTE를 사용하여 다시 작성해 보겠습니다.
mysql> WITH cte AS (SELECT * FROM country WHERE continent='Europe')
-> SELECT Name, Population
-> FROM cte
-> ORDER BY Population DESC LIMIT 5;
+--------------------+------------+
| Name | Population |
+--------------------+------------+
| Russian Federation | 146934000 |
| Germany | 82164700 |
| United Kingdom | 59623400 |
| France | 59225700 |
| Italy | 57680000 |
+--------------------+------------+구문은 매우 간단합니다. 쿼리 전에 WITH를 사용하여 CTE 또는 여러 개의 CTE를 정의할 수 있습니다. 그런 다음 쿼리에서 필요한 만큼 모든 CTE를 참조할 수 있습니다. CTE는 기본 쿼리 범위에 대한 임시 테이블로 사용할 수 있도록 미리 구체화된 쿼리로 생각할 수 있습니다.
CTE 이름 뒤에 괄호로 묶인 이름 목록이 오는 경우 열 이름을 지정할 수도 있습니다.
mysql> WITH cte(eur_name, eur_population) AS (SELECT Name, Population FROM country WHERE continent='Europe')
-> SELECT eur_name, eur_population
-> FROM cte
-> ORDER BY eur_opulation DESC LIMIT 5;
+--------------------+----------------+
| eur_name | eur_population |
+--------------------+----------------+
| Russian Federation | 146934000 |
| Germany | 82164700 |
| United Kingdom | 59623400 |
| France | 59225700 |
| Italy | 57680000 |
+--------------------+----------------+CTE는 다음 예와 같이 다른 테이블을 업데이트하기 위한 데이터 원본으로 사용할 수도 있습니다.
# 새 테이블 생성
mysql> CREATE TABLE country_2020 ( Code char(3), Name char(52), Population_2020 int, PRIMARY KEY(Code) );
Query OK, 0 rows affected (0.10 sec)
# 원본 데이터 복사
mysql> INSERT INTO country_2020 SELECT Code, Name, Population FROM country;
Query OK, 239 rows affected (0.01 sec)
Records: 239 Duplicates: 0 Warnings: 0
# 유럽 인구를 10% 증가
mysql> WITH cte(eur_code, eur_population) AS (SELECT Code, Population FROM country WHERE continent='Europe')
-> UPDATE country_2020, cte
-> SET Population_2020 = ROUND(eur_population*1.1)
-> WHERE Code=cte.eur_code;
Query OK, 46 rows affected (0.01 sec)
Rows matched: 46 Changed: 46 Warnings: 0
# 유럽이 아닌 데이터를 삭제
mysql> WITH cte AS (SELECT Code FROM country WHERE continent <> 'Europe')
-> DELETE country_2020
-> FROM country_2020, cte
-> WHERE country_2020.Code=cte.Code;
Query OK, 193 rows affected (0.02 sec)
mysql> SELECT * FROM country_2020 ORDER BY Population_2020 DESC LIMIT 5;
+------+--------------------+-----------------+
| Code | Name | Population_2020 |
+------+--------------------+-----------------+
| RUS | Russian Federation | 161627400 |
| DEU | Germany | 90381170 |
| GBR | United Kingdom | 65585740 |
| FRA | France | 65148270 |
| ITA | Italy | 63448000 |
+------+--------------------+-----------------+CTE는 다음과 같은 INSERT … SELECT 쿼리 에도 사용할 수 있습니다 .
mysql> CREATE TABLE largest_countries (Code char(3), Name char(52), SurfaceArea decimal(10,2), PRIMARY KEY(Code) );
Query OK, 0 rows affected (0.08 sec)
mysql> INSERT INTO largest_countries
-> WITH cte AS (SELECT Code, Name, SurfaceArea FROM country ORDER BY SurfaceArea DESC LIMIT 10)
-> SELECT * FROM cte;
Query OK, 10 rows affected (0.02 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM largest_countries;
+------+--------------------+-------------+
| Code | Name | SurfaceArea |
+------+--------------------+-------------+
| ARG | Argentina | 2780400.00 |
| ATA | Antarctica | 13120000.00 |
| AUS | Australia | 7741220.00 |
| BRA | Brazil | 8547403.00 |
| CAN | Canada | 9970610.00 |
| CHN | China | 9572900.00 |
| IND | India | 3287263.00 |
| KAZ | Kazakstan | 2724900.00 |
| RUS | Russian Federation | 17075400.00 |
| USA | United States | 9363520.00 |
+------+--------------------+-------------+
10 rows in set (0.00 sec)기본 쿼리 전에 미리 계산되거나 구체화된 임시 테이블과 같은 CTE를 만들 수 있습니다. 그리고 쿼리에서 필요한 만큼 해당 임시 테이블을 참조할 수 있습니다.
여러 개의 CTE를 만들 수 있으며 모두 기본 쿼리에서 사용할 수 있습니다. 구문은 다음과 같습니다.
WITH cte1 AS (SELECT ... FROM ... WHERE ...),
cte2 AS (SELECT ... FROM ... WHERE ...)
SELECT ...
FROM table1, table1, cte1, cte2 ....
WHERE .....
CTE의 범위
CTE는 서브쿼리에서도 사용할 수 있지만 이 경우 범위를 인지해야 한다. CTE는 단일 문의 범위에 대해 존재합니다.
다음과 같은 유효한 쿼리를 고려하십시오.
WITH cte AS (SELECT Code FROM country WHERE Population<1000000)
SELECT * FROM city WHERE city.CountryCode IN
(SELECT Code FROM cte); # Scope: "cte" is visible to top SELECT
SELECT * FROM city WHERE city.CountryCode IN
(WITH cte AS (SELECT Code FROM country WHERE Population<1000000)
SELECT Code from cte); # Scope: "cte" is not visible to top SELECT범위에 문제가 발생하지 않도록 하려면 CTE를 사용하는 가장 좋은 방법은 최상위 쿼리의 시작 부분에서 모든 CTE를 만드는 것입니다. 이러한 방식으로 모든 CTE는 필요한 곳 어디에서나 여러 번 사용할 수 있습니다.
여러 CTE 연결
쿼리에 대해 여러 CTE를 생성할 때 또 다른 흥미로운 기능은 chaining 입니다. 체인은 이전 CTE에 대한 하나 이상의 참조를 포함하는 모든 CTE를 정의할 수 있습니다.
다음 예는 체인을 사용하는 방법을 보여줍니다. 우리는 세계에서 가장 높은 인구 밀도와 가장 낮은 인구 밀도를 가진 국가를 찾고 싶습니다. 3개의 CTE로 구성된 체인을 만듭니다. 마지막 두 개는 첫 번째 것에 대한 참조를 포함합니다.
mysql> WITH density_by_country(country,density) AS
(SELECT Name, Population/SurfaceArea
FROM country
WHERE Population>0 and surfacearea>0),
max_density(country,maxdensity,label) AS
(SELECT country, density, 'max density'
FROM density_by_country
WHERE density=(SELECT MAX(density) FROM density_by_country)),
min_density(country,mindensity,label) AS
(SELECT country, density, 'min density'
FROM density_by_country
WHERE density=(SELECT MIN(density) FROM density_by_country))
SELECT * FROM max_density UNION ALL SELECT * FROM min_density;
+-----------+------------+-------------+
| country | maxdensity | label |
+-----------+------------+-------------+
| Macao | 26277.7778 | max density |
| Greenland | 0.0259 | min density |
+-----------+------------+-------------+이제 파생 테이블을 대신 사용하여 동일한 쿼리를 다시 작성할 수 있는 방법을 생각해 보았습니다. 'density_by_country' 의 정의를 (여러 번) 복사해야 합니다 . 최종 쿼리는 실제로 매우 커서 읽기가 어려울 수 있습니다.
VIEW 대신 CTE 사용
데이터베이스 사용자에게 VIEW를 생성할 권한이 없을 수 있습니다. CTE는 일반 SELECT와 마찬가지로 테이블과 열을 읽는 기능을 제외하고 특정 권한을 요구하지 않습니다.
그런 다음 VIEW 대신 CTE를 사용할 수 있습니다. 보조금 옵션을 제외하고 CTE는 일반적으로 나중에 보여주겠지만 VIEW보다 성능이 더 좋습니다.
뷰를 만들고 이를 사용하여 쿼리를 실행해 보겠습니다.
mysql> CREATE VIEW city_pop_by_country AS (SELECT countrycode, SUM(population) sum_population FROM city GROUP BY countrycode);
mysql> SELECT name, city_pop_by_country.sum_population/country.population ratio
FROM country, city_pop_by_country
WHERE country.code=city_pop_by_country.countrycode
AND country.population > (SELECT 10*AVG(sum_population) FROM city_pop_by_country);
+--------------------+--------+
| name | ratio |
+--------------------+--------+
| Bangladesh | 0.0664 |
| Brazil | 0.5048 |
| China | 0.1377 |
| Germany | 0.3194 |
| Egypt | 0.2933 |
| Ethiopia | 0.0510 |
| Indonesia | 0.1767 |
| India | 0.1216 |
| Iran | 0.3845 |
| Japan | 0.6153 |
| Mexico | 0.6043 |
| Nigeria | 0.1557 |
| Pakistan | 0.2016 |
| Philippines | 0.4072 |
| Russian Federation | 0.4706 |
| Turkey | 0.4254 |
| United States | 0.2825 |
| Vietnam | 0.1173 |
+--------------------+--------+
# VIEW를 사용한 쿼리의 실행 계획 확인
mysql> EXPLAIN SELECT name, city_pop_by_country.sum_population/country.population ratio FROM country, city_pop_by_country WHERE country.code=city_pop_by_country.countrycode AND country.population > (SELECT 10*AVG(sum_population) FROM city_pop_by_country);
+----+-------------+------------+------------+-------+---------------+-------------+---------+--------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+-------------+---------+--------------------+------+----------+-------------+
| 1 | PRIMARY | country | NULL | ALL | PRIMARY | NULL | NULL | NULL | 239 | 33.33 | Using where |
| 1 | PRIMARY | <derived3> | NULL | ref | <auto_key0> | <auto_key0> | 12 | world.country.Code | 16 | 100.00 | NULL |
| 3 | DERIVED | city | NULL | index | CountryCode | CountryCode | 12 | NULL | 4046 | 100.00 | NULL |
| 2 | SUBQUERY | <derived4> | NULL | ALL | NULL | NULL | NULL | NULL | 4046 | 100.00 | NULL |
| 4 | DERIVED | city | NULL | index | CountryCode | CountryCode | 12 | NULL | 4046 | 100.00 | NULL |
+----+-------------+------------+------------+-------+---------------+-------------+---------+--------------------+------+----------+-------------+이제 CTE를 사용하여 동일한 쿼리를 다시 작성해 보겠습니다.
mysql> WITH city_pop_by_country AS (SELECT countrycode, SUM(population) sum_population FROM city GROUP BY countrycode)
SELECT name, city_pop_by_country.sum_population/country.population ratio
FROM country, city_pop_by_country
WHERE country.code=city_pop_by_country.countrycode
AND country.population > (SELECT 10*AVG(sum_population) FROM city_pop_by_country);
+--------------------+--------+
| name | ratio |
+--------------------+--------+
| Bangladesh | 0.0664 |
| Brazil | 0.5048 |
| China | 0.1377 |
| Germany | 0.3194 |
| Egypt | 0.2933 |
| Ethiopia | 0.0510 |
| Indonesia | 0.1767 |
| India | 0.1216 |
| Iran | 0.3845 |
| Japan | 0.6153 |
| Mexico | 0.6043 |
| Nigeria | 0.1557 |
| Pakistan | 0.2016 |
| Philippines | 0.4072 |
| Russian Federation | 0.4706 |
| Turkey | 0.4254 |
| United States | 0.2825 |
| Vietnam | 0.1173 |
+--------------------+--------+
# CTE 사용한 쿼리의 실행 계획 확인
mysql> EXPLAIN WITH city_pop_by_country AS (SELECT countrycode, SUM(population) sum_population FROM city GROUP BY countrycode)
SELECT name, city_pop_by_country.sum_population/country.population ratio FROM country, city_pop_by_country WHERE country.code=city_pop_by_country.countrycode AND country.population > (SELECT 10*AVG(sum_population) FROM city_pop_by_country);
+----+-------------+------------+------------+-------+---------------+-------------+---------+--------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+-------------+---------+--------------------+------+----------+-------------+
| 1 | PRIMARY | country | NULL | ALL | PRIMARY | NULL | NULL | NULL | 239 | 33.33 | Using where |
| 1 | PRIMARY | <derived2> | NULL | ref | <auto_key0> | <auto_key0> | 12 | world.country.Code | 16 | 100.00 | NULL |
| 3 | SUBQUERY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 4046 | 100.00 | NULL |
| 2 | DERIVED | city | NULL | index | CountryCode | CountryCode | 12 | NULL | 4046 | 100.00 | NULL |
+----+-------------+------------+------------+-------+---------------+-------------+---------+--------------------+------+----------+-------------+두 가지 실행 계획을 살펴보면 쿼리에 DERIVED 단계가 있음을 알 수 있습니다 . VIEW의 구체화는 뷰가 참조될 때마다 더 많은 시간이 필요합니다.
샘플 데이터베이스는 작지만 두 쿼리의 실행 시간을 비교하기 위해 프로파일링을 활성화할 수 있습니다.
mysql> SET profiling=1;
# 쿼리를 여러번 수행
mysql> show profiles;
+----------+------------+-----------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-----------------------------------------------------------------------------------------------+
...
... |
| 35 | 0.00971925 | SELECT name, city_pop_by_country.sum_population/country.population ratio FROM country, ... |
| 36 | 0.00963100 | SELECT name, city_pop_by_country.sum_population/country.population ratio FROM country, ... |
| 37 | 0.00976900 | SELECT name, city_pop_by_country.sum_population/country.population ratio FROM country, ... |
| 38 | 0.00963875 | SELECT name, city_pop_by_country.sum_population/country.population ratio FROM country, ... |
| 39 | 0.00971200 | SELECT name, city_pop_by_country.sum_population/country.population ratio FROM country, ... |
| 40 | 0.00546550 | WITH city_pop_by_country AS (SELECT countrycode, SUM(population) sum_population FROM city ... |
| 41 | 0.00546975 | WITH city_pop_by_country AS (SELECT countrycode, SUM(population) sum_population FROM city ... |
| 42 | 0.00550325 | WITH city_pop_by_country AS (SELECT countrycode, SUM(population) sum_population FROM city ... |
| 43 | 0.00548000 | WITH city_pop_by_country AS (SELECT countrycode, SUM(population) sum_population FROM city ... |
| 44 | 0.00545675 | WITH city_pop_by_country AS (SELECT countrycode, SUM(population) sum_population FROM city ... |
+----------+------------+-----------------------------------------------------------------------------------------------+뷰가 있는 쿼리의 실행 시간은 약 0.0097초인 반면 CTE의 경우 약 0.0054초입니다. 따라서 CTE가 더 빠릅니다. 더 큰 테이블을 사용하고 보기에 대한 더 많은 참조가 있다는 것은 쿼리 간의 차이가 더 관련성이 있음을 의미합니다.
VIEW 대신 CTE를 사용하는 것이 단일 materialization만 필요하고(테이블이 cache처럼 임시로 저장된다는 의미) 생성된 임시 테이블을 주 쿼리에서 여러 번 참조할 수 있기 때문에 더 효율적입니다.
단, 이로 인해 CTE를 무분별하게 사용할 경우, Query performance가 오히려 더 떨어질 수 있습니다.
Query Optimizer가 execution plan을 계산할 때 CTE의 최적화를 고려하지 않고 결과를 그대로 이용합니다.
테이블에 설정한 index가 CTE에서는 적용되지 않게 됩니다.
CTE를 생성할 때 필요한 데이터를 미리 필터링하는 등 최적화를 고려한 쿼리가 필요할 수 있다.
결론
MySQL 8.0에서 사용할 수 있는 새로운 공통 테이블 표현식 기능을 도입했습니다. CTE를 사용하면 대부분의 경우 쿼리의 가독성을 단순화할 수 있고 VIEW 대신 CTE를 사용하여 전체 성능을 향상시킬 수도 있습니다.
'MySQL' 카테고리의 다른 글
| MySQL 테이블 단편화 (0) | 2021.09.02 |
|---|---|
| Recursive CTE(Common Table Expression) 활용 (0) | 2021.08.31 |
| MySQL Shell Upgrade Checker Utility (0) | 2021.08.24 |
| MySQL 8 및 MySQL 5.7 메모리 소비 (0) | 2021.08.24 |
| pt-kill (0) | 2021.08.19 |