跳至主要內容

schema

荒流2024年1月29日大约 14 分钟约 4247 字

1. 数据库引擎

  • InnoDB：一个可靠的事务处理引擎，不支持全文本搜索。

    • InnoDB 是 MySQL 的默认事务型存储引擎，也是最重要、使用最广泛的引擎。
    • InnoDB 是为处理大量短期事务而设计的，这些事务通常是正常提交的，很少会被回滚。
    • InnoDB 默认为 REPEATABLE READ 隔离级别。

  • MyISAM：支持全文本搜索，但不支持事务处理，性能极高

  • MEMORY：功能等同于MyISAM，但由于数据存储在内存中，速度很快，特别适合临时表

引擎类型可以混用，但外键不能跨引擎。

2. 数据类型

2.1 概述

对于一个关系表，除了定义每一列的名称外，还需要定义每一列的数据类型。关系数据库支持的标准数据类型包括数值、字符串、时间等，下表为一些常用类型：

名称类型说明
INT整型4 字节整数类型，范围约+/-21 亿
BIGINT长整型8 字节整数类型，范围约+/-922 亿亿
REAL浮点型4 字节浮点数，范围约+/-1038
DOUBLE浮点型8 字节浮点数，范围约+/-10308
DECIMAL(M,N)高精度小数由用户指定精度的小数，例如，DECIMAL(20,10)表示一共 20 位，其中小数 10 位，通常用于财务计算
CHAR(N)定长字符串存储指定长度的字符串，例如，CHAR(100)总是存储 100 个字符的字符串
VARCHAR(N)变长字符串存储可变长度的字符串，例如，VARCHAR(100)可以存储 0~100 个字符的字符串
BOOLEAN布尔类型存储True或者False
DATE日期类型存储日期，例如，2018-06-22
TIME时间类型存储时间，例如，12:20:59
DATETIME日期和时间类型存储日期+时间，例如，2018-06-22 12:20:59
JSON

选择数据类型的几个原则：

  • 更小的通常更好：更小的数据类型通常更快，因为它们占用的磁盘、内存和 CPU 缓存的空间更少，并且处理时需要的 CPU 周期也更少。

  • 简单为好：简单数据类型的操作通常需要更少的 CPU 周期。例如，整型数据比字符型数据的比较操作代价更低，因为字符集和排序规则使字符型数据的比较更复杂。

  • 尽量避免存储 NULL：如果查询中包含可为 NULL 的列，对 MySQL 来说更难优化，因为可为 NULL 的列使得索引、索引统计和值比较都更复杂；可为 NULL 的列会使用更多的存储空间，在 MySQL 里也需要特殊处理。

    不过，通常把可为 NULL 的列改为 NOT NULL 带来的性能提升比较小，所以（调优时）没有必要首先在现有 schema 中查找并修改这种情况，除非确定其会导致问题。

MySQL 为了兼容性支持很多别名，例如：

别名本质
INTEGERINT
BOOLTINYINT
NUMERICDECIMAL

2.2 数字类型

MySQL 有两种类型的数字：整数（whole number）和实数（real number，带有小数部分的数字）。

2.2.1 整数类型

整数类型又可以分为：

  • TINYINT：8 位存储空间
  • SMALLINT：16 位存储空间
  • MEDIUMINT：24 位存储空间
  • INT：32 位存储空间
  • BIGINT：64 位存储空间

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

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

MySQL 可以为整数类型指定宽度，例如，INT(11)，这对大多数应用毫无意义：它不会限制值的合法范围，只是规定了 MySQL 的一些交互工具（例如，MySQL 命令行客户端）用来显示字符的个数。对于存储和计算来说，INT(1) 和 INT(20) 是相同的。

2.2.2 实数类型

实数是带有小数部分的数字，然而，它们不仅适用于带小数的数字，也可以使用 DECIMAL 存储比 BIGINT 还大的整数。

MySQL 既支持精确类型，也支持不精确类型：

  • FLOAT：32 位存储空间，使用标准的浮点运算进行近似计算
  • DOUBLE：64 位存储空间，使用标准的浮点运算进行近似计算
  • DECIMAL：精确存储与计算。
    • 由于额外的空间需求和计算成本，应该尽量只在对小数进行精确计算时才使用DECIMAL，例如存储财务数据。
    • 在一些大容量的场景，可以考虑使用 BIGINT 代替 DECIMAL，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。

与整数类型一样，你只能选择存储类型；MySQL 会使用 DOUBLE 进行浮点类型的内部计算。

2.3 字符串类型

MySQL 支持多种字符串数据类型，每种类型还有许多变体。每个字符串列可以有自己的字符集和该字符集的排序规则集。

2.3.1 VARCHAR, CHAR

VARCHAR 和 CHAR 是两种最主要的字符串类型。不幸的是，很难精确地解释这些值是如何存储在磁盘和内存中的，因为这跟存储引擎的具体实现的关，我们这里的描述将假设使用的存储引擎是 InnoDB 的。

存储引擎在内存中存储 CHAR 和 VARCHAR 值的方式可能与在磁盘上存储该值的方式不同，并且服务器在从存储引擎检索该值时可能会将其替换为另一种存储格式。

请记住，字符串长度定义的不是字节数，是字符数。多字节字符集可能需要多个字节来存储 1 个字符。

  • VARCHAR：存储可变长度的字符串，是最常见的字符串数据类型。

    • VARCHAR 仅使用必要的空间，因此比 CHAR 类型更省空间。
    • VARCHAR 需要额外使用 1 或 2 字节记录字符串的长度：如果列的最大长度小于或等于 255 字节，则只使用 1 字节表示，否则使用 2 字节。
    • VARCHAR 节省了存储空间，所以对性能也有帮助。但是，由于行是可变长度的，在更新时可能会增长，这会导致额外的工作。
    • 适用于VARCHAR的场景：
      • 字符串列的最大长度大于平均长度；
      • 列的更新很少，所以碎片不是问题；
      • 使用了像 UTF-8 这样复杂的字符集，每个字符都使用不同的字符数进行存储。

  • CHAR：存储固定长度的字符串

    • MySQL 总是为定义的字符串长度分配足够的空间，当存储 CHAR 值时，MySQL 删除所有尾随空格，如果需要进行比较，值会用空格填充。

    • CHAR 适合存储非常短的字符串，或者适用于所有值的长度都几乎相同的情况。

    • CHAR 类型的下述行为可能有一点让人难以理解：

      • 假设创建一张只有一个 CHAR(10) 列的表并且往里面插入一些值：

        mysql> CREATE TABLE char_test( char_col CHAR(10));
mysql> INSERT INTO char_test(char_col) VALUES ('string1'), (' string2'), ('string3 ');

      • 当检索这些值的时候，会发现末尾的空格被截断了：

        mysql> SELECT CONCAT("'", char_col, "'") FROM char_test;
-- 'string1'
-- ' string2'
-- 'string3'

2.3.2 VARBINARY, BINARY

与 VARCHAR 和 CHAR 非常类似，只是它们存储的是二进制字符串。

所谓二进制字符串与常规字符串非常相似，但它们存储的是字节而不是字符。填充也不同：MySQL 填充 BINARY 用的是 \0（零字节） 而不是空格，并且在检索时不会去除填充值。

二进制比较比字符比较简单得多，因此速度更快。

2.3.3 TEXT, BLOB

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

  • TEXT 家族
    • TINYTEXT
    • SMALLTEXT：TEXT 是 SMALLTEXT 的同义词
    • MEDIUMTEXT
    • LONGTEXT
  • BLOB 家族
    • TINYBLOB
    • SMALLBLOB：BLOB 是 SMALLBLOB 的同义词
    • MEDIUMBLOB
    • LONGBLOB

BLOB 和 TEXT 家族之间的唯一区别是：BLOB 类型存储的是二进制数据，没有排序规则或字符集，但 TEXT 类型有字符集和排序规则。

在数据库中存储图像？

在过去，很常见的一种做法是应用程序将图像作为 BLOB 数据存储在 MySQL 数据库中，这种方法便于将应用程序的数据保存在一起，但是，随着数据的增长，修改 schmea 等操作会由于 BLOB 数据的大小而变得越来越慢。

如果可以避免的话，不要在数据库中存储图像这样的数据。相反，应该将它们写入单独的对象数据存储，并使用该表来跟踪图像的位置或文件名。

2.4 枚举类型

ENUM 类型可以存储一组预定义的不同字符串值。MySQL 在存储枚举时非常紧凑，会根据列表值的数量压缩到 1 或 2 字节中，在内部会将每个值在列表中的位置保存为整数。例如，

  • 这样一个表结构：

    mysql> CREATE TABLE enum_test (
    -> e ENUM('fish', 'apple', 'dog') NOT NULL
);
mysql> INSERT INTO enum_test(e) VALUES ('fish'), ('dog'), ('apple');

  • 其中插入的一条数据，对于枚举列来说，存储的实际是整数，而非字符串。可以通过在数值上下文中检索看到这个双重属性：

    mysql> SELECT e + 0 FROM enum_test
+-------+
| e + 0 |
+-------+
|     1 |
|     3 |
|     2 |

  • 另一个令人惊讶的事情是，ENUM 字段是根据内部整数值排序的，而不是根据字符串本身：

    mysql> SELECT e FROM enum_test ORDER BY e;
+-------+
| e     |
+-------+
| fish  |
| apple |
| dog   |

    可以通过按照需要的顺序指定 ENUM 成员来解决这个问题，也可以在查询中使用 FIELD() 函数显式地指定排列顺序，但这会导致 MySQL 无法利用索引消除排序。

MySQL 将每个枚举存储为整数，并且必须进行查找以将其转换为字符串表示，因此 ENUM 列有一些开销。这些开销通常可以被 ENUM 列的小尺寸所抵消，但并不总是如此，特别是，将 CHAR/VARCHAR 列联接到 ENUM 列可能比联接到另一个 CHAR/VARCHAR 列更慢。

书中设置了某个基准测试，其实验结果如下：

测试QPS
VARCHAR 联接 VARCHAR2.6
VARCHAR 联接 ENUM1.7
ENUM 联接 VARCHAR1.8
ENUM 联接 ENUM3.5

ENUM 类型在储值的方式上非常有效，但更改 ENUM 中的有效值会导致需要做 schema 变更。如果你没有一个健壮的系统来支持自动 schema 变更，那么如果 ENUM 经常更改，这种操作需求可能会带来很大的不便。

2.5 日期和时间类型

MySQL 中有很多数据类型用以支持各种各样的日期和时间值，比如 YEAR 和 DATE，MySQL 可以存储的最小时间粒度是微秒。

大多数时间类型都没有其他选择，因此不存在哪一种是最佳选择的问题，除了当需要同时存储日期和时间时面临的 DATETIME 和 TIMESTAMP 的选择。

DATETIME 和 TIMESTAMP 列都用来存储时间和日期，最小可以精确到微秒，其区别是：

  • DATETIME：

    • 能表达的范围为 1000~9999 年，精度为 1 微秒，它以 YYYYMMDDHHMMSS 格式存储压缩成整数的日期和时间，且与时区无关；
    • 其需要 8 字节的存储空间。

  • TIMESTAMP：

    • 顾名思义，其存储自 1970 年 1 月 1 日起格林尼治时间（GMT）午夜以来经过的秒数——以 UNIX 时间戳相同。

    • 时间戳显示的值依赖于时区，因此 TIMESTAMP 还会根据时区变化，MySQL 服务器、操作系统和客户端连接都有时区设置。

      存储值为 0 的 TIMESTAMP 在美国东部标准时间（EST）中显示为 1969-12-31 19:00:00，与格林尼治标准时间（GMT）差 5 小时。

    • 只使用 4 字节的存储空间，仅为 DATETIME 的一半，因此其表示的范围比 DATETIME 小得多：只能表示从 1970 年到 2038 年 1 月 19 日。

在设计 schema 时，日期和时间类型最终可以归结为以下几件事：

  • 需要支持前后多大范围的日期和时间？
  • 存储空间对这些数据有多重要？
  • 需要支持分数秒吗？
  • 在 MySQL 中处理日期、时间和时区，还是在代码中处理？

2.6 位压缩类型

MySQL 有几种使用值中的单个位来紧凑地存储数据的类型。所有这些位压缩类型，不管底层存储和处理方式如何，从技术上来说都是字符串类型。

  • BIT：

    • BIT(1) 定义一个包含 1 位的字段，BIT(2) 存储 2 位的字段，依此类推，BIT 列的最大长度为 64 位。

      InnoDB 将每一列存储为足够容纳这些位的最小整数类型，所以使用 BIT 列不会节省任何存储空间。

    • MySQL 在处理时会将 BIT 视为字符串类型，而不是数字类型；但是，如果在数字上下文中检索该值，则会将 BIT 字符串转换为数字。这可能让人非常困惑，因此建议慎用 BIT 类型。

  • SET：

    • 如果需要存储多个 true/false 值，可以考虑使用 MySQL 原生的 SET 数据类型，可以将多列组合成一列，这在 MySQL 内部是以一组打包的位的集合来表示的。

2.7 JSON 类型

MySQL 有原生的 JSON 数据类型，可以方便地直接在表中的 JSON 结构部分进行操作。

纯粹主义者可能会认为，在数据库中存储原始 JSON 是一种反范式，因为理想情况下，schema 应该是 JSON 中具体字段的表示。

在《高性能 MySQL》一书中，设计了一个简单的测试用例，来对比说明 JSON 类型与原生 SQL 类型的差异。总的来说，决定使用原生 SQL 还是 JSON 取决于在数据库中存储 JSON 的便捷性是否大于性能。

2.8 选择标识符的类型

这里所谓的标识符，是指区分不同数据行的一个标识，说白了就是标识符表达了数据行的 ID 的意思。例如，你有一个关于用户的表，可能希望为每个用户分配一个数字 ID 或唯一的用户名，此字段即是一个标识符。为标识符列选择合适的数据类型非常重要，因为标识符通常会是主键或索引。

在为标识符列选择类型时，不仅需要考虑存储类型，还需要考虑 MySQL 如何对该类型执行计算和比较。

  • 整数通常是标识符的最佳选择，因为它们速度快，并且可以自动递增（AUTO_INCREMENT）。
  • 如果可能，应避免使用字符串类型作为标识符的数据类型，因为它们很消耗空间，而且通常比整数类型慢。
    • 对于完全随机的字符串要非常小心，如 MD5(), SHA1(), UUID() 生成的字符串，这些函数生成的新值会任意分布在很大的空间内，这会减慢 INSERT 和某些类型的 SELECT 查询的速度。
    • 如果存储通常唯一标识符（UUID）值，则应该删除破折号，或者更好的做法是，使用 UNHEX() 函数将 UUID 值转换为 16 字节的数字，并将其存储在一个 BINARY(16) 列中，可以 HEX() 函数以十六进制格式检索值。
  • ENUM 和 SET 类型通常是糟糕的选择。

3. 字符集与排序规则

字符集（charset）与排序规则（collation）：

  • 查看 MySQL 所支持的字符集完整列表

    SHOW CHARACTER SET;

  • 查看 MySQL 所支持排序规则的完整列表

    SHOW COLLATION;

  • 创建数据库时指定默认的字符集和排序规则

    SHOW VARIABLES LIKE 'character%';
SHOW VARIABLES LIKE 'collation%';

  • 给表指定字符集和排序规则

    CREATE TABLE mytable
(
    column1	INT,
    column2 VARCHAR(10)
) DEFAULT CHARACTER SET hebrew
  COLLATE hebrew_general_ci;

  • 给每个列设置字符集和排序规则

    CREATE TABLE mytable
(
    column1	INT,
    column2	VARCHAR(10),
    column3	VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_general_ci
) DEFAULT CHARACTER SET hebrew
  COLLATE hebrew_general_ci;

MySQL 如下确定使用什么样的字符集和排序规则：

  1. 如果同时指定CHARACTER SET和COLLATE两者，则使用这些值；
  2. 如果只指定CHARACTER SET，使用此字符集及其默认的排序规则；
  3. 如果既不指定CHARACTER SET，也不指定COLLATE，则使用数据库默认值。

若需要用与创建表时不同的排序规则来排序特定的SELECT语句，可以在SELECT语句自身进行：

SELECT * FROM customers
ORDER BY lastname, firstname COLLATE latin1_general_cs;

使用Cast()和Convert()函数可以使得串在字符之间进行转换。

编辑此页
上次编辑于: 2024/5/6 10:03:53
贡献者: chua-n
应无所住，而生其心
Copyright © 2024 荒流