联接索引以及位置 [英] Index on join and where

问题描述

给出下一条SQL语句:

Given the next SQL statement:

Select * 
  from A join B
           on A.id1=B.id1 and 
              A.id2=B.id2
 where A.year=2016
   and B.year=2016

并且知道表A比表B小得多，因此我需要数据库首先按年访问A表，然后联接，然后按年过滤B表，我的问题是:

and knowing table A is much smaller than table B, so I need the database first to access A table by year, then join, then filter B table by year, my question is:

在像(id1,id2,year)这样的B上创建索引以提高性能是否有意义?

does it make sense to create an index over B like (id1,id2,year) for improve performance?

非常感谢！

推荐答案

对于此查询:

Select *
from A join
     B
     on A.id1 = B.id1 and A.id2 = B.id2
where A.year = 2016 and B.year = 2016;

我建议在A(year, id1, id2)和B(id1, id2, year)上建立索引.

I would suggest indexes on A(year, id1, id2) and B(id1, id2, year).

您也可以将查询写为:

Select *
from A join
     B
     on A.id1 = B.id1 and A.id2 = B.id2 and A.year = B.year
where A.year = 2016;

您的问题的答案是是"，并且B上的索引是正确的选择.在此版本中，索引中列的顺序并不重要.

The answer to your question is "yes" and index on B is the right thing to do. In this version, the order of the columns in the index does not really matter.

这篇关于联接索引以及位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！