MySQL巨大表JOIN导致数据库崩溃

Question

在我最近的问题（从最后一个项目中选择信息并加入总金额）中，我在生成表格时遇到了一些内存问题。

我有两个表sales1和sales2，如下所示：

id | 日期 | 客户 | 销售额

表定义如下：

CREATE TABLE sales (
    id int auto_increment primary key, 
    dates date,
    customer int,
    sale int
);

sales1和sales2具有相同的定义，但sales2在每个字段中都有sale=-1。一个客户可能不在任何一个表中，也可能在一个或两个表中。两个表中都有约300,000条记录，并且比此处指示的字段多得多（约50个字段）。它们是InnoDB。

我想为每个客户选择：

• 购买次数
• 最后一次购买价值
• 购买总金额，当它具有正值时

我使用的查询是：

SELECT a.customer, count(a.sale), max_sale
FROM sales a
INNER JOIN (SELECT customer, sale max_sale 
        from sales x where dates = (select max(dates) 
                                    from sales y 
                                    where x.customer = y.customer
                                    and y.sale > 0
                                   )

       )b
ON a.customer = b.customer
GROUP BY a.customer, max_sale;

问题：

我需要按日期将我需要用于某些计算的结果分开：2012年的信息，2013年的信息，以及所有年份的信息。

每当我只查询一年的信息时，存储所有信息需要大约2-3分钟。

但是当我尝试收集所有年份的信息时，数据库会崩溃，并且我会收到以下消息：

InternalError: (InternalError) (1205, u'Lock wait timeout exceeded; try restarting transaction')

似乎连接如此大的表对数据库来说太过繁重。当我 explain 这个查询时，几乎所有时间的百分比都来自于 creating tmp table。

我想将数据收集分成四个季度。我们获取每个季度的结果，然后进行连接和排序。但是我想这个最终的连接和排序对于数据库来说仍然过于繁重。 因此，作为专家，您会推荐哪些优化这些查询的方法，只要我不能更改表结构？

- fedorqui

---

你是如何连接这些表的？你不会把30万行进行交叉连接吧？那将会产生900亿行... - Explosion Pills

但仍然非常棒 - DiMono

一张表中有三十万行绝对不算是巨大的。 - Will A

我们需要看到选择语句的细节；或者您可以使用解释计划来确定性能瓶颈所在。您确保在日期和客户上有索引吗？ - xQbert

Answer 1

300k 行并不是一个很大的表。我们经常看到 3 亿行的表。

你查询中最大的问题是使用了相关子查询，所以它必须为外部查询中的每一行重新执行子查询。

通常情况下，你不需要在一个 SQL 语句中完成所有工作。将其拆分成几个更简单的 SQL 语句有以下优点：

• 编码更容易。
• 更易于优化。
• 更易于调试。
• 更易于阅读。
• 实现新需求时更易于维护。

购买次数

SELECT customer, COUNT(sale) AS number_of_purchases
FROM sales 
GROUP BY customer;

针对此查询，销售（客户，销售）的索引是最佳选择。

最后购买价值

这是经常出现的最大N个元素问题。

SELECT a.customer, a.sale as max_sale
FROM sales a
LEFT OUTER JOIN sales b
 ON a.customer=b.customer AND a.dates < b.dates
WHERE b.customer IS NULL;

换句话说，尝试将行a与具有相同客户和更大日期的假想行b匹配。如果找不到这样的行，则a必须具有该客户的最大日期。

对于此查询，sales(customer,dates,sale)的索引效果最佳。

如果在该最大日期上为客户拥有多个销售记录，则此查询将返回每个客户的多个行。您需要找到另一列来打破平局。如果使用自增主键，则适合用作平局打破者，因为它保证是唯一的，并且趋向于按时间顺序增加。

SELECT a.customer, a.sale as max_sale
FROM sales a
LEFT OUTER JOIN sales b
 ON a.customer=b.customer AND (a.dates < b.dates OR a.dates = b.dates and a.id < b.id)
WHERE b.customer IS NULL;

当购买总金额为正数时的总金额

SELECT customer, SUM(sale) AS total_purchases
FROM sales
WHERE sale > 0
GROUP BY customer;

对于这个查询，销售（客户，销售）的索引是最好的选择。

您应该考虑使用NULL来表示缺失的销售值，而不是-1。SUM（）和COUNT（）等聚合函数会忽略NULL，因此您不必使用WHERE子句来排除销售小于0的行。

---

回复：你的评论

我现在有一个表，其中包含年份、季度、总销售额（关于（年份、季度）对）和销售额。我想收集有关某个时期的信息：本季度、几个季度、2011年... 信息必须分为前五名客户，即销售额更大的客户等。是否可以从总购买量大于5的客户中获取最后购买值？

2012年Q4前五大客户

SELECT customer, SUM(sale) AS total_purchases
FROM sales
WHERE (year, quarter) = (2012, 4) AND sale > 0
GROUP BY customer
ORDER BY total_purchases DESC
LIMIT 5;

我希望能够用真实数据来测试，但我相信在这个查询中，对于销售（年份、季度、客户、销售）建立索引会是最好的选择。

总购买量大于5的客户的最后一次购买

SELECT a.customer, a.sale as max_sale
FROM sales a
INNER JOIN sales c ON a.customer=c.customer
LEFT OUTER JOIN sales b
 ON a.customer=b.customer AND (a.dates < b.dates OR a.dates = b.dates and a.id < b.id)
WHERE b.customer IS NULL
GROUP BY a.id
HAVING COUNT(*) > 5;

与上面其他的最大-n-per-group查询一样，对于这个查询来说，在sales（customer，dates，sale）上建立索引是最好的。它可能无法同时优化连接和分组操作，因此会产生一个临时表。但至少它只会生成一个临时表而不是多个。

---

这些查询已经足够复杂了。您不应该尝试编写一个能够给出所有这些结果的单个SQL查询。请记住Brian Kernighan的经典语录：

“每个人都知道调试比一开始编写程序要困难两倍。如果你在编写时非常聪明，那么你如何调试它呢？”

- Bill Karwin

---

非常感谢您提供如此详尽的答案。使用索引和内部连接而不是子查询，现在的速度更快了。我现在有一个包含字段 year、quarter、total_sale（关于配对（年份，季度））和 sale 的表格。我想要收集某个时期的信息：本季度、几个季度、2011 年等等。信息必须分为最高客户、销售额较大的客户等等。是否可以获取 total_purchases 大于 5 的客户的最后购买价值？我无法在没有所有查询在一起并使用 ORDER BY total_sale LIMIT X, Y 的情况下进行操作。 - fedorqui

再次感谢您，@Bill Karwin。您的解决方案为我打开了一个新的选项世界。使用索引使查询变得更加轻便，并将结果拆分为不同的查询也有很大帮助。 - fedorqui

Answer 2

我认为您应该尝试在sales(customer, date)上添加索引。子查询可能是性能瓶颈。

- Gordon Linoff

---

非常有用！谢谢 - fedorqui

Answer 3

您可以让这个程序“尖叫”起来。抛弃整个内部连接查询。真的。这是一个几乎没有人知道的技巧。

假设dates是一个日期时间，将其转换为可排序的字符串，拼接您想要的值，最大值（或最小值），子字符串，类型转换。您可能需要调整日期转换函数（此示例适用于MS-SQL），但是这个思路在任何地方都适用：

SELECT customer, count(sale), max_sale = cast(substring(max(convert(char(19), dates, 120) + str(sale, 12, 2)), 20, 12) as numeric(12, 2))
FROM sales a 
group by customer

看这里。如果你需要更多的结果列，请这样做：

SELECT yourkey
            , maxval = left(val, N1)                  --you often won't need this
            , result1 = substring(val, N1+1, N2)
            , result2 = substring(val, N1+N2+1, N3)   --etc. for more values
FROM ( SELECT yourkey, val = max(cast(maxval as char(N1))
                               + cast(resultCol1 as char(N2))
                               + cast(resultCol2 as char(N3)) )
       FROM yourtable GROUP BY yourkey ) t

请确保除了最后一个字段外，所有字段都有固定长度。这需要一些努力来理解，但是它非常易学和可重复。它适用于任何数据库引擎，即使您拥有排名函数，它通常也会显着优于它们。更多关于这个非常普遍的挑战的信息 在此处。

- bwperrin

---

(nolock) 是 Microsoft SQL Server 的一种功能。MySQL 中不存在这样的选项。 - Bill Karwin

这是一个不错的技巧，但那个表达式相当复杂。如果你需要从最大日期所在的行中获取多列数据，你就必须为每一列编写类似的表达式，并将其转换为适当的数据类型。这看起来像是一堆脆弱且难以维护的代码。 - Bill Karwin

不要这样 - 连接每个感兴趣的列，然后使用子字符串将它们全部拆分（参见上文）。当您习惯了它时，它就变得可读了 ;-p，并且它绝不脆弱 - 它总是有效的。 - bwperrin

没有分隔符-这就是char(N)（而不是varchar(N)）和str(#, N)的用武之地（str将以前导空格填充，以便将数字按照文本的方式进行数值排序）。这种技术始终有效，并且始终是最快的方法。 - bwperrin

将变长字符串转换为CHAR(N)不会在MySQL中填充该字符串，除非您设置SQL_MODE=PAD_CHAR_TO_FULL_LENGTH。您的解决方案是疯狂的且难以维护的。 - Bill Karwin