[테스트 환경]
<"employee"."e"."emp_no"> 에 따라서 에 출력되는 결과 값을 캐싱해두어 사용한다. 서브쿼리의 r_loops가 1인 것을 확인한다.
# 따라서 semi join으로 쿼리를 변환해서
서브쿼리 내의 데이터가 유일하지 않기 때문에 캐싱이 exists로 나타난 것을 확인할 수 있다.
titles 테이블의 pk 인덱스를 통해서 임시 테이블을 검색한다.(출처 :http://dev.mysql.com/doc/refman/5.7/en/explain-extended.html)
-- 서브쿼리의 데이터를 group by 로 유일하게 만든 후 진행.
OS : CentOS7
DB : MariaDB 10.1.12
# optimizer_switch 파라미터로 optimizer의 최적화를 제어할 수 있다.
MariaDB [(none)]> show variables like '%optimizer_switch%' \G
*************************** 1. row ***************************
Variable_name: optimizer_switch
Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on
1 row in set (0.00 sec)
#메인쿼리의 데이터가 유일하고 서브쿼리의 데이터가 유일할 때
-- semijoin 기능 on
MariaDB [employee]> set optimizer_switch='semijoin=on';
Query OK, 0 rows affected (0.00 sec)
MariaDB [employee]> explain extended
-> select *
-> from employees e
-> where e.emp_no in (select de.emp_no
-> from dept_emp de
-> where de.dept_no = 'd001');
+------+-------------+-------+--------+-----------------+---------+---------+--------------------+-------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------+--------+-----------------+---------+---------+--------------------+-------+----------+--------------------------+
| 1 | PRIMARY | de | ref | PRIMARY,dept_no | dept_no | 12 | const | 40528 | 100.00 | Using where; Using index |
| 1 | PRIMARY | e | eq_ref | PRIMARY | PRIMARY | 4 | employee.de.emp_no | 1 | 100.00 | |
+------+-------------+-------+--------+-----------------+---------+---------+--------------------+-------+----------+--------------------------+
2 rows in set, 1 warning (0.00 sec)
MariaDB [employee]> show warnings;
+-------+------+---------------------------------------------------------------------------------------
select "employee"."e"."emp_no" AS "emp_no","employee"."e"."birth_date" AS "birth_date"
,"employee"."e"."first_name" AS "first_name","employee"."e"."last_name" AS "last_name"
,"employee"."e"."gender" AS "gender","employee"."e"."hire_date" AS "hire_date"
from "employee"."dept_emp" "de" join "employee"."employees" "e"
where (("employee"."e"."emp_no" = "employee"."de"."emp_no") and ("employee"."de"."dept_no" = 'd001')) |
+-------+------+---------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
analyze format=json
select *
from employees e
where e.emp_no in (select de.emp_no
from dept_emp de
where de.dept_no = 'd001');
{
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 43.369,
"table": {
"table_name": "de",
"access_type": "ref",
"possible_keys": ["PRIMARY", "dept_no"],
"key": "dept_no",
"key_length": "12",
"used_key_parts": ["dept_no"],
"ref": ["const"],
"r_loops": 1,
"rows": 40528,
"r_rows": 20211,
"r_total_time_ms": 4.9655,
"filtered": 100,
"r_filtered": 100,
"attached_condition": "(de.dept_no = 'd001')",
"using_index": true
},
"table": {
"table_name": "e",
"access_type": "eq_ref",
"possible_keys": ["PRIMARY"],
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["emp_no"],
"ref": ["employee.de.emp_no"],
"r_loops": 20211,
"rows": 1,
"r_rows": 1,
"r_total_time_ms": 32.032,
"filtered": 100,
"r_filtered": 100
}
}
}
-- 옵티마이저가 변경한 쿼리를 보면 서브쿼리 테이블인 dept_emp 테이블이 드라이빙이 되어 inner join으로 풀린것 을 확인할 수 있다. dept_emp 테이블의 pk는 emp_no와 dept_no이며 조건절에 상수값으로 dept_no를 받기 때문에 emp_no가 유일함을 확인할 수 있다. 그래서 inner join으로 풀려도 데이터에 영향을 미치지 않는다.
-- semijoin 기능 off
MariaDB [employee]> set optimizer_switch='semijoin=off';
Query OK, 0 rows affected (0.00 sec)
MariaDB [employee]> explain extended
-> select *
-> from employees e
-> where e.emp_no in (select de.emp_no
-> from dept_emp de
-> where de.dept_no = 'd001');
+------+--------------+-------+------+-----------------+---------+---------+-------+--------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+--------------+-------+------+-----------------+---------+---------+-------+--------+----------+--------------------------+
| 1 | PRIMARY | e | ALL | NULL | NULL | NULL | NULL | 299290 | 100.00 | Using where |
| 2 | MATERIALIZED | de | ref | PRIMARY,dept_no | dept_no | 12 | const | 40528 | 100.00 | Using where; Using index |
+------+--------------+-------+------+-----------------+---------+---------+-------+--------+----------+--------------------------+
2 rows in set, 1 warning (0.00 sec)
MariaDB [employee]> show warnings;
+-------+------+---------------------------------------------------------------------------------------
select "employee"."e"."emp_no" AS "emp_no","employee"."e"."birth_date" AS "birth_date","employee"."e"."first_name" AS "first_name"
,"employee"."e"."last_name" AS "last_name","employee"."e"."gender" AS "gender","employee"."e"."hire_date" AS "hire_date"
from "employee"."employees" "e" where <"employee"."e"."emp_no">(("employee"."e"."emp_no","employee"."e"."emp_no" in
( (select "employee"."de"."emp_no" from "employee"."dept_emp" "de" where ("employee"."de"."dept_no" = 'd001') ),
("employee"."e"."emp_no" in on distinct_key where (("employee"."e"."emp_no" = ""."emp_no"))))))|
+-------+------+---------------------------------------------------------------------------------------
1 row in set (0.00 sec)
analyze format=json
select *
from employees e
where e.emp_no in (select de.emp_no
from dept_emp de
where de.dept_no = 'd001');
{
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 242.03,
"table": {
"table_name": "e",
"access_type": "ALL",
"r_loops": 1,
"rows": 299290,
"r_rows": 300024,
"r_total_time_ms": 108.06,
"filtered": 100,
"r_filtered": 6.7365,
"attached_condition": "(e.emp_no,e.emp_no in (subquery#2))"
},
"subqueries": [
{
"query_block": {
"select_id": 2,
"r_loops": 1,
"r_total_time_ms": 16.54,
"table": {
"table_name": "de",
"access_type": "ref",
"possible_keys": ["PRIMARY", "dept_no"],
"key": "dept_no",
"key_length": "12",
"used_key_parts": ["dept_no"],
"ref": ["const"],
"r_loops": 1,
"rows": 40528,
"r_rows": 20211,
"r_total_time_ms": 6.736,
"filtered": 100,
"r_filtered": 100,
"attached_condition": "(de.dept_no = 'd001')",
"using_index": true
}
}
}
]
}
}
-- 서브쿼리 최적화에서 semijoin으로 쿼리변환이 일어나지 않으면 서브쿼리 캐싱을 사용한다. 이 쿼리에서는 dept_no ='d001'인 데이터를 파라미터 값
-- semijoin 기능 on
MariaDB [employee]> set optimizer_switch='semijoin=on';
Query OK, 0 rows affected (0.02 sec)
MariaDB [employee]> explain extended
-> select *
-> from employees e
-> where e.emp_no in (select s1.emp_no
-> from salaries s1
-> where s1.salary > 66961);
+------+-------------+-------+------+---------------+---------+---------+-------------------+--------+----------+----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------+------+---------------+---------+---------+-------------------+--------+----------+----------------------------+
| 1 | PRIMARY | e | ALL | PRIMARY | NULL | NULL | NULL | 299290 | 100.00 | |
| 1 | PRIMARY | s1 | ref | PRIMARY | PRIMARY | 4 | employee.e.emp_no | 4 | 100.00 | Using where; FirstMatch(e) |
+------+-------------+-------+------+---------------+---------+---------+-------------------+--------+----------+----------------------------+
2 rows in set, 1 warning (0.00 sec)
MariaDB [employee]> show warnings;
+-------+------+---------------------------------------------------------------------------------------
select "employee"."e"."emp_no" AS "emp_no","employee"."e"."birth_date" AS "birth_date","employee"."e"."first_name" AS "first_name"
,"employee"."e"."last_name" AS "last_name","employee"."e"."gender" AS "gender","employee"."e"."hire_date" AS "hire_date"
from "employee"."employees" "e" semi join ("employee"."salaries" "s1")
where (("employee"."s1"."emp_no" = "employee"."e"."emp_no") and ("employee"."s1"."salary" > 66961)) |
+-------+------+---------------------------------------------------------------------------------------
1 row in set (0.00 sec)
analyze format=json
select *
from employees e
where e.emp_no in (select s1.emp_no
from salaries s1
where s1.salary > 66961);
{
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 925.3,
"table": {
"table_name": "e",
"access_type": "ALL",
"possible_keys": ["PRIMARY"],
"r_loops": 1,
"rows": 299290,
"r_rows": 300024,
"r_total_time_ms": 101.68,
"filtered": 100,
"r_filtered": 100
},
"table": {
"table_name": "s1",
"access_type": "ref",
"possible_keys": ["PRIMARY"],
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["emp_no"],
"ref": ["employee.e.emp_no"],
"r_loops": 300024,
"rows": 4,
"r_rows": 6.4283,
"r_total_time_ms": 689.72,
"filtered": 100,
"r_filtered": 8.1146,
"attached_condition": "(s1.salary > 66961)",
"first_match": "e"
}
}
}
-- salaries 테이블에는 한 emp_no당 여러 건의 데이터가 존재하기 때문에 inner join으로 쿼리변환을 하면 변환하기 전의 데이터와 동일하지 않은 결과를 리턴한다. -- semijoin 기능 off
MariaDB [(none)]> set optimizer_switch='semijoin=off';
Query OK, 0 rows affected (0.00 sec)
MariaDB [employee]> explain extended
-> select *
-> from employees e
-> where e.emp_no in (select s1.emp_no
-> from salaries s1
-> where s1.salary > 66961);
+------+--------------------+-------+----------------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+--------------------+-------+----------------+---------------+---------+---------+------+--------+----------+-------------+
| 1 | PRIMARY | e | ALL | NULL | NULL | NULL | NULL | 299290 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | s1 | index_subquery | PRIMARY | PRIMARY | 4 | func | 4 | 100.00 | Using where |
+------+--------------------+-------+----------------+---------------+---------+---------+------+--------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
MariaDB [employee]> show warnings;
+-------+------+---------------------------------------------------------------------------------------
select "employee"."e"."emp_no" AS "emp_no","employee"."e"."birth_date" AS "birth_date","employee"."e"."first_name" AS "first_name"
,"employee"."e"."last_name" AS "last_name","employee"."e"."gender" AS "gender","employee"."e"."hire_date" AS "hire_date"
from "employee"."employees" "e"
where <"employee"."e"."emp_no">(
("employee"."e"."emp_no",((("employee"."e"."emp_no") in salaries on PRIMARY
where (("employee"."s1"."salary" > 66961) and (("employee"."e"."emp_no") = "employee"."s1"."emp_no"))))))
+-------+------+---------------------------------------------------------------------------------------
1 row in set (0.00 sec)
analyze format=json
select *
from employees e
where e.emp_no in (select s1.emp_no
from salaries s1
where s1.salary > 66961);
{
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 937.37,
"table": {
"table_name": "e",
"access_type": "ALL",
"r_loops": 1,
"rows": 299290,
"r_rows": 300024,
"r_total_time_ms": 101.75,
"filtered": 100,
"r_filtered": 52.163,
"attached_condition": "(e.emp_no,(subquery#2))"
},
"subqueries": [
{
"query_block": {
"select_id": 2,
"table": {
"table_name": "s1",
"access_type": "index_subquery",
"possible_keys": ["PRIMARY"],
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["emp_no"],
"ref": ["func"],
"r_loops": 0,
"rows": 4,
"r_rows": null,
"r_total_time_ms": 690.43,
"filtered": 100,
"r_filtered": null
}
}
}
]
}
}
-- access_type인 index_subquery는 in 서브쿼리 형태를 index lookup function으로 대체해서 더 나은 성능으로 쿼리를 실행하는 타입이며 nonuique index를 사용하는 쿼리에서 나타난다. 이 때 r_loops 값이 0으로 출력된다. unique index를 사용하는 쿼리에서는 unique_subquery 타입으로 출력된다. (출처: http://dev.mysql.com/doc/refman/5.7/en/explain-output.html#jointype_unique_subquery) # 메인쿼리의 데이터에 중복이 존재하고 서브쿼리의 데이터에 중복이 존재할 때
-- semijoin 기능 on
MariaDB [employees]> set optimizer_switch='semijoin=on';
Query OK, 0 rows affected (0.00 sec)
MariaDB [employees]> explain extended
-> SELECT *
-> FROM titles a
-> where a.emp_no in (select s1.emp_no
-> from salaries s1
-> where s1.salary > 66961);
+------+--------------+-------------+--------+---------------+--------------+---------+------+---------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+--------------+-------------+--------+---------------+--------------+---------+------+---------+----------+-------------+
| 1 | PRIMARY | a | ALL | PRIMARY | NULL | NULL | NULL | 443021 | 100.00 | |
| 1 | PRIMARY | | eq_ref | distinct_key | distinct_key | 4 | func | 1 | 100.00 | |
| 2 | MATERIALIZED | s1 | ALL | PRIMARY | NULL | NULL | NULL | 2838426 | 100.00 | Using where |
+------+--------------+-------------+--------+---------------+--------------+---------+------+---------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)
MariaDB [employees]> show warnings;
+-------+------+------------------------------------------------------------------
select "employees"."a"."emp_no" AS "emp_no","employees"."a"."title" AS "title"
,"employees"."a"."from_date" AS "from_date","employees"."a"."to_date" AS "to_date"
from "employees"."titles" "a" semi join ("employees"."salaries" "s1")
where (("employees"."s1"."salary" > 66961)) |
+-------+------+------------------------------------------------------------------
{
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 1099.8,
"table": {
"table_name": "a",
"access_type": "ALL",
"possible_keys": ["PRIMARY"],
"r_loops": 1,
"rows": 442724,
"r_rows": 443308,
"r_total_time_ms": 97.457,
"filtered": 100,
"r_filtered": 100
},
"table": {
"table_name": "",
"access_type": "eq_ref",
"possible_keys": ["distinct_key"],
"key": "distinct_key",
"key_length": "4",
"used_key_parts": ["emp_no"],
"ref": ["func"],
"r_loops": 443308,
"rows": 1,
"r_rows": 0.5698,
"r_total_time_ms": 244.72,
"filtered": 100,
"r_filtered": 100,
"materialized": {
"unique": 1,
"query_block": {
"select_id": 2,
"table": {
"table_name": "s1",
"access_type": "ALL",
"possible_keys": ["PRIMARY"],
"r_loops": 1,
"rows": 2838426,
"r_rows": 2.84e6,
"r_total_time_ms": 491.01,
"filtered": 100,
"r_filtered": 37.64,
"attached_condition": "(s1.salary > 66961)"
}
}
}
}
}
}
-- 서브쿼리를 임시테이블로 생성해서 메인쿼리와 조인을 한다. IN(Subquery) 형태의 쿼리는 유일한 데이터만 리턴해야하기 때문에 중복 데이터를 제거한 후(distinct_key) 조인을 시도한다.-- semijoin 기능 off
MariaDB [employees]> set optimizer_switch='semijoin=off';
Query OK, 0 rows affected (0.00 sec)
MariaDB [employees]> explain extended
-> SELECT *
-> FROM titles a
-> where a.emp_no in (select s1.emp_no
-> from salaries s1
-> where s1.salary > 66961);
+------+--------------+-------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+--------------+-------+------+---------------+------+---------+------+---------+----------+-------------+
| 1 | PRIMARY | a | ALL | NULL | NULL | NULL | NULL | 443021 | 100.00 | Using where |
| 2 | MATERIALIZED | s1 | ALL | PRIMARY | NULL | NULL | NULL | 2838426 | 100.00 | Using where |
+------+--------------+-------+------+---------------+------+---------+------+---------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
MariaDB [employees]> show warnings;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select "employees"."a"."emp_no" AS "emp_no","employees"."a"."title" AS "title","employees"."a"."from_date" AS "from_date","employees"."a"."to_date" AS "to_date"
from "employees"."titles" "a"
where <"employees"."a"."emp_no">(("employees"."a"."emp_no","employees"."a"."emp_no"
in ( (select "employees"."s1"."emp_no" from "employees"."salaries" "s1" where ("employees"."s1"."salary" > 66961) ), ("employees"."a"."emp_no"
in on distinct_key where (("employees"."a"."emp_no" = ""."emp_no"))))))
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 1086.6,
"table": {
"table_name": "a",
"access_type": "ALL",
"possible_keys": ["PRIMARY"],
"r_loops": 1,
"rows": 442724,
"r_rows": 443308,
"r_total_time_ms": 98.412,
"filtered": 100,
"r_filtered": 100
},
"table": {
"table_name": "",
"access_type": "eq_ref",
"possible_keys": ["distinct_key"],
"key": "distinct_key",
"key_length": "4",
"used_key_parts": ["emp_no"],
"ref": ["func"],
"r_loops": 443308,
"rows": 1,
"r_rows": 0.5698,
"r_total_time_ms": 231.39,
"filtered": 100,
"r_filtered": 100,
"materialized": {
"unique": 1,
"query_block": {
"select_id": 2,
"table": {
"table_name": "s1",
"access_type": "ALL",
"possible_keys": ["PRIMARY"],
"r_loops": 1,
"rows": 2838426,
"r_rows": 2.84e6,
"r_total_time_ms": 490.89,
"filtered": 100,
"r_filtered": 37.64,
"attached_condition": "(s1.salary > 66961)"
}
}
}
}
}
}
-- -- semijoin 기능 on
MariaDB [employees]> set optimizer_switch='semijoin=on';
Query OK, 0 rows affected (0.00 sec)
SELECT *
FROM titles a
where a.emp_no in (select s1.emp_no
from salaries s1
where s1.salary > 66961
group by s1.emp_no);
+------+--------------+-------------+--------+---------------+--------------+---------+------+---------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+--------------+-------------+--------+---------------+--------------+---------+------+---------+----------+-------------+
| 1 | PRIMARY | a | ALL | PRIMARY | NULL | NULL | NULL | 443021 | 100.00 | |
| 1 | PRIMARY | | eq_ref | distinct_key | distinct_key | 4 | func | 1 | 100.00 | |
| 2 | MATERIALIZED | s1 | ALL | PRIMARY | NULL | NULL | NULL | 2838426 | 100.00 | Using where |
+------+--------------+-------------+--------+---------------+--------------+---------+------+---------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)
+-------+------+------------------------------------------------------------------
select "employees"."a"."emp_no" AS "emp_no","employees"."a"."title" AS "title"
,"employees"."a"."from_date" AS "from_date","employees"."a"."to_date" AS "to_date"
from "employees"."titles" "a" semi join ("employees"."salaries" "s1")
where (("employees"."s1"."salary" > 66961)) |
+-------+------+------------------------------------------------------------------
{
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 1095.1,
"table": {
"table_name": "a",
"access_type": "ALL",
"possible_keys": ["PRIMARY"],
"r_loops": 1,
"rows": 442724,
"r_rows": 443308,
"r_total_time_ms": 97.421,
"filtered": 100,
"r_filtered": 100
},
"table": {
"table_name": "",
"access_type": "eq_ref",
"possible_keys": ["distinct_key"],
"key": "distinct_key",
"key_length": "4",
"used_key_parts": ["emp_no"],
"ref": ["func"],
"r_loops": 443308,
"rows": 1,
"r_rows": 0.5698,
"r_total_time_ms": 241.28,
"filtered": 100,
"r_filtered": 100,
"materialized": {
"unique": 1,
"query_block": {
"select_id": 2,
"table": {
"table_name": "s1",
"access_type": "ALL",
"possible_keys": ["PRIMARY"],
"r_loops": 1,
"rows": 2838426,
"r_rows": 2.84e6,
"r_total_time_ms": 489.78,
"filtered": 100,
"r_filtered": 37.64,
"attached_condition": "(s1.salary > 66961)"
}
}
}
}
}
}
-- 서브쿼리의 데이터를 유일하게 만들어도 내부적으로 distinct_key작업을 진행한다.'MariaDB > SQL Tuning' 카테고리의 다른 글
| [MariaDB][성능] 멀티 레인지 리드(multi range read) 최적화 (0) | 2016.09.25 |
|---|---|
| [MariaDB][성능] LEFT OUTER JOIN과 스칼라 서브쿼리 실행계획 비교2 (0) | 2016.08.28 |
| [MariaDB][성능] LEFT OUTER JOIN과 스칼라 서브쿼리 실행계획 비교1 (0) | 2016.08.24 |
| [MariaDB][성능] 스칼라 서브쿼리 실행계획2 (0) | 2016.08.17 |
| [MariaDB][성능]인라인뷰 처리 방식 분석 (0) | 2016.08.17 |