Mysql优化之建表原则

建表原则(表的优化与列类型原则)

1.定长与变长分离
    如 id int占4个字节,char(4) 占4个字符长度,也是定长,time即每一单元值占的字节是固定的
    核心且常用字段,直建成定长,方在一张表上
    而 varchar,text,blob这种变长字段,适合单方一章表,用主键与核心表关联起来

2.常用字段要与非常用字段分离
    需要结合网站的具体业务分析,分析字段的查询场景,查询频率低的字段单拆出来

3.在1对多需要关联统计的字段,添加冗余字段
    例如,栏目组要查询栏目组下面的文章数量,如果没有冗余字段,就需要关联查询
         可以在栏目表中添加章节数量字段,可以避免关联查询                                                                                                                                                 

列类型选择

1.字段类型优先级
    整型 > date,time > emum char > varchar > blob,text

2.列特点分析
    (1) 整型:定长,没有国家/地区之分,没有字符集差异
        比如,tinyint 1 2 3 4 <==> char(1) a b c d
        从空间上看都是一字节,但是order by 排序tinyint快
        原因:后者需要考虑字符集与校对集(就是排序优先集)

        time : 定长运算快,节省时间,考虑时区,写sql不方便
        enum : 能约束值的目的,内部用整形来储存,但与char联查时,内部要经历串与值的转化
        char : 定长,考虑字符集和校对集
        varchar : 不定长,要考虑字符集的转换与排序时的校对集,速度慢
        text,blob : 无法使用内存临时表(排序操作只能在磁盘上进行)

        注意:
             date,time的选择可以直接选择使用时间戳

            char(1),3个字节
            enum("男","女") //内部转成数字来储存,多了一个转换的过程
            tinyint()  // 0 1 2 定长1字节

    (2)够用就行不要慷慨
         大的字段影响内存影响速度
         以年龄为例:tinyint unsigned not null;可以储存255岁,足够了,用int浪费3个字节
         以varchar(10),varchar(300)储存的内容相同,但在表中查询时,varhcar(300)要花用更多内存

    (3)尽量避免NULL()
        Null不利于索引,要用到特殊字节来标注
        (在磁盘上占据内存更大,mysql5.5已经做过优化,但是还是不便)
        null也不便于查询,=null 或者 != null 都查询不到值,只有使用 is null 或者 is not null 才可以
        可以在创建字段时候使用 not null default "" 的形式