在執行 IP 地址（IPv4 和 IPv6）範圍之間的搜尋/查找時，最佳索引策略或查詢 SELECT 是什麼？

March 8, 2019

**問題：**是否有更好的索引策略或查詢 SELECT 可用於根據另一個大型數據集查找一個大型數據集？或者，我應該考慮將查找維度表放在記憶體中（全部 125 GB）嗎？
伺服器配置：
該伺服器是執行在 VMWare 之上的虛擬伺服器，因此可以在後台添加額外的硬體，而無需重新安裝作業系統
Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) 2017 年 8 月 22 日 17:04:49 版權所有 (C) 2017 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393:) (Hypervisor)
**注意：**我之前參加的是 2014 企業版 - 我詢問了為什麼我被安排在標準版上。
只有一個實例執行 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
IP 地址使用 .NET 的 System.Net 類轉換為十六進制值：Ipaddress.Parse( IpAddress ).GetAddressBytes()。我使用 SSIS 載入數據文件，並且我有一個腳本組件，它以字節數組的形式返回 ProtocolId 和 IP 地址，它作為 DT_BYTE 進入 SSIS 並映射到 SQL Server VARBINARY(16) 欄位（字節數組是隱式的轉換為十六進制值）。
查找 IP 地址範圍
我有兩個數據集：IIS Exchange Server IP 日誌記錄和第三方供應商提供的 IP GeoLocation 數據；地理位置涵蓋一系列 IP 地址。我需要從日誌文件中查找 IP 地址並獲取其地理位置。兩個數據集都適用於 IPv4 和 IPv6，並且 IP 地址以字元串格式接收。當我載入數據時，我將 IP 地址轉換為十六進制值
$$ VARBINARY(16) $$這樣我就可以查找 IP 地址 GeoLocation。這裡的問題是我正在載入大量記錄。目前，供應商提供了近2億個IP地址Geolocations（即維度查找表）。我從一開始就知道在所有階段（即硬體配置、表分區和索引策略）都需要進行性能優化。我已經載入了一周的樣本日誌數據，大約是 1.5 億條記錄。
**注意：**解析日誌文件時忽略了大約 90% 的記錄 - 我們僅載入 10% 的記錄，因此此處無法提高性能
我在 ExchangeLogs 表上創建了以下索引：
名為 RowId 的整數 IDENTITY 列上的聚集索引
ProtocolId 上的非聚集索引（即表示為整數的 IPv4 或 IPv6），IPHex；包含 RowId 的位置
我在 IPGeoLocation 表上創建了以下索引：
名為 RowId 的整數 IDENTITY 列上的聚集索引
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
預估查詢執行計劃：預估 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
預估執行計劃：預估 OutsideIp 查詢執行計劃
實際查詢執行計劃：未完成
**注意 2：**必須包含 ProtocolId，否則每次 IP 查找有兩個結果：一個用於 IPv4，一個用於 IPv6。
這似乎是一個非常有效的執行計劃，考慮到 95% 的成本用於索引搜尋，另外 2% 用於索引掃描- 97% 歸因於索引工作。
日誌文件的每一行都包含內部和外部 IP 地址。對於載入的範例數據：
內部 IP 列表包含 3 個不同的 IP 地址。
外部 IP 列表包含大約 60,000 個 DISTINCT IP 地址。
結果：
內部 IP 列表上的 SELECT 大約需要 9 分鐘才能完成。
外部 IP 列表上的 SELECT 在允許其執行 16.25 小時（過夜）後停止。
我沒有對日誌表或 IP GeoLocation 表進行分區。這可能會通過通過兩個單獨的 LUN 流式傳輸數據來提高性能，但我仍在嘗試從我們的 IT Ops 組獲取硬體配置規範（他們剛剛配置了新伺服器，所以我還沒有這些資訊）。

首先，我建議您添加兩個單獨的索引，在
(InsideProtocolKey, InsideIpHex) INCLUDE (RowKey)

(OutsideProtocolKey, OutsideIpHex) INCLUDE (RowKey)
並再次嘗試查詢。您的 4 列索引不適用於“外部”查詢，因為列出現在第 2 和第 4 位，而對“內部”查詢（第 1 和第 3 位）僅稍有好處。此外，這 2 個索引的大小將減半（每行 20 字節對 40 字節）。
第二，小幅改進。ProtocolKey由於列（及其變體，Inside/Outside）只有兩個選項，因此您可以將（全部）從int（4 個字節）轉換為tinyint（1 個字節）甚至到bit（1 位）並每行節省 3 個字節（或 3 + 7/8）。
這不會是一個巨大的節省，但對於大桌子，它會有所幫助。對於不太大的，200M 行 x 3 字節 = 600MB 保存，對於列出現**的每個索引。**我不完全確定索引bit列的空間使用情況，但對於相同的表大小，保存肯定會與tinyint（600MB）或更多（最多775MB）相同。不過，**對於使用 column 的每個索引，**我再次提到這一點。
更小的索引，更小的磁碟尺寸，更重要的是，更少的記憶體，更有可能留在記憶體中，尤其是在你擁有的低 RAM 伺服器的情況下。
第三，如今 8GB 聽起來像是很小的 RAM，尤其是當您擁有這種大小的表時。RAM 很便宜（至少在您通過 128GB 標準/企業門檻值之前，您需要支付更高的許可費用）。

引用自：https://dba.stackexchange.com/questions/231689

在執行 IP 地址（IPv4 和 IPv6）範圍之間的搜尋/查找時，最佳索引策略或查詢 SELECT 是什麼？

相關問答

如何從查詢中消除這種代價高昂的索引查找操作？

如何提高查詢性能？

為什麼我的 EXISTS 查詢執行索引掃描而不是索引查找？

即使通過使其以並行模式執行來改進查詢，查詢性能也會更差

當我在兩個索引視圖上通過聚集索引進行內部聯接時，為什麼查詢處理器不能生成帶有 FORCESEEK 提示的查詢計劃？

包含列的非聚集索引覆蓋率