优化日期组的mysql查询 [英] Optimize mysql query for date group

问题描述

这是我的表格：

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

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

I have this query to count logs per date:

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

table test_dates 在日期列中编制索引， test_log 表在timest列中编入索引。

table test_dates is indexed in date colum and test_log table is indexed in timest column.

但是解释此查询我得到了查询输入ALL和NULL键。

But explaining this query I got query type "ALL" and NULL key.

+-----+--------------+--------+-------------+--------+----------------+----------+----------+------+--------+-----------+------------------------------------------------+--+
| 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不能使用表索引？

Why mysql cannot use table indexes?

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

Log tables has about 100000 rows and the query is very slow.

推荐答案

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

Try running this as a correlated subquery:

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;

使用 GROUP BY 。有时使用子查询可以显着提升性能。你的表有正确的索引。

MySQL is not very good when using indexes with GROUP BY. Sometimes using a subquery can be a significant boost to performance. Your table has the correct indexes.

这篇关于优化日期组的mysql查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！