建表原则
定长与变长分离
如id int,占4个字节,char(4)占4个字符长度,也是定长,time
即每个单元值占的字节是固定的。
核心且常用字段宜建成定长放在一张表,而varchar,text,blob这种变长字段适合单放一张表,用主键与核心表关联起来
常用字段与不常用字段要分离
需结合网站的具体业务来分析,分析字段的查询场景,查询频率低的字段单独拆出来
在一对多需要关联统计的字段上添加冗余字段
如select count(b.*) from a left join b on a.id=b.aid where a.id=1这种查询宜在a表直接建立统计字段,有新增就加一,而不是通过关联查询
列选择原则
字段类型优先级
整形 > date、time > enum、char > varchar > blob、text
列的特点分析:
整形:定长,没有国家/地区之分,没有字符集的差异
比如tinyint 1,2,3,4,5和char(1) 1,2,3,4,5,从空间上都是占1字节,但order by前者更快,因为后者要考虑字符集(如utf8)与校对集(就是排序规则)
time:定长、运算快、节省空间;考虑时区,写sql不方便
enum:枚举类型,内部用整型来存储,能起到约束值的目的。但与char联查时,内部要经历串与值的转化
char:定长,考虑字符集和校对集
varchar:不定长,要考虑字符集的转换和排序时的校对集,速度慢
text/blob:无法使用内存临时表(排序等操作只能在磁盘上进行)
够用就行,不要慷慨
大的字段浪费内存,影响速度;以年龄为例tinyint unsigned not null,可以存储255岁,用int的话就浪费了3个字节;又比如varvhar(10)和varvhar(255)存储相同内容,看似varchar因为变长占用相同,但在表联查时varvhar(255)要花更多内存
尽量不实用null
原因:null不利于索引,要用特殊的字节来标注,在磁盘上占据的空间其实更大