为什么此SQL不使用Date类型列使用索引

发布于 2025-01-23 06:40:07 字数 874 浏览 3 评论 0原文

mySQL：5.7/8.0

表DDL

-- auto-generated definition
create table test_date_index
(
    id  int auto_increment  primary key,
    account_id int not null,
    remark  varchar(10) null,
    cal_date date  null,
    constraint cal_date_index
    unique (cal_date, account_id)
);

在这种情况下不使用索引

explain
select *
from test_date_index
where (account_id, cal_date) in (
    select account_id, max(cal_date) from test_date_index group by account_id
);

，而是在这种情况下工作，

explain
select *
from test_date_index
where (account_id, cal_date) in (
    select account_id, '2022-04-18' from test_date_index group by account_id
)

我认为这是因为cal_date列的类型，但我找不到有关此的任何文档

原文

mysql:5.7/8.0

table ddl

-- auto-generated definition
create table test_date_index
(
    id  int auto_increment  primary key,
    account_id int not null,
    remark  varchar(10) null,
    cal_date date  null,
    constraint cal_date_index
    unique (cal_date, account_id)
);

in this case not using index

explain
select *
from test_date_index
where (account_id, cal_date) in (
    select account_id, max(cal_date) from test_date_index group by account_id
);

but work in this case

explain
select *
from test_date_index
where (account_id, cal_date) in (
    select account_id, '2022-04-18' from test_date_index group by account_id
)

i think this is because of the type of the cal_date column but i can't find any doc about this

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

你在我安 2025-01-30 06:40:07

您正在使用什么版本？在5.7之前，“行构造函数”没有优化。但是，缺乏最佳指数可能是造成迟钝的主要原因。

对于第一个查询...

重写“ GroupWise-Max”查询因此：

select  b.*
    FROM (  SELECT  account_id, max(cal_date) AS cal_date
            from  test_date_index
            group by  account_id  ) AS a
    JOIN  test_date_index AS b  USING(account_id, cal_date)

获取unique索引：

PRIMARY KEY(account_id, cal_date)

以该顺序使用这些列。需要首先在我使用的“派生”查询（子查询）中有用，需要首先。而且，这往往是组织桌子的更好方法。

您的第二个查询表明，它可以使用您的向后索引，并且在您正在运行的版本中优化了“行构造器”。

What version are you using? Before 5.7, "row constructors" were not optimized. However, the lack of the optimal index may be the main cause of sluggishness.

For the first query...

Rewrite the "groupwise-max" query thus:

select  b.*
    FROM (  SELECT  account_id, max(cal_date) AS cal_date
            from  test_date_index
            group by  account_id  ) AS a
    JOIN  test_date_index AS b  USING(account_id, cal_date)

Get promote the UNIQUE index to this:

PRIMARY KEY(account_id, cal_date)

with those columns in that order. Specificaly, account_id needs to be first in order to be useful in the "derived" query (subquery) that I used. Also, it tends to be a better way to organize the table.

Your second query shows that it can use your backward index and that 'row constructors' are optimized in the version you are running.

回复收藏 0 原文

~没有更多了~