- 数据库
- mysql 优化利器 EXPLAIN （一）
工欲善其事，必先利其器
### Explain 介绍
id
select_type
table
type
possible_keys
key
key_len
ref
rows
Extra
### Explain extended 选项介绍
#### 环境准备
##### 1. MySQL版本
mysql> select version();
+------------------+
| version() |
+------------------+
| 5.6.16.7 |
+------------------+
##### 2. 测试表
| people | CREATE TABLE `people` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`zipcode` char(32) NOT NULL DEFAULT '',
`address` varchar(128) NOT NULL DEFAULT '',
`lastname` char(64) NOT NULL DEFAULT '',
`firstname` char(64) NOT NULL DEFAULT '',
`birthdate` char(10) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `zipcode` (`zipcode`,`firstname`,`lastname`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |
| people_car | CREATE TABLE `people_car` (
`people_id` bigint(20) DEFAULT NULL,
`plate_number` varchar(16) NOT NULL DEFAULT '',
`engine_number` varchar(16) NOT NULL DEFAULT '',
`lasttime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
##### 3. 测试数据
mysql> insert into people
-> (zipcode,address,lastname,firstname,birthdate)
-> values
-> ('230031','anhui','zhan','jindong','1989-09-15'),
-> ('100000','beijing','zhang','san','1987-03-11'),
-> ('200000','shanghai','wang','wu','1988-08-25');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> insert into people_car
-> (people_id,plate_number,engine_number,lasttime)
-> values
-> (1,'A121311','12121313','2013-11-23 :21:12:21'),
-> (2,'B121311','1S121313','2011-11-23 :21:12:21'),
-> (3,'C121311','1211SAS1','2012-11-23 :21:12:21');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
