建表语句:

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