浅谈mysql explain中key_len的计算方法
mysql的explain命令可以分析sql的性能,其中有一项是key_len(索引的长度)的统计。本文将分析mysqlexplain中key_len的计算方法。
1、创建测试表及数据
CREATETABLE`member`( `id`int(10)unsignedNOTNULLAUTO_INCREMENT, `name`varchar(20)DEFAULTNULL, `age`tinyint(3)unsignedDEFAULTNULL, PRIMARYKEY(`id`), KEY`name`(`name`) )ENGINE=InnoDBDEFAULTCHARSET=utf8; INSERTINTO`member`(`id`,`name`,`age`)VALUES(NULL,'fdipzone','18'),(NULL,'jim','19'),(NULL,'tom','19');
2、查看explain
name的字段类型是
mysql>explainselect*from`member`wherename='fdipzone'; +----+-------------+--------+------+---------------+------+---------+-------+------+-----------------------+ |id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra| +----+-------------+--------+------+---------------+------+---------+-------+------+-----------------------+ |1|SIMPLE|member|ref|name|name|63|const|1|Usingindexcondition| +----+-------------+--------+------+---------------+------+---------+-------+------+-----------------------+
explain的key_len为
name字段是允许NULL,把name改为NOTNULL再测试
ALTERTABLE`member`CHANGE`name``name`VARCHAR(20)NOTNULL; mysql>explainselect*from`member`wherename='fdipzone'; +----+-------------+--------+------+---------------+------+---------+-------+------+-----------------------+ |id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra| +----+-------------+--------+------+---------------+------+---------+-------+------+-----------------------+ |1|SIMPLE|member|ref|name|name|62|const|1|Usingindexcondition| +----+-------------+--------+------+---------------+------+---------+-------+------+-----------------------+
现在key_len为62,比刚才少了1,但还是多了2。可以确定,字段为NULL会多占用一个字节。
name字段类型为varchar,属于变长字段,把varchar改为char再测试
ALTERTABLE`member`CHANGE`name``name`CHAR(20)NOTNULL; mysql>explainselect*from`member`wherename='fdipzone'; +----+-------------+--------+------+---------------+------+---------+-------+------+-----------------------+ |id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra| +----+-------------+--------+------+---------------+------+---------+-------+------+-----------------------+ |1|SIMPLE|member|ref|name|name|60|const|1|Usingindexcondition| +----+-------------+--------+------+---------------+------+---------+-------+------+-----------------------+
改为定长字段后,key_len为
总结:使用变长字段需要额外增加
以上这篇浅谈mysqlexplain中key_len的计算方法就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持毛票票。
热门推荐
1 训练狗狗憋尿
2 狗狗追踪训练
3 狗狗训练要多少钱
5 深圳狗狗训练
6 训练狗狗唤回
7 狗狗叫怎么训练
8 狗狗训练班
10 狗狗社会化训练
11 狗狗叫回训练
12 广州狗狗训练学校
13 狗狗训练躺下
14 狗狗随行训练
15 训练狗狗巡回
16 狗狗要怎么训练
17 狗狗喂食训练
18 训练狗狗躺下