mysql - 使用 `group by` 键中的函数优化查询？

Question

我使用的是MySQL 8.0，在大表上有一个慢查询需要优化。

该表包含1100万行数据及其结构:

CREATE TABLE `ccu` (
  `id` bigint NOT NULL,
  `app_id` int NOT NULL,
  `ccu` int NOT NULL,
  `audit_create` datetime NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `ccu_game_create_time_2a10bc69_idx` (`app_id`,`audit_create`) USING BTREE,
  KEY `ccu_audit_create_idx` (`audit_create`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

我的查询是:

SELECT app_id, DATE(audit_create) cal_day, MAX(ccu) pcu, ROUND(AVG(ccu)) id_acu 
FROM ccu
WHERE audit_create BETWEEN DATE_SUB(DATE(NOW()), INTERVAL 29 DAY) AND DATE(NOW())
GROUP BY app_id, DATE(audit_create)

查询运行时间超过 2 秒。我通过在...和...之间添加条件来过滤有用的数据。但是，audit_create中存储的数据格式为yyyy-MM-dd HH:mm:ss，我必须使用date函数，但是根据执行计划，只有where条件使用索引(仍然有临时表)，group by子句根本不使用任何索引。

我无权更改表结构以添加日期列。是否可以优化查询以减少查询时间？

Answer 1

我能够通过添加表达式索引来消除使用临时:

mysql> alter table ccu add key bk1 (app_id, (cast(audit_create as date)));
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain SELECT app_id, DATE(audit_create) cal_day, 
   MAX(ccu) pcu, ROUND(AVG(ccu)) id_acu  
 FROM ccu 
 WHERE date(audit_create) BETWEEN DATE_SUB(DATE(NOW()), INTERVAL 29 DAY) AND DATE(NOW()) 
 GROUP BY app_id, cast(audit_create as date)\G 
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ccu
   partitions: NULL
         type: index
possible_keys: bk1
          key: bk1
      key_len: 8
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where

不幸的是，该 EXPLAIN 报告显示它将使用 type:index，这是一种索引扫描，换句话说，它将检查 1100 万个索引条目中的每一个。它可能会使情况比您的查询中的情况更糟。

我唯一的建议是每天运行此查询一次并将结果存储在汇总表中。每天运行一次 2 秒的查询，这样您就可以快速获得聚合结果，这应该是可以接受的。但你说你没有权限添加列，所以我猜你也没有权限添加表。

在这种情况下，请购买一台速度更快、内存更大的计算机。