主页 / dba / 问题 / 299984
Accepted
Jozef Podlecki
Asked: 2021-09-23 08:11:26 +0800 CST

使用空间索引是否正确？

  • 772

我最近在一张表上使用了空间索引来改进按给定日期范围、参考标识符以及它们是否在多边形内过滤记录的查询。

这是查询的示例

DECLARE
    @start DATETIME = '2021-09-01 00:00:00.000',
    @end DATETIME = '2021-09-22 00:00:00.000',
    @shape GEOGRAPHY
    
SET @shape = geography::STPolyFromText('POLYGON((
-5.0537109 55.5783447,
-3.9990234 51.0137547,
3.2299805 50.4295179,
4.0209961 54.8133484,
-5.0537109 55.5783447
))', 4326);

SELECT
    *
FROM [dbo].[getUsersByArea](@start, @end, @shape);

我注意到了

  • 有时甚至不使用空间索引。
  • 如果它出现在查询计划中，性能仍然很差。

该user_location表有大约 7000 万条记录。

该reference表有大约 100k 条记录，通常有几千 user_location条记录引用一个表。

该user表有 100k 条记录。

我还摆弄了 OGC 地理方法，我尝试使用STIntersects, STWithin, STContains（令人惊讶的行为相同STWithin），Filter但它无法缓解提到的任何问题（STIntersects在查询计划中不知何故显得更糟）。

使用这种类型的索引是正确的方法吗？这真是令人头疼。

告诉我你的想法。

提前致谢。

这是架构定义

CREATE TABLE [dbo].[user_location] (
    [Id] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ReferenceId] [uniqueidentifier] NOT NULL,
    [RecordedOn] [datetime] NOT NULL,
    [Lat] [float] NULL,
    [Lng] [float] NULL,
    [Location] AS (case when [Lat] IS NULL OR [Lng] IS NULL then NULL else [geography]::Point([Lat],[Lng],(4326)) end) PERSISTED,
    CONSTRAINT [PK_dbo_user_location] PRIMARY KEY CLUSTERED([Id] ASC)
)

CREATE TABLE [dbo].[reference]
(
    [Id] [uniqueidentifier] NOT NULL,
    [UserId] [uniqueidentifier] NOT NULL,
    CONSTRAINT [PK_dbo_reference] PRIMARY KEY CLUSTERED([Id] ASC)
)

CREATE TABLE [dbo].[user]
(
    [Id] [uniqueidentifier] NOT NULL,
    [FullName] [nvarchar](100) NULL,
    [EmailAddress] [nvarchar](255) NULL,
    CONSTRAINT [PK_dbo_user] PRIMARY KEY CLUSTERED([Id] ASC)
) 

CREATE NONCLUSTERED INDEX [IX_reference_userId] ON [dbo].[reference]
(
    [UserId] ASC
) WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF)

CREATE NONCLUSTERED INDEX [IX_user_location_referenceId_recordedOn] ON [dbo].[user_location]
(
    [ReferenceId] ASC,
    [RecordedOn] ASC,
) WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF)

CREATE SPATIAL INDEX [SPIX_user_location] ON [dbo].[user_location]
(
    [Location]
)USING  GEOGRAPHY_AUTO_GRID 
WITH (
CELLS_PER_OBJECT = 12, STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF)

CREATE FUNCTION [dbo].[getUsersByArea]
(
    @start DATETIME,
    @end DATETIME,
    @shape GEOGRAPHY
)
RETURNS TABLE
AS
RETURN
(
    SELECT DISTINCT
        us.Id,
        us.FullName,
        us.EmailAddress
    FROM [dbo].[user_location] ul
    JOIN [dbo].[reference] rf
        ON ul.ReferenceId = rf.Id
    JOIN [dbo].[user] us
        ON us.Id = rf.UserId
    WHERE 
        ul.RecordedOn < @end
        AND ul.RecordedOn >= @start 
        AND ul.[Location] IS NOT NULL 
        AND @shape.STContains(ul.[Location]) = 1
)

还有一件有趣的事情，当您尝试检索空间索引元数据时，sys.sp_help_spatial_geography_index它会无限执行。

EXEC sys.sp_help_spatial_geography_index
    @tabname = 'user_location',
    @indexname = 'SPIX_user_location',
    @verboseoutput = 1,
    @query_sample = 'POINT (0 0)'
sql-server index

1 个回答

  1. Best Answer

    我认为您应该尝试将@shape 变量存储在另一个基表（临时表？）中并为其添加空间索引

    例子

    CREATE PROCEDURE [dbo].[getUsersByArea]
    @start DATETIME,
    @end DATETIME,
    @shape GEOGRAPHY
AS
BEGIN
    SET NOCOUNT ON;

    CREATE TABLE #Shapes
    (
        [Id] [uniqueidentifier] NOT NULL,
        Shape GEOGRAPHY NOT NULL,
        CONSTRAINT [PK_Shapes] PRIMARY KEY CLUSTERED([Id] ASC)
    )

    INSERT INTO #Shapes
    VALUES
    (NEWID(), @shape)

    CREATE SPATIAL INDEX [SPIX_TMP_Shape] ON #Shapes ([Shape])
    -- YMMV
    USING GEOGRAPHY_GRID 
    WITH (GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4= 
    HIGH),
    CELLS_PER_OBJECT = 16)

    SELECT DISTINCT
        us.Id,
        us.FullName,
        us.EmailAddress
    FROM [dbo].[user_location] ul
    JOIN [dbo].[reference] rf
        ON ul.ReferenceId = rf.Id
    JOIN [dbo].[user] us
        ON us.Id = rf.UserId
    JOIN #Shapes sh
        ON sh.Shape.STContains(ul.[Location]) = 1
    WHERE ul.[Location] IS NOT NULL
        ul.RecordedOn < @end
        AND ul.RecordedOn >= @start
)
    • 2

相关问题