mysql - 带有 RDS 的 SELECT DISTINCT 语句的奇怪行为

问题描述

我创建了一个 2TB 的 MySQL RDS，并在其中填充了 2 个总计 1.5TB 的表：

+----------+---------------------------+------------+
| Database | Table                     | Size in MB |
+----------+---------------------------+------------+
| stam_db  | owl                       | 1182043.00 |
| stam_db  | owl_owners                |  393695.00 |

该实例设置为 db.m6g.2xlarge 大小和 6000 预置 IOPS。

我运行此查询以返回前 10 行（它们都是不同的，没有重复的行）：

SELECT  DISTINCT *
FROM    owl
ORDER BY
        name
LIMIT 10;

令我惊讶的是，这个查询已经运行了 2 个小时......更令人惊讶的是，“可用存储空间”AWS 指标开始以 2.2GB/分钟的速度下降：

由于某种原因，Write IOPS 突然上升到每秒 600-700：

读取 IOPS 甚至更高，达到每秒 1850 次：

这使总 IOPS 达到 2400-2500 左右：

CPU 利用率保持在低个位数：

我有几个问题：

为什么 SELECT DISTINCT 语句会导致对数据库进行如此大量的写入？
为什么 SELECT DISTINCT 会尝试读取整个数据库，而不仅仅是前 10 行？
为什么 RDS 不使用 6000 分配的 IOPS？总 IOPS 仅为分配量的 40% 左右。

为了将来参考，以下是答案：

Q2）我想我在https://www.percona.com/blog/2019/07/17/mysql-disk-space-exhaustion-for-implicit-temporary-tables/找到了一个解释——“需要排序阶段大部分时间都需要依赖临时表，比如当你使用GROUP BY、ORDER BY或者DISTINCT时，这样的查询分两个阶段执行：第一个是收集数据，放到一个临时表中，二是对临时表进行排序。” 因此，即使是带有 ORDER BY 的常规 SELECT 也需要重新读取整个表

Q1）大量写入是由为查询创建的临时表引起的，它们可以达到原始表的100％。

Q3) 看起来 MySQL 代码创建临时表的效率不足以利用整个 6000 IOPS

标签： mysqlamazon-web-servicesamazon-rds

尝试使用EXPLAIN来分析您的 SELECT DISTINCT 查询。我敢打赌它将包括“使用临时”和/或“使用文件排序”。如果结果集足够大，这些查询将使用临时磁盘空间。但是运行这些查询的频率越高，它使用的磁盘空间就越多。

SELECT DISTINCT *如果行已经不同，我不知道为什么要使用。这可能会导致不必要地使用临时表。

理想情况下，您的查询应该是：

SELECT *
FROM    owl
ORDER BY
        name
LIMIT 10;

确保列上有一个索引name，因此它可以通过按名称读取索引顺序中的行来跳过“使用文件排序”。

为什么不使用完整配置的 IOPS？我猜是因为 MySQL 受到构建临时表的代码的限制。它不能足够快地填充临时表以使大量 IOPS 饱和。也许如果您要在许多线程中同时运行此查询，它会。但也许不是。IMO，预置的 IOPS 几乎是一个骗局。

mysql - 带有 RDS 的 SELECT DISTINCT 语句的奇怪行为

问题描述

解决方案

推荐阅读