1 简介

定义模式就相当定义一个命名空间，在该空间内可以进一步定义该模式包含的数据库对象，如基本表、视图、索引等。

一个数据库中可以建立多个模式，一个模式通常包括多个表、视图和索引等数据库对象。

正常使用的SQL Server在数据库内创建的表都是dbo开头的。因为我们正常使用的数据库用户名是dbo。

模式可能是数据库中最重要的部分。你有越复杂schema ，新开发人员將能够更容易地在您的程序上工作。

但它也提供了采用新方法并通过使用使它们更直接的可能性现代数据库功能。

实际上，其中许多功能可以卸载大量自定义程序逻辑到数据库，使开发更快。

模式章节將向您展示如何。 JSON文档可以替代很多表格、数据可以保存以用于更快的查询或更简单的存储树的方法。

2 没有重叠日期的行 仅支持PGSQL

        CREATE TABLE bookings (
         room_number int,
         reservation tstzrange,
         EXCLUDE USING gist (room_number WITH =, reservation WITH &&)
        );
        INSERT INTO meeting_rooms (
         room_number, reservation
        ) VALUES (
         5, '[2022-08-20 16:00:00+00,2022-08-20 17:30:00+00]',
         5, '[2022-08-20 17:30:00+00,2022-08-20 19:00:00+00]',
        );

防止例如同時预定多个会议室是一个复杂的任务因为竞争条件。

沒有应用程序或小心的悲观锁定规则，同时请求可以创建确切时间范围內的房间预定或重叠的。

以使用排除约束將工作卸載到数据库这將防止同一房間号的任何重叠范围。此安全功能是适用于整數、数字、日期和时间戳范围。

3 将树存储为物化路径

        -- MySQL
        CREATE TABLE tree (path varchar(255));
        INSERT INTO tree (path) VALUES ('Food');
        INSERT INTO tree (path) VALUES ('Food.Fruit');
        INSERT INTO tree (path) VALUES ('Food.Fruit.Cherry');
        INSERT INTO tree (path) VALUES ('Food.Fruit.Banana');
        INSERT INTO tree (path) VALUES ('Food.Meat');
        INSERT INTO tree (path) VALUES ('Food.Meat.Beaf');
        INSERT INTO tree (path) VALUES ('Food.Meat.Pork');
        SELECT * FROM tree WHERE path like 'Food.Fruit.%';
        SELECT * FROM tree WHERE path IN('Food', 'Food.Fruit');

        -- PostgreSQL
        CREATE EXTENSION ltree;
        CREATE TABLE tree (path ltree);
        INSERT INTO tree (path) VALUES ('Food');
        INSERT INTO tree (path) VALUES ('Food.Fruit');
        INSERT INTO tree (path) VALUES ('Food.Fruit.Cherry');
        INSERT INTO tree (path) VALUES ('Food.Fruit.Banana');
        INSERT INTO tree (path) VALUES ('Food.Meat');
        INSERT INTO tree (path) VALUES ('Food.Meat.Beaf');
        INSERT INTO tree (path) VALUES ('Food.Meat.Pork');
        SELECT * FROM tree WHERE path ~ 'Food.Fruit.*{1,}';
        SELECT * FROM tree WHERE path @> subpath('Food.Fruit.Banana', 0, -1);

除了广为人知的方法之外，您还可以使用鲜为人知的物化路径方法用于存储树的嵌套集和邻接表方法。

每行存储物化树中的路径到自身，使得树搜索的查询相对容易。和PostgreSQL 您將获得广泛的查询和操作功能。

而对于 MySQL，您必须使用简单的文本搜索功能。

4 结合 NoSQL 和关系数据库的 JSON 列

        -- MySQL
        CREATE TABLE books (
         id bigint PRIMARY KEY,
         author_id bigint NOT NULL,
         category_id bigint NOT NULL,
         name varchar(255) NOT NULL,
         price numeric(15, 2) NOT NULL,
         attributes json NOT NULL DEFAULT '{}'
        );
        -- PostgreSQL
        CREATE TABLE books (
         id bigint PRIMARY KEY,
         author_id bigint NOT NULL,
         category_id bigint NOT NULL,
         name text NOT NULL,
         price numeric(15, 2) NOT NULL,
         attributes jsonb NOT NULL DEFAULT '{}'
        );

可以通过从 NoSQL 数据库复制想法來简化模式。通过例如，查询和数据修改逻辑将容易得多。

避免很多连接或实体-属性-值方法 (EAV) 等复杂架构。但是，你应该仍然继续將大部分数据存儲在标准关系模式中。

你可以使用 JSON列简化为遵循这些规则的架构：

將很少使用的数据（例如从其他表连接的数据）移动到 JSON 数组和对象中方便查询。

仔細考虑您是否將对 JSON 文档中其他表的引用存储为您不能強制执行外键关系。

你需要一个很好的理由这样做。从未使用过深度嵌套的集合。对这些的任何修改和查询文档会乱七八糟。

更多内容： JSON columns

5 使用 JSON 数组的替代标签存储

         -- MySQL
        CREATE TABLE products (
         id bigint,
         name varchar(255),
         tagids json
        );
        CREATE INDEX producttags ON products ((CAST(tagids as unsigned ARRAY)));
        SELECT *
        FROM products
        WHERE JSON_ARRAY(3, 8) MEMBER OF(tagids) AND NOT(12 MEMBER OF(tagids));

        -- PostgreSQL
        CREATE TABLE products (
         id bigint,
         name text,
         tagids jsonb
        );
        CREATE INDEX producttags ON products (tags jsonb_path_ops);
        SELECT *
        FROM products
        WHERE tagids @> '[3,8]' AND NOT(tagids @> '[12]');

您还记得关于存储对其他表的引用必须是一个格式良好的決定的最后提示嗎？

这是使用 m:n 表简化典型标记模式的好方法，绝地值得丟失的外键。

如果將标签表的主键存储在JSON 数组，您可以对具有和不具有特定标签的产品进行高效查找。

一個传统方法需要 6 次连接，3 次到 m:n 表，3 次到标签表，而 JSON 方法只是一个简单的条件。

跳过很有效进行许多复杂的连接，特別是对于使用许多标签的基于过滤器的查询。

6 改进数据严格性的约束

    ALTER TABLE reservations
    ADD constraint start_before_end CHECK (checkin_at < checkout_at);
    ALTER TABLE invoices
    ADD constraint eu_vat CHECK (
     NOT(is_europeanunion) OR vatid IS NOT NULL
    );

某些列值或它們的存在將取決于其他列。这些依賴由您的程序验证，但也可以通过数据库中的约束进行检查确保您始終拥有有效数据的最後边界。

你可以争辩说在程序就足夠了，但是当您批量更新行或人工修改数据库时您的程序检查將不会执行。

7 根据模式验证 JSON 列

    -- MySQL
    ALTER TABLE products ADD CONSTRAINT CHECK(
     JSON_SCHEMA_VALID(
     '{
     "$schema": "http://json-schema.org/draft-04/schema#",
     "type": "object",
     "properties": {
     "tags": {
     "type": "array",
     "items": { "type": "string" }
     }
     },
     "additionalProperties": false
     }',
     attributes
     )
    );
    ALTER TABLE products ADD CONSTRAINT data_is_valid CHECK(
     validate_json_schema(
     '{
     "type": "object",
     "properties": {
     "tags": {
     "type": "array",
     "items": { "type": "string" }
     }
     },
     "additionalProperties": false
     }',
     attributes
     )
    );

使用 JSON 列，您可以用数据严格性保证來换取更轻松的查询和修改。

任何东西都可以存储在该 JSON 文档中，您必须记录严格针对其他开发人员的预期值。

但是当你使用約束來验证针对 JSON 模式的数据，您重新获得数据严格检查并获得自动类型文档。

更多内容： JSON Schema validation for columns

8 针对枚举攻击的 UUID 密钥

    -- MySQL
    ALTER TABLE users ADD COLUMN uuid char(36);
    UPDATE users SET uuid = (SELECT uuid_v4());
    ALTER TABLE users CHANGE COLUMN uuid uuid char(36) NOT NULL;
    CREATE UNIQUE INDEX users_uuid ON users (uuid);

    -- PostgreSQL
    ALTER TABLE users ADD COLUMN uuid uuid NOT NULL DEFAULT
    gen_random_uuid();
    CREATE UNIQUE INDEX users_uuid ON users (uuid);

在 URL 中使用递增的主鍵会將您的数据暴露給行为不端的对手。

他们可以將您的 ID 一个一个递增以爬取您所有的公共数据或通过监视这些数据值计算您的应用程序的增长和受欢迎程度。

一个简单有效的对策是向將在 URL 中使用的每一行添加一个随机 UUID v4公开您的真实递增 ID。

您还可以轻松地將此 UUID 添加到已有表。

更多内容： UUIDs to prevent Enumeration Attacks

9 快速删除带分区的大数据

    ALTER TABLE logs DROP PARTITION logs_2022_january;

从表中刪除大量数据，例如历史行，将花很多时间。你可以通过在许多小批量中执行刪除查詢來优化它，但是你只是在更长的时间范围內扩展工作量。

當你分区你的大表时分成更小的部分，您可以在几秒內刪除一个或多个分区。

10 预先排序的表格以便更快地访问

    SELECT *
    FROM product_comments
    WHERE product_id = 2
    ORDER BY comment_id ASC
    LIMIT 10
    -- MySQL
    CREATE TABLE product_comments (
     product_id bigint,
     comment_id bigint auto_increment UNIQUE KEY,
     message text,
     PRIMARY KEY (product_id, comment_id)
    );
    -- PostgreSQL
    CREATE TABLE product_comments (
     product_id bigint,
     comment_id bigint GENERATED ALWAYS AS IDENTITY,
     message text,
     PRIMARY KEY (product_id, comment_id)
    );
    CLUSTER product_comments USING product_comments_pkey;

您插入表中的每个数据都將在数据库文件中进行物理排序，以便选择和更新行等常见任務效率最高。

但是数据库不知道你將如何使用这些行。在构建电子商务程序时，您希望获得对产品的一些评论。

通常，这些评论通过递增的方式存储主鍵并按插入順序分佈在表中。

但是你可以強制這些行按产品 (product_id) 升序物理存储和评论日期 (comment_id)。

数据库现在可以高效地找到第一个评论和下一行中的下九个，而不是在 10 个不同的地方收集位置。

无论您使用 SSD 还是 HDD，总是将随机访问多个位置比单個操作获取多个连续字节慢。

这是我可以教給你的最令人兴奋和最复杂的性能优化数据。下面的文章分享了更多信息和实施障碍，

更多内容： Sorted Tables for Faster Range-Scans

11 预聚合值以加快查询速度

    SELECT SUM(likes_count)
    FROM articles
    WHERE user_id = 1 and publish_year = 2022;

即使您的模式設計得非常好並且您的查詢都使用了完美的索引，它们也可能还是慢，当分析查询时，例如對于仪表板，

必須聚合数十个或数十万行的性能將受到严重影响。

这样的查询是受有关数据加載速度和所需的计算限制的限制是时候从行或索引中提取信息并聚合它们了。

这个操作对于少量数据來說非常快，但是它越大，你就越应该研究存储預先聚合的值。

沒有智能索引会打败性能不必聚合数万个值的改进。

12 小结

Schema是一组相关的数据库对象的集合，Schema的名字为该组对象定义了一个命名空间，而在数据建模领域，Schema（模式）表示的是 用形式语言描述的数据库的结构 ；
简
单来说，可以这样理解，数据建模所讲的Schema<也就是元数据>保存在SQL环境下相应Catalog。