Lets say I have a simple many-to-many table between tables "table1" and "table2" that consists from two int fields: "table1-id" and "table2-id". How should I index this linking table?
假设我在表“table1”和“table2”之间有一个简单的多对多表，它包含两个int字段：“table1-id”和“table2-id”。我该如何索引此链接表？
I used to just make a composite primary index (table1-id,table2-id), but I read that this index might not work if you change order of the fields in the query. So what's the optimal solution then - make independent indexes for each field without a primary index?
我曾经只创建一个复合主索引（table1-id，table2-id），但我读到如果更改查询中字段的顺序，则此索引可能不起作用。那么什么是最佳解决方案 - 为没有主索引的每个字段制作独立索引？
Thanks.
谢谢。
3 个解决方案
#1
27
It depends on how you search.
这取决于你如何搜索。
If you search like this:
如果你这样搜索：
/* Given a value from table1, find all related values from table2 */
SELECT *
FROM table1 t1
JOIN table_table tt ON (tt.table_1 = t1.id)
JOIN table2 t2 ON (t2.id = tt.table_2)
WHERE t1.id = @id
then you need:
那么你需要：
ALTER TABLE table_table ADD CONSTRAINT pk_table1_table2 (table_1, table_2)
In this case, table1 will be leading in NESTED LOOPS and your index will be usable only when table1 is indexed first.
在这种情况下，table1将在NESTED LOOPS中领先，并且只有当table1首先被索引时，您的索引才可用。
If you search like this:
如果你这样搜索：
/* Given a value from table2, find all related values from table1 */
SELECT *
FROM table2 t2
JOIN table_table tt ON (tt.table_2 = t2.id)
JOIN table1 t1 ON (t1.id = tt.table_1)
WHERE t2.id = @id
then you need:
那么你需要：
ALTER TABLE table_table ADD CONSTRAINT pk_table1_table2 (table_2, table_1)
for the reasons above.
由于上述原因。
You don't need independent indices here. A composite index can be used everywhere where a plain index on the first column can be used. If you use independent indices, you won't be able to search efficiently for both values:
你这里不需要独立的指数。可以在任何可以使用第一列上的普通索引的地方使用复合索引。如果使用独立索引，则无法有效搜索这两个值：
/* Check if relationship exists between two given values */
SELECT 1
FROM table_table
WHERE table_1 = @id1
AND table_2 = @id2
For a query like this, you'll need at least one index on both columns.
对于这样的查询，您需要在两列上至少有一个索引。
It's never bad to have an additional index for the second field:
为第二个字段添加额外的索引永远不会是坏事：
ALTER TABLE table_table ADD CONSTRAINT pk_table1_table2 PRIMARY KEY (table_1, table_2)
CREATE INDEX ix_table2 ON table_table (table_2)
Primary key will be used for searches on both values and for searches based on value of table_1, additional index will be used for searches based on value of table_2.
主键将用于搜索两个值和基于table_1值的搜索，其他索引将用于基于table_2的值的搜索。
#2
4
As long as you are specifying both keys in the query, it doesn't matter what order they have in the query, nor does it matter what order you specify them in the index.
只要您在查询中指定两个键，它们在查询中的顺序无关紧要，也不重要在索引中指定它们的顺序。
However, it's not unlikely that you will sometimes have only one or the other of the keys. If you sometimes have id_1 only, then that should be the first (but you still only need one index).
但是，您有时不会只有一个或另一个键。如果您有时只有id_1，那么应该是第一个（但您仍然只需要一个索引）。
If you sometimes have one, sometimes the other, sometimes both, you'll need one index with both keys, and a second (non-unique) index with one field - the more selective of the two keys - and the primary composite index should start with the other key.
如果你有时有一个，有时是另一个，有时两个，你需要一个带有两个键的索引，带有一个字段的第二个（非唯一）索引 - 两个键的选择性更高 - 并且主复合索引应该从另一个键开始。
#3
0
@Quassnoi, in your first query you're actually using only tt.table_1 key as we can see from the WHERE-clause: WHERE t1.id = @id. And in the second query - only tt.table_2.
@Quassnoi，在你的第一个查询中，你实际上只使用了tt.table_1键，我们可以从WHERE子句中看到：WHERE t1.id = @id。在第二个查询中 - 只有tt.table_2。
So the multi-column index could be useful only in the third query because of WHERE table_1 = @id1 AND table_2 = @id2. If the queries of this kind are not going to be used, do you think it's worth to use two separate one-column indices instead?
因此，多列索引仅在第三个查询中有用，因为WHERE table_1 = @ id1 AND table_2 = @ id2。如果不打算使用这种类型的查询，您认为使用两个单独的单列索引是否值得？
#1
27
It depends on how you search.
这取决于你如何搜索。
If you search like this:
如果你这样搜索：
/* Given a value from table1, find all related values from table2 */
SELECT *
FROM table1 t1
JOIN table_table tt ON (tt.table_1 = t1.id)
JOIN table2 t2 ON (t2.id = tt.table_2)
WHERE t1.id = @id
then you need:
那么你需要：
ALTER TABLE table_table ADD CONSTRAINT pk_table1_table2 (table_1, table_2)
In this case, table1 will be leading in NESTED LOOPS and your index will be usable only when table1 is indexed first.
在这种情况下，table1将在NESTED LOOPS中领先，并且只有当table1首先被索引时，您的索引才可用。
If you search like this:
如果你这样搜索：
/* Given a value from table2, find all related values from table1 */
SELECT *
FROM table2 t2
JOIN table_table tt ON (tt.table_2 = t2.id)
JOIN table1 t1 ON (t1.id = tt.table_1)
WHERE t2.id = @id
then you need:
那么你需要：
ALTER TABLE table_table ADD CONSTRAINT pk_table1_table2 (table_2, table_1)
for the reasons above.
由于上述原因。
You don't need independent indices here. A composite index can be used everywhere where a plain index on the first column can be used. If you use independent indices, you won't be able to search efficiently for both values:
你这里不需要独立的指数。可以在任何可以使用第一列上的普通索引的地方使用复合索引。如果使用独立索引，则无法有效搜索这两个值：
/* Check if relationship exists between two given values */
SELECT 1
FROM table_table
WHERE table_1 = @id1
AND table_2 = @id2
For a query like this, you'll need at least one index on both columns.
对于这样的查询，您需要在两列上至少有一个索引。
It's never bad to have an additional index for the second field:
为第二个字段添加额外的索引永远不会是坏事：
ALTER TABLE table_table ADD CONSTRAINT pk_table1_table2 PRIMARY KEY (table_1, table_2)
CREATE INDEX ix_table2 ON table_table (table_2)
Primary key will be used for searches on both values and for searches based on value of table_1, additional index will be used for searches based on value of table_2.
主键将用于搜索两个值和基于table_1值的搜索，其他索引将用于基于table_2的值的搜索。
#2
4
As long as you are specifying both keys in the query, it doesn't matter what order they have in the query, nor does it matter what order you specify them in the index.
只要您在查询中指定两个键，它们在查询中的顺序无关紧要，也不重要在索引中指定它们的顺序。
However, it's not unlikely that you will sometimes have only one or the other of the keys. If you sometimes have id_1 only, then that should be the first (but you still only need one index).
但是，您有时不会只有一个或另一个键。如果您有时只有id_1，那么应该是第一个（但您仍然只需要一个索引）。
If you sometimes have one, sometimes the other, sometimes both, you'll need one index with both keys, and a second (non-unique) index with one field - the more selective of the two keys - and the primary composite index should start with the other key.
如果你有时有一个，有时是另一个，有时两个，你需要一个带有两个键的索引，带有一个字段的第二个（非唯一）索引 - 两个键的选择性更高 - 并且主复合索引应该从另一个键开始。
#3
0
@Quassnoi, in your first query you're actually using only tt.table_1 key as we can see from the WHERE-clause: WHERE t1.id = @id. And in the second query - only tt.table_2.
@Quassnoi，在你的第一个查询中，你实际上只使用了tt.table_1键，我们可以从WHERE子句中看到：WHERE t1.id = @id。在第二个查询中 - 只有tt.table_2。
So the multi-column index could be useful only in the third query because of WHERE table_1 = @id1 AND table_2 = @id2. If the queries of this kind are not going to be used, do you think it's worth to use two separate one-column indices instead?
因此，多列索引仅在第三个查询中有用，因为WHERE table_1 = @ id1 AND table_2 = @ id2。如果不打算使用这种类型的查询，您认为使用两个单独的单列索引是否值得？