深入浅出mysql_选择合适的数据类型

八. 选择合适的数据类型


8.1 CHAR 与 VARCHAR


保存方式：


char为固定长度 不足的将用空格 补齐


检索方式：


char 检索时将删除尾部的空格


优缺点：


由于char是固定长度的，所以char的处理速度更快，缺点是占用了更多的存储空间，程序需要对尾部进行空格补齐，对于长度变化不大，并对查询速度有较高要求的的可以使用char
随着mysql的升级，varchar的性能也不短的提高。


不同的引擎 char和varchar的使用规则


MyISAM:建议使用固定长度的列取代可变长度的列


MEMORY：目前使用固定长度数据进行存储，所以无论使用char和varchar 两者都当做char来处理


InnoDB：建议使用varchar 类型，对于InnoDB数据表，内部的行存储格式没有区分固定长度和可变长度列（所有数据行都指向数据列值得头指针）。因此本质上固定长度的char性能不一定比varchar的性能要好。


8.2 TEXT 与 BLOB


二者的主要差别


BLOB能用来保存二进制数据，比如照片等。
而TEXT只能保存字符数据。
TEXT 和 BLOB 中有分别包括TEXT、MEDIUMTEXT、LONGTEXT 和 BLOB、MEDIUMBLOB、LONGBLOB3 种不同的类型，它们之间的主要区别是存储文本长度不同和存储字节不同，用户应该根据实际情况选择能够满足需求的最小存储类型


TEXT和BLOB值会引起一些性能问题，特别是在执行大量的删除操作时。
删除会在数据表中留下很大的空洞，以后再填入这些空洞记录插入性能会有很大的影响。
建议定期使用 OPTIMIZE TABLE 的碎片整理功能 
>OPTIMIZE TABLE tablename;


可以使用合成的（synthetic）索引来提高大文本字段的查询性能


是什么


简单来说 合成索引就是根据大文本字段的内容建立一个散列值，并把这个值单独的存储在数据列中，接下来就可以使用散列值来检索数据，但是这用技术只能用于精确匹配查询


前缀索引可用于模糊查询


是什么


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


范例


mysql> create table t (id varchar(100),context blob,hash_value varchar(40));
Query OK, 0 rows affected (0.03 sec)


mysql> create index idx_blob on t(context(100));
Query OK, 3 rows affected (0.04 sec)
Records: 3 Duplicates: 0 Warnings: 0


mysql> desc select * from t where context like 'beijing%' \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
type: range
possible_keys: idx_blob
key: idx_blob
key_len: 103
ref: NULL
rows: 2
Extra: Using where
1 row in set (0.00 sec)


在不必要的时候 避免检索BLOB或者TEXT值


例如，SELECT * 查询就不是很好的想法，除非能够确定作为约束条件的 WHERE 子句只会找到所需要的数据行。否则，很可能毫无目的地在网络上传输大量的值。这也是 BLOB 或 TEXT标识符信息存储在合成的索引列中对用户有所帮助的例子。用户可以搜索索引列，决定需要的哪些数据行，然后从符合条件的数据行中检索 BLOB 或 TEXT 值。


把BLOB或TEXT值放到单独的表中


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


8.3 浮点数与定点数


浮点数和定点数的区别


浮点数：一般用于表示含有小数部分的数值。当一个字段被定义为浮点类型后，如果插入数据的精度超过该列定义的实际精度，则插入值会被四舍五入到实际定义的精度值，然后插入，四舍五入的过程不会报错。在 MySQL 中 float、double（或 real）用来表示浮点数。
定点数：不同于浮点数，定点数实际上是以字符串形式存放的，所以定点数可以更加精确的保存数据。如果实际插入的数值精度大于实际定义的精度，则 MySQL 会进行警告（默认的 SQLMode 下），但是数据按照实际精度四舍五入后插入；如果 SQLMode 是在 TRADITIONAL（传统模式）下，则系统会直接报错，导致数据无法插入。在 MySQL 中，decimal （或 numberic）用来表示定点数。


定点数和浮点数的应用原则：


1浮点数存在误差问题
2对货币等对精度敏感的问题应该应用定点数表示
3在编程中 用到浮点数要特别注意误差问题，避免用浮点数比较
4注意浮点数的一些特殊操作


8.4 日期类型选择


1 根据实际需要选择能够满足应用的最小存储的日期类型。如果应用只需要记录“年份”，那么用 1 个字节来存储的 YEAR 类型完全可以满足，而不需要用 4 个字节来存储的 DATE 类型。这样不仅仅能节约存储，更能够提高表的操作效率。


2 如果要记录年月日时分秒，并且记录的年份比较久远，那么最好使用 DATETIME，而不要使用 TIMESTAMP。因为 TIMESTAMP 表示的日期范围比 DATETIME 要短得多。


3如果记录的日期需要让不同时区的用户使用，那么最好使用 TIMESTAMP，因为日期类型中只有它能够和实际时区相对应。