[테스트 환경]
2. NULL값이 존재하지 않을 때, LEFT OUTER JOIN과 SUBQUERY의 속도 차이 및 실행계획 분석
OS : CentOS7
DB : MariaDB 10.1.12
1. 테이블 데이터 건수
- EMP 테이블의 건수를 10만 건으로 했을 때, LFET OUTER JOIN과 스칼라 서브쿼리의 소요 시간이 차이가 없어서 160만 건으로 진행한다.
MariaDB [DA_DB]> SELECT COUNT(*) FROM DEPT; +----------+ | COUNT(*) | +----------+ | 204 | +----------+ 1 row in set (0.01 sec) MariaDB [DA_DB]> SELECT COUNT(*) FROM EMP; +----------+ | COUNT(*) | +----------+ | 1600000 | +----------+ 1 row in set (0.31 sec)
2. NULL값이 존재하지 않을 때, LEFT OUTER JOIN과 SUBQUERY의 속도 차이 및 실행계획 분석
- A 테이블을 액세스 하는 단계에서는 소요 시간이 별로 차이가 나지 않았지만, DEPT 테이블을 액세스 하는 단계에서 서브쿼리 캐싱 효과가 발생해서 더 적은 시간이 소요된 것을 확인할 수 있다.
#1. FK 제약조건이 존재하지 않을 때
-- 스칼라 서브쿼리
MariaDB [DA_DB]> ANALYZE FORMAT=JSON
-> SELECT A.EMPNO
-> ,A.ENAME
-> ,(SELECT S1.DNAME
-> FROM DEPT S1
-> WHERE S1.DEPTNO = A.DEPTNO) AS DNAME
-> FROM EMP A;
+-------------------------------------------------------------------------------+
| ANALYZE |
+-------------------------------------------------------------------------------+
| {
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 877.95,
"table": {
"table_name": "A",
"access_type": "ALL",
"r_loops": 1,
"rows": 100000,
"r_rows": 1.6e6,
"r_total_time_ms": 409.75,
"filtered": 100,
"r_filtered": 100
},
"subqueries": [
{
"expression_cache": {
"r_loops": 1600000,
"r_hit_ratio": 99.987,
"query_block": {
"select_id": 2,
"r_loops": 200,
"r_total_time_ms": 1.3305,
"table": {
"table_name": "S1",
"access_type": "eq_ref",
"possible_keys": ["PRIMARY"],
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["DEPTNO"],
"ref": ["DA_DB.A.DEPTNO"],
"r_loops": 200,
"rows": 1,
"r_rows": 1,
"r_total_time_ms": 0.6481,
"filtered": 100,
"r_filtered": 100
}
}
}
}
]
}
} |
+-------------------------------------------------------------------------------+
1 row in set (0.88 sec)
-- LEFT OUTER JOIN
MariaDB [DA_DB]> ANALYZE FORMAT=JSON
-> SELECT A.EMPNO
-> ,A.ENAME
-> ,B.DNAME
-> FROM EMP A
-> LEFT OUTER JOIN DEPT B ON(B.DEPTNO = A.DEPTNO);
+-------------------------------------------------------------------------------+
| ANALYZE |
+-------------------------------------------------------------------------------+
| {
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 1130.8,
"const_condition": "1",
"table": {
"table_name": "A",
"access_type": "ALL",
"r_loops": 1,
"rows": 100000,
"r_rows": 1.6e6,
"r_total_time_ms": 383.27,
"filtered": 100,
"r_filtered": 100
},
"table": {
"table_name": "B",
"access_type": "eq_ref",
"possible_keys": ["PRIMARY"],
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["DEPTNO"],
"ref": ["DA_DB.A.DEPTNO"],
"r_loops": 1600000,
"rows": 1,
"r_rows": 1,
"r_total_time_ms": 405.35,
"filtered": 100,
"r_filtered": 100,
"attached_condition": "trigcond(trigcond((A.DEPTNO is not null)))"
}
}
} |
+-------------------------------------------------------------------------------+
1 row in set (1.13 sec)
3. FK 제약조건 존재
- 제약조건의 유무가 소요시간에 영향을 미치지 않는다.
-- FK 제약조건 생성
ALTER TABLE EMP ADD CONSTRAINT EMP_DEPTNO_FK FOREIGN KEY(DEPTNO)
REFERENCES DEPT(DEPTNO) ;
-- 스칼라 서브쿼리
MariaDB [DA_DB]> ANALYZE FORMAT=JSON
-> SELECT A.EMPNO
-> ,A.ENAME
-> ,(SELECT S1.DNAME
-> FROM DEPT S1
-> WHERE S1.DEPTNO = A.DEPTNO) AS DNAME
-> FROM EMP A;
+-------------------------------------------------------------------------------+
| ANALYZE |
+-------------------------------------------------------------------------------+
| {
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 852.96,
"table": {
"table_name": "A",
"access_type": "ALL",
"r_loops": 1,
"rows": 100000,
"r_rows": 1.6e6,
"r_total_time_ms": 400.29,
"filtered": 100,
"r_filtered": 100
},
"subqueries": [
{
"expression_cache": {
"r_loops": 1600000,
"r_hit_ratio": 99.987,
"query_block": {
"select_id": 2,
"r_loops": 200,
"r_total_time_ms": 1.1185,
"table": {
"table_name": "S1",
"access_type": "eq_ref",
"possible_keys": ["PRIMARY"],
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["DEPTNO"],
"ref": ["DA_DB.A.DEPTNO"],
"r_loops": 200,
"rows": 1,
"r_rows": 1,
"r_total_time_ms": 0.4271,
"filtered": 100,
"r_filtered": 100
}
}
}
}
]
}
} |
+-------------------------------------------------------------------------------+
1 row in set (0.85 sec)
-- LEFT OUTER JOIN
MariaDB [DA_DB]> ANALYZE FORMAT=JSON
-> SELECT A.EMPNO
-> ,A.ENAME
-> ,B.DNAME
-> FROM EMP A
-> LEFT OUTER JOIN DEPT B ON(B.DEPTNO = A.DEPTNO);
+-------------------------------------------------------------------------------+
| ANALYZE |
+-------------------------------------------------------------------------------+
| {
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 1150.4,
"const_condition": "1",
"table": {
"table_name": "A",
"access_type": "ALL",
"r_loops": 1,
"rows": 100000,
"r_rows": 1.6e6,
"r_total_time_ms": 392.4,
"filtered": 100,
"r_filtered": 100
},
"table": {
"table_name": "B",
"access_type": "eq_ref",
"possible_keys": ["PRIMARY"],
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["DEPTNO"],
"ref": ["DA_DB.A.DEPTNO"],
"r_loops": 1600000,
"rows": 1,
"r_rows": 1,
"r_total_time_ms": 410.68,
"filtered": 100,
"r_filtered": 100,
"attached_condition": "trigcond(trigcond((A.DEPTNO is not null)))"
}
}
} |
+-------------------------------------------------------------------------------+
1 row in set (1.15 sec)
4. NOT NULL 제약조건 존재
- 실제 데이터에 NULL값이 존재하지 않아도, MariaDB에서 확신을 할 수 없을 때, "attached_condition": "trigcond(trigcond((A.DEPTNO is not null)))" 조건으로 EMP.DEPTNO가 NOT NULL인 데이터만 LEFT OUTER JOIN을 시도한다.
- NOT NULL 제약조건이 걸린 컬럼에 대해서 LEFT OUTER JOIN을 사용했을 경우, MariaDB가 INNER JOIN으로 변환하여 작업한다.
ALTER TABLE EMP MODIFY COLUMN DEPTNO INT(4) NOT NULL;
-- [번외] NOT NULL 지정 시 DEFAULT의 유무에 관계없이 소요시간이 비슷하다.
MariaDB [DA_DB]> ALTER TABLE EMP MODIFY COLUMN DEPTNO INT(4) NOT NULL DEFAULT 10;
Query OK, 0 rows affected (1 min 10.22 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [DA_DB]> ALTER TABLE EMP MODIFY COLUMN DEPTNO INT(4) NULL ;
Query OK, 0 rows affected (1 min 18.83 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [DA_DB]> ALTER TABLE EMP MODIFY COLUMN DEPTNO INT(4) NOT NULL ;
Query OK, 0 rows affected (1 min 20.98 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- 스칼라 서브쿼리
MariaDB [DA_DB]> ANALYZE FORMAT=JSON
-> SELECT A.EMPNO
-> ,A.ENAME
-> ,(SELECT S1.DNAME
-> FROM DEPT S1
-> WHERE S1.DEPTNO = A.DEPTNO) AS DNAME
-> FROM EMP A ;
+-------------------------------------------------------------------------------+
| ANALYZE |
+-------------------------------------------------------------------------------+
| {
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 865.14,
"table": {
"table_name": "A",
"access_type": "ALL",
"r_loops": 1,
"rows": 100000,
"r_rows": 1.6e6,
"r_total_time_ms": 411.44,
"filtered": 100,
"r_filtered": 100
},
"subqueries": [
{
"expression_cache": {
"r_loops": 1600000,
"r_hit_ratio": 99.987,
"query_block": {
"select_id": 2,
"r_loops": 200,
"r_total_time_ms": 7.739,
"table": {
"table_name": "S1",
"access_type": "eq_ref",
"possible_keys": ["PRIMARY"],
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["DEPTNO"],
"ref": ["DA_DB.A.DEPTNO"],
"r_loops": 200,
"rows": 1,
"r_rows": 1,
"r_total_time_ms": 7.1696,
"filtered": 100,
"r_filtered": 100
}
}
}
}
]
}
} |
+-------------------------------------------------------------------------------+
1 row in set (0.90 sec)
-- LEFT OUTER JOIN
MariaDB [DA_DB]> ANALYZE FORMAT=JSON
-> SELECT A.EMPNO
-> ,A.ENAME
-> ,B.DNAME
-> FROM EMP A
-> LEFT OUTER JOIN DEPT B ON(B.DEPTNO = A.DEPTNO);
+-------------------------------------------------------------------------------+
| ANALYZE |
+-------------------------------------------------------------------------------+
| {
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 1132.6,
"const_condition": "1",
"table": {
"table_name": "A",
"access_type": "ALL",
"r_loops": 1,
"rows": 100000,
"r_rows": 1.6e6,
"r_total_time_ms": 404.58,
"filtered": 100,
"r_filtered": 100
},
"table": {
"table_name": "B",
"access_type": "eq_ref",
"possible_keys": ["PRIMARY"],
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["DEPTNO"],
"ref": ["DA_DB.A.DEPTNO"],
"r_loops": 1600000,
"rows": 1,
"r_rows": 1,
"r_total_time_ms": 422.72,
"filtered": 100,
"r_filtered": 100
}
}
} |
+-------------------------------------------------------------------------------+
1 row in set (1.13 sec)
5. NULL값이 존재할 때, LEFT OUTER JOIN과 SUBQUERY의 속도 차이 및 실행계획 분석
- 총 160만 건 중, 40만 건이 DEPTNO가 NULL로 설정되었다. 그래서 LEFT OUTER JOIN에서 DEPT테이블을 액세스 하는데 r_rows가 0.75로 나타났다.(DEPT에 실제 액세스한 로우 수 = 전체 로우 수 - NULL 데이터 건 수 / 전체 데이터 건 수 =1 - 40만 건/160만 건)
- NULL의 비율을 증가시켜도 소요시간엔 큰 영향이 없다.
-- NOT NULL 제약조건 제거
MariaDB [DA_DB]> ALTER TABLE EMP MODIFY COLUMN DEPTNO INT(4) NULL ;
Query OK, 0 rows affected (1 min 4.42 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- NULL의 비율을 25%로 업데이트
MariaDB [DA_DB]> UPDATE EMP
-> SET DEPTNO = NULL
-> WHERE MOD(EMPNO, 4) = 0;
Query OK, 400000 rows affected (17.77 sec)
Rows matched: 400000 Changed: 400000 Warnings: 0
MariaDB [DA_DB]> SELECT COUNT(*) FROM EMP WHERE DEPTNO IS NULL;
+----------+
| COUNT(*) |
+----------+
| 400000 |
+----------+
1 row in set (0.12 sec)
-- 스칼라 서브쿼리
MariaDB [DA_DB]> ANALYZE FORMAT=JSON
-> SELECT A.EMPNO
-> ,A.ENAME
-> ,(SELECT S1.DNAME
-> FROM DEPT S1
-> WHERE S1.DEPTNO = A.DEPTNO) AS DNAME
-> FROM EMP A;
+-------------------------------------------------------------------------------+
| ANALYZE |
+-------------------------------------------------------------------------------+
| {
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 933.3,
"table": {
"table_name": "A",
"access_type": "ALL",
"r_loops": 1,
"rows": 100000,
"r_rows": 1.6e6,
"r_total_time_ms": 452.55,
"filtered": 100,
"r_filtered": 100
},
"subqueries": [
{
"expression_cache": {
"r_loops": 1600000,
"r_hit_ratio": 99.987,
"query_block": {
"select_id": 2,
"r_loops": 201,
"r_total_time_ms": 12.372,
"table": {
"table_name": "S1",
"access_type": "eq_ref",
"possible_keys": ["PRIMARY"],
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["DEPTNO"],
"ref": ["DA_DB.A.DEPTNO"],
"r_loops": 200,
"rows": 1,
"r_rows": 1,
"r_total_time_ms": 11.933,
"filtered": 100,
"r_filtered": 100
}
}
}
}
]
}
} |
+-------------------------------------------------------------------------------+
1 row in set (1.04 sec)
-- LEFT OUTER JOIN
MariaDB [DA_DB]> ANALYZE FORMAT=JSON
-> SELECT A.EMPNO
-> ,A.ENAME
-> ,B.DNAME
-> FROM EMP A
-> LEFT OUTER JOIN DEPT B ON(B.DEPTNO = A.DEPTNO);
+-------------------------------------------------------------------------------+
| ANALYZE |
+-------------------------------------------------------------------------------+
| {
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 1065.9,
"const_condition": "1",
"table": {
"table_name": "A",
"access_type": "ALL",
"r_loops": 1,
"rows": 100000,
"r_rows": 1.6e6,
"r_total_time_ms": 404.32,
"filtered": 100,
"r_filtered": 100
},
"table": {
"table_name": "B",
"access_type": "eq_ref",
"possible_keys": ["PRIMARY"],
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["DEPTNO"],
"ref": ["DA_DB.A.DEPTNO"],
"r_loops": 1600000,
"rows": 1,
"r_rows": 0.75,
"r_total_time_ms": 341.92,
"filtered": 100,
"r_filtered": 100,
"attached_condition": "trigcond(trigcond((A.DEPTNO is not null)))"
}
}
} |
+-------------------------------------------------------------------------------+
1 row in set (1.06 sec)
-- NULL의 비율을 50%로 업데이트
MariaDB [DA_DB]> UPDATE EMP
-> SET DEPTNO = NULL
-> WHERE MOD(EMPNO, 2) = 0;
Query OK, 400000 rows affected (16.32 sec)
Rows matched: 800000 Changed: 400000 Warnings: 0
MariaDB [DA_DB]> SELECT COUNT(*) FROM EMP WHERE DEPTNO IS NULL;
+----------+
| COUNT(*) |
+----------+
| 800000 |
+----------+
1 row in set (0.21 sec)
-- 스칼라 서브쿼리
MariaDB [DA_DB]> ANALYZE FORMAT=JSON
-> SELECT A.EMPNO
-> ,A.ENAME
-> ,(SELECT S1.DNAME
-> FROM DEPT S1
-> WHERE S1.DEPTNO = A.DEPTNO) AS DNAME
-> FROM EMP A;
+-------------------------------------------------------------------------------+
| ANALYZE |
+-------------------------------------------------------------------------------+
| {
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 889.29,
"table": {
"table_name": "A",
"access_type": "ALL",
"r_loops": 1,
"rows": 100000,
"r_rows": 1.6e6,
"r_total_time_ms": 413.73,
"filtered": 100,
"r_filtered": 100
},
"subqueries": [
{
"expression_cache": {
"r_loops": 1600000,
"r_hit_ratio": 99.987,
"query_block": {
"select_id": 2,
"r_loops": 201,
"r_total_time_ms": 1.2998,
"table": {
"table_name": "S1",
"access_type": "eq_ref",
"possible_keys": ["PRIMARY"],
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["DEPTNO"],
"ref": ["DA_DB.A.DEPTNO"],
"r_loops": 200,
"rows": 1,
"r_rows": 1,
"r_total_time_ms": 0.514,
"filtered": 100,
"r_filtered": 100
}
}
}
}
]
}
} |
+-------------------------------------------------------------------------------+
1 row in set (0.89 sec)
-- LEFT OUTER JOIN
MariaDB [DA_DB]> ANALYZE FORMAT=JSON
-> SELECT A.EMPNO
-> ,A.ENAME
-> ,B.DNAME
-> FROM EMP A
-> LEFT OUTER JOIN DEPT B ON(B.DEPTNO = A.DEPTNO);
+-------------------------------------------------------------------------------+
| ANALYZE |
+-------------------------------------------------------------------------------+
| {
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 1065.5,
"const_condition": "1",
"table": {
"table_name": "A",
"access_type": "ALL",
"r_loops": 1,
"rows": 100000,
"r_rows": 1.6e6,
"r_total_time_ms": 392.75,
"filtered": 100,
"r_filtered": 100
},
"table": {
"table_name": "B",
"access_type": "eq_ref",
"possible_keys": ["PRIMARY"],
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["DEPTNO"],
"ref": ["DA_DB.A.DEPTNO"],
"r_loops": 1600000,
"rows": 1,
"r_rows": 0.5,
"r_total_time_ms": 368.07,
"filtered": 100,
"r_filtered": 100,
"attached_condition": "trigcond(trigcond((A.DEPTNO is not null)))"
}
}
} |
+-------------------------------------------------------------------------------+
1 row in set (1.06 sec)
6. 스칼라 서브쿼리 테이블/LEFT OUTER JOIN에서 DRIVEN 테이블의 데이터 건수가 많을 때
- 스칼라 서브쿼리 테이블/LEFT OUTER JOIN에서 DRIVEN 테이블의 데이터 건수가 많을 때는 캐싱효과(r_hit_ratio/r_loops)와 상관없이 소요시간이 거의 동일하다.
MariaDB [DA_DB]> ANALYZE FORMAT=JSON
-> SELECT A.EMPNO
-> ,A.ENAME
-> ,(SELECT S1.ENAME
-> FROM EMP S1
-> WHERE S1.EMPNO = A.MGR) AS MANAGER_NAME
-> FROM EMP A;
+-------------------------------------------------------------------------------+
| ANALYZE |
+-------------------------------------------------------------------------------+
| {
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 718.15,
"table": {
"table_name": "A",
"access_type": "ALL",
"r_loops": 1,
"rows": 100000,
"r_rows": 1.6e6,
"r_total_time_ms": 397.81,
"filtered": 100,
"r_filtered": 100
},
"subqueries": [
{
"expression_cache": {
"r_loops": 1600000,
"r_hit_ratio": 99.999,
"query_block": {
"select_id": 2,
"r_loops": 11,
"r_total_time_ms": 0.1686,
"table": {
"table_name": "S1",
"access_type": "eq_ref",
"possible_keys": ["PRIMARY"],
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["EMPNO"],
"ref": ["DA_DB.A.MGR"],
"r_loops": 10,
"rows": 1,
"r_rows": 1,
"r_total_time_ms": 0.0594,
"filtered": 100,
"r_filtered": 100
}
}
}
}
]
}
} |
+-------------------------------------------------------------------------------+
1 row in set (0.75 sec)
MariaDB [DA_DB]> ANALYZE FORMAT=JSON
-> SELECT A.EMPNO
-> ,A.ENAME
-> ,B.ENAME AS MANAGER_NAME
-> FROM EMP A
-> LEFT OUTER JOIN EMP B ON(B.EMPNO = A.MGR);
+-------------------------------------------------------------------------------+
| ANALYZE |
+-------------------------------------------------------------------------------+
| {
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 714.52,
"const_condition": "1",
"table": {
"table_name": "A",
"access_type": "ALL",
"r_loops": 1,
"rows": 100000,
"r_rows": 1.6e6,
"r_total_time_ms": 404.83,
"filtered": 100,
"r_filtered": 100
},
"table": {
"table_name": "B",
"access_type": "eq_ref",
"possible_keys": ["PRIMARY"],
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["EMPNO"],
"ref": ["DA_DB.A.MGR"],
"r_loops": 1600000,
"rows": 1,
"r_rows": 1,
"r_total_time_ms": 0.7399,
"filtered": 100,
"r_filtered": 100,
"attached_condition": "trigcond(trigcond((A.MGR is not null)))"
}
}
} |
+-------------------------------------------------------------------------------+
1 row in set (0.71 sec)
[결론]
- 스칼라 서브쿼리 테이블/ LFTER OUTER JOIN에서 DRIVEN 테이블의 건수가 적을 경우에 LEFT OUTER JOIN 보다 서브쿼리 캐싱효과로 인해 스칼라 서브 쿼리가 성능이 더 좋다. 테이블의 건수가 많을 때는 소요시간이 거의 동일하다.
- JOIN 조건 컬럼에 NOT NULL이 있을 경우 MariaDB 내부적으로 INNER JOIN으로 변경한다.("attached_condition": "trigcond(trigcond((A.DEPTNO is not null)))" 조건이 사라진다.)
'MariaDB > SQL Tuning' 카테고리의 다른 글
| [MariaDB][성능] 서브쿼리 최적화 (0) | 2016.09.25 |
|---|---|
| [MariaDB][성능] LEFT OUTER JOIN과 스칼라 서브쿼리 실행계획 비교2 (0) | 2016.08.28 |
| [MariaDB][성능] 스칼라 서브쿼리 실행계획2 (0) | 2016.08.17 |
| [MariaDB][성능]인라인뷰 처리 방식 분석 (0) | 2016.08.17 |
| [MariaDB][성능] 스칼라 서브쿼리 실행계획 (0) | 2016.08.15 |