对于explain extended 查看执行计划里面的一些信息作为一个DBA还是必须掌握的。
环境： MySQL5.6.36
默认字符集： utf8
一、前置回顾：
1、数值型的字段长度
字段类型 长度 UNSIGNED SIGNED有符号型 适用场合
tinyint: 1bytes 2^8-1 0-255 -128~127 小整数值
smallint: 2bytes 2^16-1 0-65535 -32768~32767 大整数值
mediumint: 3bytes 2^24-1 0-16777215 -8388608~8388607 大整数值
int: 4bytes 2^32-1 0-4294967295 -2147483648-2147483647 大整数值(最大只能存10位数字)
bigint: 8bytes 2^64-1 大整数值
float 4bytes 单精度浮点型
double 8bytes 双精度浮点型
2、char和varchar型的字段长度
char和varchar是日常使用最多的字符类型。char（N）用于保存固定长度的字符串，长度最大为255，比指定长度大的值将被截短，而比指定长度小的值将会用空格进行填补。
varchar（N）用于保存可以变长的字符串，长度最大为65535，只存储字符串实际实际需要的长度（它会增加一个额外字节来存储字符串本身的长度），varchar使用额外的1~2字节来存储值的的长度，如果列的最大长度小于或者等于255，则用1字节，否则用2字节。
char和varchar跟字符编码也有密切的联系，latin1占用1个字节，gbk占用2个字节，utf8占用3个字节。（不同字符编码占用的存储空间不同）
3、日期&时间类型的字段长度
DataType Storage Required Before MySQL 5.6.4 Storage Requiredas of MySQL 5.6.4
YEAR 1byte 1byte
DATE 3bytes 3bytes
TIME 3bytes 3 bytes + fractional seconds storage
DATETIME 8bytes 5 bytes + fractional seconds storage
TIMESTAMP 4bytes 4 bytes + fractional seconds storage
二、创建测试用表：
> use test;
> CREATE TABLE `t1` (
`a`int(11) NULL,
`aa` int(11) NOT NULL,
`b`char(10) NULL,
`bb` char(10) NOT NULL,
`c`varchar(20) NULL,
`cc` varchar(20) NOT NULL,
`d`time NULL,
`dd` time NOT NULL,
`e`timestamp NULL,
`ee` timestamp NOT NULL,
`f`datetime NULL,
`ff` datetime NOT NULL,
KEY`index_a` (`a`),
KEY`index_aa` (`aa`),
KEY`index_b` (`b`),
KEY`index_bb` (`bb`),
KEY`index_c` (`c`),
KEY`index_cc` (`cc`),
KEY`index_d` (`d`),
KEY`index_dd` (`dd`),
KEY`index_e` (`e`),
KEY`index_ee` (`ee`),
KEY`index_f` (`f`),
KEY`index_ff` (`ff`)
) ENGINE=InnoDB CHARSET=utf8 ;
上面创建了一张奇怪的表，基本上覆盖了常见的字段类型了，每个字段都加了索引。
> insert into t1 values(1,11,'2','22','3','33','12:23:12','02:11:45','2017-02-12 01:00:12','2015-04-2121:09:12','2015-12-21 21:09:12','2021-04-21 01:45:56') ;
> insert into t1 values(12,131,'62','262','38','332','22:23:12','02:18:45','2017-12-1201:34:12','2005-04-21 21:09:12','2065-12-21 21:09:12','1949-10-01 01:45:56') ;
> explain extended select * from t1where a=12;
+----+-------------+-------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | type |possible_keys | key | key_len |ref | rows | filtered | Extra |
+----+-------------+-------+------+---------------+---------+---------+-------+------+----------+-------+
| 1| SIMPLE | t1 | ref | index_a | index_a | 5 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------+---------------+---------+---------+-------+------+----------+-------+
4（int占4bytes）+ 1 (1byte用来标记是否为null)
> explain extended select * from t1where aa=12;
+----+-------------+-------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | type |possible_keys | key | key_len |ref | rows | filtered | Extra |
+----+-------------+-------+------+---------------+----------+---------+-------+------+----------+-------+
| 1| SIMPLE | t1 | ref | index_aa | index_aa | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------+---------------+----------+---------+-------+------+----------+-------+
4（int占4bytes）
> explain extended select * from t1where b='abc';
+----+-------------+-------+------+---------------+---------+---------+-------+------+----------+-----------------------+
| id | select_type | table | type |possible_keys | key | key_len |ref | rows | filtered | Extra |
+----+-------------+-------+------+---------------+---------+---------+-------+------+----------+-----------------------+
| 1| SIMPLE | t1 | ref | index_b | index_b | 31 | const | 1 | 100.00 | Using index condition |
+----+-------------+-------+------+---------------+---------+---------+-------+------+----------+-----------------------+
10*3 （char每个字符在utf字符集下占3bytes）+ 1 (1byte用来标记是否为null)
> explain extended select * from t1where bb='stfdg4';
+----+-------------+-------+------+---------------+----------+---------+-------+------+----------+-----------------------+
| id | select_type | table | type |possible_keys | key | key_len |ref | rows | filtered | Extra |
+----+-------------+-------+------+---------------+----------+---------+-------+------+----------+-----------------------+
| 1| SIMPLE | t1 | ref | index_bb | index_bb |30 | const | 1 | 100.00 | Using index condition |
+----+-------------+-------+------+---------------+----------+---------+-------+------+----------+-----------------------+
10*3 （char每个字符在utf字符集下占3bytes）
> explain extended select * from t1where c='hfdg';
+----+-------------+-------+------+---------------+---------+---------+-------+------+----------+-----------------------+
| id | select_type | table | type |possible_keys | key | key_len |ref | rows | filtered | Extra |
+----+-------------+-------+------+---------------+---------+---------+-------+------+----------+-----------------------+
| 1| SIMPLE | t1 | ref | index_c | index_c | 63 | const | 1 | 100.00 | Using index condition |
+----+-------------+-------+------+---------------+---------+---------+-------+------+----------+-----------------------+
20*3（varchar每个字符在utf字符集下占3bytes） + 2 (2bytes用来记录varchar这种变长字符的长度) + 1 (1byte用来标记是否为null)
> explain extended select * from t1where cc='fgd';
+----+-------------+-------+------+---------------+----------+---------+-------+------+----------+-----------------------+
| id | select_type | table | type |possible_keys | key | key_len |ref | rows | filtered | Extra |
+----+-------------+-------+------+---------------+----------+---------+-------+------+----------+-----------------------+
| 1| SIMPLE | t1 | ref | index_cc | index_cc | 62 | const | 1 | 100.00 | Using index condition |
+----+-------------+-------+------+---------------+----------+---------+-------+------+----------+-----------------------+
20*3（varchar每个字符在utf字符集下占3bytes） + 2 (2bytes用来记录varchar这种变长字符的长度)
> explain extended select * from t1where d='01:02:02';
+----+-------------+-------+------+---------------+---------+---------+-------+------+----------+-----------------------+
| id | select_type | table | type |possible_keys | key | key_len |ref | rows | filtered | Extra |
+----+-------------+-------+------+---------------+---------+---------+-------+------+----------+-----------------------+
| 1| SIMPLE | t1 | ref | index_d | index_d | 4 | const | 1 | 100.00 | Using index condition |
+----+-------------+-------+------+---------------+---------+---------+-------+------+----------+-----------------------+
3 (time 类型占据3bytes) + 1 (1byte用来标记是否为null)
> explain extended select * from t1where dd='01:02:02';
+----+-------------+-------+------+---------------+----------+---------+-------+------+----------+-----------------------+
| id | select_type | table | type |possible_keys | key | key_len |ref | rows | filtered | Extra |
+----+-------------+-------+------+---------------+----------+---------+-------+------+----------+-----------------------+
| 1| SIMPLE | t1 | ref | index_dd | index_dd |3 | const | 1 | 100.00 | Using index condition |
+----+-------------+-------+------+---------------+----------+---------+-------+------+----------+-----------------------+
3 (time 类型占据3bytes)
> explain extended select * from t1where e='2017-02-11 01:02:02';
+----+-------------+-------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | type |possible_keys | key | key_len |ref | rows | filtered | Extra |
+----+-------------+-------+------+---------------+---------+---------+-------+------+----------+-------+
| 1| SIMPLE | t1 | ref | index_e | index_e | 5 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------+---------------+---------+---------+-------+------+----------+-------+
4 (timestamp 类型占据4bytes) + 1 (1byte用来标记是否为null)
> explain extended select * from t1where ee='2017-02-11 01:02:02';
+----+-------------+-------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | type |possible_keys | key | key_len |ref | rows | filtered | Extra |
+----+-------------+-------+------+---------------+----------+---------+-------+------+----------+-------+
| 1| SIMPLE | t1 | ref | index_ee | index_ee | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------+---------------+----------+---------+-------+------+----------+-------+
4 (timestamp 类型占据4bytes)
> explain extended select * from t1where f='2017-02-11 01:02:02';
+----+-------------+-------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | type |possible_keys | key | key_len |ref | rows | filtered | Extra |
+----+-------------+-------+------+---------------+---------+---------+-------+------+----------+-------+
| 1| SIMPLE | t1 | ref | index_f | index_f | 6 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------+---------------+---------+---------+-------+------+----------+-------+
5 (datetime 类型占据5bytes) + 1 (1byte用来标记是否为null)
> explain extended select * from t1where ff='2017-02-11 01:02:02';
+----+-------------+-------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | type |possible_keys | key | key_len |ref | rows | filtered | Extra |
+----+-------------+-------+------+---------------+----------+---------+-------+------+----------+-------+
| 1| SIMPLE | t1 | ref | index_ff | index_ff |5 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------+---------------+----------+---------+-------+------+----------+-------+
5 (datetime 类型占据5bytes)
联合索引的key_len的计算：
先去掉上面的2个单列索引，防止执行计划跑偏，然后加上一个联合索引：
alter table t1 drop index index_c;
alter table t1 drop index index_d;
alter table t1 add index c_d (c,d);
> explain extended select * from t1where c='aaa' and d='12:21:11';
+----+-------------+-------+------+---------------+------+---------+-------------+------+----------+-----------------------+
| id | select_type | table | type |possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------+---------------+------+---------+-------------+------+----------+-----------------------+
| 1| SIMPLE | t1 | ref | c_d | c_d | 67 | const,const | 1 | 100.00 | Using index condition |
+----+-------------+-------+------+---------------+------+---------+-------------+------+----------+-----------------------+
`c`列 varchar(20)DEFAULT NULL,
`d`列 timeDEFAULT NULL,
key_len = （20*3+2+1）+（3+1）= 67
总结1：
char和varchar类型key_len计算公式:
varchr(N)变长字段且允许NULL = N *( character set：utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)
varchr(N)变长字段且不允许NULL = N * ( character set：utf8=3,gbk=2,latin1=1)+2(变长字段)
char(N)固定字段且允许NULL = N* ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)
char(N)固定字段且允许NULL = N* ( character set：utf8=3,gbk=2,latin1=1)
数值数据的key_len计算公式：
TINYINT允许NULL = 1 +1(NULL)
TINYINT不允许NULL = 1
SMALLINT允许为NULL =2+1(NULL)
SMALLINT不允许为NULL = 2
INT允许为NULL =4+1(NULL)
INT不允许为NULL = 4
BIGINT允许为NULL = 8+1(NULL)
BIGINT不允许为NULL = 8
日期时间型的key_len计算：（针对mysql5.5及之前版本）
DATETIME允许为NULL= 8 + 1(NULL)
DATETIME不允许为NULL = 8
TIMESTAMP允许为NULL = 4+ 1(NULL)
TIMESTAMP不允许为NULL = 4
日期时间型的key_len计算：（针对mysql5.6.4及之后的版本）
DATETIME允许为NULL= 5 + 1(NULL)
DATETIME不允许为NULL = 5
TIMESTAMP允许为NULL = 4+ 1(NULL)
TIMESTAMP不允许为NULL = 4
总结2：
1、从上面几个测试可以看出，假如我们建表时候使用varchar(20),varchar(255) 虽然实际存放的内容长度是一样的。但是这2种情况下索引占据的长度是不一样的。
MySQL建立索引时如果没有限制索引的大小，索引长度会默认采用的该字段的长度，
也就是说varchar(20)和varchar(255)对应的索引长度分别为20*3(utf-8)(+2+1),255*3(utf-8)(+2+1)，
其中"+2"用来存储长度信息，“+1”用来标记是否为空。
加载索引信息时用varchar(255)类型会占用更多的内存。
2、另外，我们可以看到如果建表的时候，字段设置为DEFAULT NULL，会导致索引的长度增加1bytes，此外，NULL列会导致索引失效。
补充：
查看test库中的行数量、数据大小、索引大小等统计情况：
> SELECTCONCAT(table_schema,'.',table_name) AS 'Table Name',CONCAT(ROUND(table_rows/1000000,4),'M') AS 'Number of Rows',
CONCAT(ROUND(data_length/(1024*1024*1024),4),'G')AS 'Data Size',
CONCAT(ROUND(index_length/(1024*1024*1024),4),'G')AS 'Index Size',
CONCAT(ROUND((data_length+index_length)/(1024*1024*1024),4),'G')AS'Total'
FROMinformation_schema.TABLES
WHEREtable_schema LIKE 'test';
+------------+----------------+-----------+------------+---------+
| Table Name | Number of Rows | Data Size |Index Size | Total |
+------------+----------------+-----------+------------+---------+
| test.a | 0.0164M | 0.0015G | 0.0020G | 0.0034G |
| test.b | 0.0165M | 0.0015G | 0.0000G | 0.0015G |
| test.c | 0.3594M | 0.0210G | 0.0000G | 0.0210G |
+------------+----------------+-----------+------------+---------+
3 rows in set (0.00 sec)
