mysql - 加快临时表查询速度

Question

我需要加快 mysql 中需要 45 秒的查询速度，我需要计算月/年，包括表中没有结果的月份，为此我创建了一个临时日期表，然后启动下面的查询，我怎样才能加快速度？

//CREATE TABLE OF DATES AND INSERT
CREATE TEMPORARY  TABLE fechas(fecha date);

INSERT INTO fechas(fecha)
        select * from 
        (select adddate("1970-01-01",t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
         (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
         (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
         (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
         (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
         (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
    where selected_date between "2017-01-01" and "2018-01-01" 
    order by selected_date;

//查询

SELECT DATE_FORMAT(f.fecha, "%m-%Y") as data, 
        ifnull((
            SELECT ifnull(COUNT(*),0) 
            FROM cupons c 
            WHERE YEAR(c.dataCupo) = YEAR(f.fecha) 
            AND MONTH(c.dataCupo) = MONTH(f.fecha)  
            and c.empresa=1 
            AND c.proveidor!="VINCULADO"
            group by YEAR(c.dataCupo), MONTH(c.dataCupo)
            ORDER BY c.dataCupo
        ),0)  AS num 
FROM fechas f 
where f.fecha BETWEEN "2017-01-01" and "2018-01-01" 
GROUP BY YEAR(f.fecha),MONTH(f.fecha);

//结果:

数据 |数量

01-2018 | 15

02-2018 | 0

03-2018 | 20

04-2018 | 0

...

Answer 1

将列包装在函数中会阻止 MySQL 有效利用索引。

WHERE YEAR(c.dataCupo) = ... 
  AND MONTH(c.dataCupo) = ... 

这是一种允许 MySQL 在适当的索引上使用范围扫描操作的模式

WHERE c.dataCupo >=  ... 
  AND c.dataCupo <   ...

SELECT 列表中的相关子查询将被执行多次。如果我们要走这条路，那么最好先完成fechas的分组，然后每个月执行子查询，减少执行次数。

但我不会这样做，我会使用外连接操作。

我每月只能从 fechas 获取一行，并按月获取相应的计数，而不是按天获取。

在不更改临时表的情况下，如下所示:

SELECT DATE_FORMAT(f.fecha, "%m-%Y")  AS data
     , COUNT(c.dataCupo)              AS num
  FROM fechas f
  LEFT
  JOIN cupons c 
    ON c.dataCupo  >= f.fecha
   AND c.dataCuop   < f.fecha + INTERVAL 1 MONTH
   AND c.empresa    = 1
   AND c.proveidor != 'VINCULADO'
 WHERE f.fecha >= '2017-01-01'
   AND f.fecha  < '2018-01-01'
   AND DATE_FORMAT(f.fecha,'%d') = '01'
 GROUP 
    BY f.fecha

请注意，条件 DATE_FORMAT(f.fecha,'%d') = '01' 只获取该月的第一天。看起来填充临时表会为我们提供唯一的日期值，因此我们不会得到重复的值。