SQL效率改进技巧小小书 XXshu

https://community.modeanalytics.com/sql/tutorial/sql-performance-tuning/

您可以控制的高级内容：

表大小：如果查询命中具有一个或多个具有数百万行或更多行的表，则可能会影响性能。
联接：如果查询以实质上增加结果集的行数的方式联接两个表，则您的查询可能会变慢。
聚合：合并多个行以产生一个结果需要比简单地检索那些行更多的计算。

您无法真正控制的事情与数据库本身有关：

其他正在运行查询的用户：在数据库上并发运行的查询越多，数据库在给定时间必须处理的次数就越多，一切运行的速度就越慢。如果其他人正在运行满足上述某些条件的资源特别密集的查询，则可能会特别糟糕。
数据库软件和优化：这可能是您无法控制的，但是如果您知道所使用的系统，则可以在其范围内工作以提高查询效率。

缩小桌子尺寸

筛选数据以仅包含所需的观察值可以大大提高查询速度。您如何执行此操作将完全取决于您要解决的问题。例如，如果您有时间序列数据，则将时间窗口限制为一个较小的时间可以使查询运行得更快：

SELECT * 
 FROM benn.sample_event_table 
 WHERE event_date >= '2014-03-01' 
 AND event_date < '2014-04-01'

请记住，您始终可以对一部分数据执行探索性分析，将工作细化为最终查询，然后消除限制并在整个数据集中运行您的工作。最终查询可能需要很长时间才能运行，但是至少您可以快速运行中间步骤。

值得注意的是， LIMIT对聚合的工作方式并不完全相同，即执行聚合，然后将结果限制为指定的行数。因此，如果您要汇总为以下一行，则LIMIT 100并不会加快查询速度：

 SELECT COUNT(*) 
 FROM benn.sample_event_table 
 LIMIT 100

如果要在执行计数之前限制数据集（以加快速度），请尝试在子查询中进行操作：

 SELECT COUNT(*) 
 FROM ( 
 SELECT * 
 FROM benn.sample_event_table 
 LIMIT 100 
 ) sub

注意：使用LIMIT会极大地改变您的结果，因此您应该使用它来测试查询逻辑，但不能获得实际结果。

通常，在处理子查询时，应确保将要处理的数据量限制在首先要执行的位置。这意味着将LIMIT放在子查询中，而不是外部查询中。同样，这是为了使查询快速运行，以便您可以进行测试，而不是产生良好的结果。

使连接变得不那么复杂

在某种程度上，这是上一个技巧的扩展。与最好在早期执行的查询中的某个位置减少数据的方式相同，最好在连接它们之前减小表的大小。以以下示例为例，该示例将有关大学运动队的信息添加到各所大学的运动员列表中：

 SELECT teams.conference AS conference, 
 players.school_name, 
 COUNT(1) AS players 
 FROM benn.college_football_players players 
 JOIN benn.college_football_teams teams 
 ON teams.school_name = players.school_name 
 GROUP BY 1,2

benn.college_football_players中有26,298行。这意味着需要评估26,298行在另一个表中是否匹配。但是，如果benn.college_football_players表已预先聚合，则可以减少benn.college_football_players中需要评估的行数。首先，让我们看一下聚合：

 SELECT players.school_name, 
 COUNT(*) AS players 
 FROM benn.college_football_players players 
 GROUP BY 1

上面的查询返回252个结果。因此，将其放在子查询中，然后在外部查询中加入该连接，将大大降低连接的成本：

 SELECT teams.conference, 
 sub.* 
 FROM ( 
 SELECT players.school_name, 
 COUNT(*) AS players 
 FROM benn.college_football_players players 
 GROUP BY 1 
 ) sub 
 JOIN benn.college_football_teams teams 
 ON teams.school_name = sub.school_name

在这种情况下，您不会注意到巨大的差异，因为30,000行对于数据库来说处理起来并不难。但是，如果您谈论的是成千上万的行或更多行，那么通过在加入之前进行聚合可以看到明显的改进。执行此操作时，请确保您所做的工作在逻辑上是一致的-在担心运行速度之前，您应该担心工作的准确性。

说明

您可以在任何（有效）查询的开头添加EXPLAIN ，以了解需要多长时间。它并不十分准确，但却是一个有用的工具。尝试运行此：

 EXPLAIN 
 SELECT * 
 FROM benn.sample_event_table 
 WHERE event_date >= '2014-03-01' 
 AND event_date < '2014-04-01' 
 LIMIT 100

您将获得此输出。它称为查询计划，它显示了查询的执行顺序：

首先执行列表底部的条目。因此，这表明将首先执行限制日期范围的WHERE子句。然后，数据库将扫描600行（这是一个近似数）。您可以在行数旁边看到成本，数字越高意味着运行时间越长。您应该更多地将此作为参考，而不是绝对的度量。为了明确起见，如果您对查询运行EXPLAIN ，修改昂贵的步骤，然后再次运行EXPLAIN以查看成本是否降低，这将非常有用。最后， LIMIT子句最后执行，并且运行起来非常便宜（ WHERE子句为24.65 vs 147.87）。