创建一个或多个索引？ [英] Creating One or More Indexes?

问题描述

Hello Experts，

我试图在我的某些表上创建一些索引以查看性能变化但不幸的是，我没有看到太多/任何区别。我创建的新索引仍在进行索引扫描，我期待一些索引搜索。

以下是我正在处理的表结构的示例：

 
 CREATE TABLE Invoice（
 InvoiceID INT PRIMARY KEY CLUSTERED，
 CustomerID INT NOT NULL，
 InvoiceLineID INT NOT NULL ，
价格DECIMAL（19,4），
 
 InvoiceDate DATE NOT NULL 
）; 
 
 CREATE TABLE InvoiceLine（
 InvoiceLineID INT PRIMARY KEY CLUSTERED，
 Qty INT NOT NULL 
）; 
 
 CREATE TABLE Customer（
 CustomerID INT PRIMARY KEY CLUSTERED，
 CustomerName VARCHAR（50）NOT NULL，
 CustomerAddress VARCHAR（100）NOT NULL 
） ; 
 

而且，我正在进行以下查询：

 
 SELECT i.InvoiceId，i.Price，il.Qty，c.CustomerName，c.CustomerAddress 
 FROM Invoice i INNER JOIN InvoiceLine il ON i.InvoiceLineID = il.InvoiceLineID 
 INNER JOIN客户c ON i.CustomerID = c.CustomerID 
 
在哪里i.InvoiceDate在'2018-01-01'和'2019之间-01-01'
 

因为，我正在加入InvoiceLineID和来自Invoice表的CustomerID，因为它们没有任何我认为可能导致缓慢的索引，我想在Invoice Table上创建其他索引，如下所示：

< pre class ="prettyprint lang-sql"style ="">创建NONCLUSTERED INDEX（NCI_Invoice_InvoiceLineID_CustomerID）ON [d bo]。[Invoice]
（
[InvoiceLineID]，
[CustomerID]
）INCLUDE（[InvoiceID]）;

所以，我的问题是：

1。创建非聚集索引时，是否应在索引或"INCLUDE"中提及列。 ？

2。既然，我在这里引用多个表，最好为InvoiceLineID创建一个非聚集索引，为CustomerID创建一个？

3。应该考虑列商店索引，否则？

PS：这些表有数百万条记录，并且每天都有INSERT。因此，我想确保我不会影响加载过程，同时希望快速阅读它们以用于商业智能报告。

谢谢！

---

已知是DROP，Unknown是OCEAN。

解决方案

使用索引总是一个问题你需要知道什么类型查询运行数据的结果

您可以使用查询分析器查找执行计划，并查看表中的自动静态。这将显示您查询哪些列的频率，这是索引的良好起点。其中一个目标应避免全表
扫描（通常）。

请将此标记为答案if它解决了您的问题

请投票如果它有助于解决您的问题

Hello Experts,

I was trying to create some indexes on some of my tables to see performance changes but unfortunately, I am not seeing much/any difference. The new indexes that I created are still doing Index Scans where I was expecting some Index Seeks.

Here is an example of the table structures I am dealing with:

CREATE TABLE Invoice (
InvoiceID INT PRIMARY KEY CLUSTERED,
CustomerID INT NOT NULL, 
InvoiceLineID INT NOT NULL, 
Price DECIMAL(19,4),
InvoiceDate DATE NOT NULL
);

CREATE TABLE InvoiceLine (
InvoiceLineID INT PRIMARY KEY CLUSTERED,
Qty INT NOT NULL
);

CREATE TABLE Customer(
CustomerID INT PRIMARY KEY CLUSTERED,
CustomerName VARCHAR(50) NOT NULL,
CustomerAddress VARCHAR(100) NOT NULL
);

And, I am doing the following query:

SELECT i.InvoiceId, i.Price, il.Qty, c.CustomerName, c.CustomerAddress
FROM Invoice i INNER JOIN InvoiceLine il ON i.InvoiceLineID = il.InvoiceLineID
INNER JOIN Customer c ON i.CustomerID = c.CustomerID
WHERE i.InvoiceDate BETWEEN '2018-01-01' AND '2019-01-01'

Since, I am joining on InvoiceLineID and CustomerID from the Invoice table and as they don't have any indexes which I thought might be the reason for slowness, I thought of creating additional index(es) on the Invoice Table something like this:

CREATE NONCLUSTERED INDEX (NCI_Invoice_InvoiceLineID_CustomerID) ON [dbo].[Invoice]
(
  [InvoiceLineID],
  [CustomerID]
) INCLUDE ([InvoiceID]);

So, my questions are:

1. When creating a Non-Clustered Index, should the columns be mentioned in the Index or in the "INCLUDE" ?

2. Since, I am referencing multiple tables here, is it a good idea to create a Non-Clustered index one for InvoiceLineID and one for CustomerID?

3. Should be I considering Column Store Indexs, otherwise?

PS: These tables have millions of records and have INSERTs on a daily basis. So, I wanted to make sure I am not impacting the load process and at the same time want to read them fast enough for Business Intelligence Reporting.

Thanks!

---

Known is a DROP, Unknown is an OCEAN.

解决方案

to use a index is Always a Question you Need to know what Kind of query you run for the data
you can use the query analyzer to find the execution plan and take a look also for the Auto static from the table. This will Show you which columns how often query, and this is a good start Point for a index. one of the Goals should by to avoid a full table scan (normally).

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue

这篇关于创建一个或多个索引？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！