|
---------------------------------------------------------+
| salaries | CREATE TABLE `salaries` ( |
| | `emp_no` int(11) NOT NULL, |
| | `salary` int(11) NOT NULL, |
| | `from_date` date NOT NULL, |
| | `to_date` date NOT NULL, |
| | PRIMARY KEY (`emp_no`,`from_date`) |
| | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
| | /*!50500 PARTITION BY RANGE COLUMNS(from_date) |
| | (PARTITION p01 VALUES LESS THAN ('1985-12-31') ENGINE = InnoDB, |
| | PARTITION p02 VALUES LESS THAN ('1986-12-31') ENGINE = InnoDB, |
| | PARTITION p03 VALUES LESS THAN ('1987-12-31') ENGINE = InnoDB, |
| | PARTITION p04 VALUES LESS THAN ('1988-12-31') ENGINE = InnoDB, |
| | PARTITION p05 VALUES LESS THAN ('1989-12-31') ENGINE = InnoDB, |
| | PARTITION p06 VALUES LESS THAN ('1990-12-31') ENGINE = InnoDB, |
| | PARTITION p07 VALUES LESS THAN ('1991-12-31') ENGINE = InnoDB, |
| | PARTITION p08 VALUES LESS THAN ('1992-12-31') ENGINE = InnoDB, |
| | PARTITION p09 VALUES LESS THAN ('1993-12-31') ENGINE = InnoDB, |
| | PARTITION p10 VALUES LESS THAN ('1994-12-31') ENGINE = InnoDB, |
| | PARTITION p11 VALUES LESS THAN ('1995-12-31') ENGINE = InnoDB, |
| | PARTITION p12 VALUES LESS THAN ('1996-12-31') ENGINE = InnoDB, |
| | PARTITION p13 VALUES LESS THAN ('1997-12-31') ENGINE = InnoDB, |
| | PARTITION p14 VALUES LESS THAN ('1998-12-31') ENGINE = InnoDB, |
| | PARTITION p15 VALUES LESS THAN ('1999-12-31') ENGINE = InnoDB, |
| | PARTITION p16 VALUES LESS THAN ('2000-12-31') ENGINE = InnoDB, |
| | PARTITION p17 VALUES LESS THAN ('2001-12-31') ENGINE = InnoDB, |
| | PARTITION p18 VALUES LESS THAN ('2002-12-31') ENGINE = InnoDB, |
| | PARTITION p19 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */ |
+----------+-----------------------------------------------------------------+
1 row in set
Time: 0.018s
mysql root@localhost:employees> explain select * from salaries where from_date > '1985-12-31' and from_date < '1990-12-31';
+----+-------------+----------+---------------------+------+---------------+--------+---------+--------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+---------------------+------+---------------+--------+---------+--------+--------+----------+-------------+
| 1 | SIMPLE | salaries | p02,p03,p04,p05,p06 | ALL | <null> | <null> | <null> | <null> | 384341 | 11.11 | Using where |
+----+-------------+----------+---------------------+------+---------------+--------+---------+--------+--------+----------+-------------+
1 row in set
Time: 0.023s
type
type应该被认为是解读执行计划当中最重要的部分，根据type显示的内容可以判断语句总体的查询效率。主要有以下几种类型：
- system：表只有一行(系统表)，是const的一种特殊情况。
-- 测试表departments_1生成：
mysql root@localhost:employees> create table departments_1 as select * from departments where dept_no='d005';
Query OK, 1 row affected
Time: 0.107s
mysql root@localhost:employees> alter table departments_1 add primary key(dept_no);
Query OK, 0 rows affected
mysql root@localhost:employees> create index idx_dept_name on departments_1(dept_name);
Query OK, 0 rows affected
mysql root@localhost:employees> show create table departments_1\G;
***************************[ 1. row ]***************************
Table | departments_1
Create Table | CREATE TABLE `departments_1` (
`dept_no` char(4) NOT NULL,
`dept_name` varchar(40) DEFAULT N
|