table需要索引来提高性能

I was having timeout issue when giving long period of DateTime in below query (query runs from c# application). Table had 30 million rows with a non-clustered index on ID(not a primary key).

在下面的查询中提供长时间的DateTime时，我遇到超时问题（查询从c＃应用程序运行）。表有3000万行，ID上有非聚集索引（不是主键）。

Found that there was no primary key so I recently updated ID as Primary Key, it’s not giving me timeout now. Can anyone help me for the below query to create index on more than one key for future and also if I remove non clustered index from this table and create on more than one column? Data is increasing rapidly and need improvement on performace

发现没有主键，所以我最近更新了ID作为主键，它现在没有给我超时。任何人都可以帮助我在下面的查询中为将来创建多个键的索引，如果我从这个表中删除非聚集索引并在多个列上创建？数据正在迅速增加，需要改进性能

select 
ID, ReferenceNo, MinNo, DateTime, DataNo from tbl1
where 
DateTime BETWEEN '04/09/2013' AND '20/11/2013'
and  ReferenceNo = 4 and MinNo = 3 and DataNo = 14 Order by ID

this is the create script

这是创建脚本

CREATE TABLE [dbo].[tbl1](  [ID] [int] IDENTITY(1,1) not null,  [ReferenceNo] [int] not null,   [MinNo] [int] not null,     [DateTime] [datetime] not null,     [DataNo] [int] not null,  CONSTRAINT [tbl1_pk] PRIMARY KEY CLUSTERED  ([ID] ASC )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS 
= ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] ) ON [PRIMARY]

2 个解决方案

#1

Its hard to tell which index you should use without knowing more about your database and how its used.

在不了解您的数据库及其使用方式的情况下，很难分辨出您应该使用哪个索引。

You may want to change the ID column to a clustered index. If ID is an identity column you will get very few page splits while inserting new data. It will however require you to rebuild the table and that may be a problem depending on your usage of the database. You will be looking at some downtime.

您可能希望将ID列更改为聚簇索引。如果ID是标识列，则在插入新数据时将获得非常少的页面拆分。但是，它需要您重建表，这可能是一个问题，具体取决于您对数据库的使用。你会看到一些停机时间。

If you want a covering index it should look something like this:

如果你想要一个覆盖索引，它应该看起来像这样：

CREATE NONCLUSTERED INDEX [MyCoveringIndex] ON tbl1
(
    [ReferenceNo] ASC,
    [MinNo] ASC,
    [DataNo] ASC,
    [DateTime ] ASC
)

Its no need to include ID as a column as its already in the clusted index (clusted index columns will be included in all other indexes). This will however use up a whole lot of space (somewhere in the range of 1GB if the columns above are of the types int and datetime). It will also affect your insert, update and delete performance on the table in (most cases) a negative way.

它不需要将ID作为列包含在clusted索引中（clusted索引列将包含在所有其他索引中）。然而，这会占用大量空间（如果上面的列是int和datetime类型，则在1GB范围内）。它还会影响您在表格中的插入，更新和删除性能（大多数情况下）是否定的。

You can create the index in online mode if you are using Enterprice Edition of SQL server. In all other cases there will be a lock on the table while creating the index.

如果您使用的是SQL Server的Enterprice Edition，则可以在线模式创建索引。在所有其他情况下，在创建索引时会对表进行锁定。

Its also hard to know what other queries that are made against the table. You may want to tweek the order of the columns in the index to better match other queries.

它也很难知道针对该表做出的其他查询。您可能希望调整索引中列的顺序以更好地匹配其他查询。

#2

Indexing all fields would be fastest, but would likely waste a ton of space. I would guess that a date index would provide the most benefit with the least storage capacity cost because the data is probably evenly spread out over a large period of time. If the MIN() MAX() dates are close together, then this will not be as effective:

索引所有字段的速度最快，但可能会浪费大量空间。我猜想日期索引会以最小的存储容量成本提供最大的好处，因为数据可能在很长一段时间内均匀分布。如果MIN（）MAX（）日期靠近在一起，那么这将不会有效：

CREATE NONCLUSTERED INDEX [IDX_1] ON [dbo].[tbl1] (
    [DateTime] ASC
)
GO

As a side note, you can use SSMSE's "Display Estimated Execution Plan" which will show you what the DB needs to do to get your data. It will suggest missing indexes and also provide CREATE INDEX statements. These suggestions can be quite wasteful, but they will give you an idea of what is taking so long. This option is in the Standard Toolbar, four icons to the right from "Execute".

作为旁注，您可以使用SSMSE的“显示估计执行计划”，该计划将向您显示数据库需要做什么来获取您的数据。它将建议缺少索引并提供CREATE INDEX语句。这些建议可能非常浪费，但它们可以让您了解需要花费多长时间。此选项位于标准工具栏中，“执行”右侧有四个图标。

#1