inner join 性能

最新推荐文章于 2025-07-17 06:10:29 发布

原创最新推荐文章于 2025-07-17 06:10:29 发布 · 2.1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#join #homework #loops #nested #access #table

database 专栏收录该内容

27 篇文章

订阅专栏

本文通过一个具体的SQL查询优化案例展示了如何通过使用不同的连接方式来提高查询效率。对比了使用逗号连接与INNER JOIN连接的区别，并通过执行计划进一步解释了性能差异的原因。

nlp_answer 数据多，26万多行，下面语句后者慢很多

select count(nlp_answer.id) from nlp_answer ,
nlp_homework where nlp_answer.homeworkid = nlp_homework.id;

select count(nlp_answer.id) from nlp_answer inner join
nlp_homework on nlp_answer.homeworkid = nlp_homework.id;

从执行计划也能看出差距：

SELECT STATEMENT CHOOSECost: 4 Bytes: 9 Cardinality: 1
4 SORT AGGREGATE Bytes: 9 Cardinality: 1
  3 NESTED LOOPS Cost: 4 Bytes: 2,292,471 Cardinality: 254,719
   1 INDEX FAST FULL SCAN NON-UNIQUE ZYK.IND_A_HID Cost: 4 Bytes: 1,018,876 Cardinality: 254,719
   2 INDEX UNIQUE SCAN UNIQUE ZYK.SYS_C003943 Bytes: 5 Cardinality: 1

SELECT STATEMENT CHOOSECost: 29 Bytes: 13 Cardinality: 1
5 SORT AGGREGATE Bytes: 13 Cardinality: 1
  4 TABLE ACCESS BY INDEX ROWID ZYK.NLP_ANSWER Cost: 2 Bytes: 145,552 Cardinality: 18,194
   3 NESTED LOOPS Cost: 29 Bytes: 3,311,347 Cardinality: 254,719
    1 INDEX FULL SCAN UNIQUE ZYK.SYS_C003943 Cost: 1 Bytes: 70 Cardinality: 14
    2 INDEX RANGE SCAN NON-UNIQUE ZYK.IND_A_HID Cost: 1 Cardinality: 36,388