ORDER BY [PrimaryKey] 使用临时和文件排序-解网

问：

由于排序，以下查询速度很慢（0.5 秒;使用 MySQL 5.6.21 和 MariaDB 10.4.28 进行测试），因为使用了临时表和文件排序。这是什么原因，我该如何预防？

SELECT t1.* FROM `data` AS t1 
LEFT JOIN `data_sources` AS t2 ON(t1.source_id = t2.id) 
WHERE t2.period_id = 1 
ORDER BY t1.id ASC;

解释：

1   SIMPLE  t2  ref PRIMARY,data_sources_period_id  data_sources_period_id          8   const       1   Using where; Using index; Using temporary; Using filesort
    
1   SIMPLE  t1  ref data_source_id_index          data_source_id_index              8   t2.id   40889   NULL

更新：将 SELECT t1.* 更改为 SELECT * 会导致 MySQL 和 MariaDB 上出现巨大的临时文件（20 GB+）和请求超时。我已经干净地安装了MariaDB，并将数据库作为SQL转储传输。

CREATE TABLE `data` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `source_id` bigint(20) UNSIGNED NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `data`
  ADD PRIMARY KEY (`id`),
  ADD KEY `data_source_id_index` (`source_id`);

CREATE TABLE `data_sources` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `period_id` bigint(20) UNSIGNED NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `data_sources`
  ADD PRIMARY KEY (`id`),
  ADD KEY `data_sources_period_id` (`period_id`) USING BTREE;

ALTER TABLE `data_sources`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

MySQL MariaDB 查询优化

我在本地 MySQL 8.2 上测试了您的案例（感谢您以文本形式提供可用的 DDL）。我无法重现您显示的 EXPLAIN 报告。就我而言，它首先访问，并且没有文件类型。我得出的结论是，在您的MySQL 5.6版本和我的8.2版本之间，优化器中的某些内容发生了变化。无论如何，您都应该升级，因为您使用的是不再受支持的非常旧的MySQL版本。t1

0赞 root66 11/17/2023

@PaulSpiegel 这让它变得更好，临时和文件排序都消失了。但是，“数据”表不是总是完全读取然后减少，因为 WHERE 条件只减少“data_sources”吗？这将是非常糟糕的，因为“数据”中的数量将大大增加，只有period_id才能将数量减少到可接受的水平。如果没有其他可能性，我当然也可以在数据中添加period_id，但这将是很多冗余。

1赞 Paul Spiegel 11/17/2023

@BillKarwin它取决于的基数/分离性。如果它可以帮助显著减少要排序的数据量，那么原来的执行计划可能会更好。如果没有 LIMIT 子句，则单独的 filesort 通常不是问题。另一方面，如果结果在表格的 10% 左右，表格扫描速度可能会更快。问题是没有真正好的执行计划，但无论如何你都必须选择一个。data_sources_period_id

答： 暂无答案

上一个：什么是此 SQL 查询的纯文本翻译？

下一个：使用来自同一表的 2x LEFT JOIN 优化查询

ORDER BY [PrimaryKey] 使用临时和文件排序

ORDER BY [PrimaryKey] uses temporary and filesort

评论