如何在较大的数据库中提高SQL查询日期的速度

Question

我正在尝试优化SQL查询以提高其执行速度。我已经发现"日期"列是问题所在。当我运行此查询时，包括数据库中的日期：

SELECT p.*, preis.preis
FROM produkt p INNER JOIN (SELECT * FROM preis WHERE DATE(preis.Datum) = CURDATE()) preis ON p.id = preis.produkt_id
WHERE artikel_id IN (SELECT artikel_id FROM produkt WHERE haendler_id = 1)

需要28秒才能得到结果。但是，当我将INNER JOIN select的WHERE子句更改为id > 256981时，我会得到相同的结果，只需要0062秒。

SELECT p.*, preis.preis
FROM produkt p INNER JOIN (SELECT * FROM preis WHERE preis.id > 256981) preis ON p.id = preis.produkt_id
WHERE artikel_id IN (SELECT artikel_id FROM produkt WHERE haendler_id = 1)

这是一个很大的区别。

我已经读过一些关于INDEX和不同INDEX类型的文章，但我找不到以正确的方式对DATE字段进行INDEX的解决方案。或者有其他方法可以改进这样的查询吗？

Answer 1

SELECT  p.*, q.preis
FROM  ( SELECT artikel_id FROM produkt WHERE haendler_id = 1 ) AS a
JOIN produkt p USING(artikel_id)
INNER JOIN  
( SELECT  preis
FROM  preis
WHERE  Datum >= CURDATE()
AND Datum  < CURDATE() + INTERVAL 1 DAY
) AS q  ON p.id = q.produkt_id

并具有以下索引：

preis:  INDEX(Datum, produkt_id, preis)
preis:  INDEX(produkt_id, Datum, preis)
produkt:  INDEX(haendler_id, artikel_id)
produkt:  INDEX(artikel_id)

如果produkt只能有一个preis，那么还有另一种选择。

我们确实需要查看SHOW CREATE TABLE——以检查数据类型、PRIMARY KEY和其他内容。

Answer 2

您是否尝试过在不带日期函数的datum上直接使用不带子查询和条件的联接，如下所示：

SELECT p.*, preis.preis
FROM produkt p 
INNER JOIN preis ON p.id = preis.produkt_id
WHERE preis.Datum >= CURDATE() and preis.Datum < DATE_ADD(CURDATE(), INTERVAL 1 DAY)
AND artikel_id IN (SELECT artikel_id FROM produkt WHERE haendler_id = 1)

您可以在preis.Datum上创建索引，如下所示：

CREATE INDEX preis_indx_01 ON preis(Datum);

注意：如果经常在联接中使用produkt_id，也可以考虑在其上创建索引。

Answer 3

主要问题是，当您使用mysql时，任何像CURDATE()这样的构建函数都会使查询速度变慢，所以尽可能避免它，所以尽量像这样更新您的查询

// first make today date if your code is php then $today = date('Y-m-d');
then use this in mysql 
SELECT p.*, preis.preis
FROM produkt p INNER JOIN (SELECT * FROM preis WHERE DATE(preis.Datum) = '$today') 
preis ON p.id = preis.produkt_id
WHERE artikel_id IN (SELECT artikel_id FROM produkt WHERE haendler_id = 1)

如果您也避免日期，效果会更好

SELECT p.*, preis.preis
FROM produkt p INNER JOIN (SELECT * FROM preis WHERE preis.Datum = '$today') 
preis ON p.id = preis.produkt_id
WHERE artikel_id IN (SELECT artikel_id FROM produkt WHERE haendler_id = 1)

我认为这将帮助你

Answer 4

首先，不要使用子查询。其次，我也不推荐in。因此：

SELECT p.*, pp.preis
FROM produkt p INNER JOIN
preis pp
ON p.id = pp.produkt_id
WHERE p.Datum >= CURDATE() AND
p.Datum < CURDATE() + INTERVAL 1 DAY AND
EXISTS (SELECT 1
FROM produkt p2
WHERE p2.artikel_id = p.artikel_id AND
p2.haendler_id = 1
);

然后，对于该查询，您需要product(artikel_id, haendler_id)和preis(Datum, product_id)上的索引。

Answer 5

我相信您在id列上有一个索引，但在数据列上没有索引，在该列上定义一个索引应该会得到几乎相同的结果。