代码之家 › 专栏 › 技术社区 › Craig

个别指数与覆盖指数

sql-server sql

Craig · 技术社区 · 7 年前

我的任务是改进完全非规范化的表的性能。

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

它包含一个位置层次结构,在层次结构的末尾是一个分配给该位置的人员。

示例数据如下:

A栋,一楼,空,空,空

A栋一楼1区空,空

A栋一楼1区1室,空

A栋一楼1区1室,“克雷格”

A栋一楼1区1室,“约翰”

A栋,一楼,2区,1室,空

A栋一楼2区1室,“彼得”

所以,在这个例子中,我们有两个房间,它们的层次结构。第一区的第一个房间有两个人,第二区的第一个房间有一个人。

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

NVARCHAR(MAX) . 已更改为 VARCHAR(80) . 现在我可以使用索引了。我的问题是关于要使用的索引类型。

要查找选定行的父级,我需要执行以下操作:

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

我不确定是否应该为每列创建索引。。。。或者,我应该创建一个覆盖所有列的索引。。。或者,每列一个索引,包括其余的列。

所以,

或者

位置1上的索引,位置2。。。。

或者

位置1上的索引包括(位置2,位置3…)上的索引位置2包括(位置1、位置3…)

我不知道该往哪个方向走,才能得到最好的表现。

2 回复 | 直到 7 年前

Gordon Linoff 7 年前

您应该在上创建索引 (location1, location2, location3)

但是,您对 NULL 价值观。这些将阻碍索引的使用。要防止这种情况,请将默认值改为 无效的 '' :

CREATE TABLE t (
   Id UNIQUEIDENTIFIER NOT NULL,
   Location1 NVARCHAR(80) NOT NULL DEFAULT '',
   Location2 NVARCHAR(80) NOT NULL DEFAULT '',
   Location3 NVARCHAR(80) NOT NULL DEFAULT '',
   . . . 
)

Vladimir Baranov 7 年前

所有相关列上都应该有一个索引 (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,'')

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

看看您是如何编写查询和使用 ,看来你确定 '' 无效的 . 理想情况下,所有这些列都应该 NON-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,'') + '|' + '%'