Mysql 架构及优化之-数据类型优化

---

数据类型

• 整数

数字类型：整数和实数 
tinyint(8)  smallint(16) mediuint(24) int(32) bigint(64) 数字表示对应最大存储位数
如 tinyint (-127 --- 128)    tinyint unsigned 表示不允许负数 则 范围为 (0 -- 255)
常规数据库中 int(11) 只是表示控制显示字符的个数是11个   
int(1) 和 int(20) 存储和计算是一样的          即 int(1) 照样可以存储1111(4位数)

• 实数

    实数有分数部分
    
    float 和 double 类型支持使用标准的浮点运算近似计算
    float 占用4个字节 double占用8个字节
    decimal 类型用于保存精确的小数 
    decimal(18,9) 18表示小数点前后总位数 9表示小数点后面位数 
    mysql 5.0版本以上 4个字节保存9位数字 
    decimal(18,9) 共占用9个字节 小数点前4个字节 小数点后占1个 小数点后4个字节

• 字符串类型

    varchar和char类型
    
    varchar保存可变长度的字符串 比固定长度类型占用更少的存储空间 只占用需要的空间
    varchar使用额外的1到2字节存储长度 列小于255使用1字节保存长度 大于255使用2字节保存
    varchar保留字符串末尾的空格
    
    char是固定长度 保存char值时候 **mysql去掉任何末尾的空格**  
    进行比较时 空格会被填充到字符串末尾
    很多的char列 效率高于varchar 
    比如 char(1)对于单字节字符集占用1字节 varchar(1) 占用两字节 因为1字节保存长度
    
    慷慨不是明智的 分配真正需要的空间

• Blob和Text类型

    blob和text唯一区别就是blob保存二进制数据 没有字符集和排序规则

---

选择优化的数据类型

• 更小通常越好

    使用更少的磁盘 内存 cpu  确保不会低估保存的值  但是text有字符集和排序规则
    mysql不能索引这些数据类型的完整长度 也不能为排序使用索引

• 简单就好

    比较整数的代价小于比较字符 使用mysql 内建类型保存时间和日期  使用整数保存ip

• 尽量避免NULL

     mysql难以优化可空列查询  使固定索引（整数列上的索引）编程可变大小索引 
     没有值可以使用 0 或者空字符串代替
     把null 列改为not null 带来的性能提升很小

• 确定类型

    像数字 字符串 时间 直观类型可以确定
     
    但是像 datetime 和timestamp 能保存同样的类型 timestamp 使用空间只有datetime一半 
    可以保存时区

• 使用enum代替字符串类型

enum列可以保存65535不同的字符串 存储在一个 "查找表"中 mysql内部存储的是列表中的位置 
内部存储的是这个字符串对应的位置 实际表中存储的还是字符串 

创建一个表fruit category字段为enum类型 包含4种不同水果

插入4条数据 即4中不同水果 其中 最后一个菠萝(pineapple) 没有enum值 则插入了空数据
发现字段category 保存的还是字符串 其实内部已经将这些字符串关联到enum字符的位置

支持字符串搜索和位置搜索

    emu缺点在于插入数据之前 如果没有对应enum 则需要alter表结构
    enum优点在于占用更少的存储空间
    据说 enum 用于联接查询 性能也比较好

• 日期和时间类型

        datetime 保存是 1001年到9999年，精度是秒 存储值为 2016-05-06 22:39:40
        
        timestamp 保存自 1970年1月1日午夜以来的秒数 和 unix时间戳相同
        提供4字节存储 只能表示 1970年到2038年
        默认timestamp值 为 NOT NULL 
        
        mysql中提供 from_unixtime()函数把unix时间戳转换为日期
        unix_timestamp()把日期转换为unix时间戳
        如果需要秒以下的精度保存日期和时间
        可以使用bigint类型把它以毫秒的精度保存时间戳格式 或使用double保存秒的分数部分

• 选择标识符

        整数类型通常是标识符最佳选择 速度快 且能使用auto_increment
        
        避免使用字符串做标识符 占用很多空间并且比整数类型要慢

• 特殊类型的数据

       通常使用varchar(15)保存IP地址 其实IP地址是无符号的32位整数 
       不是字符串 小数点仅仅为了可读性
       mysql提供了 inet_aton() inet_ntoa() 用于 ip地址和整数之前转换