select * from Employee;
-- Beats 69.25%
EXPLAIN
SELECT salary as SecondHighestSalary
FROM (
SELECT salary
FROM Employee
WHERE salary < (SELECT MAX(salary) FROM Employee)
UNION ALL
SELECT NULL AS salary
) AS TempTable
ORDER BY salary DESC
LIMIT 1
;
-- io가 두번
--
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using filesort |
| 2 | DERIVED | Employee | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 50.00 | Using where |
| 3 | SUBQUERY | Employee | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | NULL |
| 4 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------+
최대 급여 찾기: 쿼리의 이 부분은 동일한 테이블에서 최대 급여를 찾기 위해 또 다른 전체 스캔을 수행합니다.
NULL 추가 및 최종 정렬: UNION ALL을 통해 NULL 값이 추가되고,
최종 결과는 내림차순으로 정렬된 후 LIMIT 1로 가장 상단의 결과만 선택됩니다.
이 쿼리는 두 번의 전체 테이블 스캔(type: ALL)을 수행합니다.
이것은 데이터베이스에 인덱스가 없거나 쿼리 최적화기가 인덱스를 사용하지 않기로 결정했음을 의미합니다.
또한, Using filesort는 정렬에 추가적인 리소스가 소모됨을 나타냅니다.
/*
SELECT TOP 1 salary as SecondHighestSalary
FROM (
SELECT salary
FROM Employee
WHERE salary < (SELECT MAX(salary) FROM Employee)
UNION ALL
SELECT NULL AS salary
) AS TempTable
ORDER BY salary DESC;
*/
-- 여기서 막힘
-- salary가 없을 때, 어떻게하면 될까?
-- 어떻게하면 null이 안뜨게 하지?
-- 구글링을 해보니 aggregate function을 쓰면 된다고 한다.
SELECT
salary as SecondHighestSalary
FROM (
SELECT
salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM Employee
) as SalaryRanking
WHERE rnk = 2;
;
-- beats 99.2%
-- window function
EXPLAIN
WITH SalaryRanking AS (
SELECT
salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM Employee
)
SELECT
MAX(salary) as SecondHighestSalary
FROM SalaryRanking
WHERE rnk = 2;
-- io가 1번
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+----------------+
| 1 | PRIMARY | <derived2> | NULL | ref | <auto_key0> | <auto_key0> | 8 | const | 1 | 100.00 | NULL |
| 2 | DERIVED | Employee | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using filesort |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+----------------+
-- beats 99.39%
-- subquery
EXPLAIN
SELECT
MAX(salary) as SecondHighestSalary
FROM (
SELECT
salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM Employee
) as SalaryRanking
WHERE rnk = 2;
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+----------------+
| 1 | PRIMARY | <derived2> | NULL | ref | <auto_key0> | <auto_key0> | 8 | const | 1 | 100.00 | NULL |
| 2 | DERIVED | Employee | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using filesort |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+----------------+
id: 1, select_type: PRIMARY 부분에서는 이 CTE를 참조하여 최종 결과를 계산합니다.
여기서는 rnk 컬럼을 참조하여 두 번째로 높은 급여(WHERE rnk = 2)를 찾습니다.
결과적으로, 이 쿼리는 먼저 Employee 테이블을 전체 스캔하여 급여에 대한 순위를 매기고,
그 후에 이 순위를 사용하여 두 번째로 높은 급여를 찾습니다.
id: 2, select_type: DERIVED 부분에서는 Employee 테이블을 전체 스캔(type: ALL)하여
SalaryRanking CTE를 구성합니다. 이 과정에서 파일 정렬(Using filesort)이 사용됩니다.
Using filesort는 순위를 매기기 위한 정렬 과정에서 추가적인 비용이 발생함을 나타냅니다.
SQL
복사
의문
CTE vs Subquery
어떤 것이 더 효율적인가에 대한 의문이 들었습니다
다음과 같은 경우에 CTE를 선택하세요.
•
복잡한 쿼리를 더 읽기 쉽게 만들고 싶습니다.
•
재귀 쿼리를 사용해야 합니다.
다음과 같은 경우 서브 쿼리를 선택하세요.
•
WHERE 절 IN 키워드 또는 EXISTS를 사용하여 다른 선택 기준을 선택하고 있습니다.
•
다른 테이블의 단일 데이터 조각을 UPDATE 문의 필드에 대한 새 값으로 선택하려고 합니다.
•
CTE는 결과를 메모리나 디스크에 캐시하지 않고 매번 재계산할 수도 있습니다.