MySQL日历表和性能 [英] MySql calendar table and performances
问题描述
对于我正在从事的项目，我有一个带有两个日期的表，这意味着一个日期范围，并且我需要一种在两个日期之间每天乘"我的行的方法.
for a project i'm working on, i have a single table with two dates meaning a range of dates and i needed a way to "multiply" my rows for every day in between the two dates.
例如，我从2017年7月10日开始，到2017年7月14日结束 我需要在2017-07-10、2017-07-11、2017-07-12、2017-07-13上有4行
So for instance i have start 2017-07-10, end 2017-07-14 I needed to have 4 lines with 2017-07-10, 2017-07-11, 2017-07-12, 2017-07-13
为此，我在这里发现有人提到使用日历表"以及所有年份的日期.
In order to do this i found here someone mentioning using a "calendar table" with all the dates for years.
所以我建立了它，现在有两个简单的表:
So i built it, now i have these two simple tables:
CREATE TABLE `time_sample` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`start` varchar(16) DEFAULT NULL,
`end` varchar(16) DEFAULT NULL,
PRIMARY KEY (`societa_id`),
KEY `start_idx` (`start`),
KEY `end_idx` (`end`)
) ENGINE=MyISAM AUTO_INCREMENT=222 DEFAULT CHARSET=latin1;
此表包含我的日期范围，索引了开始和结束，主键是增量int. 样本行:
This table contains my date ranges, start and end are indexed, the primary key is an incremental int. Sample Row:
id start end
1 2015-05-13 2015-05-18
第二张表:
CREATE TABLE `time_dimension` (
`id` int(11) NOT NULL,
`db_date` date NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `td_dbdate_idx` (`db_date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
此日期有一个未来很多年每天的索引. 样本行:
This has a date indexed for every day for many years to come. Sample row:
id db_date
20120101 2012-01-01
现在，我加入了:
select * from time_sample s join time_dimension t on (t.db_date >= start and t.db_date < end);
这需要3毫秒.即使我的第一个表很大，此查询也总是非常快(我看到的最大值是50ms，有很多记录).
This takes 3ms. Even if my first table is HUGE, this query will always be very quick (max i've seen was 50ms with a lot of records).
我遇到的问题是在对结果进行分组时(我需要将其分组用于我的应用程序):
The issue i have is while grouping results (i need them grouped for my application):
select * from time_sample s join time_dimension t on (t.db_date >= start and t.db_date < end) group by db_date;
这花费了超过一秒钟的时间，而第一个表中的行却很少，因此急剧增加.为什么会发生这种情况，我该如何避免呢?
This takes more than one second with not so many rows in the first table, increasing dramatically. Why is this happening and how can i avoid this?
更改数据类型无济于事，只有第二个表只有一列无济于事.
Changing the data types doesn't help, having the second table with just one column doesn't help.
我可以提出建议吗，请:(
Can i have suggestions, please :(
推荐答案
我无法复制此结果...
I cannot replicate this result...
我有一个包含很多日期的日历表:calendar(dt)，其中dt是PRIMARY KEY DATE数据类型.
I have a calendar table with lots of dates: calendar(dt) where dt is a PRIMARY KEY DATE data type.
DROP TABLE IF EXISTS time_sample;
CREATE TABLE time_sample (
id int(11) NOT NULL AUTO_INCREMENT,
start date not NULL,
end date null,
PRIMARY KEY (id),
KEY (start,end)
);
INSERT INTO time_sample (start,end) VALUES ('2010-03-13','2010-05-09);
SELECT *
FROM calendar x
JOIN time_sample y
ON x.dt BETWEEN y.start AND y.end;
+------------+----+------------+------------+
| dt | id | start | end |
+------------+----+------------+------------+
| 2010-03-13 | 1 | 2010-03-13 | 2010-05-09 |
| 2010-03-14 | 1 | 2010-03-13 | 2010-05-09 |
| 2010-03-15 | 1 | 2010-03-13 | 2010-05-09 |
| 2010-03-16 | 1 | 2010-03-13 | 2010-05-09 |
...
| 2010-05-09 | 1 | 2010-03-13 | 2010-05-09 |
+------------+----+------------+------------+
58 rows in set (0.10 sec)
EXPLAIN
SELECT * FROM calendar x JOIN time_sample y ON x.dt BETWEEN y.start AND y.end;
+----+-------------+-------+--------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+------+------+--------------------------+
| 1 | SIMPLE | y | system | start | NULL | NULL | NULL | 1 | |
| 1 | SIMPLE | x | range | PRIMARY | PRIMARY | 3 | NULL | 57 | Using where; Using index |
+----+-------------+-------+--------+---------------+---------+---------+------+------+--------------------------+
2 rows in set (0.00 sec)
即使使用GROUP BY，我也在努力重现该问题.这是一个简单的COUNT ...
Even with a GROUP BY, I'm struggling to reproduce the problem. Here's a simple COUNT...
SELECT SQL_NO_CACHE dt, COUNT(1) FROM calendar x JOIN time_sample y WHERE x.dt BETWEEN y.start AND y.end GROUP BY dt ORDER BY COUNT(1) DESC LIMIT 3;
+------------+----------+
| dt | COUNT(1) |
+------------+----------+
| 2010-04-03 | 2 |
| 2010-05-05 | 2 |
| 2010-03-13 | 2 |
+------------+----------+
3 rows in set (0.36 sec)
EXPLAIN
SELECT SQL_NO_CACHE dt, COUNT(1) FROM calendar x JOIN time_sample y WHERE x.dt BETWEEN y.start AND y.end GROUP BY dt ORDER BY COUNT(1) DESC LIMIT 3;
+----+-------------+-------+-------+---------------+---------+---------+------+---------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+----------------------------------------------+
| 1 | SIMPLE | y | index | start | start | 7 | NULL | 2 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | x | index | PRIMARY | PRIMARY | 3 | NULL | 1000001 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+----------------------------------------------+
这篇关于MySQL日历表和性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
