查询性能：多表查询复合查询 [英] Query performance: Query on multiple tables Vs. Composite query

问题描述

表A有列 srno 和少数其他列。

Table A has column srno and few other columns.

表B有列 srno 和 id 。

我想获得 srno 从 srno c> 从表A开始。

I want to get srno from B for given id and then fetch out record(s) for that srno from table A.

例如，如果id为7，那么我可以通过两种方式来实现：

For example, if id is 7 then I can think of doing this by two ways:

select * from A as table_a, B as table_b where table_a.srno=table_b.srno and table_b.id=7;

并且，

select * from A where srno in (select srno from B where id=7);

两者都做同样的事。但是，当两个表中都有大量的记录时，性能明显更好呢？或者两者都有相同的性能？ （这里假设正确的索引等已经关注这两个表，我只是想这两个查询之间的性能比较）

Both are doing same. But when there are huge number of records in both the tables, performance wise which is better? Or both would have same performance? (Let's assume here that proper indexing etc has been taken care on these two tables. I just want performance comparison between these two queries)

推荐答案

p>您的第二个查询将总是较慢。 MySQL中这种动态的 IN 子句从来就不是一个好方法。

Your second query will always be slower. That type of dynamic IN clause in MySQL is never a good approach.

我的建议是使用第一个查询，但使用ANSI联接语法重写它，并选择所需的最小列集合，而不是 SELECT * 。

My recommendation would be to use the first query, but rewrite it using ANSI joins syntax and select the minimal set of columns you need, rather than doing SELECT *.

这将是一个很好的起点：

This would be a good starting point:

select table_a.* 
from A as table_a 
inner join B as table_b on table_a.srno=table_b.srno 
where table_b.id=7;

这篇关于查询性能：多表查询复合查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！