创建索引时如何选择列？

Question

创建索引时如何选择列？

24

这似乎是一个奇怪的问题。我了解 SQL Server 中不同类型的索引（聚集、非聚集、唯一、过滤、包含列的索引等），并知道如何创建它们。此外，我知道索引取决于查询，但我不知道在创建索引时谁选择列。例如，假设有一个简单的网站，允许用户发布文本和图片。该网站有两个简单的表格如图所示：

如何在创建索引时选择列

用于在网站中获取用户的查询为：

Select UserID,UserName from User where Email='something' and Password='something'

假设我想要为这张表创建索引，我应该在创建索引时包含哪些列？我知道不同类型的索引可能会包含不同的列，但是我怎样才能在创建聚集或非聚集索引时决定选择哪些列？我看到一些几乎总是选择where子句之后的列作为索引的例子。这是真的吗？

获取用户帖子的查询是：

Select * from Posts where UserID='something'

这个查询与第一个查询不同。这个查询可能返回多行，而第一个查询总是只返回一行。现在同样的问题，如何选择列？

我的意思是，在以下情况下如何选择列：

创建聚集索引。
创建非聚集索引。
创建包含列的非聚集索引。

上面的例子仅是为了阐明问题的思路。目标不是为了找到两个查询示例中的好索引，而是提供一个基础，以帮助在创建索引时选择列。

- Ahmed Shamel

2个回答

0

我更倾向于在电子邮件上创建一个非聚集索引，密码可以作为包含列，并在 UserId 上创建一个聚集索引，这可能是一个自增列。

- Eralper

1

正如我在问题中所说，目标不是为查询找到良好的索引。意图是找到一些基础，以帮助选择要索引的列。 - Ahmed Shamel

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Evaldas Buinauskas · Accepted Answer

在理想情况下，您需要索引出现在WHERE子句或JOIN条件中的列。在您的情况下，这将是Email和Password列。

因此，您可以在用户表和电子邮件和密码上使用非聚集索引。

因此，基本上这个索引：

CREATE NONCLUSTERED INDEX idx_User_Email_Password
    ON dbo.User (Email, Password);

因此，如果您运行此查询：

SELECT UserID, UserName
FROM User
WHERE Email = 'something'
    AND Password = 'something';

你最终会使用刚刚创建的索引（很可能是聚集索引），并通过它来查找。然而，你的查询选择了UserID和UserName，它们没有包含在你的索引中，结果会进行键值查找（它会在创建的索引中找到记录，并回到你的dbo.User表中查找SELECT语句匹配的值（UserID和UserName）。为了避免这种情况，你可以创建带有INCLUDED列的索引，以消除键值查找（你肯定要这样做）。

CREATE NONCLUSTERED INDEX idx_User_Email_Password
    ON dbo.User (Email, Password)
    INCLUDE (UserID, UserName);

使用这个索引，你将在执行计划中得到一个不错的非聚集索引查找。

此外，选择索引列的顺序很重要。假设你的表包含UserTypeID（数量不多）。那么如果你传递了一些特定的UserTypeID和一组UserIDs，SQL Server可能会选择以UserTypeID作为第一个索引列的索引。

所以进行一些测试：

CREATE TABLE #Users
(
    UserId INT
    , UserName VARCHAR(500)
    , Email VARCHAR(500)
    , Password VARCHAR(500)
);

CREATE CLUSTERED INDEX idx_Users_UserID
    ON #Users (UserID);

-- Some test data from my DB
INSERT INTO #Users (UserId, UserName, Email, Password)
SELECT TOP (10000) UserId, UserName, Email, 'password'
FROM Users;

所以这就是查询语句：

So this is the query:

SELECT *
FROM #Users;

如果不指定任何细节，这将执行索引扫描。

现在，如果我们指定UserId，它将查找您的聚集索引（我们将UserId作为键）:

SELECT *
FROM #Users
WHERE UserID = 602;

在此输入图像描述

现在让我们创建一个没有包含列的索引并查询一些内容：

CREATE NONCLUSTERED INDEX idx_Users_Email_Password
    ON #Users (Email, Password);

SELECT *
FROM #Users
WHERE Email = 'k0641088@kingon.a.uk';

正如我所讲，它使用已创建的索引并进行关键字查找，找到匹配的电子邮件和密码，并在表中查找其余列以输出它们（P.S.如果您只需要输出电子邮件，它不会进行关键字查找，也不需要它）：输入图像描述

现在让我们创建一个包含UserName的索引并运行上面的查询。它将生成这个漂亮的执行计划，只需普通的NonClustered索引查找，就像我之前说过的：

CREATE NONCLUSTERED INDEX idx_Users_Email_Password_iUserName
    ON #Users (Email, Password)
    INCLUDE (UserName);

enter image description here

这是一篇高质量的文章，我建议阅读它：https://www.simple-talk.com/sql/performance/index-selection-and-the-query-optimizer/