mysql数据类型优化

1. 数据的选择

   1. 在一般情况下，要考虑到数据库中数据的大小，更小的会更好
   2. 整型比字符数据占用的cpu周期更短
   3. 在不为空的数据中写上Not null，因为null列是默认属性
   4. 不要再查询时使用 XXX=#{null} 会使得索引失效
   5. 在面对稀松数据时（多为null的数据）可以采用InnonDB的bit类型
   6. 整数选型,TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT 。分别使用8，16，24，32，64位存储空间。它们可以存储的值的范围从−2（N−1） 到2（N−1） −1
   7. 在面对不为负数的整数可以选用UNSIGNED 无符号型的整数
   8. 对于指定的数据int(1) 或者int（20） 在存储时宽度它不会限制值的合法范围
   9. 尽量只在对小数进行精确计算时才使用DECIMAL，在数据量过大的时候我们考虑使用bigint，对其*100or *1000
   10. VARCHAR 需要使用1或2个额外字节记录字符串的长度：如果列的最大长度小于或等于255字节，则只使用1个字节表示，否则使用2个字节
   11. char类型适合存储较短的数据 ，和数据定长的数据，在存储空间上char也比varchar更能省略，如只存储单字符，char（1） 只使用一个字节，而varchar（1）会需要两个字节，其中一个是用来指示长度
   12. 与char和varchar比较相似的是BINARY 和VARBINARY，但是他们是用二进制存储字符串的
   13. 在对列定义长度是，更长的列会消耗跟更多的资源
   14. blob,text是为存储特别长的数据设计的一个是采用二进制的格式，另一个是用字符串的格式
   15. MySQL把每个BLOB 和TEXT 值当作一个独立的对象处理。存储引擎在存储时通常会做特殊处理。当BLOB 和TEXT 值太大时，InnoDB会使用专门的“外部”存储区域来进行存储，此时每个值在行内需要1～4个字节存储一个指针，然后在外部存储区域存储实际的值。
   16. 对于blob和text数据，mysql不能为全部长度的数据建立索引
   17. 在mysql中适应枚举类Enum
       例

   mysql> CREATE TABLE enum_test( -> e ENUM ('fish', 'apple', 'dog') NOT NULL -> ); 
   mysql> INSERT INTO enum_test(e) VALUES('fish'), ('dog'), ('app')

结果为
缺点: 枚举类如果需要增加新列 ，可能会对现存的数据产生影响，除非每次都选择在末尾加入，如果使用数字作为枚举类的常量，可能导致数据紊乱
优点: 能充分优化表中的结构
设想解决方案:在java中定义数据时，对数据的状态设置Enum

关于日期类，dataTime和Timestamp,datetime存储时间时采用了8个字节，timeStamp则只使用了4个字节，导致比DateTime能存储的时间小的多

• timeSatmp会根据时区，而dataTime会直接根据文本时间进行存储
• 在选择类型的时候，尽量使用TimeStamp,因为他比dataTime的效率更高
• 如果需要存储比秒级更小的时间单位，推荐使用BIGINT 类型存储微秒级别的时间截，或者使用DOUBLE 存储秒之后的小数部分
• ip地中不是字符串类型，而是32位无符号整数

2. 二. 数据设计的陷阱
   1. 太多的列，在面对mysql运行时，将缓冲中的信息转换位数据机构时间，MyIsam的变长结构和innonDB的结构在转化时，会消耗大量的cpu资源
   2. 管理的表太多了，mysql的表管理限制是61张表
   3. 针对位null值的解方式，定义一个无意义的值-1 ，或者0 来将列中的null替代
3. 总结
   • 尽量避免过度设计，例如会导致极其复杂查询的schema设计，或者有很多列的表设计（很多的意思是介于有点多和非常多之间）。
   • 使用小而简单的合适数据类型，除非真实数据模型中有确切的需要，否则应该尽可能地避免使用NULL 值。
   • 尽量使用相同的数据类型存储相似或相关的值，尤其是要在关联条件中使用的列。注意可变长字符串，其在临时表和排序时可能导致悲观的按最大长度分配内存。
   • 尽量使用整型定义标识列。避免使用MySQL已经遗弃的特性，例如指定浮点数的精度，或者整数的显示宽度。
   • 小心使用ENUM 和SET 。虽然它们用起来很方便，但是不要滥用，否则有时候会变成陷阱。最好避免使用BIT 。
   • 范式是好的，但是反范式（大多数情况下意味着重复数据）有时也是必需的，并且能带来好处