MySQL EXPLAIN 完全解读

explain 之 key_len 计算

通常在优化 SQL 查询的时候，我们都会使用 explain 分析 SQL 执行计划，通常来说当用到组合索引的时候我们如何判断索引完全用上呢？当然高手看看表结构及 SQL 语句就知道到底用到了几个字段，对于不熟悉的同学呢？我们还是可以看看 key_len 的长度，当然这个计算还是有点复杂的，不过在你看过我这篇博客以后，相信你肯定会计算的，这难不倒聪明的你。

废话不多说了，我们直接上例子。表结构如下。

mysql [localhost] {msandbox} (yayun) > show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL DEFAULT '',
  `name1` char(20) DEFAULT NULL,
  `name3` varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (yayun) >
​

上面的表结构非常简单，有个主键索引，也就是 id 字段，还有一个辅助索引，也就是 name 字段，下面我们执行一条 SQL，并分析一下执行计划，看看到底 key_len 如何计算的。
表中就 3 条记录：

mysql [localhost] {msandbox} (yayun) > select * from t1;
+----+-------+-------+-----------+
| id | name  | name1 | name3     |
+----+-------+-------+-----------+
|  1 | atlas | yayun | dengyayun |
|  2 | alex  | talex | jalex     |
|  3 | je    | jetom | tomje     |
+----+-------+-------+-----------+
3 rows in set (0.00 sec)

mysql [localhost] {msandbox} (yayun) >
​

下面进行 explain 进行查看 key_len 的长度

mysql [localhost] {msandbox} (yayun) > explain select * from t1 where name='atlas';
+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | t1    | ref  | name          | name | 60      | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
1 row in set (0.03 sec)

mysql [localhost] {msandbox} (yayun) >
​

可以看到 key_len 的长度是 60，那么这个 60 是如何计算出来的。当然如果是单列索引我们不用去计算，因为没有意义，如果是组合索引，那么知道这里的长度就是非常有意义的，我们先简单来看看这个单列索引的 key_len 等于 60 是如何计算的。
还记得前面我的表结构里面 name 字段的定义么？

name char(20) NOT NULL DEFAULT '',我定义了 char（20），且非空。

好，现在我们来计算一下，首先我的表用的 utf8 字符集，那么大家都知道 utf8 字符集占用 3 个字节，那么我又定义 char（20），知道结果了么？聪明的你一定知道了。

key_len=20*3=60

计算简单吧，这个情况确实简单，还有复杂的情况呢，嘿嘿。

我们下面继续看下一条 SQL，我们把 name 这个字段的索引去掉，添加一个联合索引，key（name，name1）

mysql [localhost] {msandbox} (yayun) > alter table t1 drop key name;
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (yayun) > alter table t1 add key idx_key_name_name1 (name,name1);
Query OK, 0 rows affected (0.29 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (yayun) >
​

我们再来进行一条查询：

mysql [localhost] {msandbox} (yayun) > explain select * from t1 where name='atlas';
+----+-------------+-------+------+--------------------+--------------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys      | key                | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+--------------------+--------------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | t1    | ref  | idx_key_name_name1 | idx_key_name_name1 | 60      | const |    1 | Using index condition |
+----+-------------+-------+------+--------------------+--------------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (yayun) > explain select * from t1 where name='atlas' and name1='yayun';
+----+-------------+-------+------+--------------------+--------------------+---------+-------------+------+-----------------------+
| id | select_type | table | type | possible_keys      | key                | key_len | ref         | rows | Extra                 |
+----+-------------+-------+------+--------------------+--------------------+---------+-------------+------+-----------------------+
|  1 | SIMPLE      | t1    | ref  | idx_key_name_name1 | idx_key_name_name1 | 121     | const,const |    1 | Using index condition |
+----+-------------+-------+------+--------------------+--------------------+---------+-------------+------+-----------------------+
1 row in set (0.04 sec)

mysql [localhost] {msandbox} (yayun) >
​

看到第一条查询和第二条的查询的执行计划有什么不同了么？没错，key_len 及 ref 列不一样了。why？以及为什么第二条 SQL 语句的 key_len 为 121，这个是如何计算的？嘿嘿，如果还用上面的计算方法你肯定计算不出来的。让我来告诉你。还记得 name1 字段的定义么？
name1 char(20) DEFAULT NULL,

可以发现 name1 字段的定义为 DEFAULT NULL，其他没变化。所以 MySQL 需要 1 个字节来标识 NULL，

所以第二条 SQL 的 key_len=20 * 3 + (20 * 3 +1)=121，通过计算，我们知道 2 个字段的索引完全用上了。

下面我们再继续看看其他的情况，给表添加一个字段，并添加一个联合索引，我们进行一个范围的查询。

mysql [localhost] {msandbox} (yayun) > alter table t1 add add_time timestamp;
Query OK, 0 rows affected (1.44 sec)
Records: 0  Duplicates: 0  Warnings: 0
​

mysql [localhost] {msandbox} (yayun) > alter table t1 add key idx_key_add_time_name3 (add_time,name3);      
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0
​

现在的表结构这样了。

mysql [localhost] {msandbox} (yayun) > show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL DEFAULT '',
  `name1` char(20) DEFAULT NULL,
  `name3` varchar(20) NOT NULL DEFAULT '',
  `add_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_key_name_name1` (`name`,`name1`),
  KEY `idx_key_add_time_name3` (`add_time`,`name3`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

mysql [localhost] {msandbox} (yayun) >
​

看 SQL，废话不多说。

mysql [localhost] {msandbox} (yayun) > explain select * from t1 where add_time >='2014-09-10 02:36:46' and add_time <='2014-09-11 02:36:46' group by name3 order by null;
+----+-------------+-------+-------+------------------------+------------------------+---------+------+------+----------------------------------------+
| id | select_type | table | type  | possible_keys          | key                    | key_len | ref  | rows | Extra                                  |
+----+-------------+-------+-------+------------------------+------------------------+---------+------+------+----------------------------------------+
|  1 | SIMPLE      | t1    | range | idx_key_add_time_name3 | idx_key_add_time_name3 | 4       | NULL |    2 | Using index condition; Using temporary |
+----+-------------+-------+-------+------------------------+------------------------+---------+------+------+----------------------------------------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (yayun) >
​

可以看见用到了我创建的联合索引 idx_key_add_time_name3，但是真的完全用到了么。其实一眼就知道没有用到，因为前面是一个范围查询，后面字段的索引就用不到，如果我这里不 order by null，还会看到 Using filesort。但是我还是想说说 key_len 是如何计算的，大家都很清楚 timestamp 占用 4 字节吧。那么答案显而易见，看见 key_len 是 4，说明只用到了联合索引 idx_key_add_time_name3 中的 add_time 字段。

我们再来看一种情况，是 char 字段和 varchar 字段组成的一个联合索引。

mysql [localhost] {msandbox} (yayun) > alter table t1 add key idx_key_name1_name3 (name1,name3);
Query OK, 0 rows affected (0.27 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (yayun) > 
​

SQL 如下：

mysql [localhost] {msandbox} (yayun) > explain select * from t1 where name1='yayun' and name3='dengyayun';
+----+-------------+-------+------+---------------------+---------------------+---------+-------------+------+-----------------------+
| id | select_type | table | type | possible_keys       | key                 | key_len | ref         | rows | Extra                 |
+----+-------------+-------+------+---------------------+---------------------+---------+-------------+------+-----------------------+
|  1 | SIMPLE      | t1    | ref  | idx_key_name1_name3 | idx_key_name1_name3 | 123     | const,const |    1 | Using index condition |
+----+-------------+-------+------+---------------------+---------------------+---------+-------------+------+-----------------------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (yayun) >
​

可以看见 key_len 的长度是 123。那么索引完全用到了么？当然有点索引常识都知道完全用到了。我这里只是为了告诉大家 key_len 到底如何计算的。
name3 varchar(20) NOT NULL DEFAULT ''

name1 char(20) DEFAULT NULL,

上面是 2 个字段的定义，1 个允许 NULL，一个 NOT NULL，一个 char，一个 varchar

所以 key_len=（20*3 + 1）+（20 * 3 + 2）= 123

由此来判断这个组合索引已经完全使用。相信有同学会问了，+1 是干嘛，+2 是干嘛。这就告诉大家，+1 是因为 MySQL 需要 1 个字节标识 NULL，+2 是因为 name3 字段为 varchar，是变长字段需要 +2。

写到这里相信大家都有一个基本认识了吧。好了，多的不说了，公式放出来给大家，自己套用公式，多做几次测试就明白啦。

key_len 的长度计算公式：

varchr(10)变长字段且允许NULL    =  10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)
varchr(10)变长字段且不允许NULL  =  10 *( character set：utf8=3,gbk=2,latin1=1)+2(变长字段)

char(10)固定字段且允许NULL      =  10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)
char(10)固定字段且不允许NULL    =  10 * ( character set：utf8=3,gbk=2,latin1=1)
​

‍

‍ ↩