在 MySQL 中使用 filesort 按日期时间列排序

发布于 2024-08-05 21:33:47 字数 745 浏览 6 评论 0原文

我有一个带有日期时间(DATE)和位(PUBLIC)的汽车表。

现在我想获取按 DATE 和 PUBLIC = 1 排序的行,所以我使用:

select
  c.*
from
  Cars c
WHERE 
   c.PUBLIC = 1
ORDER BY 
   DATE DESC

但不幸的是,当我使用解释来查看发生了什么时,我有这个:

1   SIMPLE  a   ALL     IDX_PUBLIC,DATE     NULL    NULL    NULL    103     Using where; Using filesort

获取此数据需要 0,3 毫秒,而我只有100 行。还有其他方法可以禁用文件排序吗?

如果我转到索引,我的索引(PUBLIC,DATE)不唯一。

表定义:

CREATE TABLE IF NOT EXISTS `Cars` (
  `ID` int(11) NOT NULL auto_increment,
  `DATE` datetime NOT NULL,
  `PUBLIC` binary(1) NOT NULL default '0'
  PRIMARY KEY  (`ID`),
  KEY `IDX_PUBLIC` (`PUBLIC`),
  KEY `DATE` (`PUBLIC`,`DATE`)
) ENGINE=MyISAM  AUTO_INCREMENT=186 ;

I have a table Cars with datetime (DATE) and bit (PUBLIC).

Now i would like to take rows ordered by DATE and with PUBLIC = 1 so i use:

select
  c.*
from
  Cars c
WHERE 
   c.PUBLIC = 1
ORDER BY 
   DATE DESC

But unfortunately when I use explain to see what is going on I have this:

1   SIMPLE  a   ALL     IDX_PUBLIC,DATE     NULL    NULL    NULL    103     Using where; Using filesort

And it takes 0,3 ms to take this data while I have only 100 rows. Is there any other way to disable filesort?

If i goes to indexes I have index on (PUBLIC, DATE) not unique.

Table def:

CREATE TABLE IF NOT EXISTS `Cars` (
  `ID` int(11) NOT NULL auto_increment,
  `DATE` datetime NOT NULL,
  `PUBLIC` binary(1) NOT NULL default '0'
  PRIMARY KEY  (`ID`),
  KEY `IDX_PUBLIC` (`PUBLIC`),
  KEY `DATE` (`PUBLIC`,`DATE`)
) ENGINE=MyISAM  AUTO_INCREMENT=186 ;

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

扫码二维码加入Web技术交流群

发布评论

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

评论(2

2024-08-12 21:33:47

您需要在 (public, date) 上有一个复合索引,

这样,MySQL 将按 public 进行过滤并按 date< 进行排序/代码>。

从您的 EXPLAIN 中,我发现您在 (public, date) 上没有复合索引。

相反,您在 publicdate 上有两个不同的索引。至少,它们的名称 IDX_PUBLICDATE 是这么说明的。

更新:

您的public列不是BIT,而是BINARY(1)。它是一种字符类型并使用字符比较。

当比较整数和字符时,MySQL 将后者转换为前者,反之亦然。

这些查询返回不同的结果:

CREATE TABLE t_binary (val BINARY(2) NOT NULL);

INSERT
INTO    t_binary
VALUES
(1),
(2),
(3),
(10);

SELECT  *
FROM    t_binary
WHERE   val <= 10;

---
1
2
3
10

SELECT  *
FROM    t_binary
WHERE   val <= '10';
---
1
10

将您的 public 列更改为 bit 或将您的查询重写为:

SELECT  c.*
FROM    Cars c
WHERE   c.PUBLIC = '1'
ORDER BY 
        DATE DESC

,即比较字符与字符,而不是整数。

You need to have a composite index on (public, date)

This way, MySQL will filter on public and sort on date.

From your EXPLAIN I see that you don't have a composite index on (public, date).

Instead you have two different indexes on public and on date. At least, that's what their names IDX_PUBLIC and DATE tell.

Update:

You public column is not a BIT, it's a BINARY(1). It's a character type and uses character comparison.

When comparing integers to characters, MySQL converts the latter to the former, not vice versa.

These queries return different results:

CREATE TABLE t_binary (val BINARY(2) NOT NULL);

INSERT
INTO    t_binary
VALUES
(1),
(2),
(3),
(10);

SELECT  *
FROM    t_binary
WHERE   val <= 10;

---
1
2
3
10

SELECT  *
FROM    t_binary
WHERE   val <= '10';
---
1
10

Either change your public column to be a bit or rewrite your query as this:

SELECT  c.*
FROM    Cars c
WHERE   c.PUBLIC = '1'
ORDER BY 
        DATE DESC

, i. e. compare characters with characters, not integers.

闻呓 2024-08-12 21:33:47

如果您按日期订购,则需要进行排序。如果没有按日期索引,则将使用文件排序。摆脱这种情况的唯一方法是添加日期索引或不执行排序依据。

此外,文件排序并不总是意味着文件将在磁盘上排序。如果表足够小或者排序缓冲区足够大,它可能会在内存中对其进行排序。这只是意味着表本身必须进行排序。

看起来您已经有一个关于 date 的索引,并且由于您在 where 子句中使用 PUBLIC,MySQL 应该能够使用该索引。但是,优化器可能认为由于行数太少,因此不值得为索引而烦恼。尝试向表中添加 10,000 行左右,重新分析它,看看这是否会改变计划。

If you are ordering by date, a sort will be required. If there isn't an index by date, then a filesort will be used. The only way to get rid of that would be to either add an index on date or not do the order by.

Also, a filesort does not always imply that the file will be sorted on disk. It could be sorting it in memory if the table is small enough or the sort buffer is large enough. It just means that the table itself has to be sorted.

Looks like you have an index on date already, and since you are using PUBLIC in your where clause, MySQL should be able to use that index. However, the optimizer may have decided that since you have so few rows it isn't worth bothering with the index. Try adding 10,000 or so rows to the table, re-analyze it, and see if that changes the plan.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文