为什么 MySQL 整数比较比日期时间空比较慢?

Question

我正在使用MySQL 5.7。

有一个名为transactions的表，其中包含超过 300 万条记录。表架构如下所示：

1. id- INT(自动增量(
2. deleted_at(日期时间，允许空(
3. record_status(TINYINT，默认值为 1(
4. 与此表相关的其他列...

record_status是deleted_at的整数版本。删除记录时，该值设置为 0。还会为此列创建一个索引。

基于空的 DATETIME 查询需要740 ms才能执行：

select transactions.id from transactions where transactions.deleted_at is null

基于 TINYINT 的查询需要15.1 s才能执行：

select transactions.id from transactions where transactions.record_status = 1

对 TINYINT 列(带索引(的检查不是应该更快吗？为什么会这样？

[编辑]

添加了有关表性能的信息

为了进一步进行实验，从表中删除了所有不必要的列。只有以下情况仍然存在。

1. id- INT(自动增量(
2. deleted_at(日期时间，允许空(
3. record_status(TINYINT，默认值为 1(
4. transaction_time(日期时间(

查询 1：需要 2.3 毫秒

select transactions.id from transactions
where transactions.record_status = 1 limit 1000000;

查询 2：需要 2.1 毫秒

select transactions.id from transactions 
where transactions.deleted_at is null limit 1000000;

查询 3：需要 20 秒

select transactions.id from transactions 
where transactions.record_status = 1 
and transaction_time > '2020-04-01' limit 1000;

查询 4：需要 500 毫秒

select transactions.id from transactions 
where transactions.deleted_at is null 
and transaction_time > '2020-04-01' limit 1000;

查询 5：394ms

select transactions.id from transactions
where transaction_time > '2020-04-01' limit 1000000;

我无法弄清楚为什么查询 3 需要这么长时间。

Answer 1

此问题已通过添加复合索引得到解决。

以下两种方法现在都会导致快速性能。

1. transaction_time和deleted_at上的复合键。
2. transaction_time和record_status上的复合键。

感谢@Gordon利诺夫和@jarlh。他们的建议导致了这一发现。

Answer 2

select transactions.id from transactions 
where transactions.record_status = 1 
and transaction_time > '2020-04-01' limit 1000;

如果没有这样的复合索引，则无法优化 - 按以下顺序：

INDEX(record_status, transaction_time)

(不可以，两个单独的单列索引也不起作用。