转自http://www.amogoo.com/article/3 感谢楼主的贡献
MySQL执行计划之key_len的计算
各种数据类型占用的字节数
TINYINT 1 字节
SMALLINT 2 个字节
MEDIUMINT 3 个字节
INT 4 个字节
BIGINT 8 个字节
FLOAT 4 个字节
DOUBLE 8 个字节
DECIMAL(M,D) M字节(D+2 , 如果M <D)
DATE 3 个字节
DATETIME 8 个字节
TIMESTAMP 4 个字节
TIME 3 个字节
YEAR 1 字节
CHAR(M) M字节,1 <= M <=255
VARCHAR(M) L+1 字节, 在此L <= M和1 <= M<= 255
索引key_len的长度计算公式:
L:索引列所定义字段类型字符长度
C:不同编码下一个字符所占的字节数(如utf8=3,gbk=2)
N:字段为空标记,占1字节(非空字段此标记不占用字节)
S:索引列字段是否定长(INT、CHAR、DATETIME为定长,VARCHAR为不定长),不定长字段类型需记录长度信息,占2字节
key_len = L*C[+N][+S]
建表语句:
1
2
3
4
5
6
7
8
9
10
|
CREATE
TABLE
`employees` (
`emp_no`
int
(11)
NOT
NULL
,
`birth_date`
date
NOT
NULL
,
`first_name`
varchar
(14)
NOT
NULL
,
`last_name`
varchar
(16)
DEFAULT
NULL
,
`gender` enum(
'M'
,
'F'
)
NOT
NULL
,
`hire_date`
date
NOT
NULL
,
PRIMARY
KEY
(`emp_no`),
KEY
`idx_first_last` (`first_name`,`last_name`) USING BTREE
) ENGINE=InnoDB
DEFAULT
CHARSET=utf8;
|
执行计划:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
mysql> explain
select
*
from
employees
where
first_name =
'Tomofumi'
and
last_name =
'Asmuth'
;
+
----+-------------+-----------+------+----------------+----------------+---------+-------------+------+-----------------------+
| id | select_type |
table
| type | possible_keys |
key
| key_len | ref |
rows
| Extra |
+
----+-------------+-----------+------+----------------+----------------+---------+-------------+------+-----------------------+
| 1 | SIMPLE | employees | ref | idx_first_last | idx_first_last | 95 | const,const | 1 | Using
index
condition |
+
----+-------------+-----------+------+----------------+----------------+---------+-------------+------+-----------------------+
1 row
in
set
mysql> explain
select
*
from
employees
where
first_name =
'Tomofumi'
order
by
last_name;
+
----+-------------+-----------+------+----------------+----------------+---------+-------+------+------------------------------------+
| id | select_type |
table
| type | possible_keys |
key
| key_len | ref |
rows
| Extra |
+
----+-------------+-----------+------+----------------+----------------+---------+-------+------+------------------------------------+
| 1 | SIMPLE | employees | ref | idx_first_last | idx_first_last | 44 | const | 244 | Using
index
condition; Using
where
|
+
----+-------------+-----------+------+----------------+----------------+---------+-------+------+------------------------------------+
1 row
in
set
|
key_len的长度计算公式:
L:索引列所定义字段类型字符长度
C:不同编码下一个字符所占的字节数(如utf8=3,gbk=2)
N:字段为空标记,占1字节(非空字段此标记不占用字节)
S:索引列字段是否定长(int、char、datetime为定长,varchar为不定长),不定长字段类型需记录长度信息,占2字节
key_len = L*C[+N][+S]
第一个执行计划key_len = (14 + 16) * 3 + 1 + 2*2 = 95
第二个执行计划key_len = 14 * 3 + 2 = 44
注意:由第二个执行计划可以分析得出,key_len只表示sql通过索引过滤结果集时所选择使用的索引的长度,并不包括order by的字段(类似的还有无需回表的快速索引扫描,select last_name from employees where first_name = 'Tomofumi';),所以在第二个执行计划中,虽然order by也能使用到索引(可以通过Extra中是否有sort判断),但却并没有被计算在key_len内。