MySQL(2)-数据类型和Schema

一、数据类型

只介绍基本的数据类型。

MySQL中选择合适的数据类型还是很有必要的，下面是一些通用原则：

小的就是好的

一般情况下，应该尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快，因为更少的磁盘、内存和CPU缓存、并且处理时需要额CPU周期更少。
但是要确保没有低估值范围，修改类型是非常痛苦的事，尤其是表数据量上来了之后。

简单就好

整形比字符串操作代价更低，字符串和校对规则(排序规则)使得字符串比整形更复杂。这里有2个例子：一个是应该使用MySQL内建的类型而不是字符串来存储日期和时间，另外一个是应该使用整形存储IP地址。

尽量没有NULL

如果不需要NULL值应该指定NOT NULL
如果查询中可以包含为NULL的列，对MySQL来说更难优化，因为可为NULL的列使得索引、索引统计和值比较都更为复杂。可为NULL的列会使用更多的存储空间，在MySQL中需要特殊处理。当可为NULL的列被索引时，每个索引记录都需要一个额外的字节，当可为NULL的列被索引时，每个索引记录都需要一个额外的字节，在MyISAM里甚至还能导致固定大小的索引(例如只有一个整数列的索引)变成可变大小的索引。
通常把可变为NULL的列调整为NOT NULL带来的性能比较小，没有必要首先改掉这它，但是在设计表的时候指定为NOT NULL是一个好习惯。

当然有例外：InnoDB使用单独的位(bit)存储NULL值，所以对稀疏数据有很好的空间效率。但是这一点不适用于MyISAM。

1.1 整数类型

类型：有TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT.分别使用8,16,24,32,64位存储空间，位数也决定了范围。

整数类型有可以选的UNSIGNED属性，指定是否为负值，如果非负，那整数的范围提高一倍，例如TINYINT UNSIGNED可以是0-255，而TINYINT的范围是-128-127

MySQL还可以为整数类型指定宽度，例如INT(11)，这对于大多数应用没有意义：它不会限制值的合法范围，只规定了一些客户端显示字符的个数而已。对于存储和计算而言，INT(11)和INT(20)没有区别。

 

1.2 实数类型

平台相关：

　　FLOAT和DOUBLE使用标准的浮点运算(平台相关，CPU直接支持，运算更快)。
　　DECIMAL存储精确小数，支持精确计算(服务器实现，因为一般的CPU不支持DECIMAL的直接计算)。

 

DECIMAL精度：

　　浮点和DECIMAL类型都可以指定精度，对于DECIMAL列，可以指定小数点前后所允许的最大位数，这会影响列的空间消耗。MySQL将数字打包到一个二进制字符串中(每4个字节存9个数字)。例如DECIMAL(18,9)小数点左右各9位，共9个字节。

　　MySQL5之后DECIMAL类型允许最多65个数字。
　　我们建议只指定数据类型，不指定精度。

　　因为需要额外的空间和计算开销，应该尽量只对在小数进行精确计算时才用DECAIMAL-例如存储财务数据，如果数据量真的大....真的可以考虑使用BIGINT代替(有理数*10^n方变为整数...)

浮点数：

　　DOUBLE占用8个字节，FLOAT4个，通常都比DECIMAL占用更少的字节数

 

1.3 字符串类型

VARCHAR和CHAR最主要的2种。很难精确解释这些值怎么存储在磁盘和内存中，因为这根具体的存储引擎有关。假设使用的InnoDB或者MyISAM：

VARCHAR：

• 可变字符串，没有大小写区别。比定长类型更加节省空间，因为只使用必要空间。有一种情况例外，如果使用FOW_FORMAT=FIXED创建，每一行都是定长存储，这会浪费空间。
• VARCHAR需要1或2个字节额外记录长度，如果列的最大长度<=255，使用1个，否则2个
• VARCHAR由于是可以变长的，UPDATE需要额外工作，因为可能长度变换。如果一行占用的空间增长，并且在页内没有更多的空间可以存储，这种情况下，不同的引擎是不一样的。例如，MyISAM会拆成不同的片段，InnoDB则需要分裂页来使行可以放进页内。

什么情形适合VARCHAR？

• 字符串最大长度比平均长度大很多；
• 列的更新少，所以碎片不是问题；
• 使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数来进行存储

 

 

CHAR：

• 定长的
• MySQL总是根据定义的字符串长度分配足够的空间。当存储CHAR值时，MySQL会删除所有的末尾空格。CHAR值会根据采用空格进行填充。
• CHAR适合存储很短的字符串，或者所有值都接近同一个长度。例如CHAR非常适合存储密码的MD5值，因为是一个定长的值
• 对于经常变更的数据，CHAR比VARCHAR适合，因为不容易产生碎片
• 非常短的列，CHAR更有效率。

 

二进制字符

　　与CHAR和VARCHAR类似的类型还有BINARY和VARBINARY，它们存储的是二进制字符串。二进制字符串存储的是字节码而不是字符。填充也不一样，MYSQL填充BINANRY的是\0(零字节)而不是空格。

　　当需要二进制数据，并且希望MySQL使用字节码而不是字符进行比较时，这些类型非常有用。二进制比较的优势并不仅仅体现在大小写敏感上。二进制的比较更快。

 

BLOB和TEXT

尽量避免使用它们，排序使用的是max_sort_length而不是整个字符串，不能对其进行索引。

有时使用ENUM代替字符串：

ENUM存储的是整数，快，而且大大减少大小

排序也是按照内部存储的整数而不是定义的字符串进行排序，因此在定义的时候请自己按照字母排序

不好的地方：

　　字符串列表是固定的，你想要改变必须ALTER TABLE

　　最好不要对其使用JOIN这样的关联，因为它存储的是整数，需要转换为字符串，有时开销更大

1.4 日期和时间类型

大部分时间类型都没有替代品，需要什么用什么即可。

DATETIME:

　　1001年到9999年，精度为妙，把日期和时间封装到YYYYMMDDHHMMSS的整数中。使用8个字节

TIMESTAMP：

　　保存了1970年1月1日午夜以来的秒数。范围从1970年到2038年。

　　和时区有关。

　　有特殊属性，默认情况下，如果插入时没有指定第一个TIMESTAMP的值，MYSQL设置其为当前时间

　　默认为NOT NULL，这和其他的数据类型不一样

如果要使用更小的粒度，比如你可以使用BIGINT，或者使用MariaDB替代MySQL

1.5 BIT

在MySQL5.0之后，BIT就是完全不同的数据类型了，之前就是TINYINT。

可以使用BIT在一列中存储一个或者多个true/false值。

BIT(1)定义一个包含1个位的字段，一次BIT(2)2个。

MySQL把BIT当做字符串类型，而不是数字类型，这相当令人费解，对于大部分应用，最好避免使用

如果想要在一个bit的存储空间中存储一个true/false，可以创建一个可以为NULL的CHAR(0)的列，该列可以保存空值(NULL)或者长度为0的字符串，空字符串。

...多个话还可以使用SET类型，这里不关注

 

1.6 选择标识符(identifier)

id的选择很重要，一般都是一个AUTO_INCREMENT的整数。

如果可能，应该避免使用字符串类型作为id，因为很消耗空间，并且慢。而且作为外键的类型和要主键类型一致，InnoDB甚至强制要求这一点。

 

当心ORM框架-这是一种常见的性能噩梦。

 

 

二、设计陷阱

虽然有一些普遍的好或者坏的设计原则，但是也有很多问题时有MySQL的实现机制导致的。

太多的列

太多的列可能会导致行转换代价太高，MySQL的存储引擎API工作时需要在服务层和存储引擎之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列。从行缓冲中将编码的列转换为行数据的操作代价是非常高的。MyISAM的变长行结构和InnoDB的行结构则总是需要转换。当我们研究一个CPU占用非常高的案例时，发现客户使用了非常宽的表(数千个字段)，这时转换的代价会非常高。

太多的关联

所谓的"实体-属性-值"(EAV)设计模式是一个常见的糟糕的设计模式，尤其是在MySQL下不能靠谱地工作。MySQL限制了每个关联操作最多只能有61张表，但是EAV数据库则需要许多关联。事实上，在许多关联少于61张表的情况下，解析和优化查询的代码也会成为MySQL的问题。一个粗糙的经验法则是：单个查询最好在12个表以内做关联。

全能的枚举

　　枚举的问题在之前说过了，你要修改必要ALTER TABLE...适合枚举的才枚举...(你可以使用一些骇客的方式来避免ALTER TABLE时阻塞，但不建议生产环境中这样用)

非此发明(Not Invent Herr)的NULL

　　NULL确实不建议使用，但是也不要害怕使用，比如在一些场景中，使用NULL可能会比某个神奇常数更好，比如你用-1代表一个未知的整数，可能代码会复杂很多。值得一提的是，MySQL会在索引中存储NULL值，而Oracle则不会。

 

三、范式和反范式

以经典的"员工-部门-部门领导"开始：

这个schema的问题是修改数据时可能发生数据不一致。

假如Say Brown接任Accounting部门的领导：

　　需要修改所有的Accounting有关的行。

这不明智并且容易错误，范式化就是分为2张表。。。

员工表：

 部门表：

 

 

 这就是一个典型的范式问题，第二范式。

 这里只对范式进行简单描述，相关的内容太多了：

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴德斯科范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。一般说来，数据库只需满足第三范式(3NF）就行了。

(1) 第一范式（1NF）: 比如一个字段不能有一个字段： 年龄和性别

所谓第一范式（1NF）是指在关系模型中，对域添加的一个规范要求，所有的域都应该是原子性的，即数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项。即实体中的某个属性有多个值时，必须拆分为不同的属性。在符合第一范式（1NF）表中的每个域值只能是实体的一个属性或一个属性的一部分。简而言之，第一范式就是无重复的域。

说明：在任何一个关系数据库中，第一范式（1NF）是对关系模式的设计基本要求，一般设计中都必须满足第一范式（1NF）。不过有些关系模型中突破了1NF的限制，这种称为非1NF的关系模型。换句话说，是否必须满足1NF的最低要求，主要依赖于所使用的关系模型。

(2) 第二范式(2NF)：比如在员工表中部门信息应该单独分一张表，重复的部门信息无须出现多次，因为部门信息对员工表主键没有依赖

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或记录必须可以被唯一地区分。选取一个能区分每个实体的属性或属性组，作为实体的唯一标识。

第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。简而言之，第二范式就是在第一范式的基础上属性完全依赖于主键。

(3) 第三范式（3NF）

第三范式（3NF）是第二范式（2NF）的一个子集，即满足第三范式（3NF）必须满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个关系中不能包含已在其它关系已包含的非主关键字信息。简而言之，第三范式就是属性不依赖于其它非主属性，也就是在满足2NF的基础上，任何非主属性不得传递依赖于主属性。

 

 3.1 范式的优点和缺点

在为性能问题而苦恼时，经常被建议对schema进行范式化设计，尤其是写密集的场景。这通常是种好建议：

• 范式化的更新操作通常比反范式化要快。
• 当数据较好的范式化时，就只有很少或者没有重复数据，所以只需要修改更少的数据。
• 范式化的表通常会比较小，可以更好的放置在内存中，所以执行操作会更快。
• 很少有多余的数据意味着检查列数据更少需要DISTINCT或者GROUP BY语句。就看前面的例子，如果要获取唯一的部门列表， 但是如果部门是一张单独的表，则需要简单查询这张表就可以了。

 

范式化的缺点就是需要关联。关联最讨厌了，稍微复杂一些的查询在符合范式的schema上都需要至少一次关联，也许更多。也不但代价昂贵，也可能使得一些索引策略无效。例如，范式化可能将列存放在不同的表中，而这些列如果在一个表中本来可以属于同一个索引。

举个例子：

假设有一个网站，允许用户发送消息，并且一些用户是vip，现在想要查看vip用户的最近10条信息，可能查询像这样：

分析这个查询:

1. MySQL扫描message表中的published字段的索引

2. 对每一行数据，需要到user表中检查这个用户是不是付费用户

如果只有一小部分的用户是vip，则效率很低。当然你可以从用户出发，这可能更槽糕。

主要问题是关联，需要在一个索引中又排序，又过滤，如果采用反范式化的组织数据，将2张表的字段合并一下，并且增加一个索引(account_type, published)，就可以不用关联了。

 

 3.2 混用范式

完全的范式化是实验室里才有的东西。

最常见的反范式化设计是使用复制或者缓存，在不同的表中存储相同的特定列，甚至可以用触发器更新缓存值等等。

比如在上面的网站实例中可以在message表中多存一个account_type值，但是这样更新account_type的时候要更新2张表。至于这会不会是一个问题，需要考虑更新的频率，执行SELECT的频率了，一般而言，SELECT才是大所数数据面临的问题。

另一种情况是使用排序，假如你需要依赖另一张表中的字段进行排序，可以同上

 

 

四、缓存表和汇总表

缓存表和汇总表并没有什么定义，一般缓存表就是缓存了，汇总表就是汇总了....

仍然以网站为例，假设要计算之前24小时发送的消息数，假如网站并发量高的情况下去维护一个实时的计数器并不建议。作为替代方案，来张汇总表msg_per_hr，缺点是不精确，如果必须获取24小时内准确的，也可以使用汇总表+最后一个小时内msg表的数据，也比直接msg表好很多。

 

缓存表则对优化搜索和检索查询语句很有效。有时候你需要更多不同的索引组合来加速各种类型的查询。这些矛盾的需求可能有时你只要创建一张包含主表中部分数据的缓存表。 甚至可以在缓存表中使用不同的引擎实现特殊的目的等等。

 

使用汇总表和缓存表还有一个问题就是定期重建，实时维护一般都比较消耗资源，大多数情况没有必要，定期重建并不仅仅是节省资源，可以保持表不会有碎片，以及完全有顺序组织的索引(更加的高效)。当然缓存不一定非得用MySQL缓存表什么的，缓存方案有的是redis，应用程序本地缓存等等。

在重建时，通常还要保证可用性，这时候必须用到影子表，这是在真实表后面创建的表，当完成了建表后，可以通过原子的重命名操作切换影子表和原表。