小编典典

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

mysql

可以说我在表“ table1”和“ table2”之间有一个简单的多对多表,该表由两个int字段组成:“ table1-id”和“
table2-id”。我应该如何索引此链接表?

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

谢谢。


阅读 663

收藏
2020-05-17

共1个答案

小编典典

这取决于您的搜索方式。

如果您这样搜索:

/* 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首先建立索引时,您的索引才可用。

如果您这样搜索:

/* 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)

由于上述原因。

您在这里不需要独立的索引。复合索引可以在可以使用第一列的纯索引的任何地方使用。如果使用独立索引,则将无法有效搜索这两个值:

/* Check if relationship exists between two given values */
SELECT 1
FROM table_table
WHERE table_1 = @id1
  AND table_2 = @id2

对于这样的查询,两列都至少需要一个索引。

为第二个字段添加一个附加索引从不坏:

ALTER TABLE table_table ADD CONSTRAINT pk_table1_table2 PRIMARY KEY (table_1, table_2)
CREATE INDEX ix_table2 ON table_table (table_2)

主键将用于搜索on both values和基于值的搜索table_1,附加索引将用于基于值的搜索table_2

2020-05-17