时间戳

首页 > 解决方案 > 单个指数 vs 覆盖指数

sql - 单个指数 vs 覆盖指数

问题描述

我的任务是提高完全非规范化的表的性能。

它有 30 列，但为了简单起见，我的示例有 4 列。

CREATE TABLE (
   Id UNIQUEIDENTIFIER NOT NULL,
   Location1 NVARCHAR(MAX) NULL,
   Location2 NVARCHAR(MAX) NULL,
   Location3 NVARCHAR(MAX) NULL,
   ...
   PersonId UNIQUEIDENTIFIER NULL
)

这包含位置的层次结构，并且在层次结构的末尾是分配到该位置的人员。

示例数据为：

  1. A楼，空，空，空，空
  2. A楼，底层，NULL，NULL，NULL
  3. A 楼，底层，第 1 部分，NULL，NULL
  4. A 楼底层 1 区 1 室 NULL
  5. A 楼，底层，第 1 部分，1 号房间，“Craig”
  6. A 楼底层 1 区 1 室“约翰”
  7. A 楼，底层，第 2 部分，NULL，NULL
  8. A 楼底层 2 区 1 室 NULL
  9. A 楼，一楼，第 2 部分，房间 1，“彼得”

所以，在这种情况下，我们有 2 个房间，它们的层次结构。Section 1 的 Room 1 有 2 人，Section 2 的 Room 1 有一个人。

令人印象深刻的桌子设计，我知道。

我所做的是要求我们放弃NVARCHAR(MAX). 这已更改为VARCHAR(80). 这现在允许我使用索引。我的问题是关于要使用的索引类型。

要查找所选行的父级，我需要执行以下操作：

SELECT *
FROM MyTable
WHERE ISNULL(Location1,'') = ISNULL(MyLocation1,'') AND
      ISNULL(Location2,'') = ISNULL(MyLocation2,'') AND    
      ISNULL(Location3,'') = ISNULL(MyLocation3,'')

绝大多数查询都遵循这种模式。'WHERE'ing LocationX 列。

我不确定是否应该为每列创建一个索引……或者，我是否应该创建一个涵盖所有列的索引……或者，每列一个索引，包括剩余的列。

所以，

或者

或者

我不确定该往哪个方向发展，以获得最佳性能。

标签： sqlsql-server

解决方案

所有相关列上都应该有一个索引(location1, location2, location3)，但是这里的NULL值有问题。

SELECT *
FROM MyTable
WHERE 
    ISNULL(Location1,'') = ISNULL(MyLocation1,'') AND
    ISNULL(Location2,'') = ISNULL(MyLocation2,'') AND    
    ISNULL(Location3,'') = ISNULL(MyLocation3,'')

当您ISNULL对列值使用函数（或几乎任何函数）时，通常无法使用索引。您可以通过查看执行计划来确认。

如果您编写这样的查询：

SELECT *
FROM MyTable
WHERE 
    Location1 = ISNULL(MyLocation1,'') AND
    Location2 = ISNULL(MyLocation2,'') AND    
    Location3 = ISNULL(MyLocation3,'')

将使用索引，但查询不会产生正确的结果。

查看您如何编写查询和使用ISNULL，看起来您确信''可以安全地使用 value 而不是NULL. 理想情况下，应制作所有这些列NON-NULL并将所有NULL值替换为''.

由于您不能这样做，因此处理它的一种方法是为每LocationN列创建计算的持久列，然后在这些计算列上创建索引并在查询中使用这些计算列：

CREATE TABLE dbo.MyTable
(
    ID int NOT NULL IDENTITY (1, 1),
    Location1 varchar(80) NULL,
    Location2 varchar(80) NULL,
    Location3 varchar(80) NULL,
    Location1_  AS ISNULL(Location1, '') PERSISTED,
    Location2_  AS ISNULL(Location2, '') PERSISTED,
    Location3_  AS ISNULL(Location3, '') PERSISTED 
)

创建索引

CREATE NONCLUSTERED INDEX [IX] ON [dbo].[MyTable]
(
    [Location1_] ASC,
    [Location2_] ASC,
    [Location3_] ASC
)

询问

SELECT *
FROM MyTable
WHERE 
    Location1_ = ISNULL(MyLocation1,'') AND
    Location2_ = ISNULL(MyLocation2,'') AND    
    Location3_ = ISNULL(MyLocation3,'')

此主题的另一个变体是创建单个计算列并将所有字符串与一些分隔符合并在一起，并在该单个列上创建索引。它可能更有效。

ALTER TABLE dbo.MyTable ADD
LocationAll  AS 
    isnull([Location1],'') + '|' + 
    isnull([Location2],'') + '|' +
    isnull([Location3],'') + '|' PERSISTED 


CREATE NONCLUSTERED INDEX [IX_all] ON [dbo].[MyTable]
(
    [LocationAll] ASC
)

查询将如下所示：

SELECT *
FROM MyTable
WHERE 
    LocationAll = 
        ISNULL(MyLocation1,'') + '|'
        ISNULL(MyLocation2,'') + '|'
        ISNULL(MyLocation3,'') + '|'

或者

SELECT *
FROM MyTable
WHERE 
    LocationAll LIKE
        ISNULL(MyLocation1,'') + '|'
        ISNULL(MyLocation2,'') + '|'
        ISNULL(MyLocation3,'') + '|' + '%'

推荐阅读