MySQL执行计划-轻识

MySQL执行计划

前言

在实际数据库项目开发中，由于我们不知道实际查询时数据库里发生了什么，也不知道数据库是如何扫描表、如何使用索引的，因此，我们能感知到的就只有SQL语句的执行时间。尤其在数据规模比较大的场景下，如何写查询、优化查询、如何使用索引就显得很重要了。

那么，问题来了，在查询前有没有可能估计下查询要扫描多少行、使用哪些索引呢？

答案是肯定的。以MySQL为例，MySQL通过explain命令输出执行计划，对要执行的查询进行分析。

什么是执行计划

简单来说，就是SQL在数据库中执行时的表现情况，通常用于SQL性能分析、优化等场景。

从MySQL的逻辑结构讲解，过渡到MySQL的查询过程，然后给出执行计划的例子并重点介绍执行计划的输出参数，从而理解为什么我们会选择文中建议的方案。

MySQL逻辑架构

客户端

如，连接处理、授权认证、安全等功能

核心服务

MySQL大多数核心服务均在这一层
包括查询解析、分析、优化、缓存、内置函数（如，时间、数学、加密等）
所有的跨存储引擎的功能也在这一层，如，存储过程、触发器、视图等

存储引擎

负责MySQL中的数据存储和读取
中间的服务层通过API与存储引擎通信，这些API屏蔽了不同存储引擎间的差异

查询缓存

对于select语句，在解析查询之前，服务器会先检查查询缓存（Query Cache）。如果命中，服务器便不再执行查询解析、优化和执行的过程，而是直接返回缓存中的结果集。

MySQL查询过程

如果能搞清楚MySQL是如何优化和执行查询的，对优化查询一定会有帮助。很多查询优化实际上就是遵循一些原则让优化器能够按期望的合理的方式运行。

下图是MySQL执行一个查询的过程。实际上每一步都比想象中的复杂，尤其优化器，更复杂也更难理解。本文只给予简单的介绍。

MySQL查询过程

客户端将查询发送到MySQL服务器
服务器先检查查询缓存，如果命中，立即返回缓存中的结果；否则进入下一阶段
服务器对SQL进行解析、预处理，再由优化器生成对象的执行计划
MySQL根据优化器生成的执行计划，调用存储引擎API来执行查询
服务器将结果返回给客户端，同时缓存查询结果

执行计划

执行计划的作用

表的读取顺序
数据读取操作的操作类型
哪些索引可以使用
哪些索引被实际使用
表之间的引用
每张表有多少行被优化器查询

以上的这些作用会在执行计划详解里面介绍到，在这里不做解释。

优化与执行

MySQL会解析查询，并创建内部数据结构（解析树），并对其进行各种优化，包括重写查询、决定表的读取顺序、选择合适的索引等。

用户可通过关键字提示（hint）优化器，从而影响优化器的决策过程。也可以通过通过优化器解释（explain）优化过程的各个因素，使用户知道数据库是如何进行优化决策的，并提供一个参考基准，便于用户重构查询和数据库表的schema、修改数据库配置等，使查询尽可能高效。

语法

执行计划的语法其实非常简单：在SQL查询的前面加上EXPLAIN关键字就行。

比如：EXPLAIN select * from table1，重点的就是EXPLAIN后面你要分析的SQL语句。

准备工作

导入数据表

COPY

              
              
DROP TABLE IF EXISTS `course`;
              

              
CREATE TABLE `course` (
              
  `cid` int(11) NOT NULL AUTO_INCREMENT,
              
  `cname` varchar(100) NOT NULL,
              
  `xuefen` int(11) DEFAULT NULL,
              
  `tid` int(11) DEFAULT NULL,
              
  PRIMARY KEY (`cid`)
              
) ENGINE=InnoDB AUTO_INCREMENT=1005 DEFAULT CHARSET=utf8mb4;
              

              
/*Data for the table `course` */
              

              
insert  into `course`(`cid`,`cname`,`xuefen`,`tid`) values (1001,'C++',3,101),(1002,'java',5,101),(1003,'相声表演',2,102),(1004,'电子商务',3,103);
              

              
/*Table structure for table `students` */
              

              
DROP TABLE IF EXISTS `students`;
              

              
CREATE TABLE `students` (
              
  `sid` int(11) NOT NULL AUTO_INCREMENT,
              
  `sname` varchar(100) NOT NULL,
              
  `age` int(11) DEFAULT NULL,
              
  `address` varchar(100) DEFAULT NULL,
              
  `courseid` int(11) DEFAULT NULL,
              
  PRIMARY KEY (`sid`)
              
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;
              

              
/*Data for the table `students` */
              

              
insert  into `students`(`sid`,`sname`,`age`,`address`,`courseid`) values (1,'小海子',23,'北京',1003),(2,'小沈阳',45,'沈阳',1003),(3,'刘阳',25,'山东',1002),(4,'甘能',22,'广东',1002);
              

              
/*Table structure for table `teacher` */
              

              
DROP TABLE IF EXISTS `teacher`;
              

              
CREATE TABLE `teacher` (
              
  `tid` int(5) NOT NULL AUTO_INCREMENT,
              
  `tname` varchar(100) NOT NULL,
              
  `age` int(4) DEFAULT NULL,
              
  `address` varchar(100) DEFAULT NULL,
              
  `courseid` int(11) DEFAULT NULL,
              
  PRIMARY KEY (`tid`)
              
) ENGINE=InnoDB AUTO_INCREMENT=104 DEFAULT CHARSET=utf8mb4;
              

              
/*Data for the table `teacher` */
              

              
insert  into `teacher`(`tid`,`tname`,`age`,`address`,`courseid`) values (101,'马云',50,'杭州',NULL),(102,'赵本山',52,'沈阳',NULL),(103,'刘强东',45,'北京',NULL);

执行计划详解

通过EXPLAIN关键分析的结果由以下列组成，接下来挨个分析每一个列

1	COPY explain select * from account;

ID列

描述select查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序

根据ID的数值结果可以分成一下三种情况

id相同

执行顺序由上至下

COPY

              EXPLAIN 
              
SELECT 
              
  * 
              

                FROM
              
              
  `students` a 
              
  LEFT JOIN `course` b 
              
    ON a.courseid = b.cid 
              
  LEFT JOIN `teacher` c 
              
    ON b.tid = c.tid

我们发现这几个的id都是一样的那他们就会顺序向下执行

id不同

如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

COPY

              EXPLAIN 
              
SELECT 
              
  * 
              

                FROM
              
              
  `students` 
              
WHERE courseid = 
              
  (SELECT 
              
    cid 
              
  FROM
              
    `course` 
              
  WHERE tid = 
              
    (SELECT 
              
      tid 
              
    FROM
              
      `teacher` 
              
    WHERE tname = '马云'))

我们发现这几个id是从小到大的，那么按照执行顺序应该是从大到小先执行teacher然后course 最后是students

id相同不同(两种情况同时存在)

id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

COPY

              EXPLAIN 
              
SELECT 
              
  * 
              

                FROM
              
              
  `students` 
              
WHERE courseid = 
              
  (SELECT 
              
    cid 
              
  FROM
              
    `course` a 
              
    LEFT JOIN teacher b 
              
      ON a.tid = b.tid 
              
  WHERE b.tname = '赵本山')

我们发现有两个id是2 的一个1 先按照从大到小先执行id是2的 2是相同的就按照顺序向下执行先执行 b 在执行a z最后执行students。

select_type列

查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询

SIMPLE类型

简单的 select 查询，查询中不包含子查询或者UNION

COPY

PRIMARY与SUBQUERY类型

PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为

SUBQUERY：在SELECT或WHERE列表中包含了子查询

COPY

              EXPLAIN 
              
SELECT 
              
  teacher.*,
              
  (SELECT 
              
    students.sid 
              
  FROM
              
    students 
              
  WHERE students.sid = 1) 
              

                FROM
              
              
  teacher

DERIVED类型

在FROM列表中包含的子查询被标记为DERIVED(衍生)，MySQL会递归执行这些子查询, 把结果放在临时表里。

COPY

              EXPLAIN 
              
SELECT 
              
  t1.* 
              

                FROM
              
              
  students t1,
              
  (SELECT 
              
    t2.* 
              
  FROM
              
    course t2 
              
  WHERE t2.cid NOT IN (1) 
              
  LIMIT 1) s2 
              
WHERE t1.courseid = s2.cid ;

UNION RESULT 与UNION类型

UNION：若第二个SELECT出现在UNION之后，则被标记为UNION；

UNION RESULT：从UNION表获取结果的SELECT

COPY

              EXPLAIN SELECT 
              
  * 
              

                FROM
              
              
  `students` 
              
WHERE sid = 1 
              

                UNION
              
              
SELECT 
              
  * 
              

                FROM
              
              
  `students` 
              
WHERE sid = 2

table列

显示这一行的数据是关于哪张表的

COPY

Type列

type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

需要记忆的

system>const>eq_ref>ref>range>index>ALL

一般来说，得保证查询至少达到range级别，最好能达到ref。

NULL访问类型

mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表

COPY

              EXPLAIN 
              
SELECT 
              
  MIN(sid)
              

                FROM
              
              
  `students`

System与const访问类型

System：表只有一行记录(等于系统表)，这是const类型的特列，平时不会出现，这个也可以忽略不计

Const：表示通过索引一次就找到了。

const 用于比较primary key或者unique索引。因为只匹配一行数据，所以很快如将主键置于where列表中，MySQL就能将该查询转换为一个常量，读取1次，速度比较快。

1	COPY EXPLAIN SELECT * FROM (SELECT * FROM `teacher` WHERE tid = 101 LIMIT 1) d1;

eq_ref访问类型

唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。

primary key 或 unique key 索引的所有部分被连接使用，最多只会返回一条符合条件的记录。这可能是在 const 之外最好的联接类型了，简单的 select 查询不会出现这种 type。

COPY

              -- 增加索引
              
ALTER TABLE `innodatabase`.`students` 
              
  ADD INDEX `students_courseid_index` (`courseid`) ;
              

              
EXPLAIN 
              
SELECT 
              
  a.* 
              

                FROM
              
              
  `students` a 
              
  LEFT JOIN `course` b 
              
    ON a.courseid = b.cid

Ref访问类型

非唯一性索引扫描，返回匹配某个单独值的所有行。

相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。

本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。

COPY

              EXPLAIN 
              
SELECT 
              
  * 
              

                FROM
              
              
  `students` 
              
WHERE courseid = 1003

ref_or_null访问类型

类似ref，但是可以搜索值为NULL的行。

index_merge访问类型

表示使用了索引合并的优化方法

Range访问类型

范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行

这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。

COPY

              EXPLAIN 
              
SELECT 
              
  age 
              

                FROM
              
              
  students 
              
WHERE age IN(10,20,45,50)

COPY

              EXPLAIN 
              
SELECT 
              
  age 
              

                FROM
              
              
  students 
              
WHERE age BETWEEN 10 
              
  AND 50

Index访问类型

和ALL一样，不同就是mysql只需扫描索引树，这通常比ALL快一些。

当查询的结果全为索引列的时候，虽然也是全部扫描，但是只查询的索引库，而没有去查询数据。

COPY

All访问类型

即全表扫描，意味着mysql需要从头到尾去查找所需要的行。通常情况下这需要增加索引来进行优化了

COPY

              EXPLAIN 
              
SELECT 
              
  * 
              

                FROM
              
              
  students 
              
WHERE address = ''

possible_keys列

这一列显示查询可能使用哪些索引来查找。

explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。

如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能，然后用 explain 查看效果。

COPY

              EXPLAIN 
              
SELECT 
              
  * 
              

                FROM
              
              
  students 
              
WHERE age BETWEEN 10 
              
  AND 100

key列

这一列显示mysql实际采用哪个索引来优化对该表的访问。

如果没有使用索引，则该列是 NULL。如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用 force index、ignore index。

查询中若使用了覆盖索引，则该索引和查询的select字段重叠

COPY

key_len列

这一列显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。

Key_len表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好。

key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的

举例来说，students索引 students_courseid_index由 courseid 一个个int列组成，并且每个int是4字节，并且是可以为null占用一个字节。通过结果中的key_len=4+1=5可推断出查询使用了courseid 列来执行索引查找。

COPY

              EXPLAIN 
              
SELECT 
              
  courseid
              

                FROM
              
              
  students 
              
WHERE courseid = 1003

key_len计算规则如下

字符串
- char(n)：n字节长度
- varchar(n)：2字节存储字符串长度，如果是utf-8，则长度 3n + 2
数值类型

　　
- tinyint：1字节
- smallint：2字节
- int：4字节
- bigint：8字节
时间类型　
- date：3字节
- time：3字节
- year：1字节
- timestamp：4字节
- datetime：8字节
latin1占用1个字节，gbk占用2个字节，utf8占用3个字节。（不同字符编码占用的存储空间不同）****
如果字段允许为 NULL，需要1字节记录是否为 NULL
编码（不同字符编码占用的存储空间不同）
- latin1：1字节
- gbk：2字节
- utf8：3字节

索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。

注意

根据底层使用的不通存储引擎，受影响的行数这个指标可能是一个估计值，也可能是一个精确值。及时受影响的行数是一个估计值（例如当使用InnoDB存储引擎管理表存储时），通常情况下这个估计值也足以使优化器做出一个有充分依据的决定。

字符类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

类型	大小	用途
CHAR	0-255字节	定长字符串
VARCHAR	0-65535 字节	变长字符串
TINYBLOB	0-255字节	不超过 255 个字符的二进制字符串
TINYTEXT	0-255字节	短文本字符串
BLOB	0-65 535字节	二进制形式的长文本数据
TEXT	0-65 535字节	长文本数据
MEDIUMBLOB	0-16 777 215字节	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215字节	中等长度文本数据
LONGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295字节	极大文本数据

CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

以上这个表列出了所有字符类型，但真正建所有的类型常用情况只是CHAR、VARCHAR

索引字段为char类型

n字节长度

不可为Null时

name这一列为char(10),字符集为utf-8占用3个字节

Keylen=10*3

COPY

              CREATE TABLE `s1` (
              
  `id` INT(11) NOT NULL AUTO_INCREMENT,
              
  `name` CHAR(10) NOT NULL,
              
  `addr` VARCHAR(20) DEFAULT NULL,
              
  PRIMARY KEY (`id`),
              
  KEY `name` (`name`)
              
) ENGINE=INNODB DEFAULT CHARSET=utf8;
              

              
EXPLAIN 
              
SELECT 
              
  * 
              

                FROM
              
              
  s1 
              
WHERE NAME = 'enjoy' ;

允许为Null时

name这一列为char(10),字符集为utf-8占用3个字节,外加需要存入一个null值

Keylen=10*3+1(null) 结果为31

COPY

              CREATE TABLE `s2` (
              
  `id` INT(11) NOT NULL AUTO_INCREMENT,
              
  `name` CHAR(10) DEFAULT NULL,
              
  `addr` VARCHAR(20) DEFAULT NULL,
              
  PRIMARY KEY (`id`),
              
  KEY `name` (`name`)
              
) ENGINE=INNODB DEFAULT CHARSET=utf8;
              

              
EXPLAIN 
              
SELECT 
              
  * 
              

                FROM
              
              
  s2 
              
WHERE NAME = 'enjoyedu' ;

索引字段为varchar类型

2字节存储字符串长度，如果是utf-8，则长度 3n + 2

不可为Null时

Keylen=varchar(n)变长字段+不允许Null=n*(utf8=3,gbk=2,latin1=1)+2

COPY

              CREATE TABLE `s3` (
              
  `id` INT(11) NOT NULL AUTO_INCREMENT,
              
  `name` VARCHAR(10) NOT NULL,
              
  `addr` VARCHAR(20) DEFAULT NULL,
              
  PRIMARY KEY (`id`),
              
  KEY `name` (`name`)
              
) ENGINE=INNODB DEFAULT CHARSET=utf8;
              

              
EXPLAIN 
              
SELECT 
              
  * 
              

                FROM
              
              
  s3 
              
WHERE NAME = 'enjoyeud' ;

可为Null时

Keylen=varchar(n)变长字段+允许Null=n*(utf8=3,gbk=2,latin1=1)+1(NULL)+2

COPY

              CREATE TABLE `s4` (
              
  `id` INT(11) NOT NULL AUTO_INCREMENT,
              
  `name` VARCHAR(10) DEFAULT NULL,
              
  `addr` VARCHAR(20) DEFAULT NULL,
              
  PRIMARY KEY (`id`),
              
  KEY `name` (`name`)
              
) ENGINE=INNODB DEFAULT CHARSET=utf8;
              

              
EXPLAIN 
              
SELECT 
              
  * 
              

                FROM
              
              
  s4 
              
WHERE NAME = 'enjoyeud' ;

数值类型

MySQL支持所有标准SQL数值数据类型。

这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。

关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。

BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。

作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 字节	(-128，127)	(0，255)	小整数值
SMALLINT	2 字节	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 字节	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 字节	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 字节	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 字节	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度浮点数值
DOUBLE	8 字节	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

创建表

COPY

              CREATE TABLE `numberKeyLen` (
              
`c0`  INT(255) NOT NULL ,
              
`c1`  TINYINT(255) NULL DEFAULT NULL ,
              
`c2`  SMALLINT(255) NULL DEFAULT NULL ,
              
`c3`  MEDIUMINT(255) NULL DEFAULT NULL ,
              
`c4`  INT(255) NULL DEFAULT NULL ,
              
`c5`  BIGINT(255) NULL DEFAULT NULL ,
              
`c6`  FLOAT(255,0) NULL DEFAULT NULL ,
              
`c7`  DOUBLE(255,0) NULL DEFAULT NULL ,
              
PRIMARY KEY (`c0`),
              
INDEX `index_tinyint` (`c1`) USING BTREE ,
              
INDEX `index_smallint` (`c2`) USING BTREE ,
              
INDEX `index_mediumint` (`c3`) USING BTREE ,
              
INDEX `index_int` (`c4`) USING BTREE ,
              
INDEX `index_bigint` (`c5`) USING BTREE ,
              
INDEX `index_float` (`c6`) USING BTREE ,
              
INDEX `index_double` (`c7`) USING BTREE 
              
)
              
ENGINE=INNODB
              
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
              
ROW_FORMAT=COMPACT
              
;

TINYINT类型

TINYINT类型占用1个字节允许为空占用1个字节

Keylen = 1+1 =2

1	COPY EXPLAIN SELECT * FROM numberKeyLen WHERE c1=1;

SMALLINT类型

SMALLINT类型占用2个字节允许为空占用1个字节

Keylen = 2+1 =3

1	COPY EXPLAIN SELECT * FROM numberKeyLen WHERE c2=1;

MEDIUMINT类型

MEDIUMINT类型占用3个字节允许为空占用1个字节

Keylen = 3+1 =4

1	COPY EXPLAIN SELECT * FROM numberKeyLen WHERE c3=1;

INT类型

INT类型占用4个字节允许为空占用1个字节

Keylen = 4+1 =5

1	COPY EXPLAIN SELECT * FROM numberKeyLen WHERE c4=1;

BIGINT类型

BIGINT类型占用8个字节允许为空占用1个字节

Keylen = 8+1 =9

1	COPY EXPLAIN SELECT * FROM numberKeyLen WHERE c5=1;

FLOAT类型

FLOAT类型占用4个字节允许为空占用1个字节

Keylen = 4+1 =5

1	COPY EXPLAIN SELECT * FROM numberKeyLen WHERE c6=1;

DOUBLE类型

DOUBLE类型占用8个字节允许为空占用1个字节

Keylen = 8+1 =9

1	COPY EXPLAIN SELECT * FROM numberKeyLen WHERE c7=1;

日期和时间

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个”零”值，当指定不合法的MySQL不能表示的值时使用”零”值。

TIMESTAMP类型有专有的自动更新特性，将在后面描述。

类型	大小 (字节)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

注意

datetime类型在5.6中字段长度是5个字节

datetime类型在5.5中字段长度是8个字节

创建表

COPY

              CREATE TABLE `datatimekeylen` (
              
`c1` DATE NULL DEFAULT NULL ,
              
`c2` TIME NULL DEFAULT NULL ,
              
`c3` YEAR NULL DEFAULT NULL ,
              
`c4` DATETIME NULL DEFAULT NULL ,
              
`c5`  TIMESTAMP NULL DEFAULT NULL ,
              
INDEX `index_date` (`c1`) USING BTREE ,
              
INDEX `index_time` (`c2`) USING BTREE ,
              
INDEX `index_year` (`c3`) USING BTREE ,
              
INDEX `index_datetime` (`c4`) USING BTREE ,
              
INDEX `index_timestamp` (`c5`) USING BTREE 
              
)
              
ENGINE=INNODB
              
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
              
ROW_FORMAT=COMPACT
              
;

date类型

date 类型占用3个字节允许为空占用1个字节

Keylen = 3+4 =4

1	COPY EXPLAIN SELECT * FROM datatimekeylen WHERE c1 = 1;

time类型

time 类型占用3个字节允许为空占用1个字节

Keylen = 3+4 =4

1	COPY EXPLAIN SELECT * FROM datatimekeylen WHERE c2 = 1;

year类型

time 类型占用1个字节允许为空占用1个字节

Keylen = 1+1 =2

1	COPY EXPLAIN SELECT * FROM datatimekeylen WHERE c3 = 1;

datetime类型

datetime类型在5.6中字段长度是5个字节

datetime类型占用5个字节允许为空占用1个字节

Keylen = 5+1 =6

1	COPY EXPLAIN SELECT * FROM datatimekeylen WHERE c4 = 1;

TIMESTAMP类型

TIMESTAMP类型占用4个字节允许为空占用1个字节

Keylen = 4+1 =5

1	COPY EXPLAIN SELECT * FROM datatimekeylen WHERE c5 = 1;

总结

字符类型

变长字段需要额外的2个字节（VARCHAR值保存时只保存需要的字符数，另加一个字节来记录长度(如果列声明的长度超过255，则使用两个字节)，所以VARCAHR索引长度计算时候要加2），固定长度字段不需要额外的字节。

而NULL都需要1个字节的额外空间,所以索引字段最好不要为NULL，因为NULL让统计更加复杂并且需要额外的存储空间。

复合索引有最左前缀的特性，如果复合索引能全部使用上，则是复合索引字段的索引长度之和，这也可以用来判定复合索引是否部分使用，还是全部使用。

整数/浮点数/时间类型的索引长度

NOT NULL=字段本身的字段长度

NULL=字段本身的字段长度+1(因为需要有是否为空的标记，这个标记需要占用1个字节)

datetime类型在5.6中字段长度是5个字节，datetime类型在5.5中字段长度是8个字节

Ref列

这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），func，NULL，字段名（例：innodatabase.s1.id）

1 2	COPY EXPLAIN SELECT * FROM s1 ,s2 WHERE s1.id = s2.id AND s1.name = 'enjoy'

由key_len可知s1表的PRIMARY被充分使用，name匹配s2表的name，name匹配了一个常量，即 ‘enjoy’

其中【shared.t2.col1】为【数据库.表.列】

Rows列

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，注意这个不是结果集里的行数。

COPY

              EXPLAIN 
              
SELECT 
              
  * 
              

                FROM
              
              
  students s1,
              
  course s2 
              
WHERE s1.courseid = s2.cid 
              
  AND s1.sname = 'enjoy'

Extra列

包含不适合在其他列中显示但十分重要的额外信息

Using filesort

说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。

MySQL中无法利用索引完成的排序操作称为“文件排序”，当发现有Using filesort 后，实际上就是发现了可以优化的地方。

mysql 会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。此时mysql会根据联接类型浏览所有符合条件的记录，并保存排序关键字和行指针，然后排序关键字并按顺序检索行信息。这种情况下一般也是要考虑使用索引来优化的。

没有索引

未创建索引，会浏览students整个表，保存排序关键字name和对应的id，然后排序name并检索行记录

1	COPY EXPLAIN SELECT * FROM `students` ORDER BY sname;

上图其实是一种索引失效的情况，发现没使用索引建立students.name的索引并使用

加索引

建立了students_name_index索引,此时查询时extra是using index

1	COPY EXPLAIN SELECT sname FROM `students` ORDER BY sname;

我们发现使用了索引，并且索引就是我们创建的students_name_index

Using temporary

mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化，常见于排序 order by 和分组查询 group by。

没有索引

没有索引，此时创建了张临时表来distinct

尤其发现在执行计划里面有using filesort而且还有Using temporary的时候，特别需要注意

1	COPY EXPLAIN SELECT DISTINCT sname FROM `students`;

加索引

建立了students_name_index索引，此时查询时extra是using index,没有用临时表

1	COPY EXPLAIN SELECT DISTINCT sname FROM `students`;

Using index

表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！

这发生在对表的请求列都是同一索引的部分的时候，返回的列数据只使用了索引中的信息，而没有再去访问表中的行记录。是性能高的表现。

1	COPY EXPLAIN SELECT sname FROM `students`;

Using where

mysql服务器将在存储引擎检索行后再进行过滤。就是先读取整行数据，再按 where 条件进行检查，符合就留下，不符合就丢弃。

1	COPY EXPLAIN SELECT * FROM `students` WHERE sid > 1;

impossible where

where子句的值总是false，不能用来获取任何元组

1	COPY EXPLAIN SELECT * FROM `students` WHERE 1=2

1	COPY EXPLAIN SELECT * FROM students WHERE sname ='张三' AND sname = '李四';

博客内容遵循署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0) 协议

本文永久链接是：http://www.baiyp.ren/MySQL%E6%89%A7%E8%A1%8C%E8%AE%A1%E5%88%92.html