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