56,681
社区成员
发帖
与我相关
我的任务
分享explain的结果中关于key_len的一个疑问
mysql@stat1.db.db_monitor>explain select * from test where value1='';
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+
| 1 | SIMPLE | test | ref | idx_value1_value2 | idx_value1_value2 | 13 | const | 1 | Using where |
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+
1 row in set (0.00 sec)
mysql@stat1.db.db_monitor>desc test;
+--------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+----------------+
| id | tinyint(4) | NO | PRI | NULL | auto_increment |
| value | tinyint(4) | YES | | NULL | |
| value1 | char(4) | YES | MUL | NULL | |
| value2 | char(4) | YES | | NULL | |
+--------+------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
这个key_len为啥是13呢？
show create table test;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test | CREATE TABLE `test` (
`id` tinyint(4) NOT NULL auto_increment,
`value` tinyint(4) default NULL,
`value1` char(4) collate utf8_bin default NULL,
`value2` char(4) collate utf8_bin default NULL,
PRIMARY KEY (`id`),
KEY `idx_value1_value2` (`value1`,`value2`)
) ENGINE=InnoDB AUTO_INCREMENT=128 DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+
| 1 | SIMPLE | test | ref | idx_value1_value2 | idx_value1_value2 | 13 | const | 1 | Using where |
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+
1 row in set (0.00 sec)
mysql@stat1.db.db_monitor>desc test;
+--------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+----------------+
| id | tinyint(4) | NO | PRI | NULL | auto_increment |
| value | tinyint(4) | YES | | NULL | |
| value1 | char(4) | YES | MUL | NULL | |
| value2 | char(4) | YES | | NULL | |
+--------+------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
这个key_len为啥是13呢？
show create table test;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test | CREATE TABLE `test` (
`id` tinyint(4) NOT NULL auto_increment,
`value` tinyint(4) default NULL,
`value1` char(4) collate utf8_bin default NULL,
`value2` char(4) collate utf8_bin default NULL,
PRIMARY KEY (`id`),
KEY `idx_value1_value2` (`value1`,`value2`)
) ENGINE=InnoDB AUTO_INCREMENT=128 DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
...全文
请发表友善的回复…
发表回复
曾经的阿飞 2010-07-29
- 打赏
- 举报
谢谢，又明白了一个事情
loveflea 2010-07-28
- 打赏
- 举报
这个length是字节长度
value1 | char(4) utf8 4*3=12字节+1字节null
When a nullable column is indexed, it requires an extra
byte per entry
value1 | char(4) utf8 4*3=12字节+1字节null
When a nullable column is indexed, it requires an extra
byte per entry
曾经的阿飞 2010-07-28
- 打赏
- 举报
[Quote=引用 1 楼 acmain_chm 的回复:]
KEY `idx_value1_value2` (`value1`,`value2`)
| value1 | char(4)
| value2 | char(4)
这两个字段就8个长度了吧，索引中还会包括5字节长的定位信息。
[/Quote]
我的char_set是utf-8，按理说是1-4个字节，应该是4*4*2=32
另外我还试验了一下了单个tinyint字段的索引，观察length是1，另外关于“定位”信息文档上有描述吗？找了半天没有任何资料
KEY `idx_value1_value2` (`value1`,`value2`)
| value1 | char(4)
| value2 | char(4)
这两个字段就8个长度了吧，索引中还会包括5字节长的定位信息。
[/Quote]
我的char_set是utf-8，按理说是1-4个字节，应该是4*4*2=32
另外我还试验了一下了单个tinyint字段的索引，观察length是1，另外关于“定位”信息文档上有描述吗？找了半天没有任何资料
shine333 2010-07-28
- 打赏
- 举报
[Quote=引用 3 楼 loveflea 的回复:]
这个length是字节长度
value1 | char(4) utf8 4*3=12字节+1字节null
When a nullable column is indexed, it requires an extra
byte per entry
[/Quote]
试了不同的create，确实如此。
之所以value2没有起作用(ref=const)，是因为你的select用不到value2，假如
那么value2就起作用了(ref=const,const)，长度也要计算value2，比如，我设置value1 char(5) not null,
则
而
这个length是字节长度
value1 | char(4) utf8 4*3=12字节+1字节null
When a nullable column is indexed, it requires an extra
byte per entry
[/Quote]
试了不同的create，确实如此。
之所以value2没有起作用(ref=const)，是因为你的select用不到value2，假如
explain select * from test where value1='' and value2='';那么value2就起作用了(ref=const,const)，长度也要计算value2，比如，我设置value1 char(5) not null,
则
explain select * from test where value1='';而
explain select * from test where value1='' and value2='';
ACMAIN_CHM 2010-07-27
- 打赏
- 举报
KEY `idx_value1_value2` (`value1`,`value2`)
| value1 | char(4)
| value2 | char(4)
这两个字段就8个长度了吧，索引中还会包括5字节长的定位信息。
| value1 | char(4)
| value2 | char(4)
这两个字段就8个长度了吧，索引中还会包括5字节长的定位信息。
