mysql Schema与数据类型优化

 

 

选择优化的数据类型：

• 更小通常更好，因为占用磁盘空间少，内存和cpu缓存少，处理所需要的cpu周期更少
• 简单就好，字符串比整数操作代价更高，因为有一些“字符集”和“排序规则”将是一个耗时的操作
• 尽量避免可为null，通常情况下最好使用 not null ，除非必须为null。因为如果包含可为null的列，mysql优化的时候更为困难，因为可为null的列使得索引，统计索引和值都比较复杂,可为null的列会使用更多的空间，当为可为null列建立索引时，需要额外一个字节。如果在指定的列上建立索引，就应该避免设为可为null的列。
• innodb使用单独的bit存储null值，所以对于系数的数据有很好的效率。
• 时间类型经历使用timestamp，因为temstamp只占用datetime一半的存储空间，并且会根据时区变化，具有独特的更新能力，另一方面timestamp允许的时间范围小的多，有时候它的特殊能力会成为障碍，比如范围是：1970年到2038年。

 

整数类型：

• 如果存储整数可以使用，tinyint、 smallint、mediumint、int、 bigint，分别是8、16、24、32、64位存储空间。
• 整数类型不允许负数，这大大提高了整数的效率。
• mysql会为整数类型指定宽度，比如int(11)，对于大多数应用这是没意义的，对于存储和计算来说，int(1) and int (20)是相同的。

实数类型：

• 在mysl5.0以上的版本中，对decimal类型实现了高度精确，cpu直接支持原生浮点计算，所以浮点运算明显更快。
• mysql使用double作为内部浮点计算的类型，占用8个字节，float占用4个字节
• decmail是一种存储格式，在mysql内部会转换成double类型
• decmail类型是可以指定精度的 ,比如decimal（18,9），但是mysql内部会悄悄的转换为double，所以就别指定了，直接指定类型即可。
• decmail适合存储财务类的数据，但在大的情况下可以使用bigint代替。（代替会有一些细节问题，还有如果数据需要精确到万分之一分，那么转换也存在一些问题，到时候你自己去查吧，我就不写了）

字符串类型：

• varchar与char类型都是字符串的类型，不同的存储引擎在内存中存储字符串是不一样的，所以直接不解释。
• varchar类型用于存储可变长的字符串，它比定长字符串更节省空间。
• varchar类型需要多出两个字节来存储长度信息
• 由于字符串是可以变长的，所以更新字符串就效率低
• innodb更为灵活，可以把过长的varchar存储为blob
• char是定长的，根据长度分配空间，当存储char值时，mysql会删除字符后的空格，varchar则不会。
• char适合存储最短的字符类型，或者一个等值，固定的值，比如md5
• 对于经常变更的数据，char也比varchar更好，因为char类型不容易产生碎片。
• char存储Y/N单字节只需要一个字节，而字符串则需要两个，另外一个是记录长度信息。
• 与char和varchar类似的还有binary和varbinary他们是存储二进制的字符串，二进制跟字符串非常相似，二进制存储的时字节码而不是字符。
• 慷慨是不明智的，存储hello，指定varchar（2）and varchar（200）是有很大区别的，更长的列会消耗更大的内存，尤其是内存临时表进行排序和磁盘临时表排序都非常糟
• blob和text类型，当这两个类型值太大的时候，innodb会额外指定分配其它区域来存储，此时每个值在行内需要1—4个字节存储一个指针指定。
• blob是二进制类型，没有排序和字符集，而text；是有字符集和排序规则。
• mysql不能将blob和text两种类型不能对全部长度的字符串进行索引，也不能使用这些索引消除排序。

使用枚举代替字符串类型：

• 枚举可以代替字符串，可以把一些不重复的字符串存储我一个枚举集合
• mysql内部存储枚举会非常紧凑，会把枚举集合压缩成一两个字节
• mysql内部会将枚举的集合值的位置，存储为整数，并且在表.frm文件中保存为“数字--字符串”
• mysql把枚举的搞成“数字--字符串”，所以排序的时候就坑爹了，因为是按照数字排序的。但是有办法处理的，可以使用field()函数显示排序，但是这将导致mysql无法利用索引消除排序
• 枚举最大的缺点，就是字符串列表是固定的，对于变的字符串使用枚举不是一个好主意，除非可以接受只在列表上末尾追加元素
• 反正枚举好处不多，尽量不使用

日期和时间类型：

• timestamp和datetime不一样，前者提供的值与时区有关系，而后者只是保留时间文本。
• 通过应该使用timestamp效率更高。

 

位数据类型：

• mysql提供了bit类型，对于大部分应用应该避免使用，如果想使用bit创建false/true值，可以使用char（0）

 

选择合适的标识符：

• 整数通常是最好的选择，应该它们很快并且可以使用auto_increment
• enum和set类型是糟糕的选择，enum和set使用存储固定没有变的值，比如产品状态，人的性别，但是大部分情况下不要这么搞，这都是前人告诉你的的坑，知道就行了，你要是去研究你就是屌丝。
• 避免使用字符串，因为比较消耗空间，并且比数字类型慢。

特殊的数据类型：

• 类型ipv4人们通过使用varchar(15)类存储，其实它是32位无符号整数，只是用“.”隔开方便阅读，应该使用mysql提供的inet_aton（）和inet_neoa()函数进行转换。