MySQL:如何通过查询非常大的表(7400 万行)来加速插入和分组

Question

我正在尝试运行以下查询，这需要花费数小时。我们有一个用于查询的专用服务器(不在本地主机上运行)。

这是一个包含大约 7400 万行的 InnoDB 表。我已经为分组中涉及的两列(TRAN_URN，UCI)建立了索引，希望能加快查询速度。

insert into data.urn_uci_lookup (TRAN_URN, UCI, `Count`)
select  TRAN_URN,UCI, count(*) as `Count`
from data.diablo18
group by TRAN_URN, UCI

这是否出于某种原因效率低下？我该如何改进它？

编辑:这是解释计划

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE diablo18 ALL\N\N\N\N 74631102 使用临时；使用文件排序

干杯，卢卡斯

Answer 1

此查询将读取全部 7400 万行。它还将在新表中重新创建表的大部分内容，具体取决于您拥有的组数。

我们没有足够的关于您的服务器或数据集的信息来做很多事情，只能做出有根据的猜测。

你想查看你的 innodb 配置，特别是关于你分配给它的内存量(应该几乎是服务器可用 RAM 的全部，越多越好)减去基本开销所需的内存量，如所述在 https://dev.mysql.com/doc/refman/5.5/en/innodb-buffer-pool.html .

您的服务器 io 子系统可能是瓶颈。如果 IO 很慢，服务器可能会卡住，试图跟上此查询所需的读/写。设置高性能数据库服务器比在“专用”机器上安装 mysql 服务器要复杂得多。