MySQL选择合适的数据类型

一. CHAR与VARCHAR

CHAR是固定长度的字符类型，而VARCHAR属于可变长度的字符类型。它们的区别是CHAR会需要处理行尾空格，但由于是固定长度所以处理速度比VARCHAR快得多。在MySQL中，不同引擎对CHAR和VARCHAR的使用原则有所不同。

 

MyISAM：建议使用固定长度的数据列代替可变长度的数据列。

MEMORY：目前都使用固定长度的数据行存储，因此无论使用CHAR或VARCHAR列都没有关系，两者都是作为CHAR类型处理。

InnoDB：建议使用VARCHAR类型，对于InnoDB数据表，内部的行存储格式没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），因此在本质上，使用固定长度的CHAR列不一定比使用可变长度VARCHAR列性能要好。因而，主要的性能因素是数据行使用的存储总量，由于CHAR平均占用的空间多于VARCHAR，因此使用VARCHAR来最小化需要处理的数据行的存储总量和磁盘I/O是比较好的。

---

 

二. TEXT和BLOB

在保存较大文本时，通常会选择使用TEXT或者BLOB。二者之间的主要差别是BLOB能用来保存二进制数据，比如照片；而TEXT只能保存字符数据，比如一片文章或者日记。TEXT和BLOB中又分别包括TEXT、MEDIUMTEXT、LONGTEXT和BLOB、MEDIUMBLOB、LONGBLOB三种不同类型，他们之间的主要区别是存储文本长度不同和存储字节不同。

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

 

可以使用合成的（Synthetic）索引来提高大文本字段（BLOB或TEXT）的查询性能。简单来说，合成索引就是根据大文本字段的内容建立一个散列值，并把这个值存储在单独的数据列中，接下来就可以通过检索散列值找到数据行了。这种技术只能用于精确匹配的查询（散列值类似于“<”或“>=”等范围搜索操作符是没有用处的）。可以使用MD5()函数生成散列值，也可以使用SHA1()或CRC32()，或者使用自己的应用程序逻辑来计算散列值。数值型散列值可以很高效率的存储，同时也要注意尾部空格（CHAR）的问题。用散列标识符值查找的速度比搜索BLOB列本身的速度要很多。

 

上面的例子展示了合成索引的用法，由于这种技术只能用于精确匹配，在一定程度上减少了I/O，从而提高了查询效率。如果需要对BLOB或者CLOB字段进行模糊查询，MySQL提供了前缀索引，也就是只为字段的前n列创建索引。

 

对context前100个字符进行模糊查询，就可以用到前缀索引。注意，在查询条件中，“%”不能放在最前面，否则索引将不会被使用。

 

在不必要的时候避免检索大型的BLOB或TEXT值，除非能够确定作为约束条件的WHERE子句只会找到所需要的数据行，否则，很可能毫无目的的在网络上传输大量的值。

 

把BLOB或TEXT列分离到单独的表中。在某些环境下，如果把这些数据列移动到第二张数据表中，可以把原数据表中的数据列转换为固定长度的数据行格式，这会减少主表中的碎片，可以得到固定长度数据行的性能优势。它还可以使主数据表在运行SELECT * 查询的时候不会通过网络传输大量的BLOB或TEXT值。

 

---

 

三. 浮点数和定点数

如果插入一个数据的精度超过该列定义的实际精度，则插入值会四舍五入到实际定义的精度然后插入，四舍五入的过长不会报错。

定点数不同于浮点数，定点数实际上是以字符串的形式存放的，所以定点数可以更精确的保存数据。但是实际插入的数值如果超出实际定义的精度，则MySQL会进行警告（默认的SQLMode下），然后数据在四舍五入后插入；如果SQLMode是在TRADITIONAL（传统模式）下，则系统会直接报错，导致数据无法插入。

在保存单精度浮点数时，有时会产生一些误差。这是浮点数特有的问题。另外在编程时应尽量避免浮点数的比较，如果非要使用浮点数的比较则最好使用范围比较而不要用“==”比较。

 

---

 

四. 日期类型选择

如果记录的日期需要不同时区的用户使用，那么最好使用TIMESTAMP。