加快具有索引的SQL查询 [英] Speeding up a SQL query with indexes

问题描述

我有一个名为 Products 的表。

此表包含超过300万个条目。每天有大约5000个新条目。

但是这个表每天晚上可能超过20 000次查询这个查询。

 选择价格
 FROM产品
 WHERE Code = @code 
 AND Company = @company 
 AND CreatedDate = @createdDate 
  

表结构：

 代码nvarchar（50）
公司nvarchar（10）
 CreatedDate datetime 
  

我可以看到，这个查询大约需要一秒钟来从 Products 表返回结果。

表中没有 productId 列，因为不需要。因此，表中没有主键。

我想以某种方式改进此查询以更快地返回结果。

我从来没有使用过索引。在这个表上使用索引的最好方法是什么？

如果我提供主键，你认为它会加快查询结果吗？请记住，我仍然需要通过提供3个参数来查询表格：

  WHERE代码= @code 
 AND Company = @company 
 AND CreatedDate = @createdDate。 
  

这是必须的。

提到表在夜间每天在2分钟内获得新条目。

如果我使用索引，哪一列是最好使用的，以及我应该使用聚集索引还是非聚集索引？

解决方案

最好的做法取决于表格具有哪些其他字段，表。

没有更多详细信息，包含price列的（代码，公司，创建日期）的非聚集索引肯定会提高性能。

  CREATE NONCLUSTERED INDEX IX_code_company_createddate 
 ON产品（代码，公司，创建日期）
 INCLUDE 
  

这是因为如果你有那个索引，那么SQL根本不会访问实际的表运行查询，因为它可以找到索引中给定code，company，createddate的所有行，并且它将能够真的快速，因为索引允许快速访问时使用定义键的字段，并且每行都有price值。

对于插入，对于添加的每一行，SQL Server也必须将它们添加到索引中，因此插入的性能将受到影响。你认为你应该期望SELECT的性能的增益超过对插入的影响，但你应该测试一下。

此外，你将使用更多的空间作为索引将存储除原始表使用的空间之外的每一行的所有这些字段。

如其他人在注释中所注意到的，向表中添加PK（即使意味着添加一个您实际上不需要的ProductId列）也是一个好主意。

I have a table called Products.

This table contains over 3 million entries. Every day there are approximately 5000 new entries. which only happens during the night in 2 minutes.

But this table gets queried every night maybe over 20 000 times with this query.

SELECT Price 
FROM Products 
WHERE Code = @code 
  AND Company = @company 
  AND CreatedDate = @createdDate

Table structure:

Code          nvarchar(50)
Company       nvarchar(10)
CreatedDate   datetime

I can see that this query takes about a second to return a result from Products table.

There is no productId column in the table as it is not needed. So there is no primary key in the table.

I would like to somehow improve this query to return the result faster.

I have never used indexes before. What would be the best way to use indexes on this table?

If I provide a primary key do you think it would speed up the query result? Keep in mind that I will still have to query the table by providing 3 parameters as

WHERE Code = @code 
  AND Company = @company 
  AND CreatedDate = @createdDate. 

This is mandatory.

As I mentioned that the table gets new entries in 2 minutes every day during the night. How would this affect the indexes?

If I use indexes, which column would be the best to use and whether I should use clustered or non-clustered indexes?

解决方案

The best thing to do would depend on what other fields the table has and what other queries run against that table.

Without more details, a non-clustered index on (code, company, createddate) that included the "price" column will certainly improve performance.

CREATE NONCLUSTERED INDEX IX_code_company_createddate
ON Products(code, company, createddate)
INCLUDE (price);

That's because if you have that index in place, then SQL will not access the actual table at all when running the query, as it can find all rows with a given "code, company, createddate" in the index and it will be able to do that really fast as the index allows precisely for fast access when using the fields that define the key, and it will also have the "price" value for each row.

Regarding the inserts, for each row added, SQL Server will have to add them to the index as well, so performance for inserts will be impacted. In think you should expect the gains on SELECT performance to outweigh the impact on the inserts, but you should test that.

Also, you will be using more space as the index will store all those fields for each row besides the space used by the original table.

As others have noted in the comments, adding a PK to your table (even if that means adding a ProductId column you don't actually need) might be a good idea as well.

这篇关于加快具有索引的SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！