首页 必观作品 SVG教程 SVG手册 关于 全部文章

你真的了解 SQL 吗？数据库工程师究竟建议你做什么？

有大量的生产代码在应用程序代码中嵌入了 SQL 语句，还有许多其他数据库工程师认为晦涩难懂的东西。在我的职业生涯中，我很幸运能与一些经验最丰富的数据库工程师共事。他们不仅是编写和运行大型 SQL 的工程师，也是编写 MySQL、Microsoft SQL Server 和 Oracle 等数据库运行代码的工程师。这些经验丰富的数据库工程师通常推荐的东西与许多开发人员实际构建的东西大相径庭。

图0：你真的了解 SQL 吗？数据库工程师究竟建议你做什么？

即使表格发生变化，getItems 存储过程也能保持兼容

从我十几岁开始，潮一代的工程师们就对我非常耐心和细心。无论我提出什么奇怪的问题，他们都会仔细向我解释答案。他们告诉我，如果我动了这些表，每当他们需要更改数据库结构时，就必须与我协调。他们将来很难与我的应用程序保持兼容。元素周期表

DELIMITER $$

/*
  The tables can change whenever and however they want, as 
  long as this procedure returns the same columns, any app
  that uses it remains compatible. This isolates the database
  from the application and creates a "contract" between the
  application and the database
*/
CREATE PROCEDURE getItems(currencyCode VARCHAR(3))
BEGIN
  SELECT m.menuItemName AS itemName,
          mp.menuItemPriceAmount AS itemPriceAmount,
          c.currencyCode AS itemPriceCurrencyCode
          FROM menuItem m, menuItemPrice mp, currency c
          WHERE mp.menuItemPriceMenuItemId = m.menuItemId
          AND mp.menuItemPriceCurrencyId = c.currencyId
          AND c.currencyCode = currencyCode; 
END$$

他们接着向我展示了他们有多少存储过程，只是为了保持与其他应用程序的兼容性。我们翻阅了他们的积压工作，他们向我展示了目前正在进行的一些更改。这些更改会破坏我打算执行的任何 SELECT。他们的整个路线图意味着我几乎每个月都要调整代码才能跟上他们的变化。就在这时，我突然意识到。我的应用程序中的存储过程和视图就是为了隔离而设计的。有了这种隔离，它们就能与我保持多年的兼容性。

你不需要 cron job

经验丰富的工程师经常会深入分析我的应用程序及其周围的系统架构。当偶然发现计划作业或 cron 作业时，他们会质疑它们的存在。尤其是当它们只执行 SQL 时。Oracle 10g 和 Oracle Scheduler程序引入后，他们完全拒绝了我的任何 cron 作业。当 MySQL 5.1.6 引入了 CREATE EVENT ON SCHEDULE 后，MySQL 数据库中执行 SQL 的 cron 作业就被终结了，他们是这么认为的🫣。

/* MySQL scheduled event calling a procedure */
CREATE EVENT whatever_event_name_that_is
ON SCHEDULE EVERY 1 DAY STARTS '2025-05-01 07:30:00'
  CALL whatever_procedure_that_is();

在数据库内部运行 EVENT 不仅速度更快，还能将数据管理与任何外部世界隔离开来。Postgres 有 pg_cron 扩展，可用于标准安装和部署，如 AWS 的 Aurora、Azure Postgres 和 Google Cloud SQL for Postgres。如今，微软在 SQL Server 中实现这一功能的复杂方式并没有让我感到兴奋。

Oracle、MySQL 和 MariaDB 的实现方式表明，了解数据库不仅仅是文件的替代品是多么重要。我经常听到这样一句话：”你需要将数据库本身视为一个应用程序”。RDBMS 是关系数据库管理系统（Relational Data Base Management System）的缩写，根据这个定义，它不仅仅是简单的存储。它允许进行事务处理，并对查询、插入和管理数据以及执行 SQL 进行一系列优化。

这还没有触发你的兴趣吗？

当您从喜欢的商店订购商品时，您希望收到订单状态变化的更新。因此，数据库需要记录订单状态的变化。任何更新订单状态的应用程序或存储过程都必须这样做。年轻时，当我兴高采烈地把所有日志逻辑都写进应用程序代码时，资深工程师把我拉到一边说：”让我来教教你数据库的可维护性吧。

图1：你真的了解 SQL 吗？数据库工程师究竟建议你做什么？

视图可以从表格中抽象出来，也可以提供另一种经过转换的视图

通过提供视图，查询应用程序无需处理数据库的底层关系。视图提供了数据的简化形式，这将大大降低应用程序的复杂性。此外，如果对视图的查询遇到性能问题，视图背后的 SQL 代码可以在数据库端进行优化，而无需应用程序开发人员的协助。

CREATE VIEW productSearchView AS
SELECT
    p.productName,
    a.attributeCode,
    pa.productAttributeValue
FROM
    product p
JOIN
    productAttribute pa ON p.productId = pa.productAttributeProductId
JOIN
    attribute a ON pa.productAttributeAttributeId = a.attributeId;

创建具有不同版本号的视图也是一个常见的概念，我的导师就大量使用了这一点。您可以使用 productSearchView_v1 来代替 productSearchView，并确保其始终保持不变。试想一下，如果在应用层中使用版本控制的 API，那么数据库的更改将要求您更新该 API 的每个版本。使用版本控制视图只需要调整数据库中的 SQL。

是的，您需要复制您的数据

关系数据库管理系统适用于机票预订（我就是这么过来的）、酒店、零售、政府等任何需要事务完整性的领域。库存管理和订单处理主要是在关系数据库管理系统中完成的。这些都是我们社会的 “工作马”，你不会找到很多关于它们的公开信息。我不得不依靠资深工程师的经验来了解它们及其在现实世界中的应用。

图2：你真的了解 SQL 吗？数据库工程师究竟建议你做什么？

将交易与分析隔离，确保它们互不干扰

用于报告的查询由于其资源密集的性质，往往会阻塞事务处理。这类查询需要通过单独建立的 OLAP 数据库或联机分析处理。这些数据库过去和现在通常都是 OLTP 的读取副本。将两者分开可确保大型分析查询不会干扰实时事务。这一点非常重要，但却很少应用。它甚至不需要单独的服务器，只需要一个单独的读取副本即可。

如果我们的游艇示例运行在同一个数据库中，那么在查询过去两个月中按客户和月份分组的游艇预订情况时，数据库可能会锁定几乎所有的表。设计不当的数据库环境会影响跨多个表的大规模报告查询操作，这种情况并不少见。

事务量下降？

以前似乎需要严格遵守 ACID（尤其是事务隔离）的应用程序，实际上往往不需要。想象一下，Bob 和 Alice 都想从商店买一本书，但库存中只剩下一本。当鲍勃先下订单时，事务隔离会确保鲍勃拿到书，而爱丽丝的订单被拒绝。这听起来很合理，也是最常见的例子。应用事务隔离的必要性是我在专业教育中学到的。但当我遇到微软的一位 SQL Server 工程师时，他看着我说 “嗯，这取决于客户的业务。

他告诉我的是一个不同的现实：商店的实习生 Timmy 在 Alice 被拒绝五分钟后正要更新库存。新书几个小时前就到了，但 Timmy 正在和会计辛迪调情，忽略了更新库存。现在，我们拒绝了爱丽丝的订单，让爱丽丝这个客户很不高兴，因为数据库 “以为 ”还剩一本。在没有事务隔离和最终一致性的环境中，我们会接受爱丽丝的订单。而痴情的 Timmy 很可能根本不会接受。如果这本书真的缺货，而且在不久的将来也到不了货，那么就会有人打电话给爱丽丝并解决这个问题。许多企业发现，最终一致的非隔离事务比严格遵守 ACID 更经济。

图3：你真的了解 SQL 吗？数据库工程师究竟建议你做什么？

Eloquent ORM 与原始 SQL 的对比，Ishaq Jound 和 Hamed Halimi，瑞典布莱金技术学院

DELIMITER $$

CREATE PROCEDURE updateUserRealName(IN user_json JSON)
BEGIN
    DECLARE username VARCHAR(255);
    DECLARE realname VARCHAR(255);

    -- Extract values from the JSON input
    SET username = JSON_UNQUOTE(JSON_EXTRACT(user_json, '$.userName'));
    SET realname = JSON_UNQUOTE(JSON_EXTRACT(user_json, '$.realName'));

    -- Update the userRealName in the user table
    UPDATE user
    SET userRealName = realname
    WHERE userName = username;

END $$

DELIMITER ;

JSON 的引入也将对象和数组引入了 SQL。这不仅限于处理来自外部的 JSON。对象和数组可以在存储过程、触发器、事件、视图和表格中以任何形式使用。应用程序无需使用 ORM，只需将复杂的结构以 JSON 的形式传递给数据库即可。这一进步简化了数据库内部的 SQL 数据管理。

网络规模如何？

关系数据库管理系统经常被用作某种形式的通用解决方案。虽然 RDBMS 和 SQL 可以普遍解决数据管理问题，但也有其他替代方案。开发人员不应盲目使用关系数据库。历史证明，事务处理和关系数据库的高吞吐量代价高昂。如果你正在构建一个不需要严格事务处理的社交网络，那么你就应该质疑关系数据库的必要性。

图4：你真的了解 SQL 吗？数据库工程师究竟建议你做什么？

利用 MySQL Workbench 等免费工具，精心为数据库建模是成功的关键。

太多开发人员不会使用 MySQL、MariaDB、Oracle、Microsoft SQL Server 或 Postgres 的功能。他们只使用基本的 INSERT、UPDATE、SELECT 或 DELETE。但是，选择 RDBMS 也是选择 SQL，而且 SQL 和关系数据库将成为应用程序不可分割的一部分。存储过程、触发器、视图和事件的建立是有原因的。它们是为你而生的。

开发人员还经常为版本控制和管理部署或更新而苦恼。一条金科玉律是，所有 SQL 都属于 Git。从最开始的 CREATE DATABASE 和 CREATE TABLE 到最后的 TRIGGER 和 EVENT。如果很难，那很可能是你做错了。

我们每天使用的一些大型应用程序中，80% 都是关系数据库中的 SQL。这通常是 Oracle、MySQL、Postgres 或 Microsoft SQL。你这样做也没有错。一旦你真正学会了 SQL，你就会发现它的真正魅力所在。

BEGIN TRANSACTION;
  CALL say_to_reader('{"message": "Thank you for reading, Jan"}');
COMMIT;

 

阅读余下内容
|
 

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注


京ICP备12002735号