带索引条件的查询中的MAX(id)函数

Question

我有张桌子

create table test
(
  id int auto_increment,
  createdAt TIMESTAMP not null,
  primary key (id)
);
create index createdAt
  on test (createdAt);

这是我的疑问

SELECT MAX(id) FROM test WHERE DATE(createdAt) <= '2020-02-07';

解释说是Using where; Using index，但是缓慢的查询日志显示1- 10+秒。

重要注意:带有createdAt < NOW() - 1 MONTH的旧行通过单独的cron作业定期删除。平均行数为100 k-10米。

据我所知，MAX()函数比较每一行，因此结果非常不稳定。

问:对于这种聚合函数是否有任何可能的优化？

Answer 1

回答问题。优化查询的最佳方法是使where子句更加精确。最好的方法是将请求拆分成两个超级快的查询。

两者都用额外的“选择优化的表”来解释。

因此，在第一个查询中，我们应该选择MAX。不是id，而是createdAt：

SELECT MAX(createdAt) FROM test WHERE createdAt <= '2020-02-07';

结果是以小时、分钟和秒为单位的精确值。例如，"2020-02-07 23:33:33“在下一个查询中，我们将对这个日期进行严格的比较，以获得最终结果：

SELECT MAX(id) FROM test WHERE createdAt = ?;

“在哪里？”是"2020-02-07 23:33:33“

或者您可以使用子查询在一个查询中执行此操作：

SELECT MAX(id) FROM test WHERE createdAt = (SELECT MAX(createdAt) FROM test WHERE createdAt <= '2020-02-07')

Answer 2

日期()，不是麦克斯()是恶棍.

DATE(createdAt) <= '2020-02-07'不是“可靠的”。

这具有等效的效果，并且可以使用created_at <= '2020-02-07'. Yes, that works for datatypesDATE,DATETIME, andTIMESTAMP`.：INDEX(created_at)：

Answer 3

您正在使用一个函数DATE()，在您的WHERE中，这防止了数据库为WHERE步骤使用索引，我猜它是为MAX()步骤使用索引。

创建带有DATE(createdAt)结果的附加列，并使用触发器更新该列；然后将索引添加到此计算查询中。

一旦您修复了索引数据，您可能会尝试看到ORDER BY id DESC LIMIT 1而不是MAX()是否会带来任何性能增益。