sql - 两个 SELECT 和一个 SELECT 加 JOIN 的性能差异

Question

我通过网络搜索了解 JDBC 调用是如何传输到数据库管理系统的。我想知道的是，即使数据库是本地的，JDBC 调用是否会产生网络流量以便与 DBMS 通信？

我需要争论为什么两个单独的 SQL SELECT 语句比一个带有 JOIN 的语句更昂贵。

Answer 1

简单的答案是使用 JDBC 驱动程序。

The driver is platform-dependent as it makes use of ODBC which in turn depends on native libraries of the underlying operating system the JVM is running upon. Also, use of this driver leads to other installation dependencies; for example, ODBC must be installed on the computer having the driver and the database must support an ODBC driver. The use of this driver is discouraged if the alternative of a pure-Java driver is available. The other implication is that any application using a type 1 driver is non-portable given the binding between the driver and platform. This technology isn't suitable for a high-transaction environment. Type 1 drivers also don't support the complete Java command set and are limited by the functionality of the ODBC driver.

查看更多 this

I need to argue why two separeat SQL SELECT statements are more expensive than a SQL single one with JOIN.

我会说这可能取决于场景。虽然据说 JOIN 几乎总是会有更好的性能。但是您仍然会发现某些场景(尽管很少)首选 SELECT 语句。

在大多数情况下，我们可以说 Join 通常会优于多个单个 SELECTS，因为它使数据库能够进行大量优化，因为通过使用 JOIN 可以减少开销并且它知道它有多少表必须扫描。