PostgreSQL，MySQL - 冗余更新/插入/删除优化

I have a lot of SQL insert/update/delete statements of which some are redundant. For example I might have the following types of redundancy:

我有很多SQL插入/更新/删除语句,其中一些是冗余的。例如,我可能有以下类型的冗余:

INSERT INTO "foo" ("id", ...) VALUES (123, ...)
...
DELETE FROM "foo" WHERE "id" = 123

INSERT INTO "foo" ("id", "col", ...) VALUES (123, 'value', ...)
...
UPDATE "foo" SET "col" = 'other value' WHERE "id" = 123

UPDATE "foo" SET "col" = 'value' WHERE "id" = 123
...
UPDATE "foo" SET "col" = 'other value' WHERE "id" = 123

DELETE FROM "foo" WHERE "id" = 123
...
INSERT INTO "foo" ("id",  ...) VALUES (123, ...)

I might have forgotten about some other types of redundancies that are out there. Given that:

我可能已经忘记了其他一些类型的冗余。鉴于:

There are no SELECT queries run in between those insert/update/delete statements,

这些insert / update / delete语句之间没有运行SELECT查询,

The statements run in a single transaction,

语句在单个事务中运行,

The statements are sent to the database in a single API call, parsed by the database and executed together

语句在单个API调用中发送到数据库,由数据库解析并一起执行

how much sense does it make to try to remove those redundancies before sending them to the database? In other words, do databases like PostgreSQL, MySQL have mechanisms in place to remove redundant code by themselves before actually running it?

在将这些冗余发送到数据库之前尝试删除这些冗余有多大意义?换句话说,像PostgreSQL,MySQL这样的数据库是否有机制在实际运行之前自行删除冗余代码?

Important disclaimer: I'm not in control of the actual SQL code being run. I write a wrapper around an ORM API, that would have to optimize those statements automatically. However this is hard - there are a lot of things to take care of, such as foreign key and unique constraints. Obviously any optimization on the client side would have a positive effect on the database performance. However this is a complex task, and if only analoguous algorithms already run on the database end, I'd rather let them do the job.

重要免责声明:我无法控制正在运行的实际SQL代码。我编写了一个围绕ORM API的包装器,它必须自动优化这些语句。然而,这很难 - 有很多事情要处理,例如外键和唯一约束。显然,客户端的任何优化都会对数据库性能产生积极影响。然而,这是一项复杂的任务,如果只有类似的算法已在数据库端运行,我宁愿让他们完成这项工作。

Solution

I switched to PostgreSQL 9.0, where both UNIQUE and REFERENCES constraints are deferrable. In case of a database where that holds it's possible to compress an arbitrary sequence of primitive operations on one row to just a single operation (i.e. ...,DELETE, INSERT -> UPDATE). Of course as mentioned in the answer this assumes there are no triggers (which is my case).

我切换到PostgreSQL 9.0,其中UNIQUE和REFERENCES约束都可以推迟。如果数据库中存在,则可以将一行上的任意基本操作序列压缩为单个操作(即...,DELETE,INSERT - > UPDATE)。当然正如答案中所提到的,假设没有触发器(这是我的情况)。

1 个解决方案

#1

In your examples, there will be no optimizations done, the databases will behave exactly as instructed (INSERT first then DELETE).

在您的示例中,将不会进行任何优化,数据库的行为将完全按照指示执行(首先是INSERT然后是DELETE)。

SQL Server and Oracle support MERGE command which combines INSERT, UPDATE and DELETE, but it is currently supported neither by PostgreSQL nor by MySQL.

SQL Server和Oracle支持MERGE命令,它结合了INSERT,UPDATE和DELETE,但它目前既不受PostgreSQL也不受MySQL支持。

MySQL also supports INSERT … ON DUPLICATE KEY UPDATE which can help in certain cases.

MySQL还支持INSERT ... ON DUPLICATE KEY UPDATE,这在某些情况下可以提供帮助。

#1