mysql 占用的内存大小
1、mysql执行查询计划，key_len表示索引使用的字节数，这个字节数和三个条件有关。
mysql> create table t1(v1 char(10));
Query OK, 0 rows affected
mysql> create index index_v1 on t1(v1);
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
2、是否允许为null，允许为null 会多一个字节，标识取值是否为NULL。如下：
mysql> alter table t1 modify column v1 char(10) not null;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc select * from t1;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| 1 | SIMPLE | t1 | index | NULL | index_v1 | 30 | NULL | 1 | Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
1 row in set
mysql> alter table t1 modify column v1 char(10) null;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc select * from t1;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| 1 | SIMPLE | t1 | index | NULL | index_v1 | 31 | NULL | 1 | Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
1 row in set
3、是否为变长，变长多出两个字节表示长度，如下：
mysql> alter table t1 modify column v1 varchar(10) null;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc select * from t1;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| 1 | SIMPLE | t1 | index | NULL | index_v1 | 33 | NULL | 1 | Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
1 row in set
mysql> alter table t1 modify column v1 char(10) null;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc select * from t1;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| 1 | SIMPLE | t1 | index | NULL | index_v1 | 31 | NULL | 1 | Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
1 row in set
4、字符类型，varchar(n) 中的n是字符个数，不是占用多少个字节的内存。对于不同的编码，同样一个字符占用的内存不一样。latin1 一个字符占用1个字节，gb2312 一个字符占用2个字节，utf8 一个字符占用3个字节。
mysql> alter table t1 modify column v1 char(10) charset latin1;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc select * from t1;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| 1 | SIMPLE | t1 | index | NULL | index_v1 | 11 | NULL | 1 | Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
1 row in set
mysql> alter table t1 modify column v1 char(10) charset gb2312;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc select * from t1;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| 1 | SIMPLE | t1 | index | NULL | index_v1 | 21 | NULL | 1 | Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
1 row in set
mysql> alter table t1 modify column v1 char(10) charset utf8;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc select * from t1;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| 1 | SIMPLE | t1 | index | NULL | index_v1 | 31 | NULL | 1 | Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
1 row in set
最新文章
- c/c++的Soap应用
- 洛谷P3128 [USACO15DEC]最大流Max Flow [树链剖分]
- fzu2172 字符串dp
- 关联规则-R语言实现
- linux下使用sqlplus使用上下键显示历史命令
- matlab 在代码中，显示错误，退出程序
- JenKins 环境搭建 for Centos6.5
- hdu 2191 悼念512四川汶川大地震遇难者——如今宝，感恩生活
- 史上最“脑残”的“抢火车票”程序（node.js版）
- spring mvc 返回页面数据
- Python dict 按键和值排序
- Postman教程——创建第一个集合
- Java KeyTool command
- bootstrap研究感想1
- Vue中data返回对象和返回值的区别
- hdu5443 ST表裸题：求区间最大
- Maven配置阿里云镜像仓库
- LB+nginx+tomcat7集群模式下的https请求重定向（redirect）后变成http的解决方案
- [UE4]控件模板参数
- 使用adb查看CPU和内存