高性能MySql学习笔记-第四章：Schema 与数据类型优化

1. MySQL 的数据类型

• 选择数据类型时的基本原则

  1. 更小的通常更好。 在确保没有低估需要存储的值的范围时，应当尽量使用可以正确存储数据的最小的数据类型。因为他们占用更少的磁盘、内存和 CPU 缓存，处理时需要的 CPU 周期也更少。
  2. 简单即可。 简单的数据类型操作通常需要更少的 CPU 周期。例如可以使用整数类型的则不要选择字符串类型，使用 MySQL 内建的日期时间类型，而非字符串表示日期和时间等。
  3. 尽量避免 NULL。 可为 NULL 的列在索引、索引统计和值比较都更为复杂，会使用更多的存储空间。如果索引包含 NULL 列，每个索引记录需要一个额外的字节。

• 整数类型

  类型	大小	范围（有符号）	范围（无符号）
  TINYINT	8 位 1 字节	(-128，127)	(0，255)
  SMALLINT	16 位 2 字节	(-32 768，32 767)	(0，65 535)
  MEDIUMINT	24 位 3 字节	(-8 388 608，8 388 607)	(0，16 777 215)
  INT	32 位 4 字节	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)
  BIGINT	64 位 8 字节	(-9 223 372 036 854 775 808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)

  • 整数类型可以使用UNSIGNED关键字，表示不需要负值，只保存正值。相对应的，正数的存储上限可以提高一倍，比如UNSIGNED TINYINT存储范围是0 ~ 255，TINYINT存储范围是 -128 ~ 127。有符号和无符号的正数类型使用相同的存储空间，具有相同的的性能，根据实际需要选择即可。
  • MySQL 可以为整数类型指定宽度，如INT(10)，但是这不影响存储范围和计算效果，只是规定了部分交互工具用来显示字符的个数。

• 实数类型

  • 实数类型是支持小数的数字类型。MySQL 目前支持FLOAT、DOUBLE和DECIMAL三种浮点类型，其中FLOAT是单精度浮点数，存储需要32位4字节， DOUBLE是双精度浮点数，存储需要64位8字节。DECIMAL类型用于存储更为精确的小数，甚至可以表示比 BIGINT 还大的整数。
  • DECIMAL类型一般使用DECIMAL(P,D)的格式表示，其中P是表示有效数字数的精度，范围为1〜65。D是表示小数点后的位数，范围是0~30。MySQL要求D小于或等于P。例如DECIMAL(6,2)代表最多可以存储6位数字，小数位数为2位，因此存储范围是从-9999.99 ~ 9999.99。
  • MySQL使用二进制格式存储DECIMAL，整数和小数部分需要分别分配存储空间，每9位数字将会占用4个字节。不足9位的，剩余1-2位需要占用一个字节，剩余3-4位需要占用两个字节，剩余5-6位需要占用三个字节，剩余7-9位需要占用四个字节。比如DECIMAL(22,5)，代表一共有22位，其中小数部分5位，需要占用三个字节；整数部分为22-5共17位，需要占用8个字节（按9位+8位存储）。部分文章和博客中描述小数点也需要占据一位，但是也有一些文章计算字节数时没有包含小数点，这部分暂时没有定论。
  • FLOAT 和 DOUBLE 类型支持使用标准的浮点运算进行近似计算。CPU 直接支持原生的浮点运算。但DECIMAL 无法由CPU 计算，只能由MySQL服务器进行计算，故而 FLOAT 和 DOUBLE 的运算比 DECIMAL 要快。而在存储相同范围的值时 FLOAT 和 DOUBLE 的占用空间比 DECIMAL 也要少。
  • 由于 DECIMAL 类型需要额外的空间存储和开销，一般只在明确需要对小数进行精确计算时，才使用 DECIMAL 类型，比如金融财会场景。另外，也可以考虑将小数数值，乘相应倍数后，使用 BIGINT 类型存储。

• 字符串类型

  类型	大小	用途
  CHAR	0-255 bytes (28-1)	定长字符串
  VARCHAR	0-65535 bytes (216-1)	变长字符串
  TINYBLOB	0-255 bytes (28-1)	不超过 255 个字符的二进制字符串
  TINYTEXT	0-255 bytes (28-1)	短文本字符串
  BLOB	0-65 535 bytes (216-1)	二进制形式的长文本数据
  TEXT	0-65 535 bytes (216-1)	长文本数据
  MEDIUMBLOB	0-16 777 215 bytes (224-1)	二进制形式的中等长度文本数据
  MEDIUMTEXT	0-16 777 215 bytes (224-1)	中等长度文本数据
  LONGBLOB	0-4 294 967 295 bytes (232-1)	二进制形式的极大文本数据
  LONGTEXT	0-4 294 967 295 bytes(232-1)	极大文本数据

  • CHAR 类型存储定长的字符串，MySQL 会根据定义的字符串长度分配足够的空间。当存储 CHAR 值时，MySQL 会自动使用空格右填充到指定的长度。当查询 CHAR 值时，会自动当末尾的空格删除。CHAR 类型适合存储很短的字符串或者长度基本一致的字符串。
  • VARCHAR 类型存储可变长的字符串，它比定长类型更节省空间，因为它只占用必要的空间。VARCHAR 需要使用额外的1到2个字节记录字符串的长度。如果长度在255字节以内，则只使用1个字节表示，超出255字节则使用2个字节。2个字节最大可以表示65535的长度，该长度是 VARCHAR 类型的最大存储长度。即便 VARCHAR 类型用来存储变长的字符串，但在设计时，明智的做法是遵循更小的通常更好这一原则，尽可能的只分配需要的空间。
  • 需要注意的是，在创建字段时，给定的字符串长度定义是代表的字符数，而不是字节数。如 VARCHAR(10)代表最多可以存储10个字符而非10个字节。在不同的字符集编码下，一个字符占用的字节数并不相同且可能占用多个字节。比如一个普通汉字在 UTF-8编码下需要占用3个字节，而在 UTF-16编码下只需要占用两个字节。
  • 与 CHAR 和 VARCHAR 类型类似的还有 BINARY 和 VARBINARY 类型。他们存储的是二进制字符串。MySQL 对于 BINARY 类型填充的是（零字节）而不是空格。与 CHAR 类型不同的时，当查询 BINARY 值时，MySQL 不会自动将末尾的填充值去掉。BINARY 和 VARBINARY 使用字节码比较大小，相比于使用字符比较会更快。
  • BLOB 和 TEXT 都是为了存储大数据而设计的字符串类型。分布采用二进制和字符的方式存储。TEXT 类型有字符集和排序规则。一般而言，TEXT 类型存储的内容可以无损转换到 BLOB 类型，反之则不可以。上表里的 BLOB 也可以写作 SMALLBLOB，TEXT 也可以写作 SMALLTEXT。
  • 也可以使用 ENUM 类型替代常用的字符串值，这相当于给定了一些字符串预定义值的集合。MySQL 在存储时，会将字符串转换位对应的整数存储，从而使得存储非常紧凑，可大大减小存储和 IO的占用。但是缺点是，当想新增或修改预定义的值时，只能通过 ALTER TABLE 的方式来操作，这是一种很高成本的操作。

• 日期和时间类型

  类型	大小 ( 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	混合日期和时间值，时间戳

  • TIMESTAMP 类型保存了自 UTC 时间1970年1月1日0时0分0秒一来的秒数，即 UNIX 时间戳。TIMESTAMP 只使用了4个字节的存储空间，所以它的范围比 DATETIME 小很多，只能表示1970年到2038年的时间。MySQL 提供了 FROM_UNIXTIME() 的函数把 UNIX 时间戳转换为 TIMESTAMP 类型，并提供了 UNIX_TIMESTAMP() 函数将TIMESTAMP 类型转换为时间戳。
  • TIMESTAMP 类型一个很重要的特点是，它存储的是时间戳的值而非具体的时间，故而读取 TIMESTAMP 类型的时候，MySQL 会根据 MySQL 服务器所在的时区进行相应的转换。比如在一台位于零时区的 MySQL 服务器中，存储了 TIMESTAMP 的值为1970-01-01 00:00:00 +0 时，MySQL 真正存储的是0这个时间戳的值。而当我在一台位于北京东八区的 MySQL 服务器中读取刚刚存储的这个值时，得到的将会是1970-01-01 08:00:00 +8。注意这个转换是 MySQL 服务器直接进行的，而非 ORM 中进行的。
  • DATETIME 类型使用8个字节存储，所以可以保存更大的值，从1001年到9999年，精度为秒。和 TIMESTAMP 类型不同的是，DATETIME 类型存储的就是时间的内容，无关乎时区。以上述例子为例，在一台位于零时区的 MySQL 服务器中，存储了 DATETIME 的值为1970-01-01 00:00:00 时间之后，在一台位于北京东八区的 MySQL 服务器中读取这个值，得到的仍是1970-01-01 00:00:00。这种情况，如果不进行特殊处理，会对一些业务的场景造成时间上的误差。
  • TIMESTAMP 类型和 DATETIME 类型还有一个不同点是，TIMESTAMP 类型默认是 NOT NULL 的，所以如果没有填充这个值，MySQL 会自动将其填充为档期时间，而 DATETIME 类型则会填充为 NULL。
  • 业务开发时，如何选择 TIMESTAMP 类型和 DATETIME 类型？ 从长远考虑，由于 TIMESTAMP 类型表示的范围更小，所以建议选择 DATETIME 类型。但是正如上文所讲，DATETIME 类型在遇到跨时区的 MySQL 服务器时，会有时间上的误差。这里建议在业务层或 ORM 层中，对于DATETIME类型的读写做自动的时区转换。当写入 DATETIME 类型时，自动将时间从当前业务服务器所在时区的时间转到UTC 时间，而当读取 DATETIME 类型时，再自动将该时间由 UTC 时间转换为业务服务器所在时区的时间。

• 位数据类型

  • BIT 类型存储一个或多个 true/false 值，BIT(1) 定义包含单个位的字段，BIT(2) 定义包含两个位的字段，以此类推，最大可表示64个位。
  • SET 类型打包更多的 true/false 值，可以通过 FIND_IN_SET() 函数和 FIELD() 函数确定某个位的值。它的缺点是改变 SET 这个集合的定义的代价比较高，需要使用 ALTER TABLE 命令，也无法使用索引。
  • 一般情况下，应该谨慎使用位数据类型。

2. MySQL schema 设计中的陷阱

• 如下的一些情况，在 MySQL 的 schema 设计中应到尽量避免
  1. 设计太多的列。过多的列对于 MySQL 的性能具有很大的影响。如果确有需要很多列的场景，应当进行拆分或替换位为其他数据库产品。
  2. 使用了太多的关联表。
  3. 枚举（ENUM）的项过多。
  4. 坚持 NOT NULL。在确保使用 NULL 不会影响性能的情况下，坚持 NOT NULL 可能并不是一个好的选择。比如对于无法表示的整数，使用 NULL 可能比其他标识更方便。比如 CREAT TABLE ...(dt DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00')。

3. 范式和反范式

• 范式的定义（此部分非书中内容，为外部资料补充）

  1. 第一范式（1NF）：保证每列的原子性。 第一范式是对关系模式的基本要求，不满足第一范式的数据库就不是关系数据库。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库满足了第一范式。字段值是否位原子值应当根据具体的需求而定。

     • 网上常见的一个例子是地址。一般地址表示是 xx 省 xx 市，如果只需要查询整个地址，则可以将地址存储为一列。如下面的用户信息表：

     ID	姓名	年龄	地址
     3	张三	31	山东省青岛市xxx路 xxx 号
     4	李四	32	山东省济南市xxx路 xxx 号
     5	王五	33	江苏省南京市xxx路 xxx 号

     • 但是如果需求中经常需要单独查询城市，那地址这一列就将不满足第一范式所要求的原子性，需要将xx 市单独放置一列才满足。

     ID	姓名	年龄	省份	城市	地址
     3	张三	31	山东省	青岛市	xxx路 xxx 号
     4	李四	32	山东省	济南市	xxx路 xxx 号
     5	王五	33	江苏省	南京市	xxx路 xxx 号

  2. 第二范式（2NF）：非主属性必须完全依赖于主键。通俗讲就是保证一张表只描述一件事情（实体）。

     • 一个不符合第二范式的例子是职员信息-部门信息表。在这张表中，年龄依赖的是职工的ID信息，而部门领导依赖的是部门信息。这使得非主属性并没有完全依赖该表的主键（可以认为是职工 ID）。由此而带来的一些问题是：数据冗余，相同部门的职工信息中冗余存储了部门办公地；更新复杂，如果要变更销售部的部门办公地，需要变更该部门下所有职工的信息记录；记录缺失，如果一个部门尚未招聘职工，那无从表示部门办公地这一类属于部门级别的信息。

     职工ID	姓名	年龄	部门	部门办公地
     3	张三	31	销售部	11号楼
     4	李四	32	销售部	11号楼
     5	王五	33	研发部	12号楼

     • 按照第二范式，这张表应当被拆分位职工信息表部门信息表这两张表。

     职工信息表：

     职工ID	姓名	年龄	部门ID
     3	张三	31	1
     4	李四	32	1
     5	王五	33	2

     部门信息表：

     部门ID	部门名称	部门办公地
     3	销售部	11号楼
     4	销售部	11号楼
     5	研发部	12号楼

  3. 第三范式（3NF）：要求一个数据库表中不能包含已在其它表中已包含的非主关键字信息。 仍以上述例子举例，当我们拆分了职工信息表和部门信息表后，职工信息表既已存储了部门 ID，则不应当再存储部门名称的信息。

  4. 反范式：反范式指的是通过增加冗余或重复的数据来提高数据库的读性能。

• 范式和反范式的对比

  • 范式的写操作通常比反范式要快，且更新数据较少。
  • 范式的表更小，可以更好地放入内存以加快一些操作
  • 范式的冗余数据很少，查询时更少需要使用 DISTINCT 或 GROUP BY 语句。
  • 反范式的数据都在一张表中，可以加快查询速度，避免关联查询。

• 混用范式和反范式

  • 在实际应用中，通常需要根据需求的真实情况混用范式和反范式。

4. 缓存表和汇总表

缓存表和汇总表可以用来存储那些从其他表获取或计算出来的但是查询时间比较长的数据，用来加快查询。

• 物化视图
  • 物化视图是预先计算并存储在磁盘上的表，通过各种各样的策略更新和刷新。
• 计数器表
  • 可以设计一个只有一行数据的记录器表，每操作一次，该表中的记录数就加一：UPDATE hit_counter SET cnt = cnt + 1。但是相当于对想要更新该行的事务都加了一个锁。
  • 对于加锁的问题，可以将计数器保存在多行，然后每次只随机更新其中一行，以减小锁冲突的概率。最终查询数量时，使用 SUM 聚合查询得到总数。

• 大多数的业务场景都是读多写少的场景，可以采取一些牺牲少量写性能来做一些可以大大加快读性能的操作，比如 MySQL 中的索引。