我已经搜索了很多关于下面描述的案例的解决方案，但不幸的是我没有找到类似的案例。
我有以下场景: (作为新用户，该网站拒绝了我的图片，但我可以通过邮件发送。下面是它的文本表示)
Table 1 "swap_plan" Table 2 "cell"
ClusterName | SiteID SiteID | Cell | Time | Counter
----------------------- ---------------------------------------------
Cluster A | SiteID A1 SiteID A1 | Cell A1-1 | day1 | 5
Cluster A | SiteID A2 SiteID A1 | Cell A1-1 | day2 | 3
Cluster A | SiteID A3 SiteID A1 | Cell A1-1 | day3 | 6
Cluster A | SiteID A4 SiteID A1 | Cell A1-2 | day1 | 6
Cluster A | SiteID A5 SiteID A1 | Cell A1-2 | day2 | 2
Cluster A | SiteID A6 SiteID A1 | Cell A1-2 | day3 | 9
....................... ..............................................
Cluster B | ......... ..............................................
(Where No 1) (ON Clause "SiteID") (Where No 2) Sum(Counter)
我必须显示一些性能指标(表 2“单元格”中的“计数器”)，随时间聚合(表 2“单元格”中的“时间”)和集群(表 1“swap_plan”中的“ClusterName”)。
连接是通过两个表“SiteID”的公共(public)列完成的。请注意，在表 2“单元格”中，每个 SiteID 由 3 个不同的对象(“单元格”)组成。所以，实际上我为每个单元格执行“计数器”的 SUM()。
查询如下:
SELECT ClusterName,Time,SUM(counter)
FROM cell
INNER JOIN swap_plan ON swap_plan.Siteid = cell.Siteid
WHERE ClusterName='Cluster A' AND Time>=day1 AND Time<=day2
GROUP BY Time
列类型如下:
表1“交换计划”:
- 集群名称 - CHAR(30)
- 站点 ID - VARCHAR(10)
表 2“单元格”:
- 站点 ID - VARCHAR(10)
- 时间 - 日期时间
- 计数器 - INT
“解释”显示如下:
table type key key_len ref rows Extra
swap_plan ref Index 1 30 const 31 Using where; Using index; Using temporary; Using filesort
cell ref Index_siteid 13 swap_plan.SiteID 368 Using where
使用的索引如下:
swap_plan:索引 1(1.ClusterName 和 2.SiteID)
单元格:Index_siteid (SiteID)
优化器看起来的行数很低，这很好:
swap_plan:6066 个中的 31 个和 cell:660 万个中的 368 个。
我的问题是这些“使用临时文件；使用文件排序”。据我了解，这来自 Group By 所需的排序(如果我删除它，这些过程不会根据 Explain 执行)。我发现为了避免它们，您需要在分组依据的列上有一个索引。我有一个只包含“时间”列的特殊索引，但这个索引没有被使用，即使有提示“USE INDEX FOR GROUP BY ()”。
因此，我的查询运行速度不够快 - 大约需要 15 秒(比如 15 个 SiteID 和 10 个日期)，我需要将此持续时间至少减少一半。
我的主要问题是:
- 完全有可能删除“使用临时文件；使用文件排序”或 减少执行所需的时间？ (我试图增加 读取缓冲区大小为 16MB，无影响)
- 我在 JOIN 情况下需要什么样的索引定义，在 WHERE 子句中我按不同表中的 2 列进行过滤，在 ON 子句中我按第 3 列进行过滤
- 我可以应用哪种 Group By 优化(索引等)？
非常感谢您!
请您参考如下方法:
我会这样写查询:
SELECT c.time
, SUM(c.counter)
, MAX(p.clustername) AS clustername
FROM cell c
JOIN swap_plan p
ON p.siteid = c.siteid
AND p.clustername = 'Cluster A'
WHERE c.time >= 'day1'
AND c.time <= 'day2'
GROUP
BY c.time
我肯定会在 cell 上有一个索引，以 time 作为前导列。
MySQL 可以使用相同的索引来满足范围谓词(在 WHERE 子句中)，并且无需“使用文件排序”操作即可满足 GROUP BY。
... ON cell (time)
根据列的大小，覆盖索引可能会提供最佳性能。覆盖索引包括查询中引用的表中的所有列，因此可以完全从索引页面满足查询，而无需查找基础表中的页面。
... ON cell (time, siteid, counter)
对于 swap_plan 上的索引，我有一个以 site_id 作为前导列的索引，并且包括 clustername 列，或者的:
... ON swap_plan (clustername, site_id)
或
... ON swap_plan (site_id, clustername)
看起来这两个列的组合可能会有一个 UNIQUE 约束，即 site_id 的值对于给定的 clustername 将是不同的。 (如果不是这种情况，并且相同的 (site_id,clustername) 元组出现多次，则 counter 的总和可能会膨胀。
我会寻找 EXPLAIN 输出以显示从 c.siteid 的值到 swap_plan 表的“ref”查找和 clustername 的 const(文字“Cluster A”)值。
对于大小为 31 行和 368 行的表，我们不会看到最佳执行计划和糟糕的执行计划之间的性能(运行时间)有显着差异。
当其中一个表扩展到数百万行时，差异就会变得明显。优化器对执行计划的选择受每个表的统计信息(大小、行数、列基数)的影响，因此执行计划可能会随着表大小的增加而改变。
