mysql - 使用 filesort 按 MySQL 中的日期时间列排序

Question

我有一张带有日期时间 (DATE) 和位 (PUBLIC) 的 Cars 表。

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

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

但不幸的是，当我使用 explain 查看发生了什么时，我得到了这个:

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

当我只有 100 行时，获取这些数据需要 0.3 毫秒。还有其他方法可以禁用文件排序吗？

如果我转到索引，我在 (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 ;

Answer 1

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

这样，MySQL 将根据public 进行过滤并根据date 进行排序。

从你的 EXPLAIN 我看到你在 (public, date) 上没有复合索引。

相反，您在 public 和 date 上有两个不同的索引。至少，这就是它们的名称 IDX_PUBLIC 和 DATE 告诉我们的。

更新:

您的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

，我。 e.比较字符与字符，而不是整数。