1.key-len长度计算参考：http://www.cnblogs.com/gomysql/p/4004244.html
key_len的长度计算公式：
1.varchr(10)变长字段且允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)
2.varchr(10)变长字段且不允许NULL = 10 *( character set：utf8=3,gbk=2,latin1=1)+2(变长字段)
3.char(10)固定字段且允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)
4.char(10)固定字段且不允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)
2.key-len分析
- 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好
key-len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的
3.key-len案例
mysql> explain SELECT t.id,t.`name` FROM t1 t ;
+----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
| 1 | SIMPLE | t | index | NULL | index_id_name | 37 | NULL | 3 | Using index |
+----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain SELECT t.`name`,t.id FROM t1 t ;
+----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
| 1 | SIMPLE | t | index | NULL | index_id_name | 37 | NULL | 3 | Using index |
+----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain SELECT t.id,t.`name` FROM t1 t WHERE t.id = '1' and t.`name` = 'downeyjr_1';
+----+-------------+-------+-------+-----------------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-----------------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | t | const | PRIMARY,index_id_name | PRIMARY | 4 | const | 1 | |
+----+-------------+-------+-------+-----------------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)
mysql> explain SELECT t.id,t.`name` FROM t1 t WHERE t.id = '1' ;
+----+-------------+-------+-------+-----------------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-----------------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | t | const | PRIMARY,index_id_name | PRIMARY | 4 | const | 1 | |
+----+-------------+-------+-------+-----------------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)
mysql> explain SELECT t.`name`,t.id FROM t1 t WHERE t.id = '1' ;
+----+-------------+-------+-------+-----------------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-----------------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | t | const | PRIMARY,index_id_name | PRIMARY | 4 | const | 1 | |
+----+-------------+-------+-------+-----------------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)
mysql> explain SELECT t.id,t.`name` FROM t1 t WHERE t.`name` = 'downeyjr_1';
+----+-------------+-------+-------+---------------+---------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------------+---------+------+------+--------------------------+
| 1 | SIMPLE | t | index | NULL | index_id_name | 37 | NULL | 3 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
