如何优化多组(VersionId+Year)条件的Geolocation表查询性能？

一、查询语句优化：替换OR为JOIN临时表 #

当查询包含大量OR条件组时，SQL优化器可能无法高效利用索引，改用临时表存储条件组合+内连接的方式，能让数据库更高效地执行索引查找：

-- 创建临时表存储需要的(VersionId, Year)组合
CREATE TABLE #FilterConditions (VersionId INT, Year INT)
INSERT INTO #FilterConditions
VALUES 
(175,1999), (175,2000),
(176,1996), (176,1997), (176,1998), (176,1999), (176,2000),
(177,1998),
(178,1995), (178,1996),
(180,1997), (180,1998), (180,1999)
-- 添加更多需要的条件组合

-- 内连接原表查询目标数据
SELECT g.VersionId, g.Year, g.CityId, g.Count
FROM cache.Geolocation g
INNER JOIN #FilterConditions fc 
    ON g.VersionId = fc.VersionId AND g.Year = fc.Year

DROP TABLE #FilterConditions

这种方式能让数据库针对临时表和原表的索引执行更高效的连接算法（如嵌套循环），避免OR条件导致的全索引扫描或低效执行计划。

二、索引优化：维护索引与统计信息 #

你的现有索引IX_Geolocation_Version_Year已经是覆盖索引（包含了查询所需的所有字段），但可以通过以下方式优化其性能：

1. 检查并修复索引碎片 #

1200万条数据的索引容易产生碎片，导致IO效率下降：

-- 查询索引碎片率
SELECT 
    name AS 索引名称, 
    avg_fragmentation_in_percent AS 碎片率
FROM sys.dm_db_index_physical_stats(
    DB_ID(), 
    OBJECT_ID('cache.Geolocation'), 
    NULL, NULL, 'DETAILED'
)
WHERE index_id > 0

• 如果碎片率超过30%，重建索引：

ALTER INDEX IX_Geolocation_Version_Year ON cache.Geolocation REBUILD

• 如果碎片率在5%-30%之间，重组索引：

ALTER INDEX IX_Geolocation_Version_Year ON cache.Geolocation REORGANIZE

2. 更新统计信息 #

过时的统计信息会导致SQL优化器生成低效的执行计划，强制更新全量统计信息：

UPDATE STATISTICS cache.Geolocation WITH FULLSCAN

三、数据组织优化：针对大数据量的进阶方案 #

1. 按Year分区表 #

如果查询的Year范围相对固定，将表按Year分区，可大幅减少查询时的扫描范围：

-- 1. 创建分区函数（按Year值范围划分，示例覆盖1990-2020）
CREATE PARTITION FUNCTION PF_Geolocation_Year (INT)
AS RANGE RIGHT FOR VALUES (1990,1991,1992,1993,1994,1995,1996,1997,1998,1999,2000,2001,...,2020)

-- 2. 创建分区方案，指定分区存储的文件组（这里默认用PRIMARY）
CREATE PARTITION SCHEME PS_Geolocation_Year
AS PARTITION PF_Geolocation_Year
ALL TO ([PRIMARY])

-- 3. 重建聚集索引以应用分区（假设原主键为PK_Geolocation）
ALTER TABLE cache.Geolocation DROP CONSTRAINT PK_Geolocation
ALTER TABLE cache.Geolocation ADD CONSTRAINT PK_Geolocation 
PRIMARY KEY CLUSTERED (VersionId, Year, CityId)
ON PS_Geolocation_Year(Year)

-- 4. 让非聚集索引与分区对齐
ALTER INDEX IX_Geolocation_Version_Year ON cache.Geolocation 
REBUILD ON PS_Geolocation_Year(Year)

分区后，查询只会扫描对应Year的分区，IO开销显著降低。

2. 创建非聚集列存储索引 #

列存储索引针对大数据量的查询场景有极佳的压缩率和扫描性能，适合你的1200万条数据规模：

CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Geolocation 
ON cache.Geolocation (VersionId, Year, CityId, Count)

列存储索引会将数据按列压缩存储，查询时仅扫描需要的列，大幅减少磁盘IO。

3. 预计算查询结果（缓存） #

如果这些(VersionId+Year)组合是高频查询，可以创建汇总表定时刷新数据，避免每次都扫描大表：

-- 创建汇总表
CREATE TABLE cache.Geolocation_Summary (
    VersionId INT,
    Year INT,
    CityId INT,
    Count INT,
    PRIMARY KEY (VersionId, Year, CityId)
)

-- 定时刷新（比如每天凌晨执行）
TRUNCATE TABLE cache.Geolocation_Summary
INSERT INTO cache.Geolocation_Summary
SELECT VersionId, Year, CityId, Count FROM cache.Geolocation
-- 可以加入WHERE条件只刷新需要的组合，提升刷新速度

后续查询直接从Geolocation_Summary读取，速度能提升数倍甚至数十倍。

内容的提问来源于stack exchange，提问作者Guilherme