mysql - 加入 View 后查询性能缓慢

Question

我有 2 张 table :

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    

还有一个 View (从 NOW() 返回过去 30 天的数据):

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())

每当我运行查询时，例如:

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 秒)。

以下是查询的解释:

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    

我可以做什么来改进我的查询？为什么要花这么长时间？

Answer 1

大部分问题都在这里:

LEFT JOIN `CONSUMO` ON sub1.fecha = DATE_FORMAT(fechahora, "%M %e")

特别是:

• 不要使用LEFT；你想要所有的行，而不是多余的，对吗？因此，请使用简单的 JOIN。

• 索引fechahora。

• 不要使用DESCRIBE；它的描述性不如 SHOW CREATE TABLE。

• 而不是一遍又一遍地重新计算过去 30 天；有一个包含数年日期的长表，并使用 WHERE 子句来限制所需的行。

• 不要将 fechahora 隐藏在函数内。重新排列查询，使其看起来像

  ON fechahora >= ... sub1.fecha ...AND fechahora < ... sub1.fecha + 间隔 1 天 ...

... 需要是执行“%M %e”的逆操作所需的任何内容。您可能最好更改 last_30_days 来计算普通的 DATE 数据类型。如果/当您需要在输出中使用特定格式时，请在 SELECT 中执行此操作。