如何通过索引来提高我的查询性能

i just want to know how will i index the this table for optimal performance? This will potentially hold around 20M rows.

我只想知道如何索引此表以获得最佳性能?这可能会容纳大约20M行。

  CREATE TABLE [dbo].[Table1]( 
  [ID] [bigint] NOT NULL,
  [Col1] [varchar](100) NULL,
  [Col2] [varchar](100) NULL,
  [Description] [varchar](100) NULL
  ) ON [PRIMARY]

Basically, this table will be queried ONLY in this manner.

基本上,这个表只能以这种方式查询。

    SELECT ID FROM Table1
    WHERE Col1 = 'exactVal1' AND Col2 = 'exactVal2' AND [Description] = 'exactDesc'

This is what i did:

这就是我做的:

    CREATE NONCLUSTERED INDEX IX_ID
    ON Table1(ID)
    GO 

    CREATE NONCLUSTERED INDEX IX_Col1
    ON Table1(Col1)
    GO 

    CREATE NONCLUSTERED INDEX IX_Col2
    ON Table1(Col2)
    GO 

    CREATE NONCLUSTERED INDEX IX_ValueDescription
    ON Table1(ValueDescription)
    GO

Am i right to index all these columns? Not really that confident yet. Just new to SQL stuff, please let me know if im on the right track.

我是否正确索引所有这些列?还没那么自信。刚刚接触SQL的东西,如果我在正确的轨道上,请告诉我。

Again, a lot of data will be put on this table. Unfortunately, i cannot test the performance yet since there are no available data. But I will soon be generating some dummy data to test the performance. But it would be great if there is already another option(suggestion) available that i can compare the results with.

同样,很多数据都会放在这张桌子上。不幸的是,由于没有可用的数据,我无法测试性能。但我很快就会生成一些虚拟数据来测试性能。但是如果已经有另外一个选项(建议)我可以将结果与之比较,那将会很棒。

Thanks, jack

2 个解决方案

#1

I would combine these indexes into one index, instead of having three separate indexes. For example:

我会将这些索引合并为一个索引,而不是具有三个单独的索引。例如:

CREATE INDEX ix_cols ON dbo.Table1 (Col1, Col2, Description)

If this combination of columns is unique within the table, then you should add the UNIQUE keyword to make the index unique. This is for performance reasons, but, also, more importantly, to enforce uniqueness. It may also be created as a primary key if that is appropriate.

如果列的这种组合在表中是唯一的,那么您应该添加UNIQUE关键字以使索引唯一。这是出于性能原因,但更重要的是,要强制执行唯一性。如果合适,也可以将其创建为主键。

Placing all of the columns into one index will give better performance because it will not be necessary for SQL Server to use multiple passes to find the row you are seeking.

将所有列放入一个索引将提供更好的性能,因为SQL Server不需要使用多个传递来查找您正在寻找的行。

#2

Try this -

尝试这个 -

CREATE TABLE dbo.Table1 
(
      ID BIGINT NOT NULL
    , Col1 VARCHAR(100) NULL
    , Col2 VARCHAR(100) NULL
    , [Description] VARCHAR(100) NULL
)
GO

CREATE CLUSTERED INDEX IX_Table1 ON dbo.Table1 
(
      Col1
    , Col2
    , [Description]
)

Or this -

或这个 -

CREATE TABLE dbo.Table1 
(
      ID BIGINT PRIMARY KEY NOT NULL
    , Col1 VARCHAR(100) NULL
    , Col2 VARCHAR(100) NULL
    , [Description] VARCHAR(100) NULL
)
GO

CREATE UNIQUE NONCLUSTERED INDEX IX_Table1 ON dbo.Table1 
(
      Col1
    , Col2
    , [Description]
)

#1