MySQL--选择合适的数据类型

1.CHAR和VARCHAR

  • CHAR和VARCHAR类型相似，都用来存储字符串，但他们保存和检索的方式不同。CHAR属于固定长度的字符类型，而VARCHAR属于可变长度的字符类型。
  • CHAR检索的时候删除了尾部的空格
  • 由于CHAR是固定长度的，所以他的处理速度比VARCHAR快得多，但是其缺点是浪费存储空间，程序需要对行尾空格进行处理，所以对于那些长度变化不大并且对查询速度有较高要求的数据可以考虑使用CHAR类型来存储
  • 随着MySQL版本的不断升级，VARCHAR数据类型的性能也在不断改进并提高，所以在许多的应用中，VARCHAR类型被更多的使用
  • 在MySQL中，不同的存储引擎对CAHR和VARCHAR的使用原则有所不同
  • MyISAM：建议使用固定长度的数据列代替可变长度的数据列
  • MEMORY：目前都使用固定长度的数据进行存储，因此无论使用CHAR或VARCHAR列都没有关系。两者都是作为VARCHAR类型处理
  • InnoDB：建议使用VARCHAR类型。对于InnoDB数据表，内部的行存储格式没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），因此在本质上，使用固定长度的CHAR列不一定比使用可变长度VARCHAR列性能要好。因为，主要的性能因素是数据行使用的存储总量。由于CHAR平均占用的空间多于VARCHAR，因此使用VARCHAR来最小化需要处理的数据行的存储总量和磁盘I/O是比较好的

2.TEXT与BOLB

  • 一般在保存少量字符串的时候，我们会选择CHAR或者VARCHAR；而在保存较大文本时，通常会选择使用TEXT或者BLOB
  • 二者之间的差别是BLOB能用来保存二进制数据，比如照片；而TEXT只能保存字符数据，比如一篇文章或者日记。
  • TEXT
    • TEXT　　
    • MEDIUMTEXT　　
    • LONGTEXT　　
  • BLOB
    • BLOB　　
    • MEDIUMBLOB　　
    • LONGBLOB　　
  • BLOB和TEXT值会引起一些性能问题，特别是在执行了大量的删除操作时。删除操作会在数据表中留下很大的空洞，以后填入这些空洞的记录在插入的性能上会有影响。为了提高性能，建议定期使用OPTIMIZE TABLE功能对这类表进行碎片整理，避免因为空洞导致性能问题
  • 可以使用合成的索引来提高大文本字段的查询性能。简单来说，合成索引就是根据大文本字段的内容建立一个散列值，并把这个值存储在单独的数据列中，接下来就可以通过检索散列值找到数据就行了。但是，要注意这种技术只能用于精确匹配的查询（散列值对于类似 >, >=等范围搜索操作符是没有用处的）。可以使用MD5()函数生成散列值，也可以使用SHA1()或CRC32()，或者使用自己的应用程序逻辑来计算散列值。请记住数值散列值可以很高效的存储。同样，如果散列算法生成的字符串有尾部空格，就不要把他们存储在CHAR或VARCHAR列中，他们会受到尾部空格去除的影响。合成的散列索引对于那些BLOB或TEXT数据列特别有用。用散列标识符值去查找的速度比搜索BLOB列本身的速度快很多。
  • 如果需要对BLOB或者CLOB字段进行模糊查询，MySQL提供了前缀索引，也就是只为字段的前n列创建索引。需要注意的是，这里的查询条件中 % 不能放在最前面，否则所以将不会被使用
    • 　　CREATE INDEX index_name ON table_name (field(n))
  • 在不必要的时候避免检索大型的BLOB或TEXT值。例如，SELECT * 查询就不是很好的想法，除非能够确定作为约束条件的WHERE子句只会找到所需要的数据行。否则，很可能毫无目的的在网络上传输大量的值。这也是BLOB或TEXT标识符信息存储在合成的索引列中对用户有所帮助的列子。用户可以搜索索引列，决定需要的哪些数据行。然后从符合条件的数据行中检索BLOB或TEXT值
  • 把BLOB或TEXT列分离到单独的表中。在某些环境中，如果把这些数据列移动到第二张数据表中，可以把原数据表中的数据列转换为固定长度的数据行格式，那么他就是有意义的。这会减少主表中的碎片。可以得到固定长度数据行的性能优势。他还可以是主数据表在运行SELECT * 查询的时候不会通过网络传输大量的BLOB或TEXT值。

 

3.浮点数与定点数

  • 浮点数一般用于表示含有小数部分的数值。当一个字段被定义为浮点类型后，如果插入数据的精度超过该列定义的实际精度，则插入值会被四舍五入到实际定义的精度值，然后插入，四舍五入的过程不会报错。在MySQL中，FLOAT,DOUBLE(或REAL)用来表示浮点数
  • 定点数不同于浮点数，定点数实际上以字符串形式存放的，所以定点数可以更精确的保存数据。如果实际插入的数值精度大于实际定义的精度，则Mysql会进行警告(默认的SQLMode下)，但是数据按照实际精度四舍五入后插入；如果SQLMode是在TRADITIONAL（传统模式）下，则系统直接报错，导致数据无法插入。在MySQL中，DECIMAL(或NUMBERIC)用来表示定点数
  • 浮点数存在误差
  • 对货币等对精度敏感的数据，应该用定点数表示或存储
  • 在编程中，如果用到浮点数，要特别注意误差问题，并尽量避免做浮点数比较
  • 要注意浮点数中一些特殊值的处理

 

4.日期类型

  • MySQL提供的常用日期类型有DATE, TIME, DATETIME, TIMESTAMP
  • 根据实际需要选择能够满足应用的最小存储的日期类型
  • 如果应用只需要记录年份，那么一个字节来存储的YEAR完全可以满足，而不需要用4个字节来存储的DATE类型。这样不仅能节约存储，更能提高表的操作效率
  • 如果要记录年月日时分秒，并且记录的年份比较久远，那么最后使用DATETIME，而不要使用TIMESTAMP。因为TIMESTAMP表示的日期范围比DATETIME要短得多
  • 如果记录的日期需要让不同时区的用户使用，那么最好使用TIMESTAMP，因为日期类型中只有它能够和实际时区相对应
posted @ 2017-03-23 14:37  MicroCat  阅读(169)  评论(0)  编辑  收藏  举报