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
在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 |
+------+-------------+-----------------+-------+----------------------------------+----------------------------------+---------+------+------+-----------------------+
显然,中频并不是最有效的.列被索引,并且各个行的解释与解释计划中的行计数匹配.如何利用优先级/后备查询而不会造成性能损失?
编辑
我能够弄清楚的最好的东西(但是哇,那是冗长和复制/粘贴的感觉)
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 |
+------+-------------+-----------------+-------------+-----------------------------------------------------------------------+-----------------------------------------------------------------------+---------+------+------+------------------------------------------------------------------------------------------------------+