[테스트 환경]
OS : CentOS7
DB : MariaDB 10.1.12
1. 스칼라 서브쿼리 & LEFT OUTER JOIN
- 메인쿼리의 데이터마다 다른 데이터를 리턴하도록 데이터 수정을 한 후 스칼라 서브쿼리와 LEFT OUTER JOIN의 성능을 비교한다.
- 메인쿼리의 데이터마다 다른 데이터를 호출하는 쿼리를 작성했을 경우, 서브쿼리는 캐싱효과를 사용할 수 없기 때문에 LEFT OUTER JOIN이 성능이 좋다.
- 스칼라 서브쿼리일 때 캐싱할 수 있는 데이터의 개수가 200개가 넘어가면 더이상 캐싱을 하지 않고 상태가 disabled로 바뀐다.
-- MGR 업데이트
UPDATE EMP A
SET A.MGR = CASE WHEN A.EMPNO = 1600000
THEN 1
ELSE A.EMPNO
END + 1;
-- 스칼라 서브쿼리
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;
{
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 2823.1,
"table": {
"table_name": "A",
"access_type": "ALL",
"r_loops": 1,
"rows": 1591810,
"r_rows": 1.6e6,
"r_total_time_ms": 441.11,
"filtered": 100,
"r_filtered": 100
},
"subqueries": [
{
"expression_cache": {
"state": "disabled",
"r_loops": 200,
"r_hit_ratio": 0,
"query_block": {
"select_id": 2,
"r_loops": 1600000,
"r_total_time_ms": 1877.7,
"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": 1600000,
"rows": 1,
"r_rows": 1,
"r_total_time_ms": 1033.7,
"filtered": 100,
"r_filtered": 100
}
}
}
}
]
}
}
-- LEFT OUTER JOIN
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);
{
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 1788.5,
"const_condition": "1",
"table": {
"table_name": "A",
"access_type": "ALL",
"r_loops": 1,
"rows": 1591810,
"r_rows": 1.6e6,
"r_total_time_ms": 421.34,
"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": 1001.8,
"filtered": 100,
"r_filtered": 100,
"attached_condition": "trigcond(trigcond((A.MGR is not null)))"
}
}
}
2. 캐싱효과 확인
- DEPTNO를 204개 전체로 했을 때 캐싱효과가 발생하는지 확인한다.
- EMP의 데이터를 액세스 할 때, 처음 200건의 데이터 중, DEPTNO가 중복되어 캐싱된 데이터를 사용할 수 있기 때문에 캐싱효과가 발생한 것으로 보인다. (메인쿼리의 데이터를 몇 건 정도 돌렸을 때 r_hit_ratio이 낮으면 서브쿼리 캐싱을 disable 시키는 것으로 보여진다.)
-- 랜덤으로 DEPTNO 업데이트
UPDATE EMP A
SET A.DEPTNO = (SELECT S.DEPTNO FROM DEPT S ORDER BY RAND() LIMIT 1);
SELECT COUNT(A.CNT)
FROM (SELECT COUNT(*) AS CNT
FROM EMP
GROUP BY DEPTNO)A;
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": 983.14,
"table": {
"table_name": "A",
"access_type": "ALL",
"r_loops": 1,
"rows": 1607429,
"r_rows": 1.6e6,
"r_total_time_ms": 404.35,
"filtered": 100,
"r_filtered": 100
},
"subqueries": [
{
"expression_cache": {
"r_loops": 1600000,
"r_hit_ratio": 99.987,
"query_block": {
"select_id": 2,
"r_loops": 204,
"r_total_time_ms": 1.2702,
"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": 204,
"rows": 1,
"r_rows": 1,
"r_total_time_ms": 1.0955,
"filtered": 100,
"r_filtered": 100
}
}
}
}
]
}
}
3. DEPTNO 중복에 따른 실행 계획
- 메인쿼리의 DEPTNO 중복값이 하나 차이지만 위의 쿼리는 캐싱효과가 나타나고 아래쿼리는 캐싱이 disabled 되었다.
-- 메인쿼리의 DEPTNO 중복값이 204개일 때
{
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 964.98,
"table": {
"table_name": "A",
"access_type": "ALL",
"r_loops": 1,
"rows": 1600000,
"r_rows": 1.6e6,
"r_total_time_ms": 414.05,
"filtered": 100,
"r_filtered": 100
},
"subqueries": [
{
"expression_cache": {
"r_loops": 1600000,
"r_hit_ratio": 99.987,
"query_block": {
"select_id": 2,
"r_loops": 204,
"r_total_time_ms": 0.385,
"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": 204,
"rows": 1,
"r_rows": 1,
"r_total_time_ms": 0.2254,
"filtered": 100,
"r_filtered": 100
}
}
}
}
]
}
}
-- 메인쿼리의 DEPTNO 중복값이 205개일 때
{
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 2542.6,
"table": {
"table_name": "A",
"access_type": "ALL",
"r_loops": 1,
"rows": 1600000,
"r_rows": 1.6e6,
"r_total_time_ms": 443.32,
"filtered": 100,
"r_filtered": 100
},
"subqueries": [
{
"expression_cache": {
"state": "disabled",
"r_loops": 205,
"r_hit_ratio": 2.439,
"query_block": {
"select_id": 2,
"r_loops": 1599995,
"r_total_time_ms": 1589.8,
"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": 1599995,
"rows": 1,
"r_rows": 1,
"r_total_time_ms": 852.03,
"filtered": 100,
"r_filtered": 100
}
}
}
}
]
}
}
4. EMP 활용한 스칼라 서브쿼리
- EMP의 처음 204개의 데이터에 대해서 다른 DEPTNO를 가지도록 데이터 수정
- 스칼라 서브쿼리에서건이 넘어갈 경우 LEFT OUTER JOIN이 유리하다.
- 처음 액세스하는 메인쿼리의 데이터 200건에서 캐싱된 데이터를 사용할 수 없을 경우, MariaDB는 서브쿼리 캐싱을 중단한다.
-- DEPTNO 업데이트
UPDATE EMP A
SET A.DEPTNO = A.EMPNO * 10
WHERE A.EMPNO BETWEEN 1 AND 204 ;
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": 2555,
"table": {
"table_name": "A",
"access_type": "ALL",
"r_loops": 1,
"rows": 1607429,
"r_rows": 1.6e6,
"r_total_time_ms": 448.26,
"filtered": 100,
"r_filtered": 100
},
"subqueries": [
{
"expression_cache": {
"state": "disabled",
"r_loops": 200,
"r_hit_ratio": 0,
"query_block": {
"select_id": 2,
"r_loops": 1600000,
"r_total_time_ms": 1593.2,
"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": 1600000,
"rows": 1,
"r_rows": 1,
"r_total_time_ms": 845.67,
"filtered": 100,
"r_filtered": 100
}
}
}
}
]
}
}
5. 데이터 정렬 성능 체크
- 데이터의 정렬이 성능에 영향을 미치는지 확인
- 각 테이블의 DEPTNO 컬럼과 IDX03 인덱스에 대해서 통계정보를 수집한다.
- 인덱스 통계정보 테이블에서(index_stats) 인덱스 컬럼 순서(prefix_arity)를 보면 DEPTNO에 대해서만 인덱스를 생성했는데 2의 값이 나타난다. 이는 MariaDB의 인덱스는 인덱스 컬럼 + PK 컬럼으로 EMPNO가 추가된 것을 확인할 수 있다.
- avg_frequency는 중복 값을 가진 데이터 수로 204개의 DEPTNO에 대한 데이터를 가진 EMPNO의 수이다.(7843.1373 * 204 = 1600000.0092)
-- EMP 테이블에서 DEPTNO를 정렬하여 EMP_OB 테이블 생성 CREATE TABLE EMP_OB AS SELECT A.* FROM EMP A ORDER BY A.DEPTNO ; -- PRIMARY KEY 생성 ALTER TABLE EMP_OB ADD CONSTRAINT PRIMARY KEY (EMPNO); -- EMP_OB 테이블에 DEPTNO 인덱스 생성 CREATE INDEX EMP_OB_DEPT_IDX03 ON EMP_OB(DEPTNO); -- EMP 테이블에 DEPTNO 인덱스 생성 CREATE INDEX EMP_DEPT_IDX03 ON EMP(DEPTNO); -- 통계정보 수집 MariaDB [(none)]> set global use_stat_tables = 'preferably'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show global variables like 'use_stat_tables'; +-----------------+------------+ | Variable_name | Value | +-----------------+------------+ | use_stat_tables | PREFERABLY | +-----------------+------------+ 1 row in set (0.00 sec) MariaDB [(none)]> analyze table DA_DB.EMP_OB PERSISTENT FOR COLUMNS(DEPTNO) INDEXES(EMP_OB_DEPT_IDX03); +--------------+---------+----------+-----------------------------------------+ | Table | Op | Msg_type | Msg_text | +--------------+---------+----------+-----------------------------------------+ | DA_DB.EMP_OB | analyze | status | Engine-independent statistics collected | | DA_DB.EMP_OB | analyze | status | OK | +--------------+---------+----------+-----------------------------------------+ 2 rows in set (1.52 sec) MariaDB [mysql]> analyze table DA_DB.EMP PERSISTENT FOR COLUMNS(DEPTNO) INDEXES(EMP_DEPT_IDX03); +-----------+---------+----------+-----------------------------------------+ | Table | Op | Msg_type | Msg_text | +-----------+---------+----------+-----------------------------------------+ | DA_DB.EMP | analyze | status | Engine-independent statistics collected | | DA_DB.EMP | analyze | status | OK | +-----------+---------+----------+-----------------------------------------+ 2 rows in set (1.52 sec) MariaDB [mysql]> select * from index_stats; +---------+------------+-------------------+--------------+---------------+ | db_name | table_name | index_name | prefix_arity | avg_frequency | +---------+------------+-------------------+--------------+---------------+ | DA_DB | EMP_OB | EMP_OB_DEPT_IDX03 | 1 | 7843.1373 | | DA_DB | EMP_OB | EMP_OB_DEPT_IDX03 | 2 | 1.0000 | | DA_DB | EMP | EMP_DEPT_IDX03 | 1 | 7843.1373 | | DA_DB | EMP | EMP_DEPT_IDX03 | 2 | 1.0000 | +---------+------------+-------------------+--------------+---------------+ 4 rows in set (0.00 sec) MariaDB [mysql]> select * from column_stats; +---------+------------+-------------+-----------+-----------+-------------+------------+---------------+-----------+-----------+-----------+ | db_name | table_name | column_name | min_value | max_value | nulls_ratio | avg_length | avg_frequency | hist_size | hist_type | histogram | +---------+------------+-------------+-----------+-----------+-------------+------------+---------------+-----------+-----------+-----------+ | DA_DB | EMP | DEPTNO | 10 | 2040 | 0.0000 | 4.0000 | 7843.1373 | 0 | NULL | NULL | | DA_DB | EMP_OB | DEPTNO | 10 | 2040 | 0.0000 | 4.0000 | 7843.1373 | 0 | NULL | NULL | +---------+------------+-------------+-----------+-----------+-------------+------------+---------------+-----------+-----------+-----------+ 2 rows in set (0.00 sec) MariaDB [mysql]> select * from table_stats; +---------+------------+-------------+ | db_name | table_name | cardinality | +---------+------------+-------------+ | DA_DB | EMP | 1600000 | | DA_DB | EMP_OB | 1600000 | +---------+------------+-------------+ 2 rows in set (0.00 sec)
6. 스칼라 서브쿼리와 LEFT OUTER JOIN에서 정렬 미 사용
- 스칼라 서브쿼리일 때 DEPTNO에 대한 인덱스를 타기 때문에 EMP테이블의 처음 204이 각각 다른 DEPTNO를 가지고 있다고 하더라도 캐싱효과가 나타난다.
- EMP테이블의 DEPTNO 컬럼 정렬여부가 성능에 영향을 미치지 않는다. -> Buffer pinning 효과가 없다.
-- 스칼라 서브쿼리/EMP.DEPTNO 정렬X
ANALYZE FORMAT=JSON
SELECT A.EMPNO
,(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": 640.77,
"table": {
"table_name": "A",
"access_type": "index",
"key": "EMP_DEPT_IDX03",
"key_length": "5",
"used_key_parts": ["DEPTNO"],
"r_loops": 1,
"rows": 1583554,
"r_rows": 1.6e6,
"r_total_time_ms": 320.26,
"filtered": 100,
"r_filtered": 100,
"using_index": true
},
"subqueries": [
{
"expression_cache": {
"r_loops": 1600000,
"r_hit_ratio": 99.987,
"query_block": {
"select_id": 2,
"r_loops": 204,
"r_total_time_ms": 1.9013,
"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": 204,
"rows": 1,
"r_rows": 1,
"r_total_time_ms": 0.7793,
"filtered": 100,
"r_filtered": 100
}
}
}
}
]
}
}
-- 스칼라 서브쿼리/EMP_OB.DEPTNO 정렬O
ANALYZE FORMAT=JSON
SELECT A.EMPNO
,(SELECT S1.DNAME
FROM DEPT S1
WHERE S1.DEPTNO = A.DEPTNO) AS DNAME
FROM EMP_OB A;
{
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 642.06,
"table": {
"table_name": "A",
"access_type": "index",
"key": "EMP_OB_DEPT_IDX03",
"key_length": "5",
"used_key_parts": ["DEPTNO"],
"r_loops": 1,
"rows": 1583894,
"r_rows": 1.6e6,
"r_total_time_ms": 320.54,
"filtered": 100,
"r_filtered": 100,
"using_index": true
},
"subqueries": [
{
"expression_cache": {
"r_loops": 1600000,
"r_hit_ratio": 99.987,
"query_block": {
"select_id": 2,
"r_loops": 204,
"r_total_time_ms": 1.9821,
"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": 204,
"rows": 1,
"r_rows": 1,
"r_total_time_ms": 0.813,
"filtered": 100,
"r_filtered": 100
}
}
}
}
]
}
}
-- LEFT OUTER JOIN/EMP.DEPTNO 정렬X
ANALYZE FORMAT=JSON
SELECT A.EMPNO
,B.DNAME
FROM EMP A
LEFT OUTER JOIN DEPT B ON(B.DEPTNO = A.DEPTNO);
{
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 620.06,
"const_condition": "1",
"table": {
"table_name": "A",
"access_type": "index",
"key": "EMP_DEPT_IDX03",
"key_length": "5",
"used_key_parts": ["DEPTNO"],
"r_loops": 1,
"rows": 1583554,
"r_rows": 1.6e6,
"r_total_time_ms": 318.13,
"filtered": 100,
"r_filtered": 100,
"using_index": true
},
"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": 1.4856,
"filtered": 100,
"r_filtered": 100,
"attached_condition": "trigcond(trigcond((A.DEPTNO is not null)))"
}
}
}
-- LEFT OUTER JOIN/EMP_OB.DEPTNO 정렬O
ANALYZE FORMAT=JSON
SELECT A.EMPNO
,B.DNAME
FROM EMP_OB A
LEFT OUTER JOIN DEPT B ON(B.DEPTNO = A.DEPTNO);
{
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 620.2,
"const_condition": "1",
"table": {
"table_name": "A",
"access_type": "index",
"key": "EMP_OB_DEPT_IDX03",
"key_length": "5",
"used_key_parts": ["DEPTNO"],
"r_loops": 1,
"rows": 1583894,
"r_rows": 1.6e6,
"r_total_time_ms": 318.61,
"filtered": 100,
"r_filtered": 100,
"using_index": true
},
"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": 1.0388,
"filtered": 100,
"r_filtered": 100,
"attached_condition": "trigcond(trigcond((A.DEPTNO is not null)))"
}
}
}
'MariaDB > SQL Tuning' 카테고리의 다른 글
| [MariaDB][성능] 멀티 레인지 리드(multi range read) 최적화 (0) | 2016.09.25 |
|---|---|
| [MariaDB][성능] 서브쿼리 최적화 (0) | 2016.09.25 |
| [MariaDB][성능] LEFT OUTER JOIN과 스칼라 서브쿼리 실행계획 비교1 (0) | 2016.08.24 |
| [MariaDB][성능] 스칼라 서브쿼리 실행계획2 (0) | 2016.08.17 |
| [MariaDB][성능]인라인뷰 처리 방식 분석 (0) | 2016.08.17 |