Mysql-schema与数据类型优化

1、数据类型优化

• 更小的通常最好

应该尽量使用可以正确存储数据的最小数据类型，更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期更少，但是要确保没有低估需要存储的值的范围，如果无法确认哪个数据类型，就选择你认为不会超过范围的最小类型。

• 简单就好

       简单数据类型的操作通常需要更少的CPU周期，例如，
      1、整型比字符操作代价更低，因为字符集和校对规则是字符比较比整型比较更复杂，
      2、使用mysql自建类型而不是字符串来存储日期和时间
      3、用整型存储IP地址

• 尽量避免null

如果查询中包含可为NULL的列，对mysql来说很难优化，因为可为null的列使得索引、索引统计和值比较都更加复杂，坦白来说，通常情况下null的列改为not null带来的性能提升比较小，所有没有必要将所有的表的schema进行修改，但是应该尽量避免设计成可为null的列。

• 实际细则

1. 整数类型。可以使用的几种整数类型：TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT分别使用8，16，24，32，64位存储空间。尽量使用满足需求的最小数据类型。
2. 字符串类型。使用最小的符合需求的长度。varchar(n)n小于等于255使用额外一个字节保存长度，n>255使用额外两个字节保存长度。例如：varchar(5)与varchar(255)保存同样的内容，硬盘存储空间相同，但内存空间占用不同，是指定的大小。
3. 字符类型。该类型最大长度255，会自动删除值末尾的空格，检索、写操作效率会比varchar高，以空间换时间。主要用于存储短字符串，经常更新的字符串。
4. 文本类型。MySQL 把每个 BLOB 和 TEXT 值当作一个独立的对象处理。
   两者都是为了存储很大数据而设计的字符串类型，分别采用二进制和字符方式存储。当我们的表中存在类似于 TEXT 或者是很大的 VARCHAR类型的大字段的时候，如果我们大部分访问这张表的时候都不需要这个字段，我们就该义无反顾的将其拆分到另外的独立表中，以减少常用数据所占用的存储空间。这样做的一个明显好处就是每个数据块中可以存储的数据条数可以大大增加，既减少物理 IO 次数，也能大大提高内存中的缓存命中率。
5. 时间类型。datetime占用8个字节，它与时区无关，可保存到毫秒，时间范围大。timestamp占用4个字节，时间范围1970-01-01到2038-01-19，精确到秒，采用整形存储，依赖数据库设置的时区。date占用3个字节，可以用日期函数计算，时间范围1000-01-01到9999-12-31
6. 枚举类型。有时可以使用枚举类代替常用的字符串类型，mysql存储枚举类型会非常紧凑，会根据列表值的数据压缩到一个或两个字节中，mysql在内部会将每个值在列表中的位置保存为整数，并且在表的.frm文件中保存“数字-字符串”映射关系的查找表

   create table enum_test(e enum('fish','apple','dog') not null);
   insert into enum_test(e) values('fish'),('dog'),('apple');
   select e+0 from enum_test;

7. 特殊类型数据。人们经常使用varchar(15)来存储ip地址，然而，它的本质是32位无符号整数不是字符串，可以使用INET_ATON()和INET_NTOA函数在这两种表示方法之间转换。

   select inet_aton('1.1.1.1')
   select inet_ntoa(16843009)

2、范式和反范式

• 范式

        优点：范式化的更新通常比反范式要快，当数据较好的范式化后，很少或者没有重复的数据，范式的数据比较小，可以放在内存中，操作比较快。

        缺点：范式通常需要进行关联。

• 反范式

       优点：所有数据都在同一张表中，可以避免关联，可以设计有效的索引。

       缺点：表内的冗余较多，删除数据时会造成有用数据的丢失。

• 实际使用注意点

       在企业中很好能做到严格意义上的范式或者反范式，一般需要混合使用。

       在一个网站实例中，这个网站，允许用户发送消息，并且一些用户是付费用户。现在想查看付费用户最近的10条信息。  在user表和message表中都存储用户类型(account_type)而不用完全的反范式化。这避免了完全反范式化的插入和删除问题，因为即使没有消息的时候也绝不会丢失用户的信息。这样也不会把user_message表搞得太大，有利于高效地获取数据。

       缓存衍生值也是有用的。如果需要显示每个用户发了多少消息（类似论坛的），可以每次执行一个昂贵的自查询来计算并显示它；也可以在user表中建一个num_messages列，每当用户发新消息时更新这个值。

3、主键的选择

• 代理主键

       与业务无关的，无意义的数字序列。

• 自然主键

       事物属性中的自然唯一标识。

• 推荐使用代理主键

       它们不与业务耦合，因此更容易维护。一个大多数表，最好是全部表，通用的键策略能够减少需要编写的源码数量，减少系统的总体拥有成本。

4、字符集的选择

        字符集直接决定了数据在MySQL中的存储编码方式，由于同样的内容使用不同字符集表示所占用的空间大小会有较大的差异，所以通过使用合适的字符集，可以帮助我们尽可能减少数据量，进而减少IO操作次数。

• 纯拉丁字符能表示的内容，没必要选择 latin1 之外的其他字符编码，因为这会节省大量的存储空间。
• 如果我们可以确定不需要存放多种语言，就没必要非得使用UTF8或者其他UNICODE字符类型，这回造成大量的存储空间浪费。
• MySQL的数据类型可以精确到字段，所以当我们需要大型数据库中存放多字节数据的时候，可以通过对不同表不同字段使用不同的数据类型来较大程度减小数据存储量，进而降低 IO 操作次数并提高缓存命中率。

5、存储引擎的选择

        Mysql的存储引擎有MyISAM、InnoDB、Memery、performance_schema等。其中最常用的是MyISAM、InnoDB，如下对两个存储引擎对比：