前言 当用Explain查看SQL的执行计划时，里面有列显示了 key_len 的值，根据这个值可以判断索引的长度，在组合索引里面可以更清楚的了解到了哪部分字段使用到了索引。下面演示中，表结构的合理性这边暂且不说，只是证明一下索引长度的计算方法。目前大部分博文是字符类型的索引长度计算方法，下面列举几个类型的索引长度计算方法：
1、整数类型
(dg1)root@127.0.0.1 [mytest]> desc table_key;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| sid | bigint(20) | NO | | 0 | |
| name | char(10) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| sex | tinyint(4) | NO | | NULL | |
| address | varchar(10) | YES | MUL | NULL | |
+---------+-------------+------+-----+---------+-------+
rows in set (0.01 sec)
(dg1)root@127.0.0.1 [mytest]>create index age_index on table_key (age);
来看看tinyint类型的索引长度，在NOT NULL 和 NULL 的时候 分别是1和2,tinyint字段长度为1，因为NULL 需要额外一个字节标记为空
(dg1)root@127.0.0.1 [mytest]> explain extended select * from table_key where age=38;
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | table_key | ref | age_index | age_index | 1 | const | 1 | 100.00 | NULL |
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+----------+-------+
row in set, 1 warning (0.00 sec)
(dg1)root@127.0.0.1 [mytest]> alter table table_key modify age tinyint(4);
(dg1)root@127.0.0.1 [mytest]> explain extended select * from table_key where age=38;
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | table_key | ref | age_index | age_index | 2 | const | 1 | 100.00 | NULL |
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+----------+-------+
row in set, 1 warning (0.00 sec)
(dg1)root@127.0.0.1 [mytest]>
看看bigint类型的索引长度，同样是 NOT NULL 和 NULL值的时候，分别是8和9,聪明的你应该知道了，bigint长度为8。
(dg1)root@127.0.0.1 [mytest]> alter table table_key add key sid_index (sid);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
(dg1)root@127.0.0.1 [mytest]> desc select * from table_key where sid=6;
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+
| 1 | SIMPLE | table_key | ref | sid_index | sid_index | 8 | const | 1 | NULL |
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+
row in set (0.00 sec)
(dg1)root@127.0.0.1 [mytest]>
(dg1)root@127.0.0.1 [mytest]> alter table table_key modify sid bigint(20);
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
(dg1)root@127.0.0.1 [mytest]> desc select * from table_key where sid=6;
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+
| 1 | SIMPLE | table_key | ref | sid_index | sid_index | 9 | const | 1 | NULL |
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+
row in set (0.00 sec)
看看smallint类型索引长度，同样是 NOT NULL 和 NULL值的时候，分别是2和3 smallint长度为2，允许为空需要一个字节标记
(dg1)root@127.0.0.1 [mytest]> alter table table_key modify sid smallint not null default 0;
Query OK, 9 rows affected (0.04 sec)
Records: 9 Duplicates: 0 Warnings: 0
(dg1)root@127.0.0.1 [mytest]> desc select * from table_key where sid=6;
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+
| 1 | SIMPLE | table_key | ref | sid_index | sid_index | 2 | const | 1 | NULL |
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+
row in set (0.00 sec)
(dg1)root@127.0.0.1 [mytest]> alter table table_key modify sid smallint ;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
(dg1)root@127.0.0.1 [mytest]> desc select * from table_key where sid=6;
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+
| 1 | SIMPLE | table_key | ref | sid_index | sid_index | 3 | const | 1 | NULL |
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+
row in set (0.00 sec)
看看mediumint类型索引长度，同样是 NOT NULL 和 NULL值的时候，分别是3和4
(dg1)root@127.0.0.1 [mytest]> alter table table_key modify sid mediumint NOT NULL;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
(dg1)root@127.0.0.1 [mytest]> desc select * from table_key where sid=6;
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+
| 1 | SIMPLE | table_key | ref | sid_index | sid_index | 3 | const | 1 | NULL |
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+
row in set (0.00 sec)
(dg1)root@127.0.0.1 [mytest]>
（dg1)root@127.0.0.1 [mytest]> alter table table_key modify sid mediumint ;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
(dg1)root@127.0.0.1 [mytest]> desc select * from table_key where sid=6;
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+
| 1 | SIMPLE | table_key | ref | sid_index | sid_index | 4 | const | 1 | NULL |
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+
row in set (0.00 sec)
(dg1)root@127.0.0.1 [mytest]>
整数类型索引长度跟字段长度有关，如果允许为空，需要额外一个字节去标记为空
2.浮点数类型