前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >MYSQL EXPLAIN 中的KEY_LEN的说明

MYSQL EXPLAIN 中的KEY_LEN的说明

作者头像
保持热爱奔赴山海
发布2019-09-18 11:14:39
1.4K0
发布2019-09-18 11:14:39
举报
文章被收录于专栏:数据库相关

对于explain extended 查看执行计划里面的一些信息作为一个DBA还是必须掌握的。

参考博文:http://www.cnblogs.com/xuanzhi201111/p/4554769.html

环境: 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)

本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
原始发表:2017/07/15 ,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
云数据库 MySQL
腾讯云数据库 MySQL(TencentDB for MySQL)为用户提供安全可靠,性能卓越、易于维护的企业级云数据库服务。其具备6大企业级特性,包括企业级定制内核、企业级高可用、企业级高可靠、企业级安全、企业级扩展以及企业级智能运维。通过使用腾讯云数据库 MySQL,可实现分钟级别的数据库部署、弹性扩展以及全自动化的运维管理,不仅经济实惠,而且稳定可靠,易于运维。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档