Post

Schema 与数据类型优化

Posted Updated
By 😇 20 min read

ch4 Schema 与数据类型优化

选择优化的数据类型

整型类型，实数类型，字符串类型，日期和时间类型，位数据类型，选择标识符（identifier），特殊类型数据

一般选择正确的数据类型简单原则:

A 更小的通常更好

尽量使用可以正确存储数据的最小数据类型，更小的数据类型意味着占用更小的磁盘、内存和 CPU 缓存，并且处理时需要的 CPU 周期也更小。

B 简单就好

简单数据类型需要更小的 CPU 周期。例如，整型比字符操作代价更低。

C 尽量避免 NULL

最好指定列为 NOT NULL，除非真的需要存储 NULL 值。

在为列选择数据类型时，第一步需要确定合适的大类型：数字、字符串、时间等；下一步是选择具体类型。

整数类型

有两种类型的数字：整数（whole number）和实数（real number）

整数：TINYINT 8位，SMALLINT 16位，MEDIUMINT 24位，INT 32位，BIGINT 64位

实数类型

实数是带有小数部分的数字。也可以使用 DECIMAL 存储比 BIGINT 还大的整数。

DECIMAL 类型用于存储精确的小数。浮点和 DECIMAL 类型都可以指定精度。对于 DECIMAL 列，可以指定小数点前后所允许的最大位数。如 DECIMAL(18, 9) 。

浮点类型在存储同样范围的值时，通常比 DECIMAL 使用更少的空间。FLOAT 使用 4 个字节存储。DOUBLE 占用 8 个字节。

尽量只在对小数进行精确计算时才使用 DECIMAL —— 例如存储财务数据。

字符串类型

  • VARCHAR & CHAR

VARCHAR 用于存储可变长字符串，是最常见的字符串数据类型。比定长类型更节省空间，因为它仅使用必要的空间，字符串越短使用越少的空间

CHAR 类型是定长的；MySQL 总是根据定义的字符串长度分配足够的空间。存储 CHAR 值时，MySQL 会删除所有的末尾空格。CHAR 适合存储很短的字符串，或者所有值都接近同一个长度。例如，CHAR 非常适合存储 MD5 值（定长的）。

与 VARCHAR 和 CHAR 类似的还有 BINARY 和 VARBINARY，它们存储的是二进制字符串。二进制字符串存储的是字节码而不是字符。

  • BLOB & TEXT

BLOB 和 TEXT 都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。

字符串类型 TINYTEXT SMALLTEXT TEXT MEDIUMTEXT LONGTEXT

二进制类型 TINYBLOB SMALLBLOB BLOB MEDIUMBLOB LONGBLOB

与其他类型不同，MySQL会把 BLOB 和 TEXT 值当作一个独立的对象处理。存储引擎在存储时通常会做特殊处理。当 BLOB 和 TEXT 值太大时，InnoDB 会使用专门的外部存储区域来进行存储，每个值在行内需要1-4个字节存储一个指针，然后在外部存储区域存储实际的值。

BLOB 和 TEXT 家族之间仅有的不同是 BLOB 类型存储的是二进制数据，没有排序规则或字符集，而 TEXT 类型有字符集和排序规则。

MySQL 不能将 BLOB 和 TEXT 列全部长度的字符串进行索引，也不能使用这些索引消除排序。

使用枚举（ENUM）代替字符串类型

枚举列可以把一些不重复的字符串存储成一个预定义的集合。

日期和时间类型

  • DATETIME

1001 年到 9999 年，精度为秒。它把日期和时间封装到格式为 YYYYMMDDHHMMSS 的整数中，与时区无关。使用 8 个字节的存储空间。

  • TIMESTAMP

保存了从 1970 年 1 月 1 日午夜（格林尼治标准时间）以来的秒数，它和 UNIX 时间戳相同。TIMESTAMP 只使用 4 个字节的存储空间。 UNIX_TIMESTAMP() 函数把日期转换为 Unix 时间戳

位数据类型

BIT

SET

在整数列上进行按位操作

选择标识符（identifier）

一般用标识列与其他值进行比较，或者通过标识列寻找其他列，标识列也可能在另外的表中作为外键使用。

  1. 整数类型

    标识列最好的选择是整数，因为它们很快并且可以使用 AUTO_INCREMENT

  2. ENUM 和 SET 类型

    对于某些只包含固定状态或者类型的静态“定义表”来说可能没有问题。ENUM 和 SET 列适合存储固定信息，例如有序的状态，产品类型，人的性别

  3. 字符串类型

    如果可能，尽量避免使用字符串类型作为标识列。

  • 特殊类型数据

某些类型的数据并不直接与内置类型一致。

schema 设计中的陷阱

有些普遍的好或坏的设计原则，本小节讨论设计 MySQL 的 schema 的问题。

  • 太多的列

    MySQL 的存储引擎 API 在工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列。从行缓冲中将解码过的列转换成行数据结构的待解时非常高的。转换的代价依赖于列的数量。

  • 太多的关联

    所谓的“实体-属性-值”（EAV）设计模式是一个常见的糟糕的设计模式。

    一个粗略的经验法则：如果希望查询执行得快速且并发性好，单个查询最好在 12 个表以内做关联。

  • 全能的枚举

    注意防止过度使用枚举（ENUM）

    例如

    1
2
3

    CREATE TABLE ... (
	country enum('', '0', '1', '2', ... , '31')
)

  • 变相的枚举

    枚举（ENUM）列允许在列中存储一组定义值中的单个值，集合（SET）列则允许在列中存储一组定义值中的一个或多个值。有时候这可能比较容易导致混乱。

  • 非此发明（Not Invent Here）的 NULL

    避免使用 NULL；即使需要存储一个事实上的“空值”到表中时，也不一定非得使用 NULL。

    也许可以使用 0、某个特殊值，或者空字符串作为代替。

范式和反范式

第一范式（1NF）定义： 属于第一范式关系的所有属性都不可再分，即数据项不可分。

第一范式详细的要求如下：

  1. 每一列属性都是不可再分的属性值，确保每一列的原子性；
  2. 两列的属性相近或相似或一样，尽量合并属性一样的列，确保不产生冗余数据；
  3. 单一属性的列为基本数据类型构成；
  4. 设计出来的表都是简单的二维表。

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求实体的属性完全依赖于主关键字。

满足第三范式（3NF）必须先满足第二范式（2NF）；第三范式（3NF）要求一个数据库表中不包含已在其它表中包含的非主关键字信息，即数据不能存在传递关系，即每个属性都跟主键有直接关系而不是间接关系。

范式化的好处：

  1. 范式化的更新操作通常比反范式化要快（字段较少）。
  2. 当数据较好的范式化时，就只有很少或者没有重复数据，所以只需要只需要修改更少的数据。
  3. 范式化的表通常更小，可以更好的放在内存里，所以执行操作会更快。
  4. 很少有多余的数据意味着检索列表数据时很少需要 DISTINCT 或者 GROUP BY 语句。

范式化设计的 schema 的缺点时通常需要关联。也可能让一些索引策略无效。

反范式化的 schema 可以很好的避免关联。如果不需要关联表，则对大部分查询最差的情况——即使表没有使用索引——是全表扫描。当数据比内存大时这可能比关联要快得多，因为这样避免了随机 I/O。

最常用的就是混用范式化和反范式化；部分范式化得 schema、缓存表，以及其他技巧。

最常见得反范式化数据的方法是复制或者缓存，在不同的表中存储相同的特定列。

反范式化就是违反范式化设计：

  1. 为了性能和读取效率而适当的违反对数据库设计范式的要求；
  2. 为了查询的性能，允许存在部分（少量）冗余数据。

换句话来说反范式化就是使用空间来换取时间。

属性范式设计反范式设计
更新操作快慢
数据重复度快慢
内存占用小大
查询表关联较多较少
查询索引命中较少命中更多命中

缓存表和汇总表

有时提升性能最好的方法是在同一张表中保存衍生的冗余数据。然而，有时也需要创建一张完全独立的汇总表或缓存表。

“缓存”来表示存储那些可以比较简单地从其他表获取数据的表。

比如从父表冗余一些数据到子表的。比如分类信息放到商品表里面进行冗余存放就是典型的例子。

“汇总”则保存的是使用GROUP BY语句聚合数据的表。

如果需要显示每个用户发了多少消息，可以每次执行一个对用户发送消息进行count的子查询来计算并显示它，也可以在user表用户中建一个消息发送数目的专门列，每当用户发新消息时更新这个值。

在使用缓存表和汇总表时，有个关键点是如何维护缓存表和汇总表中的数据，常用的有两种方式，实时维护数据和定期重建，这个取决于应用程序，不过一般来说，缓存表用实时维护数据更多点，往往在一个事务中同时更新数据本表和缓存表，汇总表则用定期重建更多，使用定时任务对汇总表进行更新。

  1. 缓存表：表示存储那些可以比较简单的从 schema 其他表获取（但每次获取的速度比较慢）数据的表（例如 ，逻辑上冗余的数据）。
  2. 汇总表：保存的是使用 GROUP BY 语句聚合数据的表（例如，数据不是逻辑上冗余的）
  3. 计数器表：在更新计数器表时可能碰到并发问题。解决这个问题可以将计数器保存在多行中，每次随机选择一行进行更新。

计数器表设计

计数器表在Web应用中很常见。比如网站点击数、用户的朋友数、文件下载次数、推文的赞/转发数等。对于高并发下的处理，首先可以创建一张独立的表存储计数器，这样可使计数器表小且快，并且可以使用一些更高级的技巧。

比如假设有一个计数器表，只有一行数据，记录网站的点击次数，网站的每次点击都会导致对计数器进行更新，问题在于，对于任何想要更新这一行的事务来说，这条记录上都有一个全局的互斥锁(mutex)。这会使得这些事务只能串行执行，会严重限制系统的并发能力。

1
2
3

CREATE TABLE hit_counter (
  cnt int unsigned not null
) ENGINE=InnoDB;

网站的每次点击都会导致对计数器进行更新：

1

UPDATE hit_counter SET cnt = cnt + 1;

怎么改进呢？可以将计数器保存在多行中，每次随机选择一行进行更新。在具体实现上，可以增加一个槽（slot)字段，然后预先在这张表增加100行或者更多数据，当对计数器更新时，选择一个随机的槽（slot)进行更新即可。

一个常见的需求是每隔一段实际开始一个新的计数器（例如，每天一个）。

1
2
3
4
5
6

CREATE TABLE daily_hit_counter(
  day date not null,
  slot tinyint unsigned not null,
  cnt int unsigned not null,
  primary key(day, slot)
) ENGINE=InnoDB;

不预先生成行，用 ON DUPLICATE KEY UPDATE 代替：

1
2
3

INSERT INTO daily_hit_counter(day, slot, cnt)
  VALUES(CURRENT_DATE, RAND() * 100, 1)
  ON DUPLICATE KEY UPDATE cnt = cnt + 1;

后面希望减少表的行数，就可以写一个周期执行的任务，合并所有的结果到 0 号槽，并且删除所有其他的槽。

1
2
3
4
5
6
7
8
9

UPDATE daily _hit_counter as c
  INNER JOIN(
    SELECcT day,SUM(cnt)AS cnt, MIN(slot)As mslot
    FROM daily hit counter
    GROUP BY day
  ) AS x USING(day)
SET c.cnt = IF(c.slot = x.mslot, x.cnt, 0),
    c.slot = IF(c.slot=x.mslot, 0, c.slot);
DELETE FROM daily_hit_counter WHERE slot<>0 AND cnt = 0;
  • 更快地读，更慢地写

为了提升读查询的速度，经常会需要建一些额外索引，增加冗余列，甚至是创建缓存表和汇总表。这些方法会增加写查询的负担，也需要额外的维护任务，但在设计高性能数据库时，这些都是常见的技巧:虽然写操作变得更慢了，但更显著地提高了读操作的性能。

然而，写操作变慢并不是读操作变得更快所付出的唯一代价，还可能同时增加了读操作和写操作的开发难度。

加快 ALTER TABLE 操作的速度

  1. 先在一台不提供服务的机器上执行 ALTER TABLE 操作，然后和提供服务的主库进行切换；
  2. “影子拷贝”。这个用要求的表结构创建一张和源表无关的新表，然后通过重命名和删表操作交换两张表。
Database
MySQL Database
This post is licensed under CC BY 4.0 by the author.