by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

04. 실행 계획 분석 PART2 (47p, 195 ~ 242)




4.3.10.12 Scanned N databases
4.3.10.13 Select tables optimized away
4.3.10.14 Skip_open_table, Open_frm_only, Open_trigger_only, Open_full_table
4.3.10.15 Unique row not found
create table tb_test1 (a int, primary key(a));
create table tb_test2 (a int, primary key(a));

insert into tb_test1 values(1),(2);
insert into tb_test2 values(1);

EXPLAIN
SELECT t1.a
FROM   tb_test1 t1 left join tb_test2 t2
ON     t1.a=t2.a
WHERE  t1.a=2;

+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| id   | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|    1 | SIMPLE      | t1    | const | PRIMARY       | PRIMARY | 4       | const |    1 | Using index |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
4.3.10.16 Unique row not found
EXPLAIN
SELECT * FROM employees ORDER BY last_name DESC;

+------+-------------+-----------+------+---------------+------+---------+------+--------+----------------+
| id   | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra          |
+------+-------------+-----------+------+---------------+------+---------+------+--------+----------------+
|    1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL | 299202 | Using filesort |
+------+-------------+-----------+------+---------------+------+---------+------+--------+----------------+
1 row in set (0.00 sec)
4.3.10.17 Using index
EXPLAIN
SELECT first_name, birth_date
FROM   employees 
WHERE  first_name between 'Babette' AND 'Gad';

+------+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
| id   | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+------+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
|    1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL | 299202 | Using where |
+------+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)


EXPLAIN
SELECT first_name
FROM   employees 
WHERE  first_name between 'Babette' AND 'Gad';

+------+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
| id   | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+------+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
|    1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL | 299202 | Using where |
+------+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)
4.3.10.18 Using index for group-by
EXPLAIN
SELECT first_name, count(*) as counter
FROM   employees 
GROUP BY first_name;

+------+-------------+-----------+------+---------------+------+---------+------+--------+---------------------------------+
| id   | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra                           |
+------+-------------+-----------+------+---------------+------+---------+------+--------+---------------------------------+
|    1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL | 299202 | Using temporary; Using filesort |
+------+-------------+-----------+------+---------------+------+---------+------+--------+---------------------------------+
1 row in set (0.00 sec)


EXPLAIN
SELECT emp_no, MIN(from_date) , MAX(from_date)
FROM   salaries
GROUP BY emp_no;

+------+-------------+----------+-------+---------------+---------+---------+------+---------+-------------+
| id   | select_type | table    | type  | possible_keys | key     | key_len | ref  | rows    | Extra       |
+------+-------------+----------+-------+---------------+---------+---------+------+---------+-------------+
|    1 | SIMPLE      | salaries | index | NULL          | PRIMARY | 7       | NULL | 2838426 | Using index |
+------+-------------+----------+-------+---------------+---------+---------+------+---------+-------------+
1 row in set (0.06 sec)

EXPLAIN
SELECT first_name
FROM   employees 
WHERE  birth_date > '1994-01-01'
GROUP BY first_name;

+------+-------------+-----------+------+---------------+------+---------+------+--------+----------------------------------------------+
| id   | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra                                        |
+------+-------------+-----------+------+---------------+------+---------+------+--------+----------------------------------------------+
|    1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL | 299202 | Using where; Using temporary; Using filesort |
+------+-------------+-----------+------+---------------+------+---------+------+--------+----------------------------------------------+
1 row in set (0.00 sec)

EXPLAIN
SELECT emp_no
FROM   salaries
WHERE  emp_no BETWEEN 10001 AND 200000
GROUP BY emp_no;

+------+-------------+----------+-------+---------------+---------+---------+------+---------+--------------------------+
| id   | select_type | table    | type  | possible_keys | key     | key_len | ref  | rows    | Extra                    |
+------+-------------+----------+-------+---------------+---------+---------+------+---------+--------------------------+
|    1 | SIMPLE      | salaries | range | PRIMARY       | PRIMARY | 4       | NULL | 1419213 | Using where; Using index |
+------+-------------+----------+-------+---------------+---------+---------+------+---------+--------------------------+
1 row in set (0.05 sec)
4.3.10.19 Using join buffer(Bloack Nested Loop), Using join buffer(Batched Key Access)
EXPLAIN
SELECT *
FROM   dept_emp b, employees a
WHERE  b.from_date > '2005-01-01' AND a.emp_no <10904;

+------+-------------+-------+-------+---------------+---------+---------+------+--------+-------------------------------------------------+
| id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows   | Extra                                           |
+------+-------------+-------+-------+---------------+---------+---------+------+--------+-------------------------------------------------+
|    1 | SIMPLE      | a     | range | PRIMARY       | PRIMARY | 4       | NULL |    902 | Using where                                     |
|    1 | SIMPLE      | b     | ALL   | NULL          | NULL    | NULL    | NULL | 331570 | Using where; Using join buffer (flat, BNL join) |
+------+-------------+-------+-------+---------------+---------+---------+------+--------+-------------------------------------------------+
2 rows in set (0.00 sec)
4.3.10.20 Using sort_union, Using union, Using intersect, Using sort_intersection
4.3.10.21 Using tempoary
EXPLAIN
SELECT *
FROM   employees
GROUP BY gender
ORDER BY MIN(emp_no);

+------+-------------+-----------+------+---------------+------+---------+------+--------+---------------------------------+
| id   | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra                           |
+------+-------------+-----------+------+---------------+------+---------+------+--------+---------------------------------+
|    1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL | 299202 | Using temporary; Using filesort |
+------+-------------+-----------+------+---------------+------+---------+------+--------+---------------------------------+
1 row in set (0.00 sec)
4.3.10.22 Using where
EXPLAIN
SELECT *
FROM   employees
WHERE  emp_no BETWEEN 10001 AND 10100 AND gender ='F';

+------+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+
| id   | select_type | table     | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+------+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+
|    1 | SIMPLE      | employees | range | PRIMARY       | PRIMARY | 4       | NULL |  100 | Using where |
+------+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.01 sec)
4.3.10.23 Using where with pushed condition
4.3.10.24 Deleting all rows
CREATE TABLE tab_delete (fd INT PRIMARY KEY) ENGINE=InnoDB;
INSERT INTO  tab_delete VALUES(1),(2),(3);

EXPLAIN
DELETE FROM tab_delete;

+------+-------------+-------+------+---------------+------+---------+------+------+-------------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra             |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------------+
|    1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL |    3 | Deleting all rows |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------------+
1 row in set (0.07 sec)

EXPLAIN
DELETE FROM tab_delete WHERE fd=1; 

+------+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
| id   | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+------+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
|    1 | SIMPLE      | tab_delete | range | PRIMARY       | PRIMARY | 4       | NULL |    1 | Using where |
+------+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)
4.3.10.25 FirstMatch(tbl_name)
EXPLAIN
SELECT *
FROM   departments d
WHERE  d.dept_no IN (SELECT de.dept_no FROM dept_emp de);

+------+-------------+-------+-------+---------------+-----------+---------+---------------------+------+----------------------------+
| id   | select_type | table | type  | possible_keys | key       | key_len | ref                 | rows | Extra                      |
+------+-------------+-------+-------+---------------+-----------+---------+---------------------+------+----------------------------+
|    1 | PRIMARY     | d     | index | PRIMARY       | dept_name | 42      | NULL                |    9 | Using index                |
|    1 | PRIMARY     | de    | ref   | dept_no       | dept_no   | 4       | employees.d.dept_no |    1 | Using index; FirstMatch(d) |
+------+-------------+-------+-------+---------------+-----------+---------+---------------------+------+----------------------------+
2 rows in set (0.00 sec)
4.3.10.26 LooseScan(m..n)
SET optimizer_switch=DEFAULT;
SET optimizer_switch='firstmatch=off';
SET optimizer_switch='materialization=off';

EXPLAIN
SELECT *
FROM   departments
WHERE  dept_no IN (SELECT dept_no FROM dept_emp);

+------+-------------+-------------+-------+---------------+-----------+---------+-------------------------------+------+---------------------------------------------+
| id   | select_type | table       | type  | possible_keys | key       | key_len | ref                           | rows | Extra                                       |
+------+-------------+-------------+-------+---------------+-----------+---------+-------------------------------+------+---------------------------------------------+
|    1 | PRIMARY     | departments | index | PRIMARY       | dept_name | 42      | NULL                          |    9 | Using index                                 |
|    1 | PRIMARY     | dept_emp    | ref   | dept_no       | dept_no   | 4       | employees.departments.dept_no |    1 | Using index; Start temporary; End temporary |
+------+-------------+-------------+-------+---------------+-----------+---------+-------------------------------+------+---------------------------------------------+
2 rows in set (0.00 sec)
4.3.10.27 Materialize, Scan
4.3.10.28 Start materialize, End materialize, Scan
4.3.10.29 Start tempoary, End tempoary
EXPLAIN
SELECT *
FROM   employees d
WHERE  d.emp_no IN (SELECT de.dept_no FROM dept_emp de WHERE dept_no in ('d001','d003'));

+------+-------------+-------+--------+---------------+---------+---------+----------------------+-------+-------------------------------------------+
| id   | select_type | table | type   | possible_keys | key     | key_len | ref                  | rows  | Extra                                     |
+------+-------------+-------+--------+---------------+---------+---------+----------------------+-------+-------------------------------------------+
|    1 | PRIMARY     | de    | range  | dept_no       | dept_no | 4       | NULL                 | 75424 | Using where; Using index; Start temporary |
|    1 | PRIMARY     | d     | eq_ref | PRIMARY       | PRIMARY | 4       | employees.de.dept_no |     1 | Using where; End temporary                |
+------+-------------+-------+--------+---------------+---------+---------+----------------------+-------+-------------------------------------------+
2 rows in set (0.06 sec)
4.3.10.30 Using index condition
EXPLAIN
SELECT *
FROM   employees
WHERE  first_name LIKE 'Lee%' AND first_name LIKE '%matt';

+------+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
| id   | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+------+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
|    1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL | 299202 | Using where |
+------+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)
4.3.10.31 Rowid-ordered scan, Key-ordered scan
EXPLAIN
SELECT *
FROM   employees
WHERE  first_name >='A' AND first_name <'B';

+------+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
| id   | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+------+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
|    1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL | 299202 | Using where |
+------+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

EXPLAIN
SELECT *
FROM   dept_emp d,employees e
WHERE  e.emp_no=d.emp_no AND d.dept_no IN ('d001','d002');

+------+-------------+-------+--------+-----------------+---------+---------+--------------------+--------+-------------+
| id   | select_type | table | type   | possible_keys   | key     | key_len | ref                | rows   | Extra       |
+------+-------------+-------+--------+-----------------+---------+---------+--------------------+--------+-------------+
|    1 | SIMPLE      | d     | ALL    | PRIMARY,dept_no | NULL    | NULL    | NULL               | 331570 | Using where |
|    1 | SIMPLE      | e     | eq_ref | PRIMARY         | PRIMARY | 4       | employees.d.emp_no |      1 |             |
+------+-------------+-------+--------+-----------------+---------+---------+--------------------+--------+-------------+
2 rows in set (0.00 sec)
4.3.10.32 No matching rows after patition pruning

4.3.11 EXPLAIN EXTENDED(Filtered 컬럼)

EXPLAIN EXTENDED
SELECT *
FROM   employees
WHERE  emp_no BETWEEN 10001 AND 10100 AND gender='F';

+------+-------------+-----------+-------+---------------+---------+---------+------+------+----------+-------------+
| id   | select_type | table     | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+------+-------------+-----------+-------+---------------+---------+---------+------+------+----------+-------------+
|    1 | SIMPLE      | employees | range | PRIMARY       | PRIMARY | 4       | NULL |  100 |   100.00 | Using where |
+------+-------------+-----------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

4.3.12 EXPLAIN EXTENDED(추가 옵티마이저 정보)

EXPLAIN EXTENDED
SELECT e.first_name,
  (SELECT COUNT(*) FROM dept_emp d, dept_manager m WHERE d.dept_no=m.dept_no) AS cnt
FROM   employees e
WHERE  e.emp_no=10001;

+------+-------------+-------+-------+---------------+---------+---------+---------------------+-------+----------+-------------+
| id   | select_type | table | type  | possible_keys | key     | key_len | ref                 | rows  | filtered | Extra       |
+------+-------------+-------+-------+---------------+---------+---------+---------------------+-------+----------+-------------+
|    1 | PRIMARY     | e     | const | PRIMARY       | PRIMARY | 4       | const               |     1 |   100.00 |             |
|    2 | SUBQUERY    | m     | index | dept_no       | dept_no | 4       | NULL                |    24 |   100.00 | Using index |
|    2 | SUBQUERY    | d     | ref   | dept_no       | dept_no | 4       | employees.m.dept_no | 20723 |   100.00 | Using index |
+------+-------------+-------+-------+---------------+---------+---------+---------------------+-------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

SHOW WARNINGS;

SELECT 'George' AS 'first_name'
FROM   'employees'.'employees' 'e' WHERE 1;

MariaDB [employees]> SHOW WARNINGS;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                 |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Error | 1064 | You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''employees'.'employees' 'e' WHERE 1' at line 2 |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

4.3.13 EXPLAIN PARTITIONS(Partions 컬럼)

CREATE TABLE tb_partition (
  reg_date DATE DEFAULT NULL,
  id INT DEFAULT NULL,
  name VARCHAR(50) DEFAULT NULL) ENGINE=INNODB
  partition BY range(YEAR(reg_date)) (
    partition p0 VALUES less than (2008) ENGINE=INNODB,
    partition p1 VALUES less than (2009) ENGINE=INNODB,
    partition p2 VALUES less than (2010) ENGINE=INNODB,
    partition p3 VALUES less than (2011) ENGINE=INNODB
  );
  
  EXPLAIN PARTITIONS
  SELECT * FROM tb_partition
  WHERE  reg_date BETWEEN '2010-01-01' AND '2010-12-30' ;

+------+-------------+--------------+------------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+--------------+------------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | tb_partition | p3         | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+------+-------------+--------------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

4.4 옵티마이저 힌트

4.4.1 힌트의 사용법

SELECT * FROM employees USE_INDEX(primary) WHERE emp_no=10001;
SELECT * FROM employees /*! USE_INDEX(primary)*/ WHERE emp_no=10001;

CREATE /*!32302 TEMPOARY*/ TABLE temp_emp_stat
(hire_year INT NOT NULL, emp_count INT, PRIMARY KEY(hire_year));

CREATE TEMPOARY TABLE temp_emp_stat
(hire_year INT NOT NULL, emp_count INT, PRIMARY KEY(hire_year));

4.4.2 STRAIGHT_JOIN

EXPLAIN
SELECT /*! STRAIGHT_JOIN */
       e.first_name , e.last_name, d.dept_name
FROM   employees e, departments d, dept_emp de
WHERE  e.emp_no=de.emp_no AND d.dept_no=de.dept_no;


+------+-------------+-------+--------+-----------------+-----------+---------+----------------------------------------+--------+-------------------------------------------------+
| id   | select_type | table | type   | possible_keys   | key       | key_len | ref                                    | rows   | Extra                                           |
+------+-------------+-------+--------+-----------------+-----------+---------+----------------------------------------+--------+-------------------------------------------------+
|    1 | SIMPLE      | e     | ALL    | PRIMARY         | NULL      | NULL    | NULL                                   | 299202 |                                                 |
|    1 | SIMPLE      | d     | index  | PRIMARY         | dept_name | 42      | NULL                                   |      9 | Using index; Using join buffer (flat, BNL join) |
|    1 | SIMPLE      | de    | eq_ref | PRIMARY,dept_no | PRIMARY   | 8       | employees.e.emp_no,employees.d.dept_no |      1 | Using index                                     |
+------+-------------+-------+--------+-----------------+-----------+---------+----------------------------------------+--------+-------------------------------------------------+
3 rows in set (0.00 sec)

4.4.3 USE INDEX / FORCE INDEX / IGNORE INDEX

SELECT * FROM employees WHERE emp_no=10001;
SELECT * FROM employees FORCE INDEX(primary) WHERE emp_no=10001;
SELECT * FROM employees USE INDEX(primary) WHERE emp_no=10001;
SELECT * FROM employees IGNORE INDEX(primary) WHERE emp_no=10001;
SELECT * FROM employees FORCE INDEX(ix_firstname) WHERE emp_no=10001;

4.4.4 SQL_CACHE/SQL_NO_CACHE

SELECT COUNT(*) FROM employees WHERE last_name = 'Facello';

SELECT SQL_NO_CACHE COUNT(*) FROM employees WHERE last_name='Facello';
SELECT /*! SQL_NO_CACHE */ COUNT(*) FROM employees WHERE last_name='Facello'

4.4.5 SQL_CALC_FOUND_ROWS

SELECT SQL_CALC_FOUND_ROWS * FROM employees LIMIT 5;

MariaDB [employees]> SELECT SQL_CALC_FOUND_ROWS * FROM employees LIMIT 5;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
|  10002 | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 |
|  10003 | 1959-12-03 | Parto      | Bamford   | M      | 1986-08-28 |
|  10004 | 1954-05-01 | Chirstian  | Koblick   | M      | 1986-12-01 |
|  10005 | 1955-01-21 | Kyoichi    | Maliniak  | M      | 1989-09-12 |
+--------+------------+------------+-----------+--------+------------+
5 rows in set (0.22 sec)

MariaDB [employees]> SELECT FOUND_ROWS() AS total_record_count;
+--------------------+
| total_record_count |
+--------------------+
|             300024 |
+--------------------+
1 row in set (0.00 sec)

4.4.6 기타 힌트

4.5 실행 계획 분석 시 주의사항

4.5.1 Select_type 컬럼의 주의 대상

4.5.2 Type 컬럼의 주의 대상

4.5.3 Key 컬럼의 주의 대상

4.5.4 Rows 컬럼의 주의 대상

4.5.5 Extra 컬럼의 주의 대상

4.5.5.1 쿼리가 요건을 제대로 반영하고 있는지 확인해야 하는 경우

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.