[테스트 환경]
OS : CentOS7
DB : MariaDB 10.1.12
EXPLAIN FORMAT = JSON은 MariaDB 10.1.2 버전이상 부터 지원되며, output에 대한 포맷은 정확하게 나와있지 않기 때문에 아래 파악한 내용이 확실하지 않다. ANALYZE FORMAT = JSON 은 EXPLAIN FORMAT = JSON과 ANALYZE 구문의 혼합형으로 EXPLAIN FORMAT = JSON 포맷형식에 쿼리를 실행시킨 데이터(r_loops, r_total_time_ms, r_filtered)가 추가되어 출력된다.
ANALYZE FORMAT=JSON
SELECT A.EMPNO
,A.ENAME
,(SELECT S1.DNAME
FROM DEPT S1
WHERE S1.DEPTNO = A.DEPTNO) AS DNAME
FROM EMP A;
{
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 0.3655,
"table": {
"table_name": "A",
"access_type": "ALL",
"r_loops": 1,
"rows": 14,
"r_rows": 14,
"r_total_time_ms": 0.0607,
"filtered": 100,
"r_filtered": 100
},
"subqueries": [
{
"expression_cache": {
"r_loops": 14,
"r_hit_ratio": 78.571,
"query_block": {
"select_id": 2,
"r_loops": 3,
"r_total_time_ms": 0.0344,
"table": {
"table_name": "S1",
"access_type": "eq_ref",
"possible_keys": ["PRIMARY"],
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["DEPTNO"],
"ref": ["test.A.DEPTNO"],
"r_loops": 3,
"rows": 1,
"r_rows": 1,
"r_total_time_ms": 0.0176,
"filtered": 100,
"r_filtered": 100
}
}
}
}
]
}
}
- query_block은 SELECT 구문이 있는 부분에 발생한다. 여기서는 메인 쿼리와 스칼라 서브쿼리에 SELECT구문이 사용되어 두 개의 query_block이 출력되었다. select_id는 실행된 순서를 의미하지 않으며, 실행계획에 출력된 순서대로 실행된다.[참고 5]
- 첫 번째 query_block은 1번 실행되어 0.3655ms 가 소요되었다,
- EMP 테이블에 대해서 한 번의 Table Full Scan이 발생했으며 14건의 데이터를 출력했다. 조건절이 따로 없어서 테이블 전체 데이터가 출력되어 r_filtered가 100으로 나타나며, 테이블 액세스에 소요된 시간은 0.0607ms 로 첫 번째 query_block에 소요된 시간에 포함된다. 인덱스 액세스가 발생하지 않는 경우에는 possible_keys, key, key_length, used_key_parts가 출력되지 않는다.
- 스칼라 서브쿼리가 사용되어 subqueries가 나타났고, expression_cache는 스칼라 서브쿼리를 캐싱하여 사용했다는 의미로, 두 번째 query_block 만큼 서브쿼리가 실행되고 결과값을 캐싱해서 사용한 것으로 추측된다.(r_hit_ratio가 캐싱된 서브쿼리를 사용한 비율로 [캐싱된 서브쿼리를 사용한 횟수] / [서브 쿼리를 호출한 횟수] * 100 이며, 서브쿼리는 총 14번이 호출되었지만 실제로 ID가 2인 쿼리는 3번만 호출되었으므로, 11번은 캐싱된 쿼리를 사용한 것으로 확인할 수 있다. 따라서 11/14 * 100 = 78.571로 출력되었다.)실제로 메인 쿼리의 데이터가 여러 건 일때만 해당 문구가 출력되고 [참고 1], 메인 쿼리에서 한 건만 출력되도록 조건절을 추가하면 해당 문구가 사라진다. [참고 2]
- 메인 쿼리의 결과 값 만큼 서브쿼리가 실행되었다.(r_loops:14)
- 두 번째 query_block은 서브쿼리의 SELECT를 의미한다.
- 메인 쿼리에서 DEPTNO 값을 10, 20, 30만 가지고 있어서 실제로 3번의 쿼리가 실행되었고 총 소요시간은 0.0344ms이다.
- DEPT는 PK 인덱스로 액세스 되었으며 사용된 컬럼은 DEPTNO이다. DEPTNO의 데이터타입은 int여서 4 bytes를 사용했다. 두 번째 query_block이 실행된 만큼 DEPT에 대한 인덱스 액세스가 실행되어 r_loops가 3이며, 서브쿼리이기 때문에 메인 쿼리의 한 로우당 한 건을 반환한 것을 확인할 수 있다. [참고 3] [참고 4]
[참고 1]
ANALYZE FORMAT=JSON
SELECT A.EMPNO
,A.ENAME
,(SELECT S1.DNAME
FROM DEPT S1
WHERE S1.DEPTNO = A.DEPTNO) AS DNAME
FROM EMP A
WHERE A.EMPNO IN (7369, 7380);
{
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 0.2738,
"table": {
"table_name": "A",
"access_type": "range",
"possible_keys": ["PRIMARY"],
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["EMPNO"],
"r_loops": 1,
"rows": 2,
"r_rows": 1,
"r_total_time_ms": 0.0311,
"filtered": 100,
"r_filtered": 100,
"attached_condition": "(A.EMPNO in (7369,7380))"
},
"subqueries": [
{
"expression_cache": {
"r_loops": 1,
"r_hit_ratio": 0,
"query_block": {
"select_id": 2,
"r_loops": 1,
"r_total_time_ms": 0.0127,
"table": {
"table_name": "S1",
"access_type": "eq_ref",
"possible_keys": ["PRIMARY"],
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["DEPTNO"],
"ref": ["test.A.DEPTNO"],
"r_loops": 1,
"rows": 1,
"r_rows": 1,
"r_total_time_ms": 0.0064,
"filtered": 100,
"r_filtered": 100
}
}
}
}
]
}
}
- 메인 쿼리에 PK 컬럼인 EMP에 대해서 두 건 이상의 로우가 출력되도록 조건절을 추가했을 때, expression_cache가 나타난다.
[참고 2]
ANALYZE FORMAT=JSON
SELECT A.EMPNO
,A.ENAME
,(SELECT S1.DNAME
FROM DEPT S1
WHERE S1.DEPTNO = A.DEPTNO) AS DNAME
FROM EMP A
WHERE A.EMPNO = 7369;
{
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 0.0253,
"table": {
"table_name": "A",
"access_type": "const",
"possible_keys": ["PRIMARY"],
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["EMPNO"],
"ref": ["const"],
"r_loops": 0,
"rows": 1,
"r_rows": null,
"filtered": 100,
"r_filtered": null
},
"subqueries": [
{
"query_block": {
"select_id": 2,
"r_loops": 1,
"r_total_time_ms": 0.0061,
"table": {
"table_name": "S1",
"access_type": "const",
"possible_keys": ["PRIMARY"],
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["DEPTNO"],
"ref": ["const"],
"r_loops": 0,
"rows": 1,
"r_rows": null,
"filtered": 100,
"r_filtered": null
}
}
}
]
}
}
- 메인 쿼리에 PK 컬럼인 EMP에 대해서 한 건의 로우만 출력되도록 조건절을 추가했을 때, expression_cache가 사라진다.
- 한 건만 출력하도록 했을 때, r_filtered값이 null값으로 출력된다. [참고 6]
[참고 3]
-- 데이터 변경
UPDATE EMP
SET DEPTNO = NULL
WHERE EMPNO = 7844;
ANALYZE FORMAT=JSON
SELECT A.EMPNO
,A.ENAME
,(SELECT S1.DNAME
FROM DEPT S1
WHERE S1.DEPTNO = A.DEPTNO) AS DNAME
FROM EMP A;
{
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 0.4101,
"table": {
"table_name": "A",
"access_type": "ALL",
"r_loops": 1,
"rows": 14,
"r_rows": 14,
"r_total_time_ms": 0.1192,
"filtered": 100,
"r_filtered": 100
},
"subqueries": [
{
"expression_cache": {
"r_loops": 14,
"r_hit_ratio": 71.429,
"query_block": {
"select_id": 2,
"r_loops": 4,
"r_total_time_ms": 0.0569,
"table": {
"table_name": "S1",
"access_type": "eq_ref",
"possible_keys": ["PRIMARY"],
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["DEPTNO"],
"ref": ["test.A.DEPTNO"],
"r_loops": 3,
"rows": 1,
"r_rows": 1,
"r_total_time_ms": 0.0304,
"filtered": 100,
"r_filtered": 100
}
}
}
}
]
}
}
- 메인 쿼리에서 사용되는 DEPTNO를 10, 20, 30 에서 NULL을 추가하여 총 4개의 데이터를 출력하도록 변경했을 때, 두 번째 query_block에서 r_loops가 4번이 발생하지만 DEPT테이블에서 DEPTNO에 NULL 데이터가 없기 때문에 DEPT 인덱스 액세스는 3번 발생했다.
[참고 4]
-- 데이터 변경
UPDATE EMP
SET DEPTNO = 40
WHERE EMPNO = 7844 ;
ANALYZE FORMAT=JSON
SELECT A.EMPNO
,A.ENAME
,(SELECT S1.DNAME
FROM DEPT S1
WHERE S1.DEPTNO = A.DEPTNO) AS DNAME
FROM EMP A;
{
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 0.3737,
"table": {
"table_name": "A",
"access_type": "ALL",
"r_loops": 1,
"rows": 14,
"r_rows": 14,
"r_total_time_ms": 0.0489,
"filtered": 100,
"r_filtered": 100
},
"subqueries": [
{
"expression_cache": {
"r_loops": 14,
"r_hit_ratio": 71.429,
"query_block": {
"select_id": 2,
"r_loops": 4,
"r_total_time_ms": 0.0401,
"table": {
"table_name": "S1",
"access_type": "eq_ref",
"possible_keys": ["PRIMARY"],
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["DEPTNO"],
"ref": ["test.A.DEPTNO"],
"r_loops": 4,
"rows": 1,
"r_rows": 1,
"r_total_time_ms": 0.0225,
"filtered": 100,
"r_filtered": 100
}
}
}
}
]
}
}
- 메인 쿼리에서 사용되는 DEPTNO를 10, 20, 30 에서 40을 추가하여 총 4개의 데이터를 출력하도록 변경했을 때, 두 번째 query_block에서 r_loops가 4번이 발생하고 필요한 데이터 만큼 DEPT 인덱스 액세스가 4번 발생했다.
[참고 5]
ANALYZE FORMAT=JSON
SELECT A.EMPNO
,A.ENAME
,(SELECT S1.DNAME
FROM DEPT S1
WHERE S1.DEPTNO = A.DEPTNO) AS DNAME
,(SELECT COUNT(*) AS CNT
FROM EMP S2
WHERE S2.DEPTNO = A.DEPTNO) AS SAME_DEPTNO
FROM EMP A;
{
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 0.4719,
"table": {
"table_name": "A",
"access_type": "ALL",
"r_loops": 1,
"rows": 14,
"r_rows": 14,
"r_total_time_ms": 0.0433,
"filtered": 100,
"r_filtered": 100
},
"subqueries": [
{
"expression_cache": {
"r_loops": 14,
"r_hit_ratio": 78.571,
"query_block": {
"select_id": 3,
"r_loops": 3,
"r_total_time_ms": 0.0982,
"table": {
"table_name": "S2",
"access_type": "ALL",
"r_loops": 3,
"rows": 14,
"r_rows": 14,
"r_total_time_ms": 0.068,
"filtered": 100,
"r_filtered": 33.333,
"attached_condition": "(S2.DEPTNO = A.DEPTNO)"
}
}
}
},
{
"expression_cache": {
"r_loops": 14,
"r_hit_ratio": 78.571,
"query_block": {
"select_id": 2,
"r_loops": 3,
"r_total_time_ms": 0.0397,
"table": {
"table_name": "S1",
"access_type": "eq_ref",
"possible_keys": ["PRIMARY"],
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["DEPTNO"],
"ref": ["test.A.DEPTNO"],
"r_loops": 3,
"rows": 1,
"r_rows": 1,
"r_total_time_ms": 0.0209,
"filtered": 100,
"r_filtered": 100
}
}
}
}
]
}
}
MariaDB [test]> ANALYZE
-> SELECT A.EMPNO
-> ,A.ENAME
-> ,(SELECT S1.DNAME
-> FROM DEPT S1
-> WHERE S1.DEPTNO = A.DEPTNO) AS DNAME
-> ,(SELECT COUNT(*) AS CNT
-> FROM EMP S2
-> WHERE S2.DEPTNO = A.DEPTNO) AS SAME_DEPTNO
-> FROM EMP A;
+------+--------------------+-------+--------+---------------+---------+---------+---------------+------+--------+----------+------------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+--------------------+-------+--------+---------------+---------+---------+---------------+------+--------+----------+------------+-------------+
| 1 | PRIMARY | A | ALL | NULL | NULL | NULL | NULL | 14 | 14.00 | 100.00 | 100.00 | |
| 3 | DEPENDENT SUBQUERY | S2 | ALL | NULL | NULL | NULL | NULL | 14 | 14.00 | 100.00 | 33.33 | Using where |
| 2 | DEPENDENT SUBQUERY | S1 | eq_ref | PRIMARY | PRIMARY | 4 | test.A.DEPTNO | 1 | 1.00 | 100.00 | 100.00 | |
+------+--------------------+-------+--------+---------------+---------+---------+---------------+------+--------+----------+------------+-------------+
3 rows in set (0.01 sec)
- 스칼라 서브쿼리를 두 개 사용했을 때 출력되는 id를 보면 id 순서대로 실행되지 않는 것을 확인할 수 있다.
ANALYZE FORMAT = JSON
SELECT A.*
FROM EMP A
WHERE A.MGR = 7698 ;
{
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 0.0192,
"table": {
"table_name": "A",
"access_type": "ALL",
"r_loops": 1,
"rows": 14,
"r_rows": 14,
"r_total_time_ms": 0.0115,
"filtered": 100,
"r_filtered": 35.714,
"attached_condition": "(A.MGR = 7698)"
}
}
}
-- 인덱스 생성
CREATE INDEX EMP_MGR_IDX01 ON EMP(MGR);
ANALYZE FORMAT = JSON
SELECT A.*
FROM EMP A USE INDEX(EMP_MGR_IDX01)
WHERE A.MGR = 7698 ;
{
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 0.0263,
"table": {
"table_name": "A",
"access_type": "ref",
"possible_keys": ["EMP_MGR_IDX01"],
"key": "EMP_MGR_IDX01",
"key_length": "5",
"used_key_parts": ["MGR"],
"ref": ["const"],
"r_loops": 1,
"rows": 5,
"r_rows": 5,
"r_total_time_ms": 0.0185,
"filtered": 100,
"r_filtered": 100
}
}
}
'MariaDB > SQL Tuning' 카테고리의 다른 글
| [MariaDB][성능] 스칼라 서브쿼리 실행계획2 (0) | 2016.08.17 |
|---|---|
| [MariaDB][성능]인라인뷰 처리 방식 분석 (0) | 2016.08.17 |
| [MariaDB][성능] order by 실행계획 (0) | 2016.08.10 |
| [MariaDB][성능] 실행계획 분석2 (0) | 2016.08.08 |
| [MariaDB][성능] 실행계획 분석[정리필요] (0) | 2016.08.07 |