如何在MySQL中为多对多连接正确索引链接表? [英] How to properly index a linking table for many-to-many connection in MySQL?
问题描述
假设我在表table1和table2之间有一个简单的多对多表,它包含两个int字段:table1-id和table2-id。我应该如何索引这个链接表?
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-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?
谢谢。
推荐答案
这取决于你如何搜索。
如果你这样搜索:
/* 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
然后你需要:
ALTER TABLE table_table ADD CONSTRAINT pk_table1_table2 (table_1, table_2)
在这种情况下, table1
将在 NESTED LOOPS
中领先,只有在 table1
被编入索引时,您的索引才可用。
In this case, table1
will be leading in NESTED LOOPS
and your index will be usable only when table1
is indexed first.
如果你这样搜索:
/* 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
然后你需要:
ALTER TABLE table_table ADD CONSTRAINT pk_table1_table2 (table_2, table_1)
由于上述原因。
这里你不需要独立的指数。可以在任何可以使用第一列上的普通索引的地方使用复合索引。如果您使用独立索引,您将无法有效搜索这两个值:
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)
主键将用于搜索对于两个值
,对于基于 table_1
值的搜索,将根据的值使用其他索引进行搜索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
.
这篇关于如何在MySQL中为多对多连接正确索引链接表?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!