MySql 数据表结构优化总结

1. 数据类型优化：
   1. 字段数据类型：

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

      案例：

      设计两张表，设计不同的数据类型，查看表的容量

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

   3. 整数类型：

      可以使用的几种整数类型：TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT分别使用8，16，24，32，64位存储空间。尽量使用满足需求的最小数据类型

   4. 字符串和字符类型

      char长度固定，即每条数据占用等长字节空间；最大长度是255个字符，适合用在身份证号、手机号等定长字符串

      varchar可变程度，可以设置最大长度；最大空间是65535个字节，适合用在长度可变的属性

      text不设置长度，当不知道属性的最大长度时，适合用text

      按照查询速度：char>varchar>text

   5. BLOB和TEXT类型

      MySQL 把每个 BLOB 和 TEXT 值当作一个独立的对象处理。

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

   6. datetime和timestamp

      不要使用字符串类型来存储日期时间数据

      日期时间类型通常比字符串占用的存储空间小

      日期时间类型在进行查找过滤时可以利用日期来进行比对

      日期时间类型还有着丰富的处理函数，可以方便的对时间类型进行日期计算

      使用int存储日期时间不如使用timestamp类型

   7. 使用枚举代替字符类型

      有时可以使用枚举类代替常用的字符串类型，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;

   8. 特殊类型数据

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

      案例：

      select inet_aton('1.1.1.1')

      select inet_ntoa(16843009)

2. 主键的选择：
   1. 代理主键 ： 与业务无关的，无意义的数字序列
   2. 自然主键 ： 事物属性中的自然唯一标识
   3. 推荐使用代理主键 ： 它们不与业务耦合，因此更容易维护；一个大多数表，最好是全部表，通用的键策略能够减少需要编写的源码数量，减少系统的总体拥有成本
3. 存储引擎选择对比：            
4. 适当的数据沉余：
   1. 被频繁引用且只能通过 Join 2张(或者更多)大表的方式才能得到的独立小字段。
   2. 这样的场景由于每次Join仅仅只是为了取得某个小字段的值，Join到的记录又大，会造成大量不必要的 IO，完全可以通过空间换取时间的方式来优化。不过，冗余的同时需要确保数据的一致性不会遭到破坏，确保更新的同时冗余字段也被更新。
5. 适当拆分:例如在一张表中，有字段存储数据太大，或者有多个不常用的字段，可以单独把大的存储字段和不常用的字段单独拆分到另一张表中。