MySQL 실행계획 추적

MIN CHO

October 23, 2014

Categories

• MySQL

Tags

• mysql
• mysql_explain

MySQL 실행계획 추적

1. 목적 

• index를 추가했음에도 불구하고, 사용하지 않음. 실행계획을 추적하기로 결정

2. 테스트 방법 

테스트 데이터 생성

CREATE TABLE `optimizer_test` (
  `no` int(11) NOT NULL AUTO_INCREMENT,
  `datetime` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  PRIMARY KEY (`no`),
  KEY `DATETIME` (`datetime`)
) ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8;
mysql> explain select no,datetime FROM optimizer_test WHERE datetime between '2014-09-24 00:00:00' and '2014-10-01 23:59:59' ORDER BY datetime DESC;
+----+-------------+----------------+-------+---------------+----------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+-------+---------------+----------+---------+------+------+--------------------------+
| 1 | SIMPLE | optimizer_test | index | DATETIME | DATETIME | 33 | NULL | 1 | Using where; Using index |
+----+-------------+----------------+-------+---------------+----------+---------+------+------+--------------------------+

mysql> ALTER TABLE optimizer_test ADD INDEX datetimedesc(DATETIME(23) DESC);
mysql> explain select no,datetime FROM optimizer_test WHERE datetime between '2014-09-24 00:00:00' and '2014-10-01 23:59:59' ORDER BY datetime DESC;
+----+-------------+----------------+-------+-----------------------+----------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+-------+-----------------------+----------+---------+------+------+--------------------------+
| 1 | SIMPLE | optimizer_test | index | DATETIME,datetimedesc | DATETIME | 33 | NULL | 1 | Using where; Using index |
+----+-------------+----------------+-------+-----------------------+----------+---------+------+------+--------------------------+

OPTIMIZER_TRACE

• The OPTIMIZER_TRACE table was added in MySQL 5.6.3.
• 사용법 http://dev.mysql.com/doc/internals/en/optimizer-tracing-typical-usage.html

SET optimizer_trace="enabled=on";
select no,datetime FROM optimizer_test WHERE datetime between '2014-09-24 00:00:00' and '2014-10-01 23:59:59' ORDER BY datetime DESC;
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACEG

select no,datetime FROM optimizer_test use index(datetimedesc) WHERE datetime between '2014-09-24 00:00:00' and '2014-10-01 23:59:59' ORDER BY datetime DESC;
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACEG

SET optimizer_trace="enabled=off";

3. 결과

• Json 형태로 나타나며… Json browser를 통해 확인할 수 있다.

Share Tweet LinkedIn Reddit