mysql - 临时加快查询表
问题描述
我需要加快 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
……
解决方案
在函数中包装列将阻止 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'
是让我们仅在本月的第一天。看起来填充临时表可以获得唯一的日期值,所以我们不会得到重复。
推荐阅读
- python - Scrapy 的多个请求
- paypal - 如何使用 PayPal 订阅多个定期付款计划?
- python - 使用 for 循环与使用 .copy() 或 [:] 复制列表
- c++ - 删除的功能,不能将一条记录重写为另一条记录,对象
- xamarin.forms - 无法在 UWP 应用程序中发现蓝牙设备,但在 WPF 应用程序中工作
- react-native - 如何在 React Native 中的身份验证状态更改时更改路由
- python - PyCharm IDE:“import @”代码有效,但“from @import @”无效
- javascript - 使用 Jest 模拟节点模块时出现 TypeError
- mysql - 约束一个值在 mySQL 的同一列中最多重复 3 次
- laravel - 下载页面的视图和控制器