mysql 数值 字符 优化_【实战】mysql 优化数据类型

1.更小的通常更好

选择不会超过范围的最小类型

2.简单就好

例如，整型比字符操作代价更低，因为字符集和校对规则(排序规则)使字符比较比整形比较更复杂。

3.尽量避免null

如果查询中包含可为null的列，对mysql来说更难优化，因为可为null的列使得索引，索引统计和值比较 都更复杂。

可为null的列会使用更多的存储空间，在mysql里也需要特殊处理。当可为null的列被索引时，每个索引记录需要一个额外的字节，在myISAM里甚至还可能导致固定大小的索引(例如只有一个整数列的索引)变成可变大小的索引。

通常把可为null的列改为not null 带来的性能提升比较小，所以调优时没必要首先在现有的schema中查找并修改掉这种情况。除非确定这会导致问题。但是，如果计划在列上建索引，就应该避免设计成可为null的列。

也有例外，InnoDB使用单独的bit存储null值，所以对于稀疏数据有很好的空间效率。但这一点不适用于myISAM。

4.整数类型：

tinyint(8),smallint(16),mediumint(24),int(32),bigint(64)

mysql可以为整数类型指定宽度，例如int(11)，对大多数应用这是没有意义的：它不会限制值的合法范围，只是规定了mysql的一些交互工具(例如mysql命令行客户端)用来显示字符个数。

实数类型：

不只是为了存储小数部分；也可以使用decimal存储比bigint还大的整数。mysql既支持精确类型，也支持不精确类型。

float和double，是cpu直接支持的浮点计算，所以浮点运算明显更快。

decimal类型用于存储精确的小数。

浮点和decimal类型都可以指定精度。对于decimal列，可以指定小数点前后所允许的最大位数。这会影响列的空间消耗。mysql 5.0和更高版本将数字打包保存到一个二进制字符串中(每4个字节存9个数字)。例如decimal(18,9)小数点两边各存储9个数字，一共使用9个字节：小数点前的数字用4个字节，小数点后的数字用4个字节，小数点本身占一个字节。

有多重方法可以指定浮点列所需的精度，这会使得mysql悄悄选择不同的数据类型，或者在存储时对值进行取舍。这些精度定义是非标准的，所以我们建议只制定数据类型，不指定精度。

因为需要额外的空间和计算开销，所以应该尽量只在对小鼠进行精确计算时才使用decimal例如存储财务数据。在数据量比较大的时候，可以考虑用bigint代替decimal，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。

5.字符串类型：

varchar和char

varchar用于存储可变长字符串，需要使用1或2个额外字节记录字符串的长度；

如果列的最大长度小于或等于255，则只使用1个字节表示，否则用2个字节。节省存储空间，但update更复杂。长度方差大，列的更新少，“使用了像utf-8”这样复杂的字符集，每个字符都使用不同的字节数进行存储更适合用varchar。

innodb可以把过长的varchar存储为blob。

char适合存储很短的字符串，或所有值都接近同一个长度。例如密码的md5值。

填充和截取空格的行为在不同的存储引擎都是一样的，因为这是在mysql服务器层进行处理的。

binary和varbinary

binary和varbinary存储的是二进制字符串。二进制字符串跟常规字符串非常相似，但是二进制存储的是字节码而不是字符。填充也不一样：mysql填充binary采用的是\0(零字节)而不是空格，在检索时也不会去掉填充值。mysql比较binary字符串时，每次按一个字节，并且根据该字节的数值进行比较。因此二进制比较比字符比较简单很多，所以也就更快。

blob和text类型

blob和text都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。

与其他类型不同，mysql把每个blob和text当作一个独立的对象处理。存储引擎在存储时通常会做特殊处理。当blob和text值太大时，innodb会使用专门的“外部”存储区域进行存储，此时每个值在行内需要1-4字节存储一个指针，然后在外部存储区域存储世纪的值。

blob存储二进制数据，没有排序规则或字符集，而TEXT类型有字符集和排序规则。

与其他类型不同在排序时，只对每个列的最前max_sort_length字节而不是真个字符串做排序。

memory不支持blob和text类型。尽量避免使用这2种。

使用枚举代替字符串类型

实际存储的是整数而非字符串，排序也是按整数排。其在于enum关联时效率较高，但是与varchar关联比varchar关联varchar还要慢。

启发：在“查找表”时采用证书主键而避免采用基于字符串的值进行关联。

日期和时间类型

mysql能存储的最小时间粒度为秒

datetime和TIMESTAMP

datetime吧日期和时间封装到格式为YYYMMDDHHMMSS的整数中，与时区无关。使用8个字节的存储空间。1001-9999

默认情况下mysql以一种可排序的，无歧义的格式显示DATETIME值。例如“2008-01-16 22:37:00”

TIMESTAMP

4字节只能表示1970-2038年，显示的值依赖于时区。默认情况下，如果插入时没有指定第一个timestamp列的值，mysql则设置这个列为当前时间。在插入一行记录时，mysql默认也会更新第一个timestamp列的值(除非在update语句中明确指定了值)。timestamp列默认为not null。