数据类型及字节数参考
-------------------------表的优化:-----------------------
1: 定长与变长分离
如 id int, 占4个字节, char(4) 占4个字符长度,也是定长, time
即每一单元值占的字节是固定的.
核心且常用字段,宜建成定长,放在一张表.
而varchar, text,blob,这种变长字段,适合单放一张表, 用主键与核心表关联起来.
2:常用字段和不常用字段要分离.
需要结合网站具体的业务来分析,分析字段的查询场景,查询频度低的字段,单拆出来.
3:合理添加冗余字段.
防止后期修改表，在前期设计的时候就可以合理的添加冗余字段。
-----------------------列选择原则:------------------------
1.列类型优先级
整型>date,time>char,varchar>blob(存储从二进制文件)
列的特点分析:
整型: 定长,没有国家/地区之分,没有字符集的差异time定长,运算快,节省空间. 考虑时区,写sql时不方便 where > ‘2005-10-12’;enum: 能起来约束值的目的, 内部用整型来存储,但与char联查时,内部要经历串与值的转化Char 定长, 考虑字符集和(排序)校对集varchar, 不定长 要考虑字符集的转换与排序时的校对集,速度慢.text/Blob 无法使用内存临时表
性别: 以utf8为例char(1) , 3个字长字节enum(‘男’,’女’); // 内部转成数字来存,多了一个转换过程tinyint() , // 0 1 2 // 定长1个字节.
2: 够用就行,不要慷慨 (如smallint,varchar(N))
原因: 大的字段浪费内存,影响速度,
以年龄为例 tinyint unsigned not null ,可以存储255岁,足够. 用int浪费了3个字节
以varchar(10) ,varchar(300)存储的内容相同, 但在表联查时,varchar(300)要花更多内存
3: 尽量避免用NULL()
原因: NULL不利于索引,要用特殊的字节来标注.
在磁盘上占据的空间其实更大.
实验:
可以建立2张字段相同的表,一个允许为null,一个不允许为Null,各加入1条,查看索引文件的大小. 可以发现,为null的索引要大些.(mysql5.5里,关于null已经做了优化,大小区别已不明显)
mysql> create database youhua;Query OK, 1 row affected (0.11 sec)mysql> use youhua;Database changedmysql> create table t1( -> name char(1) not null default '', -> key(name) -> )charset utf8;Query OK, 0 rows affected (0.57 sec)mysql> create table t2( -> name char(1), -> key(name) -> )charset utf8;Query OK, 0 rows affected (0.56 sec)
通过explain分析查询:
不允许为null的长度为3:
mysql> explain select * from t1 where name='Q'\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions: NULL type: refpossible_keys: name key: name key_len: 3 ref: const rows: 1 filtered: 100.00 Extra: Using index1 row in set, 1 warning (0.00 sec)
允许null的索引长度为4:
mysql> explain select * from t2 where name='Q'\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: t2 partitions: NULL type: refpossible_keys: name key: name key_len: 4 ref: const rows: 1 filtered: 100.00 Extra: Using index1 row in set, 1 warning (0.00 sec)
通过比较key_len发现null的索引大1.而且查询未null需要select * from t2 where name is null
4.Enum列的说明
1: enum列在内部是用整型来储存的
2: enum列与enum列相关联速度最快
3: enum列比(var)char 的弱势---在碰到与char关联时,要转化. 要花时间.
4: 优势在于,当char非常长时,enum依然是整型固定长度.
当查询的数据量越大时,enum的优势越明显.
5: enum与char/varchar关联 ,因为要转化,速度要比enum->enum,char->char要慢,
CREATE TABLE t3( sex ENUM('male','female') DEFAULT 'male' )CHARSET utf8;CREATE TABLE t4( sex VARCHAR(6) )CHARSET utf8;
插入两条数据:
mysql> insert into t3 values('male');Query OK, 1 row affected (0.11 sec)mysql> insert into t4 values('male');Query OK, 1 row affected (0.10 sec)
查询判断enum背后是整型:
mysql> select sex+1 from t3;+-------+| sex+1 |+-------+| 2 || 3 |+-------+2 rows in set (0.00 sec)mysql> select sex+1 from t4;+-------+| sex+1 |+-------+| 1 || 1 |+-------+2 rows in set, 2 warnings (0.00 sec)
但有时也这样用-----就是在数据量特别大时,可以节省IO.
| 列<---->列 | 时间 |
| Enum<--->enum | 10.53 |
| Char<---->char | 24.65 |
| Enum<---->char | 18.22 |
如果t2表的优势不明显, 加大t3的gender列 ,char(15), char(20)...
随着t3 gender列的变大,t2表优势逐渐明显.
原因----无论enum(‘manmaman’,’womanwomanwoman’) 枚举的字符多长,内部都是用整型表示, 在内存中产生的数据大小不变,而char型,却在内存中产生的数据越来越多.
总结: enum 和enum类型关联速度比较快，所以和enum对比的最好还是enum类型。
Enum 类型 节省了IO