理解数据库中的模式

本文涉及的产品
Redis 开源版,标准版 2GB
推荐场景:
搭建游戏排行榜
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
简介: 【5月更文挑战第6天】这篇文章探讨了数据库模式的重要性和多种优化技巧。模式提供了一个命名空间来组织数据库对象,如表、视图和索引。通过使用模式,复杂的应用程序可以变得更容易理解和维护。文章介绍了防止日期重叠的 PostgreSQL 排除约束,用于存储树结构的物化路径方法,以及结合 NoSQL 的 JSON 列来简化数据存储。这些策略能帮助优化数据库设计和提升系统效率。

1 简介

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

coffe大海.jpg

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

正常使用的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。

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
6月前
|
数据采集 Oracle 关系型数据库
实时计算 Flink版产品使用合集之是否可以支持只读模式的数据库
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStreamAPI、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
5月前
|
Prometheus 监控 关系型数据库
数据库同步革命:MySQL GTID模式下主从配置的全面解析
数据库同步革命:MySQL GTID模式下主从配置的全面解析
621 0
|
3月前
|
资源调度 关系型数据库 MySQL
【Flink on YARN + CDC 3.0】神操作!看完这篇教程,你也能成为数据流处理高手!从零开始,一步步教会你在Flink on YARN模式下如何配置Debezium CDC 3.0,让你的数据库变更数据瞬间飞起来!
【8月更文挑战第15天】随着Apache Flink的普及,企业广泛采用Flink on YARN部署流处理应用,高效利用集群资源。变更数据捕获(CDC)工具在现代数据栈中至关重要,能实时捕捉数据库变化并转发给下游系统处理。本文以Flink on YARN为例,介绍如何在Debezium CDC 3.0中配置MySQL连接器,实现数据流处理。首先确保YARN上已部署Flink集群,接着安装Debezium MySQL连接器并配置Kafka Connect。最后,创建Flink任务消费变更事件并提交任务到Flink集群。通过这些步骤,可以构建出从数据库变更到实时处理的无缝数据管道。
294 2
|
3月前
|
存储 SQL 算法
【OceanBase】惊天大反转!启动时真的会占用95%磁盘空间?别怕!揭秘真相+实用调整技巧,手把手教你如何优雅地管理磁盘空间,让你的数据库从此告别“吃土”模式!
【8月更文挑战第15天】OceanBase是一款高性能分布式数据库,启动时并不会默认占用95%磁盘空间,这是一种误解。其设计注重资源管理,可根据业务需求动态调整空间使用。通过设置`max_disk_usage`等参数、优化表设计、定期清理数据及启用压缩等功能,可有效控制磁盘占用,确保高效利用存储资源。
84 1
|
3月前
|
SQL 数据库 Java
Hibernate 日志记录竟藏着这些秘密?快来一探究竟,解锁调试与监控最佳实践
【8月更文挑战第31天】在软件开发中,日志记录对调试和监控至关重要。使用持久化框架 Hibernate 时,合理配置日志可帮助理解其内部机制并优化性能。首先,需选择合适的日志框架,如 Log4j 或 Logback,并配置日志级别;理解 Hibernate 的多级日志,如 DEBUG 和 ERROR,以适应不同开发阶段需求;利用 Hibernate 统计功能监测数据库交互情况;记录自定义日志以跟踪业务逻辑;定期审查和清理日志避免占用过多磁盘空间。综上,有效日志记录能显著提升 Hibernate 应用的性能和稳定性。
50 0
|
3月前
|
SQL API 数据库
揭秘Ruby数据库交互的黑科技!ActiveRecord模式:为何它让数据库操作如此“随心所欲”?
【8月更文挑战第31天】在Ruby编程中,与数据库交互至关重要。ActiveRecord作为Ruby on Rails框架的核心组件,凭借其简洁高效的特点,成为处理数据库操作的首选。本文深入探讨ActiveRecord模式,介绍其如何简化数据库交互,并通过示例代码展示具体应用。ActiveRecord是一种ORM框架,将数据库表映射为Ruby类,使开发者能通过操作对象间接管理数据库记录。其核心特性包括模型定义、关联管理、数据验证、事务处理及强大的查询接口。通过示例代码,展示了如何定义模型、创建记录、查询记录及处理关联,突显了ActiveRecord在简化数据库操作方面的优势。
65 0
|
5月前
|
存储 关系型数据库 数据库
回顾数据库的三级模式,为什么比直接存文件表格好?
【6月更文挑战第10天】本文介绍数据库用于解决Excel等文件系统存在的数据冗余、不一致和访问困难等问题。DBMS中的关系有一对一、一对多、多对一和多对多四种类型。键有候选键、超级键、主键、备用键和外键等类型,功能依赖分为平凡和非平凡两种。
43 0
回顾数据库的三级模式,为什么比直接存文件表格好?
|
6月前
|
存储 关系型数据库 数据库
不直接使用文件存储?浅谈数据库的三级模式及重要概念
【5月更文挑战第21天】本文介绍数据库用于解决传统文件系统如Excel的数据冗余、不一致性和访问困难等问题。关系型数据库通过DBMS实现数据管理,包括外模式(用户视图)、概念模式(全局逻辑结构)和内模式(物理存储)。
136 1
不直接使用文件存储?浅谈数据库的三级模式及重要概念
|
5月前
|
存储 NoSQL 算法
图数据库:连接数据的新模式
【6月更文挑战第16天】图数据库是处理复杂关系数据的新兴技术,使用节点、边和属性表示数据间关系。它提供强大的关系表达能力、灵活性、实时性和扩展性。新模式包括关系网络可视化、基于路径的查询、内置图算法支持,适用于推荐系统和社交网络分析,助力企业挖掘数据价值并应对大数据时代挑战。随着技术发展,图数据库将在数据连接和分析中扮演关键角色。
|
5月前
|
关系型数据库 数据库