MySQL数据类型设计

整数类型

• 整数:tinyint(8bit存储空间)、smallint(16bit存储空间)、mediumint(24bit存储空间)、int(32bit存储空间)、bigint(64bit存储空间)
• 整数有可选的unsigned属性,表示不允许为负值,无符号和有符号类型使用相同的存储空间,并具有相同的性能
• 整数计算一般使用64bit的bigint整数,即使在32bit的环境也是如此

实数

• float和double支持使用标准的浮点运算进行近似计算
• decimal类型用于存储精确的小数,在数据量比较大的时候,可以考虑bigint代替decimal,即将需要存储的货币单位根据小数位数乘以相应的倍数

字符串类型

• varchar

  • 存储可变长的字符串,比定长类型的字符串更加节省空间,仅仅使用必要的空间,但是如果mysql使用row_format=fixed创建表的时候将使用定长存储
  • varchar需要使用额外的1-2bit记录字符串的长度,若列的最大长度<=255bit,则只使用1个字节表示,否则使用2个字节表示
  • 由于行是可变长的,导致在做update的时候会变得比原来更长
  • 如果一个行占用的空间增长,并且在页内没有更多的空间可以存储
    • 对于MyISAM的执行引擎:会将行拆分成不通的片段进行压缩
    • InnoDB:需要分裂页来使行可以放进页内
  • MySQL5.0以上的在存储和检索的时候会保留末尾的空格,但是在4.1的时候或更老的版本中会剔除空格
  • 使用场景:
    • 1)字符串列的最大长度比平均长度大很多;
    • 2)列的更新很少,所以碎片不是问题;
    • 3)使用像UTF-8这样复杂的字符集,每个字符都使用不同的字节数来进行存储

• char

  • MySQL总是根据定义的字符串长度分配足够的空间,使用char类型进行存储的时候,mysql会删除所有的末尾空格
  • 使用场景:
    • 1)对于经常变更的数据,char定长存储不容易产生碎片,因而会有更好的性能
    • 2)char可以用来存储密码的md5值

• Blob与Text类型

  • 字符类型:tinytext、smalltext、text、mediumtext、longtext
  • 二进制类型:tinyblob、smallblob、blob、mediumblob、longblob
  • Innodb在blob和text存储过大的时候,会使用外部存储区域来进行存储,此时需要每个值在行内需要1-4个字节存储一个指针,然后在外部存储区域存储实际的值

• 日期和时间类型

  • DateTime:能保存大范围的值,精度是秒,它把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中,与时区无关,使用8个字节的存储空间
  • TimeStamp:只使用4个字节的存储空间,因此它的范围比DateTime小得多,一般情况下尽量使用TimeStamp,因为它的空间效率更高
  • 如果需要存储更小的细粒度时间,可以使用bigint或者是double存储秒之后的小数部分或者使用MariaDB

• 位数据类型（从技术上角度都是字符串类型,但都尽量避免使用）

  • bit（尽量避免使用）
    • mysql把bit当做字符串类型,而不是数字类型,检索的时候是一个包含二进制0或1值的字符串
    • 在数字的上下文中,是将位字符串转换层数字
    • 若在一个bit的存储空间上存储一个true/false的值,可以考虑使用一个为空的CHAR(0)列,该列可以保存空值NULL或者长度为0的字符串
  • set（修改字段是整表修改的,并且也无法在SET列上通过索引查找）-- 应用场景不强,不深入

• Id的标识符选择

  • 选择的思考:一个是考虑存储类型,一个是MySQL对这种存储类型的执行计算和比较如何
  • 落地的原则:一旦选择相应的存储类型,要保证关联的表中也尽量靠近或者是选择相同的存储类型
  • 最小原则:选择的标识符既要考虑当前的业务情况还要考虑未来可能增长的情况,比如建立一个列Id来存储省份的名称标识,这时候是不需要几千或者几万的值,可以考虑tinyint
  • 选择的类型比较:
    • 整数:可以使用AUTO_INCREMENT并且在建立索引抑或是查询的时候具有良好的性能（存储在磁盘中占用的空间按照指定的整数类型存储,不像字符串那样分散）
    • 字符串:比较消耗空间,并且如果使用MyISAM的执行引擎时会对字符串进行压缩,这样会导致磁盘查询比较慢
      • 存储UUID,应当移除’-'符号,并用unhex()函数转换为16字节的数字并存储在一个binary(16)列中,检索时通过hex()函数格式化为16进制格式
      • UUID、散列函数（md5/sha1）等生成的数据会任意分布在很大的空间内,会导致insert以及一些select语句变得很慢

• 特殊类型

  • 存储地址IP,可以使用无符号的32位整数进行存储,IP实际上就是32位无符号整数类型,不是字符串,可以使用MySQL的函数
  • IMET_ATON():将IP地址转换为32位无符号整数
  • INET_NTOA():将32位无符号整数转换为IP地址