它的规则如下:
1.所有的索引字段,如果没有设置not null,则需要加一个字节。
2.定长字段,int占四个字节、date占三个字节、char(n)占n个字符。
3.对于变成字段varchar(n),则有n个字符+两个字节。
4.不同的字符集,一个字符占用的字节数不同。latin1编码的,一个字符占用一个字节,gbk编码的,一个字符占用两个字节,utf8编码的,一个字符占用三个字节。
举例说明:
mysql> desc person;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | varchar(100) | NO | PRI | | |
| name | varchar(100) | YES | MUL | NULL | |
| age | int(10) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
查看索引情况:
mysql> show index from person;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| person | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | |
| person | 1 | idx_name | 1 | name | A | 2 | NULL | NULL | YES | BTREE | | |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
mysql> explain select name from person;
+----+-------------+--------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+----------+---------+------+------+-------------+
| 1 | SIMPLE | person | index | NULL | idx_name | 103 | NULL | 2 | Using index |
+----+-------------+--------+-------+---------------+----------+---------+------+------+-------------+
1 row in set (0.00 sec)
根据以上计算规则:
所有的索引字段,如果没有设置not null,则需要加一个字节
对于变成字段varchar(n),则有n个字符+两个字节
因此:key_len=100+2+1=103
1.所有的索引字段,如果没有设置not null,则需要加一个字节。
2.定长字段,int占四个字节、date占三个字节、char(n)占n个字符。
3.对于变成字段varchar(n),则有n个字符+两个字节。
4.不同的字符集,一个字符占用的字节数不同。latin1编码的,一个字符占用一个字节,gbk编码的,一个字符占用两个字节,utf8编码的,一个字符占用三个字节。
举例说明:
mysql> desc person;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | varchar(100) | NO | PRI | | |
| name | varchar(100) | YES | MUL | NULL | |
| age | int(10) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
查看索引情况:
mysql> show index from person;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| person | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | |
| person | 1 | idx_name | 1 | name | A | 2 | NULL | NULL | YES | BTREE | | |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
mysql> explain select name from person;
+----+-------------+--------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+----------+---------+------+------+-------------+
| 1 | SIMPLE | person | index | NULL | idx_name | 103 | NULL | 2 | Using index |
+----+-------------+--------+-------+---------------+----------+---------+------+------+-------------+
1 row in set (0.00 sec)
根据以上计算规则:
所有的索引字段,如果没有设置not null,则需要加一个字节
对于变成字段varchar(n),则有n个字符+两个字节
因此:key_len=100+2+1=103
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15498/viewspace-2138031/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/15498/viewspace-2138031/