MySql Join 性能 vs Where + in

Question

我在连接两个表时遇到了问题。一个 140M 行的大表，另一个是 100 行的小表，同时在主键上连接。

这两个表是:

DataTable 
{
    Date timestamp,
    Hash varchar(20),
    Type varchar(20),
    Purchases int,
    Store varchar(20),
    Primary key (Date, Hash)
}

DataTable 是一个非常大的表，有 1.4 亿行

ProductTable
{
    Hash varchar(20),
    Name varchar(20),
    Primary key (Hash)
}

ProductTable 是只有 100 行的小表

我运行了两个单独的查询

Select sum(DataTable.Purchases),DataTable.Store 
from DataTable 
   Join  ProductTable on ProductTable.Hash = DataTable.Hash  
   Where Type =2
   and Date<='2015-12-31'
   and Date>='2015-1-1'
group by DataTable.Store

这花了很长时间(实际上永无止境)。运行解释时，这表明它处理了几乎一半的表。如本解释所示:

select_type |table       |type    |possible_keys|key     | key_len | ref               |rows  |Extra       |
-----------------------------------------------------------------------------
Simple      |DataTable   |All     |Date, Hash   |Null    | Null    | Null              |7*10^7|using where
Simlpe      |ProductTable| eq_ref |PRIMARY       PRIMARY | 386     | ProductTable.Hash |   1 | Using where |

为了好玩，我从 ProductTable 中提取了所有相关的散列，并将它们放在 Where In 子句中。像下面这样:

Select sum(DataTable.Purchases),DataTable.Store
   From DataTable 
   Where DataTable.Hash in ("1ha84u","1ha850","1ha851",...,"1hl931")
   Type =2
   and DataTable.Date<='2015-12-31'
   and DataTable.Date>='2015-12-1'
   group by DataTable.Store

这带来了更好的性能 - 耗时不到 2 秒并且扫描的行数更少。

select_type |table       |type    |possible_keys|key           | key_len | ref |rows |Extra |
----------------------------------------------------------------------------
Simple      |DataTable   |range   |Date, Hash   |Date, Hash    | 62      | Null|11097|using where

我不明白为什么第一个查询没有使用主键，而第二个查询的性能却好得多。

我已经确保结果没有被 MySql 在运行之间缓存。

Answer 1

尝试在DataTable中添加索引(Hash, Date)