企业官网建设流程全解析

简介：面向数据库初学者及需要处理用户位置信息的开发者，这份 MySQL 手机号归属地数据合集提供可直接导入查询的号码与省市映射表，可用于营销分组、客服定位、数据统计分析等场景的练习与实战。压缩包仅 1 个文件，为单个 sql 脚本，整体约 2.22MB，导入后即得结构化的号码、省份、城市字段，便于按号码精确查询归属地，也可用分组聚合语句统计各省用户分布。已有 270 人学习下载，适合想借助真实数据熟悉 SQL 查询、多表关联与聚合分析的初中级使用者。数据完整度较高，读者可据此完成从建表导入、单条查询到批量统计的完整练习，并顺带理解敏感信息的合规使用以及访问权限、加密存储等安全要点，为后续数据分析工作积累可复用的表结构与查询思路。

1. 号段查归属地：一份 mysql 号段库能替掉多少接口调用

某公司客服后台要显示来电归属地，最早是直接调第三方接口，白天 QPS 一冲高，账单和超时率一起涨。后来把整套号段数据落到本地 mysql，按手机号前 7 位做一次主键命中，单机几万 QPS 也没再出现延迟尖刺。这份资源就是那套东西：号段主表、行政区划表、导入脚本三件套，覆盖三大运营商和虚拟运营商，能按省、市、长途区号、运营商四个维度出结果。它适合调用量大、要走内网、对稳定性有要求的业务；不适合拿来做精确定位或号码画像——号段只能告诉你“这个号开在哪个地市”，再往下就是另一套体系了。下面按建表、导入、查询、踩坑、更新一层层拆开。

2. mysql 侧建模：号段主表、行政区划表与两个字符集细节

拿到一份号段文本，很多人第一反应是“建一张表，字段全用 varchar(255)，导入完事”。跑得起来，但一旦并发上来、数据量到几十万行，问题就全冒出来了：前缀比较带隐式转换、排序走文件排序、批量 join 直接放弃索引。号段库的结构其实很简单，难点全在字段类型和字符集的取舍上，这一章先把地基打对。

2.1 三张表的分工与字段语义

号段数据天然是三层结构，硬塞进一张表会带来大量冗余，也不利于后续增量更新。

第一层是号段主表phone_segment，一行代表一个 7 位号段，主键就是号段本身。之所以选 7 位而不是 3 位或 11 位，是因为国内手机号的 11 位构成大致是「3 位网号 + 4 位号段 + 4 位用户号」，前 3 位只能区分到运营商大类的粗粒度，要精确到地市必须落到前 7 位。这是整份数据能用的前提，也是后面所有查询写法的基准。

第二层是行政区划表region，以 6 位行政区划代码为主键，存省、市、长途区号、邮编。号段主表里只放region_code外键式的引用，城市名通过 join 取。这样做的好处是：当行政区划调整（撤县设区、地市改名）时，只需要改一张小表，几十万行号段数据一行都不用动。

第三层是可选的运营商字典表，把「移动 / 联通 / 电信 / 广电 / 虚拟运营商」这类枚举单独存一份。如果业务只做展示，直接在主表里存字符串更省事；如果要做统计报表按运营商分组，字典表能让GROUP BY走整型而不是字符串比较，几十万行的聚合差距很明显。

字段设计上有三个容易被忽略的点。prefix必须是定长且大小写敏感、无隐式转换的字符类型，理由在 2.2 展开。province和city用VARCHAR(32)而不是CHAR，因为各地市名长度差异大，定长会浪费空间且影响排序效率。card_type用TINYINT UNSIGNED标记普通卡、虚商卡、物联网卡，这三种卡在归属地表现上完全不同，尤其是物联网卡，很多号段根本没有地市信息，业务侧必须能识别出来做降级。

提示：如果号段文本里本身带了「省份+城市」的拼接字段（比如「浙江 杭州」），导入前一定要拆成两列。后面按城市做聚合、做缓存 key 的时候，拆开的列能省掉大量字符串函数调用。

2.2 建表语句：为什么 prefix 用 CHAR(7) 做聚簇主键

这是整份库性能的关键决策，值得单独说清楚。

CREATE DATABASE IF NOT EXISTS phone_geo DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci; -- 5.7/8.0 都兼容；8.0 可换 utf8mb4_0900_ai_ci USE phone_geo; CREATE TABLE `phone_segment` ( `prefix` CHAR(7) CHARACTER SET ascii COLLATE ascii_bin NOT NULL COMMENT '号段前7位，如1380000', `province` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '省份', `city` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '地市', `area_code` CHAR(4) CHARACTER SET ascii NOT NULL DEFAULT '' COMMENT '长途区号，如0571', `zip_code` CHAR(6) CHARACTER SET ascii NOT NULL DEFAULT '' COMMENT '邮政编码', `isp` VARCHAR(16) NOT NULL DEFAULT '' COMMENT '运营商', `card_type` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '0普通 1虚商 2物联网', `region_code` CHAR(6) CHARACTER SET ascii NOT NULL DEFAULT '' COMMENT '行政区划代码', `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`prefix`), KEY `idx_region` (`region_code`), KEY `idx_isp_city` (`isp`, `city`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='手机号段归属地主表'; CREATE TABLE `region` ( `region_code` CHAR(6) CHARACTER SET ascii NOT NULL COMMENT '行政区划代码', `province` VARCHAR(32) NOT NULL DEFAULT '', `city` VARCHAR(32) NOT NULL DEFAULT '', `area_code` CHAR(4) CHARACTER SET ascii NOT NULL DEFAULT '', `zip_code` CHAR(6) CHARACTER SET ascii NOT NULL DEFAULT '', PRIMARY KEY (`region_code`), KEY `idx_city` (`city`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='行政区划字典';

这段 DDL 有三个刻意为之的地方，逐个解释。

第一，prefix直接做主键，没有用自增id。InnoDB 的主键是聚簇索引，数据和主键在同一个 B+ 树里，按prefix等值查一次就拿到整行，一次树查找、零次回表。如果改成自增 id + 唯一索引uk_prefix，每次查询要先走二级索引定位主键、再回表取数据，两次树查找。在每秒上万次查询的场景下，这个差异是实打实的。副作用是插入顺序不再是单调递增，页分裂会比自增主键多一些，但号段数据是一次性批量导入、之后低频增量更新，这个代价可以接受。

第二，prefix指定CHARACTER SET ascii COLLATE ascii_bin。手机号段全是数字，用 ascii 存每个字符只占 1 字节，CHAR(7)就是固定 7 字节；如果跟着库默认的 utf8mb4，每字符按 4 字节算，索引键长度直接翻四倍，B+ 树能装的键变少、树变高。ascii_bin是二进制比较，不做大小写和重音归一化，等值匹配是最快的路径。这里还有个隐蔽的好处：ascii 和 utf8mb4 的列做比较时，如果两边都是 ascii 子集的字符，优化器能直接把常量折叠掉；但如果prefix是 utf8mb4，而传进来的参数来自另一张 utf8mb4 表的表达式，字符集转换就可能落到列上，索引直接失效。第 3 章会讲这个坑的具体表现。

第三，索引只建了两个。idx_region服务于「按行政区划反查有哪些号段」的运营侧需求，idx_isp_city服务于「按运营商 + 城市统计」的报表。不要顺手给province、city各建一个单列索引，这两个字段基数低、选择性差，建成单列索引基本用不上，反而拖慢导入和更新。

注意：area_code用CHAR(4)，北京是010、成都是028，实际是 3 位，定长 4 位会在右侧补空格。比较时要么用TRIM()，要么明确接受补空格的行为——这一点在导出到前端展示时要特别处理，否则会出现「010 」这种带尾巴的区号。

2.3 数据导入：LOAD DATA 与分批 INSERT 的取舍

号段文本通常几十万行，导入方式直接决定你是一个午休搞定还是折腾一下午。

如果文件在数据库服务器本地，或者允许使用LOCAL INFILE，LOAD DATA是最快的，几十万行一般十几秒到一分钟级别。前提是服务端和客户端都要打开local_infile：

# 服务端开关（写进 my.cnf 才是永久的） mysql -uroot -p -e "SET GLOBAL local_infile = 1; SHOW VARIABLES LIKE 'local_infile';" # 客户端连接时必须显式带 --local-infile=1 mysql --local-infile=1 -ugeo -p phone_geo

LOAD DATA LOCAL INFILE '/data/phone_segment.csv' INTO TABLE phone_segment CHARACTER SET utf8mb4 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES (prefix, province, city, area_code, zip_code, isp, card_type, region_code) SET prefix = TRIM(prefix); -- 兜底去掉 CSV 里肉眼看不见的尾随空格

参数含义和改法：FIELDS TERMINATED BY ','对应 CSV 分隔符，如果拿到的是制表符分隔就换成'\t'；OPTIONALLY ENCLOSED BY '"'处理字段里带引号包裹的情况，去掉它会让「浙江,杭州」这种被误拆；IGNORE 1 LINES跳过表头，源文件没表头就必须删掉这一行，否则第一条数据会被当成脏数据吞掉；末尾的SET prefix = TRIM(prefix)是血泪经验——很多号段文本是 Excel 导出的，7 位数字后面带一个不可见空格，导入后看着是 7 位，实际是 8 位，查询永远匹配不上。

如果文件在应用服务器上、或者中间要做清洗校验，用 Python 分批写入更可控：

import csv import pymysql BATCH = 5000 # 每批行数，5000 是包大小和事务开销的平衡点 conn = pymysql.connect( host='127.0.0.1', user='geo', password='***', database='phone_geo', charset='utf8mb4', autocommit=False ) sql = ( "INSERT INTO phone_segment" "(prefix, province, city, area_code, zip_code, isp, card_type, region_code) " "VALUES (%s,%s,%s,%s,%s,%s,%s,%s) " "ON DUPLICATE KEY UPDATE province=VALUES(province), city=VALUES(city), " "area_code=VALUES(area_code), zip_code=VALUES(zip_code), isp=VALUES(isp), " "card_type=VALUES(card_type), region_code=VALUES(region_code)" ) buf = [] with conn.cursor() as cur, open('phone_segment.csv', newline='', encoding='utf-8') as f: reader = csv.reader(f) next(reader) # 跳过表头 for row in reader: row = [c.strip() for c in row] # 脏行前置拦截：位数不对、含非数字、字段缺失，一律丢弃并计数 if len(row) < 8 or len(row[0]) != 7 or not row[0].isdigit(): continue buf.append(tuple(row[:8])) if len(buf) >= BATCH: cur.executemany(sql, buf) conn.commit() buf.clear() if buf: # 收尾的残余批次 cur.executemany(sql, buf) conn.commit() conn.close()

逻辑说明：autocommit=False配合手动commit()，让每个 5000 行的批次成为一个事务，崩溃时最多回滚一批，不会出现「导入一半停在那里不知道从哪续」的尴尬。校验放在入buf之前，脏数据在内存里就被拦下来，不进数据库。ON DUPLICATE KEY UPDATE让这段脚本天然幂等，重复跑同一个文件不会报主键冲突，也顺便覆盖了后续增量更新的场景。

参数说明：BATCH不是越大越好。设成 5 万会让单条 SQL 的包体逼近max_allowed_packet默认值（64MB）的边界，同时一次事务锁住的行太多，期间其他写入会被阻塞。5000 到 10000 是普遍好用的区间。另外，MySQL 8.0.20 之后VALUES(col)语法被标记为废弃，会打 warning，新项目可以改用行别名写法AS new ON DUPLICATE KEY UPDATE province=new.province，语义一致，只是写法更现代。

3. 查询落地：从 7 位号段匹配到覆盖索引与执行计划

建好表、灌完数，接下来是真正被业务调用的部分。号段查询看上去就是一句WHERE prefix = ?，但写得不对，它在高并发下会变成全表扫描；写对了，它是 InnoDB 里最便宜的一次树查找。这一章把单查、批查、应用层封装三条路径都走一遍，每条给出可验证的写法。

3.1 号段切分规则与最基础的单条查询

先把切分规则固定下来，后面所有代码都依赖它。

单条查询的标准写法：

-- 传参前在应用层已经截好前 7 位，这里不再对列做任何函数运算 SELECT province, city, isp, card_type FROM phone_segment WHERE prefix = '1381234';

这里有一个必须强调的原则：不要在prefix列上套函数。写成WHERE LEFT(prefix, 7) = LEFT('13812345678', 7)或者WHERE CONCAT(prefix,'') = ?，聚簇索引立刻失效，几十万行全表扫。正确做法是把切分放在应用层或者 SQL 的常量侧——常量侧的函数在解析阶段就被折叠成字面量，不影响索引。

card_type字段要一起取出来。业务上最常见的翻车是：查询返回了城市，前端高高兴兴展示「浙江 杭州」，结果用户是物联网卡或者虚商卡，号段表里的city是空的，页面显示一个空白。取到card_type之后，业务侧就能判断「这一行需不需要降级到省份展示」。

3.2 执行计划验证：怎么确认它真的走了索引

写完 SQL 不算完，必须用EXPLAIN确认一遍，这一步很多人省掉，然后在线上被慢查询打脸。

EXPLAIN SELECT province, city, isp, card_type FROM phone_segment WHERE prefix = '1381234';

期望看到的结果是：type = const，key = PRIMARY，rows = 1，Extra里没有Using where之外的额外信息。type = const说明是主键等值查询，优化器在优化阶段就把这一行定位到了，执行阶段几乎是零成本。

常见的三种异常和对应原因：

type = ALL或index，说明没用上主键。最常见的原因是传参类型不匹配——如果应用层把prefix当成整数传（比如1381234），MySQL 会做隐式类型转换，把CHAR列转成数字再比较，索引失效。另一种原因是传参带了尾随空格，'1381234 '和'1381234'在ascii_bin下不相等，但某些连接层的PAD SPACE行为会让它看起来「查不到又没报错」。

type = ref且key = idx_region，说明优化器选错了索引。这种情况一般出现在带了额外过滤条件的复合查询里，比如同时按region_code和isp过滤。可以用FORCE INDEX(PRIMARY)强制走主键，但更根本的做法是检查 WHERE 条件里是不是有更「显眼」的列把优化器带偏了。

rows数值远大于 1，多半是统计信息过期，ANALYZE TABLE phone_segment;重新采一次即可。号段表数据几乎不变，一年做一两次就够。

还有一个和字符集相关的隐蔽坑，值得单独记一笔。当查询来自 join，比如ON s.prefix = LEFT(t.phone, 7)，LEFT()返回的字符串继承的是连接字符集（通常是 utf8mb4），而prefix是 ascii。MySQL 在做等值比较时会按「可转换性」规则统一字符集，如果这个转换被判定为需要作用在列上，索引就用不上了。我一般的做法是把手机号列本身声明成CHARACTER SET ascii，或者在 join 条件里显式写CONVERT(LEFT(t.phone,7) USING ascii)，把这个不确定性消掉。

3.3 批量查询：临时表 join 与分批 IN 的取舍

单个号码查询是低频路径，真正压测打爆数据库的是批量——比如导入一批工单、清洗一份客户名单、跑一次离线报表。这时最常见的写法是一长串IN：

SELECT prefix, province, city FROM phone_segment WHERE prefix IN ('1381234','1590000','1701234', ... ); -- 几百上千个

几百个还能撑住，上千个开始出问题：SQL 文本本身变长、解析开销上升、优化器在生成执行计划时对超长 IN 列表的处理会退化，极端情况下会退化成逐个探测。更稳的做法是用临时表做 join：

-- 1) 建会话级临时表，主键天然去重 CREATE TEMPORARY TABLE tmp_phone ( phone CHAR(11) CHARACTER SET ascii NOT NULL, PRIMARY KEY (phone) ) ENGINE=InnoDB; -- 2) 应用层分批灌入，每批 1000 行 INSERT INTO tmp_phone(phone) VALUES ('13812345678'), ('15900001111'), ('17012345678'); -- 3) 左连接保证「查不到」的号码也留在结果集里 SELECT t.phone, s.province, s.city, s.isp, s.card_type, CASE WHEN s.prefix IS NULL THEN 1 ELSE 0 END AS not_found FROM tmp_phone t LEFT JOIN phone_segment s ON s.prefix = CONVERT(LEFT(t.phone, 7) USING ascii);

逻辑说明：LEFT JOIN而不是INNER JOIN，是因为批量场景里「哪些号码查不到」本身就是重要信息——查不到意味着号段库版本旧，需要走降级或者补数据，用not_found标记出来，比事后拿结果集去反推省事得多。CONVERT(... USING ascii)是 3.2 提到的字符集兜底，保证 join 条件下推时索引可用。临时表用ENGINE=InnoDB而不是MEMORY，是因为MEMORY引擎对变长列和字符集的支持有坑，且数据量超过max_heap_table_size时会静默转成磁盘表，反而更慢。

参数说明：每批 1000 行是经验值。小于 500 时往返次数太多，网络 RTT 成为瓶颈；大于 5000 时单条INSERT的包体和临时表锁持有时间都会上升，并发写入同一张临时表（如果是共享会话）会互相阻塞。临时表是会话级的，连接断开自动清理，不需要手动DROP，但如果用了连接池且连接长期不释放，建议在批次结束时显式DROP TEMPORARY TABLE，避免残留数据污染下一批。

3.4 应用层封装：本地缓存与连接复用

数据库再快也是网络调用，热点号段的命中高度集中，加一层进程内缓存收益很大。

import pymysql from functools import lru_cache class PhoneGeo: def __init__(self, conn): self.conn = conn @lru_cache(maxsize=16384) def lookup(self, prefix: str): """prefix 必须是已切好的 7 位字符串，切分逻辑统一在上层做""" with self.conn.cursor(pymysql.cursors.DictCursor) as cur: cur.execute( "SELECT province, city, isp, card_type " "FROM phone_segment WHERE prefix = %s", (prefix,) ) row = cur.fetchone() # 查不到也缓存成哨兵值，防止同一个不存在的号段反复打库 return row if row else {"province": "", "city": "", "isp": "", "card_type": -1}

逻辑说明：缓存 key 用 7 位号段而不是完整手机号，把命中率从「按号码」提升到「按号段」，一个热门号段能覆盖成千上万个号码，缓存条目数从百万级降到万级。查不到时返回哨兵字典而不是None，这一点很重要——如果返回None，lru_cache不会缓存None之外的「否定结果」语义，每次都会重新查库，等于缓存对脏数据完全失效。

参数说明：maxsize=16384覆盖的是实际活跃号段的数量级。号段总量在几十万，但真实业务里活跃的、被反复查询的集中在几千到一两万个，全量加载进内存反而是另一种浪费。lru_cache挂在实例方法上会持有self的强引用，如果PhoneGeo是请求级对象，缓存会跟着请求一起被回收，等于没缓存——所以这个类必须是应用级单例，初始化一次，全局复用同一个数据库连接。

注意：应用层缓存有一个更新时效问题。号段库更新后，旧缓存最长会存活到进程重启。要么在更新流程里主动调用一次缓存清理，要么给缓存加版本号前缀，让新版本自然覆盖。

4. 避坑与排查：号段变更、虚商空值和那几条血泪记录

号段库这种「静态数据」最容易让人放松警惕，觉得导入一次就一劳永逸。实际情况是，运营商每年都在放新号段、调整号段归属，虚拟运营商和物联网卡的地市信息缺失是常态。下面五条都是真实踩过的，按「现象 → 原因 → 解决」的顺序写，方便对照排查。

4.1 五条踩坑记录

第一条：新号段查不到，用户投诉「我们这儿显示未知」。

现象是某地一批用户反馈归属地空白，抽查号码发现是近两年新放的号段，本地库里确实没有。原因是号段库版本停留在导入那天，而运营商放号是持续的，新号段从放号到进入公开数据源还有滞后。解决分两层：一是建立增量更新机制（第 5 章展开），按季度合并新号段；二是业务侧必须有降级路径，查不到时回落到第三方接口并把号码前缀写进一张miss_log表，这张表就是下一版要重点补的号段清单，比盲目全量更新高效得多。

第二条：虚商号段城市为空，前端渲染出「None」。

现象是页面出现「浙江 None」或者干脆一片空白。原因是虚拟运营商号段在数据源里往往只标注到省，city字段本身就没有值，这不是数据缺失，是这类号段的固有属性。解决方式是在查询层统一收敛，SELECT COALESCE(NULLIF(city,''), province) AS display_city，同时把card_type=1透出到前端，让展示逻辑知道「这一条只精确到省」。千万不要在数据库里给虚商号段随便填一个省会城市，那是制造错误数据，比空值更危险。

第三条：明明有数据，按条件查就是查不到。

现象是拿号码去查返回空，但直接SELECT * FROM phone_segment LIMIT 10能看到相似号段。原因通常有三种：源文件从 Excel 导出时带了不可见字符或全角数字；prefix列被误建成VARCHAR且带尾随空格；应用层传参时把号码当整数处理，前导零或类型转换把值改了。解决是三道防线——入库时TRIM+ 正则校验^[0-9]{7}$，列类型用CHAR(7) ascii_bin定长比较，应用层强制str类型并在切分前做一次phone.strip()。

第四条：批量查询跑到一半报错或者超时。

现象是单查没问题，批量跑几千个号码时 SQL 变慢甚至报Packet too large。原因是超长IN列表导致 SQL 文本膨胀，解析和执行计划生成阶段的开销远超实际数据扫描。解决是改用 3.3 的临时表 join，每批 1000 行；如果业务方坚持要用IN，至少做分批，把 5000 个拆成 5 次 1000 个，且每批之间让出连接，避免长事务。

第五条：拿本地号段库判运营商，风控误判率偏高。

现象是风控规则里用isp字段做「是否为某运营商」的判断，结果命中率对不上。原因是携号转网已经普及，号码的号段归属运营商和当前实际承载运营商可以不一致，本地号段库只能反映「开户时的号段归属」，反映不了转网后的状态。解决办法很明确：这份库只用于归属地展示和弱参考，任何涉及计费、风控、权益判定的场景，运营商字段必须走运营商侧的实时校验，不能拿静态表当权威。这条不是库的问题，是用错了地方。

4.2 自检 SQL：导入后必跑的四条校验

数据导入完成，别急着上线，先跑一遍这几条。它们能在几分钟内暴露 90% 的导入问题。

-- 1) 位数与字符异常：不是 7 位纯数字的全部揪出来 SELECT prefix, LENGTH(prefix) AS len FROM phone_segment WHERE LENGTH(prefix) <> 7 OR prefix NOT REGEXP '^[0-9]{7}$' LIMIT 100; -- 2) 关键字段缺失：省份为空的记录基本等于废数据 SELECT COUNT(*) AS empty_province FROM phone_segment WHERE province = '' OR province IS NULL; -- 3) 主键重复（理论上不可能，但导入中途失败重跑时会出现） SELECT prefix, COUNT(*) AS c FROM phone_segment GROUP BY prefix HAVING c > 1; -- 4) 省份分布：和预期对比，能快速发现「只导入了部分文件」 SELECT province, COUNT(*) AS c FROM phone_segment GROUP BY province ORDER BY c DESC LIMIT 12;

四条的含义：第一条抓脏数据，REGEXP在几十万行上跑一次大约几秒，可以接受；第二条抓字段缺失，province为空的行在业务上不可用，必须清零；第三条抓重复，正常情况下主键约束会拦住，但用INSERT IGNORE或者中间表导入时可能出现；第四条是最有价值的——如果省份分布里前几名是空字符串或者只有三四个省，说明文件被截断了或者只导入了一部分，这比任何行数统计都直观。

-- 5) 号段覆盖率抽样：随机取若干号码，看能否命中 SELECT COUNT(*) AS total, SUM(CASE WHEN s.prefix IS NULL THEN 1 ELSE 0 END) AS miss FROM ( SELECT CONCAT(LEFT(prefix,3), LPAD(FLOOR(RAND()*10000), 4, '0')) AS fake FROM phone_segment ORDER BY RAND() LIMIT 1000 ) t LEFT JOIN phone_segment s ON s.prefix = t.fake;

这条是抽样自检：拿现有号段的前 3 位拼出随机后 4 位，构造 1000 个「看起来像真号」的字符串，再回查命中率。命中率明显偏低说明号段的 7 位粒度不统一（有些源只有 3 位或 4 位精度），这种情况需要回到数据源头核对，而不是在查询层打补丁。

5. 增量更新与版本切换：让号段库不再是「一次性消耗品」

一份号段库导入完就锁死，用半年之后必然落后。真正决定这份资源能不能长期用的是更新流程：怎么发现新旧差异、怎么幂等合并、怎么在不影响线上查询的前提下切过去。这一章给一套可以直接落到定时任务里的做法。

5.1 新旧版本比对：先出一张差异清单再动手

拿到新版号段文件，不要直接往生产表里灌。先导入一张结构相同的影子表，然后和现网表做全量比对，把「新增」「变更」「消失」三类分别统计出来。

-- 影子表：结构与主表完全一致，导入新版本数据 CREATE TABLE phone_segment_new LIKE phone_segment; -- （此处执行 LOAD DATA 或分批 INSERT 导入新文件） -- 1) 新增号段 SELECT n.prefix, n.province, n.city, n.isp FROM phone_segment_new n LEFT JOIN phone_segment o ON o.prefix = n.prefix WHERE o.prefix IS NULL; -- 2) 归属地发生变化的号段（重点关注） SELECT o.prefix, o.province AS old_province, n.province AS new_province, o.city AS old_city, n.city AS new_city FROM phone_segment o JOIN phone_segment_new n ON n.prefix = o.prefix WHERE o.city <> n.city OR o.province <> n.province; -- 3) 新版里消失的号段（通常是数据源裁剪，别急着删） SELECT o.prefix, o.province, o.city FROM phone_segment o LEFT JOIN phone_segment_new n ON n.prefix = o.prefix WHERE n.prefix IS NULL;

三类结果的处理策略完全不同。新增号段直接合并，没有风险。归属地变更的号段要人工抽查——有些是运营商真的调整了，有些是新版数据源本身的错误，盲目覆盖会让老用户看到错误归属地。消失的号段建议保留不删，因为数据源裁剪不代表这些号段停了，删掉之后存量号码会查不到，宁可留着。

5.2 幂等合并：upsert 与影子表原子切换

合并有两种做法，按业务对停机的容忍度选。

第一种是原地 upsert，适合能接受短暂锁表的场景：

-- 在主表上直接合并新版数据，幂等，可重复执行 INSERT INTO phone_segment (prefix, province, city, area_code, zip_code, isp, card_type, region_code) SELECT prefix, province, city, area_code, zip_code, isp, card_type, region_code FROM phone_segment_new ON DUPLICATE KEY UPDATE province = VALUES(province), city = VALUES(city), area_code = VALUES(area_code), zip_code = VALUES(zip_code), isp = VALUES(isp), card_type = VALUES(card_type), region_code = VALUES(region_code), updated_at = NOW();

这条语句的含义是：以影子表为源，逐行往主表写，主键已存在就更新字段，不存在就插入。updated_at = NOW()让每一次真实变更都留下时间戳，后续排查「某个号段是什么时候变的」有据可依。执行时间取决于行数，几十万行一般在几十秒内，期间主表有行锁但不会全表锁，线上查询基本无感。

第二种是影子表重命名，适合要求「切换瞬间生效、不留中间态」的场景：

-- 先把新数据完整灌进 phone_segment_new，校验无误后一次性切换 RENAME TABLE phone_segment TO phone_segment_old, phone_segment_new TO phone_segment; -- 观察一段时间，确认无异常后再清理旧表 -- DROP TABLE phone_segment_old;

RENAME TABLE支持多表原子重命名，两条改名在同一个语句里完成，对外表现为瞬时切换，不会有「主表不存在」的窗口。旧表保留一周左右，出问题可以一条语句切回去，这就是线上的后悔药。缺点是切换瞬间正在执行的长查询可能受影响，所以建议放在低峰期。

5.3 定时刷新与回滚预案

更新流程定了，接下来是把它变成可调度的任务。我一般的节奏是：每季度拉一次新版号段，走「下载 → 影子表导入 → 差异比对 → 人工抽查变更项 → 合并或重命名 → 清理旧表」这条固定流水线，全程由脚本串起来，人只负责看差异报告和点确认。

调度上有两个细节值得写进脚本。一是加一把数据库层面的锁，防止上一次任务没跑完、下一次又被定时器拉起来，两个进程同时往影子表里灌数据。用SELECT GET_LOCK('phone_geo_sync', 0)拿一个命名锁，拿不到就直接退出，这是最简单可靠的互斥。二是每次更新都记录版本号和行数快照，存一张sync_history小表，字段就version、row_count、added、changed、finished_at这么几个。出问题时第一眼能看到「上一次是什么时候、动了多少行」，比翻日志快得多。

回滚预案要提前写死在文档里，而不是出事时现想。原地 upsert 方式的回滚最麻烦，因为旧值已经被覆盖，只能靠备份表恢复；所以用这种方式之前，务必先CREATE TABLE phone_segment_bak_20250101 AS SELECT * FROM phone_segment。影子表重命名方式的回滚就一条RENAME TABLE，把_old换回来即可。这也是我更倾向影子表方案的原因——省下的不是执行时间，是出事时的那份从容。

提示：号段库这类数据更新频率低但影响面广，建议把「差异比对」这一步的产出物保留下来，做成一份可追溯的变更清单。半年后有人问「某个号段的归属地为什么变了」，翻这份清单比查数据库的updated_at精确得多。

6. 进阶：把单次归属地查询压到微秒级的三条路径

号段库的查询优化到头了其实就三种形态，选哪种取决于你的 QPS 和部署形态。我在不同项目里三种都用过，这里把实测体感和切换成本摊开说。

第一条路径就是前几章写的那套，主键聚簇查询，万级 QPS 完全够用。真正需要往下走的是这样一类场景：单机每秒几万次归属地解析，且这些查询散落在多个业务模块里，每次都建连接、走网络，累积起来的开销开始显眼。

这时候把整张映射表加载进进程内存是最直接的解法。数据结构选dict，key 是 7 位号段，value 是一个轻量元组：

import pymysql def load_all(conn): """全量加载号段到内存字典；value 用元组比字典省一半内存""" with conn.cursor() as cur: cur.execute( "SELECT prefix, province, city, isp, card_type FROM phone_segment" ) return {r[0]: (r[1], r[2], r[3], r[4]) for r in cur}

加载耗时在几十万行量级大约是几百毫秒到一两秒，放在服务启动阶段完全可接受。查询就变成table.get(phone[:7])，一次哈希查找。这里的关键不是快，而是必须留热加载入口：加一个只监听内网的 HTTP 端点或者信号处理器，收到指令就重新load_all一次并原子替换引用（global TABLE; TABLE = new_table），避免因为号段更新被迫重启整个服务。原子替换这一步不能省，直接TABLE.clear()再重新填，会在清空和填充之间留下一个查询全部落空的窗口。

有个容易被忽略的细节：进程内字典占用的内存不是一次性的。如果每个 worker 进程各加载一份，8 个 worker 就是 8 份副本，40MB 会变成 320MB。对内存敏感的环境，要么只在单个进程里加载、其他进程通过本地 socket 或者共享内存访问，要么直接走 Redis 那条路。

第二条路径的收益在极端量级下才明显，但对大多数团队来说，性价比最高的其实是第三步——先把前两步做对，再谈第三步。我见过太多项目一上来就上 Redis，结果 MySQL 那边的索引根本没建对，慢查询日志里躺着全表扫描，缓存只是把问题延后了。从那以后我每次上缓存之前，都强制先去EXPLAIN里确认一遍type = const，确认数据库这条腿是健康的，再考虑要不要加缓存这层护具。

最后给一个小技巧：如果业务只需要「省市」两个字段，把isp、area_code、zip_code从查询里去掉，只取主表的两列，虽然聚簇索引本来就要读整行、省不了 IO，但在批量导出的场景里能显著减小结果集和网络传输量。导出一百万条归属地做报表时，这个改动能让输出体积缩掉三分之二，落盘和后续解析都快不少。这份号段库的价值不在于它有多少行，而在于把它接进你的系统之后，归属地这件事从此不需要再依赖外部接口的响应时间。希望帮到你。

本文还有配套的精品资源，点击获取