综合索引中的单独Join子句 [英] Separate Join clause in a Composite Index

问题描述

拥有这样的综合指数将有益于这样的事情:

Would having a Composite Index be beneficial for something like this:

SELECT * FROM a INNER JOIN b ON(a.id=b.id)
                INNER JOIN c ON(a.bar=c.id)
                INNER JOIN d ON(a.foo=d.id)

索引应为:

(a.id, a.bar, a.foo)

推荐答案

仅使用索引的前沿(a.id)，因此只有INNER JOIN到b会从索引中受益. .，因此索引中的其他列(a.bar和a.foo)对发布的示例查询没有好处.

Only the leading edge of the index would be used (a.id), so only the INNER JOIN to b would benefit from the index... so the additional columns in the index (a.bar and a.foo) are not beneficial in the sample query posted.

摘自MySql文档:

如果列不包含索引，则MySQL无法使用索引执行查找 形成索引的最左前缀.假设您有SELECT 此处显示的语句:

MySQL cannot use the index to perform lookups if the columns do not form a leftmost prefix of the index. Suppose that you have the SELECT statements shown here:

SELECT * 
FROM tbl_name 
WHERE col1=val1; 

SELECT * 
FROM tbl_name 
WHERE col1=val1 AND col2=val2;

SELECT * 
FROM tbl_name 
WHERE col2=val2; 

SELECT * 
FROM tbl_name 
WHERE col2=val2 AND col3=val3; 

如果(col1, col2, col3)上存在索引， 只有前两个查询使用索引.第三和第四查询 确实涉及索引列，但(col2)和(col2, col3)却没有 (col1, col2, col3)的最左前缀.

If an index exists on (col1, col2, col3), only the first two queries use the index. The third and fourth queries do involve indexed columns, but (col2) and (col2, col3) are not leftmost prefixes of (col1, col2, col3).

这篇关于综合索引中的单独Join子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！