我有一张包含大约1亿条记录的大表,其中有两个字段start_date
和end_date
,类型为DATE
。我需要检查某个日期范围内(比如2013-08-20
至2013-08-30
)重叠的记录数,因此我使用以下语句:
SELECT COUNT(*) FROM myTable WHERE end_date >= '2013-08-20'
AND start_date <= '2013-08-30'
日期列已经建立了索引。
重要的一点是,我正在搜索的日期范围总是在未来,而表中大部分记录都是过去的(大约9700万至9900万)。
因此,如果我添加一个 is_future - TINYINT
列,那么只需检查该条件,这个查询会更快吗?
SELECT COUNT(*) FROM myTable WHERE is_future = 1
AND end_date >= '2013-08-20' AND start_date <= '2013-08-30'
它将排除其余的大约9700万条记录,并仅检查剩余的100-300万条记录的日期条件?
我使用MySQL
谢谢
编辑
MySQL引擎是InnoDB,但如果是MyISAM,则会产生相当大的影响
这是创建表
CREATE TABLE `orders` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`title`
`start_date` date DEFAULT NULL,
`end_date` date DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
编辑 2@Robert Co的答案后
对于这种情况,分区看起来是个好主意,但是除非我将is_future
字段定义为主键,否则无法基于该字段创建分区,否则我应该删除我的主要主键 - id,而这是不可能的。因此,如果我将该字段定义为主键,那么分区有意义吗?如果我按照主键进行搜索,即通过is_future
字段搜索,它不会变得更快。
编辑 3 我需要使用实际查询来选择在该日期范围内有一些空闲桌子的餐厅
SELECT r.id, r.name, r.table_count
FROM restaurants r
LEFT JOIN orders o
ON r.id = o.restaurant_id
WHERE o.id IS NULL
OR (r.table_count > (SELECT COUNT(*)
FROM orders o2
WHERE o2.restaurant_id = r.id AND
end_date >= '2013-08-20' AND start_date <= '2013-08-30'
AND o2.status = 1
)
)
解决方案 经过更多的研究和测试,对于我的情况,计算行数最快的方法是只需再添加一个条件,即开始日期大于当前日期(因为搜索的日期范围总是在未来)。
SELECT COUNT(*) FROM myTable WHERE end_date >= '2013-09-01'
AND start_date >= '2013-08-20' AND start_date <= '2013-09-30'
还需要一个索引 - 带有start_date和end_date字段(感谢@symcbean)。
结果,对于包含1000万行的表,执行时间从7秒降至0.050秒。
解决方案2(@Robert Co):
在这种情况下,分区也可以起到作用!也许这比索引更好。或者两者都可以同时应用。
谢谢
BETWEEN
? - DevlshOne