表结构，字符集是UTF8
(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)
看看定长字符类型char的索引长度，在NOT NULL 和NULL中分别为10*3和10*3+1
(dg1)root@127.0.0.1 [mytest]> alter table table_key add index name_index (name);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
(dg1)root@127.0.0.1 [mytest]> explain extended select * from table_key where;
+----+-------------+-----------+------+---------------+------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------+---------------+------------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | table_key | ref | name_index | name_index | 30 | const | 1 | 100.00 | Using index condition |
+----+-------------+-----------+------+---------------+------------+---------+-------+------+----------+-----------------------+
row in set, 1 warning (0.00 sec)
(dg1)root@127.0.0.1 [mytest]>
(dg1)root@127.0.0.1 [mytest]> alter table table_key modify name char(10);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
(dg1)root@127.0.0.1 [mytest]> explain extended select * from table_key where;
+----+-------------+-----------+------+---------------+------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------+---------------+------------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | table_key | ref | name_index | name_index | 31 | const | 1 | 100.00 | Using index condition |
+----+-------------+-----------+------+---------------+------------+---------+-------+------+----------+-----------------------+
row in set, 1 warning (0.00 sec)
看看变长长字符类型varchar的索引长度，在NOT NULL 和NULL中分别为10*3+2和10*3+2+1
(dg1)root@127.0.0.1 [mytest]> explain extended select * from table_key where address='shanghai';
+----+-------------+-----------+------+---------------+---------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------+---------------+---------------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | table_key | ref | address_index | address_index | 32 | const | 1 | 100.00 | Using index condition |
+----+-------------+-----------+------+---------------+---------------+---------+-------+------+----------+-----------------------+
row in set, 1 warning (0.01 sec)
(dg1)root@127.0.0.1 [mytest]>
(dg1)root@127.0.0.1 [mytest]> alter table table_key modify address varchar(10);
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
(dg1)root@127.0.0.1 [mytest]> explain extended select * from table_key where address='shanghai';
+----+-------------+-----------+------+---------------+---------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------+---------------+---------------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | table_key | ref | address_index | address_index | 33 | const | 1 | 100.00 | Using index condition |
+----+-------------+-----------+------+---------------+---------------+---------+-------+------+----------+-----------------------+
row in set, 1 warning (0.00 sec)
(dg1)root@127.0.0.1 [mytest]>
来看看复合索引的key_len,（刚才测试GBK字符集，字符集转换成GBK了）
(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) | NO | | | |
| age | tinyint(4) | YES | | NULL | |
| sex | tinyint(4) | NO | | NULL | |
| address | varchar(10) | NO | MUL | | |
+---------+-------------+------+-----+---------+-------+
rows in set (0.01 sec)
(dg1)root@127.0.0.1 [mytest]> alter table table_key drop index name_index;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
(dg1)root@127.0.0.1 [mytest]> alter table table_key drop index address_index;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
(dg1)root@127.0.0.1 [mytest]> alter table table_key add index name_address_index (name,address);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
(dg1)root@127.0.0.1 [mytest]> explain extended select * from table_key where address='shanghai' and;
+----+-------------+-----------+------+--------------------+--------------------+---------+-------------+------+----------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------+--------------------+--------------------+---------+-------------+------+----------+-----------------------+
| 1 | SIMPLE | table_key | ref | name_address_index | name_address_index | 42 | const,const | 1 | 100.00 | Using index condition |
+----+-------------+-----------+------+--------------------+--------------------+---------+-------------+------+----------+-----------------------+
row in set, 1 warning (0.00 sec)
看看复合索引的长度，因为能全部使用到组合索引，所以是：2*（10）+2*（20）+2=42,下面将name字段允许为空，再来看看
(dg1)root@127.0.0.1 [mytest]> alter table table_key modify name char(10);
(dg1)root@127.0.0.1 [mytest]> explain extended select * from table_key where and address='shanghai';
+----+-------------+-----------+------+--------------------+--------------------+---------+-------------+------+----------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------+--------------------+--------------------+---------+-------------+------+----------+-----------------------+
| 1 | SIMPLE | table_key | ref | name_address_index | name_address_index | 43 | const,const | 1 | 100.00 | Using index condition |
+----+-------------+-----------+------+--------------------+--------------------+---------+-------------+------+----------+-----------------------+
row in set, 1 warning (0.00 sec)
看看复合索引的长度，因为能全部使用到组合索引，所以是：2*（10）+1+2*（20）+2=43
(dg1)root@127.0.0.1 [mytest]> explain extended select * from table_key where;
+----+-------------+-----------+------+--------------------+--------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------+--------------------+--------------------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | table_key | ref | name_address_index | name_address_index | 21 | const | 1 | 100.00 | Using index condition |
+----+-------------+-----------+------+--------------------+--------------------+---------+-------+------+----------+-----------------------+
row in set, 1 warning (0.00 sec)
那么我们来看看部分使用复合索引：2*（10）+1,将address设置为允许为空，再来看看
(dg1)root@127.0.0.1 [mytest]> alter table table_key modify address varchar(10);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
(dg1)root@127.0.0.1 [mytest]> explain extended select * from table_key where and address='shanghai';
+----+-------------+-----------+------+--------------------+--------------------+---------+-------------+------+----------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------+--------------------+--------------------+---------+-------------+------+----------+-----------------------+
| 1 | SIMPLE | table_key | ref | name_address_index | name_address_index | 44 | const,const | 1 | 100.00 | Using index condition |
+----+-------------+-----------+------+--------------------+--------------------+---------+-------------+------+----------+-----------------------+
row in set, 1 warning (0.00 sec)
(dg1)root@127.0.0.1 [mytest]>
这时候key_len=2*（10）+1+2*（10）+2+1=44
总结
1.整数类型，浮点数类型，时间类型的索引长度
NOT NULL=字段本身的字段长度
NULL=字段本身的字段长度+1，因为需要有是否为空的标记，这个标记需要占用1个字节
datetime类型在5.6中字段长度是5个字节
2.字符类型