Appearance
MySQL 里怎么存 IP 归属地?long_ip 区间表 + 接口按需回填,别再维护离线库
有个做 SaaS 的团队问过我一个问题:他们的访问日志表已经有 4000 多万行,产品经理要一份"每个月各省活跃用户数"的报表,他们算了三天,最后交上来一份 Excel。
卡住的地方不是统计,是日志里只有 IP,没有省市。想要省份,就得先把 IP 翻译成归属地。
他们第一反应是"导一份离线 IP 库进 MySQL"。我劝他们先别急——在 MySQL 这个场景里,有一条更省事的路:建一张结果表,让接口按需把查过的 IP 填进来。这篇把两种做法的差别、表怎么设计、Python 脚本怎么写,一次讲清楚。
一、先想清楚:离线库和在线接口,到底差在哪
导入离线库(纯真、ip2region、GeoLite2 这类)的做法是:下载数据文件,转成一张 start_ip_num / end_ip_num / 省市 的区间表,导入 MySQL,查询时用 BETWEEN 做范围匹配。优点是不依赖外部、查询本地化;缺点也很实在:
- 要自己维护更新。 数据文件按周或按月更新,你得写导入脚本、处理版本、做灰度切换,一年下来这是一份持续的活。
- 区间表查询对索引不友好。 4000 万行日志去
JOIN一张几十万行的区间表并按范围匹配,SQL 优化空间很有限,跑报表时数据库压力肉眼可见。 - IPv6 不好处理。 v6 是 128 位,整数化后 MySQL 得用
BINARY(16)或两个BIGINT,区间比较写起来很别扭。
每次实时调接口的问题则是另一个方向:接口免费版限制是每分钟 60 次,一张 4000 万行的报表根本没法边算边查;而且报表任务会反复查同一批 IP,重复消耗配额。
第三种做法是把两者折中:建一张结果缓存表,以 IP 为主键,存归属性字段;报表任务先在日志里找出"没查过的 IP",按量级限速调用接口回填,再让统计 SQL 直接 JOIN 这张表。
好处很直接:查询是主键等值匹配,快到没有任何优化压力;同一批 IP 只查一次,配额花在真正没见过的 IP 上;归属地是按运营商段登记的,一个 IP 的省市几个月都不会变,缓存的性价比很高。
二、表设计:以 IP 为主键,long_ip 只做辅助
先说一个关键细节:接口返回里带 long_ip 字段——就是 IPv4 的整数形式(比如 114.114.114.114 返回 1920103026)。IPv6 的 long_ip 是 0(128 位塞不进整数),这一点决定了表结构不能拿 long_ip 当主键。
sql
CREATE TABLE ip_geo (
ip VARCHAR(45) NOT NULL COMMENT 'IP 原文,IPv4/IPv6 通用',
long_ip BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'IPv4 整数形式,IPv6 为 0',
country VARCHAR(32) NOT NULL DEFAULT '',
prov VARCHAR(32) NOT NULL DEFAULT '',
city VARCHAR(64) NOT NULL DEFAULT '',
isp VARCHAR(64) NOT NULL DEFAULT '',
big_area VARCHAR(16) NOT NULL DEFAULT '' COMMENT '国内大区,如华东',
status TINYINT NOT NULL DEFAULT 1 COMMENT '1=接口命中 0=IP 非法/未命中',
updated_at INT UNSIGNED NOT NULL COMMENT '本次回填时间戳',
PRIMARY KEY (ip),
KEY idx_long_ip (long_ip),
KEY idx_prov (prov)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;几点说明:
主键用 ip 字符串而不是 long_ip。 因为 IPv6 的 long_ip 恒为 0,用整数做主键会让所有 IPv6 记录撞在一起。VARCHAR(45) 能同时装下 IPv4(15 字符)和 IPv6(45 字符),主键等值查询走 B+ 树,性能没有问题。
long_ip 留着有用。 日志表里的 IP 如果是用 INET_ATON() 存的整数,两边可以直接按整数 JOIN,省掉字符串转换;另外按 CIDR 段做前缀统计时也用得上。
status 字段别省。 接口遇到非法 IP 时,HTTP 状态码就是 400(body 里 ret 同样是 400)。这类 IP——日志里的脏数据、内网地址、被截断的字符串——要落库并标记 status=0,否则每次报表都把它们当"没查过"再查一遍,白白烧配额。
updated_at 是缓存保质期的依据。 归属地变化慢但会变,回填太老的记录要能筛出来。
三、Python 实现:回填脚本
依赖两个库:requests 和 pymysql。核心逻辑是"挑出待回填的 IP → 限速调用接口 → 批量 upsert",接口是 https://ip9.com.cn/get?ip=<IP>(免费版无需注册、60 次/分钟、支持 IPv4 与 IPv6)。
python
import time
import pymysql
import requests
API = "https://ip9.com.cn/get"
INTERVAL = 1.05 # 免费版 60 次/分钟,留一点余量
TTL = 30 * 86400 # 缓存 30 天
BATCH = 500 # 每批 upsert 的行数
DB = dict(host="127.0.0.1", port=3306, user="report",
password="******", database="stat", charset="utf8mb4",
cursorclass=pymysql.cursors.DictCursor, autocommit=False)
def pick_pending(conn, day, limit):
"""挑出今天日志里出现最多、但没查过(或缓存过期)的 IP"""
sql = """
SELECT l.ip, COUNT(*) AS cnt
FROM access_log l
LEFT JOIN ip_geo g ON g.ip = l.ip
WHERE l.day = %s
AND (g.ip IS NULL OR g.updated_at < UNIX_TIMESTAMP() - %s)
GROUP BY l.ip
ORDER BY cnt DESC
LIMIT %s
"""
with conn.cursor() as cur:
cur.execute(sql, (day, TTL, limit))
return [r["ip"] for r in cur.fetchall()]
def fetch_one(ip):
"""返回 (status, geo_dict);429/网络异常抛异常,交给调用方退避"""
resp = requests.get(API, params={"ip": ip}, timeout=5)
if resp.status_code == 400: # 非法 IP:接口返回 HTTP 400,body 里 ret 也是 400
return 0, {}
resp.raise_for_status()
obj = resp.json()
ret = obj.get("ret")
if ret == 429:
raise RuntimeError("rate_limited")
if ret == 400: # 兜底:HTTP 正常但 ret 报错
return 0, {}
if ret != 200 or not isinstance(obj.get("data"), dict):
raise RuntimeError("bad_response: ret=%s" % ret)
d = obj["data"]
return 1, {
"ip": d.get("ip", ip),
"long_ip": int(d.get("long_ip") or 0),
"country": d.get("country") or "",
"prov": d.get("prov") or "",
"city": d.get("city") or "",
"isp": d.get("isp") or "",
"big_area": d.get("big_area") or "",
}
UPSERT = """
INSERT INTO ip_geo (ip, long_ip, country, prov, city, isp, big_area, status, updated_at)
VALUES (%(ip)s, %(long_ip)s, %(country)s, %(prov)s, %(city)s, %(isp)s,
%(big_area)s, %(status)s, %(updated_at)s)
ON DUPLICATE KEY UPDATE
long_ip = VALUES(long_ip), country = VALUES(country), prov = VALUES(prov),
city = VALUES(city), isp = VALUES(isp), big_area = VALUES(big_area),
status = VALUES(status), updated_at = VALUES(updated_at)
"""
def flush(conn, rows):
if not rows:
return
with conn.cursor() as cur:
cur.executemany(UPSERT, rows)
conn.commit()
def backfill(day, limit=2000, max_minutes=30):
conn = pymysql.connect(**DB)
ips = pick_pending(conn, day, limit)
print("待回填 IP:%d 个" % len(ips))
rows, ok, bad, failed = [], 0, 0, 0
deadline = time.time() + max_minutes * 60
last_call = 0.0
for ip in ips:
if time.time() > deadline: # 控任务时长,剩下的下一轮再跑
print("到达时间上限,本轮提前结束")
break
wait = INTERVAL - (time.time() - last_call)
if wait > 0:
time.sleep(wait)
try:
status, geo = fetch_one(ip)
last_call = time.time()
except Exception as e:
failed += 1
print("查询失败 %s: %s" % (ip, e))
time.sleep(3) # 失败退避,避免雪崩
continue
row = geo if status == 1 else {"ip": ip}
row.setdefault("long_ip", 0)
row.setdefault("country", "")
row.setdefault("prov", "")
row.setdefault("city", "")
row.setdefault("isp", "")
row.setdefault("big_area", "")
row["status"] = status
row["updated_at"] = int(time.time())
rows.append(row)
ok += 1
if len(rows) >= BATCH:
flush(conn, rows)
rows = []
flush(conn, rows)
conn.close()
print("完成:成功 %d,失败 %d" % (ok, failed))
if __name__ == "__main__":
backfill(day=time.strftime("%Y-%m-%d"), limit=2000)回填完之后,报表 SQL 就非常轻了——全是主键/索引等值关联:
sql
-- 按省统计今日 PV
SELECT g.prov, COUNT(*) AS pv
FROM access_log l
JOIN ip_geo g ON g.ip = l.ip
WHERE l.day = CURDATE() AND g.status = 1
GROUP BY g.prov
ORDER BY pv DESC
LIMIT 20;
-- 缓存命中率:低于 80% 说明该加大回填量了
SELECT ROUND(SUM(g.ip IS NOT NULL) / COUNT(*) * 100, 1) AS hit_rate
FROM access_log l
LEFT JOIN ip_geo g ON g.ip = l.ip
WHERE l.day = CURDATE();四、落地注意事项
回填顺序按访问量倒序。 ORDER BY cnt DESC 这一步很关键:4000 万行日志的独立 IP 可能有几十万,但前 2000 个高频 IP 往往能覆盖 80% 以上的访问量。先填高频,报表立刻就能用,低频的慢慢补。
配额要按真实需求算。 免费版是 60 次/分钟,脚本开着一个小时能填 3400 个左右,一天 8 万个——单机报表场景基本够。但注意这个限制是按来源 IP 计的:如果这台机器上还跑着别的调用(风控、日志清洗),它们共享同一份配额,脚本要错峰跑。真要每天几十万的量,该上 VIP 版就上,别用多进程硬刚限流。
"没命中"和"查失败"要分开落库。 非法 IP 会以 status=0 写库(接口对这种情况直接返回 HTTP 400);超时和 429 则不写库,下一轮再试。如果图省事把失败也标成"已查过",你会得到一批永远查不到归属地的脏缓存。
IPv6 单独留意。 接口支持 IPv6,返回的 long_ip 是 0。如果日志表混存 v4/v6,做整数 JOIN 时记得过滤 long_ip > 0,否则所有 v6 记录都会匹配到 long_ip = 0 的垃圾行。
存 IP 本身有合规成本。 IP 属于个人信息,做地域统计其实不需要保留原 IP——常见做法是统计完成后把明细里的 IP 换成不可逆哈希,或者只保留 ip_geo 这张"IP→地域"的映射表和历史聚合结果。ip_geo 表里的记录说明"某个 IP 属于哪个城市",不指向具体人;日志明细里的 IP 才是敏感的。报表需求只用到地域时,明细该脱敏就脱敏。
区县级要换版本。 免费版的 area 是空字段,能拿到的是省市。产品要按区县出报表(比如门店覆盖分析),需要 VIP 版的 area 字段,同时 ip_type(ISP/BUS/IDC)能帮你把机房流量从"用户"里摘出去——做用户地域统计时,机房 IP 会把省份分布整得面目全非,这个字段的价值比想象中大。
总结
MySQL 里存 IP 归属地,别一上来就想"导库"。用量级判断:
- 每天查的 IP 不到几万个,用结果表 + 接口按需回填,一次写好一百行脚本,之后基本不用运维;
- 每天几十万到上百万,回填 + VIP 版配额,或者先把高频 IP 覆盖住;
- 完全不能出外网(内网、专有云),才考虑导离线库,并且要接受它带来的更新维护成本。
表结构上记住三件事:主键用 IP 字符串、IPv6 的 long_ip 是 0、失败的结果不要落库。把这三条守住,这套方案能跑很久不用管。
接口是 https://ip9.com.cn/get?ip=<IP>(不传参数返回调用方自己的归属地),字段口径和各版本能力差别,官网 https://www.ip9.com.cn 上有完整说明。先在测试库跑一轮 limit=50 的回填,确认字段对得上报表口径,再放开量。