《高性能Mysql》重点总结（二）——数据类型的选择

1. 选择优化的数据类型

选择正确的数据类型至关重要，以下是《高性能MYSQL》推荐的几个原则。

更小的通常更好：尽量使用满足需求的最小数据类型。

简单就好：简单的数据类型通常需要更少的CPU周期。例如整型比字符串代价更低，使用时间类型存储日期而不是字符串，使用整型存储IP（MYSQL提供INET_ATON()和INET_NTOA()函数在这两种表示方法之间转换）。

尽量避免NULL：如果应用程序不需要用到NULL，最好指定列为Not NULL。查询中包含可为NULL的列，对MYSQL来说更难优化。当可为NULL的列被索引时，每个索引记录需要一个额外的字节。

尽量使用相同的数据类型存储相似或相关的值：尤其是关联条件中的列。

避免使用MYSQL已经遗弃的特性：例如指定浮点数的精度，或者整数显示宽度。

小心使用ENUM和SET：最好避免使用BIT。

尽量使用整型定义标识列：使用字符串并不推荐，如果存储的是UUID值，则应该移除 -符号，更好的做法是用UNHEX()函数转成16字节的数字，并且存储在一个BINARY(16)列中。检索时可以通过HEX()函数化为十六进制格式。（参考自《高性能MYSQL》，这里不懂，这种互转不会更加损耗性能吗？）

1.1 整数类型

MYSQL中存储整数有这集中类型：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分别对应的存储空间是8、16、24、32、64位。同时还有可选的UNSIGNED属性，可以使正数的上限提高一倍。

有符号和无符号类型使用相同的存储空间，并具有相同的性能。

MYSQL可以为整数类型指定宽度，例如int(11)，但对于大多数应用这是没有意义的：它不限制值得合法范围，只是规定了MYSQL的一些交互工具（例如MYSQL命令行客户端）用来显示字符的个数。对于存储来说，int(1)和int(20)是相同的。不会有性能上的区别！

补充：无符号值可以避免误存附属，且扩大表示范围（摘自《阿里巴巴Java开发手册》）

对象	年龄区间	类型	字节	表示范围
人	150岁之内	tinyint unsigned	1	0到255
龟	数百岁	smallint unsigned	2	0到65535
		mediumint unsigned	3	0到16777215
恐龙化石	数千万年	int unsigned	4	0到42.9亿
太阳	约50亿年	bigint unsigned	8	0到约10的19次方

1.2 实数类型

DECIMAL只是一种存储格式，在计算中DECIMAL会转换为DOUBLE类型。

尽量只在对小数进行精确计算时才使用DECIMAL——例如存储财务数据。但在数据量比较大的时候，可以考虑使用BIGINT代替DECIMAL，从而避免浮点存储计算不精确和DECIMAL精确计算代价高的问题。

1.3 字符串类型

VARCHAR

可变长字符串，需要使用1或2个额外字节记录字符串的长度；如果列的最大长度小于等于255字节，怎只是用1个字节表示，否则使用2个字节。

适合使用VARCHAR的场景：字符串列的最大长度比平均长度大很多；列的更新少；使用UTF8这样每个字符使用不同字节数存储的字符集。

CHAR

适合存储很短的字符串、且对于经常变更的数据，char比varchar好（涉及碎片问题）。例如非常适合存储密码的MD5值。

BINARY和VARBINARY

存储的是二进制字符串，存储的是字节码而不是字符，填充采用\0而不是空格，检索时也不会去掉填充值。

BLOB和TEXT

前者采用二进制，后者采用字符的方式存储数据。

1.4 日期和时间类型

DATETIME

存储从1001到9999年的日期数据，精确到秒，使用8个字节的存储空间。

TIMESTAMP

存储从1970到2038年的数据，精确到秒，使用4个字节的存储空间。

除了特殊行为之外，尽量使用TIMESTAMP，因为它比DATETIME空间效率更高。如果需要存储比秒更小粒度的日期和时间值，可以使用BIGINT类型存储，或者DOUBLE存储秒之后的小数部分。

2. MYSQL schema设计中的陷阱

太多的列

太多的关联：单个查询最好在12个表以内做关联。

全能的枚举

变相的枚举

3. 范式和反范式

3.1 范式

第一范式

数据库表中的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。

例如，由“职工号”“姓名”“电话号码”组成的表(一个人可能有一部办公电话和一部移动电话)，这时将其规范化为1NF可以将电话号码分为“办公电话”和“移动电话”两个属性，即职工(职工号，姓名，办公电话，移动电话)。

第二范式

满足第一范式的前提下，要求数据库表中每个记录都有唯一标识，且非主属性不能只依赖主属性的一部分。

第三范式

满足第二范式的前提下，每个非主属性必须直接依赖于主键，不能存在传递依赖。既不能有冗余字段。

BC范式

BCNF需要符合3NF，并且，主属性不依赖于主属性。

假设有表结构： 仓库id、管理员id、物品id、物品数量。其中管理员和仓库的关系是一对一，仓库和物品的关系是一对多；

这里的主要问题是，仓库id和管理员id这两个关键字段之间的关系被耦合到每一个实例中了，这导致：

a) 表中没有数据的时候，无法描述仓库和管理员之间的关系

b) 一个仓库的管理员替换后，表中所有含有该仓库的实例中的管理员id都要被修改

解决办法是将二者的关系提出来单独建表。

则原表结构改为：仓库id 物品id 物品数量

增加表的结构： 仓库id 管理员id

3.2 范式的优点和缺点

优点：更新操作通常比反范式化快；表更小，没有冗余数据。

缺点：通常需要关联，代价昂贵，也可能是一些索引策略无效。

3.3 反范式的优点和缺点

优点：减少关联，更有效的使用索引。

4. 最后

点击获取可以查看我更多更详尽的阅读笔记哈，如有帮助的话，请给我一个star，谢谢支持~