sql - 加速大表和小表之间的内连接

Question

这可能是一个愚蠢的问题，但它可能会阐明联接在内部是如何工作的。

假设我有一张大 table L和一张小 table S (100K 行与 100 行)。

以下两个选项在速度方面会有什么不同吗？:

OPTION 1:                 OPTION 2:
---------                 ---------
SELECT *                  SELECT *
FROM L INNER JOIN S       FROM S INNER JOIN L
ON L.id = S.id;           ON L.id = S.id;

请注意，唯一的区别是表的连接顺序。

我意识到不同 SQL 语言之间的性能可能会有所不同。如果是这样，MySQL 与 Access 相比如何？

Answer 1

不，顺序无关紧要。
几乎所有 RDBMS(例如 MS Access、MySQL、SQL Server、ORACLE 等)都使用基于列统计信息的基于成本的优化器。在大多数情况下，优化器会选择正确的计划。在您给出的示例中，顺序无关紧要(前提是统计数据是最新的)。

To decide what query strategy to use,the Jet Engine optimizer usesstatistics. The following factors aresome of the factors that thesestatistics are based on:

• The number of records in a table
• The number of data pages in a table
• The location of the table
• Whether indexes are present
• How unique the indexes are

Note: You cannot view Jet database engine optimization schemes, and youcannot specify how to optimize aquery. However, you can use theDatabase Documenter to determinewhether indexes are present and howunique an index is.

Based on these statistics, theOptimizer then selects the bestinternal query strategy for dealingwith a particular query.

The statistics are updated whenever aquery is compiled. A query is flaggedfor compiling when you save anychanges to the query (or itsunderlying tables) and when thedatabase is compacted. If a query isflagged for compiling, the compilingand the updating of statistics occursthe next time that the query is run.Compiling typically takes from onesecond to four seconds.

If you add a significant number ofrecords to your database, you mustopen and then save your queries torecompile the queries. For example, ifyou design and then test a query byusing a small set of sample data, youmust re-compile the query afteradditional records are added to thedatabase. When you do this, you wantto make sure that optimal queryperformance is achieved when yourapplication is in use.

Ref .
可能感兴趣: ACC: How to Optimize Queries in Microsoft Access 2.0, Microsoft Access 95, and Microsoft Access 97
托尼·托斯的 Microsoft Access Performance FAQ值得一读。
“加入顺序无关紧要”有一个警告。
如果您的 RDBMS 的基于成本的查询优化器在创建查询计划时超时，那么连接顺序可能很重要。基于成本的优化器具有用于构建查询计划的有限资源(CPU 时间和内存)。如果它们在编译阶段超时，您将获得迄今为止找到的最佳计划。
TLDR；如果您有收到计划编译超时(而不是查询执行超时)的复杂查询，则将最严格的连接放在首位。这样，在查询计划优化器超时时，它会增加找到“更好”计划的机会。
当然，如果您遇到查询计划编译超时，您可能应该简化您的查询。