选择合适的数据类型

回到首页☞

1、CHAR 与 VARCHAR

CHAR 和 VARCHAR 类型类似，都用来存储字符串，但它们保存和检索的方式不同。CHAR 属于固定长度的字符类型，而VARCHAR 属于可变长度的字符类型。

对于字符串长度变化不大且对查询速度又较高要求的数据可以考虑使用CHAR类型，但是对于不同的存储引擎也会有一些差别：

• MyISAM存储引擎：建议使用固定长度的数据列代替可变长度的数据列；
• MEMORY存储引擎：目前都是使用的固定长度的数据行存储，因为不管是什么类型都是作为CHAR类型处理的；
• InnoDB存储引擎：建议使用VARCHAR类型。因为根据它的内部存储机制，对于固定长度和可变长度没有太大差别，都是使用指针指向数据，因此从性能上来说CHAR不一定会比VARCHAR好，而且从空间上来考虑VARCHAR更合适。

2、TEXT 与 BLOB

一般在保存少量字符串的时候，我们会选择 CHAR 或者 VARCHAR；而在保存较大文本时， 通常会选择使用 TEXT 或者 BLOB，二者之间的主要差别是 BLOB能用来保存二进制数据，比 如照片；而 TEXT 只能保存字符数据，比如一篇文章或者日记。TEXT 和 BLOB 中有分别包括 TEXT、MEDIUMTEXT、LONGTEXT 和 BLOB、MEDIUMBLOB、LONGBLOB3 种不同的类型，它们之间的主要区别是存储文本长度不同和存储字节不同，用户应该根据实际情况选择能够满足 需求的小存储类型。本节主要对 BLOB 和 TEXT 存在的一些常见问题进行介绍。

2.1、 BLOB 和 TEXT 引发的性能问题

BLOB 和 TEXT 值会引起一些性能问题，特别是在执行了大量的删除操作时。 删除操作会在数据表中留下很大的“空洞”，以后填入这些“空洞”的记录在插入的性能上 会有影响。为了高性能，建议定期使用 OPTIMIZE TABLE 功能对这类表进行碎片整理，避 免因为“空洞”导致性能问题。

下面的例子述了 OPTIMIZE TABLE 的碎片整理功能。

SELECT
	CONCAT( ROUND( SUM( DATA_LENGTH / 1024 / 1024 ), 2 ), 'M' ) 
FROM
	information_schema.TABLES 
WHERE
	table_schema = 'dev' 
	AND table_name = 't_emp'

show create table t_emp;
OPTIMIZE TABLE  dev.t_emp; 
alter table t_emp engine=innodb;
ANALYZE TABLE t_emp;

MySQL5.7已经推荐对于InnoDB的table使用 alter table table_name engine=innodb;语句的方式来进行表碎片优化。

2.2、 使用合成索引提高大文本字段（TEXT、BLOB）的查询性能

增加一列提供hashcode或者md5值，对于精准匹配有价值。
但是业务上大字段用于精准匹配的几乎没有。

2.3、前缀索引解决前模糊搜索

mysql> create index idx_ip on t(context(100));

最好的方法，不要使用大字段，大字段独立成一张表，或者放到MongoDB中，或者存储成文件。

3、 浮点数与定点数

浮点数一般用于表示含有小数部分的数值。当一个字段被定义为浮点类型后，如果插入数据的精度超过该列定义的实际精度，则插入值会被四舍五入到实际定义的精度值，然后插入，四舍五入的过程不会报错。在MySQL 中 float、double（或 real）用来表示浮点数。

在使用单精度浮点数表示时，产生了误差。这是浮点数特有的问题。因此在精度要求比较高 的应用中（比如货币）要使用定点数而不是浮点数来保存数据。

import java.math.BigDecimal; 
/* 
 * 供精确的减法运算。 
 * @param v1 
 * @param v2 
 */ 
 
public class Test { 
  
    public static void main(String[] args) throws Exception { 
      
       System.out.print("7.22-7.0=" + subtract(7.22,7.0)); 
    } 
         
    public static double subtract(double v1, double v2)    { 
      
130 
 
        BigDecimal b1 = new BigDecimal(Double.toString(v1)); 
         
        BigDecimal b2 = new BigDecimal(Double.toString(v2)); 
         
        return b1.subtract(b2).doubleValue(); 
    } 
 } 

注意：在今后关于浮点数和定点数的应用中，用户要考虑到以下几个原则：
浮点数存在误差问题；
对货币等对精度敏感的数据，应该用定点数表示或存储；
在编程中，如果用到浮点数，要特别注意误差问题，并尽量避免做浮点数比较；
要注意浮点数中一些特殊值的处理。

4、日期类型的选择

MySQL里面日期类型有DATE、TIME、DATETIME、DATESTAMP等，选用的原则基本如下：
选择能够满足应用的最小存储日期类型；比如只要年份那1个字节的YEAR就够了，没必要用4个字节的DATE；
如果记录的年份比较久远，那么最好使用DATETIME而不是DATESTAMP，因为DATESTAMP表示的日期范围要短很多；
如果记录的日期需要让不同时区的用户使用，那么要选TIMESTAMP，因为日期类型中只有它能和时区对应。

回到首页☞