主页 / dba / 问题 / 17747
Accepted
Cristian Lupascu
Asked: 2012-05-12 07:09:27 +0800 CST

TSQL 性能 - 在最小值和最大值之间加入值

  • 772

我有两个存储表：

  • IP 范围 - 国家/地区查找表
  • 来自不同 IP 的请求列表

IP 存储为bigints 以提高查找性能。

这是表结构：

create table [dbo].[ip2country](
    [begin_ip] [varchar](15) NOT NULL,
    [end_ip] [varchar](15) NOT NULL,
    [begin_num] [bigint] NOT NULL,
    [end_num] [bigint] NOT NULL,
    [IDCountry] [int] NULL,
    constraint [PK_ip2country] PRIMARY KEY CLUSTERED 
    (
        [begin_num] ASC,
        [end_num] ASC
    )
)

create table Request(
    Id int identity primary key, 
    [Date] datetime, 
    IP bigint, 
    CategoryId int
)

我想获取每个国家/地区的请求细分，因此我执行以下查询：

select 
    ic.IDCountry,
    count(r.Id) as CountryCount
from Request r
left join ip2country ic 
  on r.IP between ic.begin_num and ic.end_num
where r.CategoryId = 1
group by ic.IDCountry

我在表中有很多记录：大约 200,000 inIP2Country和几百万 in Request，因此查询需要一段时间。

查看执行计划，最昂贵的部分是索引 PK_IP2Country 上的 Clustered Index Seek，执行多次（Request 中的行数）。

另外，我觉得有点奇怪的是left join ip2country ic on r.IP between ic.begin_num and ic.end_num零件（不知道是否有更好的方法来执行查找）。

SQLFiddle 中提供了表结构、一些示例数据和查询：http ://www.sqlfiddle.com/#!3/a463e/3 （不幸的是，我认为我不能插入很多记录来重现问题，但这希望给出一个想法）。

我（显然）不是 SQL 性能/优化方面的专家，所以我的问题是：是否有任何明显的方法可以改进我所缺少的这种结构/查询的性能？

sql-server-2008 t-sql

3 个回答

  1. Best Answer

    你需要一个额外的索引。 在您的小提琴示例中，我添加了：

    CREATE UNIQUE INDEX ix_IP ON Request(CategoryID, IP)

    它涵盖了请求表并获取索引查找而不是聚集索引扫描。

    看看如何改进它并告诉我。我猜这会很有帮助，因为我确定对该索引的扫描并不便宜。

    • 3

  2. 总是有蛮力的方法：你可以爆炸你的 IP 地图。针对现有地图加入数字表，为每个 IP 地址创建一条记录。根据您的 Fiddle 数据，这只有 267K 条记录，完全没有问题。

    CREATE TABLE IPLookup
  (
  IP  BIGINT PRIMARY KEY,
  CountryID  INT
  )
INSERT INTO IPLookup (IP, CountryID)
  SELECT
    N.Number, Existing.IDCountry
  FROM
    ip2country AS Existing
    INNER JOIN Numbers AS N ON N.Number BETWEEN Existing.begin_num AND Existing.end_num

    这将使搜索更简单，并有望更快。当然，这只有在您对 进行相对较少的更新时才有意义ip2country。

    我希望其他人有更好的解决方案！

    • 2

  3. 尝试这个：

    SELECT ic.IDCountry,
        COUNT(r.Id) AS CountryCount
FROM Request r
INNER JOIN (SELECT begin_num+NUMS.N [IP], IDCountry 
            FROM ip2country
            CROSS JOIN (SELECT TOP(SELECT ABS(MAX(end_num-begin_num)) FROM ip2country) ROW_NUMBER() OVER(ORDER BY sc.name)-1 [N]
                        FROM sys.columns sc) NUMS
            WHERE begin_num+NUMS.N <= end_num) ic
ON r.IP = ic.IP
WHERE r.CategoryId = 1
GROUP BY ic.IDCountry
    • 0

相关问题