在数据库中搜索UPC码的最佳方法是什么？

Question

我有一个包含190万条12位UPC-A格式的条形码的UPC数据库，由于前导零，它们目前以varchar(13)的形式存储。我正在使用SQL Server 2008 R2。

我还有一个基于UPC-A条形码匹配查询数据库的WCF 4.0 API方法。

以下是需要解答的问题：

• 如何提高基于UPC的查询性能？
• 最佳的12位UPC-A条形码存储方式是什么？使用varchar(12)是否可行？

产品信息如下：

• ProductID (int)
• Barcode (varchar(12))
• Name (varchar(50))
• ImageUrl (varchar(255))

我的代码：

public JsonResult GetProductByCode(string code)
{
  DBEntities db = new DBEntities;

  Product product = (from prod in db.Products
                    where prod.Barcode == code
                    select prod).FirstOrDefault();

  return Json( product , JsonRequestBehavior.AllowGet );
}

- Max Alexander

---

当你没有展示你现在正在做什么时，很难建议性能的改进。 :) 另外，请定义“大规模”。 - Ken White

@KenWhite 这里有一些信息给你！ - Max Alexander

但是正在执行什么查询呢？ - Matthew

请问您能告诉我们在哪里可以获取类似于您所拥有的UPC数据库吗？ - App Work

有两种方法，一种是使用Solr，另一种是使用CouchDb。 - App Work

Answer 1

我认为对于条形码列，建立索引是必要的。

如果您将代码存储为数字，则可以节省空间。空间就是时间，因为读取较少的字节速度更快。此外，使用数字进行查找应该更快。当需要时，前导零可以重构，因为UPC-A是一种固定长度的代码。

- Peter G.

---

这里的问题不在于空间。总共只有1.9M行，而且它们是定长的，所以更多的开销是填充零和转换为显示格式，而不是使用字符。在搜索之前，您还需要将用户输入的条形码（字符串）转换为数字，再次增加开销。 - Ken White

如果重建前导零的时间开销是一个真正的问题，我对任何在普通硬件上进行的查找足够快都没有希望。 - Peter G.

@PeterG。我同意，如果将数字字符串转换为int在客户端是限制因素，那么这里存在严重问题。 - Matthew

@KenWhite 这里的问题不在于存储类型、大小或平台，而是查询的形式没有使用索引。尽管如此，如果你追求性能，基于 int 的查找将会比基于 varchar 的查找更快。 - Matthew

这个问题怎么演变成了一个关于性能的大讨论？我们再次强调，我们只是在谈论一个相对较小的行数和该列中非常有限的字符数量。如果你已经优化了所有其他代码，以至于这是主要的性能瓶颈，那么你真的很棒。然而，我怀疑情况并非如此。同意 - 基于 int 的查找将击败基于 char 或 varchar 的查找，即使加上额外的转换也是如此。但在这里，这并不值得花费这样的精力。（提到“空间”的是彼得，不是我。） - Ken White

Answer 2

我认为将其存储为varchar(12)可能是可以的。确保在条形码列上有索引是提高查询性能的最重要的一件事情。根据您对数据的使用，您可以考虑将其设置为聚集索引。

- Peter

---

如果你有写入操作，我不建议使用聚集索引。这将导致你整个拥有190万行的表在进行INSERT操作时重新排序，因为你没有插入连续的数据。 - Matthew

我会选择char（12），而不是varchar——如果它始终是12个字节的数据，那么您不需要每个字段的两个字节开销。确实只有两个字节，确实只有1.9M行...但它也存在于索引中，而且您关心性能，所以所有内容都要计算在内。 - Philip Kelley

@MatthewPK：我不同意你所说的“整个190万”的部分。即使UPC被指定为聚集索引，大多数插入操作可能只会导致很小一部分数据被重新排序。然而，对于我来说，使用UPC作为聚集索引有点烦人，但ɹǝʇǝd通过“根据您对数据的使用情况而定…”来保护了他的陈述。（如果数据只加载一次且从未进行插入/更新/删除操作，并且唯一可能的查询类型是整个UPC，则使用聚集索引是一个不错的选择。） - Codism

@Codism 在 本质上无序 的数据上使用聚集索引似乎不正确，除非有特定的原因需要这样做而不是使用非聚集索引。 - Matthew

也许我在提出聚集索引的想法时有些过头了... 我想象了这样一种情况，即这是大部分只读数据，UPC是自然键，ProductId字段只是一个生成的代理键，并且数据通常通过UPC（条形码扫描）进行访问。 - Peter

Answer 3

确保你的SQL搜索条件不包含函数，否则你的查询就不是可搜索的。

我猜想你的读取操作远远超过写入操作，如果数据在没有前导零的情况下是有意义的，我会承担在写入时截断它们的成本，并在精确值上进行搜索。此外，UPC-A只包含数字数据。我预计在数字数据上进行更高性能的搜索，而不是使用varchar，正如你所说，空间不是问题，所以你甚至可以存储两个值。

你还需要在该列上创建索引。

- Matthew