PostgreSQL通过starting_ip和ending_ip区间匹配IP所属城市慢查询优化

性能优化方案 #

1. 新增匹配查询逻辑的索引 #

你当前的查询核心逻辑是先筛选ending_ip >= 目标IP的记录，按ending_ip升序取第一条再校验starting_ip，可以直接针对这个逻辑建索引避免全表扫描：

• 基础B树索引：直接给ending_ip建B树索引，PostgreSQL原生支持inet类型的B树索引排序和比较操作，第一步的查询可以直接走索引拿到目标记录，不需要遍历全表

CREATE INDEX idx_ip_cities_ending_ip ON ip_cities USING btree(ending_ip);

• 覆盖索引优化：如果只需要返回city_name等固定字段，可以建覆盖索引，避免索引查询后回表读数据，进一步提升性能

CREATE INDEX idx_ip_cities_covering ON ip_cities USING btree(ending_ip) INCLUDE (starting_ip, city_name);

• 大数量级专用优化：如果表数据量超过百万级，可以安装PostgreSQL的ip4r扩展，使用IP范围类型+GIST索引优化，性能是普通B树索引的3-10倍：

-- 安装扩展
CREATE EXTENSION ip4r;
-- 新增IP范围字段，写入现有区间数据
ALTER TABLE ip_cities ADD COLUMN ip_range iprange;
UPDATE ip_cities SET ip_range = iprange(starting_ip, ending_ip);
-- 建GIST索引
CREATE INDEX idx_ip_cities_ip_range ON ip_cities USING gist(ip_range);
-- 简化后的查询语句
SELECT * FROM ip_cities WHERE ip_range >>= $1::inet LIMIT 1;

2. 修正SQL写法规避安全问题 #

你当前直接拼接IP参数到SQL字符串的写法存在SQL注入风险，调整为参数绑定的写法更安全，同时也不会影响数据库查询优化器的判断：

class IpToCity < ActiveRecord::Base
  establish_connection :"ip_database_#{Rails.env}"
  
  scope :search_within_ip_range, -> (ip_address) do
    sql = <<~SQL
      WITH candidate AS (
        SELECT * FROM ip_cities
        WHERE ending_ip >= ?::inet
        ORDER BY ending_ip ASC
        LIMIT 1
      )
      SELECT * FROM candidate WHERE starting_ip <= ?::inet
    SQL
    connection.select_all(sanitize_sql_array([sql, ip_address, ip_address]))
  end
end

3. 额外性能优化手段 #

• 静态数据缓存：IP地理位置库一般更新频率很低，可以把高频查询的IP对应城市结果存到Redis缓存，缓存过期时间可以设为24小时，大幅降低数据库查询压力
• 本地文件查询：如果并发要求极高，可以把IP库导出为MMDB格式（MaxMind数据库格式），直接用Ruby的maxminddb gem读取本地文件查询，性能比查数据库高100倍以上
• 整数转换优化：如果只需要支持IPv4，可以把IP提前转为32位无符号整数存储，整数比较性能比inet类型更高，建索引后查询速度也会更快

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