MySQL执行计划explain的key_len解析

.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 | | +---------+-------------+------+-----+---------+-------+ 6 rows in set (0.01 sec) (dg1)root@127.0.0.1 [mytest]> 来看看tinyint类型的索引长度，在NOT NULL 和 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 | +----+-------------+-----------+------+---------------+-----------+---------+-------+------+----------+-------+ 1 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 | +----+-------------+-----------+------+---------------+-----------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) (dg1)root@127.0.0.1 [mytest]> 看看bigint类型的索引长度，同样是 NOT NULL 和 NULL值的时候，分别是1和2 (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 | +----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+ 1 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 | +----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+ 1 row in set (0.00 sec) 看看smallint类型索引长度，同样是 NOT NULL 和 NULL值的时候，分别是2和3
(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 | +----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+ 1 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 | +----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+ 1 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 | +----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+ 1 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  |

+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+

1 row in set (0.00 sec)

(dg1)root@127.0.0.1 [mytest]>

2.浮点数类型

表结构
CREATE TABLE `table_key1` (
`id`  int NOT NULL AUTO_INCREMENT ,
`c1`  float NOT NULL ,
`c2`  double NOT NULL ,
`c3`  decimal NOT NULL ,
`c4`  date NOT NULL ,
`c5`  timestamp NOT NULL ON UPDATE CURRENT_TIMESTAMP ,
`c6`  datetime NOT NULL ,
PRIMARY KEY (`id`)
)
看看float类型的索引长度，NOT NULL和NULL的时候，分别是4和5
(dg1)root@127.0.0.1 [mytest]> desc select * from table_key1 where c1 = ‘3.22‘;
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
| id | select_type | table      | type | possible_keys | key      | key_len | ref   | rows | Extra                 |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | table_key1 | ref  | c1_index      | c1_index | 4       | const |    8 | Using index condition |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

(dg1)root@127.0.0.1 [mytest]> alter table table_key1 modify c1 float;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

(dg1)root@127.0.0.1 [mytest]> desc select * from table_key1 where c1 = ‘3.22‘;
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
| id | select_type | table      | type | possible_keys | key      | key_len | ref   | rows | Extra                 |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | table_key1 | ref  | c1_index      | c1_index | 5       | const |    8 | Using index condition |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)
看看double类型的索引长度，NOT NULL和NULL的时候，分别是8和9
(dg1)root@127.0.0.1 [mytest]> alter table table_key1 add key c2_index (c2);
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_key1 where c2 = ‘3.22‘;
+----+-------------+------------+------+---------------+----------+---------+-------+------+-------+
| id | select_type | table      | type | possible_keys | key      | key_len | ref   | rows | Extra |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-------+
|  1 | SIMPLE      | table_key1 | ref  | c2_index      | c2_index | 8       | const |    1 | NULL  |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-------+
1 row in set (0.00 sec)

(dg1)root@127.0.0.1 [mytest]> alter table table_key1 modify c2 double;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

(dg1)root@127.0.0.1 [mytest]> desc select * from table_key1 where c2 = ‘3.22‘;
+----+-------------+------------+------+---------------+----------+---------+-------+------+-------+
| id | select_type | table      | type | possible_keys | key      | key_len | ref   | rows | Extra |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-------+
|  1 | SIMPLE      | table_key1 | ref  | c2_index      | c2_index | 9       | const |    1 | NULL  |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-------+
1 row in set (0.00 sec)

(dg1)root@127.0.0.1 [mytest]> 

3、看看时间类型

看看date类型的索引长度，在NOT NULL和NULL的时候，分别是3和4
(dg1)root@127.0.0.1 [mytest]> desc select * from table_key1 where c4 = ‘2015-05-06‘;
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
| id | select_type | table      | type | possible_keys | key      | key_len | ref   | rows | Extra                 |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | table_key1 | ref  | c4_index      | c4_index | 3       | const |    4 | Using index condition |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
1 row in set, 3 warnings (0.00 sec)

(dg1)root@127.0.0.1 [mytest]> 

(dg1)root@127.0.0.1 [mytest]> alter table table_key1 modify c4 date;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

(dg1)root@127.0.0.1 [mytest]> desc select * from table_key1 where c4 = ‘2015-05-06‘;
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
| id | select_type | table      | type | possible_keys | key      | key_len | ref   | rows | Extra                 |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | table_key1 | ref  | c4_index      | c4_index | 4       | const |    4 | Using index condition |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
1 row in set, 3 warnings (0.00 sec)

(dg1)root@127.0.0.1 [mytest]> 

在timestamp类型的时候索引长度，在NOT NULL 和 NULL的时候，分别是4和5

(dg1)root@127.0.0.1 [mytest]> desc select * from table_key1 where c5 = ‘2015-05-06 11:23:21‘ ;
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
| id | select_type | table      | type | possible_keys | key      | key_len | ref   | rows | Extra                 |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | table_key1 | ref  | c5_index      | c5_index | 4       | const |    5 | Using index condition |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
1 row in set, 3 warnings (0.00 sec)

(dg1)root@127.0.0.1 [mytest]> 

dg1)root@127.0.0.1 [mytest]> alter table table_key1 modify c5 timestamp ON UPDATE CURRENT_TIMESTAMP ;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

(dg1)root@127.0.0.1 [mytest]> 



(dg1)root@127.0.0.1 [mytest]> desc select * from table_key1 where c5 = ‘2015-05-06 110:23:21‘;
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
| id | select_type | table      | type | possible_keys | key      | key_len | ref   | rows | Extra                 |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | table_key1 | ref  | c5_index      | c5_index | 5       | const |    5 | Using index condition |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
1 row in set, 3 warnings (0.00 sec)

(dg1)root@127.0.0.1 [mytest]> 



(dg1)root@127.0.0.1 [mytest]> desc select * from table_key1 where c6 = ‘2015-05-06 11:23:38‘;
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
| id | select_type | table      | type | possible_keys | key      | key_len | ref   | rows | Extra                 |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | table_key1 | ref  | c6_index      | c6_index | 8       | const |    4 | Using index condition |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
1 row in set, 3 warnings (0.00 sec)

(dg1)root@127.0.0.1 [mytest]> 


(dg1)root@127.0.0.1 [mytest]> alter table table_key1 modify c6 datetime ;
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_key1 where c6 = ‘2015-05-06 11:23:38‘;
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
| id | select_type | table      | type | possible_keys | key      | key_len | ref   | rows | Extra                 |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | table_key1 | ref  | c6_index      | c6_index | 9       | const |    4 | Using index condition |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
1 row in set, 3 warnings (0.00 sec)

 4.字符类型

表结构，字符集是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    |       |
+---------+-------------+------+-----+---------+-------+
6 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 name=‘zhangsan‘;
+----+-------------+-----------+------+---------------+------------+---------+-------+------+----------+-----------------------+
| 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 |
+----+-------------+-----------+------+---------------+------------+---------+-------+------+----------+-----------------------+
1 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 name=‘zhangsan‘;
+----+-------------+-----------+------+---------------+------------+---------+-------+------+----------+-----------------------+
| 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 |
+----+-------------+-----------+------+---------------+------------+---------+-------+------+----------+-----------------------+
1 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 |
+----+-------------+-----------+------+---------------+---------------+---------+-------+------+----------+-----------------------+
1 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 |
+----+-------------+-----------+------+---------------+---------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

(dg1)root@127.0.0.1 [mytest]> 

修改字符集为GBK，再来看看

(dg1)root@127.0.0.1 [mytest]> ALTER TABLE `table_key` DEFAULT CHARACTER SET=gbk COLLATE=gbk_chinese_ci;

(dg1)root@127.0.0.1 [mytest]> ALTER database `mytest` DEFAULT CHARACTER SET=gbk COLLATE=gbk_chinese_ci;

(dg1)root@127.0.0.1 [mytest]> show global variables like ‘%char%‘;
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | gbk                            |
| character_set_connection | gbk                            |
| character_set_database   | gbk                            |
| character_set_filesystem | binary                         |
| character_set_results    | gbk                            |
| character_set_server     | gbk                            |
| character_set_system     | utf8                           |
| character_sets_dir       | /opt/app/mysql/share/charsets/ |
+--------------------------+--------------------------------+
8 rows in set (0.00 sec)

奇怪了，为什么还是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 | 33      | const |    1 |   100.00 | Using index condition |
+----+-------------+-----------+------+---------------+---------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)


(dg1)root@127.0.0.1 [mytest]> ALTER TABLE `table_key` MODIFY COLUMN `name`  char(10) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL , DEFAULT CHARACTER SET=gbk COLLATE=gbk_chinese_ci;
Query OK, 9 rows affected (0.04 sec)
Records: 9  Duplicates: 0  Warnings: 0

(dg1)root@127.0.0.1 [mytest]> ALTER TABLE `table_key` MODIFY COLUMN `address`  char(10) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL , DEFAULT CHARACTER SET=gbk COLLATE=gbk_chinese_ci;
Query OK, 9 rows affected (0.03 sec)
Records: 9  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 | 21      | const |    1 |   100.00 | Using index condition |
+----+-------------+-----------+------+---------------+---------------+---------+-------+------+----------+-----------------------+
1 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 address varchar(10);
Query OK, 9 rows affected (0.04 sec)
Records: 9  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 | 23      | const |    1 |   100.00 | Using index condition |
+----+-------------+-----------+------+---------------+---------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)

(dg1)root@127.0.0.1 [mytest]> 

(dg1)root@127.0.0.1 [mytest]> explain extended select * from table_key where name=‘zhangsan‘;
+----+-------------+-----------+------+---------------+------------+---------+-------+------+----------+-----------------------+
| id | select_type | table     | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-----------+------+---------------+------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | table_key | ref  | name_index    | name_index | 20      | const |    1 |   100.00 | Using index condition |
+----+-------------+-----------+------+---------------+------------+---------+-------+------+----------+-----------------------+
1 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.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

(dg1)root@127.0.0.1 [mytest]> explain extended select * from table_key where name=‘zhangsan‘;
+----+-------------+-----------+------+---------------+------------+---------+-------+------+----------+-----------------------+
| id | select_type | table     | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-----------+------+---------------+------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | table_key | ref  | name_index    | name_index | 21      | const |    1 |   100.00 | Using index condition |
+----+-------------+-----------+------+---------------+------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (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 name=‘zhangsan‘;
+----+-------------+-----------+------+--------------------+--------------------+---------+-------------+------+----------+-----------------------+
| 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 |
+----+-------------+-----------+------+--------------------+--------------------+---------+-------------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

(dg1)root@127.0.0.1 [mytest]> 

(dg1)root@127.0.0.1 [mytest]> explain extended select * from table_key where name=‘zhangsan‘ 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 |
+----+-------------+-----------+------+--------------------+--------------------+---------+-------------+------+----------+-----------------------+

1 row in set, 1 warning (0.00 sec)
(dg1)root@127.0.0.1 [mytest]> alter table table_key modify name char(10);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

(dg1)root@127.0.0.1 [mytest]> explain extended select * from table_key where name=‘zhangsan‘;
+----+-------------+-----------+------+--------------------+--------------------+---------+-------+------+----------+-----------------------+
| 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 |
+----+-------------+-----------+------+--------------------+--------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

(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 name=‘zhangsan‘ 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 |
+----+-------------+-----------+------+--------------------+--------------------+---------+-------------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

(dg1)root@127.0.0.1 [mytest]> 

 

总结：

1.整数类型，浮点数类型，时间类型的索引长度

NOT NULL=字段本身的字段长度

NULL=字段本身的字段长度+1，因为需要有是否为空的标记，这个标记需要占用1个字节

2.字符类型

 

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

char(n)固定字段且允许NULL      =  n * ( utf8=3,gbk=2,latin1=1)+1(NULL)
char(n)固定字段且允许NULL      =  n * ( utf8=3,gbk=2,latin1=1)

变长字段需要额外的