外键索引疑问：两表连接时需为双方关联列建索引吗？行数是否有影响？

Great question! Let's break this down step by step, especially since you specified that the primary key won't get an automatic index (which is unusual in real-world databases, but we'll stick strictly to your assumption).

First, let's clarify the two columns' roles in the join:

• tbl1.column1: It's the primary key of tbl1, meaning it's unique and non-null, but we're assuming no index exists for it.
• tbl2.column1: It's the column we're joining on (typically acting like a foreign key in practical scenarios).

For tbl2.column1: Build the index—almost always #

When joining tbl1 and tbl2 on tbl1.column1 = tbl2.column1, the database will usually iterate through rows from one table (the "driving table") and look up matching rows in the other (the "driven table").

If tbl2 is the driven table (common if tbl1 is smaller or uses a primary key), without an index on tbl2.column1, the database has to do a full table scan of tbl2 every single time it checks a value from tbl1. That's a catastrophic performance hit once tbl2 grows beyond a tiny size (like a few hundred rows).

Even if tbl1 is the driven table, an index on tbl2.column1 lets the database quickly find matching values to join back to tbl1. So this index is non-negotiable for any non-trivial tbl2 size.

For tbl1.column1: It depends (but often worth it) #

Since tbl1.column1 is the primary key, it's unique—but without an index, the database can't quickly locate specific rows or leverage ordered data for join optimizations. Here's when it matters:

• If tbl1 is large (10k+ rows): A full table scan of tbl1 to feed the join will be slow. An index on tbl1.column1 lets the database quickly filter or retrieve only the rows needed for the join (if there's a WHERE clause) or use more efficient join algorithms like merge join (which relies on sorted data provided by indexes).
• If you frequently run queries that filter on tbl1.column1 in addition to the join: The index will speed up those filters directly, reducing the number of rows that need to be joined to tbl2.

The only time you might skip this index is if tbl1 is extremely small (a few hundred rows max)—a full scan here is fast enough that the index won't provide meaningful benefits.

Row counts are a critical factor here:

• Small tbl1 (<= 1k rows): Full table scans are negligible, so you can probably get away with only indexing tbl2.column1.
• Large tbl1 (> 10k rows): Indexing tbl1.column1 becomes valuable to avoid expensive full scans and enable better join strategies.
• Small tbl2 (<= 1k rows): You might technically get away without an index here, but it's still a good practice to add it—future growth will thank you.
• Large tbl2 (> 10k rows): The index on tbl2.column1 is mandatory. Without it, joins will grind to a halt as the database repeats full scans for every matching row from tbl1.

Just to ground this: In every major database (MySQL, PostgreSQL, SQL Server, etc.), primary keys automatically get a unique index created by default. So this scenario where a primary key has no index is extremely rare in production—but it's a great thought experiment to understand join mechanics!

内容的提问来源于stack exchange，提问作者John Olow