在 Mysql 查询中填补时间序列空白的最佳方法

Question

我需要填补mysql查询结果集的时间序列的空白。我正在测试使用包含时间序列的所有数据点的辅助表进行外连接的选项（如本线程所示：如何填充 MySQL 中的日期间隙？）。

我遇到的问题是，添加此联接会显着增加查询响应时间（从不到 1 秒增加到 90 秒）。

这是原始查询：

select date_format(fact_data7.date_collected,'%Y-%m') as date_col
   , date_format(fact_data7.date_collected,'%d-%H:%i:%s') as time_col
   , fact_data7.batch_id,fact_data7.value as fdvalue,entities.ticker as ticker
   , date_format(fact_data7.date_collected,'%Y-%m-%d') as date_col2
   , date_format(fact_data7.date_collected,'%Y') as year 
from fact_data7  
JOIN entities on fact_data7.entity_id=entities.id  
where (1=1)
  AND ((entities.id= 963
      AND fact_data7.metric_id=1
      ))
  AND date_format(fact_data7.date_collected,'%Y-%m') > '2008-01-01'
order by date_col asc

这是添加了辅助表 (month_fill) 的外连接的查询：

select date_format(month_fill.date,'%Y-%m') as date_col
    , date_format(fact_data7.date_collected,'%d-%H:%i:%s') as time_col
    , fact_data7.batch_id,fact_data7.value as fdvalue
    , entities.ticker as ticker
    , date_format(fact_data7.date_collected,'%Y-%m-%d') as date_col2
    , date_format(fact_data7.date_collected,'%Y') as year 
from fact_data7
JOIN entities
  on fact_data7.entity_id=entities.id  
RIGHT OUTER JOIN month_fill
   on date_format(fact_data7.date_collected,'%Y-%m') =  date_format(month_fill.date,'%Y-%m')  
where (1=1)
  AND (
      (entities.id= 963 AND fact_data7.metric_id=1)
      OR (entities.id is null and fact_data7.metric_id is null)
      )
  AND date_format(month_fill.date,'%Y-%m') > '2008-01-01'
order by date_col asc

我可以重构查询以提高性能吗？是否有替代解决方案来实现我正在寻找的功能？

11/15 更新：

这是第一个查询的 EXPLAIN 输出：

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  entities    const   PRIMARY     PRIMARY     4   const   1   Using filesort
1   SIMPLE  fact_data7  ALL     NULL    NULL    NULL    NULL    230636  Using where

这是第二个查询的 EXPLAIN 输出：

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  month_fill  index   NULL    date    8   NULL    204     Using where; Using index; Using temporary; Using filesort
1   SIMPLE  fact_data7  ALL     NULL    NULL    NULL    NULL    230636  Using where
1   SIMPLE  entities    eq_ref  PRIMARY     PRIMARY     4   findata.fact_data7.entity_id    1   Using where

Answer 1

我认为值得尝试重写 where 以避免使用 date_format(date_collected) 。你说你在这个字段上有一个索引，但它从未被使用过（字段是函数的参数，
MySQL不支持基于函数的索引）

Answer 2

即使不考虑重构查询，我也会首先在日期列fact_data7.data_collected 和month_fill.date 上添加索引。范围查询“>”您所做的是减慢进程速度，从理论上讲，添加索引应该会提高性能，但您需要足够的记录，否则管理索引只会减慢速度，因为管理索引涉及处理。

请参阅此 mysql 文档 http://dev.mysql.com/doc /refman/5.0/en/optimization-indexes.html

我不确定您到底想要实现什么，但您可以尝试使用 ifnull(value1,value2) 函数来实现mysql的。您的查询可能类似于以下内容：

select ifnull(date_format(fact_data7.date_collected,'%Y-%m'),date_format(month_fill.date,'%Y-%m')) as date_col, 
date_format(fact_data7.date_collected,'%d-%H:%i:%s') as time_col, 
fact_data7.batch_id,
fact_data7.value as fdvalue,
entities.ticker as ticker,
date_format(fact_data7.date_collected,'%Y-%m-%d') as date_col2 ,
date_format(fact_data7.date_collected,'%Y') as year 
from fact_data7 , month_fill
JOIN entities on fact_data7.entity_id=entities.id  
where ((entities.id= 963 AND fact_data7.metric_id=1) OR (entities.id is null and fact_data7.metric_id is null))
and date_format(fact_data7.date_collected,'%Y-%m') =  date_format(month_fill.date,'%Y-%m') --you will need a condition similar to this depends on the data
AND date_format(fact_data7.date_collected,'%Y-%m')>'2008-01-01'
order by date_col asc