MySQL 서버는 서브 쿼리를 최적으로 실행하지 못할 때가 많다. FROM절에 사용되는 서브쿼리나 WHERE 절의 IN(subquery)구문은 효율적이지 않을 수도 있다.
상관서브쿼리(Correlated subquery)
Subquery 외부에서 정의된 테이블의 컬럼을 참조해서 검색을 수행할 때 상관 서브 쿼리라고 한다. 상관 서브 쿼리는 독립적으로 실행되지 못하고 항상 외부 쿼리가 실행된 후 그 결과값이 전달돼야만 서브쿼리가 실행될 수 있다. 다음 예제에서 EXISTS 이하의 서브 쿼리에서는 dept_emp 테이블에서 지정된 기간 내에 부서가 변경된 사원을 검색하고 있다. 상관 서브 쿼리는 외부 쿼리보다 먼저 실행되지 못하기 때문에 일반적으로 상관 서브 쿼리를 포함하는 비교 조건은 범위 제한 조건이 아니라 체크 조건으로 사용된다.
explain format=tree
select *
from employees e
where exists (select 1
from dept_emp de
where de.emp_no = e.emp_no
and de.from_date between '2000-01-01' and '2011-12-30');
MySQL8.0.20 에서는 서브쿼리가 효율적으로 최적화되어 실행된다. 서브쿼리가 먼저 드라이빙 테이블로 index range scan으로 실행되고 드리븐테이블인 employees의 pk로 조인되어 실행된다.
독립서브쿼리(Self-Contained subquery)
다음 예제 쿼리와 같이 외부쿼리의 컬럼을 사용하지 않고 서브 쿼리에서 정의된 컬럼만 참조한다. 외부의 쿼리와 상관없이 항상 같은 결과를 반환하므로 외부 쿼리보다 먼저 실행되어 외부 쿼리의 검색을 위한 상수로 사용되는 것이 일반적이다. 독립 서브 쿼리가 포함된 비교 조건은 범위 제한 조건으로 사용될 수 있다.
select de.dept_no, de.emp_no
from dept_emp de
where de.emp_no=(select e.emp_no
from employees e
where e.first_name= 'Georgi' and e.last_name = 'Facello'
limit 1);
1. 서브 쿼리의 제약 사항
1) 서브 쿼리는 대부분의 문장에서 사용할 수 있지만 LIMIT 절과 LOAD DATA INFILE의 파일명에는 사용할 수 없다. 서브 쿼리를 IN 연산자와 함께 사용할 때에는 효율적으로 처리되지 못한다.
select *
from employees e
where e.emp_no in (select de.emp_no
from dept_emp de
where de.from_date between '2000-01-01' and '2011-12-30');
MySQL8.0에서는 효율적으로 처리된다.
2) IN/ALL/ANY/SOME 서브 쿼리에는 LIMIT를 사용할 수 없다.
select count(*) -- 10,485
from employees e
where exists (select 1
from dept_emp de
where de.emp_no = e.emp_no
and de.from_date between '2000-01-01' and '2011-12-30');
select count(*) -- 10,485
from employees e
where exists (select 1
from dept_emp de
where de.emp_no = e.emp_no
and de.from_date between '2000-01-01' and '2011-12-30'
limit 1); /* LIMIT을 사용해도 결과는 동일하다. */
select *
from employees e
where e.emp_no in (select de.emp_no
from dept_emp de
where de.from_date between '2000-01-01' and '2011-12-30'
limit 10);
SQL Error [1235] [42000]: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
MySQL8.0에서는 IN/ALL/ANY/SOME 서브쿼리에서 LIMIT을 지원하지 않는다.
3) 서브 쿼리를 이용해 하나의 테이블에 대해 읽고 쓰기를 동시에 할 수 없다.
UPDATE departments2
SET dept_name=(SELECT concat(dept_name,'2') FROM departments2 WHERE dept_no='d009')
WHERE dept_no='d001';
SQL Error [1093] [HY000]: You can't specify target table 'departments2' for update in FROM clause
서브쿼리를 이용해 departments 테이블을 읽고, 조회된 값을 다시 departments 테이블에 업데이트하는 쿼리다. 실제 읽는 레코드와 변경하는 레코드는 다른 레코드이지만 MySQL의 현재 모든 버전에서는 허용하지 않는다.
하지만 departments 테이블을 읽는 서브 쿼리의 결과를 임시 테이블로 저장하도록 쿼리를 변경하여 MySQL 서버는 임시 테이블을 읽어서 departments 테이블을 변경하는 것으로 인식하기 때문에 문제없이 처리된다. 하지만 이러한 방식은 별도의 임시 테이블을 필요로하기에 피할 수 없는 경우에만 사용할 것을 권장한다. 또한 이러한 쿼리는 데드락의 원인이 되기도 한다.
UPDATE departments2
SET dept_name=
(SELECT dept_name FROM (SELECT concat(dept_name,'2') FROM departments2 WHERE dept_no='d009') temp )
WHERE dept_no='d001';
2. SELECT 절에 사용된 서브 쿼리
스칼라 서브 쿼리는 항상 컬럼과 레코드가 하나인 결과를 반환해야 한다. 그 값이 NULL이든 아니든 상관없이 하나의 컬럼으로 레코드 1건이 존재해야한다.
select emp_no, (select dept_name from departments where dept_name='Sales1')
from dept_emp
limit 10;
select emp_no, (select dept_name from departments)
from dept_emp limit 10;
SQL Error [1242] [21000]: Subquery returns more than 1 row
select emp_no, (select dept_no, dept_name from departments where dept_name = 'Sales1')
from dept_emp limit 10;
SQL Error [1241] [21000]: Operand should contain 1 column(s)
3. 서브 쿼리를 조인형태로 변환
바깥 쪽 테이블(dept)과 서브쿼리테이블(emp)의 관계가 1:M인 경우는 조인형태로 변환 시 결과의 건수가 늘어나기 때문에 GROUP BY와 같은 처리가 필요하다.
select d.deptno, d.dname, d.loc
from dept d
where exists (select 1 from emp e where e.deptno=d.deptno);
select d.deptno, d.dname, d.loc
from dept d inner join emp e
on d.deptno = e.deptno
group by d.deptno, d.dname, d.loc;
4. WHERE 절에 NOT IN과 함께 사용된 서브 쿼리 - NOT IN (subquery)
IN(subquery) 형태의 쿼리는 MySQL 옵티마이저가 exists 패턴으로 변형해서 실행한다. 마찬가지로 NOT IN(subquery) 형태의 쿼리는 NOT EXISTS 형태의 구문으로 변환해서 실행한다.
SQL 표준에서는 NULL을 "알 수 없는 값"으로 정의한다. MySQL에서는 d.deptno가 NULL이면 다음 두가지 중 어떤 경우인지를 비교하는 작업을 수행하게 된다.
- 서브 쿼리가 결과 레코드를 한 건이라도 가진다면
NULL IN (레코드를 가지는 결과) => NULL
- 서브 쿼리가 결과 레코드를 한 건도 가지지 않는다면
NULL IN (빈 결과) => FASLE
MySQL에서 NOT IN(subquery) 형태의 최적화는 왼쪽의 값이 NULL인지 아닌지에 따라 exists로 최적화를 적용할지 말지가 결정된다. 왼쪽 값이 NULL이라면 최적화하지 못하고 NOT IN연산자의 오른쪽에 위치한 서브 쿼리가 결과를 한 건이라도 가지는지 판단해야 한다.
NOT IN(subquery)에서 왼쪽의 값이 NULL이 되면 서브 쿼리는 항상 풀 테이블 스캔으로 처리되는데 이 때 서브쿼리가 자체적인 조건을 가지고 있으면서 테이블의 건수가 많다면 상당히 많은 시간이 소요 될 수도 있다.
MySQL의 옵티마이저가 NULL에 대한 고려 없이 최적화 할 수 있으려면 가급적 컬럼이 NULL값을 가지지 않게 NOT NULL 옵션을 사용하는 것이 좋다.
select * from dept1;
select * from emp1;
create index ix_deptno on dept1(deptno);
select d.*
from dept1 d
where deptno not in (select deptno from emp1);
이경우는 양쪽 컬럼의 데이터에 NULL이 존재하여 결과가 없다. 서브쿼리에 deptno is not null 조건을 추가해야 한다.
select d.*
from dept d
where deptno not in (select deptno from emp);
d.deptno가 PK이어서 최적화 되어 anti join 형태로 풀린다.
select d.*
from dept1 d
where deptno not in (select deptno from emp);
d.deptno가 NULL값을 가지고 있어서 FULL TABLE 스캔을 통해 서브쿼리에서 만족하는 조건이 1건도 없을때 까지 필터링 한다.
select d.*
from dept1 d
where deptno not in (select deptno from emp)
and d.deptno is not null;
d.deptno is not null 조건을 추가하여 index scan을 하게 한다.
IN (subquery)는 INNER JOIN으로 개선하고 NOT IN(subquery)는 LEFT JOIN을 사용한다.
select d.*
from dept1 d left join emp e
on d.deptno = e.deptno
where e.deptno is null
-- and d.deptno is not null /* dept1에는 원래 NULL 데이터 존재, NULL데이터 제외하는 조건 추가*/
;
5. FROM 절에 사용된 서브 쿼리
FROM절에 사용된 서브쿼리가 최적화 되지 못하고 임시 테이블을 사용한다면 조인의 형태로 최적화 해야 한다.
[임시 테이블 관련 상태 변수]
show status like 'created_tmp%';
select sql_no_cache *
from (select * from employees where emp_no in (10001,10002,10100,10201)) y;
MySQL8.0에서는 임시테이블을 사용하지 않는다.
'MySQL > MySQL' 카테고리의 다른 글
| INSERT INTO .. ON DUPLICATE KEY UPDATE.. (0) | 2024.12.10 |
|---|---|
| AUTO_INCREMENT (0) | 2024.12.10 |
| GROUP BY & ORDER BY (0) | 2024.12.09 |
| JOIN (0) | 2024.12.09 |
| LIMIT (0) | 2024.12.09 |