准备工作  

CREATE TABLE `test_keylen` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `number_int_null` int(11) DEFAULT NULL COMMENT 'int类型 允许NULL',
  `varchar_utf8` varchar(20) CHARACTER SET utf8 NOT NULL COMMENT 'varchar类型 utf8 不允许NULL',
  `varchar_utf8_null` varchar(20) CHARACTER SET utf8 DEFAULT NULL COMMENT 'varchar类型 utf8 允许NULL',
  `varchar_utf8mb4` varchar(20) NOT NULL COMMENT 'varchar类型 utf8mb4 不允许NULL',
  `char_utf8mb4` char(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_int_null` (`number_int_null`),
  KEY `idx_bigint` (`number_bigint`),
  KEY `idx_varchar_utf8` (`varchar_utf8`),
  KEY `idx_varchar_utf8_null` (`varchar_utf8_null`),
  KEY `idx_varchar_utf8mb4` (`varchar_utf8mb4`),
  KEY `idx_char_utf8` (`char_utf8mb4`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

ps: 自行插入数据。

相关知识

  数据类型和长度

在使用MySQL时常用的数据类型有int、bigint、char、varchar、date、datetime、timestamp、float、double,每种类型和长度见下表:

  Explain的 ken_len  计算

key_len的长度计算公式扩展:

bigint(20)整形字段且允许NULL    =  8 +1(NULL)
bigint(20)整形字段且不允许NULL =  8
int(20)整形字段且允许NULL         =  4+1(NULL)
int(20)整形字段且不允许NULL      =  4

varchr(10)变长字段且允许NULL    =  10 * ( character set:utf8mb4=4,utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)
varchr(10)变长字段且不允许NULL =  10 *( character set:
utf8mb4=4,utf8=3,gbk=2,latin1=1)+2(变长字段)

char(10)固定字段且允许NULL        =  10 * ( character set:
utf8mb4=4,utf8=3,gbk=2,latin1=1)+1(NULL)
char(10)固定字段且不允许NULL     =  10 * ( character set:
utf8mb4=4,utf8=3,gbk=2,latin1=1)

TINYINT允许NULL = 1 + 1(NULL)

TINYINT不允许NULL = 1

SMALLINT允许为NULL = 2+1(NULL)

SMALLINT不允许为NULL = 2

DATETIME允许为NULL =  8 + 1(NULL)

DATETIME不允许为NULL = 8

TIMESTAMP允许为NULL = 4 + 1(NULL)

TIMESTAMP不允许为NULL = 4

论证

  NULL占1字节

比较 两个int类型,区别在允不允许null。

EXPLAIN SELECT `id` from `test_keylen` where `number_int_null`=100;
EXPLAIN SELECT `id` from `test_keylen` where `id`=1;

结论

示例

key_len字节

int(11) unsigned NOT NULL

4

int(11) DEFAULT NULL

4+1

  字符集utf8和utf8mb4

比较 两个varchar类型,区别在字符集不同。

EXPLAIN SELECT `id` from `test_keylen` where `varchar_utf8`='100';
EXPLAIN SELECT `id` from `test_keylen` where `varchar_utf8mb4`='100';

结论

示例

key_len字节

varchar(20) CHARACTER SET utf8 NOT NULL

20*3+2(变长字段)

varchar(20) CHARACTER SET utf8mb4 NOT NULL

20*4+2(变长字段

  变长字段

比较 两个相同字符集,相同单位,但类型不同,一个定长char,一个变长varchar。

EXPLAIN SELECT `id` from `test_keylen` where `varchar_utf8mb4`='100';
EXPLAIN SELECT `id` from `test_keylen` where `char_utf8mb4`='100';

结论

示例

key_len字节

varchar(20) CHARACTER SET utf8mb4 NOT NULL

20*4+2(变长字段)

char(20) CHARACTER SET utf8mb4 NOT NULL

20*4

总结

  • 变长类型varchar,额外占用2字节。(与存储占用字节没关系,无论是>255亦或是<255)
  • null需要额外消耗1字节。
  • 不同类型与各自的占用的存储空间有关。
03-17 16:33