SQL查询-2000万条记录-返回信息的最佳实践

Question

我有一个包含以下表格的 SQL 数据库:

Table: PhoneRecords
--------------
ID(自增长)
名字
姓氏
电话号码
邮政编码

这是一个非常简单、直接的表格。该表格有超过 2000 万记录。我正在寻找从表格中检索基于区号的记录的最佳方法。例如，下面是一个示例查询:

SELECT phonenumber, firstname
FROM [PhoneRecords]
WHERE (phone LIKE '2012042%') OR
      (phone LIKE '2012046%') OR
      (phone LIKE '2012047%') OR
      (phone LIKE '2012083%') OR
      (phone LIKE '2012088%') OR
      (phone LIKE '2012841%')

如您所见，这是一个丑陋的查询语句，但如果我没有遇到超时问题，它可以完成工作。

有人能告诉我如何以最快速度/优化的方式执行上述查询以显示结果吗？目前，上述查询大约需要在9gb 1600mhz内存、i7 930四核OC'd 4.01ghz的计算机上花费2个小时才能完成。显然，我拥有执行此类查询所需的计算机性能，但仍需要太长时间来查询。

- eqiz

---

你能提供表上索引的详细信息吗？ - Ed Harper

Answer 1

你可能在phonenumber列上缺少索引。

CREATE INDEX IX_PHONERECORDS_PHONENUMBER_FIRSTNAME 
  ON dbo.PhoneRecords (PhoneNumber) INCLUDE (FirstName)

如果这不起作用，发布执行计划 (CTRL+M)。

- Lieven Keersmaekers

---

谢谢大家的快速回复。这个电话没有被索引，但我现在正在设置它，尝试运行查询（使用Union All而不是OR）来查看它是否更快。我会发布我的发现，希望任何遇到此问题的人也能解决它。 - eqiz

@eqiz：当你发布这篇文章时，我也很想知道是否仅添加索引就足够让优化器处理你的OR查询，或者查询重写有助于优化器选择最佳计划。 - Mark Byers

如果使用 union all 比 or 子句更快（或更慢），那我会感到惊讶。 - Lieven Keersmaekers

Answer 2

首先，您需要在列phone上创建索引。如果没有，请添加。

如果查询仍然运行缓慢，您可以尝试使用UNION ALL而不是OR，因为这对优化器来说可能更容易处理。这是因为您构造条件的方式保证结果将是唯一的。因此，您的查询可以重写为：

SELECT phonenumber, firstname FROM [PhoneRecords] WHERE phone LIKE '2012042%'
UNION ALL
SELECT phonenumber, firstname FROM [PhoneRecords] WHERE phone LIKE '2012046%'
UNION ALL
SELECT phonenumber, firstname FROM [PhoneRecords] WHERE phone LIKE '2012047%'
UNION ALL
SELECT phonenumber, firstname FROM [PhoneRecords] WHERE phone LIKE '2012083%'
UNION ALL
SELECT phonenumber, firstname FROM [PhoneRecords] WHERE phone LIKE '2012088%'
UNION ALL
SELECT phonenumber, firstname FROM [PhoneRecords] WHERE phone LIKE '2012041%'

这个查询应该能够使用索引高效地运行。

在实际运行查询之前，应该查看执行计划，并确保没有表扫描或索引扫描。

- Mark Byers

---

联合等同于使用 OR 语句。SQL 查询优化器不会以任何方式使用相同的计划吗？ - uriDium

解释一下？你是不是和MySQL混淆了？ - Lieven Keersmaekers

抱歉，是的...有些混淆了！你说得对 - SQL Server没有这个功能。对于SQL Server来说，查看计划最简单的方法是在SSMS中。有一个名为“显示预估执行计划”的按钮。 - Mark Byers

Answer 3

您是否有任何索引？第一步是在PhoneNumber列上放置一个索引。如果这不够（我不知道在索引列的部分字符串搜索的确切细节），我建议添加另一个名为“AreaCode”的列，该列可以从PhoneNumber列自动计算。然后，您可以在AreaCode列上添加索引。

- Anders Abel

Answer 4

首先，一个非常显然的问题是，你是否有索引？如果你要对电话号码进行查询，那么你需要在至少电话号码上创建索引。你应该创建一个包含你想要的字段和where子句中包含的字段的覆盖索引，这样计算机就不必浪费时间在找到索引后再获取你想要的信息的行上。显然，相反的情况是，你的索引越大，查询速度就越慢。

- uriDium

Answer 5

你可以将电话号码列拆分为：[区号]，[电话号码]

然后，如果这个查询在你的应用程序中是“最重要”的，并且返回行数/总行数的比例很高，则在[区号]上添加聚集索引；否则，添加标准索引。

你也可以保留电话号码列并直接对其进行索引，具体取决于你的应用程序。

- Guillaume

Answer 6

首先，我会将电话列分成“区号”和“电话号码”。

另外，我会将这些数字转换为整数；索引会更快地执行。

AreaCode = 2012042

应该比之前快得多

PhoneNumber LIKE '2012042%'

- Miroslav Zadravec

Answer 7

即使您进行表扫描（如果选择性较低，即使有索引也可能发生），您的查询应该比2小时快得多。 如果没有其他查询扫描的表竞争，并且sqlserver max内存足够大，您的表就足够小，可以完全适合SQL服务器缓冲区池中。 因此，虽然您可以使用一些技巧，如添加索引或将电话号码拆分为区域+电话，但应调查SQL Server配置和系统配置。

- Cornel Creanga

Answer 8

http://igoro.com/archive/precomputed-view-a-cool-and-useful-sql-pattern

创建一个包含电话号码前n个数字的材料化视图作为自己的列。然后，您可以针对区号列进行查询并包括名称。预先计算区号，以便不必在每次选择时都进行计算。如果可能，请勿使用or运算符。使用union来帮助查询计划使用索引。

目前，您正在运行的查询将进行20,000,000次x比较，其中x是每次执行选择时要搜索的区号数量。通过查询精确的索引列，您将无需访问表格，而且可以以高效的方式O(log n)搜索索引。

- Josh Smeaton