Real MariaDB (2017년)
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.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.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.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.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)
"구루비 데이터베이스 스터디모임" 에서 2017년에 "Real MariaDB" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/4188
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.