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分析
- 表示索引中使用的字節(jié)數(shù)奕扣，可通過該列計算查詢中使用的索引的長度蒋歌，在不損失精確性的情況下滚秩，長度越短越好
key-len顯示的值為索引字段的最大可能長度棺榔，并非實(shí)際使用長度饥追，即key_len是根據(jù)表定義計算而得掠河，不是通過表內(nèi)檢索出的
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)
