数据库与数据类型的优化

Dear 丶 2022-05-09 01:28 195阅读 0赞

### 数据库与数据类型的优化 ###

*   *  1. 选择优化的数据类型
     *   *  1.1 整数类型
         *  1.2 实数类型
         *  1.3 字符串类型
         *  1.4 日期和时间类型
         *  1.5 位数据类型
         *  1.6 选择标识符
     *  2. MySQL 数据库设计中的陷阱
     *  3. 范式和反范式
     *   *  3.1 范式
         *  3.2 反范式
         *  3.3 混用范式化和反范式化
     *  4. 缓存表和汇总表
     *  5. 加快ALTER TABLE 操作的速度
     *  6. 总结

## 1. 选择优化的数据类型 ##

1.  最小数据类型
2.  简单数据类型
3.  尽量避免NULL

选择数据类型时：

1.  需要确定合适的大类型：数字、字符串、时间。
2.  选择具体的类型。考虑长度、范围，允许的精度，需要的物理空间。

例如

选择时间时，`DATETIME` 和 `TIMESAMP` 都可以存储相同类型的数据：日期，时间，精确到秒。

然而`TIMESAMP` 只使用 `DATETIME` 一半的存储空间，并且会根据时区变化，具有特殊的自动更新能力。

另一方面，`TIMESAMP` 允许的时间范围要小得多，有时候它的特殊能力会成为障碍。

### 1.1 整数类型 ###

有两种类型的数字：

1.  整数
2.  实数

如果是存储整数：

1.  TINYINT (8)
2.  SMALLINT (16)
3.  MEDIUMINT (24)
4.  INT (32)
5.  BIGINT (64)

整数类型有可选的 UNSIGNED 属性，表示不允许负值，这大致可以使正数的上限提高一倍。

例如

`TINYINT` 的存储范围是 `-128 ~ 127` ,`UNSIGNED TINYINT` 可以存储范围是 `0 ~ 255`。

### 1.2 实数类型 ###

实数是带有小数部分的数字。

1.  `FLOAT` （4字节）
2.  `DOUBLE` （8字节）

`FLOAT` 和 `DOUBLE` 类型只支持使用标准的浮点运算进行近似计算。

`DECIMAL` 类型用于存储精确的小数。

因为 CPU不支持对 DECIMAL 的直接计算，所以在 MySQL 5.0 以及更高版本中，MySQL服务器自身实现了 DECIMAL 的高精度计算。相对而言，CPU直接支持原生浮点计算，所以浮点运算明显更快。

因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用DECIMAL—例如存储财务数据。

但在数据量比较大的时候，可以考虑使用 BIGINT 代替 DECIMAL，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。

### 1.3 字符串类型 ###

varchar 和 char 是两种最主要的字符串类型。

varchar

varchar 类型用于存储可变长字符串，是最常见的字符串数据类型。

它比定长类型更节省空间，因为它仅使用必要的空间（越短的字符串使用越少的空间）。

varchar 需要1或2个额外字节记录字符串的长度。

下面这些情况下使用 varchar是合适的：

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

char

char 类型是定长的： MySQL总是根据定义的字符串长度分配足够的空间。

下面这些情况下使用 varchar是合适的：

1.  char适合存储很短的字符串，或者所有值都接近一个长度。（如密码的MD5值，定长值）
2.  对于经常变更的数据，char也是比varchar更好，因为定长的char类型不容易产生碎片。
3.  对于非常短的列，char 比varchar在存储空间上也更有效率。（如char(1)来存储Y和N,只需要一个字节，但是varchar(1)却需要两个字节，因为还需要一个记录长度的额外字节）

与 char 和 varchar 类似的类型还有 binary 和 varbinary ，它们存储的是二进制字符串。

使用 varchar(5) 和 varchar(200) 存储“hello” 的空间开销是一样的。那么使用更短的列有什么优势吗？

事实上有很大的优势。更长的列会消耗更多的内存，因为MySQL通常会分配固定大小的内存块来保存内部值。

尤其是使用内存临时表进行排序或操作时会特别糟糕。所以，最好的策略是只分配真正需要的空间。

blob 和 text 类型

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

### 1.4 日期和时间类型 ###

datetime

使用8个字节的存储空间。这个类型能保存大范围的值，从1001年到9999年，精度为秒。

timestamp

使用4个字节的存储空间。只能表示从1970年到2038年。

除特殊行为之外，通常应该尽量使用 timestamp，因为它比 datetime 空间效率更高。

如果需要存储比秒更小粒度的日期和时间值怎么办？

那么就可以使用`BIGINT`类型存储微秒级别的时间戳。

### 1.5 位数据类型 ###

MySQL有少数几种存储类型使用紧凑的位存储数据。所有这些位类型，不管底层存储格式和处理方式如何，比技术上来说都是字符串类型。

BIT

MySQL 把 BIT 当作字符串类型，而不是数字类型。当检索 BIT(1) 的值时，结果是一个包含二进制0或1值的字符串，而不是 ASCII 码的“0”或“1”。

应该谨慎使用 BIT 类型。对于大部分应用，最好避免使用这种类型。

SET

如果需要保存很多 true/ false值，可以考虑合并这些列到一个SET 数据类型，它在 MySQL 内部是以一系列打包的位的集合来表示的。

### 1.6 选择标识符 ###

选择标识列的类型时，不仅仅需要考虑存储类型，还需要考虑MySQL对这种类型怎么执行计算和比较。

整数通常是标识列最好的选择，因为它们很快并且可以使用 AUTO\_INCREMENT.

如果可能，应该避免使用字符串类型作为标识列，因为它们很消耗空间。

如果存储 UUID 值，则应该移除“-”符号，或者更好的做法是，使用 UNHEX() 函数转换 UUID 值为16字节的数字，并且存储在一个 BINARY(16)中，检索时可以通过HEX() 函数来格式化为十六进制格式。

IP地址，人们经常使用 varchar(15) 列来存储 IP 地址。

然而，它们实际上是32位无符号整数，不是字符串。用小数点将地址分为四段的表示方法也只是为了让人们阅读容易。

所以应该用无符号整数存储IP地址。MySQL提供 INET\_ATON() 和 INET\_NTOA() 函数在这两种表示方法之间转换。

## 2. MySQL 数据库设计中的陷阱 ##

设计MySQL 的数据库的问题。

不好的设计：

1.  太多列
2.  太多关联
3.  过度使用枚举
4.  不要怕使用NULL

## 3. 范式和反范式 ##

### 3.1 范式 ###

范式的优点

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

范式的缺点

1.  通常需要关联。

### 3.2 反范式 ###

反范式的优点

1.  反范式化的数据都在一张表中，可以很好的避免关联。

反范式的缺点

1.  更新数据会变复杂。

### 3.3 混用范式化和反范式化 ###

实际应用中经常需要混用。

最常见的反范式化数据的方法是复制或者缓存，在不同的表中存储相同的特定列，也就是冗余数据列，减少关联表。

另一个从父表冗余一些数据到子表的理由是排序的需要。

## 4. 缓存表和汇总表 ##

有时提升性能最好的方法是在同一张表中保存衍生的冗余数据。

然而，有时也需要创建一张完全独立的汇总表或缓存表（特别是满足检索的需求时）。

缓存表：表示存储那些可以比较简单地从数据库其它表获取（但每次获取的速度比较慢）数据的表。

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

不严格的计数或通过小范围查询填满间隙的严格计数，都比实时计算统计值速度快很多。

计数器表

如果应用在表中保存计数器，则在更新计数器时可能碰到并发问题。

更快地读，更慢的写

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

## 5. 加快ALTER TABLE 操作的速度 ##

ALTER TABLE 操作的性能对大表来说是个大问题。

MySQL执行大部分修改表结构操作的方法是用新的结构创建一个空表，从旧表中查出所有数据插入新表，然后删除旧表。这样操作可能需要花费很长时间，如果内存不足而表又很大，而且还有很多索引的情况下尤其如此。

使用方法有两种：

1.  先在一台不提供服务的机器上执行ALTER TABLE操作，然后和提供服务的主库进行切换。
2.  影子拷贝。用要求的表结构创建一张和源表无关的新表，然后通过重命名和删表操作交换两张表。

## 6. 总结 ##

良好的数据库设计原则是普遍适用的，但MySQL有它自己的实现细节要注意。

概括来说，尽可能保持任何东西小而简单总是好的。

MySQL喜欢简单，需要使用数据库的人应该也同样喜欢简单的原则：

1.  尽量避免过度设计，
2.  使用小而简单的合适数据类型，除非真实数据模型中有确切的需要，否则应该尽可能避免使用NULL值。
3.  尽量使用相同的数据类型存储相似或相关的值，尤其是要在关联条件中使用的列。
4.  注意可变长字符串，其在临时表和排序时可能导致悲观的按最大长度分配内存。
5.  尽量使用整型定义标识列。
6.  以免使用MySQL已经遗弃的特性，如指定浮点数的精度，或者整数的显示宽度。
7.  小心使用ENUM和SET。最好避免使用BIT。

范式是好的，但是反范式有时也是必需的，并且能带来好处。

最后，ALTER TABLE 是让人痛苦的操作，因为在大部分情况下，它都会锁表并且重建整张表。