mysql schema数据混乱_mysql--Schema与数据类型优化

存储引擎

在MySQL5.5版本将InnoDB作为默认的存储引擎。

数据类型优化

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

timestamp只使用datetime一般的存储空间，并且会根据时区变化。

整数类型

tinyint、smallint、mediumint、int、bigint分别使用8、16、24、32、64为存储空间。

MySQL可以为整数类型指定宽度，例如INT(11)，对大多数应用这是没有意义的：它不会限制值的合法范围，只是规定MySQL的一些交互工具用来显示字符的个数。对于存储和计算来说，INT(1)和INT(20)是相同的。

实数类型

实数是带有小数部分的数字。DECIMAL类型用于存储精确的小数，但因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用DECIMAL(例如存储财务数据)

VARCHAR和CHAR类型

VARCHAR类型

VARCHAR类型用于存储可变长字符串，它比定长类型更节省空间，VARCHART需要使用额外的1或2个额外字节记录字符串的长度，如果列的最大长度小于或等于255字节，则只使用1个字节表示，否则使用2个字节。假设使用latin1字符集，一个VARCHAR(10)的列需要11个字节的存储空间。VARCHAR(1000)的列需要1002个字节，因为需要2个字节存储长度信息。

在InnoDB中，它可以把过长的VARCHAR存储为BLOB，我们稍后讨论这个问题。

CHAR

CHAR类型是定长的：MySQL总是根据定义的字符串长度分配足够的空间。当存储CHAR值时，MySQL会删除所有的末尾空格。CHAR值会根据需要采用空格进行填充以方便计较。

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

BLOG和TEXT类型

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

BOLB和TEXT家族之间仅有的不同是BLOB类型存储的是二进制数据，没有排序规则或字符集，而TEXT类型有字符集和排序规则。

MySQL对BLOG和TEXT列进行排序与其他类型是不同的：它只对没个列的最前max_sort_length字节而不是整个字符串做排序。如果只需要排序前面一小部分字符，则可以减小max_sort_length的配置，或者使用ORDER BY SUSTRING(column, length)。

使用枚举(ENUM)代替字符串类型

有时候可以使用枚举代替常用的字符串类型。枚举列可以把一些不重复的字符串存储成一个预定义的集合。MySQL在存储枚举时非常紧凑，会根据列表值的数量压缩到一个或者两个字节中。MySQL内部会将每个值在列表中的位置保存为整数，并且在表.frm文件中保存“数字-字符串”映射关系的“查找表”。

日期和时间类型

DATETIME

这个类型能保存大范围的值，从1001年到9999年，精度为秒。它把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中，与时区无关。使用8个字节的存储空间。

TIMESTAMP

这个类型保存了1970年1月1日午夜以来的秒数，TIMESTAMP只使用4个字节的存储空间，只能表示1970年到2038年。MySQL提供了FROM_UNIXTIME()，并提供了UNIX_TIMESTAMP()函数把日期转换为Unix时间戳。

特殊类型数据

IPv4地址应该使用无符号整数来存储，MySQL提供INET_ATON()和INET_NTOA()函数在这两种表示方法之间转换。

一些琐碎的知识

加快ALTER TABLE操作的速度

MySQL的ALTER TABLE操作的性能对大表来说是个大问题。MySQL执行大部分修改表结构操作的方法是用新的结构创建一个空表，从旧表中查出所有的数据插入新表，然后删除旧表

参考书目：《高能能MySQL(第3版)》