时间戳

首页 > 解决方案 > Mysql日期范围查询慢

mysql - Mysql日期范围查询慢

问题描述

我有 2 个 mysql 表spot_times - 10k 行和visit_times - 530 万行。

我正在尝试编写一个可以基于 10 分钟窗口加入的spot_times.spot_date查询visit_times.visit_date。

两个日期字段均已编入索引，列类型为日期时间。

我编写了以下需要数小时才能运行的 sql。

    Select spot_date, count(visit_date) total_visits
      From spot_times st 
      Left 
      Join visit_times v 
        on v.visit_date between st.spot_date and st.spot_date + interval 10 minute

    group by 1;

此查询需要数小时才能运行。

我的解释计划看起来查询没有使用索引。

解释计划

请帮忙。

标签： mysqlperformancedateindexing

解决方案

只是认为它可能对遇到相同问题的任何人有用。

我首先在按字段排序的表visit_id上添加了一个 auto_increment 列。visits_timesvisit_date

想法是获得visit_id最接近st.spot_date和st.spot_date + interval 10 minute。然后减去visit_id应该是范围之间的总访问次数。

创建了一个函数来返回visit_id一个日期和间隔。函数使用visit_date索引并循环，直到它找到一个记录，在每个循环中添加一秒。

DELIMITER //

DROP function IF EXISTS `spot_time_function` //

CREATE  function `spot_time_function`( p_datetime datetime, p_time int)
returns int
BEGIN
        
declare v_id  int ;              
declare z int;         
        
set z = 0;   
    
    time_loop:  LOOP
    
    select visit_id into v_id from visit_times where visit_date = p_datetime + interval p_time minute + interval z second limit 1;
    
            IF  v_id is not null THEN 
                LEAVE  time_loop;
            END  IF;
                
            SET  z = z + 1;
        
    END LOOP;     
    
return v_id;
    
END //

DELIMITER ;

所以最终查询看起来像。

Select 
spot_date, 
spot_time_function(spot_date,10) - spot_time_function(spot_date,0) as total_visit 
From spot_times;

上面的查询在 0.110 秒内运行。

推荐阅读