高性能MySql-Schema与数据类型优化

Schema与数据类型优化

更小的通常好

• 尽量使用可以正确存储数据的最下数据类型，更小的数据类型通常更快，占用更小的磁盘,内存和cpu缓存,并且处理时需要的cpu周期更少
• 但是要确保没有低估需要存储的值的范围，因为在schema中的多个地方增加数据类型的范围是一个非常耗时和痛苦的操作。如果无法确定哪个数据类型是最好的，就选择你认为不会超过范围的最小类型

简单就好

• 简单数据类型的操作通常需要更少的CPU周期。例如，整型比字符操作代价更低，
• 整型比字符操作代价更低，因为字符集和校对规则(排序规则)使字符比较比整型比较更复杂
• 使用MySQL内建的类型而不是字符串来存储日期和时间
• 用整型存储IP地址。int类型占4个字节，tinyint占1个字节。

尽量避免NUll

• 如果查询中包含null的列，会使得索引，索引统计和值比较更复杂
• 当可以NULL的列被索引时,每个索引记录需要一个额外的字节

datetime和timesamp

• datetime和timesamp都可以存储相同的数据类型,时间和日期，精确到秒。然而timesamp只使用datetime一半的存储空间
• timesamp 会根据时区变化，具有特殊的自动更新能力，允许的时间范围要小很多，有时它的特许能力会成为障碍

字段列的类型选择

• 在满足值的范围情况下，尽量选择最小的数据类型。列如tinyint比int少三个字节。tinyint 1字节 (-128，127) (0，255) 小整数值
• 字段固定长度如手机号，身份证号用char,可变长度使用varchar因为其长度固定，方便程序的存储与查找,付出的是空间的代价。varchar是以空间效率为首位的
• 整数类型通常是标识列最好的数据类型，因为他们很快并且可以使AUTO_INCREMENT
• 如果可能，尽量批量使用字符串类型，因为它们消耗更多的空间，比数字类型慢
• 如果存储UUID值，可以移除-符号。使用UNHEX()函数转换为UUID的16字节的的数字，并存储在一个binary(16)列中。取值时可以使用HEX()格式化为16进制格式

使用inet_aton和inet_ntoa处理ip地址数据

• 插入数据前，先用inet_aton把ip地址转为整型，可以节省空间，因为char(15) 占16字节。
• 显示数据时，使用inet_ntoa把整型的ip地址转为电地址显示即可。

总结

• 尽量避免过度设计，例如会导致极其复杂査询的schema设计，或者有很多列的表设计
• 使用小而简单的合适数据类型，除非真实数据模型中有确切的需要，否则应该尽可能地避免使用NULL值。
• 尽量使用相同的数据类型存储相似或相关的值，尤其是要在关联条件中使用的列。
• 注意可变长字符串，其在临时表和排序时可能导致悲观的按最大长度分配内存。
• 尽量使用整型定义标识列。
• 避免使用MySQL已经遗弃的特性，例如指定浮点数的精度，或者整数的显示宽度。
• 小心使用ENUM和SET。虽然它们用起来很方便，但是不要滥用，否则有时候会变成 陷阱。最好避免使用BIT。
• ALTER TABLE是让人痛苦的操作，因为在大部分情况下，它都会锁表并且重建整张表。例如在备机执行ALTER并在完成后把它切换为主库。