MySql日历表及性能

Question

对于我正在进行的一个项目，我有一个包含两个日期的表，这意味着一个日期范围，我需要一种方法来“乘以”我的行在两个日期之间的每一天。

例如，我有2017-07-10,2017-07-14，我需要有4行，2017-07-10,2017-07-11,2017-07-12,2017-07-13。

为了做到这一点，我在这里发现有人提到使用“日历表”与所有日期数年。

所以我建造了它，现在我有了两张简单的桌子：

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。样本行：

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;

这有一个日期索引的每一天在许多年以后。样本行：

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);

这个需要3ms。即使我的第一个表很大，这个查询也总是非常快的(我看到最多的是50 my，有很多记录)。

我遇到的问题是在对结果进行分组时(我需要将它们分组用于我的应用程序)：

select * from time_sample s join time_dimension t on (t.db_date >= start and t.db_date < end) group by db_date;

这需要超过1秒的时间，第一个表中的行数不多，并且急剧增加。为什么会发生这种事，我怎样才能避免这种情况？

更改数据类型没有帮助，只有一个列的第二个表没有帮助。

请给我一些建议：

Answer 1

我不能复制这个结果..。

我有一个有很多日期的日历表:日历( dt )，其中dt是主键日期数据类型。

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)

即使是一群人，我也很难重现这个问题。这是一个简单的数字..。

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                     |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+----------------------------------------------+