MySQL是否能够在未使用的连接中优化引用完整性?

Question

下面是两个等价的查询(因为外键约束+主键):

CREATE TABLE customers ( id int PRIMARY KEY );
CREATE TABLE orders ( id int, customer_id int );
ALTER TABLE orders ADD FOREIGN KEY (customer_id) REFERENCES customers(id);
SELECT id
, (SELECT 1 FROM customers c WHERE c.id = o.id) one
FROM orders o;
SELECT o.id
, 1 one
FROM orders o
JOIN customers c
ON c.id = o.customer_id;

在这两种情况下，orders的每一行都将在结果中有一行。

这也意味着如果所有这些查询(其中一个)都是其他查询的子查询，并且其他查询不使用one列，则可以跳过连接。

我的问题是:MySQL是否足够聪明，可以在这两种情况下跳过连接?如果我换成NULL列和LEFT JOIN列呢?

我现在正在制作一些视图来规范一些报表表。所以我需要了解是否有一些规范的格式(自行车hedding?)我应该使用这样的东西来确保我的大视图在需要时被有效地和最小限度地处理。

Answer 1

它绝对能够从子查询、cte和视图中优化掉未使用的列和查找。

它究竟做什么取决于版本和确切的DDL、查询和索引统计。在MariaDB上，ANALYZE FORMAT=JSON SELECT ...将显示实际的执行计划。在Oracle MySQL上，使用EXPLAIN ANALYZE SELECT ....

要记住的是，查询计划随着表的增长而变化(当统计数据过时时，请偶尔执行ANALYZE TABLE)。而且，实际应用程序的查询模式很难预测。

如果你的应用变得非常大，你可能想要禁用外键检查，而用好的代码来强制完整性。强制fk占用cpu时间和IO。在其他条件相同的情况下，不要依赖于FKs这样的约束条件。如果可能的话，编写不需要它们的代码。(但是，当然，如果您的代码在启用FK约束的情况下更有意义，请继续以这种方式编写。)

设计你的查询、cte、视图和所有这些，使它们易于阅读和推理。让你的应用工作起来。然后，如果你有幸拥有一个不断增长的应用程序和不断增长的表，那么每三个月左右重新审视瓶颈查询计划一次。

我设计了表、索引和约束，将它们投入生产，然后必须向我的继任者解释它们。我从来没有，哪怕一次，"我应该把这个数据库做得更复杂或更聪明"在我交接工作的时候。很多时候我都在想"我希望以前的自己能把这个设计得更简单"。

有一句口号说"过早优化是万恶之源"。这是夸大其词。但是公平地说，过早的数据库优化