0103如何通过索引长度确定是否使用全索引

转自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内。