Last Updated on 4월 22, 2022 by Jade(정현호)
안녕하세요
이번 포스팅에서는 MySQL 서버 8.0.24 추가된 Transform correlated scalar subqueries 기능에 대한 내용으로 아래 포스팅에서 이어지는 두번째 글 입니다.
Subquery in JOIN clause
서브 쿼리가 JOIN 절에 있으면 파생 테이블이 원래의 내부 조인보다 먼저 외부 테이블에 조인이 되게 됩니다.
그래서 상관 관계가 있는 경우 다음과 같이 변환을 시도 됩니다.
SELECT COUNT(*)
FROM t1 a
JOIN
t1 outr
ON a.a= (SELECT count(*)
FROM t1 inr
WHERE inr.a = outr.a);
->
SELECT COUNT(0) AS `COUNT(*)`
FROM t1 a
LEFT JOIN
( SELECT COUNT(0) AS `count(*)`, -- the new derived table!!
inr.a AS a
FROM t1 inr
GROUP BY inr.a) derived_1_2
ON derived_1_2.a = outr.a
JOIN t1 outr
WHERE (a.a = COALESCE(derived_1_2.`count(*)`,0));
이 쿼리를 실행하면 아래와 같은 에러 메세지가 발생되게 됩니다.
ERROR 1054 (42S22): Unknown column 'outr.a' in 'on clause'
JOIN 절에 사용된 "outr.a"은 두 번째 조인 테이블에서 가져온 것이기 때문에 에러가 발생되는 것 입니다.
그래서 원래 쿼리 순서를 다음과 같이 재정렬 하여 변경합니다.
SELECT COUNT(0) AS `COUNT(*)`
FROM t1 outr
LEFT JOIN
( SELECT COUNT(0) AS `count(*)`,
inr.a AS a
FROM t1 inr
GROUP BY inr.a) derived_1_2
ON derived_1_2.a = outr.a
JOIN t1 a
WHERE (a.a = COALESCE(derived_1_2.`count(*)`,0));
다만 조인 절이 outer join의 일부인 경우 재정렬할 수 없고 Query 변환이 불가능합니다.
현재까지는 inner join 케이스에 대한 재정렬도 시도하지 않습니다. 즉, 상관 관계가 있는 경우 JOIN 절에서 스칼라 하위 쿼리 변환을 지원하지 않습니다.
-- ORIGINAL
explain
SELECT COUNT(*)
FROM t2 a
JOIN
t2 outr
ON a.a= (SELECT count(*)
FROM t2 inr
WHERE inr.a = outr.a);
+----+--------------------+-------+-------+------+---------+------------+------+----------+--------------------------+
| id | select_type | table | type | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------+-------+------+---------+------------+------+----------+--------------------------+
| 1 | PRIMARY | outr | index | ix_a | 5 | NULL | 5 | 100.00 | Using index |
| 1 | PRIMARY | a | ref | ix_a | 5 | func | 1 | 100.00 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | inr | ref | ix_a | 5 | tdb.outr.a | 1 | 100.00 | Using index |
+----+--------------------+-------+-------+------+---------+------------+------+----------+--------------------------+
-- subquery_to_derived 활성화
set session optimizer_switch='subquery_to_derived=on';
explain
SELECT COUNT(*)
FROM t2 a
JOIN
t2 outr
ON a.a= (SELECT count(*)
FROM t2 inr
WHERE inr.a = outr.a);
+----+--------------------+-------+-------+------+---------+------------+------+----------+--------------------------+
| id | select_type | table | type | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------+-------+------+---------+------------+------+----------+--------------------------+
| 1 | PRIMARY | outr | index | ix_a | 5 | NULL | 5 | 100.00 | Using index |
| 1 | PRIMARY | a | ref | ix_a | 5 | func | 1 | 100.00 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | inr | ref | ix_a | 5 | tdb.outr.a | 1 | 100.00 | Using index |
+----+--------------------+-------+-------+------+---------+------------+------+----------+--------------------------+
--> plan 변경 없음
위의 내용 과 같이 변환이 안되기 때문에 subquery_to_derived 를 활성화 해도 변환이 되지 않습니다.
Window functions
서브 쿼리에 Window functions 가 포함된 경우에도 변환이 시도되지 않습니다.
ORDER BY + LIMIT/OFFSET
ORDER BY + LIMIT/OFFSET 의 쿼리에서의 내용을 확인 해보기 위해서 먼저 테스트에 사용할 테이블을 생성 하도록 하겠습니다.
-- t5 테이블 생성
CREATE TABLE t5 (id INT, contract_id INT, datestamp DATETIME);
INSERT INTO t5 VALUES
(1,2,'2006-09-18 09:07:53'), (2,3,'2006-09-18 09:07:53'),
(3,4,'2006-09-18 09:07:53'), (4,10,'2008-09-18 09:07:53'),
(5,7,'2006-09-18 09:07:53'), (6,5,'2006-09-18 09:07:53'),
(7,9,'2006-09-18 09:07:53'), (8,10,'2006-09-18 09:07:53'),
(9,10,'2010-09-18 09:07:53'), (10,6,'2014-09-18 09:07:53');
-- t6 테이블 생성
CREATE TABLE t6 (id INT);
INSERT INTO t6 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
조회를 하면 아래와 같은 조회결과를 확인 할 수 있습니다.
SELECT (SELECT datestamp
FROM t5
WHERE contract_id = t6.id
ORDER BY datestamp ASC
LIMIT 1) AS subq
FROM t6;
+---------------------+
| subq |
+---------------------+
| NULL |
| 2006-09-18 09:07:53 |
| 2006-09-18 09:07:53 |
| 2006-09-18 09:07:53 |
| 2006-09-18 09:07:53 |
| 2014-09-18 09:07:53 |
| 2006-09-18 09:07:53 |
| NULL |
| 2006-09-18 09:07:53 |
| 2006-09-18 09:07:53 |
+---------------------+
계속 설명한 내용과 같이 쿼리를 변경하게 되면 아래와 같이 변경할 수 있게 됩니다.
SELECT derived_1_2.datestamp AS subq
FROM t6 LEFT JOIN
( SELECT t5.datestamp AS datestamp,
t5.contract_id AS contract_id,
COUNT(0) AS Name_exp_3
FROM t5
GROUP BY t5.contract_id
ORDER BY t5.datestamp
LIMIT 1) derived_1_2
ON (derived_1_2.contract_id = t6.id) AND
(derived_1_2.Name_exp_3 reject_if > 1);
하지만 이 쿼리는 잘못된 답을 주게 됩니다.
+---------------------+ | subq | +---------------------+ | NULL | | 2006-09-18 09:07:53 | | NULL | | NULL | | NULL | | NULL | | NULL | | NULL | | NULL | | NULL | +---------------------+
contract_id 에 대해서 그룹화(group by) 후 datestamp로 정렬한 결과에서 LIMIT 1은 하나의 그룹만 출력을 하게 되기 때문입니다.
(derived_1_2 파생 테이블의 결과가 1건임)
표현식 순서대로 그룹화을 하게 되면 아래와 같이 쿼리로 변환이 되며, 결과를 얻을 수 있습니다.
+---------------------+ | subq | +---------------------+ | NULL | | 2006-09-18 09:07:53 | | 2006-09-18 09:07:53 | | 2006-09-18 09:07:53 | | 2006-09-18 09:07:53 | | 2014-09-18 09:07:53 | | 2006-09-18 09:07:53 | | NULL | | 2006-09-18 09:07:53 | | 2006-09-18 09:07:53 | | 2008-09-18 09:07:53 | <-- 결과가 다른 추가로 출력된 값 | 2010-09-18 09:07:53 | <-- 결과가 다른 추가로 출력된 값 +---------------------+
처음 변환된 쿼리에 비해서 더 좋은 결과를 출력 되었지만, 여전히 2개의 잘못된 행이 출력되고 있습니다.
파생 테이블(derived_1_2)의 ON 절에 일치하는 여러 행이 있으므로 다시 datestamp 값은 잘못된 값을 얻게 됩니다.
안의 내부 쿼리만 별도로 수행하면 아래와 같은 결과를 확인 할 수 있습니다.
SELECT t5.datestamp AS datestamp,
t5.contract_id AS contract_id
FROM t5
GROUP BY t5.contract_id, t5.datestamp
ORDER BY t5.datestamp DESC;
+---------------------+-------------+
| datestamp | contract_id |
+---------------------+-------------+
| 2014-09-18 09:07:53 | 6 |
| 2010-09-18 09:07:53 | 10 | <--- 원본의 결과와 맞지 않는 값
| 2008-09-18 09:07:53 | 10 | <--- 원본의 결과와 맞지 않는 값
| 2006-09-18 09:07:53 | 2 |
| 2006-09-18 09:07:53 | 3 |
| 2006-09-18 09:07:53 | 4 |
| 2006-09-18 09:07:53 | 7 |
| 2006-09-18 09:07:53 | 5 |
| 2006-09-18 09:07:53 | 9 |
| 2006-09-18 09:07:53 | 10 |
+---------------------+-------------+
DESC 로 정렬하면 올바른 결과를 얻을 수 있지만 그것에 의존할 수는 없습니다.
그래서 올바른 SQL 변환은 다음과 같습니다.
SELECT derived_1_2.datestamp AS subq
FROM t6
LEFT JOIN
( SELECT MIN(datestamp) OVER () AS datestamp,
t5.contract_id AS contract_id
FROM t5
GROUP BY contract_id, datestamp) derived_1_2
ON derived_1_2.contract_id = t6.id;
+---------------------+
| subq |
+---------------------+
| NULL |
| 2006-09-18 09:07:53 |
| 2006-09-18 09:07:53 |
| 2006-09-18 09:07:53 |
| 2006-09-18 09:07:53 |
| 2006-09-18 09:07:53 |
| 2006-09-18 09:07:53 |
| NULL |
| 2006-09-18 09:07:53 |
| 2006-09-18 09:07:53 |
| 2006-09-18 09:07:53 |
| 2006-09-18 09:07:53 |
+---------------------+
즉, ORDER BY ASC + LIMIT 1을 MIN으로 효과적으로 변환할 수 있습니다.
그러나 MySQL Team은 이번 WL(#13520)에서 LIMIT/OFFSET을 사용한 상관 서브 쿼리에 대해서는 변환하지 않았다고 합니다.
그래서 이와 같은 케이스의 경우에도 subquery_to_derived 활성화에도 Query 변환이 되지 않으며 Plan 도 유지하게 됩니다.
이번 포스팅에서는 subquery_to_derived 를 활성화 하였을 경우 서브쿼리에서 derived table 로 변경되는 사례와 변경이 불가능한 사례 몇가지를 확인해 보았습니다.
Oracle 에서도 위의 내용과 같이 서브쿼리를 인라인뷰(테이블의 서브쿼리)로의 변환을 서브쿼리 unnest 라고 하며, 가능한 범위 내에서(기본 전제는 동일한 결과를 보장하는) Query Transformation 이 이루어지게 됩니다.
몇몇 경우 서브쿼리를 unnest 하지 않는 것이 더 좋은 수행 방법이 될수도 있으나, 대부분의 경우 서브쿼리를 unnest 를 하여 메인 쿼리 블럭과 동일한 level 로 맞춤으로써 다앙한 Access 방법과 Join 방법을 사용하는 방향으로 수행되고 있습니다.
MySQL 의 경우도 계속적으로 서브쿼리에 대해서 개선 하기 위해서 semijoin 이나 materialization 와 같은 기능을 활용하거나, 쿼리 작성시 부터 서브쿼리 보다는 Join 을 사용하도록 쿼리를 작성(또는 수정)하여 사용해왔습니다.
물론 여러 테스트나 사전에 검증과 확인이 필요한 것은 맞으나 위에서 설명된 여러가지 개선된 내용을 확인했을 때 subquery_to_derived 옵티마이저를 사용하여 서브쿼리를 조금 더 적극 적으로 풀어내는 방향으로 진행해도 좋지 않을까 하는 생각으로 포스팅을 마무리 합니다.
Reference
Reference Link
• mysql.com/worklog/13520
• mysql.com/blog/mysql-8-0-24-release
• mysql.com/correlated-subqueries
• mysql.com/subquery-materialization
• mysql.com/transformation-scalar-in
• mariadb.com/subqueries-scalar-subqueries
• oracle.com/oracle/Scalar-Subquery-Expressions
연관된 다른 글
Principal DBA(MySQL, AWS Aurora, Oracle)
핀테크 서비스인 핀다에서 데이터베이스를 운영하고 있어요(at finda.co.kr)
Previous - 당근마켓, 위메프, Oracle Korea ACS / Fedora Kor UserGroup 운영중
Database 외에도 NoSQL , Linux , Python, Cloud, Http/PHP CGI 등에도 관심이 있습니다
purityboy83@gmail.com / admin@hoing.io
