主页 / dba / 问题 / 231689
Accepted
J Weezy
Asked: 2019-03-09 11:08:25 +0800 CST

在 IP 地址（IPv4 和 IPv6）范围之间执行搜索/查找时，最佳索引策略或查询 SELECT 是什么？

  • 772

问题：是否有更好的索引策略或查询 SELECT 可用于查找一个大数据集与另一个大数据集？或者，我应该看看将查找维度表放在内存中（全部 125 GB）吗？

服务器配置：

  • 该服务器是运行在 VMWare 之上的虚拟服务器，因此可以在后台添加额外的硬件而无需重新安装操作系统
  • Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Aug 22 2017 17:04:49 版权所有 (C) 2017 Microsoft Corporation Standard Edition（64 位）在 Windows Server 2016 Standard 10.0（Build 14393：）（Hypervisor）
  • 注意：我之前使用的是 2014 Enterprise - 我询问过为什么我被安排在 Standard 上。
  • 只有一个实例运行 2 个数据库：我的和 DBA
  • 2 个文件组，每个有 1 个文件：PRIMARY（系统表：非默认）和 SECONDARY（非系统表：默认）。SECONDARY 旨在可扩展以在添加更多 CPU 后容纳更多文件。最初创建文件组时，服务器只有 2 个 CPU
  • 8 GB 内存
  • 500 GB 磁盘存储 (ISCSI SAN)
  • 4 个 CPU（我假设是英特尔）

IIS Exchange Server 日志表架构：

CREATE TABLE [FWY].[ExchangeServerLogTest](
    [RowKey] [int] IDENTITY(1,1) NOT NULL,
    [SourceFileName] [varchar](50) NOT NULL,
    [SourceServer] [varchar](9) NOT NULL,
    [SourceService] [varchar](6) NOT NULL,
    [EventOccuranceTs] [datetime] NOT NULL,
    [ServiceType] [varchar](50) NOT NULL,
    [UserNameType] [varchar](25) NOT NULL,
    [DomainId] [varchar](50) NULL,
    [DomainName] [varchar](255) NULL,
    [UserNameToLookup] [varchar](255) NOT NULL,
    [UserAgent] [varchar](255) NULL,
    [OutsideProtocolId] [varchar](10) NOT NULL,
    [OutsideIp] [varchar](39) NULL,
    [OutsideIpHex] [varbinary](16) NULL,
    [InsideProtocolId] [varchar](10) NOT NULL,
    [InsideIp] [varchar](39) NULL,
    [InsideIpHex] [varbinary](16) NULL,
    [DeviceId] [varchar](32) NULL,
    [DeviceType] [varchar](25) NULL,
    [DeviceModel] [varchar](75) NULL,
    [AsOfDt] [date] NULL,
    [OutsideProtocolKey] [int] NULL,
    [InsideProtocolKey] [int] NULL,
 CONSTRAINT [PK_ExchangeServerLogTest] PRIMARY KEY CLUSTERED 
(
    [RowKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [SECONDARY]
) ON [SECONDARY]

非聚集索引：

CREATE NONCLUSTERED INDEX [NCIDX_ExchangeServerLogTest_InsideOutsideProtocolKeyIpHexInclRowKey] ON [FWY].[ExchangeServerLogTest]
(
    [InsideProtocolKey] ASC,
    [OutsideProtocolKey] ASC,
    [InsideIpHex] ASC,
    [OutsideIpHex] ASC
)
INCLUDE (   [RowKey]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

IP GeoLocation 数据供应商表架构

CREATE TABLE [DE].[IpGeoLocation](
    [CreateTs] [datetime] NOT NULL,
    [CreateBy] [varchar](50) NOT NULL,
    [CreateSequenceKey] [int] NULL,
    [UpdateTs] [datetime] NULL,
    [UpdateBy] [varchar](50) NULL,
    [UpdateSequenceKey] [int] NULL,
    [ActiveInd] [int] NOT NULL,
    [RowKey] [int] IDENTITY(1,1) NOT NULL,
    [VendorKey] [int] NULL,
    [VendorTypeKey] [int] NULL,
    [DimensionTypeKey] [int] NULL,
    [ProtocolKey] [int] NULL,
    [ProtocolId] [varchar](10) NOT NULL,
    [EffectiveStartDate] [date] NULL,
    [EffectiveEndDate] [date] NULL,
    [NetworkStartIp] [varchar](39) NOT NULL,
    [NetworkStartIpHex] [varbinary](16) NULL,
    [NetworkEndIp] [varchar](39) NOT NULL,
    [NetworkEndIpHex] [varbinary](16) NULL,
    [Country] [varchar](255) NOT NULL,
    [Region] [varchar](255) NOT NULL,
    [City] [varchar](255) NOT NULL,
    [ConnectionSpeed] [varchar](255) NOT NULL,
    [ConnectionType] [varchar](255) NOT NULL,
    [MetroCode] [int] NOT NULL,
    [Latitude] [numeric](6, 3) NULL,
    [Longitude] [numeric](6, 3) NULL,
    [PostalCode] [varchar](255) NOT NULL,
    [PostalExtension] [varchar](255) NOT NULL,
    [CountryCode] [int] NOT NULL,
    [RegionCode] [int] NOT NULL,
    [CityCode] [int] NOT NULL,
    [ContinentCode] [int] NOT NULL,
    [TwoLetterCountry] [varchar](2) NOT NULL,
    [InternalCode] [int] NOT NULL,
    [AreaCodes] [varchar](255) NOT NULL,
    [CountryConfidenceCode] [int] NOT NULL,
    [RegionConfidenceCode] [int] NOT NULL,
    [CityConfidenceCode] [int] NOT NULL,
    [PostalConfidenceCode] [int] NOT NULL,
    [GmtOffset] [varchar](255) NOT NULL,
    [InDistance] [varchar](255) NOT NULL,
    [TimeZoneName] [varchar](255) NOT NULL,
 CONSTRAINT [PK_IpGeoLocation] PRIMARY KEY CLUSTERED 
(
    [RowKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [SECONDARY]
) ON [SECONDARY]

非聚集索引：

CREATE NONCLUSTERED INDEX [NCIDX_IpGeoLocation_ProtocolKeyNetworkStartEndIpHexIncRowKey] ON [DE].[IpGeoLocation]
(
    [ProtocolKey] ASC,
    [NetworkStartIpHex] ASC,
    [NetworkEndIpHex] ASC
)
INCLUDE (   [RowKey]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

使用 .NET 的 System.Net 类将 IP 地址转换为其十六进制值：Ipaddress.Parse( IpAddress ).GetAddressBytes()。我用 SSIS 加载数据文件，我有一个脚本组件，它返回 ProtocolId 和 IP 地址作为字节数组，它作为 DT_BYTE 进入 SSIS 并映射到 SQL Server VARBINARY(16) 字段（字节数组隐式转换为十六进制值）。

查找 IP 地址范围

我有两个数据集：IIS Exchange Server IP 日志记录和第三方供应商提供的 IP GeoLocation 数据；其中地理位置涵盖了一系列 IP 地址。我需要从日志文件中查找 IP 地址并获取其 GeoLocation。两个数据集都适用于 IPv4 和 IPv6，并且 IP 地址以字符串格式接收。当我加载数据时，我将 IP 地址转换为十六进制值 [VARBINARY(16)]，以便我可以查找 IP 地址 GeoLocation。

这里的问题是我正在加载大量记录。目前，供应商提供了近2亿个IP地址Geolocations（即维度查找表）。我从一开始就知道在所有阶段（即硬件配置、表分区和索引策略）都需要进行性能优化。我加载了一周的样本日志数据，大约有 1.5 亿条记录。

注意：日志文件在大约 90% 的记录被忽略的地方被解析——我们只加载了 10% 的记录，所以这里没有性能提升

我在 ExchangeLogs 表上创建了以下索引：

  1. 名为 RowId 的整数 IDENTITY 列上的聚集索引
  2. ProtocolId（即表示为整数的 IPv4 或 IPv6）、IpHex 上的非聚集索引；其中包含 RowId

我在 IPGeoLocation 表上创建了以下索引：

  1. 名为 RowId 的整数 IDENTITY 列上的聚集索引
  2. ProtocolId（即表示为整数的 IPv4 或 IPv6）、StartIpHex 和 EndIpHex 上的非聚集索引；其中包含 RowId

在搜索 IP 地理定位时，我按如下方式加入两个数据集：

SELECT COUNT(DISTINCT DE.RowKey)
FROM DE.IpGeoLocation DE
INNER JOIN FWY.ExchangeServerLogTest T
    ON T.InsideProtocolKey = DE.ProtocolKey
    AND T.InsideIpHex BETWEEN DE.NetworkStartIpHex AND DE.NetworkEndIpHex

Estimated Query Execution Plan：估计的InsideIp查询执行计划

实际查询执行计划：等待查询完成

SELECT COUNT(DISTINCT DE.RowKey)
FROM DE.IpGeoLocation DE
INNER JOIN FWY.ExchangeServerLogTest T
    ON T.OutsideProtocolKey = DE.ProtocolKey
    AND T.OutsideIpHex BETWEEN DE.NetworkStartIpHex AND DE.NetworkEndIpHex

Estimated Execution Plan：估计OutsideIp查询执行计划

实际查询执行计划：未完成

注2：必须包含ProtocolId，否则每次IP查找有两种结果：一种用于IPv4，一种用于IPv6。

这似乎是一个非常有效的执行计划，考虑到 95% 的成本用于索引查找，另外 2% 用于索引扫描- 97% 归因于索引工作。

日志文件的每一行都包含内部和外部 IP 地址。对于加载的示例数据：

  1. 内部 IP 列表包含 3 个不同的 IP 地址。
  2. 外部 IP 列表包含大约 60,000 个不同的 IP 地址。

结果：

  1. 内部 IP 列表上的 SELECT 大约需要 9 分钟才能完成。
  2. 外部 IP 列表上的 SELECT 在允许其运行 16.25 小时（一夜）后被停止。

我没有对日志表或 IP GeoLocation 表进行分区。这可能会通过两个单独的 LUN 流式传输数据来提高性能，但我仍在尝试从我们的 IT Ops 小组获取硬件配置规范（他们刚刚配置了新服务器，所以我还没有该信息）。

sql-server performance

1 个回答

  1. Best Answer

    • 首先，我建议你添加两个单独的索引，在

      (InsideProtocolKey, InsideIpHex) INCLUDE (RowKey)

(OutsideProtocolKey, OutsideIpHex) INCLUDE (RowKey)

      并再次尝试查询。您的 4 列索引不适合“外部”查询，因为这些列出现在第 2 和第 4 个位置，对“内部”查询（第 1 和第 3）仅略有好处。另外，这 2 个索引的大小将减半（每行 20 字节 vs 40 字节）。

    • 第二，小改进。ProtocolKey由于列（及其变体，内部/外部）只有两个选项，因此您可以将（所有这些）从int（4 字节）转换为tinyint（1 字节）甚至转换为bit（1 位）并每行保存 3 个字节（或 3 + 7/8）。

      这不会是一个巨大的节省，但对于大桌子来说，它会有所帮助。对于不太大的情况，对于出现列的每个索引，200M 行 x 3 字节 = 600MB 保存。我不完全确定索引bit列的空间使用情况，但对于相同的表大小，保存肯定会与tinyint(600MB) 或更多（最多 775MB）相同。尽管如此，我再次提到这一点，对于使用列的每个索引。

      更小的索引，更小的磁盘空间，更重要的是，更少的内存，更有可能留在内存中，尤其是在你拥有低 RAM 服务器的情况下。

    • 第三，如今 8GB 的​​ RAM 听起来很小，尤其是当您有这么大的表时。RAM 很便宜（至少在您超过 128GB 标准/企业门槛之前是这样，然后您需要支付更高的许可费用）。

    • 4

相关问题