Need to improve performance query a table with mil

问题:

This query on an audit trail table starts slowing down once the load increases in performance testing (reads and inserts) and I've done what I can with indexing. With this query and these tables and indexes, what more can I do?

CREATE TABLE [dbo].[mod2] (
  [id] [int] IDENTITY,
  [userId] [int] NOT NULL,
  [epochTime] [bigint] NOT NULL,
  [forecastId] [int] NOT NULL,
  [description] [char](12) NOT NULL,
  [auxText] [text] NULL,
  [auxDate] [date] NULL
);


ALTER TABLE [dbo].[mod2] ADD CONSTRAINT PK_mod2 PRIMARY KEY(ID);

ALTER TABLE [dbo].[mod2]  WITH CHECK
    ADD CONSTRAINT [FK_mod2_forecastId] FOREIGN KEY([forecastId])
    REFERENCES [dbo].[forecast] ([id]);

ALTER TABLE [dbo].[mod2] CHECK CONSTRAINT [FK_mod2_forecastId];

ALTER TABLE [dbo].[mod2]  WITH CHECK
    ADD CONSTRAINT [FK_mod2_userId] FOREIGN KEY([userId])
    REFERENCES [dbo].[user] ([id]);

ALTER TABLE [dbo].[mod2] CHECK CONSTRAINT [FK_mod2_userId];

CREATE NONCLUSTERED INDEX IX_modification_auxDate ON [dbo].[mod2] (auxDate ASC);

CREATE NONCLUSTERED INDEX IX_modification_epochTime ON [dbo].[mod2] (epochTime ASC);

CREATE NONCLUSTERED INDEX IX_modification_description ON [dbo].[mod2] (description ASC);

CREATE NONCLUSTERED INDEX IX_modification_forecastId ON [dbo].[mod2] (forecastId ASC);

CREATE NONCLUSTERED INDEX IX_modification_userId ON [dbo].[mod2] (userId ASC);

and this is my query:

SELECT name, epochTime, auxDate 
    FROM mod2 WITH (NOLOCK)
    JOIN [user] ON [user].id = mod2.userId 
    WHERE forecastId = ? AND description = ? AND auxDate = ?

This is a legacy system and it was crawling before I put these indexes on it and changed the description field from VARCHAR to CHAR

The forecast and user id fields are INT and indexed similarly.

回答1:

There are a few things you can do here.

One is to ensure there is a clustered index on user for its id field (probably there is, but making sure won't hurt).

The individual indexes you put in are not great - in particular given the query pattern you have shown - either non of them would be used (as they do not contain the full data), or some of them might be used and then the full table will be refered to in order to pick out the remaining data required to fulfill the query.

For this particular query, for mod2, I'd likely add an index on userId with it covering forecastId, description and auxDate - this way the index contains all the data required to fulfil the query (on the mod2 side).

回答2:

CREATE NONCLUSTERED INDEX IXmod2_user_desc_forecast_auxdate 
ON [dbo].[mod2] (userId, forecastId, description, auxDate DESC);

and the query plans looks like this:

  |--Nested Loops(Inner Join, OUTER REFERENCES:([MyDB].[dbo].[mod2].[id]) OPTIMIZED)
       |--Nested Loops(Inner Join, OUTER REFERENCES:([MyDB].[dbo].[user].[id], [Expr1006]) WITH UNORDERED PREFETCH)
       |    |--Clustered Index Scan(OBJECT:([MyDB].[dbo].[user].  [PK_user]), ORDERED FORWARD)
       |    |--Index Seek(OBJECT:([MyDB].[dbo].[mod2].[IX_mod2_user_desc_forecast_auxdate]), SEEK:([MyDB].[dbo].[mod2].[userId]=[MyDB].[dbo].[user].[id] AND [MyDB].[dbo].[mod2].[forecastId]=(40357) AND [MyDB].[dbo].[mod2].[description]='SAVE' AND [MyDB].[dbo].[mod2].[auxDate]='2017-01-31') ORDERED FORWARD)
       |--Clustered Index Seek(OBJECT:([MyDB].[dbo].[mod2].[PK_mod2]), SEEK:([MyDB].[dbo].[mod2].[id]=[MyDB].[dbo].[mod2].[id]) LOOKUP ORDERED FORWARD)