mysql - 没有全表扫描的优先级/级联日期时间

Question

SELECT IF(priority_date, priority_date, created_at) as created_at
FROM table
WHERE IF(priority_date , priority_date , created_at) 
    BETWEEN '2017-10-10 00:00:00' AND '2017-10-10 23:59:59';

在性能方面执行此查询的最佳方式是什么？

我有一个相当大的表，其中有两个日期时间。 created_at 和 priority_date。

priority_date 并不总是存在，但如果存在，它应该是查询的内容，否则它会回退到 created_at。 created_at 始终在创建行时生成。上面的查询导致(几乎)全表扫描。

初始查询的解释计划:

+------+-------------+-----------------+------+---------------+------+---------+------+--------+-------------+
| id   | select_type | table           | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+------+-------------+-----------------+------+---------------+------+---------+------+--------+-------------+
|    1 | SIMPLE      | table           | ALL  | NULL          | NULL | NULL    | NULL | 444877 | Using where |
+------+-------------+-----------------+------+---------------+------+---------+------+--------+-------------+

我还应该注意 priority_date 或 created_at 不一定都在同一行的相关时间范围内。所以做这样的事情:

WHERE priority_date BETWEEN '2017-10-10 00:00:00' AND '2017-10-10 23:59:59'
OR created_at BETWEEN '2017-10-10 00:00:00' AND '2017-10-10 23:59:59'

如果 priority_date 是 2017-10-04 23:10:43 并且 created_at 是 2017-10，可能会产生不好的结果-10 01:23:45

该表的当前行:582739

WHERE priority_date BETWEEN... 的计数:3908

WHERE created_at BETWEEN... 的计数:3437

仅在 WHERE BETWEEN 中查询的其中一列的示例说明:

+------+-------------+-----------------+-------+----------------------------------+----------------------------------+---------+------+------+-----------------------+
| id   | select_type | table           | type  | possible_keys                    | key                              | key_len | ref  | rows | Extra                 |
+------+-------------+-----------------+-------+----------------------------------+----------------------------------+---------+------+------+-----------------------+
|    1 | SIMPLE      | table           | range | table_created_at_index           | table_created_at_index           | 5       | NULL | 3436 | Using index condition |
+------+-------------+-----------------+-------+----------------------------------+----------------------------------+---------+------+------+-----------------------+

显然 IF 不是最有效的。列被索引并且各个行的解释与解释计划中它们的行数相匹配。如何在不损失性能的情况下利用优先级/回退查询？

编辑

我能想到的最好的(但是哇，是那种冗长和复制/粘贴的感觉)

SELECT IF(priority_date, priority_date, created_at) as created_at, priority_date
FROM table 
WHERE priority_date BETWEEN '2017-10-10 00:00:00' AND '2017-10-10 23:59:59'
    OR created_at BETWEEN '2017-10-10 00:00:00' AND '2017-10-10 23:59:59'
HAVING ((priority_date AND priority_date BETWEEN '2017-10-10 00:00:00' AND '2017-10-10 23:59:59')
    OR created_at BETWEEN '2017-10-10 00:00:00' AND '2017-10-10 23:59:59');

及其解释计划:

+------+-------------+-----------------+-------------+-----------------------------------------------------------------------+-----------------------------------------------------------------------+---------+------+------+------------------------------------------------------------------------------------------------------+
| id   | select_type | table           | type        | possible_keys                                                         | key                                                                   | key_len | ref  | rows | Extra                                                                                                |
+------+-------------+-----------------+-------------+-----------------------------------------------------------------------+-----------------------------------------------------------------------+---------+------+------+------------------------------------------------------------------------------------------------------+
|    1 | SIMPLE      | table           | index_merge | table_priority_date_index,table_created_at_index                      | table_priority_date_index,table_created_at_index                      | 6,5     | NULL | 7343 | Using sort_union(table_priority_date_index,table_created_at_index); Using where                      |
+------+-------------+-----------------+-------------+-----------------------------------------------------------------------+-----------------------------------------------------------------------+---------+------+------+------------------------------------------------------------------------------------------------------+

Answer 1

首先你需要一个关于 (priority_date, created_at) 的复合索引，然后你可以使用这样的查询:

SELECT IF(priority_date, priority_date, created_at) as created_at, priority_date
FROM table 
WHERE priority_date BETWEEN '2017-10-10' AND '2017-10-10 23:59:59'
  OR (priority_date IS NULL AND created_at BETWEEN '2017-10-10' AND '2017-10-10 23:59:59');

在复合索引中将 priority_date 放在首位会产生很大的不同。不需要工会。

用 2000 个结果解释 400k 行的结果:

Extra: Using where; Using index
key: priority_created_compound
rows: 2000