在 MySQL 中使用 filesort 按日期时间列排序
我有一个带有日期时间(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技术交流群](/public/img/jiaqun_03.jpg)
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
您需要在
(public, date)
上有一个复合索引,这样,
MySQL
将按public
进行过滤并按date< 进行排序/代码>。
从您的
EXPLAIN
中,我发现您在(public, date)
上没有复合索引。相反,您在
public
和date
上有两个不同的索引。至少,它们的名称IDX_PUBLIC
和DATE
是这么说明的。更新:
您的
public
列不是BIT
,而是BINARY(1)
。它是一种字符类型并使用字符比较。当比较整数和字符时,
MySQL
将后者转换为前者,反之亦然。这些查询返回不同的结果:
将您的
public
列更改为bit
或将您的查询重写为:,即比较字符与字符,而不是整数。
You need to have a composite index on
(public, date)
This way,
MySQL
will filter onpublic
and sort ondate
.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 ondate
. At least, that's what their namesIDX_PUBLIC
andDATE
tell.Update:
You
public
column is not aBIT
, it's aBINARY(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:
Either change your
public
column to be abit
or rewrite your query as this:, i. e. compare characters with characters, not integers.
如果您按日期订购,则需要进行排序。如果没有按日期索引,则将使用文件排序。摆脱这种情况的唯一方法是添加日期索引或不执行排序依据。
此外,文件排序并不总是意味着文件将在磁盘上排序。如果表足够小或者排序缓冲区足够大,它可能会在内存中对其进行排序。这只是意味着表本身必须进行排序。
看起来您已经有一个关于 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.