提高 MS SQL 事务性能

Question

如果您缺少信息，我可以在请求时附加它们。

工作区

我有一个运行在MS SQL 2012标准版上的数据库，其结构如下：

表： 1.用户（id、softId（非唯一）、出生日期） - 行数：1050万 - 索引：三列和出生日期（聚集索引） 2.文档（docId、userId、创建日期、删除日期、姓氏、名字、分类ID） - 行数：2300万 - 索引：姓氏、名字、docId、创建日期、userID（聚集索引） - 注意：在这种情况下，名称与文档相关，而不是与userId相关。 3.分类（id、描述） - 行数：200 4.三个“数据”表 - 行数：100万、130万和30万 - 索引：docIds

关系： - 用户到文档：1对n - 分类到文档：1对n - 文档到数据表：1对n

为了选择完整的记录，我目前使用以下语句：

服务器执行时间为16秒。

SELECT * FROM (
    select * from docs 
    where userID in (
        select distinct userID from users where softId like '...'
    )
) as doc
LEFT JOIN users on users.userID = doc.userId
LEFT JOIN classifications on classifications.id = doc.classificationId
LEFT JOIN data1 on data1.docId = doc.docId
LEFT JOIN data2 on data2.docId = doc.docId
LEFT JOIN data3 on data3.docId = doc.docId;

更新 - 现在为15秒

SELECT
docID, calssificationId, classificationDescription,
userId, softId, forename, lastname, birthdate,
data1.id, data1.date, data2.id, data2.date, data3.id, data3.date,
FROM docs
JOIN users on users.userID = doc.userId AND softId like '...'
LEFT JOIN classifications on classifications.id = doc.classificationId
LEFT JOIN data1 on data1.docId = doc.docId
LEFT JOIN data2 on data2.docId = doc.docId
LEFT JOIN data3 on data3.docId = doc.docId;

执行计划

• 执行计划

服务器执行时间为17秒

DECLARE @userIDs table( id bigint );
DECLARE @docIDs table( id bigint );

insert into @userIDs select userID from users where softId like '...';
insert into @docIDs select docId from docs where userId in ( select id from @userIDs);
SELECT * FROM users where userID in ( select id from @userIDs);
SELECT * FROM docs where docID in (select id from @docIDs);
SELECT * FROM data1 where data1.docId in (select id from @docIDs);
SELECT * FROM data2 where data2.docId in (select id from @docIDs);
SELECT * FROM data3 where data3.docId in (select id from @docIDs);
GO

更新 - 现在为14秒

DECLARE @userIDs table( id bigint, softId varchar(12), birthdate varchar(8) );
DECLARE @docIDs table( id bigint, classification bigint, capture_date datetime, userId bigint, lastname varchar(50), forename varchar(50) );


INSERT INTO @userIDs select userID, softId, birthdate from users where softId like '...';
INSERT INTO @docIDs select docID, classification, capture_date, userID, lastname, forename from docs where userID in ( select id from @userIDs);

SELECT * FROM @userIDs;
SELECT * FROM @docIDs;

SELECT [only needed fields] FROM data1 where docID in (select id from @docIDs);
SELECT [only needed fields] FROM data2 where docID in (select id from @docIDs);
SELECT [only needed fields] FROM data3 where docID in (select id from @docIDs);

执行计划

• 用户ID的执行计划
• 文档ID的执行计划
• 用户ID输出的执行计划
• 数据1的执行计划

常规更新 @AntonínLejsek建议将文档的docId作为聚集索引，将pkId作为非聚集索引。这改变了执行时间如下：

• Join语句：-1秒
• Multi-Select语句：-5秒

我再次检查了索引并更改了包含的列，现在它们的执行时间是：

• Join语句：4秒
• Multi-Select语句：6秒

“简单”的问题

是否有人有减少执行时间的建议？

- coivip

---

亲爱的，首先：为什么在子查询中使用distinct，如select distinct userID from users where softId like '...'。userID不是users表的主键吗？ - Joe Taras

@JoeTaras：你说得对，distinct是不必要的。我已经把它移除了，但没有任何影响。 - coivip

在这里使用select *合适吗，还是只需要检索特定的字段？能否发布执行计划？ - alroc

@alroc，我通过更改其他内容来减少字段数量，但没有更改帖子。抱歉，现在已经添加了执行计划的链接。 - coivip

Answer 1

我会将逻辑表述为：

我会取消第一个子查询，只对 users 表进行必要的操作：

SELECT *
FROM docs JOIN
     users
     ON users.userID = doc.userId AND softId LIKE '...'  LEFT JOIN
     . . .

< p >如果您已经使用< code >JOIN，那么< code >IN中的逻辑是不必要的。

注意：这可能没有太大帮助，因为您的查询似乎返回了大量数据，包括列和行。

- Gordon Linoff

---

我按照建议进行了更改，现在我们有15秒的执行时间，并将在主帖上更新状态 - 谢谢。查询通常返回约1000行。 - coivip

userID是否是users表上聚集索引的第一个键？如果不是，您将需要为其创建一个单独的索引。此外，您应该为所有表的主键（包括classifications等表）创建索引。您还应该仔细检查data表上的索引，并通过逐个left join构建结果集的方式来计时查询。对于该查询来说，10秒以上的时间似乎太长了。 - Gordon Linoff

userId是非聚集索引 - 据我理解，聚集索引就像树一样，所以在出生日期上进行聚集更有意义，或者这样做是错误的吗？所有表都有主键。 - coivip

@coivip 你需要让 users(userId) 成为某个索引的第一个键，无论是聚集索引还是非聚集索引。 - Gordon Linoff

@GordonLinhoff 这是在非聚集索引上的第一个。 - coivip

Answer 2

我看到计划中有两个不同的数据库，我建议先在一个数据库中进行测试。

数据库设计很奇怪。你在birthdate上有聚集索引。由于它不是唯一的，数据库必须为其制定另外4B数字以使其唯一。因此，每个非聚集索引中都有12B的键，这是空间和性能效率低下的。你甚至没有将id包括在非聚集索引中，因此需要查找，这是浪费时间的。在大多数情况下，你应该以主键为基础进行聚集，并且主键应该是id。 --删除-- 虽然softIds几乎是唯一的，但这段话已经不相关了。

- Antonín Lejsek

---

我知道，第二个数据库是一个测试数据库，我在其中合并了一些表来减少查询中的连接。我有10120000个用户ID和10100000个软件ID。我理解聚集索引就像一棵树，所以对于我来说，出生日期作为聚集索引更有意义，而用户ID和软件ID则是非聚集索引——或者我理解错了吗？通常我需要搜索类似于'123456%'这样的内容——最大长度为12。 - coivip

@coivip 谢谢，这种情况下将softId移动到另一个表中不是一个好主意。有关群集索引，请参见此处：https://dev59.com/C2855IYBdhLWcg3wc0Dy - Antonín Lejsek

我晚些时候在家会查看链接。我现在不在办公室，直到明天早上才有机会更改。我修改后将发布它们。 - coivip

我在dos中切换了userId和docId的索引类型。在连接版本中，它对执行时间没有影响，在多选语句中，它减少了2秒的执行时间。 - coivip

Answer 3

在定义主键的同时，在您的表变量上添加聚集索引。

DECLARE @userIDs table( id bigint primary key, softId varchar(12), birthdate varchar(8) );
DECLARE @docIDs table( id bigint primary key, classification bigint, capture_date datetime, userId bigint, lastname varchar(50), forename varchar(50) );

- Bruce Dunwiddie

---

相同的执行时间 - coivip