需要提高性能以查询具有数百万行的表-SQL Server [英] Need to improve performance query a table with millions of rows - SQL Server

问题描述

一旦性能测试(读取和插入)中的负载增加，并且对索引的处理已完成，对审计跟踪表的查询就会开始变慢.通过此查询以及这些表和索引，我还能做什么?

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);

这是我的查询:

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

这是一个旧系统，在我将这些索引放在上面并将description字段从VARCHAR更改为CHAR

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

预测和用户id字段为INT，并以相似的方式编制索引.

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

推荐答案

在这里您可以做一些事情.

There are a few things you can do here.

一种方法是确保user上的id字段具有聚集索引(可能存在，但请确保不会受到伤害).

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.

对于此特定查询，对于mod2，我可能会在userId上添加

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).

这篇关于需要提高性能以查询具有数百万行的表-SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！