sql - 加快包含 300k+ 记录的 MySQL 查询

Question

我需要查找我所有产品 (sku) 的最新库存数量。
我有一个表(称为“库存”)，其中包含包含此信息的 315k+ 条记录(对于大多数 sku，每天都会添加一批新数据)。引用数据在另一个表中(称为“stockfile”)。

这是执行此操作的查询:

SELECT s1 . * , f1 . *
FROM stock s1
JOIN stockfile f1 ON ( s1.stockfileid = f1.stockfileid )
LEFT OUTER JOIN ( stock s2
JOIN stockfile f2 ON ( s2.stockfileid = f2.stockfileid )
) ON ( s1.sku = s2.sku
AND ( f1.date < f2.date
OR f1.date = f2.date
AND f1.stockfileid < f2.stockfileid) )
WHERE s2.sku IS NULL

这些是表定义

SHOW CREATE TABLE 库存:

CREATE TABLE `stock` (
 `stockid` bigint(20) NOT NULL AUTO_INCREMENT,
 `sku` char(25) NOT NULL,
 `quantity` int(5) NOT NULL,
 `creationdate` datetime NOT NULL,
 `stockfileid` smallint(5) unsigned NOT NULL,
 `touchdate` datetime NOT NULL,
 PRIMARY KEY (`stockid`),
 KEY `stock_sku` (`sku`),
 KEY `stock_stockfileid` (`stockfileid`)
) ENGINE=MyISAM AUTO_INCREMENT=316039 DEFAULT CHARSET=latin1

SHOW CREATE TABLE 库存文件:

CREATE TABLE `stockfile` (
 `stockfileid` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
 `filename` varchar(25) NOT NULL,
 `creationdate` datetime DEFAULT NULL,
 `touchdate` datetime DEFAULT NULL,
 `date` datetime DEFAULT NULL,
 `begindate` datetime DEFAULT NULL,
 `enddate` datetime DEFAULT NULL,
 PRIMARY KEY (`stockfileid`),
 KEY `stockfile_date` (`date`)
) ENGINE=MyISAM AUTO_INCREMENT=266 DEFAULT CHARSET=latin1

如果没有任何额外的索引，它需要……永远。我添加了这些，它加快了大约 250 秒:

CREATE INDEX stock_sku ON stock(sku);
CREATE INDEX stock_stockfileid ON stock(stockfileid);
CREATE INDEX stockfile_date ON stockfile(date);

这是原始查询的 EXPLAIN，带有这些索引。

id  select_type  table   type    possible_keys               key        key_len  ref                     rows   Extra
1   SIMPLE       s1      ALL    stock_stockfileid           NULL       NULL     NULL                    316038
1   SIMPLE       f1      eq_ref  PRIMARY                     PRIMARY    2        kompare.s1.stockfileid  1     
1   SIMPLE       s2      ref    stock_sku,stock_stockfileid stock_sku  25       kompare.s1.sku          12     Using where
1   SIMPLE       f2      eq_ref  PRIMARY,stockfile_date      PRIMARY    2        kompare.s2.stockfileid  1

还有其他方法可以加快速度吗？

• 感谢 Bill Karwin 解决了最初的问题!

Answer 1

我不确定我的查询是否正确，但如果可以安全地假设最大日期也有最大 stockfileid(就像你的 OR 条件一半建议的那样)也许像这样的查询会有所帮助:

SELECT s1.*, f1.*
 FROM
  stock s1 JOIN stockfile f1 USING (stockfileid)
  JOIN (
   SELECT sku, max(date) AS maxdate, max(stockfileid) AS maxfileid
   FROM stock JOIN stockfile USING (stockfileid)
   GROUP BY sku
  ) AS dfi ON (s1.sku,f1.date,f1.stockfileid)=(dfi.sku,maxdate,maxfileid);

不确定这是否是您想要的以及它是否更快，但它应该是。另一方面，如果 fileid 拥有一切，则根本不需要考虑日期。无论如何，我认为这种预过滤可能有助于作为一个起点。