mysql - 针对日期组优化mysql查询

Question

这是我的表:

CREATE TABLE IF NOT EXISTS `test_dates` (
  `date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `test_log` (
  `id` int(10) unsigned NOT NULL,
  `timest` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `test_dates`
  ADD PRIMARY KEY (`date`);

ALTER TABLE `test_log`
  ADD PRIMARY KEY (`id`),
  ADD KEY `emissione` (`timest`);

我有这个查询来计算每个日期的日志:

SELECT d.date, COUNT(l.id) 
FROM test_dates d
LEFT JOIN test_log l ON l.timest>=d.date AND l.timest<d.date + INTERVAL 1 DAY 
GROUP BY d.date

表 test_dates 在日期列中索引，test_log 表在时间列中索引。

但是在解释这个查询时我得到了查询类型“ALL”和 NULL 键。

+-----+--------------+--------+-------------+--------+----------------+----------+----------+------+--------+-----------+------------------------------------------------+--+
| id  | select_type  | table  | partitions  | type   | possible_keys  |   key    | key_len  | ref  | rows   | filtered  |                     Extra                      |  |
+-----+--------------+--------+-------------+--------+----------------+----------+----------+------+--------+-----------+------------------------------------------------+--+
|  1  | SIMPLE       | d      | NULL        | index  | PRIMARY        | PRIMARY  | 3        | NULL |   705  | 100.00    | Using index                                    |  |
|  1  | SIMPLE       | l      | NULL        | ALL    | emissione      | NULL     | NULL     | NULL | 98256  | 100.00    | Range checked for each record (index map: 0x2) |  |
+-----+--------------+--------+-------------+--------+----------------+----------+----------+------+--------+-----------+------------------------------------------------+--+

为什么mysql不能使用表索引？

日志表有大约100000行，查询很慢。

Answer 1

尝试将其作为相关子查询运行:

SELECT d.date,
       (SELECT COUNT(l.id) 
        FROM log l 
        WHERE l.timest >= d.date AND l.timest < d.date + INTERVAL 1 DAY 
       ) as cnt
FROM dates d;

MySQL 在GROUP BY 中使用索引时不是很好。有时使用子查询可以显着提高性能。您的表具有正确的索引。