MySQL之数据类型，范式，主键，字符集
要在数据类型方面优化MySQL，应该先理解MySQL的各个数据类型的特点和范围
MySQL数据类型
数值类型
| 类型 | 大小 | 范围（有符号） | 范围（无符号） | 用途 |
|---|---|---|---|---|
| TINYINT | 1 Bytes | (-128，127) | (0，255) | 小整数值 |
| SMALLINT | 2 Bytes | (-32 768，32 767) | (0，65 535) | 大整数值 |
| MEDIUMINT | 3 Bytes | (-8 388 608，8 388 607) | (0，16 777 215) | 大整数值 |
| INT或INTEGER | 4 Bytes | (-2 147 483 648，2 147 483 647) | (0，4 294 967 295) | 大整数值 |
| BIGINT | 8 Bytes | (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) | (0，18 446 744 073 709 551 615) | 极大整数值 |
| FLOAT | 4 Bytes | (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) | 0，(1.175 494 351 E-38，3.402 823 466 E+38) | 单精度 浮点数值 |
| DOUBLE | 8 Bytes | (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 双精度 浮点数值 |
| DECIMAL | 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 | 依赖于M和D的值 | 依赖于M和D的值 | 小数值 |
日期/时间类型
| 类型 | 大小 ( bytes) | 范围 | 格式 | 用途 |
|---|---|---|---|---|
| DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
| TIME | 3 | ‘-838:59:59’/‘838:59:59’ | HH:MM:SS | 时间值或持续时间 |
| YEAR | 1 | 1901/2155 | YYYY | 年份值 |
| DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
| TIMESTAMP | 4 | 1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07 | YYYYMMDD HHMMSS | 混合日期和时间值，时间戳 |
字符串类型
| 类型 | 大小 | 用途 |
|---|---|---|
| CHAR | 0-255 bytes | 定长字符串 |
| VARCHAR | 0-65535 bytes | 变长字符串 |
| TINYBLOB | 0-255 bytes | 不超过 255 个字符的二进制字符串 |
| TINYTEXT | 0-255 bytes | 短文本字符串 |
| BLOB | 0-65 535 bytes | 二进制形式的长文本数据 |
| TEXT | 0-65 535 bytes | 长文本数据 |
| MEDIUMBLOB | 0-16 777 215 bytes | 二进制形式的中等长度文本数据 |
| MEDIUMTEXT | 0-16 777 215 bytes | 中等长度文本数据 |
| LONGBLOB | 0-4 294 967 295 bytes | 二进制形式的极大文本数据 |
| LONGTEXT | 0-4 294 967 295 bytes | 极大文本数据 |
数据类型更小的通常更好
在建表选择字段的数据类型时，应该尽量使用可以正确存储数据的最小数据类型，更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期更少，但是要确保没有低估需要存储的值的范围。
在满足条件的情况下尽量选择简单的数据类型
简单数据类型的操作通常需要更少的CPU周期，例如：
- 整型比字符操作代价更低，因为字符集和校对规则是字符比较比整型比较更复杂，
- 使用mysql自建类型而不是字符串来存储日期和时间
- 用整型存储IP地址
尽量避免使用null
在MySQl中null并不等于null，而且需要额外的列来描述是否允许为null。
同时，如果查询中包含可为NULL的列，对mysql来说很难优化，因为可为null的列使得索引、索引统计和值比较都更加复杂。
整数类型
可以使用的几种整数类型：TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT分别使用8，16，24，32，64位存储空间。尽量使用满足需求的最小数据类型。
INT(11) 中的数字只是规定了交互工具显示字符的个数，对于存储和计算来说是没有意义的。
字符和字符串类型
MySQL实际应用最多的是char，varchar，text等，它们最关键的不同在于最长度的控制，长度越固定，查询效率通常越快，长度控制越松散，存储内容更灵活时查询效率就越低。
char
特点：
- 最大长度：255；长度固定，即每条数据占用等长字节空间
- 会自动删除末尾的空格（varchar会保留空格）
- 检索效率、写效率会比varchar高，以空间换时间
应用场景：
- 存储长度波动不大的数据，如：md5摘要，身份证号、手机号等定长字符串。
- 存储短字符串、经常更新的字符串
varchar
VARCHAR 这种变长类型能够节省空间，因为只需要存储必要的内容。但是在执行 UPDATE 时可能会使行变得比原来长，当超出一个页所能容纳的大小时，就要执行额外的操作。MyISAM 会将行拆成不同的片段存储，而 InnoDB 则需要分裂页来使行放进页内。
特点：
- 往往使用最小的符合需求的长度，长度可变，可以设置最大长度。
- varchar(n) n小于等于255使用额外一个字节保存长度，n>255使用额外两个字节保存长度。
- varchar(5)与varchar(255)保存同样的内容，硬盘存储空间相同，但内存空间占用不同，是指定的大小 。
- varchar在mysql5.6之前变更长度，或者从255一下变更到255以上时时，都会导致锁表。
应用场景：
- 存储长度波动较大的数据，如：文章，有的会很短有的会很长
- 字符串很少更新的场景，每次更新后都会重算并使用额外存储空间保存长度
- 适合保存多字节字符，如：汉字，特殊字符等
text与BLOB
text不设置长度，当不知道属性的最大长度时，适合用text，MySQL 把每个 BLOB 和 TEXT 值当作一个独立的对象处理。
两者都是为了存储很大数据而设计的字符串类型，分别采用二进制和字符方式存储。
时间类型
时间类型的选择要看需要的精度
datatime
- 占用8个字节
- 与时区无关，数据库底层时区配置，对datetime无效
- 可保存到毫秒
- 可保存时间范围大
- 不要使用字符串存储日期类型，占用空间大，损失日期类型函数的便捷性
timestamp
- 占用4个字节
- 时间范围：1970-01-01到2038-01-19
- 精确到秒
- 采用整形存储
- 依赖数据库设置的时区
- 自动更新timestamp列的值
date
- 占用的字节数比使用字符串、datetime、int存储要少，使用date类型只需要3个字节
- 使用date类型还可以利用日期时间函数进行日期之间的计算
- date类型用于保存1000-01-01到9999-12-31之间的日期
枚举类型
用枚举类型代替字符串类型，可以用在数据为多个但为定值的列里，比如：状态，是否，性别等
mysql存储枚举类型会非常紧凑，会根据列表值的数据压缩到一个或两个字节中，mysql在内部会将每个值在列表中的位置保存为整数，并且在表的.frm文件中保存“数字-字符串”映射关系的查找表
1 | create table enum_test(e enum('fish','apple','dog') not null); |
特殊类型数据
人们经常使用varchar(15)来存储ip地址，然而，它的本质是32位无符号整数不是字符串，MySQL提供了INET_ATON() 和 INET_NTOA() 函数，用于IPv4地址在整型和字符串之间的转化。
1 | select inet_aton('1.1.1.1') |
范式与反范式
大家都知道数据库设计时要遵循反范式原则，这样可以尽可能的降低数据冗余，但数据之间的联系本身就需要数据冗余（例如外键），所以在实际开发中要根据我们的需求来权衡范式与反范式。
范式
优点
- 范式化的更新通常比反范式要快
- 当数据较好的范式化后，很少或者没有重复的数据
- 范式化的数据比较小，可以放在内存中，操作比较快
缺点
- 通常需要进行关联
反范式
优点
- 所有的数据都在同一张表中，可以避免关联
- 可以设计有效的索引；
缺点
- 表格内的冗余较多，删除数据时候会造成表有些有用的信息丢失
案例
在企业中很好能做到严格意义上的范式或者反范式，一般需要混合使用：
在一个网站实例中，这个网站，允许用户发送消息，并且一些用户是付费用户。现在想查看付费用户最近的10条信息。 在user表和message表中都存储用户类型(account_type)而不用完全的反范式化。这避免了完全反范式化的插入和删除问题，因为即使没有消息的时候也绝不会丢失用户的信息。这样也不会把user_message表搞得太大，有利于高效地获取数据。
另一个从父表冗余一些数据到子表的理由是排序的需要。
缓存衍生值也是有用的。如果需要显示每个用户发了多少消息（类似论坛的），可以每次执行一个昂贵的自查询来计算并显示它；也可以在user表中建一个num_messages列，每当用户发新消息时更新这个值。（所以实际上我们要权衡时应该用count(*) 还是增加一个列 num_messages，每次去更新这个列）
主键的选择
主键分为代理主键与自然主键：
- 代理主键：与业务无关的，无意义的数字序列
- 自然主键：事物属性中的自然唯一标识
一般推荐使用代理主键，因为它们不与业务耦合，因此更容易维护。另外，数据库里大多数表，最好是全部表，通用的键策略能够减少需要编写的源码数量，减少系统的总体拥有成本。（比如雪花算法生成ID）
字符集的选择
utf8 编码最大字符长度为 3 字节，如果遇到 4 字节的宽字符就会插入异常了。Emoji表情或者某些特殊字符 = 4个字节，所以当需要存储中文时，建议使用UTF-8mb4作为字符集。
- 纯拉丁字符能表示的内容，没必要选择 latin1 之外的其他字符编码，因为这会节省大量的存储空间。
- 如果我们可以确定不需要存放多种语言，就没必要非得使用UTF8或者其他UNICODE字符类型，这回造成大量的存储空间浪费。
- MySQL的数据类型可以精确到字段，所以当我们需要大型数据库中存放多字节数据的时候，可以通过对不同表不同字段使用不同的数据类型来较大程度减小数据存储量，进而降低 IO 操作次数并提高缓存命中率。
存储引擎
存储引擎区别本质上是数据文件的组织形式的不同。
下面是MyISAM与InnoDB的对比：InnoDB中主键索引为聚簇索引，其他未非聚簇索引。
适当的数据冗余
被频繁引用且只能通过 Join 2张(或者更多)大表的方式才能得到的独立小字段。这样的场景由于每次Join仅仅只是为了取得某个小字段的值，Join到的记录又大，会造成大量不必要的 IO，这种情况下把这些字段单独拿出来建一张表最好（即通过空间换取时间的方式来优化）。不过，冗余的同时需要确保数据的一致性不会遭到破坏，确保更新的同时冗余字段也被更新。
适当拆分
适当拆分指的不是分库分表中的垂直切分与水平切分，垂直切分指的是按照业务来进行切分，把不同业务的表放到不同服务器上，减少服务器压力；水平切分指把数据按照某一范围放到不同的物理服务器上。
当我们的表中存在类似于 TEXT 或者是很大的 VARCHAR类型的大字段的时候，如果我们大部分访问这张表的时候都不需要这个字段，我们就该义无反顾的将其拆分到另外的独立表中，以减少常用数据所占用的存储空间。这样做的一个明显好处就是每个数据块中可以存储的数据条数可以大大增加，既减少物理 IO 次数，也能大大提高内存中的缓存命中率。