mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> explain select name from t3;
+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
| 1 | SIMPLE | t3 | index | NULL | in_01 | 63 | NULL | 1 | Using index |
+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> desc t3;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | MUL | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> show create table t3;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| t3 | CREATE TABLE `t3` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
KEY `in_01` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)
mysql> select 20*3+2+1;
+----------+
| 20*3+2+1 |
+----------+
| 63 |
+----------+
1 row in set (0.01 sec)
mysql> alter table t3 modify name varchar(255);
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> explain select name from t3;
+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
| 1 | SIMPLE | t3 | index | NULL | in_01 | 768 | NULL | 1 | Using index |
+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
1 row in set (0.01 sec)
mysql> desc t3;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(255) | YES | MUL | NULL | |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> select 255*3+2+1;
+-----------+
| 255*3+2+1 |
+-----------+
| 768 |
+-----------+
1 row in set (0.00 sec)
mysql> alter table t3 modify name varchar(255) not null;
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> explain select name from t3;
+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
| 1 | SIMPLE | t3 | index | NULL | in_01 | 767 | NULL | 1 | Using index |
+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> explain select name from t3;
+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
| 1 | SIMPLE | t3 | index | NULL | in_01 | 63 | NULL | 1 | Using index |
+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> desc t3;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | MUL | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> show create table t3;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| t3 | CREATE TABLE `t3` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
KEY `in_01` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)
mysql> select 20*3+2+1;
+----------+
| 20*3+2+1 |
+----------+
| 63 |
+----------+
1 row in set (0.01 sec)
mysql> alter table t3 modify name varchar(255);
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> explain select name from t3;
+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
| 1 | SIMPLE | t3 | index | NULL | in_01 | 768 | NULL | 1 | Using index |
+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
1 row in set (0.01 sec)
mysql> desc t3;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(255) | YES | MUL | NULL | |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> select 255*3+2+1;
+-----------+
| 255*3+2+1 |
+-----------+
| 768 |
+-----------+
1 row in set (0.00 sec)
mysql> alter table t3 modify name varchar(255) not null;
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> explain select name from t3;
+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
| 1 | SIMPLE | t3 | index | NULL | in_01 | 767 | NULL | 1 | Using index |
+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
1 row in set (0.00 sec)
ken_len表示索引使用的字节数,根据这个值,就可以判断索引使用情况,特别是在组合索引的时候,判断所有的索引字段都被查询用到。
在查看官方文档的时候,也没有发现详细的key_len的计算介绍,后来做了一些测试,在咨询了丁奇关于变长数据类型的值计算的时候,突然想到innodb 行的格式,在这里的计算中有点类似,总结一下需要考虑到以下一些情况:
(1).索引字段的附加信息:可以分为变长和定长数据类型讨论,当索引字段为定长数据类型,比如char,int,datetime,需要有是否为空的标记,这个标记需要占用1个字节;对于变长数据类型,比如:varchar,除了是否为空的标记外,还需要有长度信息,需要占用2个字节;
(备注:当字段定义为非空的时候,是否为空的标记将不占用字节)
(2).同时还需要考虑表所使用的字符集,不同的字符集,gbk编码的为一个字符2个字节,utf8编码的一个字符3个字节;
结论:mysql中 varchar(20)和varchar(255)不是随便取值,因为后面的值可能影响字段所在索引的大小,以及查询性能。