Skip to content

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 的回填,确认字段对得上报表口径,再放开量。