二、schema与数据类型优化

一、数据类型的优化

1.更小的通常更好

​ 尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少

2.简单就好

• 简单数据类型需要更少的CPU周期，比如整型比字符操作代价更低，因为字符集和排序规则使字符比较比整型比较更复杂。
• 比如应该用MySQL内建的类型(date, time, datetime)来存储时间和日期，
• 使用整型存储IP地址。

3.尽量避免null

最好指定列为NOT NULL。因为可为NULL的列使得索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间。可为NULL的列被索引时，每个索引记录需要一个额外的字节，如果想在列上建索引，应该尽量避免设计成可为NULL的列

4.实际细则

4.1 整数类型

TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT分别使用8,16,24,32,64位存储空间。它们可以存储的值得范围从-2(N-1)到2(N-1)-1，如果是UNSIGNED，表示不允许负值，那正数的上限提高一倍

4.2 实数类型

FLOAT、DOUBLE、DECIMAL。float类型使用4个字节存储，double使用8个字节存储，decimal占用的字节数是变长的，一般对精度要求较高的使用decimal存储，比如财务数据，但是当数据量较大时，考虑使用BIGINT代替decimal，将要存储的货币单位根据小数的位数乘以相应的倍数即可。在执行效率方面，float类型最快，decimal最慢。

4.3字符串类型

VARCHAR、CHAR。VARCHAR比CHAR更节省空间，VARCHAR会使用1或2个额外的字节记录字符串的长度：如果列的最大长度小于或等于255，使用1个字节表示，否则使用2个字节表示。如果使用UTF8字符集，应该选择VARCHAR类型。CHAR适合存储很短的字符串，或者所有值都接近同一个长度。比如MD5加密后的值。对于经常变更的数据，CHAR比VARCHAR更好，因为CHAR类型不易产生碎片。

varchar应用场景：

• 存储长度波动较大的数据，如：文章，有的会有很短有的会很长
• 字符串很少更新的场景，每次更新都会重算并使用额外存储空间保存长度
• 适合报错多字节字符，如汉字，特殊字符

char应用场景：

• 存储长度波动不大的数据：如MD5摘要
• 存储短字符串，经常更细的字符串

4.4 BLOB和TEXT类型

Mysql 把每个blob 和TEXT值当做一个独立的对象处理。两者都是为了存储很大数据而设计的字符串类型，分别采用二进制和字符方式进行存储

4.5 日期和时间类型

datetime

• 占用八个字节
• 与时区无关，数据库底层时区配置，对datetime无效
• 可保存到毫秒
• 可保存时间范围大
• 不要使用字符串存储日期类型，占用空间大，损失日期类型的函数便捷性

timestap

• 占用4个字节
• 时间范围： 1970-01-01到2038-01-19
• 精确到秒
• 采用整形存储
• 依赖数据库设置的时区
• 自动更新timestamp列的值

date

• 占用的字节数比使用字符串、datetime、int存储更少，只需要3个字节
• 使用date类型还可以利用日期时间函数进行日期之间的计算
• date类型用于保存1000-01-01到9999-12-31之间的日期

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

4.7特殊类型

人们经常使用varchar(15)来存储ip地址，然而。他的本质是32为无符号整数不是字符串，可以使用

INET_ATON()和INET_NTOA函数在这两种表示方法之间转换

二、合理使用范式和反范式

• 第一范式
• 确保数据表中每列（字段）的原子性。
• 如果数据表中每个字段都是不可再分的最小数据单元，则满足第一范式。
• 例如：user用户表，包含字段id,username,password
• 第二范式
• 在第一范式的基础上更进一步，目标是确保表中的每列都和主键相关。
• 如果一个关系满足第一范式，并且除了主键之外的其他列，都依赖于该主键，则满足第二范式。
• 例如：一个用户只有一种角色，而一个角色对应多个用户。则可以按如下方式建立数据表关系，使其满足第二范式。
  user用户表，字段id,username,password,role_id
  role角色表，字段id,name
  用户表通过角色id（role_id）来关联角色表
• 第三范式
• 在第二范式的基础上更进一步，目标是确保表中的列都和主键直接相关，而不是间接相关。
• 例如：一个用户可以对应多个角色，一个角色也可以对应多个用户。则可以按如下方式建立数据表关系，使其满足第三范式。
  user用户表，字段id,username,password
  role角色表，字段id,name
  user_role用户-角色中间表，id,user_id,role_id
  像这样，通过第三张表（中间表）来建立用户表和角色表之间的关系，同时又符合范式化的原则，就可以称为第三范式。
• 反范式化
• 反范式化指的是通过增加冗余或重复的数据来提高数据库的读性能
• 例如：在上例中的user_role用户-角色中间表增加字段role_name。
  反范式化可以减少关联查询时，join表的次数。

1、范式

优点

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

缺点

• 通常进行关联

2、反范式

优点

• 所有的数据都在同一张表，可以避免关联
• 可以设计有效的索引

缺点

• 表格内的冗余比较多，删除数据的时候会造成表有些有用的信息丢失