缓慢的查询性能导致加入视图 [英] Slow query performance left joining a view
问题描述
我有 2 张桌子:
describe CONSUMO
Field Type Null Key Default Extra
idconsumo int(11) NO PRI NULL auto_increment
idkey int(11) NO MUL NULL
ip varchar(50) NO Unknown
fechahora datetime NO NULL
describe CONTRATADO
Field Type Null Key Default Extra
idkey int(11) NO PRI NULL auto_increment
idusuario int(11) NO MUL NULL
idproducto int(11) NO MUL NULL
key varchar(64) NO MUL NULL
descripcion varchar(50) YES "API KEY"
peticiones int(11) YES NULL
caducidad datetime YES NULL
还有一个视图(从 NOW() 返回过去 30 天):
And a view (that returns the last 30 days from NOW()):
CREATE VIEW `last_30_days` AS
SELECT
DATE_FORMAT((NOW() - INTERVAL ((`units`.`i` + (`tens`.`i` * 10)) + (`hundreds`.`i` * 100)) DAY),
'%M %e') AS `fecha`,
DATE_FORMAT((NOW() - INTERVAL ((`units`.`i` + (`tens`.`i` * 10)) + (`hundreds`.`i` * 100)) DAY),
'%Y%m%d') AS `fecha_order`
FROM
((`0_9_range` `units`
JOIN `0_9_range` `tens`)
JOIN `0_9_range` `hundreds`)
WHERE
((NOW() - INTERVAL ((`units`.`i` + (`tens`.`i` * 10)) + (`hundreds`.`i` * 100)) DAY) BETWEEN (NOW() - INTERVAL 1 MONTH) AND NOW())
每当我运行查询时，例如:
Whenever I run a query, as for example:
SELECT sub1.fecha, COUNT(idconsumo) as consumo
FROM `last_30_days` `sub1`
LEFT JOIN `CONSUMO` ON sub1.fecha = DATE_FORMAT(fechahora, "%M %e")
LEFT JOIN `CONTRATADO` ON CONSUMO.idkey = CONTRATADO.idkey
WHERE idusuario IS NULL OR idusuario = 1
GROUP BY `sub1`.`fecha`
ORDER BY `sub1`.`fecha_order`
需要相当长的时间(约 20 秒).
it takes quite some time (~20 seconds).
这里是查询的解释:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived3> ALL NULL NULL NULL NULL 10 "Using temporary; Using filesort"
1 PRIMARY <derived13> ALL NULL NULL NULL NULL 10 "Using join buffer"
1 PRIMARY <derived23> ALL NULL NULL NULL NULL 10 "Using where; Using join buffer"
1 PRIMARY CONSUMO ALL NULL NULL NULL NULL 908553
1 PRIMARY CONTRATADO eq_ref PRIMARY PRIMARY 4 c1geoapi.CONSUMO.idkey 1 "Using where"
23 DERIVED NULL NULL NULL NULL NULL NULL NULL "No tables used"
24 UNION NULL NULL NULL NULL NULL NULL NULL "No tables used"
25 UNION NULL NULL NULL NULL NULL NULL NULL "No tables used"
26 UNION NULL NULL NULL NULL NULL NULL NULL "No tables used"
27 UNION NULL NULL NULL NULL NULL NULL NULL "No tables used"
28 UNION NULL NULL NULL NULL NULL NULL NULL "No tables used"
29 UNION NULL NULL NULL NULL NULL NULL NULL "No tables used"
30 UNION NULL NULL NULL NULL NULL NULL NULL "No tables used"
31 UNION NULL NULL NULL NULL NULL NULL NULL "No tables used"
32 UNION NULL NULL NULL NULL NULL NULL NULL "No tables used"
NULL "UNION RESULT" <union23,24,25,26,27,28,29,30,31,32> ALL NULL NULL NULL NULL NULL
13 DERIVED NULL NULL NULL NULL NULL NULL NULL "No tables used"
14 UNION NULL NULL NULL NULL NULL NULL NULL "No tables used"
15 UNION NULL NULL NULL NULL NULL NULL NULL "No tables used"
16 UNION NULL NULL NULL NULL NULL NULL NULL "No tables used"
17 UNION NULL NULL NULL NULL NULL NULL NULL "No tables used"
18 UNION NULL NULL NULL NULL NULL NULL NULL "No tables used"
19 UNION NULL NULL NULL NULL NULL NULL NULL "No tables used"
20 UNION NULL NULL NULL NULL NULL NULL NULL "No tables used"
21 UNION NULL NULL NULL NULL NULL NULL NULL "No tables used"
22 UNION NULL NULL NULL NULL NULL NULL NULL "No tables used"
NULL "UNION RESULT" <union13,14,15,16,17,18,19,20,21,22> ALL NULL NULL NULL NULL NULL
3 DERIVED NULL NULL NULL NULL NULL NULL NULL "No tables used"
4 UNION NULL NULL NULL NULL NULL NULL NULL "No tables used"
5 UNION NULL NULL NULL NULL NULL NULL NULL "No tables used"
6 UNION NULL NULL NULL NULL NULL NULL NULL "No tables used"
7 UNION NULL NULL NULL NULL NULL NULL NULL "No tables used"
8 UNION NULL NULL NULL NULL NULL NULL NULL "No tables used"
9 UNION NULL NULL NULL NULL NULL NULL NULL "No tables used"
10 UNION NULL NULL NULL NULL NULL NULL NULL "No tables used"
11 UNION NULL NULL NULL NULL NULL NULL NULL "No tables used"
12 UNION NULL NULL NULL NULL NULL NULL NULL "No tables used"
NULL "UNION RESULT" <union3,4,5,6,7,8,9,10,11,12> ALL NULL NULL NULL NULL NULL
我可以做些什么来改进我的查询?为什么需要这么长时间?
What can I do in order to improve my query? Why is it taking so long?
推荐答案
大部分问题都在这里:
LEFT JOIN `CONSUMO` ON sub1.fecha = DATE_FORMAT(fechahora, "%M %e")
特别是:
不要使用
LEFT；你想要所有的行，没有多余的行，对吗?所以使用普通的JOIN.
Don't use
LEFT; you want all the rows, and no extra ones, correct? So use a plainJOIN.
做索引fechahora.
不要使用DESCRIBE；它的描述性不如 SHOW CREATE TABLE.
Don't use DESCRIBE; it is less descriptive than SHOW CREATE TABLE.
而不是一遍又一遍地重新计算过去 30 天；有一个包含数年日期的长表，并使用 WHERE 子句来限制所需的行.
Rather than recomputing the last 30 days over and over; have a long table with several year's worth of dates, and use a WHERE clause to limit the desired rows.
不要在函数中隐藏 fechahora.重新排列查询，使其看起来像
Don't hide fechahora inside a function. Rearrange the query so it looks like
ON fechahora >= ... sub1.fecha ...和费查霍拉<... sub1.fecha + 间隔 1 天 ...
ON fechahora >= ... sub1.fecha ... AND fechahora < ... sub1.fecha + INTERVAL 1 DAY ...
... 必须是%M %e"的逆运算.您最好更改 last_30_days 以计算普通的 DATE 数据类型.如果/当您需要在输出中使用特定格式时，请在 SELECT 中进行.
The ... needs to be whatever it takes to do the inverse of "%M %e". You would probably be better off changing last_30_days to compute a plain DATE datatype. If/when you need a particular formatting in the output, do it in the SELECT.
这篇关于缓慢的查询性能导致加入视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
