如何在 MySQL 中为多对多连接正确索引链接表?

假设我在表table1"和table2"之间有一个简单的多对多表，它由两个 int 字段组成:table1-id"和table2-id".我应该如何索引这个链接表?

我曾经只创建一个复合主索引 (table1-id,table2-id)，但我读到如果您更改查询中字段的顺序，该索引可能不起作用.那么最佳的解决方案是什么——在没有主索引的情况下为每个字段创建独立的索引?

谢谢.

解决方案

这取决于你的搜索方式.

如果你这样搜索:

/* 给定 table1 中的值，从 table2 中查找所有相关值 */选择 *从表 1 t1JOIN table_table tt ON (tt.table_1 = t1.id)JOIN table2 t2 ON (t2.id = tt.table_2)哪里 t1.id = @id

那么你需要:

ALTER TABLE table_table ADD CONSTRAINT pk_table1_table2 (table_1, table_2)

在这种情况下，table1 将在 NESTED LOOPS 中领先，并且您的索引仅在 table1 首先被编入索引时才可用.>

如果你这样搜索:

/* 给定 table2 中的一个值，从 table1 中查找所有相关值 */选择 *从表 2 t2JOIN table_table tt ON (tt.table_2 = t2.id)JOIN table1 t1 ON (t1.id = tt.table_1)哪里 t2.id = @id

那么你需要:

ALTER TABLE table_table ADD CONSTRAINT pk_table1_table2 (table_2, table_1)

出于上述原因.

这里不需要独立索引.可以在第一列上使用普通索引的任何地方都可以使用复合索引.如果您使用独立索引，您将无法有效地搜索这两个值:

/* 检查两个给定值之间是否存在关系 */选择 1FROM table_tableWHERE table_1 = @id1AND table_2 = @id2

对于这样的查询，两列至少需要一个索引.

为第二个字段添加一个额外的索引从来都不是坏事:

ALTER TABLE table_table ADD CONSTRAINT pk_table1_table2 PRIMARY KEY (table_1, table_2)在 table_table (table_2) 上创建索引 ix_table2

主键将用于两个值的搜索和基于table_1值的搜索，附加索引将用于基于值的搜索table_2.

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?

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?

Thanks.

解决方案

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.

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.